MySQL InnoDB Index

B + Tree Index

One feature of B + tree indexes in dB is high fan-out. Generally, the height of B + tree in dB is about 2-3 layers, which means that only 2-3 Io operations are required. The current disk has approximately 100 I/O operations per second, and 2-3 times means that the query time is only 0.02-0.03 seconds.

Clustered Index

The InnoDB Storage engine table is an index organization table, that is, the data in the table is stored sequentially with the primary key installed. The clustered index is to construct a B + based on the primary key of each table, and the leaf node stores the row record data of the entire table. Therefore, the clustered index is also part of the index.

The actual data page can only be sorted by a B + tree. Therefore, each table can have only one clustered index. In many cases, the query optimizer tends to use clustered indexes, because clustered indexes allow us to directly find data on the leaf node of the index.

Secondary Index

For secondary indexes (also called non-clustered indexes), leaf nodes do not contain all data of rows. In addition to the key value, each leaf node also contains a bookmarkdonefile that tells the InnoDB Storage engine where row data corresponding to the index can be found. Because the InnoDB Storage engine is an index organization table, the secondary index bookmarks of the InnoDB Storage engine are the clustered index keys for the corresponding row data.

When to use B + tree indexes

Generally, it makes sense to use B + tree indexes to access a few rows in a table. For fields such as gender, region, and type, they have a small value range (low selectivity). Generally, B + tree indexes are not recommended. Otherwise, if a field has a wide range of values and has almost no duplicates (high selectivity), it is most appropriate to use the B + tree index, such as the email field, in some applications, repeated occurrences are usually not allowed.

Therefore, when you access a highly selective field and retrieve a few rows from the table, it is necessary to add a B + tree index to this field. However, if the access field is highly selective, but the retrieved row data occupies most of the data in the table, the MySQL database will not use the B + tree index.

ArticleRefer: