本文實例講述了mysql聚簇索引的頁分裂。分享給大家供大家參考，具體如下：
在MySQL中，MyISAM采用的是非聚簇索引的，InnoDB存儲引擎是采用聚簇索引的。
聚簇結構的特點：
- 根據主鍵查詢條目時,不用回行(數據就在主鍵節點下)
- 如果碰到不規則數據插入時,造成頻繁的頁分裂
為什么會產生頁分裂？
這是因為聚簇索引采用的是平衡二叉樹算法，而且每個節點都保存了該主鍵所對應行的數據，假設插入數據的主鍵是自增長的，那么根據二叉樹算法會很快的把該數據添加到某個節點下，而其他的節點不用動；但是如果插入的是不規則的數據，那么每次插入都會改變二叉樹之前的數據狀態。從而導致了頁分裂。
測試：
創建2張表
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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條規則的主鍵數據，來看看區別。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
<?phpset_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數據庫計有所幫助。
原文鏈接：https://blog.csdn.net/baochao95/article/details/61924952
