Mysql 执行计划

1. 查询优化步骤

1. 观察，至少跑一天，看生产的慢sql情况
2. 开启慢查询日志，设置阙值，比如超过5分钟就是慢查询，并把他抓取出来
3. explain+慢查询分析
4. Show profile查询sql在mysql服务器中的执行细节和生命周期情况
5. 运维经理或DBA，进行sql数据库服务器的参数调优

2. 性能分析 EXPLAIN执行计划

使用explain关键字可以模拟优化器（服务层）执行sql查询语句，从而知道Mysql是如何处理SQL语句的，分析你的查询语句或者表结构的性能瓶颈

| id | select_type | table | type| possible_keys | key | key_len | ref| rows |Extra |

通过他我们可以知道

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以被使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

① id

Select查询的序列号，包含一组数字，表示查询中执行select或操作表的顺序

• id相同的时候；执行顺序自上向下
• id不同；如果是子查询，id序号会递增，id值越大优先级越高，越先被执行
• id有相同又有不同；id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

当我们知道了表的执行顺序有什么用？
最常用的，在日常工作中遵循小表驱动大表的原则，到时候就可以观察sql加载表的顺序是不是你所想的先加载小表再加载大表

② select type

这一列表示查询中每个select子句的类型，是简单还是复杂SELECT

(1) SIMPLE
简单SELECT，查询中不包含UNION或子查询等

(2) PRIMARY
查询中若包含子查询，最外层的查询被标记为primary

(3) UNION
* 若第二个select出现在UNION之后，则被标记为UNION
* 若union包含在from子句的子查询中，外层SELECT被标记为DERIVED

(4) DEPENDENT UNION
UNION中的第二个或后面的SELECT语句，取决于外面的查询

(5) UNION RESULT
用来从UNION的匿名临时表检索结果的SELECT被称为UNION RESULT

(6) SUBQUERY
包含在SELECT列表中的子查询中的SELECT（换句话说，不在FROM子句中）

(7) DEPENDENT SUBQUERY
(子查询中的第一个SELECT，依赖于外部查询)

(8) DERIVED
在from列表中包含的子查询被标记为DERIVED(衍生)，mysql会递归执行这些子查询，把结果放在临时表中，服务器内部称其为派生表，因为该临时表是从子查询中派生来的

注意看table项，derived2表示由id为2的查询衍生出的虚表

(9) UNCACHEABLE SUBQUERY
(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

③ type

mysql 用法 Explain_lvhaizhen的博客-CSDN博客

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”

越向右性能越好
system>const>eq_ref>ref>range>index>all
一般来说要保证查询至少达到range级别，最好能达到ref

(1) system
表只有一行记录，是const的特例类型，平时不会出现，可以忽略不计，一般系统表只有一行记录

(2) constant
通常情况下，如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量。至于如何转化以及何时转化，这个取决于优化器。

• 可以看到，在子查询中，id为主键，通过主键索引只用查找一次就能查到对应的值，所以是constant
• 对于子查询查询出的衍生表d1里面只有一行数据，所以外面查询的类型为system

(3) eq_ref
唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见主键或唯一索引扫描，他会将他们与某个参考值做比较

ref_eq 与 ref相比牛的地方是，它知道这种类型的查找结果集只有一个，什么情况下结果集只有一个呢？那便是使用了主键或者唯一性索引进行查找的情况，比如根据学号查找某一学校的一名同学，在没有查找前我们就知道结果一定只有一个，所以当我们首次查找到这个学号，便立即停止了查询。这种连接类型每次都进行着精确查询，无需过多的扫描，因此查找效率更高，当然列的唯一性是需要根据实际情况决定的

(4) ref
非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回匹配某值（某条件）的多行值，属于查找和扫描的混合体。

出现该ref的条件是： 查找条件列使用了索引而且不为主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描

(5) range
只检索给定范围的行，使用一个索引来检索行，可以在key列中查看使用的索引，一般出现在where语句的条件中，如使用between、>、<、in等查询。

range指的是有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。关于range比较容易理解，需要记住的是出现了range，则一定是基于索引的。同时除了显而易见的between，and以及’>’,’<'外，in和or也是索引范围扫描

这种扫描比全表扫描好，因为他只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引

(6) index
全索引扫描，这种连接类型只是另外一种形式的全表扫描，只不过它的扫描顺序是按照索引的顺序
index和ALL的区别：index只遍历索引树，通常比ALL快，因为索引文件通常比数据文件小。虽说index和ALL都是全表扫描，但是index是从索引中读取，ALL是从磁盘中读取。


这种扫描根据索引然后回表取数据，和all相比，他们都是取得了全表的数据，而且index要先读索引而且要回表随机取数据，因此index不可能会比all快（取同一个表数据），但为什么官方的手册将它的效率说的比all好，唯一可能的原因在于，当使用了覆盖索引的时候不需要再回表查询

(7) all
这便是所谓的“全表扫描”，如果是展示一个数据表中的全部数据项，倒是觉得也没什么，如果是在一个查找数据项的sql中出现了all类型，那通常意味着你的sql语句处于一种最原生的状态，有很大的优化空间

all是一种非常暴力和原始的查找方法，非常的耗时而且低效。用all去查找数据就好比这样的一个情形：S学校有俩万人，我告诉你你给我找到小明，然后你怎么做呢！你当然是把全校俩万人挨个找一遍，即使你很幸运第一个人便找到了小明，但是你仍然不能停下，因为你无法确认是否有另外一个小明存在，直到你把俩万人找完为止。所以，基本所有情况，我们都要避免这样类型的查找，除非你不得不这样做

④ possible_keys & keys

• possible_keys：可能用到的索引
• keys ：实际使用到的索引

查询中若使用到了覆盖索引则只出现在keys中

⑤ key_len

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

• 表示索引中使用的字节数
• key_len和精确不能共得
  

⑥ ref

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有： const(常量)，字段名等。一般是查询条件或关联条件中等号右边的值，如果是常量那么ref列是const，非常量的话ref列就是字段名

• col1匹配t2表的col1
• col2匹配一个常量，即ac

⑦ rows

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

⑧ extra

包含不适合在其他列中显示但是十分重要的额外信息
(1) Using filesort（坏）

• 文件内排序，看到这个的时候，查询就需要优化了
• Using filesort表明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取
  

(2) Using temporary（坏）

• 使用了临时表保存中间结果，Mysql在对查询结果排序的时候使用了临时表，常见于排序order by和分组查询group by
• 需要先把数据放到临时表，使用完再把临时表删除
• 非常危险，“十死无生”，急需优化
  
  (3) Using index（好）
• 表明相应的select操作中使用了覆盖索引，避免访问表的额外数据行，效率不错
• 如果同时出现了Using where，表明索引被用来执行索引键值的查找
  
• 如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作。
  

(4) Using where