数据库—索引失效原因解释

联合索引的失效问题分析：

结合上面的索引的B+树结构，下面罗列了几种常见场景：

//以a结尾的在B+树结构中并不是有序的,无法使用索引
select * from test_user where a like '%1';
//以a开头的在B+树结构中是有序的，可以使用索引
select * from test_user where a like '1%';
//无法使用索引
select * from test_user where a like '%1%';

//下面两种情况因为都用到了覆盖索引，此时直接遍历索引树
select a from test_user where a like '%1';
select a from test_user where a like '%1%';
//a有序,可以使用索引
select a from test_user where a like '1%';

//最左匹配法则
//因为a是有序的，并且当a=1是b也是有序的，所以这里可以使用联合索引
select * from test_user where a = '1' and b = '2';
//a是有序的，但是当a>1时,b是无序的，所以a可以使用索引,b无法使用索引,虽然这里无法使用联合索引，但是这里会可以使用索引下推,减少回表的次数
select * from test_user where a > '1' and b = '2';
//b是无序的，所以无法使用索引
select * from test_user where b = '2';


//a是有序的，所以a可以使用索引
select * from test_user where a = '1' or a = '2';
//a是有序的,但是当a=1时,b时无序的,无法使用索引
select * from test_user where a = '1' or b = '2';

B站有小伙伴指出，索引可以看作沿着索引树进行的二分查找，所以不能进行二分查找(即不是顺序的)的情况都是索引失效。

参考：

演示B+树等常见数据结构的网站:

Data Structure Visualization

【吊打面试官】MySQL索引失效的底层原理，终于有人讲清楚了_哔哩哔哩_bilibili