MySQL storage engine comparison

MySQL's common storage engines are MyISAM, InnoDB, MEMORY, and MERGE. InnoDB provides transaction security tables, and other storage engines are non-transaction security tables. MyISAM is the default storage engine of MySQL. MyISAM does not support transactions or foreign keys, but it has fast access speed and has no requirements on transaction integrity. The InnoDB Storage engine provides,

MySQL's common storage engines are MyISAM, InnoDB, MEMORY, and MERGE. InnoDB provides transaction security tables, and other storage engines are non-transaction security tables. MyISAM is the default storage engine of MySQL. MyISAM does not support transactions or foreign keys, but it has fast access speed and has no requirements on transaction integrity. The InnoDB Storage engine provides,

MySQL's common storage engines are MyISAM, InnoDB, MEMORY, and MERGE. InnoDB provides transaction security tables, and other storage engines are non-transaction security tables.

MyISAM is the default storage engine of MySQL. MyISAM does not support transactions or foreign keys, but it has fast access speed and has no requirements on transaction integrity.

The InnoDB Storage engine provides transaction security with commit, rollback, and crash recovery capabilities. However, compared with the MyISAM storage engine, InnoDB writes less efficiently and occupies more disk space to retain data and indexes.

The MEMORY storage engine uses the content in MEMORY to create tables. Each MEMORY table corresponds to only one disk file. MEMORY tables have fast access because their data is stored in the MEMORY and the HASH index is used by default. However, once the service is disabled, data in the table will be lost.

The MERGE storage engine is a combination of MyISAM tables, which must have the same structure. The MERGE table itself does not have data. The internal MyISAM table is used to query, update, and delete tables of the MERGE type.

MyISAM tables also support different storage formats in 3:
1 static table
2. Dynamic table
3. compress the table
Static tables use the default storage format. Fields in static tables are not variable-length fields. The advantage is that they are stored very quickly and easy to cache, and are easy to recover due to faults. The disadvantage is: it usually occupies more space than dynamic tables. (Note: When the column width is insufficient during storage, spaces are used to fill up the space. At that time, these spaces are not obtained during access)
Fields in a dynamic table become longer. The advantage is that the occupied space is relatively small, but frequent update and deletion of records results in fragments, and performance needs to be improved on a regular basis, in addition, it is relatively difficult to recover when a fault occurs.
The compressed table occupies a small amount of disk space, and each record is compressed separately. Therefore, there is only a small access expense.

MySQL only supports the InnoDB external key storage engine. When creating a foreign key, you must have an index in the Appendix. When creating a foreign key for a sub-table, the corresponding index is automatically created.
There are two InnoDB Storage Methods: 1 using shared tablespace storage 2 using multiple tablespaces

The MEMORY storage engine is mainly used for code tables with less frequent changes in content, or as an intermediate result table for statistical operations. This allows you to efficiently analyze intermediate results and obtain the final statistical results. Exercise caution when updating MEMORY storage engine tables because the data is not actually written to the disk, therefore, you must consider how to obtain the modified data after the next restart of the service.

MERGE is used to combine a series of equivalent MyISAM tables in a logical manner and reference it as an object. The advantage of the MERGE table is that it can break through the limit on the size of a single MyISAM table. By distributing different tables on multiple disks, the access efficiency of the MERGE table can be effectively improved.

Another article :-)

During this time, I read High Performance MySQL and found that the storage engine is unfamiliar with many details.

To adapt to a variety of runtime environments, MYSQL provides a variety of Storage engines, at the application development level, developers can choose the appropriate Storage Engine solution based on their needs. more flexibly, you can choose different Storage engines based on the characteristics of the data to be stored in each table. That is to say, in a MYSQL database, different Storage engines can be used together.

First, let's take a look at the MySQL architecture. At the highest abstraction layer, you can use Garlan & Shaw's layered structure system to represent (left)

The application layer provides user interfaces for all RDBMS users. The logic layer includes the implementation of all core functions, and the physical layer stores data on hardware devices.

The right of the figure details the composition of the logic layer. The query processing subsystem, transaction management subsystem, recovery management subsystem, and storage management subsystem constitute the logic layer of MySQL. It is believed that the Storage Engine is located in the Storage Management, that is, the Storage Engine is part of the Storage Management subsystem.

In order to make the idea clearer, the following provides a comprehensive architecture diagram (or, more specifically, a flowchart, just ignoring the feedback)

The above three figures are from an unofficial (not guaranteed to be correct) MySQL architecture Report, which is similar to the MySQL general structure (, basically corresponds to the Logic Layer. from the right side of the first figure, we can see that the MySQL logic layer also follows the hierarchical architecture.

The interfaces between Layer 2 and Layer 3 in a connection are not a single API for any storage engine. They consist of 20 basic functions such as "Start transaction, return result set. The storage engine does not process SQL statements and does not communicate with each other. Their tasks simply respond to requests sent from higher layers.

Storage Engine Features

The four storage engines mentioned above have their own applicable environments, depending on some of their unique features. Mainly reflected in the performance, transactions, concurrency control, integrity of reference, cache, fault recovery, backup and back-to-storage, and other aspects


Currently, MyISAM and InnoDB are widely used storage engines, and MyISAM is the first choice for most Web applications. The main difference between MyISAM and InnoDB lies in performance and transaction control.

MyISAM is an extension implementation of the early isam (Indexed Sequential Access Method, MySQL5.0 does not support ISAM now, ISAM is designed to be suitable for handling situations where the read frequency is much higher than the write frequency. Therefore, ISAM and MyISAM did not consider the support for things, exclude TPM, and do not need transaction records, the query efficiency of ISAM is considerable, and the memory usage is very small. While inheriting these advantages, MyISAM provides a large number of practical new features and related tools that keep pace with the times. For example, considering concurrency control, table-level locks are provided. Although MyISAM itself does not support fault tolerance, you can use myisamchk for fault recovery. In addition, MyISAM uses separate storage files (MYD data files and MYI index files) for each table, which makes backup and recovery very convenient (copy and overwrite) and supports online recovery.

Therefore, if your application does not need transactions and only processes basic CRUD operations, MyISAM is the best choice.

InnoDB is designed to be suitable for highly concurrent reads and writes. MVCC (Multi-Version Concurrency Control) and row-level locks are used to provide ACID-compliant transaction support. InnoDB supports the integrity of foreign key references and fault recovery capabilities. In addition, the InnoDB performance is actually good, especially when processing large data volumes, in the official words: the CPU efficiency of InnoDB is incomparable to other disk-based relational database storage engines. However, InnoDB backup and recovery is a little troublesome, unless you have used muyun-tablespace support provided by Versions later than 4.1, because InnoDB and MyISAM are different, its data files are not independent of each table. Instead, the shared tablespace is used. The simple copy overwrite method is not applicable to the table. You must recover the data after stopping MYSQL. It is much simpler to use Per-Table Tablespacesd so that each Table corresponds to an independent tablespace file.

In general, InnoDB is a good choice if you need transaction support and a high concurrency read/write frequency. If the concurrent read/write frequency is not high, you can consider BDB. However, BDB support will not be provided in MySQL5.1 and later versions. This option is missing.

As for Heap and BDB, the penetration rate is not as good as the first two, but in some cases, it is quite suitable.

The Heap Storage engine stores data in the memory. Because there is no disk I./O wait, the speed is extremely fast. However, because it is a memory storage engine, any modifications made will disappear after the server is restarted.

Heap is suitable for testing.


BDB is MySQL's first transaction-safe storage engine. Built on the basis of the Berkeley DB database library, transaction security is the same, but the penetration rate of BDB is obviously inferior to that of InnoDB, most MySQL databases are looking for a storage engine that supports transactions, while also looking for a storage engine that supports MVCC or row-level locking. BDB only supports Page-level Lock.

Attached to a table of features of each storage engine in High Performance MySQL

Attribute

MyISAM

Heap

BDB

InnoDB

Transactions	No	No	Yes	Yes
Lock granularity	Table	Table	Page (8 KB)	Row
Storage	Split files	In-memory	Single file per table	Tablespace (s)
Isolation levels	None	None	Read committed	All
Portable format	Yes	N/	No	Yes
Referential integrity	No	No	No	Yes
Primary key with data	No	No	Yes	Yes
MySQL caches data records	No	Yes	Yes	Yes
Availability	All versions	All versions	MySQL-Max	All Versions