读懂MySQL的explain

亦凉 2023-02-25 08:38 62阅读 0赞

关于MySQL的优化，我们可以查看[官方文档][Link 1]！

这里就explain查看MySQL优化器的执行计划作简单的分析，可以据此做SQL优化！

提示：相关例子的数据库表结构在文末附上！

首先看一个简单例子：  
![在这里插入图片描述][20200712123148680.png]  
可看到执行explain后会返回很多字段信息，以下为所返回的字段信息对应的解释：

<table> 
 <thead> 
  <tr> 
   <th>字段</th> 
   <th>内容解释</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>select查询的序列号；id相同，执行顺序由上至下；id不同，id的序号会递增，id值越大优先级越高，越先被执行</td> 
  </tr> 
  <tr> 
   <td>select_type</td> 
   <td>查询的类型</td> 
  </tr> 
  <tr> 
   <td>table</td> 
   <td>当前访问的数据表名</td> 
  </tr> 
  <tr> 
   <td>partitions</td> 
   <td>匹配的分区</td> 
  </tr> 
  <tr> 
   <td>type*</td> 
   <td>查询使用的访问类型</td> 
  </tr> 
  <tr> 
   <td>possible_keys</td> 
   <td>显示可能应用在这张表中的索引，0到n个（Null表示没有），但不代表实际会使用到</td> 
  </tr> 
  <tr> 
   <td>key*</td> 
   <td>实际使用到的索引，Null表示没有使用索引</td> 
  </tr> 
  <tr> 
   <td>key_len</td> 
   <td>索引使用的字节数长度。在不损失精确性的情况下，长度越短越好；计算规则可自行搜索</td> 
  </tr> 
  <tr> 
   <td>ref</td> 
   <td>与索引作比较的列；显示索引的哪一列被使用了，表示哪些列或常量被用于查找索引列上的值；属性值通常为库.表.字段（比如test.user.id）,或者const常量</td> 
  </tr> 
  <tr> 
   <td>rows*</td> 
   <td>根据表统计信息以及索引选用情况，大致估算出找到所需的记录所需要读取的行数，所以越小越好。可以用来查询sql的读取行数</td> 
  </tr> 
  <tr> 
   <td>filtered</td> 
   <td>查询条件过滤的行数的百分比</td> 
  </tr> 
  <tr> 
   <td>Extra*</td> 
   <td>额外的信息</td> 
  </tr> 
 </tbody> 
</table>

**注：加星号\*表示这些字段反馈出来的信息很重要。**

下面对逐个字段进行详细的分析：

**1. id**  
select查询的序列号，在涉及多表查询或者包含子查询的时候，可以显示各张表或者每个select的执行顺序；当id相同，执行顺序由上至下；当id不同，id的序号会递增，id值越大优先级越高，越先被执行；  
![在这里插入图片描述][20200712124642116.png]  
如上图中，别名为t3的表先执行，然后在t1和t2表中，依据id相同则由上至下的执行顺序，t2会比t1先执行。

**2. select\_type**  
查询的类型

<table> 
 <thead> 
  <tr> 
   <th>select_type查询类型</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>SIMPLE</td> 
   <td>简单查询，不使用UNION或子查询</td> 
  </tr> 
  <tr> 
   <td>PRIMARY</td> 
   <td>查询中包含union或者子查询，最外层查询被标记为primary主查询</td> 
  </tr> 
  <tr> 
   <td>UNION</td> 
   <td>UNION中第二个或之后的SELECT语句</td> 
  </tr> 
  <tr> 
   <td>DEPENDENT UNION</td> 
   <td>UNION中第二个或之后的SELECT语句取决于外面的查询</td> 
  </tr> 
  <tr> 
   <td>UNION RESULT</td> 
   <td>UNION的结果</td> 
  </tr> 
  <tr> 
   <td>SUBQUERY</td> 
   <td>子查询中的第一个SELECT</td> 
  </tr> 
  <tr> 
   <td>DEPENDENT SUBQUERY</td> 
   <td>子查询中的第一个SELECT, 取决于外面的查询</td> 
  </tr> 
  <tr> 
   <td>DERIVED</td> 
   <td>衍生表(FROM子句中的子查询)</td> 
  </tr> 
  <tr> 
   <td>MATERIALIZED</td> 
   <td>物化子查询</td> 
  </tr> 
  <tr> 
   <td>UNCACHEABLE SUBQUERY</td> 
   <td>结果集无法缓存的子查询，必须重新评估外部查询的每一行</td> 
  </tr> 
  <tr> 
   <td>UNCACHEABLE UNION</td> 
   <td>UNION中第二个或之后的SELECT，属于无法缓存的子查询</td> 
  </tr> 
 </tbody> 
</table>

**3. table**  
表名，不仅指存在的物理表，还包括临时表等，所以会存在以下的值：  
`<unionM,N>`: 引用id为M和N UNION后的结果  
`<derivedN>`: 引用id为N的结果派生出的表。派生表可以是一个结果集，例如派生自FROM中子查询的结果。  
`<subqueryN>`: 引用id为N的子查询结果物化得到的表。即生成一个临时表保存子查询的结果。

**4. partitions**  
版本5.7以前，该项是explain partitions显示的选项，5.7以后成为了默认选项。该列显示的为分区表命中的分区情况。非分区表该字段为空（null）。

**5. type**  
查询使用的访问类型，以下列表的访问类型的性能由上至下从最好到最差：

<table> 
 <thead> 
  <tr> 
   <th>访问类型</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>NULL</td> 
   <td>不需要访问表或者索引</td> 
  </tr> 
  <tr> 
   <td>system</td> 
   <td>表中只有一行数据或者是空表，这是const类型的一个特例，且只能用于MyISAM和MEMORY表；如果是InnoDB引擎表，type列在这个情况通常都是all或者index</td> 
  </tr> 
  <tr> 
   <td>const</td> 
   <td>最多只有一行记录匹配；const用于比较主键索引或者唯一索引，因为只匹配一行数据，所以很快；其他数据库也叫做唯一索引扫描</td> 
  </tr> 
  <tr> 
   <td>eq_ref</td> 
   <td>多表join时，对于来自前面表的每一行，在当前表中只能找到一行。这可能是除了system和const之外最好的类型。当主键或唯一非NULL索引的所有字段都被用作join联接时会使用此类型</td> 
  </tr> 
  <tr> 
   <td>ref</td> 
   <td>多表join时，对于来自前面表的每一行，在此表的索引中可以匹配到多行。若联接只用到索引（比如组合索引）的最左前缀或索引不是主键或唯一索引时，使用ref类型（也就是说，此联接能够匹配多行记录）ref可用于使用’=‘或’&lt;=&gt;'操作符作比较的索引列。</td> 
  </tr> 
  <tr> 
   <td>fulltext</td> 
   <td>使用全文索引的时候是这个类型。要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引</td> 
  </tr> 
  <tr> 
   <td>ref_or_null</td> 
   <td>跟ref类型类似，只是增加了null值的比较。实际用的不多。</td> 
  </tr> 
  <tr> 
   <td>index_merge</td> 
   <td>表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取多个索引，性能可能大部分时间都不如range</td> 
  </tr> 
  <tr> 
   <td>unique_subquery</td> 
   <td>用于where中的in形式子查询，子查询返回不重复值唯一值，可以完全替换子查询，效率更高。该类型替换了下面形式的IN子查询的ref：value IN (SELECT primary_key FROM single_table WHERE some_expr)</td> 
  </tr> 
  <tr> 
   <td>index_subquery</td> 
   <td>该联接类型类似于unique_subquery。适用于非唯一索引，可以返回重复值。</td> 
  </tr> 
  <tr> 
   <td>range</td> 
   <td>索引范围查询，常见于使用 =, &lt;&gt;, &gt;, &gt;=, &lt;, &lt;=, IS NULL, &lt;=&gt;, BETWEEN, IN()或者like等运算符的查询中。</td> 
  </tr> 
  <tr> 
   <td>index</td> 
   <td>索引全表扫描，把索引从头到尾扫一遍。这里包含两种情况：一种是查询使用了覆盖索引，那么它只需要扫描索引就可以获得数据，这个效率要比全表扫描要快，因为索引通常比数据表小，而且还能避免二次查询。在extra中显示Using index，反之，如果在索引上进行全表扫描，没有Using index的提示。</td> 
  </tr> 
  <tr> 
   <td>ALL</td> 
   <td>全表扫描，性能最差</td> 
  </tr> 
 </tbody> 
</table>

**6. possible\_keys**  
显示可能应用在这张表中的索引，0到n个（Null表示没有），但不代表实际会使用到

**7. key**  
实际使用到的索引，Null表示没有使用索引

**8. key\_len**  
索引使用的字节数长度

**9. ref**  
与索引作比较的列；如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func;  
![在这里插入图片描述][20200712132716447.png]如上图所示，这是一条连接查询，t1的rel为test.t2.user\_id，意思为test库中别名为t2的数据表的user\_id字段，对应着关联条件 t1.id = t2.user\_id；

**10. rows**  
根据表统计信息以及索引选用情况，大致估算出找到所需的记录所需要读取的行数，所以越小越好。可以用来查询sql的读取行数

**11. filtered**  
查询条件过滤的行数的百分比

**12. Extra**  
额外信息

<table> 
 <thead> 
  <tr> 
   <th>属性值</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Using filesort</td> 
   <td>表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大</td> 
  </tr> 
  <tr> 
   <td>Using temporary</td> 
   <td>查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化</td> 
  </tr> 
  <tr> 
   <td>Using index</td> 
   <td>“覆盖索引扫描”, 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错</td> 
  </tr> 
  <tr> 
   <td>Using where</td> 
   <td>表示使用了where条件，当where的条件字段不是索引字段</td> 
  </tr> 
  <tr> 
   <td>Using join buffer</td> 
   <td>表示使用了连接缓存</td> 
  </tr> 
  <tr> 
   <td>Impossible WHERE</td> 
   <td>where子句的值总是false，不能用来获取任何元组</td> 
  </tr> 
  <tr> 
   <td>distinct</td> 
   <td>在select部分使用了distinc关键字，一旦找到了与行相联合匹配的行，就不在搜索了</td> 
  </tr> 
  <tr> 
   <td>Select tables optimized away</td> 
   <td>表示select操作已经是最优化的状态了</td> 
  </tr> 
 </tbody> 
</table>

**附SQL语句：**

角色表与数据：

CREATE TABLE `role`  (
      `id` bigint(20) NOT NULL COMMENT '角色id',
      `code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '角色编码',
      `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '角色名称',
      `description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '描述',
      `created_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      `updated_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间',
      `created_by` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人',
      `updated_by` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '更新人',
      `deleted` int(2) NULL DEFAULT 0 COMMENT '是否已删除 1已删除 0未删除',
      `remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
      `version` bigint(15) NULL DEFAULT 0 COMMENT '版本号',
      PRIMARY KEY (`id`) USING BTREE,
      UNIQUE INDEX `uni_code`(`code`) USING BTREE COMMENT 'code唯一索引'
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '后台角色表' ROW_FORMAT = Dynamic;
    
    INSERT INTO `role` VALUES (1252533748180717890, 'R001', '超级管理员', '测试', '2020-01-08 09:28:33', '2020-07-12 12:12:35', NULL, NULL, 0, NULL, 0);

用户表与数据：

CREATE TABLE `user`  (
      `id` bigint(20) NOT NULL COMMENT '用户id',
      `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名',
      `password` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '密码密文',
      `email` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '邮箱',
      `phone` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机号码',
      `created_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      `updated_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间',
      `created_by` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人',
      `updated_by` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '更新人',
      `deleted` int(2) NULL DEFAULT 0 COMMENT '是否已删除 1已删除 0未删除',
      `remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
      `version` bigint(15) NULL DEFAULT 0 COMMENT '版本号',
      PRIMARY KEY (`id`) USING BTREE,
      UNIQUE INDEX `uni_phone`(`phone`) USING BTREE COMMENT '手机号唯一索引',
      INDEX `idx_username_phone`(`username`, `phone`) USING BTREE COMMENT '用户名+手机号组合索引'
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '后台用户表' ROW_FORMAT = Dynamic;
    
    INSERT INTO `user` VALUES (1252533748180717570, 'admin', '$2a$10$vDAifcQnl.wo5vdKr2W.j.IdqqAehLJrARa4wvjrOeJgxBz2jq1he', '12345678910@163.com', '12345678910', '2019-11-24 23:11:18', '2020-07-12 12:11:59', NULL, NULL, 0, '', 0);

用户角色关联表与数据：

CREATE TABLE `user_role_relation`  (
      `id` bigint(20) NOT NULL COMMENT '关系id',
      `user_id` bigint(20) NOT NULL COMMENT '用户id',
      `role_id` bigint(20) NOT NULL COMMENT '角色id',
      `created_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      `updated_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间',
      `created_by` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人',
      `updated_by` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '更新人',
      `deleted` int(2) NULL DEFAULT 0 COMMENT '是否已删除 1已删除 0未删除',
      `remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
      `version` bigint(15) NULL DEFAULT 0 COMMENT '版本号',
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '后台用户和角色关系表' ROW_FORMAT = Dynamic;
    
    INSERT INTO `user_role_relation` VALUES (1252533748180725550, 1252533748180717570, 1252533748180717890, '2020-01-08 09:29:10', '2020-07-12 12:12:55', NULL, NULL, 0, NULL, 0);

[Link 1]: https://www.mysqlzh.com/doc/64.html
[20200712123148680.png]: /images/20230209/4bbe75f0b4f245c9be5b42dc4b32fc85.png
[20200712124642116.png]: https://img-blog.csdnimg.cn/20200712124642116.png
[20200712132716447.png]: https://img-blog.csdnimg.cn/20200712132716447.png