范围查找(比如日期范围)下查询出现全表扫描MySQL数据库索引失效

当下MySQL数据库在多款数据库中脱颖而出，成为使用最广泛的数据库之一，这里我们来看看数据库索引上的一个问题。我们知道在数据量上去以后，查询会变得很慢，这是因为数据持久化在磁盘上的，每次查询都需要针对磁盘做IO操作。

针对这种问题，常用的方法之一是建索引，让我们来看看使用MySQL的B-Tree索引时，某个查询场景下即使规范正确地使用了索引也会失败的情况。

例如我这里有一张表：idx_test

create table idx_test
(
    ID               bigint auto_increment primary key,
    COUPON_NO        varchar(100),
    COUPON_RULE_ID   bigint,
    USER_ID          bigint,
    STATUS           int,
    VALID_START_TIME datetime,
    VALID_END_TIME   datetime
)

索引如下：

create index APP_VALID_INDEX on idx_test (VALID_START_TIME, VALID_END_TIME, USER_ID);

这时候我们使用如下sql来实验，看看索引失效的情况

EXPLAIN
SELECT u.`COUPON_RULE_ID` FROM idx_test u
WHERE u.`VALID_START_TIME` < ?
  AND u.`VALID_END_TIME` > ?
  AND `USER_ID` = 1

让我们先来看看一共有几条数据

补充条件参数执行sql发现并没有生效，这是为什么呢

将参数改成 2021-01-18 17:53:01 后发现居然走了索引

经过对比，第一种条件下，第一个索引字段的范围下，几乎包含了所有记录的99%的数据，这个时候等同于全表扫描，所以没有使用索引，而第二种条件下，只有一条数据，所以占比很小。

同时我又查阅了一些资料，MySQL数据库在查询数据条数约占总条数五分之一以下时能够使用到索引，但超过五分之一时，则使用全表扫描了 ，这个五分之一应该可以配置，但不建议，这个数值应该是MySQL查询优化的一个推论值和推荐值。

故我们在建立索引时，应尽可能不去在范围查找的字段上建索引，或者尽可能不把范围查找的字段放在where的第一个，比如上述查询，可以将USER_ID放在第一位，并建立一个USER_ID为第一个的联合索引，不过假设第二个条件下还是有大量的数据被扫描到，那还是相当于只走了USER_ID索引，没有走联合索引。

另外，网友很多有说使用is null来判断时索引不生效，我测试是生效的，后续可以深入了解下这一点

分享到这里就结束了，欢迎大家讨论指正。