作者：勉仁
查询优化是数据库管理非常重要的一个方面，而使用合适的查询优化分析方法可以大幅提升效率。本文将要介绍查询优化中常用的分析方法，包括EXPLAIN命令、Optimizer Trace、Profiling、常用的监控指标。
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列展示表示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 |
描述 |
表名 |
表的名称（有别名为别名） |
<derivedN> |
物化表，N为内部标号 |
<subqueryN> |
物化的subquery |
<unionM,N> |
UNION query block:M和N 的result |
partitions列
分区表需要访问哪些分区。
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执行的耗时、输出的行数、反复执行的次数。
mysql> explain analyze 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) (actual time=0.668..0.824 rows=200 loops=1) -> Table scan on t3 (cost=0.03 rows=206) (actual time=0.007..0.125 rows=206 loops=1) -> Hash -> Nested loop inner join (cost=62.05 rows=69) (actual time=0.089..0.570 rows=100 loops=1) -> Filter: ((t2.c1 > 50) and (t2.c1 is not null)) (cost=20.85 rows=69) (actual time=0.069..0.195 rows=100 loops=1) -> Table scan on t2 (cost=20.85 rows=206) (actual time=0.027..0.147 rows=206 loops=1) -> Index lookup on t1 using i_c1 (c1=t2.c1), with index condition: (t1.c1 = t2.c1) (cost=0.50 rows=1) (actual time=0.003..0.003 rows=1 loops=100) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Optimizer Trace
MySQL Optimizer Trace功能会跟踪MySQL优化器对查询优化过程的关键信息，用户可以通过语句获取这些信息，帮助用户理解为什么MySQL会选择当前计划，对排查MySQL计划选择问题非常有用。
相关表
Optimizer trace在打开记录后，可以通过表INFORMATION_SCHEMA.OPTIMIZER_TRACE来查看。 表的列定义如下，列QUERY和TRACE实际最大不止65535。
mysql> desc information_schema.optimizer_trace; +-----------------------------------+----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------------------+----------------+------+-----+---------+-------+ | QUERY | varchar(65535) | NO | | | | | TRACE | varchar(65535) | NO | | | | | MISSING_BYTES_BEYOND_MAX_MEM_SIZE | int(20) | NO | | | | | INSUFFICIENT_PRIVILEGES | tinyint(1) | NO | | | | +-----------------------------------+----------------+------+-----+---------+-------+
各列的含义如下：
- QUERY 跟踪的查询
- TRACE 跟踪的信息，用Json格式展示。
- MISSING_BYTES_BEYOND_MAX_MEM_SIZE 表示有多少trace信息因trace记录大小已经超过optimizer_trace_max_mem_size，导致没有记录下来。
- INSUFFICIENT_PRIVILEGES 如果有VIEW或者存储过程定义了SQL SECURITY，当因没有权限无法查看trace的时候，会标记为1。
相关变量与使用
Optimizer trace的相关变量如下：
- optimizer_trace optimizer_trace是一个switch变量，switch开关有'enabled'和'one_line'，默认为'enabled=off,one_line=off'。 enabled控制是否开启optimizer trace，配置为on即开启。 one_line控制输出的trace是否打印为一行，配置为on的时候会将trace展示为一行，拷贝时候方便，但不便阅读。off时候会json格式展开，方便直接阅读。
- optimizer_trace_max_mem_size
用来限制trace记录最大可以使用的内存空间，单位是byte。 如果optimizer_trace_max_mem_size不够存储所有信息，information_schema的optimizer_trace表MISSING_BYTES_BEYOND_MAX_MEM_SIZE列会展示有多少字节未被记录。可以根据需要调大该参数的值。
- optimizer_trace_features
用来控制跟踪哪些优化器流程。默认值是'greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on'。 greedy_search是控制跟踪JOIN ORDER的选择过程，MySQL JOIN ORDER选择算法是greedy_search。 range_optimizer是控制跟踪分析扫描范围的优化过程。 dynamic_range是控制跟踪动态range优化。 repeated_subselect是控制是否跟踪子查询的重复执行。对于一些执行过程中的优化考量，optimizer trace也会跟踪，对于关联子查询就会出现非常多次，通过这个开关可以控制是否跟踪子查询的重复执行。
- optimizer_trace_offset、optimizer_trace_limit
两个参数是配合使用的，用来缓存一定条数的optimizer trace。optimizer_trace_offset默认值为-1，表示不缓存。当配置为大于等于0的时候，表示从第几条开始缓存。optimizer_trace_limit表示缓存多少条trace记录，默认为1。当配置缓存后，超过optimizer_trace_limit的trace后，就不再缓存新的记录。 例如配置optimizer_trace_offset为0，optimizer_trace_limit为8，那么information_schema中optimizer_trace表最大就会缓存8条trace记录，后面的查询trace不再缓存。
- end_markers_in_json 默认为OFF。当配置为ON，会给TRACE的JSON格式记录中，每一部分都添加结尾。这样对于JSON很大的记录部分，可以清晰看到结尾的位置。 Optimizer trace的基础使用方法如下：
-- 开启optimizer trace set optimizer_trace='enabled=on'; -- 可根据trace memory是否超过限制设定 set optimizer_trace_max_mem_size=内存限制大小; -- 执行explain query；或者直接执行query explain query; -- query; -- 查看optimizer trace记录 select * from information_schema.optimizer_trace;
其他对optimizer trace的控制可以根据上述的相关变量做需要的配置。
主要信息
Optimizer Trace跟踪和展示的信息包含了在文章《MySQL查询优化分析 - MySQL优化执行的基础概念》MySQL优化器框架一节介绍的MySQL优化器主要流程，逻辑优化、初始分析、基于代价的物理优化、JOIN ORDER选择、计划改进。
- join_preparation
这段trace信息对应了逻辑优化流程。(anti)semi-join转换；视图合并等查询变换会记录在这里。
- join_optimization
这对应内核代码JOIN::optimize的主要流程，做各类初始分析，基于代价的物理优化、计划改进。
- condition_processing 这段是做谓词条件优化等操作。包含了：等值推导、常量传播(Constant Propagation)。
- substitute_generated_columns
处理function index，替换其generated column条件从而可以利用function index做过滤。
- table_dependencies 推导表依赖关系。比如left join一定要需要先执行前表。
- rows_estimation 表单独访问时候行数估算。这里对应的是初始分析中表访问分析，这里估算的行数并不代表最终访问行数和代价。其可以帮助JOIN ORDER选择时候有初始选择顺序，便于剪枝，同时在greed_search选择JOIN ORDER时候同样表的索引分析会利用这里分析过的扫描行数。
- considered_execution_plans
这里是真正的路径、JOIN ORDER选择部分
- plan_prefix
表示前缀表
- best_access_path
当前表最优路径分析
- rest_of_plan
后续表JOIN的路径分析
- attaching_conditions_to_tables 将where上可以下推到表上的条件推到表上，可以提前过滤数据。
- optimizing_distinct_group_by_order_by
优化distinct\group by\order by，对于可以省略的操作会省略，对于常量列会从这些操作中移除。
- reconsidering_access_paths_for_index_ordering 重新考虑驱动表上其他索引是否有可以提供Order by需要的序，省略filesort，同时代价优化器认为更低。如果有这里就会替换掉前面best_access_path已经选择的索引。
Profiling
MySL profiling功能可以收集查询执行期间系统资源的消耗，包含了各个阶段的运行时间、CPU、IO的使用情况。
相关变量与使用
相关变量如下：
- profiling
Profiling开关。默认值为OFF。当配置为ON，打开Profiling功能。
- profiling_history_size
当打开profiling，保存多少条查询的Profiling信息。默认值为15。 使用相关的语句： SHOW PROFILES; 展示缓存了Profiling信息的查询语句及他们的id编号、执行耗时。
mysql> show profiles; +----------+------------+---------------------------------------+ | Query_ID | Duration | Query | +----------+------------+---------------------------------------+ | 1 | 0.00046100 | select * from t1 order by c1 | | 2 | 0.00041125 | select * from t1 order by c1 limit 10 | +----------+------------+---------------------------------------+
SHOW PROFILE [type] [FOR QUERY n] [LIMIT row_count [OFFSET offset]]; type取值可以是 ALL\BLOCK IO\CONTEXT SWITCHES\CPU\IO\MEMORY\PAGE FAULTS\SOURCE\SWAPS。多个type之间用逗号间隔。其中MEMORY并未实现。 直接执行SHOW PROFILE展示的是最新一条被缓存的Profiling信息。 SHOW PROFILE FOR QUERY n中的n是SHOW PROFILES中的编号，可以查看指定缓存的Profiling。 LIMIT OFFSET控制的是一条查询的Profiling信息中输出哪部分行（阶段）的信息。
mysql> show profile for query 3; +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000103 | | Executing hook on transaction | 0.000009 | | starting | 0.000016 | | checking permissions | 0.000007 | | Opening tables | 0.000088 | | init | 0.000011 | | System lock | 0.000012 | | optimizing | 0.000005 | | statistics | 0.000029 | | optimizing_phase2 | 0.000005 | | preparing | 0.000068 | | executing | 0.000060 | | end | 0.000004 | | query end | 0.000006 | | waiting for handler commit | 0.000009 | | closing tables | 0.000009 | | freeing items | 0.000059 | | cleaning up | 0.000005 | +--------------------------------+----------+
主要信息
show profile各个status表示的信息如下，下面的信息部分是包含了：
- starting 初始化状态、SQL解析
- checking permissions 权限检查
- Opening tables 打开查询相关的表
- init 初始化查询所需结构
- System lock 查询表锁等
- optimizing 查询优化阶段
- statistics 收集统计信息
- executing 执行
- Sending data 开始返回数据的开销
- Creating sort index 排序
常用的监控
慢查询日志及审计
在PolarDB中通过慢查询日志和审计，我们可以看到数据库慢查的执行时间、扫描行数、锁等待时间等信息。对于审计日志还包含了logical read\physical read，查询逻辑读、物理读的次数，可以用于辅助排查IO和回滚历史版本的影响。同时通过审计日志对一个session所有操作的记录，我们可以排查session系统变量配置等操作对查询的影响。
运行指标状态监控
常用的状态指标有：
- CPU
一方面，多个慢查的并发执行会导致CPU负载高。 另一方面CPU负载打满时候，也会导致短查询因得不到有效的调度执行变慢。
- Active sessions
活跃连接数。当活跃连接数太高，CPU会花费大量时间在线程切换上，从而导致查询变慢，CPU打满。
- 历史事务长度
Innodb_trx_history_list_len。如果该值很大，那么意味着有长查询。大量历史事务会导致大量的更新删除等操作产生的旧数据没有被Purge。查询会读取很多脏数据，拖慢数据库整体性能。同时二级索引由于record上没有trx_id信息，当查询期间二级索引叶上有更新的时候可能会带来这些脏数据的回表判断可见性，对查询的性能影响会很大。
- 长事务
通过访问information_schema.innodb_trx我们可以观察是否有长事务。如果有长事务未提交，相关的锁会一直持有、会影响ReadView、DDL无法进行。也会影响二级索引访问的可见性判断，原本不需要回表的查询可能出现大量回表做可见性判断。
- 行锁指标
对于一些需要加锁的DML或者SELECT FOR UPDATE等语句，是否有行锁、锁等待时间对性能影响很大。 Innodb_row_lock_time 表示行锁总的等待时间。 Innodb_row_lock_time_max 表示行锁最大的等待时间 Innodb_row_lock_waits 表示行锁总的等待次数
- page读指标
Innodb_pages_read InnoDB从磁盘读取page的总数 Innodb_buffer_pool_read_requests 表示访问InnoDB buffer pool缓存的次数 Innodb_buffer_pool_reads 表示访问InnoDB buffer pool缓存未命中后访问磁盘次数
总结
本文介绍了MySQL查询优化分析的常用方法EXPLAIN、Optimizer Trace、Profiling和常用监控指标。查询优化分析往往会涉及到优化器、执行器、引擎特征等各方面知识。优化器方面往往需要考虑：是否有合适索引；统计信息是否准确；是否有更高效的查询变换；是否列类型等影响了索引选择。执行器：计划的执行过程如何；排序数据量；物化表数据量；JOIN产生的数据量；是否有开销大的自定义函数。引擎：访问数据是否需要回表；MVCC机制，可见性判断，可读版本的影响；行锁加锁范围、加锁顺序。在利用上述方法分析后，再做测试验证。
欢迎访问阿里云云数据库 RDS MySQL 详情页了解更多信息：https://www.aliyun.com/product/rds/mysql
