最近、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 は実際にはサーバー層とストレージ エンジン層に分かれています。
MySQL で SQL ステートメントを実行するには、最終結果を得るためにサーバー層とストレージ エンジン層の間で複数のやり取りが必要になります。たとえば、次のクエリを考えてみましょう。 SELECT * FROM t WHERE key1 > 'a' AND key1 < 'b' AND common_field != 'a'; サーバー層は、上記のステートメントが次の 2 つのソリューションを使用して実行できることを分析します。
サーバー層は、上記の 2 つのソリューションのどちらがコストが低いかを分析し、コストが低いソリューションを実行プランとして選択します。次に、ストレージ エンジンによって提供されるインターフェイスが呼び出され、実際にクエリが実行されます。 ここでは、ソリューション 2 が採用され、セカンダリ インデックス idx_key1 を使用して上記のクエリを実行すると仮定します。サーバー層とストレージ エンジン層間の会話は次のようになります。 サーバー層:「idx_key1 セカンダリ インデックスの ('a', 'b') 間隔の最初のレコードをチェックして、テーブルを返した後、完全なレコードを返してください。」 InnoDB は、「わかりました。すぐに確認します。」と応答します。次に、InnoDB は、idx_key1 セカンダリ インデックスに対応する B+ ツリーを通じて、スキャン間隔内の最初のセカンダリ インデックス レコード ('a'、'b') をすばやく見つけ、完全なクラスター化インデックス レコードをサーバー レイヤーに返します。 完全なクラスター化インデックス レコードを受信した後、サーバー レイヤーは common_field!='a' 条件が満たされているかどうかを引き続き判断します。満たされていない場合はレコードが破棄され、満たされている場合はレコードがクライアントに送信されます。次にストレージエンジンに「次のレコードをください」と伝えます。
InnoDB: 「分かりました。すぐに確認します。」 InnoDB は、レコードの next_record 属性に基づいて、idx_key1 の ('a', 'b') 間隔で次のセカンダリ インデックス レコードを見つけ、テーブル返却操作を実行して、取得した完全なクラスター化インデックス レコードをサーバー レイヤーに返します。
完全なクラスター化インデックス レコードを受信した後、サーバー レイヤーは 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 は次のように処理します。
上記のプロセスから、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をよろしくお願いいたします。 以下もご興味があるかもしれません:
|
<<: フォームの読み取り専用属性と無効な属性についての簡単な説明
>>: Linux ファイアウォール設定の詳細な手順 (yum ウェアハウス設定に基づく)
目次ダーティページ(メモリページ)ダーティページが表示されるのはなぜですか?メモリ管理メカニズムの簡...
docker イメージ ID は一意であり、イメージを物理的に識別できます。repository: ...
定義と使用法<input> タグはユーザー情報を収集するために使用されます。 type ...
1.コンテナ内の /etc/hosts、/etc/resolv.conf、/etc/hostname...
1. 現在のスクリプトの実際のパスを取得します。 #!/bin/bash if [[ $0 =~ ^...
1. 最初の方法は、unhup コマンドを直接使用してプログラムをバックグラウンドで実行することです...
mysql ストアド プロシージャの概念:特定のタスク (クエリと更新) を実行できる、データベース...
CSS ファイルでは、フォント名が文字化けしていることがよくあります。これは、作成者が中国語フォン...
参考までにWindowsにMySQLをインストールします。具体的な内容は次のとおりです。 1.まずM...
この記事の例では、グラフィック検証コードを実装するためのVueプロジェクトの具体的なコードを参考まで...
目次1. 概要1. 説明文テスト2. 結果の各列の説明2. ID列の説明1. 環境整備2. expl...
目次空の文字列 '' と null を比較するケース1: 空の文字列を使用する ...
CentOS8 に最小限のデプロイメントで OpenStack Ussuri をインストールするため...
この記事では、例を使用して、MySQL がストアド プロシージャで引き起こすエラー状態 (SIGNA...
目次HTML ページの作成js部分の機能を実装する1. 左ボタンと右ボタン2. 小さな円を動的に生成...