Shulou(Shulou.com)06/01 Report--

See the official document https://dev.mysql.com/doc/refman/5.7/en/memory-storage-engine.html

Summary

1. The memory engine table data is only stored in memory. After inserting the data, the file only has the table structure file, but there is no table data file. After restart, the data is lost, but the table structure is still there and the table structure file is still there.

2. Memory engine table. One session modifies the data, and other sessions can see the modified data immediately, which is completely different from the temporary table of oracle.

3. To clear the memory engine table, delete, truncate, drop or restart can be used. There is no way for flush buffer_cache like oracle to directly empty memory.

4. The maximum size of the memory engine table is limited by the parameter max_heap_table_size. For more information, please see the official documentation.

Https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_heap_table_size

5. That is to say, the table of the momory engine, except for these two points, the table data is put in memory and lost after restart, everything else is the same as the normal table.

6. The memory engine is not recommended in the production environment because it has two biggest disadvantages. One is that it has only table locks and no row locks, so that once the table is updated, it will block other sessions' reading and writing to the table. Second, its data is stored in memory, once in MmurS architecture, S slave database restarts, S slave database data will be lost, but M master database data is still there, and then affect master-slave synchronization, because if a update statement is received after restart, the master library executes normally, sending the statement to the slave database will report an error and cannot find the updated row, resulting in master-slave synchronization stopped.

7. If you have to use the advantages of the memory engine and store the data in memory, you can consider using the memory engine temporary table, which can avoid the two disadvantages of the above 6.

Experimental process

Mysql > use test1

Mysql > create table tab3 (hid int) engine memory

Mysql > insert into tab3 values (1)

Mysql > insert into tab3 values (1)

Mysql > insert into tab3 values (1)

Mysql > select count (*) from tab3

+-+

| | count (*) |

+-+

| | 3 |

+-+

At this point, looking at the data file of tab3, it is found that there is only table structure.

[root@mydb ~] # ll / var/lib/mysql/test1 | grep tab3

-rw-r- 1 mysql mysql 8558 Sep 30 11:00 tab3.frm

Open another session

Mysql > select count (*) from test1.tab3

+-+

| | count (*) |

+-+

| | 3 |

+-+

Restart the database and view the tab3 data

Mysql > select count (*) from test1.tab3

+-+

| | count (*) |

+-+

| | 0 |

+-+

At this point, look at the data file of tab3, and the table structure is still in

[root@mydb ~] # ll / var/lib/mysql/test1 | grep tab3

-rw-r- 1 mysql mysql 8558 Sep 30 11:00 tab3.frm

Session 1

Mysql > create table T1 (hid int)

Mysql > create table T2 (hid int) engine=memory

Mysql > insert into T1 values (1), (2), (3)

Mysql > insert into T2 values (1), (2), (3)

Mysql > update T1 set hid=sleep (60) where hid=1

Session 2

Mysql > select * from T1

+-+

| | hid |

+-+

| | 1 |

| | 2 |

| | 3 |

+-+

Normal execution, no blockage

Session 1

Mysql > update T2 set hid=sleep (60) where hid=1

Session 2

Mysql > select * from T2

-- blocking until session 1 finishes execution

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.