Talking about MySQL storage engine-innodb&myisam

The storage engine is located on the third tier of MySQL's logical architecture, responsible for storing and extracting data from MySQL. There are many MySQL storage engines, and different storage engines have different ways of saving data and indexes. Each storage engine has its advantages and disadvantages, and this article discusses only the most common InnoDB and MyISAM two storage engines. In this article you can view the graphical MySQL index for data storage form and index

MySQL Logical architecture diagram:

InnoDB Storage Engine

InnoDB is the default transactional storage engine and the most important and most widely used storage engine. In the absence of special circumstances, it is generally preferable to use the InnoDB storage engine.

1??、 data Storage form

When using InnoDB, the data table is divided into. frm and IDB two files for storage.

2??、 The grain size of the lock

InnoDB uses MVCC (multi-version concurrency control) to support high concurrency, INNODB implements four isolation levels, the default level is repetable read, and prevents Phantom reads from appearing through a gap lock policy. Its lock granularity is the row lock. "Implemented by MVCC, MVCC will be introduced later"

3??、 Transactions

InnoDB is a typical transactional storage engine that supports real hot backups through a number of mechanisms and tools.

4 Storage characteristics of??、 data

The InnoDB table is based on a clustered index (described in another blog), and the clustered index has a high performance on the primary key query, although his level two index (non-primary key index) must contain a primary key column, and other indexes will be large.

MyISAM Storage Engine

1??、 data Storage form

MyISAM takes the form of an index and data separation, saving the data in three files. Frm.myd,. Myis.

2??、 The grain size of the lock

MyISAM does not support row locks, so a shared lock is added to the table when read, and an exclusive lock is added to the table when written. Because the entire table is locked, compared to InnoDB, it is inefficient for concurrent writes.

3??、 Transactions

MyISAM does not support transactions.

4 Storage characteristics of??、 data

MyISAM are stored based on non-clustered indexes.

5??、 Other

MyISAM provides a number of features, including full-text indexing, compression, spatial functions, delayed update of index keys, and more.

A compressed table cannot be modified, but a compressed table can significantly reduce disk footprint, so you can also reduce disk IO to provide query performance.

Full-text indexing, which is a segmentation-based index that can support complex queries.

Delaying the update of the index key does not immediately write the updated index data to the disk, but instead writes it to the in-memory buffer and writes the corresponding index to the disk only when the buffer is cleared, which greatly improves write performance.

Three, contrast and choice

Each of the two storage engines has a bit, MyISAM focus on performance, InnoDB focus on the business.两者最大的区别就是InnoDB支持事务，和行锁。

How do I choose between the two storage engines?

① do you have a transactional operation? There, InnoDB.

Does ② store concurrent modifications? There, InnoDB.

Does ③ pursue fast queries with fewer data modifications? Yes, MyISAM.

Does ④ use full-text indexing? If you do not reference a third-party framework, you can choose MyISAM, but you can use third-party frameworks and inndb more efficiently.

Iv. talking about MVCC

MySQL Most transactional storage engines do not implement a simple row lock. They generally implement multi-version concurrency control (MVCC) at the same time based on the consideration of improving concurrency performance.

It can be considered that MVCC is a variant of the row-level lock, which avoids lock-up operations in most cases and therefore costs less. Regardless of how they are implemented, they have a non-blocking read operation, and the write operation locks only the lines that are made.

MVCC is achieved by saving a snapshot of the data at a point in time, meaning that the data that each transaction sees is consistent regardless of how long the transaction executes. The MVCC of InnoDB is implemented by saving two hidden columns behind each row of records, one of which saves the creation time of the row, one that saves the line's expiration time (or deletion time), and of course, not the time, but the system version number. For each transaction, the version number is incremented, and the system version number at the start of the transaction is the version number of the transaction.

ID	name	creation time (line version number)	Delete Time (delete version number)
1	Mary	1	Null
2	Jann	1	Null

In terms of the REPEATABLE read isolation level of the InnoDB storage engine:

SELECT

? ① only the data rows with the creation time version number less than the current transaction version number (to ensure that the transaction reads the rows that exist before the transaction starts, or the rows that the transaction itself inserts)

? The delete version number of the ② row is either undefined or greater than the current transaction version number, which ensures that the transaction is read to the row that was not deleted before the transaction was started

Only records of compound appeal two conditions will be returned as results

INSERT

? Save the current system version number as the row version number for the inserted data

DELETE

? Save the current system version number as the Delete row version number

?

UPDATE

? Inserts a row of data and assigns the current system version number to the row version number; co-worker saves the current system version number to the original line as the delete version number.

Note: MVCC only works at two isolation levels of repeatable and read Committed.
My personal blog: Li Qiang's personal blog (background architecture based on Ssm,nginx+redis)

Talking about MySQL storage engine-innodb&myisam