Considerations when creating a memory table in a MySQL database

MySQL databaseCreateMemory tableThe method is very simple. You only need to specify ENGINE = MEMORY:

 
 

  
  
CREATE TABLE  `tablename` ( `columnName` varchar(256) NOT NUL)   
  
  
 
  
  
ENGINE=MEMORY DEFAULT CHARSET=latin1   
  
  
 
  
  
MAX_ROWS=100000000;  
 
 

Note:

When the data in the memory table is larger than the size set by max_heap_table_size, mysql converts the data that exceeds the size and stores the data on the disk. Therefore, the performance is compromised, so we also need to adjust max_heap_table_size according to our actual situation, for example, in. in the cnf file [mysqld], add max_heap_table_size = 2048 M. In addition, you can use MAX_ROWS to control the number of table records in the table creation statement.

Memory tables use Hash hash indexes to store data in the memory. Therefore, it is extremely fast and suitable for caching Small and Medium databases. However, the usage is limited. The following are some of the feelings about the usage of the Blue Grass.

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

2. It is only suitable for use. Heap does not allow xxxTEXT and xxxBLOB data types. Only the = and <=> operators are allowed to search records. <,>, <= or> =) auto_increment is not supported; only non-null data columns can be indexed ).

Note: The operator "<=>" description: NULL-safe equal. this operator and the "=" operator perform the same comparison operation. However, when both operation codes are NULL, the obtained value is 1 instead of NULL. When an operation code is NULL, the value is 0 instead of NULL.

3. 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 in the actual database path and will not be automatically deleted. After the restart, the heap will be cleared. At this time, the heap query results will be empty.

4. If heap is a replicated data table, all primary key, index, auto-increment and other formats will no longer exist after the replication. You need to re-Add the primary key and index if necessary.

5. There are the following solutions for data loss caused by restart:

A. Before any query, run a simple query to check whether the heap table has data. If no data exists, write the data again, or DROP the table to copy a table again. This requires more than one query. However, you can write an include file, which can be called at any time on the page that requires the heap table.

B. For the page on which the heap table is to be queried, the result of the dataset is judged for the first time and only for the first time. If the result is empty, the data needs to be written again. This saves one query.

C. A better way is to automatically write data to the heap every time mysql restarts. However, you need to configure the server. The process is complicated and the versatility is limited.

Currently, Blue Grass uses the second method.

6. Some SQL statements that may be used as expected

 
 

  
  
// Delete a table if it exists
  
  
 
  
  
Drop table if exists 'abc ';
  
  
 
  
  
// Copy the entire table xyz. The heap table abc contains all the data)
  
  
 
  
  
Invalid create table 'abc' = heap select * from 'xyz ';
  
  
 
  
  
// Add the primary key id
  
  
 
  
  
Alter table 'abc' add primary key ('id ');
  
  
 
  
  
// Add the index username
  
  
 
  
  
Alter table 'abc' add index 'abc' ('username ');
 
 

7. Create a table instance

 
 

  
  
CREATE TABLE `DB` (   
  
  
 
  
  
`id` int(11) default NULL,   
  
  
 
  
  
`songname` varchar(255) NOT NULL default '',   
  
  
 
  
  
`singer` varchar(255) NOT NULL default '',   
  
  
 
  
  
KEY `songname` (`songname`,`singer`)   
  
  
 
  
  
)  
 
 

Invalid = the TABLE is invalid when HEAP creates a TABLE. This TABLE structure is used to create a memory TABLE. If MYSQL is restarted, the data in the memory table will disappear. But the access speed will be much faster!

This section describes how to create a memory table in a MySQL database.