Mysql 范围查询索引失效在使用 Mysql 查询查询时，我们经常使用范围查询来进行数据分页展示。然后有时却发现并不能

在使用 Mysql 查询查询时，我们经常使用范围查询来进行数据分页展示。例如：select * from page where update_time < {end} and update_time > {start} order by template_id limit 20 即使我们创建了索引 update_time, 选择不同的 end 和 start, explain 的结果也各有不同。有时会产生全表扫描，从而导致线上事故。

实验

当where条件后边是一些范围比较时，例如 !=, >, <, >=, <=, between, in这些，到底什么时候走索引，什么时候走全表扫描，我们结合下边的例子来看。

CREATE TABLE `page` (
  `page_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'page_id',
  `template_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '模版id',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`page_id`),
  KEY `idx_update_time` (`update_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='';

查询近一个月内 top 20 的 page 信息, 观察 sql 的执行计划。

explain select * from page where update_time < '2020-12-01 03:22:15' and update_time > '2020-11-01 03:22:15' order by template_id limit 20

可以看到，在 type=All 即使用了全表扫描; rows=1161 即大概要扫描 1161 行数据; filtered=11.11 即查询结果大概占 rows * 11.11%; Extra = Using where; Useing filesort 即使用条件查询和文件排序。

查询近一个周内 top 20 的 page 信息, 观察 sql 的执行计划。

explain select * from page where update_time < '2020-12-01 03:22:15' and update_time > '2020-11-23 03:22:15' order by template_id limit 20

可以看到，在 type=range 即使用了范围查询; rows=842 即大概要扫描 842 行数据; filtered=100.00 即查询结果大概占 rows * 100.00%; Extra = Using index condition; Useing filesort 即使用索引查询和文件排序。
我们可以看到，同样的 sql 语句由于查询范围的不同，执行计划也各不相同，一个使用索引，一个全表扫描。

问题分析

同样的 sql 语句，为什么优化器使用的执行计划却不相同，我们可以从 Mysql 的官方文档中寻找答案。大概意思是说：是否使用索引查询取决于优化器是否相信索引查询比全表查询效率更高。如果一次查询，使用索引能够过滤 70% 以上的数据则优化器会选择索引查询。当然，这个比例并不是十分固定，优化器还会评估表的行数，I/O块的大小等一系列因素来决定使用哪种方式查询数据。 Each table index is queried, and the best index is used unless the optimizer believes that it is more efficient to use a table scan. At one time, a scan was used based on whether the best index spanned more than 30% of the table, but a fixed percentage no longer determines the choice between using an index or a scan. The optimizer now is more complex and bases its estimate on additional factors such as table size, number of rows, and I/O block size.

解决方案

强制使用索引，我们修改 sql 语句来强制指定索引查询。

select * from page force index(idx_update_time) where update_time < '2020-12-01 03:22:15' and update_time > '2020-11-01 03:22:15' order by template_id limit 20

分开查询，我们可以将大的范围查询拆分成多个小的范围查询，从而保证查询走索引，但需要业务逻辑层做而外处理。此外，我们没办法掌控最小查询范围的粒度。

select * from page force index(idx_update_time) where update_time < '2020-12-01 03:22:15' and update_time > '2020-11-21 03:22:15' order by template_id limit 20
select * from page force index(idx_update_time) where update_time < '2020-11-21 03:22:15' and update_time > '2020-11-11 03:22:15' order by template_id limit 20
select * from page force index(idx_update_time) where update_time < '2020-11-11 03:22:15' and update_time > '2020-11-01 03:22:15' order by template_id limit 20

使用子查询，可先使用索引查出 page_id,然后通过 page_id 回表查询指定数据。可以看到，先使用索引 update_time 查询出 page_id, 然后在通过 page_id 查询符合条件的数据。

explain select * from page inner join (select page_id from page where update_time < '2020-12-01 03:22:15' and update_time > '2020-11-01 03:22:15') t on page.page_id = t.page_id order by template_id limit 20