mysql 执行计划 代价_order by、limit对mysql执行计划的影响实例

问题引入

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的执行计划相同

实际问题

线上服务业务执行了sql语句3，虽然mysql认为是较优的执行计划，实际导致了慢查询的出现。

解决方案

针对where条件与order by的条件增加联合索引，对应上述实例即增加shop_id,created_time的联合索引