Index implementation of MyISAM and InnoDB

1, MyISAM uses B+tree as the index structure, the leaf node data holds the pointer, namely the record address. The primary key index and the secondary index are the same.
2, InnoDB also use B+tree as the index structure, also do not need to note that for the primary key index, InnoDB use the clustered index, the InnoDB data file itself is the index file. MyISAM, the primary key index and the data file are separated.
3, InnoDB data file, to press the primary key clustered index, which requires INNODB table must have a primary key (MyISAM can not). If you do not explicitly specify a primary key, InnoDB automatically selects a field that uniquely identifies the record as the primary key, such as the Auto_increment field, and if no such column exists, InnoDB automatically generates an implied field as the primary key, which is a 6-byte implicit field, which is a long shape.
4, for the auxiliary index of INNODB, the data of the leaf node holds the value of the primary key. This means that using a secondary index to locate a record requires two indexes: first the value of the primary key is found using the secondary index, and a record is found using the primary key index, based on the value of the primary key.
5. Why should the InnoDB index be designed like this?
If the secondary index data holds the row pointer, when the row moves or the data page splits, the value of the data field row pointer needs to be updated, which increases maintenance costs. There is no problem with the value of the primary key in data. Row movement and Data page splitting, the primary key index is automatically updated. The value of the Data Association primary key, which does not need to be updated, is equivalent to adding an indirection layer. This indirect layer has a small impact on performance because it is very fast to locate records through the primary key.
6, understand the InnoDB index implementation, there are several places to note:
Do not use a long field as the primary key, because the secondary index uses the primary key index to locate the record, which is too long, uses more memory, and affects performance.
Using a monotone field as the primary key, especially insert, b+tree maintenance is expensive if it is non-monotonic.
7, this can be explained very well, the isolation level repeatable-read, does not use the index to lock the entire table, uses the index (primary key index or the secondary index) only will lock the corresponding row.
8. In the query execution plan, there is a field type, Eq_ref indicates that the primary key index is used to navigate directly to the record. Ref indicates that the secondary index is used first, the value of the primary key is found, and the primary key index is used to navigate to the record.

Index implementation of MyISAM and InnoDB