MySQL learning tutorial clustered index

Clustering is actually relative to the InnoDB database engine. Therefore, when clustering indexes, we use the two MySQL database engines, InnoDB and MyISAM.

Comparison of data distribution between InnoDB and MyISAM

CREATE TABLE test (col1 int NOT NULL,
     col2 int NOT NULL,
     PRIMARY KEY(col1),
     KEY(col2));

First, create a table through the above SQL statement, where col1 is the primary key and indexes are created for both columns of data. Then the primary key value of our data is 1-10000 and inserted into the database in a random order.

MyISAM data distribution

The data storage logic of MyISAM is relatively simple, which is to create a data table in the order in which data is inserted. Intuitively, the following figure is shown:

It can be seen that the data is generated "row by row" in the order of insertion. There is also a row number field in front of it, which is used to quickly locate the row index when the index is found.

Let's look at the specific details:

The above figure shows the specific implementation of the index established according to the primary key under the MyISAM engine. It can be seen that while the primary keys are arranged in order on the leaf nodes, the nodes also store the specific row number of the primary key in the database table. As we said above, this row number can help us quickly locate the position of the data in the table. This row number can also be understood as a pointer pointing to the specific data row where the primary key is located.

So what if we create an index based on col2? Will it be any different? The answer is no:

So the conclusion is that there is actually no difference whether the index created in MyISAM is a primary key index or not. The only difference is that it is a "primary key index".

InnoDB data distribution

Because InnoDB supports clustered indexes, the index implementation on MyISAM is different.

Let's first look at how the clustered index based on the primary key is implemented on InnoDB:

First, like the primary key index on MyISAM, the leaf nodes of the index here also include the primary key values, and the primary key values ​​are arranged in sequence. The difference is that each leaf node also includes the transaction id, rollback pointer and other non-primary key column values ​​(col2 here). So we can understand that the clustered index on InnoDB arranges all the row data in the original table according to the primary key and then places it on the leaf node of the index. This is a difference from MyISAM in the primary key index. After finding the corresponding primary key value, the MyISAM primary key index needs to use the pointer (row number) to find the corresponding data row in the table. The InnoDB primary key index puts all the data information in the index, which can be directly searched in the index.

Let's take a look at the secondary index in InnoDB:

It can be seen that, unlike the primary key index in InnoDB, the secondary index does not store all row data information in the leaf node, but only stores the primary key information corresponding to the data row in addition to the value of the index column. We know that in MyISAM, the secondary index is the same as the primary key index. In addition to the value of the index column, it only stores a pointer (row number) information.

Compare the secondary indexes on the two engines. That is, the advantages and disadvantages of storing pointers and storing primary key values.

Storing the primary key value first will incur more space overhead than just storing a pointer. However, when our data table is split or undergoes other structural changes, the index storing the primary key value will not be affected, but the index storing the pointer may need to be updated and maintained again.

Compare the two indexes in the two engines with a single graph:

Summarize

This is the end of this article about the MySQL learning tutorial on clustered indexes. For more relevant MySQL clustered index content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!