MySQL's storage engine and index

2 MySQL storage engine and Index

It can be said that the database must have an index, the retrieval process becomes a sequential lookup without an index, and the time complexity of O (n) is almost intolerable. It's very easy to imagine how a table with a single keyword can be indexed using a B + tree, as long as the keyword is stored in the tree's node. When the database contains multiple fields in a single record, a B + tree can only store the primary key, and if the non-primary key field is retrieved, the primary key index loses its function and becomes a sequential lookup.  A second set of indexes should be established on the second column to retrieve. This index is organized by a separate B + tree. There are two common ways to solve the problem of multiple B + trees accessing the same set of table data, a clustered index (clustered index), a non-clustered index (secondary). Although these two names are called indexes, this is not a separate index type, but a way of storing data. For clustered index storage, row data and primary key B + trees are stored together, auxiliary keys B + trees store only secondary and primary keys, and primary and non-primary key B + trees are almost two types of trees. For non-clustered index storage, the primary key B + Tree stores a pointer to the true data row in the leaf node, rather than the primary key.

InnoDB uses a clustered index, organizes the primary key into a B + tree, and the row data is stored on the leaf node, and if the "where id = 14" condition is used to find the primary key, the corresponding leaf node is found according to the B + tree's retrieval algorithm, then the row data is obtained. A conditional search on the Name column requires two steps: The first step is to retrieve the name in the secondary index B + tree and reach its leaf node for the corresponding primary key. The second step uses the primary key to perform a B + tree retrieval operation at the main index B + species, and finally reaches the leaf node to get the entire row of data.

Myism uses the non-clustered index, the nonclustered index of the two B + trees does not look different, the structure of the node is exactly the same as the content of the storage, the primary key index B + Tree node stores the primary key, secondary key index B + Tree stores the secondary key. Table data is stored in a separate place, the leaf nodes of the two B + trees use an address to point to the real table data, and there is no difference between the two keys for table data. Because the index tree is independent, retrieve the index tree without accessing the primary key through the secondary key.

To better visualize the differences between the two indexes, we imagine a table that stores 4 rows of data. Where ID is the primary index, name is the secondary index. The diagram clearly shows the difference between the clustered index and the non-clustered index.

We focus on clustered indexes, which appear to be significantly less efficient than nonclustered indexes, since each use of a secondary index is two times the B + Tree lookup, isn't it superfluous? What are the advantages of clustered indexes?

1 because the row data and leaf nodes are stored together so that the primary key and row data are loaded together into memory, the leaf node can be found immediately return the row data, if you follow the primary key ID to organize data, to obtain data faster.

2 Secondary indexes The advantage of using a primary key as a pointer instead of using an address value as a pointer is to reduce the maintenance of the secondary index when a row move or data page splits, using a primary key value as a pointer to make the secondary index occupy more space. The benefit is that INNODB does not need to update this "pointer" in the secondary index while it is moving. That is, the location of the line (implemented in the 16K page to locate, will be involved later) will be changed with the data in the database changes (the previous B + tree node splitting and page splitting), using the clustered index can be guaranteed regardless of the primary key B + tree node changes, the secondary index tree is not affected.

MySQL's storage engine and index