索引优化分析（二）

一、SQL语句的性能分析（Explain）

1、查看SQL语句的执行计划

      使用explain关键词可以模拟优化器执行SQL查询语句，从而知道Mysql是如何处理sql语句，分析出查询语句或表结构的性能瓶颈，使用语法如下：

explain + sql语句：explain select * from table_name

查询结果如下：

2、Explain+SQL语句结果分析

（1）id：selectct 查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序（查询顺序）

（a）id相同，执行顺序由上至下     
（b）id不同，如果是子查询，id的序号会递增，id值越大，优先级越高，越先被执行
（c）id相同不同，同时存在

（2）select_type：表示查询的类型

1、SIMPLE：简单的select查询，查询中不包含子查询或union
2、PRIMARY：查询中若包含任何复杂的子部分，最外层查询则标记为primary
3、SUBQUERY：在select或where列表中包含了子查询
4、DERIVED：在from列表中包含的子查询被标记为derived(衍生)，mysql会递归执行这些子查询，把结果放在临时表里
5、UNION：若第二个select出现在union之后，则被标记为union
6、UNION RESULT：从union表中获取结果的select

（3）table：显示这一行的数据是于哪张表的来自

（4） partitions：使用的哪个分区，需要结合表分区才可以看到

（5）type：表示按某种类型来查询，例如按照索引类型查找，按照范围查找

        该值表示查询的sql语句好坏，从最好到最差依次为：system>const>eq_ref>ref>range>index>ALL

1、system：表中只有一行记录，这是const类型的特列
2、const：表示通过索引一次就能查到了，const用于比较primary key或unique索引，因为只匹配一行数据，所以很快，如将主键置于where列表中，mysql就能将该查询转换为一个常量
3、eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条与之匹配常见于主键或唯一索引扫描
4、ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独的行，然而，它可能找到多个符合条件的行，所以它应该属于查找和扫描的混合体
5、range：只检索给定范围的行，使用一个索引来选择行，一般就是在where语句中出现betwent、<、>、in等查询中，这种范围扫描索引扫描比全表扫描要好，因为它只需要开始与索引的某一个点，而结束于另一个点，不用扫描全部索引
6、index：full index scan，index与all区别为index类型只遍历索引树，这通常比all快，因为索引文件通常比数据文件小
7、all：full table scan，将遍历全表以找到匹配的行
备注：一般来说，保证查询至少到达range级别，最好能到达ref

（6）possible_keys：显示可能应用在该表中的索引，如果是多个索引的话，用逗号隔开，但查询时不一定被实际应用到

（7）key：查询时实际使用到的索引，如果为null，则没有使用到索引；若查询时使用了覆盖索引，则该索引和查询的select字段重叠

（8）key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引长的，长度越短越好

（9）ref：显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常数被用于查找索引列上的值

（10）rows：查询时大致估算出找到所需的记录，所需要读取的总行数

（11）filtered：通过过滤条件之后对比总数的百分比

（12）Extra：包含不适合在其他列中显示但十分重要的额外信息

• Using filesort：说明mysql会对数据使用的一个外部的索引排序，而不是按照表内的索引顺序进行读取，mysql中无法利用索引完成的排序操作称为"文件排序"，出现该关键词，需要优化处理。（组合索引排序时，不能跳过前面索引排序，按索引顺序进行排序，不产生索引排序冲突）

• Using temporary：使用了临时表保存中间结果，mysql在对查询结果排序时使用了临时表，常见于排序order by和分组查询group by（一般group by都会排序产生临时表）。出现该关键词，需要优化处理。

• Using index：表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错。覆盖索引：就是select的数据列只用从索引中就能够取得，不必读取数据行，mysql可以利用索引返回select列表中的字段，而不必根据索引（物理地址）再次读取数据文件，即查询列要被所建的索引覆盖。

• Using where：表明使用了where过滤。
• Using join buffer：使用了连接缓存。
• impossible where ：where子句的值总是false，不能用来获取任何数据，即条件永远不成立。
• distinct：优化distinct操作，在找到第一匹配的数据后立即停止找同样值的动作。

3、Explain性能分析的作用

（1）了解表的读取顺序（表的执行顺序）>id

（2）数据读取操作的操作类型（如：子查询等）>select_type、type

（3）查询中哪些索引可以使用  >possible_keys

（4）查询中哪些索引被实际使用到 >key

（5）表之间的引用 >table

（6）每张表有多少行被优化器查询 >rows

二、索引优化

1、索引失效情况如下类型（应该避免）

（1）全值匹配我最爱，即where条件按照索引的顺序查询，效果最佳（组合索引时，最前索引不能丢）
（2）最佳左前辍法则，如果为组合索引，要遵守最左前辍法则，查询时从索引的最左前列开始并且不跳过索引中的列（不按索引顺序检索容易导致索引失效）
（3）不在索引列上做任何的操作（计算、函数、类型转换等），会导致索引失效而转向全表扫描
（4）存储引擎不能使用索引中范围条件右边的列，即索引中查询条件存在范围时，索引将会失效
（5）尽量使用覆盖索引，即只访问索引的查询（索引列跟查询列一致），减少select * 
（6）mysql在使用不等于（!= 或<>）的时候无法使用索引会导致全表扫描（可通过覆盖索引方式解决）
（7）is null或is not null也是无法使用索引的（可通过覆盖索引方式解决）
（8）like以通配符开头（'%test%'）,mysql索引失效会变成全表扫描，但是索引支持右模糊查询，即like 'test%'（可通过覆盖索引方式解决）
（9）字符串不加单引号索引失效（mysql会自动类型转换，导致索引失效）
（10）少用or，用它来连接时会索引失效（可通过覆盖索引方式解决）

 2、索引创建、优化建议

（1）经常作为条件的字段应添加索引，多个则创建组合索引
（2）多表关联查询时，保证join语句中被驱动表上join条件字段已经被索引（创建索引）
（3）永远用小结果集驱动大的结果集（即小表驱动大表）