This article mainly introduces the features of the Mysql storage engine and summarizes the features of the two most commonly used storage engines and the applicable environments of each engine. For more information, see
Features of several common storage engines
Next we will focus on several common storage engines and compare the differences and recommended usage methods between different storage engines.
Features |
Myisam |
BDB |
Memory |
InnoDB |
Archive |
Storage restrictions |
No |
No |
Yes |
64 TB |
No |
Transaction Security |
|
Supported |
|
Supported |
|
Lock mechanism |
Table lock |
Page Lock |
Table lock |
Row lock |
Row lock |
B-tree index |
Supported |
Supported |
Supported |
Supported |
|
Hash index |
|
|
Supported |
Supported |
|
Full-text index |
Supported |
|
|
|
|
Cluster index |
|
|
|
Supported |
|
Data cache |
|
|
Supported |
Supported |
|
Index cache |
Supported |
|
Supported |
Supported |
|
Data compression |
Supported |
|
|
|
Supported |
Space usage |
Low |
Low |
N/ |
High |
Very low |
Memory usage |
Low |
Low |
Moderate |
High |
Low |
Batch insert speed |
High |
High |
High |
Low |
Very high |
Supports foreign keys |
|
|
|
Supported |
|
The two most commonly used storage engines:
• Myisam is the default storage engine of Mysql. When creating a new table without specifying the storage engine for the new table, Myisam is used by default. Each MyISAM is stored as three files on the disk. The file names are the same as the table names. the extensions are. frm (storage table definition),. MYD (MYData, storage data), and. MYI (MYIndex, storage index ). Data files and index files can be placed in different directories, and I/O is evenly distributed for faster speed.
• The InnoDB storage engine provides transaction security with commit, rollback, and crash recovery capabilities. However, compared with the storage engine of Myisam, InnoDB writes less efficiently and occupies more disk space to retain data and indexes.
How to select an appropriate storage engine
Standard selection: select a suitable storage engine based on application characteristics. for complex application systems, you can select multiple storage engines for combination based on the actual situation.
The following is the applicable environment for common storage engines:
1. MyISAM: the default MySQL plug-in storage engine. it is one of the most commonly used storage engines in Web, data warehousing, and other application environments.
2. InnoDB: used for transaction processing applications. it has many features, including ACID transaction support.
3. Memory: stores all data in RAM and provides extremely fast access in environments where you need to quickly search for references and other similar data.
4. Merge: allows MySQL DBAs or developers to logically combine a series of equivalent MyISAM tables and reference them as one object. It is suitable for VLDB environments such as data warehousing.