[大数据]MySQL 调试篇

1、MySQL 调试篇

本篇主讲如何使用 explain 和 explain 各个参数的意义、如何查看被优化器优化后的 SQL 和手动影响优化器的优化。

1.1、explain

当 explain 与 SELECT, DELETE, INSERT, REPLACE, UPDATE 这些语句一起使用时会显示相应的执行计划信息。当和 connection_id 一块使用时，显示的是相应命名语句的执行计划。

在讲解 explain 的字段之前，我们先来说下 explain。explain 和相关的 SQL 一块执行会返回相应的执行计划，而每行的记录表示了在执行 SQL 时会使用的表，而记录的显示顺序，表明优化器先取哪张表来进行数据关联。有时候会看到和自己定义关联表的顺序不一致，那就是被优化器重新定义了关联顺序了。

而执行器是如何关联这些表的数据的呢？它会先取出第一个表的第一行记录，然后去匹配下一个表的所有行，接着再匹配下一个表，知直到所有的表匹配完毕后，取出各个表需要查询的字段。然后回溯到上一个表，拿出下一条记录匹配关联，然后依次迭代，就像千层饼嵌套循环一样。当然这个是最简单的嵌套循环关联算法，还有其他算法就不在这细说了，下次再说。下面我们拿一个简单的例子来说下。

数据准备

CREATE TABLE `user_account` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) DEFAULT NULL COMMENT '用户名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


CREATE TABLE `account_info` (
  `id` int NOT NULL AUTO_INCREMENT,
  `head_img` varchar(255) DEFAULT NULL COMMENT '头像',
  `account_id` int DEFAULT NULL COMMENT '用户ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


INSERT INTO `user_account`(`id`, `user_name`) VALUES (1, '张三');
INSERT INTO `user_account`(`id`, `user_name`) VALUES (2, '李四');
INSERT INTO `user_account`(`id`, `user_name`) VALUES (3, '王五');

INSERT INTO `account_info`(`id`, `head_img`, `account_id`) VALUES (1, '1', 1);
INSERT INTO `account_info`(`id`, `head_img`, `account_id`) VALUES (2, '2', 2);
INSERT INTO `account_info`(`id`, `head_img`, `account_id`) VALUES (3, '3', 3);
INSERT INTO `account_info`(`id`, `head_img`, `account_id`) VALUES (4, '44', 1);

这时候我们来个最简单的关联查询语句：

select m.id,m.user_name,n.head_img
from user_account m
left join account_info n
on m.id = n.account_id

按照我们上面的思路，上面语句的执行过程回事这样的：
user_account 作为驱动表先取出符合条件的数据，这里没有 where 就取第一行 “张三” 数据了，第一张表拿到第一行数据然后去被驱动表匹配数据，匹配到了 account_info 表中 id 1 和 4 的数据，然后拿到符合数据的 1 去下一个表进行匹配，但是没有下一个表了，所以直接取相应字段输入，这时候输出了与 1 和 4 的匹配数据，回溯到上一个表也就是 user_account 表取下一条数据，以此类推。最后结果是这样的。

id  user_name head_img
1	张三	44
1	张三	1
2	李四	2
3	王五	3

explain 参数

• id（JSON-[select_id]）

select 标识符，在当前查询的序号。如果该行指的是 union 结果则可以是 null。SQL 中有几个 select 就会有几个不同的ID（关联查询 ID 是一样的），具体还得看优化器优化后的 SQL 去判断。

• select_type

查询的类型。

• • SIMPLE

官网给的定义是 “没有使用 union 或者子查询” 的简单查询。这个就不解释了。

• • PRIMARY

最外层的查询。如果是使用子查询没有被优化器优化成多表连接的话最外层的查询就是 PRIMARY。如果是使用 union 或者 union all，最左边的就是 PRIMARY。

• • UNION

UNION 中第二或者之后的查询。使用 union 后，第一个是 PRIMARY，第二个之后就是 union 了（除了最后一个）。

• • DEPENDENT UNION

UNION 中第二或者之后的查询。依赖于外部查询。

• • UNION RESULT

union 后的结果。使用 union 后，一般最后就是 union 去重后的结果了，也就是 UNION RESULT。

• • SUBQUERY

子查询中第一个查询。未被优化器优化成多表连接的方式，第一个是 PRIMARY，第二个就是 SUBQUERY 了。

• • DEPENDENT SUBQUERY

子查询中第一个查询。依赖于外部查询。就是子查询中需要查询的条件依赖了外部表的数据，这时候子查询就是 DEPENDENT SUBQUERY 了。

• • DERIVED

派生表。将查询结果作为一张表来查询的表叫派生表。

• • DEPENDENT DERIVED

派生表依赖于其他表。

• • MATERIALIZED

物化子查询

• • UNCACHEABLE SUBQUERY

不可缓存的子查询

• • UNCACHEABLE UNION

在 union 中第二或者之后的查询，它属于不可缓存子查询。

• table（JSON-[table_name]）

输出行所引用的表。

• partitions

匹配记录的分区。非分区表该值为 null。

• type （JSON-[access_type]）

连接的类型。

• • system

当前表仅有 1 条记录（=系统表），是 const 类型的一种特例。

• • const

当前查询最多只有一个匹配行，并且使用常量匹配主键或者唯一索引时，类型是 const，const 读取速度非常快，因为他们只被读取 1 次。

select *
from user_account
where id  = 1

• • eq_ref

当使用关联查询时被驱动表的关联条件是通过主键或者唯一索引进行关联的时候，这时候就是 eq_ref。当主键索引是组合索引时，索引列必须全部进行匹配。

select *
from user_account m
left join account_info n
on m.id = n.id


# 这里 user_account 增加一个 test 主键索引。
select *
from account_info m
left join user_account n
on m.id = n.id and n.test = m.id

• • ref

当使用普通索引进行等值匹配或者 “<=>” 匹配时，类型就是 ref。

select *
from user_account 
where test  = 1

• • fulltext

使用全文索引关联。

• • ref_or_null

跟 ref 很像，它还搜索额外的 NULL 值。

select *
from user_account 
where test  <=> 1 or test is null

• • index_merge

在查询中使用了索引合并优化。也就是索引中包含了多个索引，这些索引被优化成 1 个大的索引。

• • unique_subquery

在 eq_ref 的基础上，替代其 in 的一些子查询：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

• • index_subquery

类似于 unique_subquery，只不过上面使用的是主键索引或者唯一索引，而它匹配的是普通索引。

value IN (SELECT key_column FROM single_table WHERE some_expr)

• • range

匹配了范围性的数据索引。range 在以下运算符中生效 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, IN()

select *
from user_account m
where id in (1,2)

• • index

跟 All 类似，只不过它只扫描索引树。

这里有两种情况：

第一个种是只适用覆盖索引（只查索引）就能满足查询条件。在 Extra 中有 Using index. 标识。这种方式通常比 all 扫描快，因为索引的数据量总比全表数据量小。

第二种全表扫描是通过索引的顺序来查找的。这种 Extra 中不会有 Using index.

查询组合索引时使用单个索引会使用当前类型。

• • ALL

全表扫描。

以上内容参考官网地址： https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-join-types

性能等级从高到低。至于如何优化 type 等级，在索引篇讲了。

• possible_keys(JSON-[possible_keys])

查询可能会使用到的索引。

• key(JSON-[key])

查询中实际使用的索引。有时候可能出现不在 possible_keys 的值当中，如果命中的是覆盖索引的话就会出现这种情况。如果想强制使用索引或者忽略索引，请使用 FORCE INDEX、USE INDEX或IGNORE INDEX。

• key_len(JSON-[key_length])

实际上使用到的索引长度（字节数）。对联合索引有参考意义。

• ref(JSON-[ref])

当使用索引时，等值比较的比较对象。如果值为func，则使用的值是某个函数的结果。

• rows(JSON-[rows])

这个表示优化器认为当前查询必须要扫描的行数才能拿到结果。在 InnoDB 中，当前结果是估算值，不一定准确，因为 MVCC 导致行不确定。

• filtered(JSON-[filtered])

表示按照条件筛选后剩余的记录数的百分比。rows × filtered 表示与下一张表需要连接的行数。

• Extra

此列包含有关 MySQL 如何解析查询的附加信息

• • Backward index scan

可以对 InnoDB 表使用降序索引，和 Using index. 一块显示。

• • Child of ‘table’ pushed join@1
• • const row not found

对于 SELECT … FROM tbl_name 这样的查询，被查询的表是空的。

• • Deleting all rows

对于一些支持简单快速删除所有表数据的存储引擎，如果被优化器使用则会显示当前选项。

• • Distinct

当查询做去重操作时，当查询到第一个匹配记录时会停止往下继续搜索相同的值。

• • FirstMatch(tbl_name)

tbl_name 使用半连接 FirstMatch 策略。

• • Full scan on NULL key

当优化器无法使用索引查找时会使用子查询优化作为回退策略。

• • Impossible HAVING

HAVING 条件总是 false，不能匹配到任何数据。

• • Impossible WHERE

where 条件总是 false。

• • Impossible WHERE noticed after reading const tables

MySQL 读取所有的 const（和 system ）表后认为 where 匹配条件总是 false。

• • LooseScan(m…n)

半连接策略被使用。

• • No matching min/max row

对于 SELECT MIN(…) FROM … WHERE condition. 没有满足条件的数据。

• • no matching row in const table

对于一个关联查询，有个空的表或者没有满足唯一索引数据的表。

• • No matching rows after partition pruning

当 partition pruning 后优化器发现没有任何需要去更新或者删除的东西。和 Impossible WHERE for SELECT 含义类似。

• • No tables used

没有 from 或者查的表是 DUAL。

• • Not exists

当被驱动字段被定义为 not null，但是查询条件中又有被驱动字段匹配 null 的数据，这时候就会提示 Not exists。

• • Plan isn’t ready yet

如果优化器还没有完成当前查询的执行计划时会出现当前值。

• • Range checked for each record (index map: N)
• • Recursive
• • Rematerialize
• • Scanned N databases

这说明了在处理 INFORMATION_SCHEMA 表时扫描了多少个服务器的目录。 N 的值可以是 0、1 或 all

• • Select tables optimized away

优化器确定了最多返回 1 行。例如像 min(c1) 这种只需要查索引树最左端的记录就能拿到结果。这时候优化器就会把它当作一个常量。

• • Skip_open_table, Open_frm_only, Open_full_table
• • Start temporary, End temporary

临时表用了 semijoin Duplicate Weedout 策略。

• • unique row not found

对于 SELECT … FROM tbl_name 没有一条记录满足主键索引或者唯一索引。

• • Using filesort

排序没有使用到索引，只能在内存或者磁盘中进行排序。

• • Using index

仅使用索引树中的信息从表中检索列信息，而无需执行额外的查找来读取实际行。

• • Using index condition

where 中存在索引列，但未使用到索引列。通过访问索引组来判断是否需要回表查询所有行。这个叫做索引条件下推。

• • Using index for group-by

与 Using index 类似，Using index for group-by 说明了 MySQL 找到了一个索引可以查找 group by 或者 distinct 的列而不用访问多余的磁盘操作。

• • Using index for skip scan

说明了使用 skip scan 。

• • Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access), Using join buffer (hash join)

被驱动表不能很好的利用索引加快访问速度，MySQL 会为被驱动表分配一块 “join buffer” 加快访问速度。

• • Using MRR
• • Using sort_union(…), Using union(…), Using intersect(…)

使用了索引合并。

• • Using temporary

为了执行查询，MySQL 使用了临时表保存结果。一般查询中包括 GROUP BY 和 ORDER BY 会发生这种情况。

• • Using where

有 where 查询条件，但是没有使用索引进行而全表扫描。

where 中使用了非索引列的匹配条件。

• • Using where with pushed condition
• • Zero limit

查询有个 LIMIT 0

内容参考官网地址：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-extra-information。优化在索引篇讲。

2、SHOW WARNINGS

很多时候执行器执行的 SQL 不一定是我们自己定义的 SQL，在优化器内容会进行相应的 SQL 优化，这时候我们怎么看优化后的 SQL 呢？

我们打开 MySQL 的命令行，然后输入我们要查询的 SQL：

explain select * from user_account m left join account_info n on m.id = n.account_id;

然后再使用 SHOW WARNINGS

/* select#1 */ 
SELECT
`data_generate`.`m`.`id` AS `id`,
`data_generate`.`m`.`test` AS `test`,
`data_generate`.`m`.`user_name` AS `user_name`,
`data_generate`.`n`.`id` AS `id`,
`data_generate`.`n`.`head_img` AS `head_img`,
`data_generate`.`n`.`account_id` AS `account_id` 
FROM
	`data_generate`.`user_account` `m`
	LEFT JOIN `data_generate`.`account_info` `n` ON ( ( `data_generate`.`n`.`account_id` = `data_generate`.`m`.`id` ) ) 
WHERE
	TRUE 

这个就是优化后的 SQL。

有时候 message 里面不一定只有执行的 SQL ，还有一些其他的特殊标记字符：

<auto_key>

为临时表自动生成的键。

(expr)

表达式执行一次之后将结果缓存到后面继续使用。

(query fragment)

将子查询转换成 EXISTS。

<in_optimizer>(query fragment)

没有意义的优化对象。

<index_lookup>(query fragment)

使用索引查找匹配数据。

(condition, expr1, expr2)

如果 condition 为真，执行 expr1，否则执行 expr2。

<is_not_null_test>(expr)

验证表达式不为 null。

(query fragment)

使用物化子查询。

materialized-subquery.col_name

<primary_index_lookup>(query fragment)

使用主键查询数据。

<ref_null_helper>(expr)

/ select#N / select_stmt

outer_tables semi join (inner_tables)

半连接操作。

临时表。

3、使用优化器 Hints 影响优化器

死锁检测和事务锁相互持有检测在事务篇讲。