MySQL Memory table Basic Learning Tutorials _c language

The memory table is a 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 is not the same as the temporary table, the temporary table is stored in memory, The maximum required memory for a temporary table needs to be set by Tmp_table_size = 128M. When the data exceeds the maximum set of the temporary table, automatically into the disk table, at this time because of the need for IO operation, performance will be greatly reduced, and the memory table will not be full of memory, will prompt data full error.

Temporary tables and memory tables can be created manually, but temporary tables are more useful when the system creates the data to improve performance, such as subqueries, where temporary tables are not shared between multiple connections. Only the memory table is discussed here

Create a table that can be created with engine=heap (type is not supported in mysql5.5, and then used engine to form a habit).

CREATE TABLE Test
(
 ID int unsigned NOT NULL Auto_increment primary key, state
 char (),
 type char (), 
   date char ()
) engine=memory DEFAULT Charset=utf8;

Features of the memory table

1. For varchar and other variable-length types, the memory table using a fixed length to store;

2. The memory table can have a non unique key;

3. The memory table cannot contain blobs or text columns;

4. The memory table supports auto_increment columns;

5. The memory table supports insertion delay, so that reading takes precedence;

6. Non-temporary memory table and other non-memory tables are shared directly on all clients;

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

1. Server memory is large enough;

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

The data in the memory table is stored in memory, while the internal temporary table (generated by our query statement) is stored in memory at the right time, when the internal temporary table becomes large, MySQL automatically converts it into a table stored on disk, and the memory table we create is not automatically converted.

3. When we separate delete from a memory table, memory is not reclaimed, memory is reclaimed only when the whole table is delete;

4. On the MySQL master-slave server, the memory table can be copied

Some restrictions on the use of memory tables

The MySQL memory table uses hash indexes to keep the data in memory, so it is extremely fast and suitable for caching small and medium-sized databases, but with some limitations on usage.

1, heap to all users of the connection is visible, which makes it very suitable for caching.

2, only suitable for use of the occasion. Heap does not allow the use of Xxxtext and xxxblob data types; only the = and <=> operators are allowed to search for records (& lt;, >, <=, or >=) are not allowed; Auto_increment is not supported ; only non-empty data columns are allowed to be indexed (not NULL).
Note: operator "<=>" description: Null-safe equal. This operator performs the same comparison operation as the "=" operator, but when the two opcode is null, the value is 1 and not NULL, and when one opcode is null, The resulting value is 0 instead of NULL.

3. Once the server restarts, 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 is not automatically deleted. After the reboot, the heap will be emptied, and the query result for heap is empty.

4, if the heap is a replicated data table, then all primary keys, indexes, and so on after the copy of the format will no longer exist, you need to add the primary key and index, if necessary.

5, for the restart caused by data loss, there are the following solutions:
A, before any query, perform a simple query to determine if there is data in the heap table and, if not, to write the data back, or to copy a table back to the drop table. This requires one more query. However, can be written as include file, in need of the heap table page at any time to call, more convenient.
b, for pages that require the heap table, the dataset results are judged for the first time on the page and only the first time the table is queried, and if the result is empty, the data will need to be rewritten. This saves you the query one time.
C, a better way is to automatically write data to heap every time MySQL restarts, but the server needs to be configured, the process is more complex and the versatility is limited.
Blue Grass is currently using the second approach.

6. Some SQL statements that are expected to be used

If the table exists, delete the
drop table if EXISTS ' abc ';
Copy entire table xyz to heap table ABC (contains all data)
CREATE table ' abc ' TYPE=HEAP SELECT * from ' XYZ ';
Add primary Key ID
ALTER TABLE ' abc ' Add PRIMARY key (' id ');
Add index username
ALTER TABLE ' abc ' Add index ' abc ' (' username ');