How MySQL MyISAM and InnoDB are indexed

In MySQL, the index is the concept of storage engine level, different storage engine implementation of the index is different, this article mainly discusses the MyISAM and InnoDB two storage engine index implementation way.

MyISAM Index Implementation

The MyISAM engine uses B+tree as the index structure, and the data domain of the leaf node holds the address of the record. is a schematic diagram of the MyISAM index:

There are three columns in the table, assuming that we have Col1 as the primary key, it is a MyISAM table's main index (Primary key) schematic. You can see that the index file of MyISAM only stores the address of the data record. In MyISAM, the primary index and secondary index (secondary key) are structurally indistinguishable, except that the primary index requires that key be unique, and the secondary index key can be duplicated. If we establish a secondary index on Col2, the structure of this index is as follows:

It is also the address of a b+tree,data field that holds data records. Therefore, the algorithm of index retrieval in MyISAM is to search the index according to the B+tree search algorithm first, if the specified key exists, the value of its data field is fetched, then the data record is read with the value of the data field.

The index of MyISAM is also called "non-aggregation", and the reason for this is to differentiate it from InnoDB's clustered index. Clustered indexes (clustered index), nonclustered indexes (secondary index), these two names are called indexes, but this is not a separate index type, but a way of storing data. For clustered index storage, row data and primary key B + trees are stored together, auxiliary keys B + trees store only secondary and primary keys, and primary and non-primary key B + trees are almost two types of trees. For non-clustered index storage, the primary key B + Tree stores a pointer to the true data row in the leaf node, rather than the primary key.

InnoDB Index Implementation

Although InnoDB also uses B+tree as the index structure, the implementation is very different from MyISAM.

The first major difference is that the InnoDB data file itself is the index file. As you know above, the MyISAM index file and the data file are detached, and the index file only holds the address of the data record. In InnoDB, the table data file itself is an index structure organized by B+tree, and the tree's leaf node data field holds the complete data record. The key of this index is the primary key of the data table, so the InnoDB table data file itself is the primary index.

Is the InnoDB primary index (also the data file), you can see that the leaf node contains the complete data record. This index is called a clustered index. Because the InnoDB data file itself is clustered by the primary key, the INNODB requires that the table must have a primary key (MyISAM can not), and if it is not explicitly specified, the MySQL system automatically selects a column that uniquely identifies the data record as the primary key, and if no such column exists, Then MySQL automatically generates an implicit field for the InnoDB table as the primary key, which is 6 bytes long and has a length of type.

The second difference from the MyISAM index is that the secondary index of the InnoDB data field stores the value of the corresponding record primary key instead of the address. In other words, all secondary indexes of InnoDB refer to the primary key as the data domain. For example, to define a secondary index on the Col3:

Here is the ASCII code of the English character as the comparison criterion. Clustered index This implementation makes search by primary key efficient, but secondary index search needs to retrieve two times index: first retrieves the secondary index to obtain the primary key, and then retrieves the record with the primary key to the primary index.

Understanding how index implementations of different storage engines can be useful for proper use and optimization of indexes, such as knowing the InnoDB index implementation, makes it easy to understand why it is not recommended to use too-long fields as primary keys, because all secondary indexes refer to the primary index, and the long primary index makes the secondary index too large. For example, using non-monotonic fields as primary keys is not a good idea in InnoDB because the InnoDB data file itself is a b+tree, and a non-monotonic primary key causes the data file to be frequently split in order to maintain b+tree characteristics when inserting new records, which is inefficient, Using the self-increment field as the primary key is a good choice.

The following is a more straightforward example:

Adaptive Hash Index of INNODB

The InnoDB storage engine monitors the lookup of indexes on a table, and if it observes that a hash index can lead to an increase in speed, a hash index is established, so it is called adaptive (adaptive). The adaptive Hash index is constructed from the B + Tree of the buffer pool and is therefore fast to build. And without having to hash the entire table, the InnoDB storage engine automatically hashes the pages based on the frequency and pattern of access. The default index type for MySQL's heap storage engine is hash.

Reference:

Http://www.cnblogs.com/ylqmf/archive/2011/09/16/2179166.html

Http://blog.codinglabs.org/articles/theory-of-mysql-index.html

Http://www.codeceo.com/article/mysql-innodb-index.html

How MySQL MyISAM and InnoDB are indexed