Introduction to the features and usage of MySQL memory tables

The memory table is the table in memory, the size of the memory used can be specified by the max_heap_table_size in my.cnf, such as max_heap_table_size=1024m, the memory table and the temporary table is not the same, the temporary table is stored in memory, The maximum required memory for the temporary table needs to be set by Tmp_table_size = 128M. When the data exceeds the maximum setting of the temporary table, the automatic conversion to disk table, this time because of the need for IO operation, performance will be greatly reduced, and the memory table will not, the memory table full, will prompt the data full error. Yun Ding Casino

Temporary tables and memory tables can be created manually, but temporary tables are more useful when the system is created on its own to organize data to improve performance, such as subqueries, where temporary tables cannot be shared across multiple connections. Only memory tables are discussed here

Create the table is, with ENGINE=HEAP can be created (mysql5.5 already does not support type, later all use engine, form habit).

CREATE TABLE test (ID int unsigned NOT NULL auto_increment primary key,state char (TEN), type char (), date char ()) Engine=me Mory DEFAULT Charset=utf8;

Features of memory tables

1. The table definition for the memory table is stored on disk with the extension. frm, so the reboot is not lost.
2. The data in the memory table is stored in memory, so the reboot will lose the data.
3. The memory table uses a fixed record length format.
4. The memory table does not support blob or text columns, such as varchar and text fields are not supported.
5. The memory table supports auto_increment columns and indexes on columns that can contain null values (most of the online is not supported, which is wrong). Memory table support is greater than (>) less than (<) operation, Online also said not supported.
6. After MySQL restarts, the primary key, the self-increment, the index still exist, only the data is lost. This is also a few wrong words on the web correction.
7. The Memory table table is shared among all clients (like any other non-temporary table).
8. The memory storage engine performs hash and btree indexes. You can specify one or the other for a given index by adding a using clause as follows:

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

1. Memory table initialization, you can use--init-file to initialize, to avoid restarting MySQL after the data is emptied. For example--init-file= "/data/mysql/init.sql", the Init.sql format is:

Use Db_test;select * * * into m_table;

1. When the database is copied, if the host is dropped, the delete from [memory table] is automatically added to the Binlog, and the slave data is also deleted to ensure data consistency on both sides.
2. The memory table does not support transactions.
3. The memory table is a table lock, and performance may degrade when modifications are frequent.

Use of memory tables

The memory table uses a hash index to keep the data in memory, so it has a very fast speed and is suitable for caching small to medium sized databases, but there are some restrictions on usage.

1. The heap's connection to all users is visible, which makes it ideal for caching.
2. Only suitable for use in the occasion. The heap does not allow the use of Xxxtext and XXXBLOB data types. Note: the operator "<=>" description: Null-safe equal. This operator performs the same comparison operation as the "=" operator, but when the two opcode is null, the resulting value is 1 and not null, and when an opcode is null, The resulting value is 0 and not 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 under the actual database path and is not automatically deleted. After the reboot, the heap is emptied, and the query results for the heap are empty.
4. If the heap is a replicated data table, all the primary keys, indexes, and self-increment formats will no longer exist after replication, and the primary keys and indexes need to be re-added, if necessary.
5. For data loss due to restart, there are the following workarounds:
• Before any queries, execute a simple query to determine if the heap table has data, write it back if it does not exist, or re-copy a table from the drop table. This requires more than one query. However, it is convenient to write an include file and call it at any time when the page needs to use the heap table.
• For pages that require the heap table, the dataset results are judged first and only when the table is first queried, and if the result is empty, the data needs to be re-written. This saves you one query at a time.
• A better approach is to automatically write data to the heap each time MySQL restarts, but the server needs to be configured, the process is complex and the versatility is limited.

Small records, MyISAM and InnoDB

InnoDB myisamalter table ' tablename ' ENGINE = myisam//MYISAM turn innodbalter table tablename Type=innodb; ALTER TABLE ' tablename ' ENGINE = InnoDB

Introduction to the features and usage of MySQL memory tables