SQL优化——MySQL中执行计划的使用

Myth丶恋晨 2023-06-13 13:26 6阅读 0赞

**目录**

1 执行计划是什么

2  在mysql中查看执行计划

2.1 准备数据

2.2 查看执行计划

3 详细说明执行计划结果各个字段的含义

3.1 id

3.2 select\_type

3.3 table

3.4 type

3.5 possible\_keys

3.6 key

3.7 key\_len

3.8 ref

3.9 rows

4.0 Extra

--------------------

## 1 执行计划是什么 ##

我们将一个查询的SQL在数据库中执行后，会得到我们想要的结果数据，但我们并不知道数据库是如何去解析这个SQL，也不知道我们预想中应该起作用的索引有没有生效。此时，有一个可视化界面展示給用户这方面的信息就很重要了，而执行计划就以一个表格的形式展示了这些我们想知道的信息。

## 2  在mysql中查看执行计划 ##

### 2.1 准备数据 ###

这里创建两张表，user\_info与role\_info，以user\_info的role\_id字段与role\_info表的id字段关联，建表语句如下。

CREATE TABLE `user_info` (
      `id` bigint(32) NOT NULL,
      `name` varchar(32) DEFAULT NULL,
      `age` int(3) DEFAULT NULL,
      `address` varchar(32) DEFAULT NULL,
      `nick_name` varchar(32) DEFAULT NULL,
      `role_id` bigint(32) DEFAULT NULL,
      `create_time` date DEFAULT NULL,
      `update_time` date DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_name_age_nick` (`name`,`age`,`nick_name`) USING BTREE,
      KEY `idx_age` (`age`) USING BTREE,
      KEY `idx_nick_name` (`nick_name`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `role_info` (
      `id` bigint(32) NOT NULL DEFAULT '0',
      `name` varchar(32) DEFAULT NULL,
      `create_time` date DEFAULT NULL,
      `update_time` date DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

### 2.2 查看执行计划 ###

在mysql中只要在任意查询语句前面加上**explain或者desc**命令就可以查看该sql的执行计划，如下图所示。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k1MDY3OTgyNzg_size_16_color_FFFFFF_t_70][]

## 3 详细说明执行计划结果各个字段的含义 ##

查询SQL执行计划后，显示的是一个类似数据表的结果集，包含10列，下面详细介绍这10个字段的意义。

### 3.1 id ###

涉及多表查询的SQL，各个表的执行会有顺序，这里的**id决定了执行的顺序。**使用explain得多多行数据，在这里**id值大的先执行**；**id值相同时**，表示执行的顺序是由上至下。下面两张图展示的，一为内连接、二为包含子查询的sql。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k1MDY3OTgyNzg_size_16_color_FFFFFF_t_70 1][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k1MDY3OTgyNzg_size_16_color_FFFFFF_t_70 2][]

### 3.2 select\_type ###

表示**查询的类型**，就是说这个SQL是单表查询或者子查询、连接查询等。select\_type取值为一下几种，查询的效率由上至下是越来越慢的。

SIMPLE：查询中不包含子查询或者UNION，通常是单表查询，为查询速度最快的查询；

PRIMARY：查询中包含子查询的sql，在最外层查询的select\_type会为此，且只有一个；

SUBQUERY：子查询语句；

DERIVED：from子句中出现的子查询，该结果存在于临时表中；

UNION:：union连接两个select查询，第二个SELECT出现在UNION之后，标记为UNION；

UNION RESULT： 从UNION结果中进行SELECT，因其不参与对数据表的操作，只是临时表，所以id为null。

### 3.3 table ###

显而易见，table列表示的是查询的是哪张表。

### 3.4 type ###

type是执行计划中的一个重要指标，表示访问类型，跟索引有很大的关系，简单说就是一个查询是扫描了全表还是索引起到了它该有的作用。大致有以下几个值，从上至下表示效率由快至慢。一般来说在进行优化时，需**保证至少达到range级别，最好达到ref级别**。

NULL：不访问表，直接返回结果，比如SELECT 2 FROM DUAL;

system：访问的表只有一行数据或是空表；

const：通过索引一次就查到结果，只返回一条记录，通常是指通过主键查找或者唯一索引；

eq\_ref：多表关联查询，且查出的记录只有一条，常出现在主键查询或者唯一索引；

ref：扫描非唯一性索引，返回的结果可能会有多行。

range：走索引，在WHERE之后使用BETWEEN, <,>,in等操作；

index：遍历了整个索引树，性能不高；

all：遍历全表。

### 3.5 possible\_keys ###

显示可能应用在该表的索引。

### 3.6 key ###

显示实际走的索引，若为null则表示没有走索引。

### 3.7 key\_len ###

显示索引使用的字节数，在不损失精度的条件下，该字段值越小越好。

### 3.8 ref ###

通常在索引生效时，它的值是const。

### 3.9 rows ###

显示查询扫描行的数量。

### 4.0 Extra ###

显示以上字段没有展示出来的信息，需关注的值有以下几种。

**      （1）using filesort**：按“文件排序”；

**（2）using temporary**： 用到了临时表存储结果；

**（3）using index**：用到了索引，效率较好。

当出现using filesort或者using temporay时效率时这个sql的效率是比较低的，如果数据量大，需要优化。

在前面我创建了一张user\_info表，这张表的主键是id，age字段上有索引，address字段上没建索引。

（1）首先我以id进行排序，查看它的执行计划。

explain SELECT * FROM user_info ORDER BY id;

**Extra字段的值为null**，这是正常的。

（2）我用没有建立索引的字段address字段进行排序。

explain SELECT * FROM user_info ORDER BY address;

此时**Extra字段的值为Using filesort，**可以考虑对其优化。

（3）使用建立了索引的字段age进行排序。

explain SELECT * FROM user_info ORDER BY age;

**     **   此时**Extra字段值依然为Using filesort**

（4）只查询索引字段。

explain SELECT age FROM user_info ORDER BY age;

此时**Extra字段值为Using index**，用到了索引，是高效的。

**using temporary在使用group by语句的时候会出现，**解决的办法也类似，**给字段加索引。**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k1MDY3OTgyNzg_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2019111716333867.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k1MDY3OTgyNzg=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k1MDY3OTgyNzg_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191117165032205.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k1MDY3OTgyNzg=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k1MDY3OTgyNzg_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191117170256534.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k1MDY3OTgyNzg=,size_16,color_FFFFFF,t_70