MySQL InnoDBセカンダリインデックスのソート例の詳細な説明

ソート問題

最近、Geek Time の「45 Lectures on MySQL Practice」を読み、InnoDB セカンダリインデックスに関する不十分な理解を修正しました。関連する内容を要約する良い機会です。

PS:この記事のすべてのテストは MySQL 8.0.13 に基づいています。

まず質問させてください。次の SQL によって作成されたテーブルには 2 つのクエリステートメントがあります。どのインデックスが不要でしょうか。

テーブル `geek` を作成します (
 `a` int(11) NULLではない、
 `b` int(11) NULLではない、
 `c` int(11) NULLではない、
 `d` int(11) NULLではない、
 主キー (`a`,`b`)、
 キー `c` (`c`)、
 キー `ca` (`c`,`a`)、
 キー `cb` (`c`,`b`)
)ENGINE=InnoDB;

c=N で geek から * を選択し、制限 1 で順序付けします。
c=N で geek から * を選択し、b で順序を制限し、1 にします。

著者の答えは、インデックス c と ca のデータモデルは同じなので、ca は冗長であるというものです。なぜ？ ?

セカンダリインデックスには行の位置ではなくプライマリキーの値が格納され、インデックスは順序付けられていることもわかっています。

c が ca と同じデータモデルを持つ場合、セカンダリインデックスのリーフノードは、インデックス列だけでなく、関連付けられているプライマリキー値でも並べ替える必要があります。

以前の私の理解では、セカンダリインデックスはインデックス列によってのみソートされ、プライマリキーの値はソートされないというものでした。

コラムニストに尋ねたところ、次のような答えが返ってきました。インデックス c は cab (セカンダリインデックス) のようにソートされ、主キーが含まれ、順序どおりになっていることを確認します。（追記：これは原文ではありません。3回質問してやっと答えが分かりました）。

まず「かどうか」を問い、次に「なぜ」を問うという考えに基づいて、私たちはいくつかの調査を行いました。

はい、もしくは、いいえ？

InnoDB データファイルを直接表示できる場合は、このソートルールに従っているかどうかを直接確認できます。残念ながらバイナリファイルなので、閲覧するのに便利なツールもないので諦めました。

その後、MyISAM エンジンと InnoDB エンジンの両方のテーブルをサポートする MySQL ハンドラーステートメントを見つけました。ハンドラーステートメントは、テーブルストレージエンジンに直接アクセスするためのインターフェイスを提供します。

次の構文は、指定されたテーブル内の指定されたインデックスの最初/前/次/最後のレコードを読み取ることを示します。

ハンドラー table_name/table_name_alias は index_name first/pre/next/last を読み取ります。

ハンドラーステートメントを使用して検証してみましょう。まず、単純なテーブルを作成し、いくつかのデータを挿入します。

テーブル t_simple を作成します (
 id int 主キー、
 v 整数、
 キー k_v (v)
)ENGINE=InnoDB デフォルト文字セット=utf8mb4;

t_simpleに値（1、5）を挿入します。
t_simpleに値（10、5）を挿入します。
t_simpleに値（4、5）を挿入します。

上記の挿入ステートメントでは、セカンダリインデックス列の値は同じであり、プライマリキーの順序が合っていません。このようにして、トラバーサル中にプライマリキーの順序で格納されているかどうかを確認できます。

mysql> ハンドラー t_simple を ts として開きます。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> ハンドラ ts 読み取り k_v 次へ;
+----+------+
| id | v |
+----+------+
| 1 | 5 |
+----+------+
セット内の 1 行 (0.00 秒)

mysql> ハンドラ ts 読み取り k_v 次へ;
+----+------+
| id | v |
+----+------+
| 4 | 5 |
+----+------+
セット内の 1 行 (0.00 秒)

mysql> ハンドラ ts 読み取り k_v 次へ;
+----+------+
| id | v |
+----+------+
| 10 | 5 |
+----+------+
セット内の 1 行 (0.00 秒)

結果から、トラバースされたセカンダリインデックスの値が等しい場合、プライマリキーの順序でトラバースしていることがわかります。セカンダリインデックスは、インデックス列だけでなく、プライマリキーの値でもソートされていることを基本的に判断できます。

なぜ？

これまで MySQL でこのようなメカニズムについて言及されているのを見たことがありませんでしたし、以前の会社やその前の会社の DBA もこのことに気づいていませんでした。

最後に、DBA の同僚は、次の説明を含むインデックス拡張を見つけました。

InnoDB は、プライマリキー列を追加することで、各セカンダリインデックスを自動的に拡張します。次のテーブル定義を検討してください。

テーブルt1を作成します（
 i1 INT NOT NULL デフォルト 0,
 i2 INT NOT NULL デフォルト 0,
 d 日付デフォルト NULL、
 主キー (i1, i2)
 インデックスk_d（d）
)エンジン = InnoDB;

InnoDB は各セカンダリインデックスを自動的に拡張し、プライマリキー値をインデックス列に追加し、拡張された複合列をインデックスのインデックス列として使用します。上記の t_simple テーブルの k_v インデックスの場合、拡張インデックスは (v, id) 列です。

オプティマイザーは、拡張セカンダリインデックスの主キー列に基づいて、そのインデックスを使用するかどうか、およびその使用方法を決定します。オプティマイザーは、ref、range、index_merge などのインデックスアクセスタイプ、ルーズインデックススキャン、結合およびソートの最適化、min()/max() の最適化に拡張セカンダリインデックスを使用できます。

インデックス拡張機能が有効になっているかどうかを確認するには、 show variables like '%optimizer_switch%';有効または無効にするにはSET optimizer_switch = 'use_index_extensions=on/off';を使用します。これは現在のセッションにのみ影響します。

テストの結果、現在のセッションのインデックス拡張がオフになっている場合でも、ハンドラーを使用してアクセスすると、主キーによるソートの効果が得られることがわかりました。

要約する

上記はこの記事の全内容です。この記事の内容が皆さんの勉強や仕事に一定の参考学習価値を持つことを願っています。ご質問があれば、メッセージを残してコミュニケーションしてください。123WORDPRESS.COM を応援していただきありがとうございます。

以下もご興味があるかもしれません: