Detailed explanation of MySQL storage engine

1. MySQL commonly used storage engines and features

1. InnoDB storage engine

After MySQL version 5.5, MySQL’s default built-in storage engine It is already InnoDB. Its main features are:

(1) Disaster recovery is relatively good;
(2) Supports transactions. The default transaction isolation level is repeatability, which is implemented through MVCC (Concurrent Version Control).
(3) The lock granularity used is row-level locking, which can support higher concurrency;
(4) Support foreign keys;
(5) Use some hot backup tools to support online hot backup;
(6) There is buffer management in InnoDB. Through the buffer pool, all indexes and data are cached to speed up the query;
(7) For InnoDB type tables, the physical organization form of the data is clustering. cluster table. All data is organized according to primary keys. The data and indexes are put together and are located on the leaf nodes of number B;

2. MyISAM storage engine
Before version 5.5, MyISAM was the default storage engine of MySQL. This storage engine has poor concurrency and cannot It supports transactions, so there are relatively few usage scenarios. Its main features are:

(1) Does not support transactions;
(2) Does not support foreign keys. If you forcibly add a foreign key, no error will be prompted, only foreign keys will be added. The key has no function;
(3) The query cache for data will only cache the index, not the data like InnoDB, and it uses the cache of the operating system itself;
(4) The default lock granularity is table level lock, so the concurrency is very poor, locking is fast, and there are fewer lock conflicts, so deadlocks are less likely to occur; Supported), but MySQL's full-text index is basically not used. For full-text index, there are other mature solutions, such as: ElasticSearch, Solr, Sphinx, etc.
(6) If the host where the database is located goes down, MyISAM data files are easily damaged and difficult to recover;

3. MEMORY storage engine

stores data in memory and uses Redis on the market. Memcached and others have similar ideas. In order to improve the speed of data access, the main features are:

(1) The supported data types are limited. For example, TEXT and BLOB types are not supported. For string type data, only fixed is supported. For rows of length, VARCHAR will be automatically stored as CHAR type;

(2) The supported lock granularity is table-level lock. Therefore, when the amount of access is relatively large, table-level locks will become the bottleneck of the MEMORY storage engine;
(3) Since the data is stored in memory, all data will be lost after the server is restarted;
(4 ) When querying, if a temporary table is used and there are BLOB and TEXT type fields in the temporary table, then the temporary table will be converted into a MyISAM type table, and the performance will decrease sharply;

4. ARCHIVE Storage engine

The ARCHIVE storage engine is suitable for limited scenarios. Since it supports compression, it is mainly used for archiving logs, streaming and other data. Main features:

(1) Supports Zlib compression, and the data is stored in Before inserting into the table, it will be compressed first;

(2) Only supports SELECT and INSERT operations. The stored data can only be queried, not modified or deleted;
(3) Only supports auto-increment keys. Index, other indexes are not supported;

5. CSV storage engine

Data transfer trial, main features:

(1) Its data format is text in .csv format, which can be edited directly Save;

(2) It is more convenient to import and export. You can directly export the data in a table to csv and try to open it with Excel office software;

2. Comparison between InnoDB and MyISAM

1. Due to the difference in lock granularity, InnoDB supports higher concurrency than MyISAM;

2. InnoDB has row-level locks and MyISAM has table-level locks, so InnoDB is more prone to deadlock than MyISAM. The probability of conflict is greater, and the cost of locking is also greater, because each row needs to be locked;
3. In terms of backup and disaster recovery, InnoDB supports online hot backup and has a very mature online hot backup solution;
4. In terms of query performance, the query efficiency of MyISAM is higher than that of InnoDB, because InnoDB needs to maintain the data cache during the query process, and the query process first locates the data block where the row is located, and then locates it in the data block. to the row to be searched; and MyISAM can directly locate the memory address where the data is located, and can directly find the data;
5. SELECT COUNT(*) statement, if the number of rows is more than tens of millions, MyISAM can quickly find out, InnoDB queries are particularly slow because MyISAM stores the number of rows separately, and InnoDB requires Zhu Xing to count the number of rows; so if you use InnoDB and need to query the number of rows, you need to perform special processing on the number of rows, such as: offline query And cache;
6. MyISAM’s table structure files include: .frm (table structure definition), .MYI (index), .MYD (data); while InnoDB’s table data files are: .ibd and .frm (table Structure definition);

3. How to choose the appropriate storage engine

1. Whether the usage scenario requires transaction support;

2. Whether it is necessary to support high concurrency, InnoDB’s concurrency is much higher For MyISAM;
3. Whether it is necessary to support foreign keys;
4. Whether it is necessary to support online hot backup;
5. Efficient buffering of data. InnoDB buffers both data and indexes, while MyISAM only buffers Index;
6. Index, the indexes of different storage engines are not the same;

For more MySQL related technical articles, please visit the MySQL Tutorial column to learn!

The above is the detailed content of Detailed explanation of MySQL storage engine. For more information, please follow other related articles on the PHP Chinese website!