Comparison of features of three common storage engines in MySQL database

Comparison of features of three common storage engines in MySQL database

The storage engine of MySQL may be the most distinctive of all relational database products, not only can multiple storage engines be used at the same time, in addition, plug-ins are used between each storage engine and MySQL.

Because the features of various storage engines vary greatly, this article mainly describes how to select an appropriate storage engine to deal with different business scenarios.

MyISAM

Features
Transactions not supported: MyISAM storage engine does not support transactions. Therefore, business scenarios requiring transactions cannot be used.
Table-level locking: the locking mechanism is table-level indexing, which can reduce the implementation cost of locking but also greatly reduce its concurrency performance.
Read/write blocking: not only does MyISAM block write during write, but the read itself does not block other reads.
Only cache indexes: MyISAM can use the key_buffer cache to greatly improve access performance and reduce disk I/O. However, this cache area only caches indexes instead of data.

Applicable scenarios
No transaction support (not supported)
Relatively low concurrency (locking mechanism problems)
Relatively few data changes (blocking problem)
Read-oriented
Data Consistency requirements are not very high

Best practices
Index as much as possible (Cache Mechanism)
Adjust the read/write priority to ensure that important operations are given priority based on actual needs.
Enable delayed insertion to improve Writing Performance in large batches
Try to write the insert data to the end in sequence to reduce blocking.
Break down large operations to reduce the blocking time of a single operation
Reduce the number of concurrency. In some high-concurrency scenarios, queuing mechanism is implemented through applications.
Making full use of Query Cache can greatly improve access efficiency for relatively static data.
MyISAM's Count is especially efficient only when scanning the whole table. Actual data access is required for the count with other conditions.

InnoDB

Features
Good transaction support: supports four transaction isolation levels and multi-version read
Row-Level Lock: The full table scan will still be a table Lock Based on the index. Pay attention to the impact of the GAP lock.
Read/write blocking is related to the transaction isolation level.
Highly Efficient Caching: data can be cached as well as indexes.
The entire table and primary key are stored in clusters to form a Balance Tree.
All Secondary indexes Save the primary key information.

Applicable scenarios
Requires transaction support (with good transaction features)
Row-level locking can adapt to high concurrency, but it is necessary to ensure that the query is completed through the index.
Scenarios with frequent data updates
High data consistency requirements
The memory size of the hardware device is large. The good cache capability of InnoDB can be used to improve memory utilization and minimize disk IO.

Best practices
The primary key should be as small as possible to avoid extra space burden on the Secondary index.
Avoid full table scan because table locks are used.
Cache all indexes and data as much as possible to improve response speed
When large volumes of small inserts, try to control the transaction by yourself instead of using autocommit for automatic commit.
Reasonably set the innodb_flush_log_at_trx_commit parameter value and do not excessively pursue security.
Avoid Primary Key Update, because this will bring a lot of data movement

NDBCluster

Features
Distributed: the distributed storage engine. It can be composed of multiple NDBCluster storage engines, which respectively store part of the overall data.
Transactions supported: Like Innodb, transactions supported
It can be different from mysqld on one host: It can be separated from mysqld on an independent host, and then communicate with mysqld over the network.
Memory demand is huge: new versions of indexes and indexed data must be stored in memory, and all data and indexes of old versions must exist in memory

Applicable scenarios
High concurrency requirements
The response to a single request is not very critical
The query is simple and the filtering conditions are relatively fixed. Each request has a small amount of data and you do not want to perform horizontal Sharding on your own.

Best practices
Make the query as simple as possible to avoid data transmission across nodes
To meet the computing performance of SQL nodes as much as possible. A larger cluster SQL node will obviously have redundant Data nodes.
Use the 10-ge network environment to connect nodes to reduce the latency of data transmission at the network layer.

Note: The above three storage engines are currently the mainstream storage engines, and other storage engines such as Memory, Merge, CSV, and Archive are rarely used, here we will not analyze them one by one.

Articles you may be interested in:

• Analysis of MySQL Memory storage engine
• Advantages and disadvantages of InnoDB and MyISAM for MySQL storage engine Selection
• Mysql5.5 InnoDB Storage engine configuration and Optimization
• Differences between MySQL storage engine InnoDB and MyISAM
• How to convert MySQL database MyISAM storage engine to Innodb
• How to modify the data table storage engine in MySQL
• Differences between MySQL storage engine MyISAM and InnoDB
• MySQL storage engine Summary