MySQL storage engine

InfoQ 2022-08-06 13:34:40 阅读数:587

actually performs access to database data.Currently MySQL uses the InnoDB engine by default.Compared with using the MyISAM engine in the past, it has the following advantages:

  • Index: The data file itself is the main index.
  • Foreign keys: Support foreign keys.
  • Transactions: add local logs to support safe recovery; support row-level locks to improve concurrency.
  • Concurrency: Support multi-version concurrency control to improve performance.


Storage Structure
MySQL databases use the following two data structures to store and look up data:

  • B+ tree
    : (default) suitable for querying multiple pieces of data continuously.
  • Hash table
    : suitable for querying a single piece of data.
index type
index name|index type|field type|remarks -|-|- PRIMARY KEY|primary index|primary key|field value cannotRepeat, nor can it be empty.INDEX|Common Index|Custom Field|None, low efficiency.UNIQUE|Unique Index|Custom Field|Field values ​​cannot be repeated, and the efficiency is high.FULLTEXT|Text Index|Custom Field|None, for text retrieval.

  • Main index

In the InnoDB storage engine, the data file itself is the main index (clusteredindex): Data is stored in a B+ tree, sorted according to the primary key value.

We can build auxiliary indexes (non-clustered indexes) for other fields to improve the query speed of the fields, but at the same time reduce the table update speed.Record the primary key value in the secondary index instead of the field address: After searching according to the secondary index, you still need to query the data in the primary index according to the primary key value.

  • Combined index

The index can contain multiple fields, the combined index of N fieldsN indexes are actually created.

The combined index of the three fields a/b/c will actually be searched first in the a index, then in the a/b index, and finally in the a/b/c indexFind in.


The view is a virtual table and does not actually store data.Its content is obtained by querying other tables, and is dynamically generated when the view is referenced.

  • Permission management: The permission management of the table cannot be limited to specific rows and columns, but the result set that the user can get can be limited through the view.
  • Data independence: Changes in the structure of the table will not affect the data queried by users using the view.

Foreign key

The slave table is related to the primary key of the main table through the foreign key to establish the relationship between the data tables.

  • Advantages: Ensure data consistency and integrity.
  • Disadvantages: Increase the coupling between data and make it difficult to cluster.
    Foreign keys are therefore deprecated.
Deletion strategy
When performing UPDATE/DELETE operations on the data of the master table, it will affect the associated slave tables.


Log database operations when database data changes.When an error or conflict occurs, a rollback can be performed.Ensure data consistency.
bin log archive log
In the beginning, MySQL did not work with the InnoDB engine, and other storage engines only had the general bin log for archiving (at the server layer).

The InnoDB engine completes the main memory data update and submits it to the executor, and the bin log records the operation.If the main memory data is updated and the database crashes when the bin log is not written, the original data will be lost during subsequent machine backups.This results in no safe recovery of data: once the database restarts abnormally, the previously committed records will be lost.
redolog redo log
MySQL comes with redo log after introducing InnoDB engine.It is used to restore system records when the database restarts abnormally.

  • When the InnoDB engine completes the main memory data update but has not yet committed, the redo log records the operation and enters the prepare state.
  • Operations are logged by the bin log when the InnoDB engine submits to the executor.
  • After the commit is completed, the executor notifies the InnoDB engine, and the redo log enters the commit state.

If the database crashes when the bin log is not written, the data will be restored according to the redo log in subsequent machine backups.

The database crashes if the bin log has been written but the redo log is still in the prepare state.MySQL will judge whether the redo log is complete, and submit it immediately if it is complete.Otherwise, judge whether the bin log is complete. If it is complete, submit the redo log, and if it is incomplete, roll back the transaction.This solves the problem of data consistency.
copyright:author[InfoQ],Please bring the original link to reprint, thank you.