目录

 

单表使用索引及常见索引失效

 关联查询优化

子查询优化 

排序分组优化 

---

单表使用索引及常见索引失效

索引失效案例

1、全值匹配我最爱。

CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME)

建立索引前：

 建立索引后：

 

2、最佳左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。
 

3、不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描。

 4、存储引擎不能使用索引中范围条件右边的列。

5、mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描。

 6、is not null 也无法使用索引,但是is null是可以使用索引的。

 7、like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作。

 8、字符串不加单引号索引失效。

 9、小总结。假设index(a,b,c)：

Where语句	索引是否被使用
where a = 3	Y,使用到a
where a = 3 and b = 5	Y,使用到a，b
where a = 3 and b = 5 and c = 4	Y,使用到a,b,c
where b = 3 或者 where b = 3 and c = 4  或者 where c = 4	N
where a = 3 and c = 5	使用到a， 但是c不可以，b中间断了
where a = 3 and b > 4 and c = 5	使用到a和b， c不能用在范围之后，b断了
where a is null and b is not null	is null 支持索引 但是is not null 不支持,所以 a 可以使用索引,但是  b不可以使用
where a <> 3	不能使用索引 where   abs(a) =3    不能使用 索引
where a = 3 and b like 'kk%' and c = 4	Y,使用到a,b,c
where a = 3 and b like '%kk' and c = 4	Y,只用到a
where a = 3 and b like '%kk%' and c = 4	Y,只用到a
where a = 3 and b like 'k%kk%' and c = 4	Y,使用到a,b,c

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 建立索引建议

1.  对于单键索引，尽量选择针对当前query过滤性更好的索引。
2. 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
3. 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引。
4. 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。
5. 书写sql语句时，尽量避免造成索引失效的情况。 

 关联查询优化

1. 保证被驱动表的join字段已经被索引。
2. left join 时，选择小表作为驱动表，大表作为被驱动表。
3. inner join 时，mysql会自己帮你把小结果集的表选为驱动表。
4. 子查询尽量不要放在被驱动表，有可能使用不到索引。
5. 能够直接多表关联的尽量直接关联，不用子查询。

子查询优化 

1. 尽量不要使用not in  或者 not exists，用left outer join  on  xxx is null 替代。

排序分组优化 

1.  ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序。
2. 索引的选择，执行案例前先清除emp上的索引，只留主键。当范围条件和group by 或者 order by  的字段出现二选一时 ，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。
3. GROUP BY关键字优化：group by 使用索引的原则几乎跟order by一致 ，唯一区别是groupby 即使没有过滤条件用到索引，也可以直接使用索引。groupby实质是先排序后进行分组，遵照索引建的最佳左前缀；当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置；where高于having,能写在where限定的条件就不要去having限定了。

 

参考：http://www.atguigu.com/download_detail.shtml?v=30#player