Comparison of MyISAM and InnoDB index implementations

MyISAM Index Implementation

The MyISAM engine uses B+tree as the index structure, and the data field of the leaf node holds the address of the record. As shown in figure:

Here set a total of three columns, assuming that we are Col1 as the primary key, then the image above is a MyISAM table of the main index (Primary key) schematic. You can see that the MyISAM index file only holds the address of the data record. In MyISAM, the primary index and the secondary index (secondary key) are structurally indistinguishable, except that the primary index requires the key to be unique, and the key of the secondary index can be duplicated. If we establish a secondary index on the Col2, the structure of this index is shown in the following illustration:

It's also a b+tree,data domain. The address of the data record. 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, take out the value of its data field, then read the corresponding data record with the value of the data field as the address.
The MyISAM index is also called "nonclustered," and is so called to distinguish it from the InnoDB clustered index. InnoDB Index Implementation

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

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

The diagram above is a schematic diagram of the InnoDB Primary index (also the data file), and 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 the table to have a primary key (MyISAM can not), and if 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 implied field for the InnoDB table as the primary key, which is 6 bytes long and has a type of length shaping.

The second difference from the MyISAM index is that the secondary index data field of the InnoDB stores the value of the corresponding record primary key instead of the address. In other words, all of the secondary indexes of INNODB reference the primary key as the data field. For example, the following figure is a secondary index defined on the COL3:

The ASCII code of English characters is used here as the comparison criterion. Clustered indexes This implementation makes searching by primary key efficient, but a secondary index search requires a two-pass index: First retrieving the secondary index to obtain the primary key, and then retrieving the record with the primary key to the primary index. Summary

In database development, understanding how different storage engines are indexed can be very helpful for proper use and optimization of indexes. For example, once you know the InnoDB index implementation, it is easy to see why the long field is not recommended as a primary key because all secondary indexes reference the primary index, and the too long primary index makes the secondary index too large. For example, using a non monotone field as a primary key is not a good practice in InnoDB because the InnoDB data file itself is a b+tree, and a non monotone primary key causes the data file to be frequently split and adjusted to maintain b+tree characteristics when inserting new records, which is very inefficient. Using the self-added field as the primary key is a good choice.