What is the page splitting principle of clustered index in mysql?

This article introduces what is the principle of page splitting in mysql clustering index, the content is very detailed, interested friends can refer to, hope to be helpful to you.

The characteristics of clustering structure:

When querying entries based on the primary key, there is no need to return the line (the data is under the primary key node)

If you encounter irregular data insertion, it will cause frequent page splitting.

Why does a page split occur?

This is because the clustering index uses the balanced binary tree algorithm, and each node stores the data corresponding to the primary key, assuming that the primary key inserted into the data is self-growing. then according to the binary tree algorithm, the data will be quickly added to a node, while other nodes do not need to move; but if the insertion is irregular data, then each insertion will change the data state before the binary tree. This causes the page to split.

Test:

Create 2 tables

Create table T8 (id int primary key,c1 varchar, c2 varchar, c3 varchar, c4 varchar, c5 varchar, c6 varchar) engine innodb charset utf8;create table T9 (id int primary key,c1 varchar, c2 varchar, c3 varchar, c4 varchar, c5 varchar, c6 varchar) engine innodb charset utf8

Write a php script to insert 1W pieces of irregular primary key data and 1W pieces of rule primary key data to see the difference.