MySQL의 LIMIT 문에 대한 심층 분석

이 기사에서는 MySQL의 LIMIT 문을 이해하고 질문에 대해 이야기합니다. MySQL의 LIMIT가 그렇게 나쁜가요? 모두에게 도움이 되기를 바랍니다!

최근 Q&A 그룹에서 많은 친구들이 아이들에게 LIMIT에 대해 질문했습니다.

Question

스토리가 원활하게 전개되려면 먼저 테이블이 있어야 합니다.

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;

테이블 t에는 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로 바꾸면 전체 테이블을 스캔하고 파일 정렬을 수행해야 합니다. 실행 계획은 다음과 같습니다. : LIMIT 1换成LIMIT 5000, 1，则却需要进行全表扫描，并进行filesort，执行计划如下：

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条二级索引记录进行回表操作不就好了么，这样的代价肯定比全表扫描+filesort强呀。

很遗憾的告诉各位，由于MySQL实现上的缺陷，不会出现上述的理想情况，它只会笨笨的去执行全表扫描+filesort，下边我们唠叨一下到底是咋回事儿。

server层和存储引擎层

大家都知道，MySQL内部其实是分为server层和存储引擎层的：

• server层负责处理一些通用的事情，诸如连接管理、SQL语法解析、分析执行计划之类的东西

• 存储引擎层负责具体的数据存储，诸如数据是存储到文件上还是内存里，具体的存储格式是什么样的之类的。我们现在基本都使用InnoDB存储引擎，其他存储引擎使用的非常少了，所以我们也就不涉及其他存储引擎了。

MySQL中一条SQL语句的执行是通过server层和存储引擎层的多次交互才能得到最终结果的。比方说下边这个查询：

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

server层会分析到上述语句可以使用下边两种方案执行：

• 方案一：使用全表扫描

• 方案二：使用二级索引idx_key1，此时需要扫描key1列值在('a', 'b')之间的全部二级索引记录，并且每条二级索引记录都需要进行回表操作。

server层会分析上述两个方案哪个成本更低，然后选取成本更低的那个方案作为执行计划。然后就调用存储引擎提供的接口来真正的执行查询了。

这里假设采用方案二，也就是使用二级索引idx_key1执行上述查询。那么server层和存储引擎层的对话可以如下所示：

server层：“hey，麻烦去查查idx_key1二级索引的('a', 'b')区间的第一条记录，然后把回表后把完整的记录返给我哈”

InnoDB：“收到，这就去查”，然后InnoDB就通过idx_key1二级索引对应的B+树，快速定位到扫描区间('a', 'b')的第一条二级索引记录，然后进行回表，得到完整的聚簇索引记录返回给server层。

server层收到完整的聚簇索引记录后，继续判断common_field!='a'

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

일부 학생들은 이해하지 못합니다: LIMIT 5000, 1 보조 인덱스 idx_key1을 사용할 수도 있습니다. 먼저 5001번째 보조 인덱스 레코드를 스캔한 다음 5001번째 보조 인덱스 레코드를 스캔할 수 있습니다. 테이블을 기록하고 테이블로 되돌려 놓는 것이 좋지 않을까요? 이 비용은 확실히 전체 테이블 스캔 + 파일 정렬보다 낫습니다.

MySQL 구현의 결함으로 인해 위의 이상적인 상황은 발생하지 않는다는 점을 말씀드리게 되어 유감입니다. 전체 테이블 스캔 + 파일 정렬만 어리석게 수행하게 됩니다.

서버 계층 및 스토리지 엔진 계층

우리 모두 알고 있듯이 MySQL은 실제로 서버 계층과 스토리지 엔진 계층으로 구분됩니다.

• 🎜서버 스토리지 엔진 계층은 연결 관리, SQL 구문 분석, 실행 계획 분석과 같은 몇 가지 일반적인 작업을 처리합니다. 여기서는 데이터가 파일에 저장되는지 아니면 메모리에 저장되는지와 같은 특정 데이터 저장을 담당합니다. , 구체적인 저장 형식은 무엇입니까? 현재는 기본적으로 InnoDB 스토리지 엔진을 사용하고 있으며, 다른 스토리지 엔진은 거의 사용되지 않으므로 다른 스토리지 엔진은 다루지 않겠습니다. 🎜

🎜MySQL에서 SQL 문을 실행하려면 최종 결과를 얻기 위해 서버 계층과 스토리지 엔진 계층 간의 여러 상호 작용이 필요합니다. 예를 들어 아래 쿼리는 다음과 같습니다. 🎜

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

🎜서버 계층은 위 명령문이 다음 두 가지 옵션을 사용하여 실행될 수 있는지 분석합니다. 🎜

• 🎜옵션 1: 전체 테이블 스캔 사용🎜
• 🎜옵션 2 : 보조 인덱스 idx_key1을 사용합니다. 이때 ('a', 'b') 사이의 key1 컬럼 값이 있는 모든 보조 인덱스 레코드를 스캔해야 하며, 각 보조 인덱스 레코드는 테이블. 🎜

🎜서버 계층은 위의 두 가지 옵션 중 어느 것이 더 낮은 비용인지 분석한 다음 더 낮은 비용의 옵션을 실행 계획으로 선택합니다. 그런 다음 스토리지 엔진에서 제공하는 인터페이스를 호출하여 실제로 쿼리를 실행합니다. 🎜🎜옵션 2가 채택되었다고 가정합니다. 즉, 위 쿼리를 실행하는 데 보조 인덱스 idx_key1이 사용됩니다. 그러면 서버 계층과 스토리지 엔진 계층 간의 대화는 다음과 같을 수 있습니다. 🎜🎜🎜🎜서버 레이어: "야, idx_key1 2차 인덱스의 ('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 중국어 웹사이트의 기타 관련 기사를 참조하세요!