【sql优化】避免索引失效的原则

• a.复合索引，不要跨列或无序使用（最佳左前缀）先a,再b再c ，（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个索引
		-- 用到了0个索引,原因：对于复合索引，如果左边失效，右侧全部失效。最佳左前缀，一定要保证左边有效
		-- (a,b,c)，例如如果 a失效，则b c同时失效。
		explain select * from book where authorid*2 = 1 and typeid = 2 ;
	
 		drop index idx_atb on book ; 
		alter table book add index idx_authroid (authorid) ;
		alter table book add index idx_typeid (typeid) ;
		-- 对于独立索引，authorid,typeid没有最佳左前缀原则，即使最左边失效，也不影响后边索引
		explain select * from book where authorid*2 = 1 and typeid = 2 ;

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

​ 比如：复合索引中如果有>，则自身和右侧索引全部失效

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

比如：本来应该使用authoried和typeid两个索引，但是由于SQL优化器对SQL的修改，导致只有一个索引生效

体验SQL优化的概率事件：因为SQL优化底层中的服务层有一个SQL优化器，优化器会对我们的SQL语句产生干扰

	drop index idx_typeid on book;
	drop index idx_authroid on book;
	
	alter table book add index idx_book_at (authorid,typeid);-- 用复合索引
	
	-- 复合索引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 < 1 and typeid =2 ;
	-- 复合索引全部失效
	explain select * from book where authorid < 4 and typeid =2 ;

结论：我们学习索引优化 ，是一个大部分情况适用的结论，但由于SQL优化器等原因 该结论不是100%正确。
一般而言， 范围查询（> < in），之后的索引失效。

4、补救：尽量使用索引覆盖（using index）,索引优化是100%，不会出现SQL优化的概率事件
（a,b,c） -------- select a,b,c from xx… where a= … and b =… ;

5、like尽量以“常量”开头，不要以'%'开头，否则索引失效（模糊查询）；

​ 如果必须使用like '%x%'进行模糊查询，可以使用索引覆盖 挽救一部分。

	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%';-- tname不会失效
 
	-- 如果必须使用like '%x%'进行模糊查询，可以使用索引覆盖 挽救一部分。
	explain select tname from teacher  where tname like '%x%'; 

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

explain select * from teacher where tname = 'abc' ;

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

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

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