避免索引失效的一些原则

MySql版本：5.5

（1）复合索引

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

 (a,b,c)

 b.复合索引，尽量使用全索引匹配

 (a,b,c)

（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同时失效。
	
  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 ;

（3）复合索引不能使用不等于（!= <>）或is null (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），之后的索引失效。

（4）补救。尽量使用索引覆盖（using index）

   （a,b,c）

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

（5） 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%'进行模糊查询，可以使用索引覆盖 挽救一部分。

（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 失效。