order by、limit对mysql执行计划的影响实例

最新推荐文章于 2023-05-23 17:50:44 发布

weixin_34220834

最新推荐文章于 2023-05-23 17:50:44 发布

阅读量432

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/hebaodan/blog/1547255

版权

2019独角兽企业重金招聘Python工程师标准>>>

问题引入

order by、limit语句会影响mysql索引执行计划，mysql可能会使用我们认为不合理的索引，（但对mysql自身来说认为是合理的）。

具体实例

表结构简化后：

CREATE TABLE `test` (
   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增长主键',
   `shop_id` int(11) NOT NULL COMMENT '商户id',
   `booking_time` datetime NOT NULL COMMENT '用餐时间',
   `created_time` datetime NOT NULL COMMENT '创建时间',
   `updated_time` datetime NOT NULL COMMENT '更新时间',
   `other_column` int(11) NOT NULL DEFAULT '0' COMMENT '其他还有约20个未列举出来的字段',
   PRIMARY KEY (`id`),
   KEY `IX_shop_id_booking_time` (`shop_id`,`booking_time`),
   KEY `IX_updated_time` (`updated_time`),
   KEY `IX_created_time` (`created_time`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='示例表'

SQL语句简化后：

1、select count(*) from test where shop_id = 12594791; 有3w条数据

2、explain select * from test where shop_id = 12594791 order by created_time;

执行计划为： type ref

possible_keys IX_created_time,IX_shop_id_booking_time

key IX_shop_id_booking_time

rows 56920

Extra Using index condition; Using filesort

即通过索引IX_shop_id_booking_time查找数据后，mysql对数据集排序，这里的Using filesort并不一定是磁盘排序，也可能是在内存中完成，与缓冲区大小相关，更多可google之，这里提供一篇可参考文章：http://www.linuxidc.com/Linux/2016-08/133838.htm

3、explain select * from test where shop_id = 12594791 order by created_time limit 0, 20;

执行计划主要内容为：

type index

possible_keys IX_created_time,IX_shop_id_booking_time

key IX_created_time

rows 10697

Extra Using where

即通过扫描索引树IX_created_time，然后回表查询数据，用where条件过滤，直到扫描得到的结果集大小有20条数据就终止。

可以看到，加上limit 0， 20后执行计划有所变化，因为mysql查询优化器主要是基于成本考虑的，这里成本主要指读取数据的IO成本。mysql认为针对这次查询只需顺序扫描created_time索引，然后回表查询过滤就能得到结果，而不需要额外的排序代价了，当然，这里与特定条件的数据相关，shop_id = 12594791的数据有3w条，排序的代价很高。如果是数据较小的shop_id，仍然会采用IX_shop_id_booking_time索引。

经过上面的解析，我们可以猜测，如果limit的偏移量较大的话，通过顺序扫描created_time的数据会很多而导致扫描行数激增，那么仍然会用IX_shop_id_booking_time索引。接下来的验证情况：

4、explain select * from YY_BookingRecord where shop_id = 12594791 order by created_time limit 1000, 20;

执行计划主要内容为： type ref

possible_keys IX_created_time,IX_shop_id_booking_time

key IX_shop_id_booking_time

rows 56956

Extra Using index condition; Using filesort

与sql语句2的执行计划相同