mysql中聚簇索引的頁分裂原理是什么

這篇文章給大家介紹mysql中聚簇索引的頁分裂原理是什么，內容非常詳細，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

聚簇結構的特點：

• 根據主鍵查詢條目時,不用回行(數據就在主鍵節點下)

• 如果碰到不規則數據插入時,造成頻繁的頁分裂

為什么會產生頁分裂？

這是因為聚簇索引采用的是平衡二叉樹算法，而且每個節點都保存了該主鍵所對應行的數據，假設插入數據的主鍵是自增長的，那么根據二叉樹算法會很快的把該數據添加到某個節點下，而其他的節點不用動；但是如果插入的是不規則的數據，那么每次插入都會改變二叉樹之前的數據狀態。從而導致了頁分裂。

測試：

創建2張表

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;

寫一個php腳本，用于插入1W條無規則的主鍵數據和1W條規則的主鍵數據，來看看區別。

<?php
set_time_limit(0);
$conn = mysql_connect('localhost','root','1234');
mysql_query('use test;');
//自增長主鍵
$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/>';
//無序的主鍵
$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/>';

測試結果圖

1W條規則的數據：998秒 = 16分鐘
1W條不規則的數據：1939秒 = 32分鐘

結論：

聚簇索引的主鍵值,應盡量是連續增長的值,而不是要是隨機值, (不要用隨機字符串或UUID)，否則會造成大量的頁分裂與頁移動。在使用InnoDB的時候最好定義成：

id int unsigned primary key auto_increment

關于mysql中聚簇索引的頁分裂原理是什么就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。