mysql执行计划怎么写 mysql的sql执行计划详解非常有用

MySQL执行计划

我们知道，当一条sql查询语句执行时，会通过服务层中的优化器生成“查询执行计划”。而使用explain关键字可以查询到执行的SQL查询语句，从而知道MySQL是如何处理SQL的，即SQL的执行计划。因此根据执行计划我们可以选择更好的索引和写出更优化的查询语句，分析我们的查询语句或是表结构的性能瓶颈。

公司主营业务：网站设计制作、成都网站建设、移动网站开发等业务。帮助企业客户真正实现互联网宣传，提高企业的竞争能力。创新互联是一支青春激扬、勤奋敬业、活力青春激扬、勤奋敬业、活力澎湃、和谐高效的团队。公司秉承以“开放、自由、严谨、自律”为核心的企业文化，感谢他们对我们的高要求，感谢他们从不同领域给我们带来的挑战，让我们激情的团队有机会用头脑与智慧不断的给客户带来惊喜。创新互联推出东台免费做网站回馈大家。

首先先解释一下以上执行计划中各列的含义：

2. PRIMARY： 如果查询语句中包含子查询或者UNION操作，指最外层的SELECT；

3. UNION： UNION中的第二个或后面的SELECT语句；

4. UNION RESULT： UNION 的结果；

5. SUBQUERY： 子查询中的第一个SELECT；

6. DERIVED： 导出表的SELECT(FROM子句的子查询)。

下面介绍在实际开发过程中，常见的几种类型：

1. const： 表示通过索引一次就找到数据，用于比较primary key或者unique索引，很快就能找到对应的数据；

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

3. ref： 非唯一索引扫描，返回匹配的所有行；

4. index_merge： 经常出现在使用一张表中的多个索引时，mysql会将多个索引合并在一起；

5. range： 使用一个索引检索指定范围的行，一般在where语句中会出现between、、、in等范围查询；

6. index： index连接类型与ALL相同，只是遍历索引树；

7. ALL： 全表扫描，找到匹配行。与index比较，ALL需要扫描磁盘数据，index值需要遍历索引树。

误区：

上述图片可以看到，key_len的值为9（即hotelID（4）+dateTime（5）），没有使用到全部联合索引，以下是改良后的sql语句：

此时key_len的值为14（即hotelID（4）+dateTime（5）+dateTime（5）），使用到了key中所有索引。

优化前：

很显然，从explain执行计划中可以看到，该sql语句使用了两个索引，但是从我们自己的优化目标中，只需要使用IDX_DataChange_CreateTime这一个索引就够了，以下是我们通过一些小手段影响优化器得到的优化方案：

Day42 Mysql 执行计划

官方文档 对于5.7 版本 explain的描述

const ref range index all

const: 主键索引

ref: 二级索引

range： 索引的范围扫描

index: 比如用到了联合索引 比如 a,b,c，走的是联合索引

all: 全表扫描

rows: 需要逐行去扫描的记录数

filted: 起作用的比例

!!!上线前sql一定要进行预执行

explain变种

const: 表中只有一条元素匹配时为system.

最好达到range级别，最差要到ref级别。

utf-8: 数字或者字母= 1字节 ，汉子占三个字节。

type 表示 关联类型

ref 代表查找值所 用到的列或者常量 ，常见的有const,字段名

Mysql学会查看sql的执行计划

首先在Mysql的服务中有 连接器、查询缓存(Mysql8 已经删除)、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现

而一条sql怎么执行是由优化器决定的, 优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

而执行计划就是优化器优化后的sql的执行的详细方案

Mysql中查看执行计划的方式有两种 : 1. 使用desc    2.使用 explain  使用它俩的效果是一样的

接下来要通过执行计划知道sql是怎么执行的

执行计划中有几个重要的字段, 分别是 

id,  table,  type,  possible_keys,  key,  key_len, Extra

id :  可以通过ID来查看在多表联查中sql是先查询哪张表的 id相同的从上往下依次执行,id不同的id大的先执行

table :   table当然就是查询的表名

type :  查询的类型   查询类型分为  ALL,  index,  range,  ref , eq_ref, const(system),  null

    ALL: 指的全盘扫描,没有走任何索引   查询结果集大于25% 优化器可能会走全盘扫描   字符串查询的时候一定要加"" 不然可能会全索引扫描(隐式转换)   统计信息 失效 或者 过旧 也可能走全盘扫描  因为优化器会参考统计信息来制定执行计划

   index: 全索引扫描  就是扫描整颗索引树

       range: 索引范围  查询索引树的一部分范围   范围索引中     =  =  like  的效率会比  or   in  的效率高, 使用like %再前面的不走索引

ref:   辅助索引的等值查询            

                当查询的数据量小,优化器也有可能会走索引的全盘扫描  这里我就不贴图了;

   eq_ref : 多表连接查询中,被连接的表的连接条件列是主键或者唯一键

   const(system): 主键 或者 唯一键 的等值查询

           null: 没有数据

他们的性能是依次递增的 全盘扫描性能最差,  const性能最高

possible_keys:  查询过程中可能用到的索引

key: 真正使用到的索引

key_len:  走索引的长度

    这个是怎么计算的呢?  

   key_len 的计算方法 :

int 类型最长存储4个字节长度的数字  有not null  是4字节  没有的话会花1字节存储是不是null

tinyint 最大存储一个字节    也会花1字节来判断是不是null

字符串类型 : 字符集 utf8mb4  1-4字节

varchar超过255会预留2个字节存储长度 没超预留1个字节

key_len 永远是你设置的长度的最大的  

联合索引可以通过key_len 来判断走了几个索引

    使用desc format=json select * from table 可以查看详细情况

filtered:  索引扫描过滤掉数据的占比

Extra: 额外的信息 

    Using filesort ：MySQL 对数据在sql层进行了排序，而不是按照表内的索引进行排序读 取。 效率比较低

    Using temporary ：使用临时表保存中间结果，也就是说 MySQL 在对查询结果排序时使用了临时表，常见于order by 或 group by。

    Using index ：表示 SQL 操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高。

    Using index condition ：表示 SQL 操作命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录。

    Using where ：表示 SQL 操作使用了 where 过滤条件。

    Select tables optimized away ：基于索引优化 MIN/MAX 操作或者 MyISAM 存储引擎优化 COUNT(*) 操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即可完成优化。

Using join buffer (Block Nested Loop) ：表示 SQL 操作使用了关联查询或者子查询，且需要进行嵌套循环计算

怎么去看懂mysql的执行计划

mysql的查看执行计划的语句很简单，explain+你要执行的sql语句就OK了。

举一个例子

EXPLAIN SELECT * from employees where employees.gender='M'

返回的结果如下：

这些结果都代表什么？

id是一组数字，表示查询中执行select子句或操作表的顺序。

如果id相同，则执行顺序从上至下。

如果是子查询，id的序号会递增，id越大则优先级越高，越先会被执行。

id如果相同，则可以认为是一组，从上往下顺序执行，所有组中，id越高，优先级越高，越容易执行。

selecttype有simple，primary，subquery，derived(衍生)，union，unionresult。

simple表示查询中不包含子查询或者union。

当查询中包含任何复杂的子部分，最外层的查询被标记成primary。

在select或where列表中包含了子查询，则子查询被标记成subquery。

在from的列表中包含的子查询被标记成derived。