SQL优化 - 避免索引失效的一些原则

1. 复合索引不要跨列或无序使用（最佳左前缀原则）

复合索引尽量使用全索引匹配

2. 复合索引不能使用 不等于(!= <>)、IS NUL(IS NOT NULL) 或 大于号(>) 否则会造成自身以及右侧索引全部失效

explain select * from book where authorid = 1 and typeid =2 ;
 
-- SQL优化，是一种概率层面的优化。至于是否实际使用了我们的优化，需要通过explain进行推测。
 
explain select * from book where authorid != 1 and typeid =2 ;
explain select * from book where authorid != 1 and typeid !=2 ;
		
	
体验概率情况(< > =)：原因是服务层中有SQL优化器，可能会影响我们的优化。
drop index idx_typeid on book;
drop index idx_authroid on book;
alter table book add index idx_book_at (authorid,typeid);
explain select * from book where authorid = 1 and typeid =2 ;--复合索引at全部使用
explain select * from book where authorid > 1 and typeid =2 ; --复合索引中如果有>，则自身和右侧索引全部失效。
explain select * from book where authorid = 1 and typeid >2 ;--复合索引at全部使用
----明显的概率问题---
explain select * from book where authorid < 1 and typeid =2 ;--复合索引at只用到了1个索引
explain select * from book where authorid < 4 and typeid =2 ;--复合索引全部失效
 
--我们学习索引优化 ，是一个大部分情况适用的结论，但由于SQL优化器等原因  该结论不是100%正确。
--一般而言， 范围查询（> <  in），之后的索引失效。

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

假设A.X是索引
 
SELECT ... WHERE A.X*3 = ...  【无效】
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同时失效。
	
drop index idx_atb on book ; 
alter table book add index idx_authroid (authorid) ;
alter table book add index idx_typeid (typeid) ;
explain select * from book where authorid*2 = 1 and typeid = 2 ;

4. LIKE尽可能以"常量"开头，不要以"%"开头，否则索引失效

select * from xx where name like '%x%' ; --name索引失效
	
explain select * from teacher  where tname like '%x%'; --tname索引失效
 
explain select * from teacher  where tname like 'x%';
 
explain select tname from teacher  where tname like '%x%'; --如果必须使用like '%x%'进行模糊查询，可以使用索引覆盖 挽救一部分。

5. 尽量不要使用or，否则索引失效

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