MySQL の LIMIT ステートメントの詳細な分析

この記事では、MySQL の LIMIT ステートメントを理解し、MySQL の LIMIT はそれほど悪いものなのでしょうか?という質問について説明します。お役に立てれば幸いです!

最近、Q&A グループで多くの友人が子供たちに LIMIT について質問しました。この質問について簡単に説明しましょう。

質問

ストーリーをスムーズに進めるためには、まずテーブルを用意する必要があります。

CREATE TABLE t (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1)
) Engine=InnoDB CHARSET=utf8;

テーブルには 3 つの列が含まれており、 id 列は主キー、 key1 列は副インデックス列です。テーブルには 10,000 レコードが含まれています。 [関連する推奨事項: mysql ビデオ チュートリアル ]

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

mysql>  EXPLAIN SELECT * FROM t ORDER BY key1 LIMIT 1;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | index | NULL          | idx_key1 | 303     | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

これは理解しやすいです。セカンダリ インデックス 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 | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9966 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

一部の学生は理解していません: LIMIT 5000, 1セカンダリ インデックス idx_key1 も使用できます。最初に 5001 番目のセカンダリ インデックス レコードをスキャンしてから、 5001 番目のセカンダリ インデックス レコードが最初です。5001 個のセカンダリ インデックス レコードに対してテーブル リターン操作を実行するのは良いことではないでしょうか? このコストは、フル テーブル スキャン ファイルソートよりも明らかに優れています。

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

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

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

• サーバー層は、接続管理、SQL 構文解析、実行プランの分析など、いくつかの一般的な処理を担当します。

• ストレージ エンジン層は、特定の処理を担当します。データ ストレージ (データがファイルに保存されるかメモリに保存されるか、特定の保存形式は何かなど)。現在は基本的に InnoDB ストレージ エンジンを使用しており、他のストレージ エンジンはほとんど使用されないため、他のストレージ エンジンについては説明しません。

MySQL で SQL ステートメントを実行すると、サーバー層とストレージ エンジン層の間の複数の対話を通じて最終結果が得られます。たとえば、次のクエリ:

SELECT * FROM t WHERE key1 > &#39;a&#39; AND key1 < &#39;b&#39; AND common_field != &#39;a&#39;;

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

• オプション 1: フル テーブルを使用するscan

• オプション 2: セカンダリ インデックス idx_key1 を使用します。この時点で、key1 列の値が ('a', 'b') の間にあるすべてのセカンダリ インデックス レコードをスキャンする必要があります。すべてのレコードをテーブルに返す必要があります。

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

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

サーバー層: 「idx_key1 の ('a', ' を確認してください)セカンダリ インデックス b') 間隔内の最初のレコード、そしてテーブルに戻った後に完全なレコードを返します。"

InnoDB: "受信しました。今すぐ確認してください。" その後、InnoDB は idx_key1 セカンダリを使用します対応する B ツリーは、スキャン間隔 ('a'、'b') 内の最初のセカンダリ インデックス レコードをすばやく見つけ、テーブルを返して完全なクラスター化インデックス レコードを取得し、サーバー層に返します。

サーバー層は完全なクラスター化インデックス レコードを受信した後、

common_field!='a' 条件が true であるかどうかの判断を続けます。 true でない場合、レコードは破棄されます。それ以外の場合、レコードはクライアントに送信されます。次に、ストレージ エンジンに次のように指示します。「次のレコードをください。」

ヒント:

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

InnoDB: "受信しました。今すぐ確認してください。" InnoDB は、レコードの next_record 属性に基づいて idx_key1 の ('a', 'b') 間隔で次のセカンダリ インデックス レコードを検索し、テーブルを返す操作を実行して、完全なクラスター化インデックス レコードをサーバー層に返します。

小贴士：

不论是聚簇索引记录还是二级索引记录，都包含一个称作next_record的属性，各个记录根据next_record连成了一个链表，并且链表中的记录是按照键值排序的（对于聚簇索引来说，键值指的是主键的值，对于二级索引记录来说，键值指的是二级索引列的值）。

server层收到完整的聚簇索引记录后，继续判断common_field!='a'条件是否成立，如果不成立则舍弃该记录，否则将该记录发送到客户端。然后对存储引擎说：“请把下一条记录给我哈”

... 然后就不停的重复上述过程。

直到：

也就是直到InnoDB发现根据二级索引记录的next_record获取到的下一条二级索引记录不在('a', 'b')区间中，就跟server层说：“好了，('a', 'b')区间没有下一条记录了”

server层收到InnoDB说的没有下一条记录的消息，就结束查询。

现在大家就知道了server层和存储引擎层的基本交互过程了。

那LIMIT是什么鬼？

说出来大家可能有点儿惊讶，MySQL是在server层准备向客户端发送记录的时候才会去处理LIMIT子句中的内容。拿下边这个语句举例子：

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

如果使用idx_key1执行上述查询，那么MySQL会这样处理：

• server层向InnoDB要第1条记录，InnoDB从idx_key1中获取到第一条二级索引记录，然后进行回表操作得到完整的聚簇索引记录，然后返回给server层。server层准备将其发送给客户端，此时发现还有个LIMIT 5000, 1的要求，意味着符合条件的记录中的第5001条才可以真正发送给客户端，所以在这里先做个统计，我们假设server层维护了一个称作limit_count的变量用于统计已经跳过了多少条记录，此时就应该将limit_count设置为1。

• server层再向InnoDB要下一条记录，InnoDB再根据二级索引记录的next_record属性找到下一条二级索引记录，再次进行回表得到完整的聚簇索引记录返回给server层。server层在将其发送给客户端的时候发现limit_count才是1，所以就放弃发送到客户端的操作，将limit_count加1，此时limit_count变为了2。

• ... 重复上述操作

• 直到limit_count等于5000的时候，server层才会真正的将InnoDB返回的完整聚簇索引记录发送给客户端。

从上述过程中我们可以看到，由于MySQL中是在实际向客户端发送记录前才会去判断LIMIT子句是否符合要求，所以如果使用二级索引执行上述查询的话，意味着要进行5001次回表操作。server层在进行执行计划分析的时候会觉得执行这么多次回表的成本太大了，还不如直接全表扫描+filesort快呢，所以就选择了后者执行查询。

怎么办？

由于MySQL实现LIMIT子句的局限性，在处理诸如LIMIT 5000, 1这样的语句时就无法通过使用二级索引来加快查询速度了么？其实也不是，只要把上述语句改写成：

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

这样，SELECT id FROM t ORDER BY key1 LIMIT 5000, 1作为一个子查询单独存在，由于该子查询的查询列表只有一个id列，MySQL可以通过仅扫描二级索引idx_key1执行该子查询，然后再根据子查询中获得到的主键值去表t中进行查找。

这样就省去了前5000条记录的回表操作，从而大大提升了查询效率！

吐个槽

设计MySQL的大叔啥时候能改改LIMIT子句的这种超笨的实现呢？还得用户手动想欺骗优化器的方案才能提升查询效率~

更多编程相关知识，请访问：编程视频！！

以上がMySQL の LIMIT ステートメントの詳細な分析の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。