Different implementations of myisam and innodb Indexes

The MyISAM engine uses B + Tree as the index structure. The data domain of the leaf node stores the data record address. It is the schematic diagram of the MyISAM index: Here there are three columns in the Table. Suppose we use Col1 as the Primary key, it is the Primary index (Primary key) of the MyISAM table. It can be seen that the index file of MyISAM only stores the address of the data record. In MyISAM, the primary index and Secondary index (Secondary key) have no difference in structure, but the primary index requires that the key is unique, and the Secondary index key can be repeated. If we create a secondary index on Col2, the index structure is shown in: it is also a B + Tree, and the data domain stores the data record address. Therefore, the index search algorithm in MyISAM first searches for indexes based on the B + Tree search algorithm. If the specified Key exists, the value of its data field is obtained, then, read the corresponding data records using the data domain value as the address. The index method of MyISAM is also called "non-clustered". The reason for this is to distinguish it from the clustered index of InnoDB.
Although InnoDB Index implementation uses B + Tree as the index structure, the specific implementation method is different from MyISAM. The first major difference is that the InnoDB data file itself is an index file. As mentioned above, the MyISAM index file is separated from the data file, and the index file only stores the data record address. In InnoDB, the table data file itself is an index structure organized by B + Tree. The leaf node data field of this Tree stores complete data records. The key of this index is the primary key of the data table, so the InnoDB table data file itself is the primary index. It is an InnoDB primary index (also a data file). We can see that the leaf node contains a complete data record. This index is called a clustered index. Because the data files in InnoDB need to be clustered by the primary key, InnoDB requires that the table have a primary key (MyISAM may not). If it is not explicitly specified, mySQL automatically selects a column that uniquely identifies a data record as the primary key. If this column does not exist, MySQL automatically generates an implicit field for the InnoDB table as the primary key, this field is 6 bytes in length and its type is long integer.
The second difference from the MyISAM index is that InnoDB's secondary index data domain stores the value of the primary key of the corresponding record rather than the address. In other words, all secondary indexes of InnoDB reference the primary key as the data domain. For example, to define a secondary index on Col3: here the ASCII code of English characters is used as a comparison criterion. Clustered index makes the search by primary key very efficient, but secondary index search requires two indexes: first, retrieve the secondary index to obtain the primary key, then, use the primary key to search for the record in the primary index.
Understanding the index implementation methods of different storage engines is very helpful for correct use and optimization of indexes. For example, after knowing the index Implementation of InnoDB, it is easy to understand why it is not recommended to use too long fields as the primary key, because all secondary indexes reference the primary index, too long primary index will make the secondary index too large. For example, it is not a good idea to use non-monotonous fields as the primary key in InnoDB, because the InnoDB data file itself is a B + Tree, non-monotonous primary keys will cause frequent split and adjustment of data files to maintain the features of B + Tree during the insertion of new records, which is very inefficient, using an auto-increment field as the primary key is a good choice.