mysql高级 --- 索引优化（索引失效）（索引两大功能：查找和排序）

案例

---

1. 全职匹配
   
2. 最佳左前缀法则 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
   
3. 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
   
   
4. 存储引擎不能使用索引中范围条件右边的列
   
5. 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *
   
   
   
6. mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
   
7. is not null 也无法使用索引,但是is null是可以使用索引的
   
8. like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作
   
   新建一张 tbl_user 表
   explain之后 type都为all
   建立索引
   可以使用覆盖索引，索引不会失效，但当锅盖大于锅口时，索引会失效
   
   
9. 字符串不加单引号索引失效
   
10. 少用or,用它来连接时会索引失效
    

总结：

---

例题分析

mysql会在底层自动做一些优化，以最佳的顺序来进行查询

范围右边全失效，对于第二个，mysql依然会选择最优顺序

c3 的作用在排序而不再查找

和c4关系不大

跨过c3利用c4直接排序，mysql直接做不到，但是它会利用filesort帮助你来完成工作

---

难…

1.只用到了c1一个字段的索引，但是c2，c3用于排序，无filesort

2.出现了filesort，建的索引是1234，它没有按照顺序来，3 2 颠倒了

3.和上面的结果一样，但是性能不一样

4.用c1、c2两个字段索引，但是c2、c3用于排序,无filesort

一般情况下，order by 只要和索引的顺序不一样就会产生filesort
本例有常量c2的情况，和2对比，有常量的情况下，order by c3，c2 就相当于 order by c3,a2 (a2 就是固定的一个，对一个排序等于不排序)，到最后就相当于只剩c3一个

group by 和order by 几乎是一致的（分组之先排序）

定值（常量），范围（失效），还是排序，一般order by 是给个范围
group by基本上都要进行排序，会有零时表产生

---

一般性建议：

• 对于单键索引，尽量选择针对当前query过滤性更好的索引
• 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。(避免索引过滤性好的索引失效)
• 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
• 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

---

总结

1.观察，至少跑一天，看看生产的慢sql的情况
2.开启慢查询日志，设置阙值，比如超过五秒就是慢sql将它捕获
3.explain+慢sql分析
4.show profile
5.运维经理+DBA，进行Mysql数据库的服务器参数调优