SQL优化之避免索引失效原则

避免索引失效的一些原则

索引使用原则

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

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

####不要在索引上使用任何操作（如：计算、函数、类型转换），否则索引失效。
正常演示

explain select * from book where authorid=1 and typeid=2\G

	

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

结论

对于复合索引 左边失效，右边全部失效
单个索引没有影响

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

实例演示

	explain select    *  from book where authorid!=1 and typeid=2 \G

我们可以看到，使用了 ！ 导致了索引失效

延伸：SQL优化其实一种概率层面的优化，至于是有使用，还需要explain进行推测

体验概率情况(< > =)：原因是服务层中有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 ;--复合索引全部失效

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

那我们怎么避免索引失效呢？

尽量使用索引覆盖（Using Index）

like （模糊查询）以常量开头，避免‘%%’

select * from teacher where name='%x%';-- 会导致索引失效。

	若果一定需要使用‘’%x%‘’这种模糊查询可以使用索引覆盖

explain	select name from teacher where name like '%x%';

不要使用类型转换（显示、隐式）

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

不要使用 OR 否则索引失效

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

其他的优化方法

exist 和 in

	select * from table where exists/in(子查询)；
	-- 如果主查询数据及大使用 in
	-- 若果子查询的数据集大使用exists

exists语法： 将主查询的结果，放到子查需结果中进行条件校验（看子查询是否有数据，如果有数据 则校验成功）  ，如果 复合校验，则保留数据；

select tname from teacher where exists (select * from teacher) ; 
	--等价于select tname from teacher	

in

select ..from table where tid in  (1,3,5) ;

order by 优化

产生问题

	Using filesort

Using filesort 底层用到的两种算法

 1.双路排序（MySQL4.1之前默认）====>两次IO(扫描两次次磁盘)
 1.1 第一次IO：读取排序字段，对排序字段进行排序（在Buffer中）
 1.2 第二次IO:扫描其他字段（IO比较消耗性能）
 2.单路排序（MySQL4.1之后默认）===>一次IO（扫描一次磁盘）
 2.1 在buffer中进行排序。但种单路排序 会有一定的隐患 （不一定真的是“单路|1次IO”，有可能多次IO）。
 导致的原因分析:如果数据量大，则无法将所有数据一次性放在buffer，会导致“分片读取，分次读取”

注意

注意：
1.单路排序比双路培训占用更多buffer
2. 单路排序在使用时，如果数据大，可以考虑调大buffer的容量大小：
  set max_length_for_sort_data = 1024  单位byte
3. 如果max_length_for_sort_data值太低，则mysql会自动从 单路->双路   
 （太低：需要排序的列的总大小超过了max_length_for_sort_data定义的字节数）

Oder By 查询的优化策略

 1.选择使用单路、双路、适当调整buffer大小

 set max_length_for_sort_data = 1024  -- 设置buffer大小

2.避免使用 select  * (* 需要计算排序 )
3. 复合索引不要跨列使用，避免使用Using filesort
4. 保证全部的排序字段，保证排序的一致性（都是升序或升序）