避免索引失效的一些原则

梦里梦外; 2023-10-06 13:06 8阅读 0赞

#### 文章目录 ####

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;