MySQLのLIMIT文について詳しく説明します

質問

ストーリーがスムーズに展開するためには、まず表が必要です。

テーブルtを作成(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    キー1 VARCHAR(100)、
    共通フィールド VARCHAR(100)、
    主キー (id)、
    キー idx_key1 (キー1)
) エンジン=InnoDB CHARSET=utf8;

テーブル t には 3 つの列が含まれており、id 列は主キーであり、key1 列はセカンダリインデックス列です。テーブルには 10,000 件のレコードが含まれています。

次のステートメントを実行すると、セカンダリインデックス idx_key1 が使用されます。

mysql> EXPLAIN SELECT * FROM t ORDER BY key1 LIMIT 1;
+----+-------------+---------+-----------+--------+---------------+-----------+-------+------+------+------+------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+---------+-----------+--------+---------------+-----------+-------+------+------+------+------+
| 1 | SIMPLE | t | NULL | インデックス | NULL | idx_key1 | 303 | NULL | 1 | 100.00 | NULL |
+----+-------------+---------+-----------+--------+---------------+-----------+-------+------+------+------+------+
セットに 1 行、警告 1 件 (0.00 秒)

セカンダリインデックス idx_key1 では、key1 列が順序付けられているため、これは簡単に理解できます。クエリが key1 列でソートされた最初のレコードを取得する場合、MySQL は idx_key1 から最初のセカンダリインデックスレコードを取得するだけで、その後テーブルに直接戻って完全なレコードを取得できます。

ただし、上記のステートメントの LIMIT 1 を LIMIT 5000, 1 に変更すると、テーブル全体をスキャンしてファイルソートを実行する必要があります。実行プランは次のようになります。

mysql> EXPLAIN SELECT * FROM t ORDER BY key1 LIMIT 5000, 1;
+----+-------------+--------+-----------+--------+---------------+-------+-------+-------+---------+----------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+--------+-----------+--------+---------------+-------+-------+-------+---------+----------------+
| 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 9966 | 100.00 | ファイルソートを使用 |
+----+-------------+--------+-----------+--------+---------------+-------+-------+-------+---------+----------------+
セットに 1 行、警告 1 件 (0.00 秒)

学生の中には理解していない人もいます: LIMIT 5000, 1 ではセカンダリインデックス idx_key1 も使用できます。最初に 5001 番目のセカンダリインデックスレコードをスキャンし、次に 5001 番目のセカンダリインデックスレコードに対してテーブルリターン操作を実行できます。このコストは、完全なテーブルスキャン + ファイルソートよりも確実に優れています。

残念ながら、MySQL の実装に欠陥があるため、上記の理想的な状況は発生しません。愚かにも、完全なテーブルスキャン + ファイルソートが実行されるだけです。何が起こっているのか説明しましょう。

サーバー層とストレージエンジン層

ご存知のとおり、MySQL は実際にはサーバー層とストレージエンジン層に分かれています。

サーバー層は、接続管理、SQL 構文の解析、実行プランの分析など、いくつかの一般的な処理を担当します。
ストレージエンジンレイヤーは、データがファイルに保存されるかメモリに保存されるか、特定のストレージ形式は何かなど、特定のデータストレージを担当します。現在は基本的に InnoDB ストレージエンジンを使用しており、他のストレージエンジンはほとんど使用されていないため、他のストレージエンジンは使用しません。

MySQL で SQL ステートメントを実行するには、最終結果を得るためにサーバー層とストレージエンジン層の間で複数のやり取りが必要になります。たとえば、次のクエリを考えてみましょう。

SELECT * FROM t WHERE key1 > 'a' AND key1 < 'b' AND common_field != 'a';

サーバー層は、上記のステートメントが次の 2 つのソリューションを使用して実行できることを分析します。

解決策1: テーブル全体のスキャンを使用する
解決策 2: セカンダリインデックス idx_key1 を使用します。この場合、key1 列の値が ('a'、'b') の間であるすべてのセカンダリインデックスレコードをスキャンする必要があり、各セカンダリインデックスレコードをバックリストする必要があります。

サーバー層は、上記の 2 つのソリューションのどちらがコストが低いかを分析し、コストが低いソリューションを実行プランとして選択します。次に、ストレージエンジンによって提供されるインターフェイスが呼び出され、実際にクエリが実行されます。

ここでは、ソリューション 2 が採用され、セカンダリインデックス idx_key1 を使用して上記のクエリを実行すると仮定します。サーバー層とストレージエンジン層間の会話は次のようになります。

サーバー層:「idx_key1 セカンダリインデックスの ('a', 'b') 間隔の最初のレコードをチェックして、テーブルを返した後、完全なレコードを返してください。」

InnoDB は、「わかりました。すぐに確認します。」と応答します。次に、InnoDB は、idx_key1 セカンダリインデックスに対応する B+ ツリーを通じて、スキャン間隔内の最初のセカンダリインデックスレコード ('a'、'b') をすばやく見つけ、完全なクラスター化インデックスレコードをサーバーレイヤーに返します。

完全なクラスター化インデックスレコードを受信した後、サーバーレイヤーは common_field!='a' 条件が満たされているかどうかを引き続き判断します。満たされていない場合はレコードが破棄され、満たされている場合はレコードがクライアントに送信されます。次にストレージエンジンに「次のレコードをください」と伝えます。

ヒント:
ここで、レコードをクライアントに送信すると、実際にはローカルネットワークバッファーに送信されます。バッファーサイズは net_buffer_length によって制御され、デフォルトのサイズは 16 KB です。ネットワークパケットは、バッファがいっぱいになった場合にのみ実際にクライアントに送信されます。

InnoDB: 「分かりました。すぐに確認します。」 InnoDB は、レコードの next_record 属性に基づいて、idx_key1 の ('a', 'b') 間隔で次のセカンダリインデックスレコードを見つけ、テーブル返却操作を実行して、取得した完全なクラスター化インデックスレコードをサーバーレイヤーに返します。

ヒント:
クラスター化インデックスレコードとセカンダリインデックスレコードの両方に、next_record という属性が含まれています。各レコードは next_record に基づいてリンクリストに接続され、リンクリスト内のレコードはキー値によって並べ替えられます (クラスター化インデックスの場合、キー値はプライマリキーの値を参照し、セカンダリインデックスレコードの場合、キー値はセカンダリインデックス列の値を参照します)。

...そして、上記のプロセスを何度も繰り返します。

それまで：

つまり、InnoDB は、セカンダリインデックスレコードの next_record に従って取得された次のセカンダリインデックスレコードが間隔 ('a'、'b') 内にないことを検出するまで、サーバーレイヤーに「間隔 ('a'、'b') 内に次のレコードはありません」と伝えます。

サーバー層は、InnoDB から次のレコードがないというメッセージを受信すると、クエリを終了します。

これで、サーバー層とストレージエンジン層間の基本的な相互作用プロセスが誰でも理解できました。

LIMITって何ですか？

MySQL は、サーバー層がクライアントにレコードを送信する準備ができたときにのみ、LIMIT 句の内容を処理すると言うと、少し驚かれるかもしれません。次の文を例に挙げます。

SELECT * FROM t ORDER BY key1 LIMIT 5000, 1;

idx_key1 を使用して上記のクエリを実行すると、MySQL は次のように処理します。

サーバーレイヤーは InnoDB に最初のレコードを要求します。InnoDB は idx_key1 から最初のセカンダリインデックスレコードを取得し、テーブル返還操作を実行して完全なクラスター化インデックスレコードを取得してから、それをサーバーレイヤーに返します。サーバー層はクライアントに送信する準備ができており、LIMIT 5000, 1 という要件があることが分かりました。これは、条件を満たす 5001 番目のレコードのみが実際にクライアントに送信できることを意味します。ここで統計を実行してみましょう。サーバー層は、スキップされたレコードの数をカウントするための limit_count という変数を保持していると仮定します。この時点で、limit_count は 1 に設定されている必要があります。
次に、サーバーレイヤーは InnoDB に次のレコードを要求します。InnoDB は、セカンダリインデックスレコードの next_record 属性に基づいて次のセカンダリインデックスレコードを見つけ、完全なクラスター化インデックスレコードをサーバーレイヤーに返します。サーバー層がクライアントに送信すると、limit_count が 1 しかないことが分かるので、クライアントに送信する操作を諦めて、limit_count を 1 増やします。このとき、limit_count は 2 になります。
...上記の手順を繰り返します
limit_count が 5000 の場合、サーバー層は実際に InnoDB から返された完全なクラスター化インデックスレコードをクライアントに送信します。

上記のプロセスから、MySQL はレコードが実際にクライアントに送信されるまで LIMIT 句が要件を満たしているかどうかを判断しないため、セカンダリインデックスを使用して上記のクエリを実行すると、5001 回のテーブル返却操作が必要になることがわかります。実行プランを分析すると、サーバー層は、多数のテーブルを返すことのコストが高すぎると感じ、直接的なフルテーブルスキャン + ファイルソートほど高速ではないため、後者を選択してクエリを実行します。

何をするか？

MySQL の LIMIT 句の実装の制限により、LIMIT 5000, 1 などのステートメントを処理するときにセカンダリインデックスを使用してクエリを高速化することはできないのでしょうか。実際はそうではありません。上記の文を次のように書き直してください。

SELECT * FROM t、(SELECT id FROM t ORDER BY key1 LIMIT 5000, 1) AS d
    ここで、t.id = d.id;

このように、SELECT id FROM t ORDER BY key1 LIMIT 5000, 1 は別のサブクエリとして存在します。サブクエリのクエリリストには id 列が 1 つしかないため、MySQL はセカンダリインデックス idx_key1 のみをスキャンしてサブクエリを実行し、サブクエリで取得したプライマリキー値に基づいてテーブル t を検索することができます。

これにより、最初の 5,000 件のレコードについてテーブルに戻る必要がなくなり、クエリの効率が大幅に向上します。