Talk about MySQL storage engine

MySQL's storage engine is an important part of the MySQL architecture and the core of the MySQL architecture. The plug-in storage engine is an important feature that distinguishes it from other databases. It is at the bottom of the server side of the MySQL architecture and is the implementation of the underlying physical structure. It is used to store data in files or memory in various technical ways. Different storage engines have different storage mechanisms, indexing techniques and locking levels. . Common MySQL storage engines include InnoDB, MyISAM, Memory, Archive, etc. They have their own characteristics. We can establish corresponding storage engine tables according to different specific applications.

Before talking about different storage engines, we need to understand a few basic concepts:

(1) Transaction

Transaction is A set of atomic SQL statements or an independent unit of work. If the database engine can successfully apply this set of SQL statements to the database, then it will be executed. If any of the statements cannot be executed due to a crash or other reasons, then all None of the statements will be executed. In other words, all statements within the transaction either execute successfully or fail.

Give a typical example of a bank application:

Assume that the bank's database has two tables: a check table and a savings table. Now a customer A wants to transfer 2,000 yuan from his checking account to his Savings account, then at least three steps are required:

a. Check that A’s checking account balance is higher than 2,000 yuan;

b. Subtract 2,000 yuan from A’s checking account balance;

c. Add 2,000 yuan to A’s savings account balance.

These three steps must be packaged in a transaction. If any step fails, all steps must be rolled back. Otherwise, A, as a customer of the bank, may inexplicably lose 2,000 yuan, and something will go wrong. . This is a typical transaction. This transaction is the smallest indivisible unit of work. All operations in the entire transaction are either submitted successfully or failed and rolled back. It is impossible to execute only part of it. This is also the atomic characteristic of the transaction.

(2) Read lock and write lock

Whenever there are multiple SQLs that need to modify data at the same time, concurrency control problems will arise. .

Assume that in a public mailbox, user A is reading the mailbox, and at the same time, user B is deleting an email in the mailbox. What will happen? Customer A may exit with an error when reading, or may read inconsistent mailbox data. If you treat the mailbox as a table in the database, you can see that it has the same problem.

The way to solve this kind of classic problem is concurrency control. That is, when dealing with concurrent reading or writing, the problem can be solved by implementing a lock system composed of two types of locks. These two types of locks are shared locks and exclusive locks, also called read locks and write locks.

Read locks are shared, that is, they do not block each other. Multiple clients can read the same resource at the same time without interfering with each other. Write locks are exclusive, that is, a write lock blocks other write locks and read locks. Only in this way can it be ensured that only one user can perform writing at a given time, preventing other users from reading the same resource being written. Write locks have higher priority than read locks.

(3) Row locks and table locks

Locking occurs every moment in the actual database system, and locks are also granular, which improves sharing The method of resource concurrent issuance is to make locks more selective and try to only lock part of the data that needs to be modified instead of all resources, so precise locking is required. However, locking also consumes resources, including obtaining the lock, checking whether the lock is released, and releasing the lock, etc., which will increase the system overhead. The so-called lock strategy is to find a balance between lock overhead and data security. This balance will also affect performance.

Each MySQL storage engine has its own lock strategy and lock granularity. The two most commonly used important lock strategies are table locks and row locks.

Table lock is the least expensive strategy and will lock the entire table. When a user writes to the table, he must first obtain the write lock, which will block all read and write operations on the table by other users. When there is no write lock, other reading users can obtain the read lock, and read locks do not block each other. Row lock can support maximum concurrent processing, but it also brings the maximum lock overhead. It only locks specified records, and other processes can still operate on other records in the same table. Table-level locks are fast but have many conflicts, while row-level locks have fewer conflicts but are slower.​​​

Understanding the above concepts, we can well distinguish the differences between different storage engines.

InnoDB Storage Engine

MySQL storage engine can be divided into official storage engine and third-party storage engine. InnoDB is a powerful third-party storage engine with relatively Good performance and automatic crash recovery features are currently widely used and are the mainstream of the current MySQL storage engine. It is popular in both transactional storage and non-transactional storage.

InnoDB storage engine supports transactions, row locks, non-locking reads, and foreign keys.

Unless there are special reasons, you can consider using InnoDB first when creating tables. InnoDB is also a very good storage engine that is worth spending time to learn in depth. We plan to study this storage engine in the future, so we will not go into details here.

2. MyISAM storage engine

The MyISAM storage engine is the storage engine officially provided by MySQL. It was the mainstream MySQL storage engine before the emergence and improvement of InnoDB. , but it is gradually being phased out mainly because it does not support transactions. This may be because the developers of MySQL believe that not all applications require transactions, so this storage engine that does not support transactions exists.

MyISAM does not support transactions, row-level locks, table locks, and full-text indexes. The biggest flaw is that it cannot safely recover after a crash.

MyISAM has a simple design and data is stored in a compact format, so its performance is very good in certain scenarios. However, its table locks bring performance problems. If you find that all queries are "Locked" for a long time Status, table lock is the culprit.

Therefore, for read-only data or tables that are relatively small and can tolerate repair operations, you can still use MyISAM. For applications that do not require transactions, choose the MyISAM storage engine and you may be able to get For higher performance, tables using the MyISAM storage engine exist in the default information_schema library that comes with MySQL.

| TRIGGERS | CREATETEMPORARY TABLE `TRIGGERS` (
  `TRIGGER_CATALOG` varchar(512) NOT NULLDEFAULT '',
  `TRIGGER_SCHEMA` varchar(64) NOT NULL DEFAULT'',
  `TRIGGER_NAME` varchar(64) NOT NULL DEFAULT'',
  `EVENT_MANIPULATION` varchar(6) NOT NULLDEFAULT '',
  `EVENT_OBJECT_CATALOG` varchar(512) NOT NULLDEFAULT '',
  `EVENT_OBJECT_SCHEMA` varchar(64) NOT NULLDEFAULT '',
  `EVENT_OBJECT_TABLE` varchar(64) NOT NULLDEFAULT '',
  `ACTION_ORDER` bigint(4) NOT NULL DEFAULT'0',
  `ACTION_CONDITION` longtext,
  `ACTION_STATEMENT` longtext NOT NULL,
  `ACTION_ORIENTATION` varchar(9) NOT NULLDEFAULT '',
  `ACTION_TIMING` varchar(6) NOT NULL DEFAULT'',
  `ACTION_REFERENCE_OLD_TABLE` varchar(64)DEFAULT NULL,
  `ACTION_REFERENCE_NEW_TABLE` varchar(64)DEFAULT NULL,
  `ACTION_REFERENCE_OLD_ROW` varchar(3) NOTNULL DEFAULT '',
  `ACTION_REFERENCE_NEW_ROW` varchar(3) NOTNULL DEFAULT '',
  `CREATED` datetime DEFAULT NULL,
  `SQL_MODE` varchar(8192) NOT NULL DEFAULT '',
  `DEFINER` varchar(77) NOT NULL DEFAULT '',
  `CHARACTER_SET_CLIENT` varchar(32) NOT NULLDEFAULT '',
  `COLLATION_CONNECTION` varchar(32) NOT NULLDEFAULT '',
  `DATABASE_COLLATION` varchar(32) NOT NULLDEFAULT ''
)ENGINE=MyISAM DEFAULT CHARSET=utf8 |

3. Memory storage engine

Memory storage engine puts the data in the table in memory, so it is very fast, but because of its It supports table locks, so the concurrency performance is poor. The worst thing is that this storage engine will lose all the data in the table after the database restarts or crashes. It is only suitable for temporary tables that store temporary data. This storage engine is generally used in MySQL. Stores the intermediate result set of the query. For example, the default information_schema library that comes with MySQL has many tables using the Memory storage engine.

|TABLES | CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT'',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT'',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
  `ENGINE` varchar(64) DEFAULT NULL,
  `VERSION` bigint(21) unsigned DEFAULT NULL,
  `ROW_FORMAT` varchar(10) DEFAULT NULL,
  `TABLE_ROWS` bigint(21) unsigned DEFAULTNULL,
  `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULTNULL,
  `DATA_LENGTH` bigint(21) unsigned DEFAULTNULL,
  `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULTNULL,
  `INDEX_LENGTH` bigint(21) unsigned DEFAULTNULL,
  `DATA_FREE` bigint(21) unsigned DEFAULT NULL,
  `AUTO_INCREMENT` bigint(21) unsigned DEFAULTNULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(32) DEFAULT NULL,
  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
  `CREATE_OPTIONS` varchar(255) DEFAULT NULL,
  `TABLE_COMMENT` varchar(2048) NOT NULLDEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8|

4. Archive storage engine

The Archive storage engine only supports INSERT and SELECT operations and supports row locks, but it is not a transaction-safe storage. The biggest advantage of the engine is that it has a good compression ratio, which can generally reach 1:10, and can store the same data in a smaller disk space.

Archive storage engine is very suitable for storing archived data, such as historical data, log information data, etc. This type of data often has a very large amount of data, and basically only has INSERT and SELECT operations. Using this storage engine can save disks very much. space.

Take a historical table with 250 million records in a certain library as an example:

mysql> select TABLE_ROWSfrom TABLES where TABLE_NAME='history';
+------------+
| TABLE_ROWS |
+------------+
|  251755162 |
+------------+
1 row in set (0.01 sec)

Originally, when it defaulted to the InnoDB storage engine, the table size was 12G.

mysql> select concat(round(sum(DATA_LENGTH/1024/1024), 2),'MB')as  
data from TABLES where TABLE_NAME='history';
+------------+
| data       |
+------------+
| 12918.88MB |
+------------+
1 row in set (0.00 sec)

When the Archive storage engine is used to rebuild the above table and re-insert the same data, the size of the table becomes less than 2G, which shows that the storage has a good compression ratio.

Other storage engines are less commonly used and will not be discussed here.

The above is about the content of MySQL storage engine. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!