mysql高级---explain详解

一、Mysql Query Optimizer

二、Mysql 瓶颈分析

• CPU:CPU在饱和的时候，一般发生在数据装入内存或从磁盘上读取数据的时候（从磁盘到内存）
  
• IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候（装入数据>内存容量）
  
• 服务器硬件的性能瓶颈：top,free,iostat和vmstat来查看系统的性能状态
  

三、Explain（查询执行计划）

假设mysql未出现瓶颈，且优化分析也未出现问题，那么就可以应用explain来分析sql查询执行计划

3.1是什么？（模拟、执行、分析）

定义：使用exlain关键字可以模拟优化器执行SQL查询语句，从而知道ＭySql是如何处理你的SQL语句的。

然后根据结果分析查询语句或是表结构的性能瓶颈。

3.2 能干什么？

• 表的读取顺序（id:id越大，执行越早)
• 数据读取操作的操作类型(select_type)
• 哪些索引可以使用（possible_keys)
• 哪些索引被实际使用(key)
• 表之间的引用(ref)
• 每张表有多少行被优化器查询(rows)

3.3 怎么用？

• 语法：explain + sql语句
• 举例：EXPLAIN SELECT * from tb_item a left join tb_item_desc b on a.id = b.item_id;
• 执行计划包含信息：

3.4 各字段解释说明

字段信息：

3.4.1、id （关键指标）

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的读取和加载顺序。

三种情况说明

情况一：id相同，执行顺序由上至下

情况二：id不同的情况， 若是子查询，id的序号会递增， id值越大优先级越高，越先被执行。

即，括号最里层的查询最先执行

情况三：id相同和不同，同时存在的情况。

• id如果相同，可以认为是一组，从上往下顺序执行；
• 在所有组中，id值越大，优先级越高，越先执行

图解：

• s1是一张衍生虚表
• 衍生=DERIVED
• derived2表示衍生表， id=2的衍生虚表
• 图中的 id=2先执行，然后再顺序执行id=1的多条语句。

3.4.2、select_type

表示数据读取操作的操作类型（查询类型），主要用于区分普通查询、联合查询、子查询等的复杂查询

注：临时表会增加系统的负担

3.4.3、table

显示这行数据是属于哪张表的

3.4.4、type（关键指标）

概要：显示了查询使用了何种类型，一共有8种值,从最好到最差依次如下：

• 常用类型
• 工作中常用的 system>const>eq_ref>ref>range>index>all>null
• 实际的最好到最差排序情况

          

详细说明：

• system:表只有一条记录（等于系统表），是const的特例，平时很少出现，可以忽略

• const:表示通过索引一次就找到数据，用于primary key或者唯一索引

    说明：因为只匹配一行数据，所以很快，如将主键置于where列表中，MySQL就能将该查询转换为一个常量。

    案例：

    

   

• eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常用于主键索引和唯一性索引

    1、正例 

    EXPLAIN select * from tb_content_category a right join  tb_content b on a.id=b.category_id

    

    说明：以b表为驱动表，对于a表来说，将b.category_id传递给a.id的关联结果是唯一的

    

    2、反例 （理论上有用到索引，但是实际中未用到索引，索引失效 ）

    EXPLAIN select * from tb_content_category a left join  tb_content b on a.id=b.category_id

    

    说明：不能用a表做为驱动表 ，因为对于b来说，将a.id传递给b.category_id的关联结果 不唯一

    

    3、强关联的情况，会自动判断用哪个表做为驱动表

    EXPLAIN select * from tb_content_category a , tb_content b where b.category_id=a.id

    EXPLAIN select * from  tb_content b,tb_content_category a  where b.category_id=a.id

     以上两句SQL的查询执行计划结果一致 

    

   

重要结论：从以上的1、2两个案例可以看出，右连接的情况，左表 的关联字段要有建唯一索引，这样唯一索引才会生效（如1）；左连接的情况，右表的关联字段不是唯一索引，

                所以会出现 索引失效的情况（如2）。

问题：左连接的场景，给b表建了 单值索引 ，但是分析结果还是一样的未用到此 单值索引 ，这又是为啥呢？

    

• ref:非唯一性索引扫描，常用的一种查询类型

    返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，

    然而，它可能会找到多个符合条件的行，所以它应该 属于查找和扫描的混合体。

    案例：

    

• range：只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引。

    说明：一般就是在你的where语句中出现了between、<、>、in等的查询，这种范围扫描索引，要比扫描要好，

    因为它只需要开始于索引的某一个点，而结束于另一个点，不用扫描全部索引。

   

     案例：

        

• index：全索引扫描，只遍历索引树。

    说明：这个通常比all快，因为索引文件通常比数据文件小， 也就是说虽然all和index都是读全表，但是index是从索引文件中读取的，而all是从硬盘中读取的数据

    所以index比较快。

    案例：

    

    

• all：表示全表扫描，从硬盘中的数据文件中读取数据，若表数据量大（百万条数据）的时候就要考虑优化了

    

   总结：一般来说至少要达到range级别，最好能达到ref级别

    

3.4. 5、possible_keys

显示可能应用在这张表中的索引，一个或多个

查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被实际使用

3.4.6、key（关键指标）

说明：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

1、实际使用到的索引，如果为null，表示未使用到索引（未建索引或索引失效）

2、查询中若使用了覆盖索引，则该索引只出现在key列表中（type:index,说明只遍历索引树，extra:using index）

换句话说：select语句查询的列的个数和顺序，跟表所建的复合索引的列的个数和顺序一致的情况，索引只出现在key列表中

3.4.7 key_len

说明：表示索引使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。

key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出来的。

3.4.8 ref 

说明：查询中与其它表关联的字段，外键关系建立索引

案例：

个人理解：在关联查询的时候，谁是主表，那么就引用谁的 主键索引，如下例的情况

eg 1:右连接，b 是主表，EXPLAIN SELECT * from tb_item a right join tb_item_desc b on a.id = b.item_id;

eg 2:左连接，a 是主表,EXPLAIN SELECT * from tb_item a left join tb_item_desc b on a.id = b.item_id;

注：以上操作的前提是a.id是唯一索引，且b.item_id在b表中也是唯一不重复的情况。

换句话说，即a,b表是一对一的关系 

3.4.9 rows （关键指标）

说明：根据表统计信息及索引选用的情况，大致估算出查出满足条件的结果，所需要读取的行数

案例：

3.4.10 extra

＃说明 ：包含不适合在其他列中显示，但十分重要的额外信息

＃详解：

1、using fileort:说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为“文件排序”。

前一条SQL用文件排序，比较慢；而后一条SQL用了索引排序，所以效率更高

注：前一条SQL用了col3排序，而col2不确定的情况下，索引失效，而后一句SQL已确认col1的值，所以按col2,col3排序时，组合索引排序是有效的

2、using temporary:使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by

小结：当sql语句中有排序或分组时，排序和分组的列的顺序最好按照表所创建的 复合索引的列的顺序一致 。否则会额外用到文件索引或临时表，大大影响了sql的执行效率

案例一中如果col1没有使用范围而是常量，则不会出现临时表和filesort

3、using index:从索引中检索

表示相应的select操作中使用了覆盖索引（covering index),避免访问了表的数据行，效率不错！

• 如果同时出现using where,表明索引被用来执行索引键值的查找；
  
• 如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
  

案例说明：

什么是覆盖索引？

个人理解：select 查询的数据列跟复合索引的列顺序和个数不超过的情况，不必读取硬盘中数据文件中的数据行，可直接从索引文件中读取。

★理解方式一：就是select的数据列只用从索引中就能够取得，不必读取数据行，mysql可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说：查询列要被所建的索引覆盖。

理解方式二：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。

补充纠正：select 列表中只取有创建索引的列（顺序要匹配，那个数是否都要匹配？个数超过索引肯定不行，若个数小于组合索引中列的个数呢？也可以称为覆盖索引吗？）

——————————回答：是的，个数从索引中能取得就行，可以小于。

4、using where:使用了where 过滤条件

5、using join buffer:使用连接缓存

6、impossible where :where子句的值是false，不能用来获取任何元组。

7、select tables optimized away

在没有group by子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成阶段即完成优化。

８、distinct:优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

3.5 热身case