Mysql中between...and引起的索引失效问题及解决【转】

发生场景

在查询学生表的时候，需要支持根据创建时间来筛选出某段时间内入学的学生总数，因此在创建时间上加了索引，但是最终发现还是会走全量查询。

实验过程

1 2 3 4 5 6 7

CREATE TABLE `t_user` (   `id` bigint(11) unsigned NOT NULL COMMENT '学生id',   `name` varchar(24) NOT NULL COMMENT '学生名称',   `createTime` dat NOT NULL COMMENT '创建时间',   PRIMARY KEY (`id`),   KEY `index_updateTime` (`createTime`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

1	select count(1) from t_user where createTime between '2022-08-30' and '2022-08-31';

使用insert语句插入3万多条数据后，其中30号至31号之前有9千多条数据，发现查询的时候并没有走到索引。

结果如下：

缩小查询区间，只查31号的（4千多条数据），却发现可以走到索引了，

结果如下：

1	select count(1) from t_user where createTime between '2022-08-31' and '2022-08-31';

再找一个数据量为5千的区间来试一下，也是会走到索引的，

结果如下：

1	select count(1) from t_user where createTime between '2022-08-23' and '2022-08-25';

没走到索引是因为between…and引起的吗？如果改为>=和<=呢。

结果如下：

1	select count(1) from t_user where createTime >= '2022-08-30' and createTime  <= '2022-08-31';

结论

经过实验发现，当查询的数据量达到6千（占比20%左右），就不走索引了。

引起原因：

表的数据量太大，会让数据库中的优化器进行处理。

优化器是在表里面有多个索引的时候，决定使用哪个索引，查询的量太大，导致优化器认为走全表查询时间效率更佳。

但是如果一定要用到区间查询，这个问题该如何解决呢？

方案一

1	select count(1) from t_user where createTime between '2022-08-30' and '2022-08-31' limit 1;

方案二

1	select count(1) from t_user FORCE INDEX(index_updateTime) where createTime between '2022-08-30' and '2022-08-31';

思考：

当查询数据量达到一定量的时候会导致between…and索引失效，那分页查询的时候呢？

1	select id,name, createTime from t_user where createTime between '2022-08-30' and '2022-08-31' limit 1,500;

可见，查询第一页的时候索引有效，但是随着页码越来越大的时候，索引却失效了，

1	select id,name, createTime from t_user where createTime between '2022-08-30' and '2022-08-31' limit 100000,500;

因此，遇到这种因查询数据量过大而导致的索引失效的问题，需要对其功能做相应限制处理

转自

Mysql中between...and引起的索引失效问题及解决_Mysql_脚本之家
https://www.jb51.net/database/293553brm.htm