WEB开发者-分享WEB开发知识,让开发变得更简单!

深入解析MySQL中的LIMIT语句

本篇文章带大家了解一下MySQL中的LIMIT语句,聊聊一个问题--MySQL的LIMIT这么差劲的吗?希望对大家有所帮助!

深入解析MySQL中的LIMIT语句

最近有多个小伙伴在答疑群里问了小孩子关于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;

表t包含3个列,id列是主键,key1列是二级索引列。表中包含1万条记录。【相关推荐: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列排序的第1条记录,那MySQL只需要从idx_key1中获取到第一条二级索引记录,然后直接回表取得完整的记录即可。

但是如果我们把上边语句的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 > 'a' AND key1 < 'b' AND common_field != 'a';

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

  • 方案一:使用全表扫描

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

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

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

深入解析MySQL中的LIMIT语句

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

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

吐个槽

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

更多编程相关知识,请访问:编程视频!!

以上就是深入解析MySQL中的LIMIT语句的详细内容,更多请关注web开发者其它相关文章!

本文链接:https://www.webkfz.com/sjk/sjkzh/1n6s.html

版权声明:站内所有文章皆来自网络转载,只供分享作用,不代表本站的观点!

发表评论 共有 0 条评论)

联系客服
网站客服 业务合作 QQ
1244305267
公众号
公众号
公众号
返回顶部