sequence

This article introduces several storage engines in MySQL and their usage scenarios and differences. There will be a separate article to sort out and summarize the most commonly used InnoDB storage engine.

Pluggable storage engine

InnoDB

Starting with MySQL database version 5.5.8, InnoDB storage engine is the default storage engine.

scenario

Mainly for online transaction processing (OLTP) applications.

features

  • Support transactions
  • Row lock design
  • Support foreign keys
  • It also supports non-locking reads similar to Oracle, that is, the default read operation does not generate locks.
  • Insert the buffer
  • The second to write
  • Adaptive hash index
  • The proofs

About transactions and storage

InnoDB achieves high concurrency by using multi-version Concurrency Control (MVCC) and implements 4 isolation levels of SQL standard with REPEATABLE level by default. Meanwhile, a strategy called next-key locking is used to avoid phantom.

The InnoDB storage engine clustered tables so that each table is stored in primary key order. If the primary key is not explicitly specified at table definition, the InnoDB storage engine generates a 6-byte ROWID for each row and uses it as the primary key. The InnoDB storage engine puts data into a logical table space, which is managed like a black box by the InnoDB storage engine itself.

MyISAM

scenario

Mainly for some OLAP database applications.

features

  • Transactions not supported
  • Table lock design
  • Full text index support

About the storage

MyISAM storage engine table consists of MYD, which is used to store data files, and MYI, which is used to store index files. You can further compress the data file by using the MyisamPack tool, because the myisampack tool uses Huffman encoded static algorithms to compress the data, so the compressed table using the MyisamPack tool is read-only, Of course, users can also extract data files through Myisampack. Its buffer pool caches only index files, not data files, which are cached by the operating system itself, unlike most other databases that use the LRU algorithm to cache data. Therefore, when the table of MyISAM storage engine does disk file sorting, it depends on the system call of the operating system for data reading, which will cost more

Memory

scenario

It is ideal for temporary tables for storing temporary data, as well as latitude tables in data warehouses.

features

  • It is very fast to keep the data in the table in memory, but if the database restarts or crashes, the data in the table will disappear.
  • Hash indexes are used by default
  • Only table locks are supported and the concurrency performance is poor
  • TEXT and BLOB column types are not supported
  • Varium-length fields (VARCHar) are stored as invariant fields (CHAR), thus wasting memory.

other

The MySQL database uses the Memory storage engine as a temporary table to hold the intermediate result set of the query. If the intermediate result set is larger than the capacity set of the Memory storage engine table, or if the intermediate result contains TEXT or BLOB column type fields, the MySQL database converts the intermediate result to the MyISAM storage engine table and stores it on disk.

Archive

scenario

Provides high-speed insertion and compression functions. Ideal for storing archived data, such as log information.

features

  • Only INSERT and SELECT operations are supported
  • Indexes are supported as of MySQL5.1.

other

The Archive storage engine uses the Zlib algorithm to compress and store rows, with a compression ratio of 1:10. The Archive storage engine uses row locking for highly concurrent insert operations, but it is not a transaction-safe storage engine by itself

CSV

CSV storage engines can treat CSV files as mysql tables. Indexes are not supported, and the storage format is a normal CSV file. All columns must not be NULL

Fedrated

A Federated storage engine table does not hold data, but points to a table on a remote MySQL database server. This is very similar to SQL Server’s linked Server and Oracle’s transparent gateway, except that the current Federated storage engine only supports MySQL database tables and does not support heterogeneous database tables.

other

Simple lists are no progress in writing articles. Most of the storage engine introduction in this article comes from the book inside MySQL Technology: InnoDB Storage Engine

If you find this article helpful, please leave a red like before you go, or browse the author’s other articles if you are interested. If you think this article is a waste of your time, please leave your comments in the comments section. If you have any questions, please leave a message. The author is willing to spend time and energy to find answers and discuss them together.