Knowledge about MySQL Memory storage engine

Knowledge points about Memory storage engine

The Memory storage engine is rarely used in daily work, but in some MySQL syntax, the memory table of the memory engine is used. It has the following characteristics:

1. The syntax for creating a memory table is create table … engine=memory.

2. The data of this table is stored in the memory and will be cleared when the system restarts, but the table structure still exists.

2. The data and index of the Memory storage engine are separate. Tables of the memory storage engine can also have primary keys. The primary key id stores the location of each data. The primary key id is a hash index, and the keys on the index are not continuous.

This data organization form where data and indexes are stored separately is called a "heap-organized table", which is different from the "index-organized table" of the Innodb storage engine.

The difference between Innodb and memory storage engines

1. The data storage order of the Memory storage engine is the same as the insertion order, while the data storage order of the InnoDB storage engine is arranged in order according to the clustered index. Here are some examples:

mysql> create table t1(id int primary key, c int) engine=Memory;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0

mysql> select * from t1;
+----+------+
| id | c |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
| 0 | 0 |
+----+------+
10 rows in set (0.00 sec)


mysql> create table t2(id int primary key, c int) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t2 values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0


mysql> select * from t2;
+----+------+
| id | c |
+----+------+
| 0 | 0 |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
+----+------+
10 rows in set (0.00 sec)

It can be seen that in the results of the memory storage engine, the record (0,0) is at the end, which is consistent with the insertion order, while in the results of the innodb storage engine, they are arranged in the order of the records.

2. When there is a hole in the data file, the InnoDB storage engine needs to insert data at the specified location, while the Memory storage engine can insert data as long as it finds a gap.

3. When the data location changes, the memory storage engine needs to modify all indexes, while the innodb storage engine only needs to modify the primary key index.

4. The innodb storage engine has a "table return" when querying, while the memory storage engine does not need a table return.

5. The innodb storage engine supports varchar, but the memory storage engine does not. All varchars are treated as chars. In addition, the memory storage engine does not support blob and text fields.

6. The memory storage engine cannot support range queries. Because its index type is a hash index

In production environments, it is generally not recommended to use memory tables for the following two reasons:

1. Memory tables do not support row locks, but only table locks. Once the table is updated, other operations on the table, including read operations, will be blocked. This means that the concurrency performance of such tables is poor.

2. Data persistence is poor. Once the database crashes, the data in the memory table will be lost. He will forget to write a delete from table statement to the binlog, but the table structure still exists. In a dual-M environment, this delete statement may pollute the temporary table data of the master database if the slave database crashes, which is a relatively dangerous operation.

The above is the detailed content about the MySQL Memory storage engine. For more information about the MySQL Memory storage engine, please pay attention to other related articles on 123WORDPRESS.COM!