mysql 分析explain命令调优执行sql的计划2

一 sql的排查

1.1 性能下降原因

1.索引失效

2.sql写的太烂

3.关联查询太多join(设计缺陷或不得已的需求)

4.服务器调优以及各个参数配置(缓冲、线程数)

1.2 mysql的瓶颈

1.cpu在饱和的时候，一般发生在数据装入内存或从磁盘上读取数据时候。

2.磁盘I/O瓶颈发生在装入数据远远大于内存容量的时候。

3.服务器硬件的性能瓶颈：top，free，iostat和vmstat来查看系统的性能。

1.3 排查步骤

1.通过top命令 排查mysql进程使用cpu的占有率

2.通过show full processlist； 抓取慢sql。

通过mysql -uxxx -pxxx 进入mysql命令行，输入：show full processlist；

3.通过explain 查看sql执行计划

二  mysql的explain执行计划详解

2.1 explain/desc概述

使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。 分析你的查询语句或是表结构的性能瓶颈。

通过explain能够查看：

1.表的读取顺序

2.数据读取操作的操作类型

3.哪些索引可以使用

4.哪些缩影被实际使用

5.表之间的引用

6.每张表有多少行被优化器查询

2.2 id列

表示查询中执行 select 子句或操作表的顺序。

1.规则1：id相同，则从上到下顺序执行

 2.规则2：id不同，id的数字越大，优先级越高，越先执行。

 3.规则3：有相同也有不同

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

 2.3 selectType

 select_type 代 表查询的类型 ，主要是用于区别普通查询、联合查询、子查询等的复杂查询

2.3.1 simple

SIMPLE(简单SELECT,不使用UNION或子查询等)

desc select * from user

 2.3.2 primary

PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)

desc select * from user where role_id=(select id from role where name='开发');

  2.3.3 derived

在 FROM 列表中包含的子查询被标记为 DERIVED(衍生) MySQL 会递归执行这些子查询, 把结果放在临时表里。

desc select * from ( select * from user where name='Java' union select * from user where role_id=1 ) a;

 第一个执行的select_type为简单查询，第二个及以后的查询为UNION，最后合并时的select_type为UNION RESULT。 

  2.3.4 subquery

在SELECT或WHERE列表中包含了子查询

desc select * from user where role_id=(select id from role where name='开发');

   2.3.5  dependent subquery

在SELECT或WHERE列表中包含了子查询,子查询基于外层

desc select * from user where role_id = ( select id from role where id=user.id );

    2.3.6  uncacheable subquery

无法使用缓存的子查询

    2.3.7  union

desc select * from user where name='Java' union select * from user where role_id=1;

若第二个SELECT出现在UNION之后，则被标记为UNION； 若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED

    2.3.8  union result

desc select * from user where name='Java' union select * from user where role_id=1;

从UNION表获取结果的SELECT

2.3.9  uncacheable union

UNION查询的结果不能被缓存

2.3.10  materialized

MATERIALIZED(物化子查询) 在SQL执行过程中，第一次需要子查询结果时 执行子查询并将子查询的结果保存为临时表 ，后续对子查询结果集的 访问将直接通过临时表获得。

2.4 table列

查询涉及的表或衍生表

2.5 partions分区

partitions查询涉及到的分区

1.创建分区表

 2.查看

 desc select * from user_partitions where id>200;

查询id大于200（200>100，p1分区）的记录，查看执行计划，partitions是p1，符合我们的分区规则

2.6  Type列

type 提供了判断查询是否高效的重要依据依据。通过 type 字段, 我们判断此次查询是全表扫描还是索引扫描等。

可以使用desc，或者 explain 执行，查看执行计划：

类型从好到坏的排序:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >ALL

常见的有：system>const>eq_ref>ref>range>index>ALL

一般来说，得保证查询至少达到range级别，最好能达到ref。

2.6.1 System

system: 表中只有一条数据，相当于系统表； 这个类型是特殊的 const 类型；

system：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计。

2.6.2 Const

const: 主键或者唯一索引的常量查询，表格最多只有1行记录符合查询，通常const使用到主键或者唯一索引进行定值查询。表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为一个常量

 2.6.3 eq_ref

eq_ref: 除了system和const类型之外,效率最高的连接类型；唯一索引扫描，对于每个索引键，表中只有一条记录与之对应；常用于主键或唯一索引扫描。（联表唯一，和上面的区别在于索引数量不同）

根据上面的知识；可知id相同，由上至下依次执行，分析结果可知：

先查询t表就是teacher表中name字段为a的记录，由于name字段没有索引，所以全表扫描（type:ALL），一共有3条记录，扫描了3行（rows:3）,1条符合条件（filtered:33.33 1/3）；

再查询tc即teacher_card表使用主键和之前的t.tc_id关联；由于是关联查询，并且是通过唯一索引（主键）进行查询，仅能返回1或0条记录，所以type为eq_ref。

总结：equ_ref用于是关联多表查询时的唯一索引查询，对每个索引键，表中只有一条或零条记录与之匹配

 2.6.4 ref

ref：此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了最左前缀规则索引的查询（换句话说，返回的可能是多行数据）

  2.6.5 range

 range：表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中。在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

   2.6.6 index

如果索引是复合索引，并且复合索引列满足select所需的所有数据，则仅扫描索引树。在这种情况下，Extra为 Using index。仅索引扫描，通常比ALL索引小于表数据，查询更快 。

index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）。

1.select 查询的列为tc_id,满足覆盖索引规则，此时扫描索引树，extra为using index

2.索引列不满足select所需的所有数据（如索引是tc_id，查询是name），此时需要回表扫描；按索引顺序查找数据行。 Uses index没有出现在 Extra列中。 

   2.6.7 all

all：ALL: 全表扫描,没有任何索引可以使用时。这是最差的情况，应该避免。

由于name字段不存在索引，type:ALL全表扫描；可通过对name字段设置索引进行优化。 

2.6  possibleKeys

possible_keys（理论上要多少索引）显示可能应用在这张表中的索引，一个或多个。
查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用。

2.7  keys

key（实际用到的索引）实际使用的索引。如果为NULL，则没有使用索引，查询中若使用了覆盖索引，则该索引仅出现在key列表中。

所谓的覆盖索引：查询的字段正好和建立索引的字段和顺序一致。

2.8  keys_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。只计算利用索引作为index key的索引长度，不包括用于group by/order by的索引长度
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

1. 一般地，key_len 等于索引列类型字节长度，例如int类型为4 bytes，bigint为8 bytes；
2. 如果是字符串类型，还需要同时考虑字符集因素，例如utf8字符集1个字符占3个字节，gbk字符集1个字符占2个字节
3. 若该列类型定义时允许NULL，其key_len还需要再加 1 bytes
4. 若该列类型为变长类型，例如 VARCHAR（TEXT\BLOB不允许整列创建索引，如果创建部分索引也被视为动态列类型），其key_len还需要再加 2 bytes

字符集会影响索引长度、数据的存储空间，为列选择合适的字符集；变长字段需要额外的2个字节，固定长度字段不需要额外的字节。而null都需要1个字节的额外空间，所以以前有个说法：索引字段最好不要为NULL，因为NULL让统计更加复杂，并且需要额外一个字节的存储空间。

假设这些字段为索引字段

id：长度为4

age：长度为 4+1=5

 empno： 4+1=5；

name:  utf-8 字符集  20*3+2  ； gbk ：20*2+2；

一些其他例子

2.9 ref

ref：（显示使用到的条件查询，如果是常量就为const）

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

显示该表的索引字段关联了哪张表的哪个字段

 由key_len可知t1表的索引idx_col1_col2被充分使用，t1表的col1匹配t2表的col1，col2匹配了一个常量，即 ‘ac’。

2.10 rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。（越小越好）

案例：

 可以看到t2表没有创建索引

创建复合索引，进行查看

 2.11 filtered

返回结果的行数占读取行数的百分比，值越大越好

先查询t表就是teacher表中name字段为a的记录，由于name字段没有索引，所以全表扫描（type:ALL），一共有3条记录，扫描了3行（rows:3）,1条符合条件（filtered:33.33 1/3）；

再查询tc即teacher_card表使用主键和之前的t.tc_id关联；扫描索引（type:ref），返回1条记录，最终返回1条记录，（filtered:100 1/1）。

2.12 Extras

其他的额外重要的信息

2.12.1 Using filesort

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

 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。

2.12.2 Using temporary

使了用临时表保存中间结果, MySQL 在对查询结果排序时使用临时表 。常见于排序 order by 和分组查询 group by。

2.12.3 Using index

Using index 代表表示相应的 select 操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！

如果没有出现  using where ，表明索 引被用来执行索引键值的查找 ;（直接从索引中查找到了数据）

如果同时出现 using where ，表明索 引只是用来读取数据而非利用索引执行查找 。（通过索引进行了查找），using index 使用覆盖索引后还得通过where设置过滤条件才能获得数据

例子：

using index+无与using index+ using where 的例子
例如要查找汉字刘这个字，通过字典的拼音索引liu进行搜索，一下子找到汉字刘，可理解为using index；

如果翻到liu拼音的章节开始找到，流，留，...直到找到汉字刘，还得按条件遍历查找，这种情况就是。using index usingwhere

1.覆盖索引：就是select的数据列只用从索引中就能够取得，不必读取数据行。早mysql可以利用索引就可以返回select列表中的字段，而不必根据索引再查找数据问文件。概况：查询的列要被所见的索引覆盖

2.12.4 Using where

表明使用了 where 过滤。

2.12.5 Using join buffer

使用了连接缓存。

2.12.6 impossible where

where 子句的值总是 false，不能用来获取任何元组

三  explain的综合分析案例

1.sql语句

2.阐释

执行顺序1： 第四行 ，id为4，select_type为union，说明第四个select是union后面的select，最先执行【select name,id from t2】

执行顺序2： 第二行，id为3，是整个查询中第三个select的一部分。因查询包含在from中，所以为derived。【select id,namefrom t1 where other_column=’’】

执行顺序3：第三行，id为2，select列表中的子查询select_type为subquery，为整个查询中的第二个select。【select id from t3】

执行顺序4： 第一行，id为1，表示是union里的第一个select，select_type列的primary表示该查询为外层查询，table列被标记为derived，表示查询结果来自一个衍生表，其中derived3中3代表该查询衍生自第三个select查询，即id为3的select。【select d1.name… 】

执行顺序5：第五行，：代表从union的临时表中读取行的阶段，table列的<union，1,4>表示用第一个和第四个select的结果进行union操作。【两个结果union操作】