Features and usage of MySQL memory tables

The memory table is a table in the memory. The size of the memory used can be calculated by using My. specify max_heap_table_size in cnf. For example, max_heap_table_size = 1024 M. The memory table is different from the temporary table. The temporary table is also stored in the memory. The maximum memory required for the temporary table must be set by tmp_table_size = M. When the data exceeds the maximum value of the temporary table, it is automatically converted to a disk table. When I/O operations are required, the performance will be greatly reduced, but the memory table will not. When the memory table is full, A message indicating full data error is displayed.

Both temporary tables and memory tables can be created manually. However, temporary tables are used to organize data after being created by the system to improve performance, such as subqueries, temporary tables cannot be shared among multiple connections. Here we will only discuss memory tables

Create a table that can be created using engine = heap (type is not supported in mysql5.5 and will be used in the future to form a habit ).

Copy codeThe Code is as follows: 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;

Features of memory tables

The memory table is defined as stored on the disk. The extension is. frm, so the restart will not be lost.
The data in the memory table is stored in the memory, so data will be lost after restart.
The memory table uses a fixed record length format.
Memory tables do not support BLOB or TEXT columns, such as varchar and text fields.
The memory table supports the AUTO_INCREMENT column and the index of the column that can contain NULL values (this is incorrect because it is not supported on the Internet ). Memory tables support operations greater than (>) and less than (<), which are not supported on the Internet.
After mysql is restarted, the primary key, auto-increment, and index still exist, but data is lost. This is also used to correct some incorrect texts on the Internet.
The memory table is shared among all clients (just like any other non-TEMPORARY table ).
The MEMORY storage engine performs HASH and BTREE indexes. You can specify one or another for a given index by adding a USING clause as follows:

Copy codeThe Code is as follows: create table lookup
(Id INT, index using hash (id ))
ENGINE = MEMORY;

Create table lookup
(Id INT, index using btree (id ))
ENGINE = MEMORY;

You can use -- init-file to initialize the memory table to avoid data clearing after mysql is restarted. For example, -- init-file = "/data/mysql/init. SQL", the init. SQL format is:

Copy codeThe Code is as follows: use db_test;
Select *** into m_table;

During database replication, if the host crashes, the delete from [memory table] will be automatically added to the binLog to delete the slave data to ensure data consistency between the two sides.
The memory table does not support transactions.
The memory table is a table lock. When the modification is frequent, the performance may decrease.
Memory table usage
Memory tables use Hash hash indexes to store data in the memory. Therefore, it is extremely fast and suitable for caching Small and Medium databases, but it is subject to some restrictions.

Heap is visible to all users, which makes it very suitable for caching.
Applicable only to scenarios. Heap does not allow xxxTEXT and xxxBLOB data types. Note: The operator "<=>" description: NULL-safe equal. this operator and the "=" operator perform the same comparison operation. However, when both operation codes are NULL, the obtained value is 1 instead of NULL. When an operation code is NULL, the value is 0 instead of NULL.
Once the server is restarted, all 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 deleted automatically. After the restart, the heap will be cleared. At this time, the heap query results will be empty.
If heap is a replicated data table, all primary key, index, auto-increment and other formats will no longer exist after the replication. You need to re-Add the primary key and index if necessary.
There are the following solutions for data loss caused by restart:
Before any query, execute a simple query to determine whether the heap table has data. If no data exists, re-write the data or DROP the table to copy a new table. This requires more than one query. However, you can write an include file, which can be called at any time on the page that requires the heap table.
For the page on which the heap table is required, the results of the dataset are judged for the first time and only for the first time when the table is queried. If the results are empty, the data needs to be written again. This saves one query.
A better way is to automatically write data to heap every time mysql restarts. However, you need to configure the server. The process is complicated and the versatility is limited.
Small record, mutual conversion between MyISAM and InnoDB

Copy codeThe Code is as follows: // convert InnoDB to MyISAM
Alter table 'tablename' ENGINE = MYISAM
// Convert MyISAM to InnoDB
Alter table tablename type = innodb;
Alter table 'tablename' ENGINE = InnoDB