MySQL クラスター化インデックスのページ分割原理の分析例

この記事では、MySQL クラスター化インデックスのページ分割を例を使って説明します。ご参考までに、詳細は以下の通りです。

MySQL では、MyISAM は非クラスター化インデックスを使用し、InnoDB ストレージ エンジンはクラスター化インデックスを使用します。

クラスター構造の特徴:

• 主キーに基づいてエントリをクエリする場合、行に戻る必要はありません（データは主キーノードの下にあります）
• 不規則なデータが挿入されると、ページ分割が頻繁に発生します。

ページ分割はなぜ発生するのでしょうか?

これは、クラスター化インデックスがバランスバイナリツリーアルゴリズムを使用し、各ノードが主キーに対応する行のデータを格納するためです。挿入されたデータの主キーが自己増加していると仮定すると、バイナリツリーアルゴリズムは特定のノードにデータをすばやく追加し、他のノードを移動する必要はありません。ただし、不規則なデータが挿入されると、挿入ごとにバイナリツリーの以前のデータの状態が変更されます。これによりページが分割されます。

テスト：

2つのテーブルを作成する

テーブルt8を作成します(
id int 主キー、
c1 varchar(500)、
c2 varchar(500)、
c3 varchar(500)、
c4 varchar(500)、
c5 varchar(500)、
c6 varchar(500)
) エンジン innodb 文字セット utf8;
テーブルt9を作成します(
id int 主キー、
c1 varchar(500)、
c2 varchar(500)、
c3 varchar(500)、
c4 varchar(500)、
c5 varchar(500)、
c6 varchar(500)
) エンジン innodb 文字セット utf8;

10,000 個の不規則な主キー データと 10,000 個の通常の主キー データを挿入する PHP スクリプトを記述して、違いを確認します。

<?php
時間制限を設定する(0);
ローカルホストに接続します。
mysql_query('テストを使用します;');
// 主キーを自己増分する $str = str_repeat('a', 500);
開始時刻をマイクロタイムに設定します。
($i=1;$i<=10000;$i++) の場合{
 mysql_query("t8 値に挿入($i、'$str'、'$str'、'$str'、'$str'、'$str'、'$str')");
}
終了時間 = マイクロタイム(true);
echo $endTime-$startTime.'<br/>';
//順序付けられていない主キー $arr = range(1, 10000);
シャッフル($arr);
開始時刻をマイクロタイムに設定します。
foreach($arr を $i として){
 mysql_query("t9 値に挿入($i、'$str'、'$str'、'$str'、'$str'、'$str'、'$str')");
}
終了時間 = マイクロタイム(true);
echo $endTime-$startTime.'<br/>';

テスト結果グラフ

10,000 ルールのデータ: 998 秒 = 16 分
10,000 個の不規則データ: 1939 秒 = 32 分

結論は：

クラスター化インデックスの主キー値は、ランダムな値ではなく、継続的に増加する値にする必要があります (ランダムな文字列または UUID を使用しないでください)。そうしないと、多数のページ分割とページ移動が発生します。 InnoDB を使用する場合、次のように定義するのが最適です。

id int unsigned primary key auto_increment

MySQL 関連のコンテンツに興味のある読者は、このサイトの次のトピックをチェックしてください: 「MySQL インデックス操作スキルの概要」、「MySQL 共通関数の概要」、「MySQL ログ操作スキルの概要」、「MySQL トランザクション操作スキルの概要」、「MySQL ストアド プロシージャ スキルの概要」、および「MySQL データベース ロック関連スキルの概要」。

この記事が皆様のMySQLデータベース設計に役立つことを願っています。