A brief introduction to MySQL storage engine

1. MySql Architecture

Before introducing the storage engine, let's first introduce the MySQL architecture so that everyone knows where the storage engine occupies in the entire MySQL system. The following figure is an official architecture diagram:

MySQL architecture diagram

As can be seen from the above figure, MySQL consists of the following parts:

• Connection pool components
• Management Services and Tools Components
• SQL interface components
• Query Analyzer Components
• Optimizer components
• Cache component
• Plug-in storage engine
• Physical Files
  

The most important feature that distinguishes MySQL database from other databases is its plug-in table storage engine. As can be seen from the above figure, MySql supports many types of storage engines. It is important to note that storage engines are based on tables, not databases.

2. MySql storage engine

The advantage of the plug-in storage engine is that different storage engines can be selected according to the characteristics of specific applications. The following are several commonly used storage engines for MySQL.

2.1 InnoDB Storage Engine

The InnoDB storage engine supports transactions and is designed primarily for online transaction processing (OLTP) applications. Its features include row lock design, foreign key support, and non-locking read support similar to Oracle, that is, the default read operation will not generate locks. Starting from MySQL database version 5.5.8, InnoDB storage engine is the default storage engine.

InnoDB achieves high concurrency by using multi-version concurrency control (MVCC) and implements four SQL standard isolation levels, with the default being the REPEATABLE level. At the same time, a strategy called next-key locking is used to avoid phantom reading. In addition, the InnoDB storage engine also provides high-performance and high-availability features such as insert buffer, double write, adaptive hash index, and read ahead.

For the storage of data in the table, the InnoDB storage engine uses a clustered approach, so the storage of each table is stored in the order of the primary key. If you do not explicitly specify a primary key when defining a table, the InnoDB storage engine generates a 6-byte ROWID for each row and uses it as the primary key.

2.2 MyISAM Storage Engine

The MyISAM storage engine does not support transactions or table lock design, but supports full-text indexing and is mainly aimed at some OLAP database applications. In addition, another unique feature of the MyISAM storage engine is that its buffer pool only caches index files, not data files, which is very different from most databases. Starting from MySQL 5.0, MyISAM supports 256TB of single-table data by default, which is sufficient to meet general application requirements.

2.3 Memory Storage Engine

The Memory storage engine (formerly known as the HEAP storage engine) stores table data in memory. If the database is restarted or crashes, the data in the table will disappear. It is well suited for temporary tables that store temporary data, as well as latitude tables in data warehouses. The Memory storage engine uses hash indexes by default instead of the familiar B+ tree indexes.

Although the Memory storage engine is very fast, it still has certain limitations in use. For example, it only supports table locks, has poor concurrency performance, and does not support TEXT and BLOB column types. Most importantly, variable-length fields (varchar) are stored in the same way as fixed-length fields (char), which wastes memory.

In addition, one thing that is easily overlooked is that the MySQL database uses the Memory storage engine as a temporary table to store the intermediate result set of the query. If the intermediate result set is larger than the capacity setting of the Memory storage engine table, or if the intermediate result contains TEXT or BLOB column type fields, the MySQL database will convert it to a MyISAM storage engine table and store it on disk. As mentioned before, MyISAM does not cache data files, so the performance of the temporary table generated at this time will be lost for queries.

2.4 Archive Storage Engine

The Archive storage engine supports only INSERT and SELECT operations and supports indexes starting from MySQL 5.1. The Archive storage engine uses the zlib algorithm to compress data rows for storage, with a compression ratio of up to 1:10. As its name suggests, the Archive storage engine is well suited for storing archived data, such as log information. The Archive storage engine uses row locks to implement high-concurrency insert operations, but it is not a transaction-safe storage engine. Its design goal is mainly to provide high-speed insert and compression functions.

Of course, MySql also supports many other storage engines, which are not listed here one by one.

3. Comparison of storage engines

The storage engine can be understood as the storage structure of the table, and each storage engine supports different features. MySQL supports plug-in storage engines, and you can specify a different storage engine for each data table. The characteristics of commonly used storage engines are as follows:

We can also use the following command to see which storage engines the current database supports:

-- View the supported storage engines show engines;

The following is a brief summary of the three most commonly used storage engines:

• InnoDB: The default storage engine of MySQL, which supports transactions, row-level locks and table-level locks, various indexes, and foreign keys. Higher versions of MySQL also support full-text indexes, but the efficiency of batch data insertion is low.
• MyISAM: It has high data insertion efficiency and data query speed, supports full-text indexing, but does not support database transactions or row-level locks, and only supports table-level locks.
• MEMORY: When using this storage engine, the data in the table will be loaded into the memory, and the query is very fast, but the memory requirement is high.
  

Therefore, we should choose the appropriate storage engine according to the specific needs of the application, rather than blindly choosing the default storage engine (INNODB).

If you want to provide transaction safety (ACID compliance) capabilities for commit, rollback, and recovery, and require concurrency control, InnoDB is a good choice. If the data table is mainly used to insert and query records, the MyISAM engine provides higher processing efficiency. If you only need to temporarily store data, the amount of data is not large, and high data security is not required, you can choose to save the data in the MEMORY engine in memory. MySQL uses this engine as a temporary table to store the intermediate results of the query. If there are only INSERT and SELECT operations, you can choose the Archive engine. The Archive storage engine supports high-concurrency insert operations, but it is not transaction-safe. The Archive storage engine is very suitable for storing archived data. For example, the Archive engine can be used to record log information.

4. References

MySQL Technical Insider

The above is a brief introduction to the details of the MySQL storage engine. For more information about the MySQL storage engine, please pay attention to other related articles on 123WORDPRESS.COM!