Shulou(Shulou.com)05/31 Report--

This article mainly explains "the characteristics and usage of MySQL database memory table". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn the characteristics and usage of MySQL database memory tables.

Memory tables, that is, tables placed in memory, the size of memory used can be specified by max_heap_table_size in My.cnf, such as max_heap_table_size=1024M, memory tables are not the same as temporary tables, temporary tables are also stored in memory, and the maximum memory required for temporary tables needs to be set by tmp_table_size = 128m. When the data exceeds the maximum value of the temporary table, it is automatically converted to a disk table. Due to the need for IO operation, the performance will be greatly degraded, but the memory table will not. When the memory table is full, it will prompt that the data is full of errors.

Both temporary tables and memory tables can be created manually, but temporary tables play a more important role in organizing data to improve performance after being created by the system itself. for example, temporary tables cannot be shared among multiple connections, such as subqueries. Only memory tables are discussed here.

To create a table, you can create it with engine=heap (type is no longer supported in mysql5.5, and engine will be used later, 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;CREATE TABLE lookup (id INT, INDEX USING HASH (id)) ENGINE=MEMORY; CREATE TABLE lookup (id INT, INDEX USING BTREE (id)) ENGINE=MEMORY

The memory table can be initialized using-- init-file to avoid emptying the data after restarting mysql. For example-- init-file= "/ data/mysql/init.sql", the init.sql format is:

Use db_test;select * into m_table

In database replication, if the host pawns, the delete from [memory table] will be automatically added to the binLog, and the data of the slave will also be deleted to ensure data consistency on both sides.

Memory tables do not support transactions.

Memory tables are table locks, and performance may degrade when frequent modifications are made.

The use of memory tables

Memory tables use hash indexes to keep data in memory, so they are very fast and suitable for caching small and medium-sized databases, but there are some restrictions on their use.

Heap's connections are visible to all users, which makes it ideal for caching.

Suitable for use only. Heap does not allow the use of xxxTEXT and xxxBLOB data types. Note: operator "" description: NULL-safe equal. This operator performs the same comparison as the "=" operator, but when both opcodes are NULL, the resulting value is 1 instead of NULL, and when one opcode is NULL, it is 0 instead of NULL.

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

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 will need to be re-added, if necessary.

For data loss caused by reboot, there are the following solutions:

Before any query, execute a simple query to determine whether data exists in the heap table, and if not, rewrite the data, or the DROP table replicates a table again. This requires one more query. However, it can be written as an include file, which can be called at any time on the page that needs to use the heap table.

For pages that require the heap table, the dataset result is judged the first time and only when the table is queried on the page, and if the result is empty, the data needs to be rewritten. This saves a query.

A better approach is to automatically write data to mysql each time it restarts, but you need to configure the server, which is complex and limited in versatility.

Small record, MyISAM and InnoDB convert each other:

/ / InnoDB to MyISAM

ALTER TABLE `tablename` ENGINE = MYISAM

/ / MyISAM to InnoDB

Alter table tablename type=innodb

ALTER TABLE `tablename` ENGINE = InnoDB

At this point, I believe that everyone on the "MySQL database memory table characteristics and usage" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.