What are the differences between mysql storage engines?

The difference between storage engines in MySQL: Taking Innodb and myisam as an example, the former supports transactions but the latter does not; the former emphasizes versatility and supports more extended functions, while the latter mainly focuses on performance; the former does not support full-text indexing , and the latter supports full-text indexing, etc.

mysql supports several storage engines. Here we mainly discuss several commonly used storage engines. Innodb, myisam

INNODB

INNODB index implementation

The same thing as MyISAM is that InnoDB also uses B Tree. A data structure to implement B-Tree index. The big difference is that the InnoDB storage engine uses the "clustered index" data storage method to implement the B-Tree index. The so-called "aggregation" means that the data rows and adjacent key values ​​​​are compactly stored together. Note that InnoDB can only The records of a leaf page (16K) are aggregated (that is, the clustered index satisfies a certain range of records), so records containing adjacent key values ​​​​may be far apart.

In InnoDB, the table is called an index organized table. InnoDB constructs a B Tree according to the primary key (if there is no primary key, a unique and non-empty index will be selected instead. If there is no primary key, a unique and non-empty index will be selected instead. For such an index, InnoDB will implicitly define a primary key as a clustered index), and at the same time, the leaf pages store the row record data of the entire table. The leaf nodes of the clustered index can also be called data pages. Non-leaf pages can be viewed Do a sparse index of leaf pages.

The following figure illustrates the implementation of InnoDB clustered index, and also reflects the structure of an innoDB table. It can be seen that in InnoDB, the primary key index and data are integrated and not separated.

#This implementation method gives InnoDB ultra-high performance in retrieval by primary key. You can choose a clustered index purposefully, for example, in a mail table, you can choose user ID to aggregate data. In this way, you only need to read a small number of consecutive data pages from the disk to obtain all the mails of a user with a certain ID, avoiding the need for Random I/O spent reading scattered pages.

InnoDB is an I/O operation. Innodb reads and writes using MVCC to support high concurrency.

Full table scan

When InnoDB does a full table scan, it is not efficient because InnoDB does not actually read sequentially. In most cases, it reads randomly. Pick. When doing a full table scan, InnoDB scans pages and rows in primary key order. This applies to all InnoDB tables, including fragmented tables. If the primary key page table (the page table that stores primary keys and rows) is not fragmented, a full table scan is quite fast because the read order is close to the physical storage order. But when the primary key page is fragmented, the scan will become very slow

Row-level lock

Provides row locking (locking on row level), provided with Oracle Type-consistent non-locking read in SELECTs. In addition, the row lock of the InnoDB table is not absolute. If MySQL cannot determine the range to be scanned when executing a SQL statement, the InnoDB table will also lock the entire table. Tables, such as

update table set num=1 where name like “%aaa%”

MYISAM

MyISAM index implementation

Each MyISAM is stored as three files on disk . The name of the first file begins with the name of the table, and the extension indicates the file type. The MyISAM index file [.MYI (MYIndex)] and the data file [.MYD (MYData)] are separated. The index file only saves the pointer (physical location) of the page where the record is located. The page is read through these addresses, and then the page is read. The indexed row. Let’s take a look at the structure diagram first

#The above picture illustrates well that the leaves in the tree save the physical location of the corresponding row. Through this value, the storage engine can smoothly query the table and obtain a complete row of records. At the same time, each leaf page also saves a pointer to the next leaf page. This facilitates range traversal of leaf nodes. As for the secondary index, it is implemented in the MyISAM storage engine in the same way as the above figure. This also shows that MyISAM's indexing method is "non-clustered", which is in contrast to Innodb's "clustered index"

MyISAM will read the index into memory by default and operate directly in memory;

Table-level lock

Summary: Innodb emphasizes versatility and compares the supported expansion functions Many, myisam mainly focuses on performance

Difference

1. InnoDB supports transactions, MyISAM does not support it. For InnoDB, every SQL language is encapsulated into a transaction by default and automatically submitted. , this will affect the speed, so it is best to put multiple SQL statements between begin and commit to form a transaction;

2. InnoDB is a clustered index, and the data file is tied to the index and must be There must be a primary key, and indexing through the primary key is very efficient. However, the auxiliary index requires two queries, first to query the primary key, and then to query the data through the primary key. Therefore, the primary key should not be too large, because if the primary key is too large, other indexes will also be large. MyISAM is a non-clustered index, the data files are separated, and the index saves the pointer of the data file. Primary key indexes and secondary indexes are independent.

3. InnoDB does not save the specific number of rows in the table. When executing select count(*) from table, a full table scan is required. MyISAM uses a variable to save the number of rows in the entire table. When executing the above statement, you only need to read the variable, which is very fast;

4. Innodb does not support full-text index, while MyISAM supports full-text index. In terms of query efficiency, MyISAM is higher;

The above is the detailed content of What are the differences between mysql storage engines?. For more information, please follow other related articles on the PHP Chinese website!