范围查找(比如日期范围)下查询出现全表扫描MySQL数据库索引失效
当下MySQL数据库在多款数据库中脱颖而出,成为使用最广泛的数据库之一,这里我们来看看数据库索引上的一个问题。我们知道在数据量上去以后,查询会变得很慢,这是因为数据持久化在磁盘上的,每次查询都需要针对磁盘做IO操作。
针对这种问题,常用的方法之一是建索引,让我们来看看使用MySQL的B-Tree索引时,某个查询场景下即使规范正确地使用了索引也会失败的情况。
例如我这里有一张表:idx_test
create table idx_test(IDbigint auto_increment primary key,COUPON_NO varchar(100),COUPON_RULE_ID bigint,USER_IDbigint,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来实验,看看索引失效的情况
EXPLAINSELECT u.`COUPON_RULE_ID` FROM idx_test uWHERE u.`VALID_START_TIME` < ?AND u.`VALID_END_TIME` > ?AND `USER_ID` = 1
让我们先来看看一共有几条数据
补充条件参数执行sql发现并没有生效,这是为什么呢
将参数改成 -01-18 17:53:01 后发现居然走了索引
经过对比,第一种条件下,第一个索引字段的范围下,几乎包含了所有记录的99%的数据,这个时候等同于全表扫描,所以没有使用索引,而第二种条件下,只有一条数据,所以占比很小。
同时我又查阅了一些资料,MySQL数据库在查询数据条数约占总条数五分之一以下时能够使用到索引,但超过五分之一时,则使用全表扫描了 ,这个五分之一应该可以配置,但不建议,这个数值应该是MySQL查询优化的一个推论值和推荐值。
故我们在建立索引时,应尽可能不去在范围查找的字段上建索引,或者尽可能不把范围查找的字段放在where的第一个,比如上述查询,可以将USER_ID放在第一位,并建立一个USER_ID为第一个的联合索引,不过假设第二个条件下还是有大量的数据被扫描到,那还是相当于只走了USER_ID索引,没有走联合索引。
另外,网友很多有说使用is null来判断时索引不生效,我测试是生效的,后续可以深入了解下这一点