最近、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 ウェアハウス設定に基づく)
目次1. テンプレート文字列とは何ですか? 2. 複数行のテンプレート文字列2.1 式付きテンプレー...
説明する: fuser は、現在ディスク上のファイル、マウント ポイント、さらにはネットワーク ポー...
この記事では、WeChatアプレットで星評価を実装するための具体的なコードを参考までに紹介します。具...
インデックスを使用してクエリを高速化する1. はじめにWeb 開発には、ビジネス テンプレート、ビジ...
HTML でアンカーの位置を設定する方法はいくつかあるので、ここで紹介します。 1. ID ポジショ...
目次1. キャラクター文法パラメータ索引戻り値2. 連結文法パラメータ文字列2 [, …文字列N]戻...
目次1. ステートメントを挿入する1.1 行を挿入する1.2 複数行を挿入する1.3 クエリステート...
docker パッケージング Python 環境の手順は次のとおりです。 1 pip listの下に...
定義と使用@media クエリを使用すると、さまざまなメディア タイプに異なるスタイルを定義できます...
MySQL は、myisam、innodb、memory、archive、example など、多く...
この記事では、トランザクション、インデックス、ロックなどの MySQL の知識ポイントの原理と使用法...
これは多くの人がやったことがあるはずです。ただうずうずして書きたかったので、時間をかけていじってダー...
序文: Vue では、props を使用して、もともと分離されていたコンポーネントを直列に接続するこ...
Ubuntu は、Linux をベースにした無料のオープンソース デスクトップ PC オペレーティン...
序文PIPE、パイプラインと翻訳されます。 Angular パイプは、HTML コンポーネントで宣言...