Features and usage of MySQL memory tables

The size of the memory used can be specified by max_heap_table_size in My.cnf, such as max_heap_table_size=1024M. The memory table is different from the temporary table, which is also stored in memory. The maximum memory required by the temporary table needs to be set by tmp_table_size = 128M. When the data exceeds the maximum value of the temporary table, it will be automatically converted to disk table. At this time, the performance will be greatly reduced due to the need for IO operation, but the memory table will not. When the memory table is full, it will prompt the data full error.

Both temporary tables and in-memory tables can be created manually, but the more important role of temporary tables is to organize the data to improve performance after the system creates it itself. For example, subqueries, temporary tables cannot be Shared between multiple connections. Only memory tables are discussed here

To create the table, engine=heap can be created (type is no longer supported in mysql5.5, but engine will be used in the future, forming a habit).

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 table definition of the memory table is stored on disk with the extension.frm, so restarts are not lost.
The data of the memory table is stored in memory, so the data will be lost on restart.
The memory table USES a fixed record length format.
Memory tables do not support BLOB or TEXT columns, such as the varchar and text fields.
In-memory tables support AUTO_INCREMENT columns and indexes on columns that can contain NULL values (most of the web says it does not, which is incorrect). Memory table support greater than ( > Less than () < ) operation, online also said not to support.
After the mysql restart, the primary key, autoincrement, and index still exist, but the data is lost. This is also on the Internet 1 some error text correction.
Memory table tables are Shared between all clients (just like any other non-TEMPORARY table).
The MEMORY storage engine performs the HASH and BTREE indexes. You can specify one or another for a given index by adding an USING clause as shown below:

CREATE TABLE lookup
(id INT, INDEX USING HASH (id))
ENGINE = MEMORY;
CREATE TABLE lookup
(id INT, INDEX USING BTREE (id))
ENGINE = MEMORY;

Memory table initialization, can be used -- init-file to initialize, to avoid the data is empty after restart mysql. For example - init - file = ". / data/mysql init sql ", init. sql format for:

use db_test;
select *** into m_table;

During database replication, if the host is down, delete from [memory table] will be automatically added to binLog and the data of slave will also be deleted to ensure the data 1 uniformity on both sides.
Memory tables do not support transactions.
Memory tables are table locks, and performance can degrade when changes occur frequently.
Memory table usage
In-memory tables use hash hash indexes to keep data in memory, so they are extremely fast and suitable for caching small and medium-sized databases, but their use is limited.

The heap is visible to all users' connections, which makes it ideal for caching.
Suitable for use only. heap does not allow the use of xxxTEXT and xxxBLOB data types. Note: operator" < = > "Note: NULL-safe equal. This operator performs the same comparison operation as the" = "operator, 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.
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 under the actual database path and will not be automatically deleted. After restarting, heap will be cleared, and the query results for heap will be empty.
If heap is a replicated table of data, all primary keys, indexes, self-incrementing, and other formats will no longer exist after replication, and primary keys and indexes will need to be added again, if necessary.
For data loss caused by restart, there are the following solutions:
Before any query, perform a simple query to determine whether the heap table has data, and if it does not, re-write the data, or re-copy a table from the DROP table. This requires an additional query. However, it can be written as an include file, which can be easily called at any time when the heap table is needed.
For pages that require the heap table, the data set result is judged the first time and only the first time the table is queried on that page, and if the result is empty, the data needs to be rewritten. This saves one query.
A better approach would be to automatically write data to heap each time mysql is restarted, but the server needs to be configured and the process is complex and limited in generality.
Small record, MyISAM and InnoDB interchangeably

// InnoDB turn MyISAM
ALTER TABLE `tablename` ENGINE = MYISAM
// MyISAM turn InnoDB
alter table tablename type=innodb;
ALTER TABLE `tablename` ENGINE = InnoDB