The difference between the index implementation methods of MyISAM and InnoDB storage engines

1. MyISAM index implementation:

1) Primary key index:

MyISAM engine uses B+Tree as the index structure, and the data field of the leaf node stores The address of the data record. The following figure is the schematic diagram of MyISAM primary key index:

##                                                There are three columns in total. Suppose we use Col1 As the primary key, the figure myisam1 shows the primary index (Primary key) of a MyISAM table. It can be seen that MyISAM's index file only saves the address of the data record.

2) Secondary index (Secondary key)

In MyISAM, there is no structural difference between the primary index and the secondary index (Secondary key). The primary index requires the key to be unique, while the key of the secondary index can be repeated.

If we create an auxiliary index on Col2, the structure of this index is as shown below:

is also the same B+Tree, the data field saves the address of the data record. Therefore, the index retrieval algorithm in

MyISAM is to first search the index according to the B+Tree search algorithm. If the specified Key exists, the value of its data field is taken out, and then the value of the data field is used as the address to read the corresponding data record. .

MyISAM's indexing method is also called "non-clustered". The reason why it is called so is to distinguish it from InnoDB's clustered index.

2. InnoDB index implementation

Although InnoDB also uses B+Tree as the index structure, the specific implementation method is completely different from MyISAM.

1) Primary key index:

         

MyISAM index file and data file are separated, and the index file only saves the address of the data record. In InnoDB, the table data file itself is an index structure organized by B+Tree, and the leaf node data field of this tree saves 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.

##                                                                                                                                                                    

(Figure inndb primary key index) is a schematic diagram of the InnoDB primary index (also a data file). You can see that the leaf nodes contain complete data records. This kind of index is called clustered index. Because InnoDB's data files themselves are aggregated by primary key, InnoDB requires that the table must have a primary key (MyISAM may not have one). If it is not explicitly specified, the MySQL system will automatically select a column that can uniquely identify the data record as the primary key. If it does not exist, For this type of column, MySQL automatically generates an implicit field as the primary key for the InnoDB table. The length of this field is 6 bytes and the type is long.

2). InnoDB’s auxiliary index

All auxiliary indexes in InnoDB refer to the primary key as the data field. For example, the following figure shows an auxiliary index defined on Col3:

## The InnoDB table is built based on a clustered index. Therefore, InnoDB's index can provide a very fast primary key lookup performance. However, its auxiliary index (Secondary Index, that is, non-primary key index) will also contain the primary key column, so if the primary key is defined relatively large, other indexes will also be large. If you want to define many indexes on the table, try to define the primary key as small as possible. InnoDB does not compress indexes.

The ASCII code of the text character is used as the comparison criterion.

This implementation of clustered index makes searching by primary key very efficient, but auxiliary index search requires retrieving the index twice: first, retrieve the auxiliary index to obtain the primary key, and then use the primary key to retrieve the records in the primary index.

The index implementation methods of different storage engines are very helpful for the 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 overly long fields as primary keys. , because all secondary indexes refer to the primary index, a primary index that is too long will make the secondary index too large. For another 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. Non-monotonic primary keys will cause the data file to maintain the characteristics of the B+Tree when inserting new records. Frequent split adjustments are very inefficient, and using auto-increment fields as primary keys is a good choice.

The difference between InnoDB index and MyISAM index :

First, the difference between the main index, the InnoDB data file itself is the index file . The index and data of MyISAM are separated.

The second is the difference between auxiliary indexes: InnoDB's auxiliary index data field stores the value of the corresponding record's primary key instead of the address. There is not much difference between MyISAM's secondary index and the primary index.

MySql index algorithm principle analysis (easy to understand, only talking about B-tree)

The above is the detailed content of The difference between the index implementation methods of MyISAM and InnoDB storage engines. For more information, please follow other related articles on the PHP Chinese website!