摘自：https://hackmysql.com/book-2/
EXPLAIN ANALYZE是什么
EXPLAIN ANALYZE是MySQL8.0.18版本推出的一个用于查询的分析工具，它将显示MySQL在查询上花费的时间以及原因。它将生成查询计划、检测查询并执行查询操作，同时会计算行数并测量执行计划中各个点所花费的时间。执行完成后，EXPLAIN ANALYZE将打印计划和测量值，而不是查询结果。
这个新功能建立在常规的EXPLAIN查询计划检查工具之上，可以看作是MySQL 8.0中早期添加的EXPLAINFORMAT=TREE的一个扩展功能。除了正常的EXPLAIN会打印的查询计划和估计成本外，EXPLAIN ANALYZE还会打印执行计划中单个迭代器的实际成本。
MySQL Query Executor
8.0的Query Executor使用模块化的迭代器进行了升级
template <class RealIterator>
bool TimingIterator<RealIterator>::Init() {
++m_num_init_calls;
steady_clock::time_point start = now(); /* start time */
bool err = m_iterator.Init(); /* real iterator */
steady_clock::time_point end = now(); /* end time */
m_time_spent_in_first_row += end - start;
m_first_row = true;
return err;
}
迭代器接口有两个方法：Init()和Read()
一个loop表示一次迭代器的调用过程：调用Init()，然后调用Read()，直到没有更多的行
EXPLAIN ANALYZE会打印每个迭代器的测量值，如下图所示：
以(actual time=0.106..9.991 rows=2844 loops=2)为例：解释如下
- 0.106
Init time: 调用Init()和读取第一行（第一次执行Read()）花费的平均毫秒时间 - 9.991
Read time: 调用Init()和读取所有行(执行所有Read())的平均花费时间(毫秒) - rows=2844
读取行的总记录数，所有loop - loops=2
Init()调用的次数，或者迭代器执行的次数
第一个时间值，叫做init time，视为启动开销，通常来说是非常慢的，但是依赖于具体的迭代器
第二个时间值，叫做read time，可以通过loops * read time = iterator time这个公式来计算iterator time，即迭代器花在读取行的总时间 = 9.991 ms × 2 = 19.982，很显然，loops=1时，read time和iterator time相同
init time、read time和iterator time是我的术语，不是MySQL的官方术语，因为MySQL没有为这些值指定简洁的名称。
一般来说，iterator time是从叶子到根的累积时间
-> A (200ms loops=1)
-> B (185ms loops=1)
-> C (90ms loops=2)
叶子节点 iterator C 花费了 180 ms (90 × 2 loops). 由于iterator B 调用了 iterator C, iterator B 调用的时间是 5 ms ( 减去 iterator C的时间：185 ms − 180 ms). 同样地, iterator A 调用 iterator B, 因此 A 的时间是 15 ms (200 ms − 185 ms)。查询花费200 ms（180ms + 5ms + 15ms），而不是这些时间之和（575 ms = 200 ms + 185 ms + 90 ms）
案例分析
案例1 文件排序
使用到的表如下：
CREATE TABLE `sbtest1` (
`id` int NOT NULL AUTO_INCREMENT,
`k` int NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB;
通过下面的存储过程填充1000000条数据
DELIMITER $$
CREATE PROCEDURE InsertDataToSbtest1()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE rand_k INT;
DECLARE rand_c CHAR(120);
DECLARE rand_pad CHAR(60);
WHILE i <= 1000000 DO
-- 生成随机的整数作为 'k' 列的值
SET rand_k = FLOOR(RAND() * 10000);
-- 生成随机的字符串作为 'c' 和 'pad' 列的值
SET rand_c = LPAD(FLOOR(RAND() * 1000000), 120, 'a'); -- 用 'a' 填充字符
SET rand_pad = LPAD(FLOOR(RAND() * 100000), 60, 'b'); -- 用 'b' 填充字符
-- 插入一条记录
INSERT INTO sbtest1 (k, c, pad) VALUES (rand_k, rand_c, rand_pad);
-- 递增计数器
SET i = i + 1;
END WHILE;
END$$
DELIMITER ;
经测试，在i712700 32G DDR5机器上跑了 1916s，使用的是MySQL 8.0.35 Windows版本
同时本地文件占了大概244M
默认情况没有走k_1索引，而是走的主键索引，查询449985条数据花了1.745s
mysql> EXPLAIN SELECT c FROM sbtest1 WHERE k < 4500 ORDER BY id;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | sbtest1 | NULL | index | k_1 | PRIMARY | 4 | NULL | 987633 | 50.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
如果让它强制走k_1索引，查询449985条数据花了15.932s，查询计划中出现了filesort
mysql> EXPLAIN SELECT c FROM sbtest1 FORCE INDEX(k_1) WHERE k < 4500 ORDER BY id;
+----+-------------+---------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------------------------------+
| 1 | SIMPLE | sbtest1 | NULL | range | k_1 | k_1 | 4 | NULL | 493816 | 100.00 | Using index condition; Using MRR; Using filesort |
+----+-------------+---------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
using MRR表示使用了Multi-Range Read Optimization这个优化策略
走了索引，并且type是range，并且用了索引下推优化策略，Using index condition，看起来就是
Using filesort导致了查询慢
mysql> EXPLAIN ANALYZE SELECT c FROM sbtest1 FORCE INDEX(k_1) WHERE k < 4500 ORDER BY id\G;
*************************** 1. row ***************************
EXPLAIN:
(1)-> Sort: sbtest1.id (cost=298031 rows=493816)
(2) (actual time=16366..16450 rows=449985 loops=1)
(3) -> Index range scan on sbtest1 using k_1 over (k < 4500), with index condition: (sbtest1.k < 4500)
(4) (cost=298031 rows=493816) (actual time=59.7..15499 rows=449985 loops=1)
1 row in set (16.51 sec)
首先，根据通常的理解，这个sql应该是先取出数据然后再进行排序，毕竟没有数据的话要怎么排序呢？所以这个结果应该是第3行在前，第1行在后，但是实际结果却是相反的
为了便于观察，我将输出进行了换行及标号（上面文本中每行开头的带数字的括号），现在解释一下这个输出内容里面各操作的执行时间：
第4行，15499(ms)表示第3行的Index range scan这个操作花了大约15.5s（15499 - 59.7 = 15439.3ms），第2行表示第1行的Sort操作从16366ms开始，到16450ms结束，花了84ms
总执行时间为16450ms，93.9%的时间花在读取数据上，0.5%的时间花在排序上，剩下5.6%的时间花在其他阶段，比如preparing，statistics，logging，cleaning up等等
答案很明显了文件排序不是使此查询变慢的根本原因。问题是数据访问，449985行不是一个小的结果集。对于每秒执行数十亿次操作的CPU来说，对449985个值进行排序几乎是零工作，但对于必须遍历索引、进行管理的关系数据库来说，读取449985行数据这是一项可观的工作量
为什么都觉得出现了Using Filesort会导致速度慢？
因为MySQL在排序数据超过sort_buffer_size这个变量的值（单位为字节）时，会使用磁盘上的临时文件。262144字节 = 256M，而我刚才测试的1000000条数据是244M，现在只是对449985条数据排序，所以肯定没超过sort_buffer_size值
mysql> SHOW VARIABLES LIKE 'sort_buffer_size';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+
1 row in set, 1 warning (0.00 sec)
而硬盘驱动器的速度比内存慢几个数量级。以前当旋转磁盘是标准时，所以很慢；但现在SSD固态硬盘存储通常非常快。在以高吞吐量QPS进行查询的场景，文件排序可能是一个问题，但还是需要使用EXPLAIN ANALYZE进行测量和验证。
案例2 简单的JOIN查询
CREATE TABLE IF NOT EXISTS `elem` (
id int unsigned not null primary key,
a char(2) not null,
b char(2) not null,
c char(2) not null,
INDEX `idx_a_b` (a, b)
) ENGINE=InnoDB;
INSERT INTO elem VALUES
('1', 'Ag', 'B', 'C' )
,('2', 'Au', 'Be', 'Co')
,('3', 'Al', 'Br', 'Cr')
,('4', 'Ar', 'Br', 'Cd')
,('5', 'Ar', 'Br', 'C' )
,('6', 'Ag', 'B', 'Co')
,('7', 'At', 'Bi', 'Ce')
,('8', 'Al', 'B', 'C' )
,('9', 'Al', 'B', 'Cd')
,('10', 'Ar', 'B', 'Cd');
CREATE TABLE IF NOT EXISTS `elem_names` (
`symbol` char(2) NOT NULL,
`name` varchar(16) DEFAULT NULL,
PRIMARY KEY (`symbol`)
) ENGINE=InnoDB;
INSERT INTO elem_names VALUES
('Ag', 'Silver' )
,('Al', 'Aluminum' )
,('Ar', 'Argon' )
,('At', 'Astatine' )
,('Au', 'Gold' )
,('B', 'Boron' )
,('Be', 'Beryllium')
,('Bi', 'Bismuth' )
,('Br', 'Bromine' )
,('C', 'Carbon' )
,('Cd', 'Cadmium' )
,('Ce', 'Cerium' )
,('Co', 'Cobalt' )
,('Cr', 'Chromium' );
EXPLAIN SELECT name FROM elem JOIN elem_names ON (elem.a = elem_names.symbol) WHERE a IN (‘Ag’, ‘Au’, ‘At’)\G;
mysql> EXPLAIN SELECT name FROM elem JOIN elem_names ON (elem.a = elem_names.symbol) WHERE a IN ('Ag', 'Au', 'At');
+----+-------------+------------+------------+--------+---------------+---------+---------+--------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+---------+---------+--------------------+------+----------+--------------------------+
| 1 | SIMPLE | elem | NULL | range | idx_a_b | idx_a_b | 8 | NULL | 4 | 100.00 | Using where; Using index |
| 1 | SIMPLE | elem_names | NULL | eq_ref | PRIMARY | PRIMARY | 8 | mysql_learn.elem.a | 1 | 100.00 | NULL |
+----+-------------+------------+------------+--------+---------------+---------+---------+--------------------+------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)
EXPLAIN ANALYZE结果如下
mysql> EXPLAIN ANALYZE SELECT name FROM elem JOIN elem_names ON (elem.a = elem_names.symbol) WHERE a IN ('Ag', 'Au', 'At')\G;
*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join (cost=2.46 rows=4) (actual time=0.0295..0.0471 rows=4 loops=1)
-> Filter: (elem.a in ('Ag','Au','At')) (cost=1.06 rows=4) (actual time=0.0194..0.0319 rows=4 loops=1)
-> Covering index range scan on elem using idx_a_b over (a = 'Ag') OR (a = 'At') OR (a = 'Au') (cost=1.06 rows=4) (actual time=0.017..0.0284 rows=4 loops=1)
-> Single-row index lookup on elem_names using PRIMARY (symbol=elem.a) (cost=0.275 rows=1) (actual time=0.0032..0.00323 rows=1 loops=4)
1 row in set (0.00 sec)
下面将其输出格式进行调整和标号
mysql> EXPLAIN ANALYZE SELECT name FROM elem JOIN elem_names ON (elem.a = elem_names.symbol) WHERE a IN ('Ag', 'Au', 'At')\G;
*************************** 1. row ***************************
(3)-> Nested loop inner join (cost=2.46 rows=4)
(actual time=0.0295..0.0471 rows=4 loops=1)
(1) -> Filter: (elem.a in ('Ag','Au','At')) (cost=1.06 rows=4)
(actual time=0.0194..0.0319 rows=4 loops=1)
(0) -> Covering index range scan on elem using idx_a_b over (a = 'Ag') OR (a = 'At') OR (a = 'Au') (cost=1.06 rows=4)
(actual time=0.017..0.0284 rows=4 loops=1)
(2) -> Single-row index lookup on elem_names using PRIMARY (symbol=elem.a) (cost=0.275 rows=1)
(actual time=0.0032..0.00323 rows=1 loops=4)
通常应该以深度优先规则阅读EXPLAIN ANALYZE的输出内容。
虽然查询执行的结果显示JOIN操作是根节点，但开始读取行是从表elem上的Covering index range scan开始的，即(0)位置。然后在（1）位置使用IN子句的条件对行进行筛选。匹配的行用于查找和连接elem_names表中的相应行，PRIMARY lookup（2）
注意，（0）位置处的Covering index range scan操作和（1）位置处的Filter操作的loops均为1，这是因为，join操作中的第一个表只会被访问一次。但是（2）位置的primary key lookup的loops=4，这是因为被连接的表（join操作中的第二个和后续表）通常会对前面表中的每一行进行多次访问。同样，(1)位置处的filter匹配结果rows=4，这对应于primary key lookup的loops=4：第一个表（elem表）中的4行导致MySQL访问连接的表（elem_names表）4次。
扫一扫
8028
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
