InooDB and Memory

Data is organized differently:

  • The InnoDB engine puts data on the primary key index, and the other indexes hold the primary key ID. Organize tables for indexes
  • The Memory engine stores the data separately and stores the data location on the index. Is the heap organization table

Typical differences:

InnoDB table data is stored in order, while Memory table data is stored in order.

2. InnoDB tables are always placed on the right side when inserting new data when data files have empty Spaces. The Memory table can be inserted when it finds a space

InnoDB table changes primary key index when data location changes, Memory table needs to change index value

4. InnoDB supports variable-length data types, different records may have different lengths. Memory tables use a fixed length, with each row of the same length

Memory tables use hash indexes by default, so range queries are not supported.

alter table t1 add index a_btree_index using btree (id);
Copy the code

Memory tables are used for temporary tables

There are two problems with Memory tables:

1, Memory does not support row locks, only table locks, not suitable for concurrent scenarios2The data is stored in Memory, the database restarts, and all Memory tables are emptiedCopy the code

Benefits of Memory tables for temporary tables:

Temporary tables are not accessed by other threads. There is no concurrency problem

2. Temporary tables also need to be deleted after the restart. Clearing data is not a problem

Temporary tables in the standby database do not affect user threads in the primary database

And compared to InnoDB as a temporary table, it has the following advantages:

Write data to a temporary table faster than write data to a disk

2. Memory indexes use hash indexes, which are faster than B-tree indexes

3. For temporary tables with small data, memory footprint is limited