Mysql 运行原理 (3) - expalin 详解

于 2023-08-17 15:05:20 发布
阅读量150 收藏
点赞数
分类专栏： 数据库 文章标签： mysql sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/qq_38074398/article/details/132300230
版权

在实际开发中，常会发生下面的两种情况：

  1. 创建了索引，未必应用到了索引
  2. 应用了索引，未必能提升查询效率

或者当遇到一些慢 SQL 时，我们想要进行优化。但又不知道导致慢的原因或者关键点在哪，所以我们需要了解慢 SQL 的执行情况。

我们可以通过下面两种方式进行查看 SQL 的执行情况：

  1. 之前提到过 optimizer_trace 工具
  2. explain 关键字，只需要加在执行的 SQL 前，就可以查看到 SQL 的执行计划列表了

explain 查询出的执行计划列表，每一条计划数据都表示着一次查询，所以执行计划列表的数据越少越好。

下面就主要介绍 explain 关键字的功能。先准备一下测试数据：

CREATE TABLE users (
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(58) NOT NULL,
	email VARCHAR(100) NOT NULL,
	password VARCHAR(160) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE products (
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(50) NOT NULL,
	price FLOAT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE orders (
	id INT PRIMARY KEY AUTO_INCREMENT,
	user_id INT NOT NULL,
	order_date DATETIME NOT NULL,
	total_price FLOAT NOT NULL,
	product_id INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

alter table users add index index_name_email (name,email);

INSERT INTO users (`name`, email, `password`)
VALUES 
('张三', 'zhangsan@example.com', 'password123'),
('李四', 'lisi@example.com', 'password123'),
('王五', 'wangwu@example.com', 'password123'),
('赵六', 'zhaoli@example.com', 'password123'),
('钱七', 'qianqi@example.com', 'password123');


INSERT INTO products (`name`, price)
VALUES 
('产品 1', 10.0),
('产品 2', 15.00),
('产品 3', 20.00),
('产品 4', 12.80),
('产品 5', 18.8);

INSERT INTO orders (user_id, order_date, total_price, product_id)
VALUES 
(1, '2023-02-18 10:00:00', 100.00, 1),
(2, '2023-02-18 11:00:00', 50.88, 2),
(3, '2023-02-18 12:00:00', 20.08, 3),
(4, '2023-02-18 13:00:00', 15.80, 4),
(5, '2023-02-18 14:00:00', 25.805, 5);

explain 结果集字段解释

explain 结果中各字段的含义：

  1. id：执行计划的自增 id
  2. select_type：查询语句执行的查询操作类型
  3. table：表名
  4. partitions：分区
  5. type：查询所用的访问类型
  6. possible_keys：可能用到的索引
  7. key：实际查询用到的索引
  8. key_len：实际使用索引的长度
  9. ref：使用到索引时，与索引进行等值匹配的列或常量
  10. rows：预计扫描的行数（索引行数或表记录数据行数）
  11. filtered：符合查询条件的数据百分比
  12. extra：SQL 执行的额外信息

id & table

table 列：表示的就是字面意思，当前查询的表。有两种特殊情况：

  1. 当 from 子句中有子查询时，那 table 就会以 <derivedN> 格式出现。此时查询所依赖的表是一个衍生出的表，即依赖执行计划中 id 为 N 的子查询结果集。
  2. 当使用 union 时，UNION RESULT 的 table 值为 <unionN1,N2>，N1、N2 表示为执行计划中 id 为 N1、N2 的执行计划。

这两种情况都会在后面出现。

id 列：每个查询都有一个自增 id。分为以下几种情况：

如果 id 相同，从上向下执行

EXPLAIN 
SELECT * FROM users u
JOIN orders o ON o.user_id = u.id
JOIN products p ON p.id = o.product_id

如果 id 相同，从上向下执行

查询顺序：u > o > p

如果 id 不同，id 大的先执行

EXPLAIN 
SELECT * FROM orders o WHERE product_id = (SELECT p.id FROM products p WHERE p.price = 10)

如果 id 不同，id 大的先执行

查询顺序：p > o

同时存在 id 不同，且某些 id 有多个执行计划的情况

-- 关闭 mysql5.7 对衍生表合并优化
SET SESSION optimizer_switch = 'derived_merge=off';

EXPLAIN 
SELECT o.* FROM (SELECT id FROM products) AS temp 
JOIN orders o ON o.product_id = temp.id 

-- 开启 mysql5.7 对衍生表合并优化
SET SESSION optimizer_switch = 'derived_merge=on';

同时存在 id 不同，且每个 id 有多个执行计划的情况

table：derived2 表示是衍生类型表，基于 id 为 2 的执行计划的结果集。

顺序：products > o > derived2

如果 id 为 NULL，表示为结果集，不代表这是一次查询。且最后执行。

EXPLAIN 
SELECT u.id FROM users u
UNION
SELECT p.id FROM products p

如果 id 为 NULL

table：union1,2 表示联合执行计划 id 为 1、2 次查询的结果集。

顺序：p > u，最后合并两次查询的结果集。

可以发现，每一个 select 都会对应计划 id。并且根据 SQL 的不同，比如进行 join 关联查询，会使得有多个 id 值相同的执行计划。所以，在不考虑特殊优化的情况下，我们可以将每一个 select 都认为会有一个计划 id。

执行计划的特殊情况

优化器会针对子查询进行一定的优化重写 SQL：

EXPLAIN 
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE id = 1);

SHOW WARNINGS;

SQL 嵌套了子查询，常规理解这个 SQL 应有两个 id 不同的执行计划，我们查看一下结果：

执行计划的特殊情况
执行计划 id 都为 1，这是为什么呢？可以查看查询结果2中的 Message，这个结果数据是 SHOW WARNINGS 这条 SQL 查询出来的：

/* select#1 */ 

select 
'1' AS `id`,
'张三' AS `name`,
'zhangsan@example.com' AS `email`,
'password123' AS `password` 
from `explain_test`.`orders` 
join `explain_test`.`users` where 1

优化器将我们的 in 子查询优化为了只有一个 select 的关联查询，所以执行计划中的 id 都为 1。

select_type

该列表示每个 select 的查询类型，主要用于区分普通查询、子查询、联合询等复杂查询。

嵌套类型

  • PRIMARY：嵌套查询最外层的部分被标记为PRIMARY

  • SUBQUERY：出现在 select 或 where 后面中的子查询被标记为 SUBQUERY

  • DERIVED：SUBQUERY的一种特例，出现的位置比较特殊，是在 from 后面的子查询。MySQL 会将子查询结果存放在一个临时表中，称为衍生表，而非原始表。

simple

简单 select 普通查询，不包括子查询与 union。

单表、关联查询都属于简单查询：

EXPLAIN 
SELECT * FROM orders o

EXPLAIN 
SELECT * FROM orders o
JOIN users u ON u.id = o.user_id

primary

复杂查询中的最外层查询。比如在子查询、联合查询中，id 为 1 的执行计划的 select_type 通常是 primary。

子查询：

EXPLAIN 
SELECT o.*, (SELECT u.id FROM users u WHERE u.id = 1)  FROM orders o

primary-子查询

联合查询：

EXPLAIN 
SELECT u.id FROM users u
UNION 
SELECT o.id FROM orders o

primary-联合查询

subquery & dependent subquery

前者子查询不依赖外部查询结果，后者反之依赖外部查询结果。

EXPLAIN 
SELECT o.*, (SELECT u.id FROM users u WHERE u.id = 1)  FROM orders o

subquery

EXPLAIN 
SELECT o.*, (SELECT u.id FROM users u WHERE u.id = o.user_id)  FROM orders o

dependent subquery

derived

衍生查询，表示 FROM 后的是一个子查询。

-- 关闭 mysql5.7 对衍生表合并优化
SET SESSION optimizer_switch = 'derived_merge=off';

EXPLAIN 
SELECT * FROM (SELECT id FROM products) AS temp;

-- 开启 mysql5.7 对衍生表合并优化
SET SESSION optimizer_switch = 'derived_merge=on';

derived

union & union result

union 的临时表去重

union 的查询类型分为 union 与 union all 这两种 SQL 查询方式。union 会在临时表中，针对相同结果集进行去重，union all 则不会进行去重。

EXPLAIN 
SELECT u.id FROM users u
UNION
SELECT o.id FROM orders o
UNION
SELECT p.id FROM products p

EXPLAIN 
SELECT u.id FROM users u
UNION ALL
SELECT o.id FROM orders o
UNION ALL
SELECT p.id FROM products p

执行结果（将3、2、1的结果在临时表中进行数据的去重）：

union

union all

普通 union 查询与 union 查询作为 FROM 数据源语句时的区别

简单的 union 查询的第一个 select 的查询类型为 primary，后面 select 的查询类型则为 union：

EXPLAIN 
SELECT u.id FROM users u
UNION
SELECT o.id FROM orders o
UNION
SELECT p.id FROM products p

union-1

union 查询若是 FROM 后的子查询语句，则第一个 select 的查询类型为 dereived，后面的 select 的查询类型依旧为 union：

EXPLAIN 
SELECT temp.* FROM (
	SELECT u.id FROM users u
	UNION
	SELECT o.id FROM orders o
	UNION
	SELECT p.id FROM products p
) AS temp

union-2

denpendent union

union 查询若是作为非 FROM 后的子查询时，union 语句中第一个 select 的查询类型为：dependent subquery，后面的 select 查询类型为 dependent union。

EXPLAIN 
SELECT o.* FROM orders o WHERE o.user_id IN (
	SELECT u.id FROM users u
	UNION
	SELECT p.id FROM products p
)

denpendent union

type

查询所使用的访问类型，不同类型的效率是不相同的。这个是调优慢 SQL 的关键字段。

效率从高到低：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL，一般来说要保证在 range，最好能达到 ref 级别。

最低效的为 ALL，即全表扫描。若发现执行类型为 index、ALL 时，就需要考虑能发优化到 range，在看看是否能优化到 ref 或 eq_ref。但为 ref 还是 eq_ref 就需要看业务场景了，因为一个是允许多值，一个是唯一值。

system

const 类型的一种特殊场景，查询的表只有一行记录，并且该表使用的存储引擎的统计数据是精确的。

InnoDB 存储引擎的统计数据是不精确的，即使表中只有一条数据，但 type 依旧为全表扫描 ALL。

DROP TABLE IF EXISTS t;
CREATE TABLE t(i INT) ENGINE=INNODB;
INSERT INTO t VALUES(1);
EXPLAIN SELECT * FROM t;

type-InnoDB-ALL

Memory 存储的统计数据是精确的：

DROP TABLE IF EXISTS tt;
CREATE TABLE tt(i INT) ENGINE=MEMORY;
INSERT INTO tt VALUES(1);
EXPLAIN SELECT * FROM tt;

type-memory-system

const

基于唯一性质的索引字段（主键或者唯一索引）进行常量匹配

EXPLAIN 
SELECT * FROM orders o WHERE o.id = 1

type-const

eq_ref

基于唯一性质的索引字段（主键或者唯一索引）进行关联查询

ALTER TABLE orders ADD UNIQUE uniq_user_id (user_id)
EXPLAIN 
SELECT * FROM users u
JOIN orders o ON o.user_id = u.id

type-eq_ref

注意查询 users 表的执行计划数据：type 为 ALL，possible_keys 为 PRIMARY，但实际使用索引 key 为 NULL，这是因为优化器进行成本分析后认为全表扫描的效率要高于索引查询。

ref

基于非唯一性质的索引字段进行常量匹配（可能会存在多条匹配记录），或通过该字段进行关联查询

-- 修改 order 表的 user_id 唯一索引为普通索引
ALTER TABLE orders ADD INDEX idx_user_id (user_id);
DROP INDEX uniq_user_id ON orders; 
-- 执行查询计划
EXPLAIN 
SELECT * FROM users u
JOIN orders o ON o.user_id = u.id

type-ref-关联查询

EXPLAIN
SELECT * FROM orders WHERE user_id = 1

type-ref-常量匹配

range

使用非唯一性质的索引进行范围搜索

EXPLAIN
SELECT * FROM orders WHERE user_id > 1

type-range

index（需优化，缩小查询范围）

Full Index Scan，index 与 ALL 都是全表扫描，区别在于 index 只遍历字段索引树文件，ALL 是遍历独立表空间文件。也可以说，index 只遍历非聚簇索引文件，ALL 遍历聚簇索引文件。因为索引文件通常比存储实际表数据文件小，所以 index 要比 ALL 更快。

EXPLAIN
SELECT user_id FROM orders

type-index

因为我们只查询了索引列的值，没有非索引列的值，所以只需要迭代索引树文件即可，即最终的 type 为 index。

ALL（需优化，为字段建立索引）

全表扫描，遍历聚簇索引文件。

EXPLAIN
SELECT * FROM orders

type-all

特殊类型：NULL

Mysql 在优化过程中就已经可以得到查询结果，执行时甚至不需要访问表或索引。使得 type 为 NULL，比如聚合函数：

EXPLAIN
SELECT MIN(id) FROM orders

type-null

possible_keys

查询中可能使用到的单或多个索引。如果没有可使用索引，则显示 NULL。

也可能出现前面出现的情况：possible_keys 有列，而 key 显示 NULL 的情况。这种情况是因为表中数据不多，mysql 认为索引对此查询帮助不大，选择了全表查询。

但如果该列是NULL，则没有可使用索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能。

key

实际查询中使用到的索引。如果实际没有使用索引，则显示 NULL。如果想强制使用或忽视 possible_keys列中的索引，在查询中使用 force index、ignore index。

key_len

表示索引中使用的字节数，查询中使用的索引的长度（最大可能长度），并非实际使用长度，理论上长度越短越好。key_len是根据表定义计算而得的，不是通过表内检索出的

举例说明：film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成，并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找。

extra

SQL 执行的一些额外信息，有很多的额外执行信息，但我们只以一些重要常用的执行信息进行讲解。

using index

表示执行的 select 操作中使用了覆盖索引（Covering Index），即访问非聚簇索引文件即可得到查询结果，无需在回表访问聚簇索引文件。

例：

EXPLAIN
SELECT o.user_id FROM orders o

extra-using-index

using where

表示 SQL 执行时使用了 WHERE 条件过滤数据。可以分为几种情况：

  1. 使用非索引列进行数据过滤：
EXPLAIN 
SELECT o.id FROM orders o WHERE o.product_id > 4

using-where-1

  1. 使用主键索引列进行数据过滤：
EXPLAIN 
SELECT * FROM orders o WHERE o.id > 4

using-where-2
若通过主键索引列进行过滤，结果比较特殊，仍是 using where。

using index condition

使用普通索引列进行数据过滤（不涉及覆盖索引，最后需要回表扫描。其实都在描述普通索引）

EXPLAIN 
SELECT * FROM orders o WHERE o.user_id > 4

using-where-3

以上情况的排列组合

  1. 使用普通索引列与非索引列进行数据过滤：
EXPLAIN 
SELECT * FROM orders o WHERE o.user_id > 4 AND o.total_price > 100

普通索引列与非索引列进行数据过滤

  1. 使用多个索引列进行数据过滤：
-- 新增 product 索引
ALTER TABLE orders ADD INDEX idx_product_id (product_id);

EXPLAIN 
SELECT o.user_id FROM orders o WHERE o.user_id > 4 AND o.product_id > 4

使用多个普通索引列进行数据过滤
最终使用了 idx_user_id 索引，回表查询聚簇索引文件，在进行 product_id 条件过滤，所以为 using index condition; using where。如果

在看另一种情况，普通索引列与主键索引列进行过滤：

EXPLAIN 
SELECT o.user_id FROM orders o WHERE o.user_id > 4 AND o.id > 4

普通索引列与主键索引列进行过滤
最终的 extr 的结果为 using where。也就是说并没有使用覆盖索引进行过滤，注意执行计划中的 key 字段：primary，原来是因为优化器最终选择了主键索引，而不是覆盖索引。可以总结下来：extra 中的具体信息取决于实际执行时使用的索引类型

  1. 使用覆盖索引列进行数据过滤：
EXPLAIN 
SELECT o.id FROM orders o WHERE o.id > 4

覆盖索引列进行数据过滤

using temporary

表示需要使用临时表来处理查询。

在前面测试 union 联合查询的时候，我们就发现了，最后会多个查询结果会在一个临时表中进行数据的去重。

除此之外，还有下面这种情况：

EXPLAIN 
SELECT DISTINCT u.password FROM users u

using temporary-1
password 不是索引列，若进行去重，就需要和 union 一样在一个临时表空间中对数据进行去重。

那么对一个索引列进行去重还会创建临时表吗？

EXPLAIN 
SELECT DISTINCT u.name FROM users u

using temporary-1
先分析一下执行情况：使用的 key 为联合索引，又只查询索引列，符合了覆盖索引的特征。所以，此时就不需要在构建一个临时表来进行去重处理，只需要迭代整个非聚簇索引文件即可（底层的叶子结点的数据都是有序的，顺序迭代即可，若发现当前元素与前元素相同，则不收集即可）。

所以可以总结出一个优化点，对 DISTINCE 的列，若是一个覆盖索引列，则可建立索引优化效率。

再看一个反例：

EXPLAIN 
SELECT DISTINCT u.name, u.password FROM users u

using temporary-2
对索引列进行了去重，但又同时查询了一个非索引列，使得必须查询聚簇索引文件才能得到 password 列的数据，所以 type 为 ALL。

那为什么为 ALL 呢？为什么不能先从 name 索引文件中搜索去重后的数据，在回表查询这些数据在聚簇索引文件中的 password 列的值呢？我们假设最坏的情况：没有重复的 name 数据。那么就会先搜索整个非聚簇索引文件，在搜索整个聚簇索引文件，还不如直接搜索整个聚簇索引文件。那么最终就仍需要使用临时表空间进行去重了。

using filesort

当对非索引列进行 order by 排序时，数据较少时会在内存中进行排序。如果数据较多，则需要在磁盘中排序。

根据非索引列进行排序：

EXPLAIN 
SELECT u.name FROM users u ORDER BY u.password

using filesort-1
根据索引列进行排序（覆盖索引）：

EXPLAIN 
SELECT u.name FROM users u ORDER BY u.name

using filesort-2
根据索引列进行排序：

EXPLAIN
SELECT u.* FROM users u ORDER BY u.name

using filesort-2
注意这个 case，最终依旧是在存储空间进行的排序。为什么呢？还是要根据索引的使用情况进行分析。因为我们查询的是 u.*，很多列的数据是在 name 索引列的非聚簇索引文件中没有的，所以 type 为 ALL（为什么不走 name 索引前面也解释过类似的情况了）。所以就无法走 name 的索引文件，最后只能在内存中对聚簇索引文件中所有的叶子节点结果集进行排序了。

Select tables optimized away

当通过一些聚合函数，如：min / max，统计查询索引列的值。

这个情况就是前面在讲解 type 为 NULL 时的情况，Mysql 在优化过程中就已经可以得到查询结果，执行时甚至不需要访问表或索引。

EXPLAIN 
SELECT MIN(u.id) FROM users u

Select tables optimized away

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值