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

This article mainly explains the "MySQL database to create memory tables what are the limitations", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in-depth, together to study and learn "MySQL database to create memory tables what are the restrictions" it!

When the data in the memory table is larger than the capacity set by max_heap_table_size, mysql will convert the excess data to disk, so the performance is greatly reduced, so we also need to adjust the max_heap_table_size according to our actual situation, for example, add: max_heap_table_size=2048M under [mysqld] in the .cnf file. In addition, the number of records in the table can be controlled by MAX_ROWS in the table statement.

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.

What are the restrictions on creating memory tables in MySQL database

1. Heap is visible to all users' connections, which makes it very suitable for caching.

2. Only suitable for use. Heap does not allow the use of xxxTEXT and xxxBLOB data types; only the = and operators are allowed to search records (not allowed, =); auto_increment; is not supported and only allows indexing of non-empty data columns (notnull).

Note: operator "" description: NULL-safeequal. 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.

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 in the actual database path and will not be deleted automatically. After the restart, the heap will be emptied, and the query results for heap will be empty.

4. 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.

5. For data loss caused by restart, there are the following solutions:

Before any query, execute a simple query to determine whether there is data 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.

B. For the page that needs the heap table, the result of the dataset is judged when the page queries the table for the first time and only for the first time. If the result is empty, the data needs to be rewritten. This saves a query.

C, a better way is to automatically write data to mysql every time it restarts, but you need to configure the server, which is complicated and limited in versatility.

Bluegrass is currently using the second method.

6. Some sql statements that may be used in anticipation

/ / if the table exists, delete DROPTABLEIFEXISTS`abc`; / / copy the whole table xyz to heap table abc (including all data) CREATETABLE`abc` invalid = heapselect* from `xyz`; / / add primary key idALTABLE`abc`ADDPRIMARYKEY (`id`); / / add index usernameALTERTABLE`abc`ADDINDEX`abc` (`username`)

7. Create a table example

CREATETABLE`DB` (`id`int (11) defaultNULL, `songname`varchar (255) NOTNULLdefault'', `singer`varchar (255) NOTNULLdefault'', KEY`songname` (`songname`, `singer`))

Invalid = invalid HEAP when creating a table TABLE invalid option also has this table structure is to create a memory table. If MYSQL restarts, the data in the memory table will disappear. But the access speed will be much faster!

Thank you for your reading, the above is the content of "what are the restrictions on creating memory tables in MySQL database". After the study of this article, I believe you have a deeper understanding of the limitations of creating memory tables in MySQL databases, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.