在高并发、大数据量的业务场景下，数据库响应速度直接决定了系统的用户体验与稳定性。慢SQL如同隐藏在系统中的“性能刺客”，不仅无谓地消耗着数据库的CPU、内存和IO资源，还可能引发连接池耗尽、请求超时等连锁反应，最终导致服务雪崩。因此，快速、精准地定位慢SQL，是进行数据库性能调优的首要且关键的步骤。
本文将系统性地介绍三种由浅入深定位慢SQL的方法，并以MySQL为例，涵盖从初步发现到深度剖析的全流程，旨在帮助开发者建立一套完整的慢SQL治理思路。
一、慢查询日志：初步定位耗时SQL列表
慢查询日志是MySQL内置的一项基础功能，它会自动记录所有执行时间超过预设阈值的SQL语句，并包含执行时长、锁等待时间、扫描行数等详细信息。这是发现效率低下查询最直接的工具。
1. 三步开启慢查询日志
默认情况下，MySQL并未开启慢查询日志（以避免不必要的性能开销）。我们可以通过临时或永久两种方式开启它。对于生产环境，建议永久开启并合理配置阈值。
第一步：查看当前配置
首先，登录MySQL客户端，通过以下命令了解慢查询相关的各项配置状态：
-- 查看慢查询日志是否开启（ON=开启，OFF=关闭）
show variables like '%slow_query_log%';
-- 查看慢查询阈值（单位：秒，默认10s）
show variables like 'long_query_time';
-- 查看日志输出方式（FILE=文件，TABLE=系统表）
show variables like '%log_output%';
-- 查看未走索引的SQL是否记录（ON=记录，OFF=不记录）
show variables like 'log_queries_not_using_indexes';
-- 查看慢查询日志存储路径
show variables like 'slow_query_log_file';
典型的结果示例如下（默认关闭状态）：
| Variable_name |
Value |
| slow_query_log |
OFF |
| slow_query_log_file |
/var/lib/mysql/localhost-slow.log |
| long_query_time |
10.000000 |
| log_output |
FILE |
| log_queries_not_using_indexes |
OFF |
建议：在生产环境中，long_query_time 通常建议设置为0.5到1秒，而非默认的10秒，以便能更早地捕捉到潜在的性能瓶颈。可以通过 SET SESSION long_query_time = 0.5; 在当前会话立即生效以进行调试。但请注意，使用 SET GLOBAL 设置的全局变量对已存在的连接无效，需要新建立的连接才会应用新值。
第二步：临时开启（用于应急排查）
若需在不重启MySQL服务的情况下立即开始记录，可使用 SET GLOBAL 命令临时开启。这种方式适合线上紧急问题排查。
-- 开启慢查询日志
set global slow_query_log = ON;
-- 设置阈值为2秒（新连接生效，旧连接需重新连接）
set global long_query_time = 2;
-- 记录未走索引的SQL（即使未超阈值）
set global log_queries_not_using_indexes = ON;
-- 设置日志输出方式为文件（比表存储更高效）
set global log_output = 'FILE';
-- 刷新当前会话配置，使新的全局慢查询阈值立即对当前连接生效
flush logs;
set session long_query_time = global.long_query_time;
⚠️ 注意：log_queries_not_using_indexes = ON 可能会产生大量日志，特别是对于高频但数据量小的查询。建议仅在排查期间开启，或配合 min_examined_row_limit 参数（例如设为1000，只记录扫描行数超过1000的无索引查询）来减轻日志压力。
第三步：永久开启（生产环境推荐）
临时配置在MySQL重启后会失效。为确保长期有效，需要修改MySQL的配置文件（my.cnf 或 my.ini）。
- Linux系统：路径通常为
/etc/my.cnf 或 /etc/mysql/my.cnf。
- Windows系统：路径通常为
MySQL安装目录/my.ini。
在 [mysqld] 配置节点下，添加或修改以下配置：
[mysqld]
# 开启慢查询日志
slow_query_log = 1
# 慢查询日志文件路径（需确保MySQL进程有写入权限）
slow_query_log_file = /var/lib/mysql/mysql-slow.log
# 慢查询阈值（单位：秒）
long_query_time = 2
# 记录未使用索引的SQL（建议开启）
log_queries_not_using_indexes = 1
# 仅当扫描行数超过此值时才记录无索引查询（可选，减轻日志压力）
min_examined_row_limit = 1000
# 记录执行耗时超过阈值的管理员语句（如ALTER TABLE等DDL）（可选）
log_slow_admin_statements = 1
# 记录慢的优化器执行计划（可选）
log_slow_slave_statements = 1
# 日志输出方式（FILE=文件，TABLE=mysql.slow_log表，也可同时设置FILE,TABLE）
log_output = FILE
配置完成后，重启MySQL服务以使配置生效。
2. 慢查询日志解读与分析
慢查询日志中的每条记录都包含了丰富的信息，以下是一个典型示例：
# Time: 2024-05-20T15:30:45.123456Z
# User@Host: app_user[app_user] @ 192.168.1.100 [192.168.1.100] Id: 1234
# Query_time: 3.800000 Lock_time: 0.000200 Rows_sent: 150 Rows_examined: 120000
# Rows_affected: 0 Bytes_sent: 28500
SET timestamp=1684606245;
select * from order_info where user_id = 567 and create_time between '2024-01-01' and '2024-06-01';
关键字段解读与优化线索：
Time: SQL执行的精确时间戳。User@Host: 执行该SQL的用户和客户端IP，可用于定位问题请求的业务来源。Query_time: SQL实际执行时间（本例为3.8秒，超过了2秒的阈值）。Lock_time: 等待表锁或行锁的时间（0.0002秒，说明并非锁等待导致变慢）。Rows_sent: 返回给客户端的行数（150行）。Rows_examined: MySQL服务器层扫描的行数（12万行）。这是最关键的指标之一。扫描行数（12万）远大于返回行数（150），强烈暗示该查询可能没有使用合适的索引，或者索引失效，导致了全表或大面积扫描。Bytes_sent: 返回数据的字节大小（约28KB，通常不是网络瓶颈）。- 最后一行：具体的慢SQL语句。
3. 慢查询日志分析工具：mysqldumpslow与pt-query-digest
当日志文件变得很大时，手动分析效率极低。此时需要借助工具进行筛选和聚合分析。
工具1：mysqldumpslow（MySQL自带，轻量便捷）
mysqldumpslow 是MySQL官方提供的慢查询日志分析工具，可以按不同维度进行排序和筛选。
# 1. 按执行时间排序，取前10条最慢的SQL
mysqldumpslow -s t -t 10 /var/lib/mysql/mysql-slow.log
# 2. 按扫描行数排序，取前10条（扫描行数越多，通常效率越低）
mysqldumpslow -s r -t 10 /var/lib/mysql/mysql-slow.log
# 3. 按执行次数排序，取前10条（高频的慢SQL对系统整体负载影响更大）
mysqldumpslow -s c -t 10 /var/lib/mysql/mysql-slow.log
# 4. 筛选包含‘order_info’表的慢SQL
mysqldumpslow -g 'order_info' /var/lib/mysql/mysql-slow.log
工具2：pt-query-digest（功能强大，属于Percona Toolkit）
对于更复杂的分析场景，推荐使用 pt-query-digest。它功能更加强大，能够将相似的SQL（例如where user_id=1和where user_id=2）聚合归类，并分析其执行频率、平均耗时、95%耗时等统计信息。
安装（以Linux为例）：
# 安装Percona Toolkit
yum install percona-toolkit -y
# 验证安装
pt-query-digest --version
常用命令示例：
# 1. 分析整个慢查询日志文件，生成详细报告
pt-query-digest /var/lib/mysql/mysql-slow.log > slow_report.txt
# 2. 只分析最近1小时的慢SQL
pt-query-digest --since=1h /var/lib/mysql/mysql-slow.log > slow_report_1h.txt
# 3. 只分析涉及‘order_info’表的慢SQL
pt-query-digest --filter '$event->{table} eq "order_info"' /var/lib/mysql/mysql-slow.log > slow_report_order.txt
# 4. 直接分析mysql.slow_log表（当log_output='TABLE'时非常方便）
pt-query-digest h=127.0.0.1,u=root,p=your_password,D=mysql,t=slow_log > slow_report_table.txt
pt-query-digest 生成的报告摘要部分需要重点关注：Total（总耗时/次数）、Avg（平均耗时）、95%（95%的SQL在此时间内完成，比平均值更具参考价值）、RowsExam（平均扫描行数）以及聚合后的Query模板。
提醒：不要仅仅关注“最慢的一条SQL”。很多时候，那些执行频率很高、耗时中等（例如200ms）的SQL，由于被频繁执行，其对系统资源的累计消耗可能远大于一条偶尔执行、耗时5秒的SQL。这类高频中等慢SQL对系统整体稳定性的威胁更大。
慢查询日志的局限在于，它只能记录已经执行完成且超过阈值的SQL。如果一条SQL因为锁等待或其他原因一直没执行完，或者你想知道一条SQL在执行过程中到底在哪个环节（解析、优化、等待IO等）耗时最多，慢查询日志就无能为力了。
此时，你需要 Performance Schema。它是MySQL 5.5版本引入的性能监控引擎，可以近乎实时地追踪服务器内部执行的几乎所有事件，包括SQL执行的每一个细微步骤。
MySQL 5.6及以上版本通常默认开启。可以通过以下命令确认：
show variables like 'performance_schema';
如果值为 OFF，需要在 my.cnf 配置文件中添加 performance_schema = ON 并重启MySQL。
⚠️ 注意：开启Performance Schema会带来一定的性能开销（通常在5%以内）。如果系统对性能极度敏感，可以只开启需要的监控项，而非全部。
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE '%statements%' OR NAME LIKE '%waits%';
2. 常用监控场景SQL示例
场景1：查看当前正在执行的慢SQL
当系统出现卡顿，但慢查询日志里找不到记录时（SQL可能尚未执行完成），可以查询进程列表。
select
p.id as process_id,
u.user,
p.host,
p.db,
p.command,
p.time as execution_time, -- 已执行时间（秒）
p.state, -- 执行状态（如：Sending data、Sorting result）
p.info as sql_text -- SQL内容
from information_schema.processlist p
left join mysql.user u on p.user = u.user
where p.time > 10 -- 筛选执行超过10秒的SQL
and p.command != 'Sleep'; -- 排除空闲睡眠连接
场景2：分析SQL执行各阶段耗时
通过特定汇总表，可以清晰地看到SQL在解析、优化、执行等阶段的耗时。
select
digest_text as sql_text, -- SQL模板
count_star as execute_count, -- 执行次数
FORMAT_PICO_TIME(sum_timer_parse) as parse_time, -- 解析耗时
FORMAT_PICO_TIME(sum_timer_optimize) as optimize_time, -- 优化耗时
FORMAT_PICO_TIME(sum_timer_execute) as execute_time, -- 执行耗时
sum_rows_examined as total_rows_examined -- 总扫描行数
from performance_schema.events_statements_summary_by_digest
order by sum_timer_execute desc
limit 10;
场景3：查看索引使用情况
了解哪些索引被频繁使用，哪些索引自创建以来从未被使用过（可以考虑删除以节省空间和提升写性能）。
-- 查看指定数据库中索引的使用次数，按次数升序排列可快速发现“僵尸索引”
select
table_schema,
table_name,
index_name,
count_star as use_count -- 使用次数
from performance_schema.table_io_waits_summary_by_index_usage
where table_schema = 'test_db' -- 替换为目标数据库名
order by use_count asc;
提示：Performance Schema的统计数据在MySQL重启后会清零。因此，判断一个索引是否“无用”，需要结合足够长的业务周期（例如一周或一个月）来观察。
三、sys schema：简化性能分析的视图工具
Performance Schema虽然强大，但其表结构复杂，查询语句编写起来比较繁琐。MySQL 5.7版本引入了 sys schema，它基于Performance Schema和Information Schema，提供了一系列预设好的、更易读的视图，大大降低了性能分析的门槛。
常用sys视图查询示例
场景1：快速找出未使用的索引
-- 查看`test_db`数据库中所有从未被使用过的索引
select * from sys.schema_unused_indexes where table_schema = 'test_db';
场景2：定位执行过全表扫描的SQL
-- 查看最近执行过全表扫描的SQL，按执行次数排序
select * from sys.statements_with_full_table_scans order by exec_count desc;
场景3：分析当前的InnoDB锁等待情况
-- 查看当前正在发生的锁等待，包括等待者、持有者、等待的锁信息等
select * from sys.innodb_lock_waits;
这个视图能直观显示是哪个事务阻塞了哪个事务，涉及哪张表哪一行，帮助DBA快速定位并解决死锁或长锁等待问题。
场景4：发现I/O消耗最高的表
-- 按I/O总字节数排序，找出最耗磁盘读写的表文件
select * from sys.io_global_by_file_by_bytes order by total_bytes desc limit 10;
如果某张表的 total_bytes 异常高于其他表，很可能意味着该表存在大量的全表扫描或低效查询，需要优先进行优化。
四、定位之后：建立验证与优化的闭环
通过上述慢查询日志、Performance Schema、sys schema三层次的分析，我们能够精准定位到问题SQL。但这仅仅是第一步，接下来必须进行科学的根因分析与优化验证。
- 务必使用执行计划验证：对定位到的慢SQL，第一时间使用
EXPLAIN（或MySQL 8.0+的 EXPLAIN ANALYZE）查看其执行计划。EXPLAIN 展示的是优化器预计的执行路径，而 EXPLAIN ANALYZE 会实际执行该查询（对写操作需格外谨慎），并返回真实的执行成本，包括精确的扫描行数、是否使用临时表、文件排序等，是判断索引是否生效、是否存在隐式类型转换等问题的金标准。
- 形成“定位→分析→优化→验证”的完整闭环：
- 定位：利用工具发现可疑SQL。
- 分析：结合
Rows_examined、锁等待时间、I/O消耗、EXPLAIN 计划等，综合判断性能瓶颈所在（是索引缺失？是JOIN顺序不佳？还是数据分布问题？）。
- 优化：针对性地采取措施，如添加/调整索引、重写SQL语句、调整表结构、修改数据库配置参数等。
- 验证：优化后，必须再次对比关键指标，如
Query_time、Rows_examined、数据库服务器的CPU和I/O负载等，确保优化措施确实有效且未引入新的问题。
只有建立了这样的闭环优化机制，才能将慢SQL治理从被动的“救火”转变为主动的“防火”，从而在 高并发 与大数据的业务洪流中，持续保障数据库这一核心系统的稳健与高效。关于更多数据库性能优化的实战技巧与深度讨论，欢迎在云栈社区与我们交流。
发表于 16 小时前
|
查看: 0|
回复: 0
