本篇博客将介绍四种常用的 SQL 性能问题定位方法：慢查询日志分析、Explain 执行计划解读、Show Profile 深度分析和 Trace 优化器追踪。用于定位和追踪慢SQL,实现慢SQL的分析和优化。

通过慢查询日志定位

慢查询日志

慢查询日志是 MySQL 提供的一种日志记录功能，用于记录执行时间超过指定阈值（long_query_time）的 SQL 语句。通过分析慢查询日志，我们可以找出执行效率低下的 SQL 语句，为优化提供明确目标。

配置与启用慢查询日志

在 MySQL 配置文件（my.cnf 或 my.ini）中添加：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

分析慢查询日志

MySQL 提供了 mysqldumpslow 工具来分析慢查询日志：

# 查看执行时间最长的10条SQL
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

# 查看访问次数最多的10条SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log

# 查看包含特定关键词的慢查询
mysqldumpslow -g "ORDER BY" /var/lib/mysql/slow.log

通过 EXPLAIN 语句定位

EXPLAIN

EXPLAIN 是分析 SQL 执行计划的关键工具，通过在 SELECT 语句前添加 EXPLAIN 关键字即可使用：

EXPLAIN SELECT * FROM users WHERE age > 30 AND city = 'Beijing';

2.2 关键字段解读

type 字段（访问类型）

type 字段表示 MySQL 在表中找到所需行的方式，从最优到最差依次为：

• system：表只有一行记录（等于系统表）
• const：通过索引一次就找到了，const用于比较primary key或者unique索引
• eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配
• ref：非唯一性索引扫描，返回匹配某个单独值的所有行
• range：只检索给定范围的行，使用一个索引来选择行
• index：Full Index Scan，index与ALL区别为index类型只遍历索引树
• ALL：Full Table Scan，将遍历全表以找到匹配的行

一般来说SQL的访问类型要在 range以上, 否则就要进行分析优化 , 提高性能

rows 字段

表示MySQL认为执行查询必须检查的行数，数值越小越好。

filtered 字段

表示返回结果的行数占需要读取行数的百分比，值越大越好。

Extra 字段

包含MySQL解决查询的额外信息，常见值及其含义：

• Using index：使用覆盖索引，避免了访问表的数据行
• Using where：在存储引擎检索行后再进行过滤
• Using temporary：需要使用临时表来存储结果集，常见于排序和分组查询
• Using filesort：无法利用索引完成的排序操作
• Using join buffer：强调了获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果

高级 EXPLAIN 分析

MySQL 8.0 引入了 EXPLAIN ANALYZE，提供实际执行时的统计信息：

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30 AND city = 'Beijing';

SHOW PROFILE 分析

启用和基本使用

SHOW PROFILE 可以深入分析 SQL 语句执行过程中各个阶段的资源消耗情况。

-- 查看 profiling 是否开启
SELECT @@profiling;

-- 开启 profiling（会话级别）
SET profiling = 1;

-- 执行需要分析的SQL
SELECT * FROM orders WHERE customer_id = 1000;

-- 查看所有查询的概要信息
SHOW PROFILES;

-- 查看特定查询的详细时间消耗
SHOW PROFILE FOR QUERY 1;

-- 查看特定查询的CPU和IO消耗
SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;

PROFILE 解析

• starting：查询开始执行
• checking permissions：检查权限
• Opening tables：打开表
• init：初始化
• System lock：系统锁
• optimizing：优化
• statistics：统计信息
• preparing：准备
• executing：执行
• Sending data：发送数据（通常是最耗时的阶段）
• end：结束
• query end：查询结束
• closing tables：关闭表
• freeing items：释放资源
• cleaning up：清理

示例

//执行查询
mysql> select * from question;

如图为执行结果，通过show profiles可以找到最近的查询记录，再根据SHOW PROFILE FOR QUERY 6;查看ID为6的查询分析。分析结果如图所示:

1. starting (0.000246秒)
   • 耗时正常，属于查询启动的标准开销。
2. Executing hook on transaction (0.000001秒)
   • 耗时极短，完全正常
3. Opening tables(0.030310秒)
   • 这是最耗时的阶段，占总执行时间的76%，明显异常。
   • 这里耗时长是因为我启动时，InnoDB缓存池中无表结构，缓存未命中，是从磁盘加载表结构。

通过分析各个阶段的耗时，可以精准定位SQL执行的瓶颈所在。

Trace 分析优化器选择过程

启用 Trace 功能

Trace 功能可以让我们了解优化器如何选择执行计划，为什么选择A计划而不是B计划。

-- 开启trace功能（默认关闭）
SET optimizer_trace="enabled=on";

-- 设置trace最大内存，避免过大SQL导致截断
SET optimizer_trace_max_mem_size=1000000;

-- 执行SQL查询
SELECT * FROM orders WHERE customer_id = 1000 AND order_date > '2023-01-01';

-- 查看trace信息
SELECT * FROM information_schema.optimizer_trace;

-- 关闭trace功能
SET optimizer_trace="enabled=off";

Trace 参数

Trace 输出是一个JSON格式的数据，主要包含以下部分：

1. join_preparation：准备阶段，如格式化SQL等
2. join_optimization：优化过程，包括：
   • condition_processing：条件处理
   • table_dependencies：表依赖关系
   • ref_optimizer_key_uses：可能使用的索引
   • considered_execution_plans：考虑的执行计划
   • attaching_conditions_to_tables：附加条件到表
3. join_execution：执行阶段

通过分析considered_execution_plans部分，可以了解优化器为什么选择了最终的执行计划，以及放弃了哪些可能的执行计划。

SQL优化步骤

1. 首先检查慢查询日志，确认该查询确实被记录为慢查询
2. 使用EXPLAIN分析，查看执行计划，重点关注type、rows、key和Extra字段
3. 如果EXPLAIN不能明确问题，使用SHOW PROFILE分析各阶段耗时
4. 如果怀疑优化器选择了错误的执行计划，使用Trace功能分析优化器的决策过程
5. 根据分析结果采取相应优化措施，如添加索引、重写SQL、调整数据库配置等

6. 总结

方法	适用场景	优点	缺点
慢查询日志	长期监控，找出所有慢SQL	全面	事后分析，不能实时
EXPLAIN	分析单条SQL执行计划	快速，简单	只是预估，非实际执行
SHOW PROFILE	分析SQL执行各阶段耗时	详细的时间消耗分析	需要会话级别开启
Trace	分析优化器选择过程	深入了解优化器决策	输出复杂，需要解析

SQL优化是一个持续的过程，需要结合业务特点和数据变化不断调整优化策略。