避免索引失效的一些原则

文章目录

1、复合索引
a.复合索引，不要跨列或无序使用（最佳左前缀）
b.复合索引，尽量使用全索引匹配

2、不要在索引上进行任何操作（计算、函数、类型转换），否则索引失效
例如

select ..where A.x = .. ;  --假设A.x是索引

不要：select ..where A.x*3 = .. ;

explain select * from book where authorid = 1 and typeid = 2 ;
--用到了at2个索引
explain select * from book where authorid = 1 and typeid*2 = 2 ;
--用到了a1个索引
explain select * from book where authorid*2 = 1 and typeid*2=2;
----用到了0个索引
explain select * from book where authorid*2 = 1 and typeid = 2 ;
----用到了0个索引原因：对于复合索引，如果左边失效，右侧全部失效。(a,b,c)，例如如果 b失效，则b c同时失效。

3、复合索引不能使用不等于（!= <>）或is null (is not null)，否则自身以及右侧所有全部失效。
复合索引中如果有>，则自身和右侧索引全部失效。

---

SQL优化，是一种概率层面的优化。至于是否实际使用了我们的优化，需要通过explain进行推测。

体验概率情况(< > =)：原因是服务层中有SQL优化器，可能会影响我们的优化。

explain select * from book where authorid < 1 and typeid =2 ;
--复合索引at只用到了1个索引
explain select * from book where authorid < 4 and typeid =2 ;
--复合索引全部失效

学习索引优化 ，是一个大部分情况适用的结论，但由于SQL优化器等原因 
该结论不是100%正确。

---

4、如何补救：尽量使用索引覆盖（using index）

                （a,b,c）
select a,b,c from xx..where a=  .. and b =.. ;

5、like尽量以“常量”开头，不要以’%‘开头，否则索引失效，但是在有些查询中是一定需要出现以%开头的情况。

6、尽量不要使用类型转换（显示、隐式），否则索引失效

explain select * from teacher where tname = 'abc' ;
explain select * from teacher where tname = 123 ;
//程序底层将 123 -> '123'，即进行了类型转换，因此索引失效

7、尽量不要使用or，否则索引失效

explain select * from teacher where tname ='' or tcid >1 ; 
--将or左、右侧的tname 失效。

但是使用or并不是一定会使索引失效，你需要看or左右两边的查询列是否命中相同的索引。

例如：假设USER表中的user_id列有索引，age列没有索引

下面这条语句其实是命中索引的

select * from `user` where user_id = 1 or user_id = 2;

以下的两条都无法命中索引

select * from `user` where user_id = 1 or age = 20;

`假设age列也有索引的话，依然是无法命中索引的。`
select * from `user` where user_id = 1 or age = 20;