MySQL's B + Tree index

This article discusses the types of indexes that MySQL supports and their pros and cons. Note that in MySQL, indexes are implemented at the storage engine level rather than the server layer, so the indexes of the different storage engines work differently, and not all the storage engines support all types of indexes.

B + Tree is a classical data structure, which is produced by the combination of a balanced tree and a two-fork lookup tree. It is a balanced lookup tree designed for disk or other direct access aids, in a B + tree, all record nodes are in the same level of the key value of the order stored in the same layer of leaf nodes, the leaf nodes are connected by the pointer to form a two-way circular chain list, Non-leaf nodes (root nodes, side points) hold only the key values and do not store the actual data. Here's an example of a 2-story B + Tree:

Maintaining the tree balance is mainly to improve query performance, but in order to maintain the balance of the tree, the cost is also huge, when there is data insertion or deletion, the need to use split node, left, right and other methods. B + trees are highly balanced because of their high fan-out, usually at 2~3 levels, and can effectively reduce IO times when queried. B + Tree indexes can be divided into clustered index (clustered index) and nonclustered index (that is, secondary index, secondary index).

Clustered index

InnoDB table when the index organization table, that is, the table data by the primary key B + Tree storage, leaf node directly stored data, each table can only have one clustered index.

Secondary index

A secondary index (also called a nonclustered index) is a leaf node that does not contain all of the data for a row, and a leaf node contains a bookmark connection in addition to the key value, which is used to find the corresponding row data. The following illustration shows the relationship between the InnoDB storage Engine Secondary index and the clustered index:

As can be seen from the above illustration, the secondary Index leaf node holds the primary key value, obtains the primary key value, and then looks for the entire row of data from the clustered index. For example, if you look for data in a secondary index with a height of 3, you first get the primary key value (3 IO) from the secondary index, then look for the entire row of data (3 IO) from a clustered index of 3, and a total of 6 io. Multiple secondary indexes can exist on a single table.