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

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

最近、Q&A グループで多くの友人が子供たちに 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 に基づいてリンク リストに接続され、リンク リスト内のレコードはキー値によって並べ替えられます (クラスター化インデックスの場合、キー値はプライマリ キーの値を参照し、セカンダリ インデックス レコードの場合、キー値はセカンダリ インデックス列の値を参照します)。

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

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

それまで:

つまり、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 件のレコードについてテーブルに戻る必要がなくなり、クエリの効率が大幅に向上します。

トゥカオ

MySQL を設計した人たちは、この非常に愚かな LIMIT 句の実装をいつ修正するのでしょうか?クエリの効率を向上させるには、ユーザーは手動でオプティマイザーを欺く必要があります。

これでMySQLのLIMIT文に関する記事は終了です。MySQLのLIMIT文についてさらに詳しく知りたい方は、123WORDPRESS.COMの過去の記事を検索するか、以下の関連記事を引き続きご覧ください。今後とも123WORDPRESS.COMをよろしくお願いいたします。

以下もご興味があるかもしれません:
  • MySQL 選択最適化ソリューションに関する簡単な説明
  • MySQL で結果を選択して更新を実行する例のチュートリアル
  • MySQLの読み書き分離により挿入後にデータが選択されなくなる問題を解決
  • MySQL SELECT文の実行方法
  • MySQL で distinct メソッドを使用する詳細な例
  • MySQL で重複を削除するには、distinct または group by を使用する必要がありますか?
  • MySQL における distinct と group by の違い
  • union (all) と limit および exists キーワードの使用法を理解するための MySQL シリーズチュートリアル
  • MySQL のクエリパフォーマンスに対する制限の影響
  • MySQL での select、distinct、limit の使用

<<:  フォームの読み取り専用属性と無効な属性についての簡単な説明

>>:  Linux ファイアウォール設定の詳細な手順 (yum ウェアハウス設定に基づく)

推薦する

JS ES 新機能テンプレート文字列

目次1. テンプレート文字列とは何ですか? 2. 複数行のテンプレート文字列2.1 式付きテンプレー...

Linux での fuser コマンドの使用法の詳細な説明

説明する: fuser は、現在ディスク上のファイル、マウント ポイント、さらにはネットワーク ポー...

WeChatミニプログラムが星評価を実装

この記事では、WeChatアプレットで星評価を実装するための具体的なコードを参考までに紹介します。具...

MySql インデックスはクエリ速度を向上させる一般的な方法のコード例

インデックスを使用してクエリを高速化する1. はじめにWeb 開発には、ビジネス テンプレート、ビジ...

HTMLでアンカーの位置を設定するためのいくつかの一般的な方法

HTML でアンカーの位置を設定する方法はいくつかあるので、ここで紹介します。 1. ID ポジショ...

JavaScript 文字列の一般的なメソッドの詳細な説明

目次1. キャラクター文法パラメータ索引戻り値2. 連結文法パラメータ文字列2 [, …文字列N]戻...

MySQL学習データベース操作DML初心者向け詳細解説

目次1. ステートメントを挿入する1.1 行を挿入する1.2 複数行を挿入する1.3 クエリステート...

DockerでPython環境をパッケージ化するプロセスの詳細な説明

docker パッケージング Python 環境の手順は次のとおりです。 1 pip listの下に...

@media レスポンシブ CSS を使用してさまざまな画面に適応する例

定義と使用@media クエリを使用すると、さまざまなメディア タイプに異なるスタイルを定義できます...

Mysql InnoDBとMyISAMの違いの分析

MySQL は、myisam、innodb、memory、archive、example など、多く...

MySql の知識ポイント: トランザクション、インデックス、ロックの原則、使用状況の分析

この記事では、トランザクション、インデックス、ロックなどの MySQL の知識ポイントの原理と使用法...

CSS3 でのシンプルな LED デジタル時計の実装方法

これは多くの人がやったことがあるはずです。ただうずうずして書きたかったので、時間をかけていじってダー...

Vue での props の使い方の紹介

序文: Vue では、props を使用して、もともと分離されていたコンポーネントを直列に接続するこ...

UbuntuにMySQLデータベースをインストールする方法

Ubuntu は、Linux をベースにした無料のオープンソース デスクトップ PC オペレーティン...

AngularパイプラインPIPEの紹介と使い方

序文PIPE、パイプラインと翻訳されます。 Angular パイプは、HTML コンポーネントで宣言...