Example analysis of the page splitting principle of MySQL clustered index

This article uses an example to illustrate the page splitting of MySQL clustered indexes. Share with you for your reference, the details are as follows:

In MySQL, MyISAM uses non-clustered indexes, and the InnoDB storage engine uses clustered indexes.

Characteristics of cluster structure:

• When querying entries based on the primary key, there is no need to return to the row (the data is under the primary key node)
• If irregular data is inserted, frequent page splits will occur.

Why does page split occur?

This is because the clustered index uses a balanced binary tree algorithm, and each node stores the data of the row corresponding to the primary key. Assuming that the primary key of the inserted data is self-increasing, the binary tree algorithm will quickly add the data to a certain node, while other nodes do not need to be moved; but if irregular data is inserted, each insertion will change the previous data status of the binary tree. This results in page splits.

test:

Create 2 tables

create table t8(
id int primary key,
c1 varchar(500),
c2 varchar(500),
c3 varchar(500),
c4 varchar(500),
c5 varchar(500),
c6 varchar(500)
) engine innodb charset utf8;
create table t9(
id int primary key,
c1 varchar(500),
c2 varchar(500),
c3 varchar(500),
c4 varchar(500),
c5 varchar(500),
c6 varchar(500)
) engine innodb charset utf8;

Write a PHP script to insert 10,000 irregular primary key data and 10,000 regular primary key data to see the difference.

<?php
set_time_limit(0);
$conn = mysql_connect('localhost','root','1234');
mysql_query('use test;');
//Self-increment primary key $str = str_repeat('a', 500);
$startTime = microtime(true);
for($i=1;$i<=10000;$i++){
 mysql_query("insert into t8 values($i,'$str','$str','$str','$str','$str','$str')");
}
$endTime = microtime(true);
echo $endTime-$startTime.'<br/>';
//Unordered primary key $arr = range(1, 10000);
shuffle($arr);
$startTime = microtime(true);
foreach($arr as $i){
 mysql_query("insert into t9 values($i,'$str','$str','$str','$str','$str','$str')");
}
$endTime = microtime(true);
echo $endTime-$startTime.'<br/>';

Test result graph

Data for 10,000 rules: 998 seconds = 16 minutes
10,000 irregular data: 1939 seconds = 32 minutes

in conclusion:

The primary key value of the clustered index should be a continuously increasing value instead of a random value (do not use a random string or UUID), otherwise it will cause a large number of page splits and page moves. When using InnoDB, it is best defined as:

id int unsigned primary key auto_increment

Readers who are interested in more MySQL-related content can check out the following topics on this site: "Summary of MySQL Index Operation Skills", "Summary of MySQL Common Functions", "Summary of MySQL Log Operation Skills", "Summary of MySQL Transaction Operation Skills", "Summary of MySQL Stored Procedure Skills" and "Summary of MySQL Database Lock-Related Skills".

I hope this article will be helpful to everyone's MySQL database design.