SQL索引失效问题-范围查询导致的后续索引失效

1、案例：

sql语句如下：

SELECT * FROM table WHERE a = 1 AND b > 2 AND c = 3;

假如设置的索引为：idx_a_b_c

执行上面sql语句时就会遇到索引失效问题，原因是因为在c = 3之前有一个范围查询（b > 2），范围查询会导致后续索引失效。

2、解决方法：

把索引修改为idx_a_c_b，这样的话，就会在最后去执行范围查询，这样就能避免索引失效问题了。

3、原因：

范围查询后结果集的有序性不再符合索引的原始顺序要求

大多数关系数据库管理系统（RDBMS）使用 B-树或变种（如 B+树）来实现索引，而B-树是一种自平衡的树数据结构，能够保持数据有序。

例如：MySQL的InnDB存储引擎使用的是B+树，MyIsam存储引擎也是B+树

 

假设有下面数据

a | b | c 
----------
1 | 1 | 1 
1 | 3 | 4 
1 | 3 | 5
1 | 4 | 2
1 | 4 | 3 

范围查询

此时的索引为idx_a_b_c，经过执行WHERE a = 1 AND b > 2（b > 2是范围查询），后会变成下面这样

a | b | c 
----------
1 | 3 | 4 
1 | 3 | 5
1 | 4 | 2
1 | 4 | 3 

 显然，c列的4，5，2，3不是有序的，无法符合索引的原始顺序要求，所以，如果再去执行c = 3的话索引会失效。

精准匹配

 如果执行的是WHERE a = 1 AND b = 4（b = 4是精准匹配），那么结果会变成下面这样

a | b | c 
----------
1 | 4 | 2 
1 | 4 | 3

 c列的4，5是有序的，所以，如果再去执行c = 3的话就可以继续使用索引。