MySQL优化之理解查询计划

最新推荐文章于 2024-04-09 18:40:31 发布

皛皛

最新推荐文章于 2024-04-09 18:40:31 发布

阅读量299

点赞数

分类专栏：数据库文章标签： mysql sql

本文链接：https://blog.csdn.net/lcl_xiaowugui/article/details/119355415

版权

数据库专栏收录该内容

13 篇文章 1 订阅

订阅专栏

参考《MySQL 是怎样运行的：从根儿上理解 MySQL》作者:小孩子4919

参考《MySQL 官方文档》

要进行数据库 SQL 优化必须先理解一条 SQL 具体是怎么执行的。MySQL 提供了 EXPLAIN 来描述优化器处理 SQL 的规则以及流程。本文会详细解释 EXPLAIN 各字段的含义以及不同结果所代表的意义，从而帮助理解查询计划，为 SQL 优化做基础。

一、总览图

在这里插入图片描述

二、EXPLAIN 概述

EXPLAIN 中会解释 MySQL 是如何处理语句，包括表的连接方式以及处理顺序等信息。从 MySQL 5.6.3版本开始，EXPLAIN 可以分析SELECT、DELETE、INSERT、REPLACE 和 UPDATE 语句，在这之前，仅可以分析 SELECT 语句。

EXPLAIN 通常会跟具体语句来进行解析查看，也可以直接查看连接执行 SQL 情况。eg：EXPLAIN for connection 403326;

Note: EXPLAIN tb_name;也可用来查看对应表中列信息，等同于SHOW COLUMNS FROM tb_name;

三、EXPLAIN 输出格式

1、EXPLAIN 输出列

列名	json 名称	意义
id	select_id	表 id 号
select_type	None	select 查询类型
table	table_name	表名
partitions	partitions	匹配的分区
type	access_type	连接类型
possible_keys	possible_keys	可能的索引选择
key	key	实际选择的索引
key_len	key_length	所选key的长度
ref	ref	与索引比较的列
rows	rows	估计需要扫描的行数
filtered	filtered	按表条件过滤的行的百分比
Extra	None	附加信息

2、各列详解

以下 SQL 示范数据均来自于官方提供的 employees 库，可点击下载测试数据

2.1 id(JSON name: select_id)

一般查询语句都以 SELECT 关键字开头，查询中每出现一个 SELECT 关键字，MySQL就会为其分配一个唯一的 id 值。这个值就是 EXPLAIN 结果的第一列。例如下面的简单查询只有一个 SELECT 关键字，所以 EXPLAIN 的结果就只有 id 为 1 的一条记录：

(mysql)@[employees]>explain select * from departments where dept_no = 'd009';
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | departments | NULL       | const | PRIMARY       | PRIMARY | 12      | const |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

对于连接查询来说，一个 SELECT 语句之后的 FROM 可能会有多个表，在执行计划中，每张表都会有一条记录，但这些记录的 id 号是相同的。例如：

(mysql)@[employees]>explain select * from dept_manager inner join departments using(dept_no);
+----+-------------+--------------+------------+-------+---------------+-----------+---------+-------------------------------+------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys | key       | key_len | ref                           | rows | filtered | Extra       |
+----+-------------+--------------+------------+-------+---------------+-----------+---------+-------------------------------+------+----------+-------------+
|  1 | SIMPLE      | departments  | NULL       | index | PRIMARY       | dept_name | 122     | NULL                          |    9 |   100.00 | Using index |
|  1 | SIMPLE      | dept_manager | NULL       | ref   | dept_no       | dept_no   | 12      | employees.departments.dept_no |    2 |   100.00 | NULL        |
+----+-------------+--------------+------------+-------+---------------+-----------+---------+-------------------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

在上述id 相同的情况下，出现在前面的表会先被执行，这张表被称为驱动表，后面的表叫做被驱动表。所以从上面的结果来看，优化器打算让 departments 表做为驱动表，让 dept_manager 表做为被驱动表。

对于包含子查询的语句可能会涉及到多个 SELECT 关键字，所以在包含子查询的查询执行计划中，每个 SELECT 关键字都会对应一个独立的 id 号。在多个 id 值的情况下，id 值越大的会先执行。例如：

(mysql)@[employees]>explain select * from dept_manager where dept_no in (select dept_no from departments where dept_no < 'd015') or from_date < '2000-01-01';
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table        | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | PRIMARY     | dept_manager | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |   24 |   100.00 | Using where              |
|  2 | SUBQUERY    | departments  | NULL       | range | PRIMARY       | PRIMARY | 12      | NULL |    9 |   100.00 | Using where; Using index |
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
2 rows in set, 1 warning (0.01 sec)

但有时 MySQL 会将 in 子查询重写为连接查询，可通过 EXPLAIN 的 id 是否相同以及 show warnings 的结果来了解是否进行了重写。例如：

(mysql)@[employees]>explain select * from dept_manager where dept_no in (select dept_no from departments where dept_no < 'd015');
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------------------------------+------+----------+--------------------------+
| id | select_type | table        | partitions | type  | possible_keys | key     | key_len | ref                           | rows | filtered | Extra                    |
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------------------------------+------+----------+--------------------------+
|  1 | SIMPLE      | departments  | NULL       | range | PRIMARY       | PRIMARY | 12      | NULL                          |    9 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | dept_manager | NULL       | ref   | dept_no       | dept_no | 12      | employees.departments.dept_no |    2 |   100.00 | NULL                     |
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------------------------------+------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select `employees`.`dept_manager`.`emp_no` AS `emp_no`,`employees`.`dept_manager`.`dept_no` AS `dept_no`,`employees`.`dept_manager`.`from_date` AS `from_date`,`employees`.`dept_manager`.`to_date` AS `to_date` from `employees`.`departments` join `employees`.`dept_manager` where ((`employees`.`dept_manager`.`dept_no` = `employees`.`departments`.`dept_no`) and (`employees`.`departments`.`dept_no` < 'd015'))

可以看到，上述虽然 SQL 中包含了子查询，但两条记录 id 都为1，并且可以看到 SQL 被写为了连接查询。

2.2 select_type

MySQL 为每个 SELECT 子查询都定义了 select_type 的属性，根据这个属性就可以快速知道该子查询在整体查询中扮演的角色。

常见的类型如下：

SIMPLE
- 查询中不包含子查询或者 UNION 查询的都属于 SIMPLE 类型。单表查询和连接查询都属于该类型
PRIMARY
- 对于包含 UNION、子查询等 SQL 来说，它是由小查询组成。其中最左边或最外层的查询会被标记为 PRIMARY 类型
UNION
- 对于包含 UNION、UNION ALL 的查询，除了最左边的查询，其余的小查询均被标记为 UNION 类型
UNION RESULT
- 对于 UNION 查询来说，MySQL 会在内部创建一个临时表并对结果进行去重操作。针对该临时表的查询操作会被标记为 UNION RESULT类型
SUBQUERY
- 包含子查询的查询语句不能转换为半连接(semi-join)的形式，并且该查询是不相关子查询，查询优化器会将该子查询物化。此时子查询对应的第一个 SELECT 关键字的查询则被标记为 SUBQUERY
- 非相关子查询是独立于外部查询的子查询，子查询总共执行一次，执行完毕后将值传递给外部查询
- 相关子查询的执行依赖于外部查询的数据，外部查询执行一行，子查询就执行一次
- 非相关子查询比相关子查询效率高
DEPENDENT SUBQUERY
- 包含子查询的查询语句不能转换为半连接(semi-join)的形式，并且该查询是相关子查询。该子查询的第一个 SELECT 关键字对应的类型则为 DENENDENT SUBQUERY。该子查询可能会被执行多次
DERIVED
- 采用物化子查询的方式执行的包含派生表的查询。该派生表对应子查询的类型为 DERIVED
MATERIALIZED
- 对于包含子查询的 SQL，优化器选择将子查询物化之后和外层查询进行连接查询时，该子查询对应的类型为 MATERIALIZED

2.3 table(JSON name: table_name)

每行所涉及的表的名称。有以下一些特殊的值：

<unionM, N>：EXPLAIN 结果中 id 为 M 和 N 的两条记录结果并集的临时表，一般用于 UNION 去重
<derivedN>：EXPLAIN 结果中 id 为 N 的行的派生表结果。派生表一般指 FROM 子句中的子查询
<subqueryN>：EXPLAIN 结果中对 id 为 N 的行的物化子查询的结果

2.4 partitions(JSON name: partitions)

分区表中查询匹配记录的分区。未使用分区表该值为 NULL

2.5 type(JSON name: access_type)

表示 MySQL 对某个表的访问方法。完整的访问方法包含：NULL、system、const、eq_ref、ref、fulltext、ref_or_null、index_merge、unique_subquery、index_subquery、range、index、ALL。常见访问方式如下：

NULL

MySQL 不用访问索引或表就可以获得结果。例如：SELECT sysdate();

system

表中只有一条记录并且存储引擎的统计信息是准确的。例如使用了 MyISAM 引擎的表的统计信息是准确的。

-- 创建一张测试表
CREATE TABLE `t_aoe` (
  `id` int(11) NOT NULL,
  `name` varchar(32) DEFAULT NULL COMMENT 'name',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='aoe';

-- 插入一条数据
insert into t_aoe(id, name) values(1,'aaa');

(mysql)@[employees]>explain select * from t_aoe;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t_aoe | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select `employees`.`t_aoe`.`id` AS `id`,`employees`.`t_aoe`.`name` AS `name` from `employees`.`t_aoe`

-- 引擎改为 MyISAM 引擎
(mysql)@[employees]>alter table t_aoe engine = MyISAM;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

(mysql)@[employees]>explain select * from t_aoe;      
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t_aoe | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select '1' AS `id`,'aaa' AS `name` from dual

可以看到当存储引擎是 MyISAM 统计信息准确并且表中只有一条记录时，type 为 system，而为 InnoDB 这类估算的统计信息时，type 为 ALL。

const

根据主键或唯一索引与常量进行等值比较时，会使用 const 访问方式。

(mysql)@[employees]>explain select * from departments where dept_no = 'd009';
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | departments | NULL       | const | PRIMARY       | PRIMARY | 12      | const |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select 'd009' AS `dept_no`,'Customer Service' AS `dept_name` from `employees`.`departments` where ('d009' = 'd009')

eq_ref

进行连表查询时，被驱动表是通过主键或折唯一索引的方式来进行访问，则会使用 eq_ref 这种访问方式。如果主键或唯一索引是联合索引，则所有字段都必须是等值比较。

(mysql)@[employees]>explain select * from departments inner join dept_emp on departments.dept_no = dept_emp.dept_no and dept_emp.emp_no = 1; 
+----+-------------+-------------+------------+--------+-----------------+---------+---------+----------------------------+------+----------+-------+
| id | select_type | table       | partitions | type   | possible_keys   | key     | key_len | ref                        | rows | filtered | Extra |
+----+-------------+-------------+------------+--------+-----------------+---------+---------+----------------------------+------+----------+-------+
|  1 | SIMPLE      | dept_emp    | NULL       | ref    | PRIMARY,dept_no | PRIMARY | 4       | const                      |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | departments | NULL       | eq_ref | PRIMARY         | PRIMARY | 12      | employees.dept_emp.dept_no |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+--------+-----------------+---------+---------+----------------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select `employees`.`departments`.`dept_no` AS `dept_no`,`employees`.`departments`.`dept_name` AS `dept_name`,`employees`.`dept_emp`.`emp_no` AS `emp_no`,`employees`.`dept_emp`.`dept_no` AS `dept_no`,`employees`.`dept_emp`.`from_date` AS `from_date`,`employees`.`dept_emp`.`to_date` AS `to_date` from `employees`.`departments` join `employees`.`dept_emp` where ((`employees`.`dept_emp`.`emp_no` = 1) and (`employees`.`departments`.`dept_no` = `employees`.`dept_emp`.`dept_no`))

ref

通过普通的二级索引与常量进行等值匹配时来访问某个表。

ref_or_null

和 ref 类似，只不过索引列的值可以为 NULL，则可能使用 ref_or_null 访问方式。

-- t1 表结构如下，并且已经写入部分数据
CREATE TABLE `t1` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(11) DEFAULT '',
  `iState` varchar(11) DEFAULT '',
  `col` varchar(32) DEFAULT NULL COMMENT 'col',
  `col2` varchar(32) DEFAULT NULL COMMENT 'col2',
  `col27` varchar(32) DEFAULT NULL COMMENT 'col4',
  `col100` varchar(32) DEFAULT NULL COMMENT '这里是该列的注释内容',
  `col3` varchar(32) NOT NULL DEFAULT '' COMMENT 'col3',
  `col5` varchar(32) DEFAULT NULL COMMENT 'col5',
  `col6` varchar(32) DEFAULT '' COMMENT 'col6',
  PRIMARY KEY (`id`),
  KEY `idx_emp_no` (`emp_no`),
  KEY `idx_salary` (`salary`),
  KEY `idx_col2` (`col2`)
) ENGINE=InnoDB AUTO_INCREMENT=11376198 DEFAULT CHARSET=utf8;

(mysql)@[employees]>explain select * from t1 where col2 = 'lcl' or col2 is null;
+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+---------+----------+-----------------------+
| id | select_type | table | partitions | type        | possible_keys | key      | key_len | ref   | rows    | filtered | Extra                 |
+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+---------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | ref_or_null | idx_col2      | idx_col2 | 99      | const | 5523271 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+---------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

index_merge

在有些 SQL 中，一个查询可能会使用到多个二级索引，这种优化的方式被称为索引合并，但索引合并有时不一定比全表查询快。

(mysql)@[employees]>explain select * from t1 where emp_no = 10000 or salary = 2000;
+----+-------------+-------+------------+-------------+-----------------------+-----------------------+---------+------+------+----------+-------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                                           |
+----+-------------+-------+------------+-------------+-----------------------+-----------------------+---------+------+------+----------+-------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | index_merge | idx_emp_no,idx_salary | idx_emp_no,idx_salary | 4,4     | NULL |    2 |   100.00 | Using union(idx_emp_no,idx_salary); Using where |
+----+-------------+-------+------------+-------------+-----------------------+-----------------------+---------+------+------+----------+-------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

unique_subquery

这是针对一些包含 IN 的子查询的 SQL 语句，如果优化器将 IN 改写为 EXISTS 子查询，而且子查询可以用到主键或唯一索引进行匹配，该子查询会为 unique_subquery 类型。

-- IN 子句中类似于以下格式
  value IN (SELECT primary_key FROM single_table WHERE some_expr)

(mysql)@[employees]>explain select * from employees where emp_no in (select id from t1 where t1.id > 10000) or birth_date > '1990-01-01';           
+----+--------------------+-----------+------------+-----------------+---------------+---------+---------+------+--------+----------+--------------------------+
| id | select_type        | table     | partitions | type            | possible_keys | key     | key_len | ref  | rows   | filtered | Extra                    |
+----+--------------------+-----------+------------+-----------------+---------------+---------+---------+------+--------+----------+--------------------------+
|  1 | PRIMARY            | employees | NULL       | ALL             | NULL          | NULL    | NULL    | NULL | 299478 |   100.00 | Using where              |
|  2 | DEPENDENT SUBQUERY | t1        | NULL       | unique_subquery | PRIMARY       | PRIMARY | 8       | func |      1 |   100.00 | Using where; Using index |
+----+--------------------+-----------+------------+-----------------+---------------+---------+---------+------+--------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

index_subquery

和unique_subquery类似，只不过子查询使用的是普通索引

value IN (SELECT key_column FROM single_table WHERE some_expr)

(mysql)@[employees]>explain select * from employees where emp_no in (select emp_no from t1 where t1.emp_no > 10000) or birth_date > '1990-01-01'; 
+----+--------------------+-----------+------------+----------------+---------------+------------+---------+------+--------+----------+--------------------------+
| id | select_type        | table     | partitions | type           | possible_keys | key        | key_len | ref  | rows   | filtered | Extra                    |
+----+--------------------+-----------+------------+----------------+---------------+------------+---------+------+--------+----------+--------------------------+
|  1 | PRIMARY            | employees | NULL       | ALL            | NULL          | NULL       | NULL    | NULL | 299478 |   100.00 | Using where              |
|  2 | DEPENDENT SUBQUERY | t1        | NULL       | index_subquery | idx_emp_no    | idx_emp_no | 4       | func |     29 |   100.00 | Using where; Using index |
+----+--------------------+-----------+------------+----------------+---------------+------------+---------+------+--------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

range

使用索引查询记录，并且返回在给定范围内的结果。key 字段会表明使用的索引，key_len 表示用的的索引中最长字段的长度。

range 在一个索引列和常量进行一些比较操作时比较常见。例如：=、<>、>、≥、<、≤、IS NULL、<=>、BETWEEN、IN() 等

-- 一般为以下格式有可能为range类型
SELECT * FROM tbl_name
  WHERE key_column = 10;

SELECT * FROM tbl_name
  WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name
  WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name
  WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

-- 例子：
(mysql)@[employees]>explain select * from t1 where emp_no between 10000 and 20000;
+----+-------------+-------+------------+-------+---------------+------------+---------+------+--------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows   | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+--------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | range | idx_emp_no    | idx_emp_no | 4       | NULL | 792594 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+--------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

(mysql)@[employees]>explain select * from t1 where emp_no in (10000,10010,10020); 
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | range | idx_emp_no    | idx_emp_no | 4       | NULL |   34 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

(mysql)@[employees]>explain select * from titles where emp_no = 10000 and title in ('Senior Engineer', 'Staff', 'Engineer');
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | titles | NULL       | range | PRIMARY       | PRIMARY | 156     | NULL |    3 |   100.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

index

可以使用覆盖索引，但需要扫描全部的索引记录。一般用于以下两种情况：

如果是覆盖索引，可以满足所有需要的数据，也就是说查询列都为索引中的字段。这种情况下只需要扫描索引树即可，Extra 列会显示 Using index. 索引扫描一般比 ALL 全表扫描快一些，因为索引大小通常都小于表数据。
全表扫描是使用从索引中读取的记录(以索引的顺序)查找数据记录。Extra 列不显示 Using index。

(mysql)@[employees]>explain select col2 from t1 where col2 = 2000; 
+----+-------------+-------+------------+-------+---------------+----------+---------+------+----------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows     | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+----------+----------+--------------------------+
|  1 | SIMPLE      | t1    | NULL       | index | idx_col2      | idx_col2 | 99      | NULL | 11046540 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+----------+----------+--------------------------+
1 row in set, 3 warnings (0.00 sec)

ALL

全表扫描。对前表中的每个行组合进行全表扫描。如果该表是驱动表并且未标记为 const，那么性能通常会很慢。一般可以通过添加索引避免全表扫描。

2.6 possible_keys(JSON name: possible_keys)

possible_keys 列显示了 MySQL 在查找记录时可能使用到的索引，这个值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本就需要花费更多的时间，可以尽量删除那些不使用的索引。

如果这列为 NULL (JSON 格式中为 undefined)，说明这个查询没有可用的索引。这种情况可以检查 where 子句中使用的列，查看是否可以创建合适的索引，改进查询性能。

2.7 key(JSON name: key)

key 列显示了 MySQL 在查询过程中实际使用到的索引。如果 MySQL 决定使用 possible_keys 中列出的索引，则会在 key 中显示。当使用到了覆盖索引，则只会在 key 列中显示。如果为 NULL 的话则表示没有找到合适的索引。想要强制使用或忽略在 possible_keys 中列出的某个索引，可以使用 FORCE INDEX、USE INDEX 或者 IGNORE INDEX。

2.8 key_len(JSON name: key_length)

key_len 表示了当优化器决定使用哪个索引执行查询时，索引记录的最大长度。它由以下三部分组成：

对于使用固定长度的索引来说，实际占用的存储空间最大长度就是一个固定值。但对于指定了字符集的变长类型的索引列来说，为该变长类型的最大长度。例如：某个索引列的类型是 varchar(100)，使用了 utf8 字符集，那么该列实际占用的最大存储空间是 100 * 3 = 300个字节
如果该索引列可以存储 NULL 值，则 key_len 比不可以存储 NULL 值是多 1 个字节
对于变长字段来说，都会提供 2 个字节的空间来存储变长列的实际长度

-- id为bigint类型，且为 NOT NULL不允许存储NULL值，可看到 key_len 为 8
(mysql)@[employees]>explain select * from t1 where id = 1000;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

-- col2 为 varchar(32)，默认值为NULL，字符集为 utf8，key_len = 32 * 3 + 1 + 2 = 99
(dba:5011)@[employees]>explain select * from t1 where col2 = 'test';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_col2      | idx_col2 | 99      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

在 InnoDB 中，存储变长字段的长度为 1～2 个字节，EXPLAIN 直接使用了 2 字节来表示，是由于 MySQL EXPLAIN 并不会真实去引擎层执行查询，而是属于 Server 层的功能，更主要是让人们可以准确的知道联合索引情况下实际到底是用了几个索引字段。例如：

-- 以官方提供的 employees 测试库中的 titles 表为例
-- 在 titles 表中，存在一个联合主键：PRIMARY KEY (`emp_no`,`title`,`from_date`)，表结构如下：
CREATE TABLE `titles` (
  `emp_no` int(11) NOT NULL,
  `title` varchar(50) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date DEFAULT NULL,
  PRIMARY KEY (`emp_no`,`title`,`from_date`),
  CONSTRAINT `titles_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `_employees_old` (`emp_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- emp_no 为 int 类型，且不可以为 NULL，所以只使用 emp_no 索引列，此时 key_len = 4
(mysql)@[employees]>explain select * from titles where emp_no = 10000;
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | titles | NULL       | ref  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

-- 使用 emp_no 和 title 列联合进行查询，emp_no 占用 4 字节，对应 key_len1 = 4
-- title 为 varchar(50)，是变长字段，字符集为 utf8，不可以为 NULL，所以对应 key_len2 = 50 * 3 + 2 = 152
-- 最终的 key_len = key_len1 + key_len2 = 156
(mysql)@[employees]>explain select * from titles where emp_no = 10000 and title = 'engines';
+----+-------------+--------+------------+------+---------------+---------+---------+-------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key     | key_len | ref         | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | titles | NULL       | ref  | PRIMARY       | PRIMARY | 156     | const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+---------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

2.9 ref(JSON name: ref)

当 type 为 const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery ，使用索引列等值匹配的条件来进行查询时，ref 列表示和索引字段进行匹配的内容类型。一般为一个常数或某个字段列。例如：

-- ref 为 const，表示和索引列 emp_no 进行比较的是一个常数
(mysql)@[employees]>explain select * from titles where emp_no = 10000;
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | titles | NULL       | ref  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

-- ref 为 employees.employees.emp_no，说明和 t1 表 idx_emp_no 索引字段匹配查找的条件是 employees 库 employees 表的 emp_no 字段列
(mysql)@[employees]>explain select * from employees inner join t1 using (emp_no);
+----+-------------+-----------+------------+------+---------------+------------+---------+----------------------------+--------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key        | key_len | ref                        | rows   | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------------+---------+----------------------------+--------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ALL  | PRIMARY       | NULL       | NULL    | NULL                       | 299478 |   100.00 | NULL  |
|  1 | SIMPLE      | t1        | NULL       | ref  | idx_emp_no    | idx_emp_no | 4       | employees.employees.emp_no |     29 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+------------+---------+----------------------------+--------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

2.10 rows(JSON name: rows)

优化器认为的预计使用的行数。对于 InnoDB 索引来说，这是个预估值。如果使用索引来执行查询，仅表示预计扫描的索引记录行数。

2.11 filtered(JSON name: filtered)

预估被表条件过滤的记录比例。例如：

(mysql)@[employees]>explain select * from t1 where emp_no < 8000 and from_date > '1990-01-01'; 
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | idx_emp_no    | idx_emp_no | 4       | NULL |    9 |    33.33 | Using index condition; Using where |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

从上面示例可以看出，该条 SQL 使用了 idx_emp_no 索引，MySQL 预计满足 emp_no < 8000 的条数为 9 条，filtered 就表示在这 9 条记录中，有多少记录满足剩余的匹配条件，也就是说优化器预测在这 9 条记录中满足 from_date > ‘1990-01-01’ 这个条件的百分比为 33.33%。

这个参数主要是针对于连表查询，可以大概知道对被驱动表的查询次数。例如：

(mysql)@[employees]>explain select * from employees inner join t1 on employees.emp_no = t1.id where t1.col2 = 'test'; 
+----+-------------+-----------+------------+--------+------------------+----------+---------+-----------------+------+----------+-------------+
| id | select_type | table     | partitions | type   | possible_keys    | key      | key_len | ref             | rows | filtered | Extra       |
+----+-------------+-----------+------------+--------+------------------+----------+---------+-----------------+------+----------+-------------+
|  1 | SIMPLE      | t1        | NULL       | ref    | PRIMARY,idx_col2 | idx_col2 | 99      | const           |   90 |   100.00 | NULL        |
|  1 | SIMPLE      | employees | NULL       | eq_ref | PRIMARY          | PRIMARY  | 4       | employees.t1.id |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+--------+------------------+----------+---------+-----------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

从上述执行计划可以看到，MySQL 是准备将 t1 表做为驱动表，employees 表做为被驱动表，驱动表 t1 的 rows 为 99，filtered 列为 100%，这表明我们大概需要对被驱动表 employees 执行大约 99 * 100% = 99次查询。

2.12 Extra

Extra 用来描述一些其余的额外补充信息。完整信息可查看官方文档，常见的如下：

Impossible Where

查询条件永远为 false，会提示该信息

(mysql)@[employees]>explain select * from t1 where 1 = 2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set, 1 warning (0.00 sec)

Using index

表示可以使用覆盖索引的情况

Index Condition Pushdown

在同个索引中，多个索引字段都使用了范围查询时，后面的范围查询无法使用到索引优化查询。早期 MySQL 需要回表找到完整记录后再进行条件判断，再将符合条件的记录加入到结果集中。InnoDB 后面通过在索引中判断符合记录是否符合查询条件，如果这个条件不满足，就不会进入回表流程。由于回表操作是随机 I/O，比较耗时，这样改进省略了一些回表操作，这个优化被称为 Index Condition Pushdown(索引条件下推)。

-- 由于 col2条件均为范围查询，第一个条件之后本身第二个条件无法使用索引，但使用索引条件下推优化，省去了部分回表操作
(mysql)@[employees]>explain select * from t1 where col2 > 'a' and col2 like '%test%';  
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | range | idx_col2      | idx_col2 | 99      | NULL |   97 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

Using where

当 SQL 中包含 Where 条件无法使用到索引的情况，Extra 列中会出现 Using where 信息。例如：

-- col 列无索引，需要进行全表扫描
(mysql)@[employees]>explain select * from t1 where col = 'test';                     
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 11046540 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

-- col2 列有索引，并且会走对应索引，col 列无索引
(mysql)@[employees]>explain select * from t1 where col = 'test' and col2 = 'test';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_col2      | idx_col2 | 99      | const |   90 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Using join buffer(Block Nested Loop)

Join 的原理相当于先筛选出驱动表符合条件的记录，再通过连接条件逐条对被驱动表进行筛选，当表数据量比较大并且无法使用到有效索引时，需要不断地对被驱动表进行重复匹配。MySQL 针对这种情况提供了一个 join buffer 的内存空间来提高查询效率。将若干条驱动表的记录装在 join buffer 中，每条被驱动表的记录一次性和 join buffer 中的所有结果做匹配。这种算法也叫做基于块的嵌套循环算法。

(mysql)@[employees]>explain select * from employees inner join t1 on employees.birth_date = t1.from_date;    
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra                                              |
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   299478 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t1        | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 11046540 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+

从上面示例可以看出，使用 employees 表做为驱动表，t1 表做为被驱动表，由于无法使用到索引，使用了 join buffer 减少对 t1 表的访问次数，提高查询效率。在访问 t1 表时，满足驱动表 employees 条件的记录已经确定，所以每次对 t1 表的判断其实是 t1.from_date = 常量，在 Extra 中还会显示 Using where 信息。

Not exists

当使用外连接时，条件中包含被驱动表为 NULL 值的搜索条件，但字段禁止为 NULL 时，会提示 Not exists 附加信息。

-- t1 表的 id 字段为自增主键，不能为 NULL
(mysql)@[employees]>explain select * from employees left join t1 on employees.birth_date = t1.from_date where t1.id is NULL;  
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra                                                          |
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   299478 |   100.00 | NULL                                                           |
|  1 | SIMPLE      | t1        | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 11046540 |    10.00 | Using where; Not exists; Using join buffer (Block Nested Loop) |
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

Using filesort

排序操作无法使用到索引时，只能在内存或磁盘中进行排序，在内存或磁盘上进行排序的操作统称为 filesort。如果查询数据量比较大，并且需要使用 filesort 排序，一般性能是比较低的，需要尽量为排序字段添加索引避免 filesort。

(mysql)@[employees]>explain select * from employees inner join t1 on employees.emp_no = t1.emp_no order by t1.from_date limit 5;
+----+-------------+-----------+------------+--------+---------------+---------+---------+---------------------+----------+----------+----------------+
| id | select_type | table     | partitions | type   | possible_keys | key     | key_len | ref                 | rows     | filtered | Extra          |
+----+-------------+-----------+------------+--------+---------------+---------+---------+---------------------+----------+----------+----------------+
|  1 | SIMPLE      | t1        | NULL       | ALL    | idx_emp_no    | NULL    | NULL    | NULL                | 11046540 |   100.00 | Using filesort |
|  1 | SIMPLE      | employees | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | employees.t1.emp_no |        1 |   100.00 | NULL           |
+----+-------------+-----------+------------+--------+---------------+---------+---------+---------------------+----------+----------+----------------+
2 rows in set, 1 warning (0.00 sec)

在这里插入图片描述

可以看出，通过索引字段排序和使用 filesort 排序查询效率差异非常大。

Using temporary

对于一些包含 distinct、group by、union 等子句的查询，如果不能使用索引来进行查询，MySQL 会借助临时表来完成查询、去重、排序等功能。如果查询过程中使用到了临时表，则会提示 Using temporary 信息。

-- col列不是 t1 表的索引字段
(mysql)@[employees]>explain select col from t1 group by col;  
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra                           |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+---------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 11046540 |   100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)

可以看出，上述 Extra 列不仅出现了 Using temporary，还出现了 Using filesort。这是由于在 MySQL 中，group by 查询会默认添加上 order by 子句，如果不想进行排序，需要我们显式添加 order by NULL子句，避免查询时排序产生的成本。建立和维护临时表的成本非常大，对 SQL 执行效率有比较大的影响，尽量通过索引来替换掉临时表的使用。

皛皛

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL优化之理解查询计划

参考《MySQL 是怎样运行的：从根儿上理解 MySQL》作者:小孩子4919参考《MySQL 官方文档》要进行数据库 SQL 优化必须先理解一条 SQL 具体是怎么执行的。MySQL 提供了 EXPLAIN 来描述优化器处理 SQL 的规则以及流程。本文会详细解释 EXPLAIN 各字段的含义以及不同结果所代表的意义，从而帮助理解查询计划，为 SQL 优化做基础。一、总览图二、EXPLAIN 概述EXPLAIN 中会解释 MySQL 是如何处理语句，包括表的连接方式以及处理顺序等信息。
复制链接

扫一扫