MySQL查询优化分析 - 常用分析方法
查询优化是数据库管理非常重要的一个方面，而使用合适的查询优化分析方法可以大幅提升效率。本文将要介绍查询优化中常用的分析方法，包括EXPLAIN命令、Optimizer Trace、Profiling、常用的监控指标。
MySQL查询优化分析系列文章，前文有：
《MySQL查询优化分析 - MySQL优化执行的基础概念》：介绍了MySQL优化器框架、代价模型、执行计划的基础概念，影响执行效率和计划选择的关键因素。
EXPLAIN查看解读执行计划
查询优化分析中，EXPLAIN是一个极为常用的命令。我们通过EXPLAIN来查看查询的执行计划，包括选择了哪个索引路径、访问方式、JOIN ORDER、估算的扫描行数、选择率等信息。
语法
EXPLAIN主要语法如下：
EXPLAIN [ANALYZE] [FORMAT= [TRADITIONAL | JSON | TREE]] explainable_stmt;
EXPLAIN FOR CONNECTION connection_id。
EXPLAIN展示信息的解读
EXPLAIN explainable_stmt与EXPLAIN FORMAT=TRADITIONAL explainable_stmt等价。 其展示的各个列的信息如下：
列名 说明 id 显示Query Block的序列号。 select_type 显示本QueryBlock的类型 table 表名（别名)。 partitions 分区表需要访问哪些partition type 访问方式 possible_keys 表中可能使用的索引 key 实际选择的索引 key_len 使用到的索引字节长度。使用到的字段多，使用字节程度长。 ref 索引等值引用到的对象。const或者前缀表上的列。 rows 估算的扫描行数 filtered 选择率 Extra 其他的额外信息id列
表示查询中第几个SELECT(query block)的标识符。如果是UNION语句可能为NULL，TABLE列展示<unionM,N>表示UNION id为M和N的query block的UNION。
select_type列
Value 描述 SIMPLE 简单查询，没有子查询和UNION PRIMARY 包含UNION，最外层的为PRIMARY SUBQUERY 子查询 DEPENDENT SUBQUERY 关联子查询 DERIVED 物化表 DEPENDENT DERIVED 关联其他query block的物化表 MATERIALIZED 物化的子查询 UNION UNION语句中第二个和后续的query block。 DEPENDENT UNION UNION语句中第二个和后续query block，依赖前面的查询 UNION RESULT UNION的result下面语句为有MATERIALIZED子查询语句EXPLAIN结果。
mysql> explain select * from t1 where c1 in (select c1 from t2);
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+------------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | Using where |
| 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 5 | test.t1.c1 | 1 | 100.00 | NULL |
| 2 | MATERIALIZED | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | NULL |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+------------+------+----------+-------------+
下面语句为UNION查询的EXPLAIN结果。
mysql> explain select c1 from t1 union select c1 from t2;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | NULL |
| 2 | UNION | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | NULL |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
table列
VALUE 描述 表名 表的名称（有别名为别名） 物化表，N为内部标号 物化的subquery <unionM,N> UNION query block:M和N 的resultpartitions列
分区表需要访问哪些分区。
type列
表的访问方式
Value 描述 ALL 对表进行全表扫描（读取所有行）。 index 完整索引扫描。 system 该表只有1行。 const 表中最多只有一行匹配的数据。在优化阶段先读取该行，并将表中的所有列均视为常量。 eq_ref 对表上pk/uk唯一索引的等值关联访问。 ref 非唯一索引或唯一索引的前缀的常量等值或者等值关联访问。 ref_or_null 类似于“ ref”，同时会找为NULL的值。c1 = 1 or c1 is null range 索引范围扫描 index_merge 多个索引组合使用。结果做union/intersect fulltext 全文索引用于访问行。 unique_subquery 这与eq_ref相似，但用于转换为键查找的子查询 index_subquery 这与ref类似，但用于转换为键查找的子查询。primary key const访问方式
mysql> explain select * from tpk where c1 = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------------------------+
| 1 | SIMPLE | tpk | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Directly search via Primary Index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------------------------+
全表访问和REF访问
mysql> explain select t1.* from t1 left join t2 on t1.c1 = t2.c1;
+----+-------------+-------+------------+------+---------------+------+---------+------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | NULL |
| 1 | SIMPLE | t2 | NULL | ref | i_c1 | i_c1 | 5 | test.t1.c1 | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+------------+------+----------+-------------+
mysql> explain select * from t1 where c1 = 1;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ref | i_c1 | i_c1 | 5 | const | 6 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
range访问
mysql> explain select * from t1 where c1 > 1 and c1 < 10;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | t1 | NULL | range | i_c1 | i_c1 | 5 | NULL | 8 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
possible_keys列
可能用到的索引。这些索引是存在索引列体现在查询范围、group by\order by、或者覆盖所有字段的索引。最优的索引可能就在这些索引之中。
key列
优化器最终选择的索引。如果为NULL表明没有使用索引，走的全表扫描。
key_len列
该索引在查询范围分析中使用到的字节数。使用到的字节数大说明使用的索引列多。
ref列
索引列等值访问引用到的值，可能是const/column。
mysql> explain select t1.* from t1 left join t2 on t1.c1 = t2.c1 and t2.c1 = 1;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | NULL |
| 1 | SIMPLE | t2 | NULL | ref | i_c1 | i_c1 | 5 | const | 1 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+
rows列
估算的扫描行数。 这里在JOIN中是一次访问的扫描行数。做被驱动表的时候，表整体扫描行数要用prefix_rows去乘。
filtered列
选择率，condition的过滤性，单位是%。输出行数要乘以该值。选择率越小，说明过滤后行数越少。 在优化器中filter估算的偏差会导致输出行数的偏差影响后面JOIN表的路径选择，也会影响代价的估算。
extra
这里会展示MySQL执行计划的一些额外信息。其中一些可能的内容如下：
const row not found 表为空 no matching row in const table const访问方式的表未找到满足条件的行 Distinct DISTINCT操作 Impossible WHERE WHERE始终为false，因此SELECT不会返回任何行。例如：WHERE 1=2 Impossible HAVING HAVING子句始终为false，因此SELECT不会返回任何行。 No matching min/max row 在MIN()/MAX()值的早期优化期间，检测到没有行可以匹配该WHERE子句。 Using index 仅索引访问可以获取需要的列，不需要回表获取其他列。 No tables used 这是一个没有使用任何表格的子查询。例如，没有FROM子句或FROM DUAL子句。 FirstMatch semi-join使用FirstMatch策略 Not exists 如果找到一个匹配的行，则停止在更多行之后搜索。使用时LEFT JOIN，可以明确搜寻内不存在的列LEFT JOIN TABLE。范例：SELECT * FROM t1 LEFT JOIN t2 on (…) WHERE t2.not_null_column IS NULL。由于t2.not_null_column只能NULL如果没有匹配的行的条件，我们就可以停止搜索，如果我们找到一个匹配的行。 Using filesort 需要排序操作 Using where WHERE条件 Using index condition WHERE条件被下推到表引擎层以在索引回表前做过滤。 Range checked for each record(index map: …) 仅当没有很好的默认索引可使用时，才会发生这种情况，但是当我们可以将上一个表中的所有列视为常量时，可能会使用某些索引。 Using index for group-by 索引用于解析GROUP BY(min/max)或DISTINCT查询。可以快速检索数据。 Using intersect(…) 对于index_merge intersect。显示哪些索引被使用。 Using join buffer 缓存前面输出行，用于后续计算，一次数据访问同时计算多个缓存的数据。减少join表的访问次数。 Using sort_union(…) index merge做sort union。 Using temporary 将创建一个临时表来保存结果。下面是一些示例：
# Using index
mysql> explain select c1 from t1 where t1.c1 = 1;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ref | i_c1 | i_c1 | 5 | const | 6 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
# Using index condition
mysql> explain select * from t1 where t1.c1 > 1 and t1.c1 < 5;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | t1 | NULL | range | i_c1 | i_c1 | 5 | NULL | 3 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
# Using where
mysql> explain select * from t1 where t1.c1 > 1 and t1.c1 < 5 and t1.c2 = 1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------------+
| 1 | SIMPLE | t1 | NULL | range | i_c1 | i_c1 | 5 | NULL | 3 | 10.00 | Using index condition; Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------------+
# Using filesort
mysql> explain select * from t1 order by c1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
# Using index for group by
explain select a1, min(a2) from t1 group by a1;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 5 100.00 Using index for group-by
EXPLAIN FORMAT=JSON
通过EXPLAIN FORMAT=JSON我们可以看到一些通过EXPLAIN看不到的信息。在查询优化中我们可以看到： 访问索引路径的used_key_parts，使用到索引的哪些列做范围扫描的界定。 rows_produced_per_join:join产生多少行数据。 used_columns:表会访问哪些列。 同时我们可以看到每个query block的代价，执行到每个表的代价。
mysql> explain format=json select c1, c2 from t1 where c2 = 1\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1.05"
},
"table": {
"table_name": "t1",
"access_type": "ref",
"possible_keys": [
"i_c2"
],
"key": "i_c2",
"used_key_parts": [
"c2"
],
"key_length": "9",
"ref": [
"const"
],
"rows_examined_per_scan": 3,
"rows_produced_per_join": 3,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.75",
"eval_cost": "0.30",
"prefix_cost": "1.05",
"data_read_per_join": "120"
},
"used_columns": [
"c1",
"c2"
]
}
}
}
EXPLAIN FORMAT=TREE
MySQL8.0.16引入了FORMAT=TREE的计划展示格式，以树形输出执行计划，展示执行计划的每个operator，优化器预估的代价和预估行数。这里预估的代价是指该operator执行一次的代价，如果需要重复执行多次则需要考虑驱动的Operator输出行数。预估行数是指该operator执行一次输出多少行数据。例如filter展示的是过滤之后的行数，要处理的行数是下层operator的输出行数。 MySQL执行计划是火山模型，执行计划树自顶向下调用Read()接口，数据则自底向上被拉取处理。因此FORMAT=TREE展示的树形计划，数据获取顺序是最底层的先执行获取。对于并列的operator，上面的operator是树的左节点，优先执行。
mysql> explain format=tree select * from t2 join t3 on t2.c1 = t3.c1 join t1 on t1.c1 = t2.c1 where t2.c1 > 50;
+------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+------------------------------------------------------------------------------------------------------------------------------------------+
| -> Inner hash join (t3.c1 = t2.c1) (cost=1476.69 rows=1414)
-> Table scan on t3 (cost=0.03 rows=206)
-> Hash
-> Nested loop inner join (cost=62.05 rows=69)
-> Filter: ((t2.c1 > 50) and (t2.c1 is not null)) (cost=20.85 rows=69)
-> Table scan on t2 (cost=20.85 rows=206)
-> Index lookup on t1 using i_c1 (c1=t2.c1), with index condition: (t1.c1 = t2.c1) (cost=0.50 rows=1)
|
+-----------------------------------------------------------------------------------------------------------------------------------------+
EXPLAIN ANALYZE
MySQL8.0.18添加了EXPLAIN ANALYZE。该语句会真实执行，并收集执行时候的实际信息，在EXPLAIN FORMAT=TREE的基础上添加了这些信息的展示。这些信息有：每个Operator执行的耗时、输出的行数、反复执行的次数。
文章来源：
Author：杨泽(勉仁)
link：/monthly/monthly/2024/05/02/