MySQL's memory table basics tutorial

The size of the memory table can be specified by max_heap_table_size in My.cnf, such as max_heap_table_size=1024M. When the data exceeds the maximum value of the temporary table, it will be automatically converted to the disk table. At this time, the performance will be greatly reduced due to the need for IO operation, while the memory table will not. When the memory table is full, it will prompt the data full error.

Temporary tables and in-memory tables can be created manually, but temporary tables are more useful for the system to organize the data to improve performance after it has created itself, such as subqueries, which cannot be Shared between multiple connections. Only memory tables are discussed here

To create the table, engine=heap can be created with engine=heap.

create table test
(
 id int unsigned not null auto_increment primary key,
 state char(10),
 type char(20),
 date char(30)
)ENGINE=MEMORY DEFAULT CHARSET=utf8;

memory table features

1. For variable length types such as varchar, the memory table is stored with a fixed length;

2. The memory table can have non-unique 1 key;

3. The memory table cannot contain BLOB or TEXT columns;

4. Memory table supports column AUTO_INCREMENT;

5. Memory table supports insert delay to make read priority;

6. Non-temporary memory tables and other non-memory tables are directly Shared by all clients;

When we use the memory table, we need to pay attention to the following aspects:

1. The server memory is large enough;

2. The memory table we created is different from the MySQL internal temporary table:

The data of           memory table is stored in memory, while the internal temporary table (produced by our query statement) is stored in memory when appropriate. When the internal temporary table becomes large, MySQL will automatically convert it to a table stored on disk, while the memory table we created will not automatically convert.

3. When we separately delete from a certain memory table, memory will not be recycled; Memory is recycled only when the entire table is delete;

4. On MySQL's master-slave server, the memory tables can be copied

Some usage limits of memory table

The MySQL memory table USES a hash hash index to keep data in memory, so it is extremely fast and suitable for caching small and medium-sized databases, but its use is limited.

1. heap is visible to all users' connections, which makes it ideal for caching.

2. Suitable for use only. heap does not allow the use of xxxTEXT and xxxBLOB data types; Only = and is allowed < = > Operator to search for records (not allowed & lt; , > , < = or > =); auto_increment is not supported; Only non-null data columns are allowed to be indexed (not null).
Note: operator" < = > Note: NULL-safe equal. This operator and the = operator perform the same comparison operation, except that when both opcodes are NULL, the value is 1 instead of NULL, and when one opcode is NULL, the value is 0 instead of NULL.

3. Once the server restarts, all the heap table data is lost, but the heap table structure still exists, because the heap table structure is stored in the actual database path and will not be automatically deleted. After rebooting, heap will be emptied, and the query results for heap will be empty.

4. If heap is a replicated data table, all primary key, index, self-increment and other formats will no longer exist after replication, and primary key and index need to be added again, if necessary.

5. For data loss caused by restart, there are the following solutions:
a. Before any query, perform a simple query to determine whether there is data in the heap table. If there is no data, write the data back to the a table, or copy a new table from the DROP table. This requires one more query. However, it can be written as an include file, which is convenient to call whenever you need the heap table.
b, for the page that needs the heap table, judge the result of the data set when the table is queried for the first and only the first time. If the result is empty, the data needs to be rewritten. This saves 1 query.
c. A better approach is to automatically write data to heap every time mysql restarts, but the server needs to be configured, the process is complex, and the generality is limited.
Bluegrass currently USES the second approach.

6. 1 some expected sql statements

// If the table exists, it is deleted 
DROP TABLE IF EXISTS `abc`;
// Copy the entire table xyz for heap table abc (all data included) 
CREATE TABLE `abc` type=heap select * from `xyz`;
// Add a primary key id
ALTER TABLE `abc` ADD PRIMARY KEY (`id`);
// Adding indexes username
ALTER TABLE `abc` ADD INDEX `abc` (`username`);