Linux服务器上的MySQL出现慢查询,如何结合执行计划和资源指标定位瓶颈?
Linux服务器上的MySQL出现慢查询时,不能只看到接口变慢就直接加索引。应用端耗时可能包含连接池等待、网络传输和结果集处理,MySQL慢日志记录的则主要是服务端执行与等待时间。需要把慢日志、执行计划、锁等待、Linux资源指标放到同一条时间线上,才能判断瓶颈究竟在SQL计划、磁盘、内存、锁竞争,还是数据库之外的请求链路。
一个可执行的判断路径是:先确认慢查询是否真实且集中发生,再按网络与连接、Linux系统、MySQL服务、SQL和应用逻辑建立原因树;随后使用低风险只读检查逐层排除;最后通过执行计划和资源变化定位根因,并用相同参数、相近负载验证恢复情况。

一、先确认慢查询的时间范围和影响范围
1. 区分接口慢、连接慢和SQL执行慢
先记录以下信息:
- 慢查询首次出现和持续结束的时间;
- 受影响的接口、任务或业务功能;
- SQL模板及实际参数范围;
- 慢查询的平均耗时、P95、P99和最大耗时;
- 同期请求量、错误率、连接池等待时间;
- MySQL所在服务器的CPU、内存、磁盘和网络情况。
如果接口耗时为2秒,但MySQL慢日志中的Query_time只有几十毫秒,问题通常不在SQL执行本身,需要继续检查连接池获取连接、应用线程阻塞、网络传输和结果集处理。
如果慢日志中的Query_time接近接口总耗时,并且Rows_examined明显偏大,则执行计划和数据访问路径的优先级更高。
2. 检查慢查询日志配置
先确认慢日志是否开启、阈值是多少,以及日志是否有足够的磁盘空间。以下命令只读取配置,不会修改数据库参数:
mysql -e "SHOW VARIABLES WHERE Variable_name IN ('slow_query_log','slow_query_log_file','long_query_time','log_queries_not_using_indexes','log_slow_replica_statements');"
df -h
不同MySQL版本和发行版的变量可能略有差异。如果没有慢日志,不建议在业务高峰直接开启并设置极低阈值。应先确认日志目录、轮转策略和磁盘余量,再由具备权限的人员在可控时间窗口内短时采集。
常见慢日志片段可以类似下面这样:
# Time: 2025-01-15T10:20:31.123456Z
# Query_time: 2.184321 Lock_time: 0.003120 Rows_sent: 20 Rows_examined: 2843150
SELECT id, status, created_at
FROM orders
WHERE tenant_id = 18 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;
上面只是格式示例,不代表某个实际环境已经执行过检查。判断时重点关注:
Query_time:服务端处理该语句的总时间;Lock_time:等待锁的线索,不能单独作为InnoDB锁竞争的唯一依据;Rows_sent:返回给客户端的行数;Rows_examined:执行过程中检查过的行数。
如果只返回20行,却检查了数百万行,通常需要优先检查索引和过滤条件。如果Rows_examined不高但Query_time很长,则应转向锁等待、磁盘延迟、临时表或网络结果传输。
3. 按SQL模板聚合,而不是只看单条记录
同一条SQL可能因为参数不同而呈现完全不同的耗时。例如某个租户只有几百行数据,另一个租户有数千万行数据。可以在启用Performance Schema时,按摘要查看耗时和扫描量:
SELECT
DIGEST_TEXT,
COUNT_STAR,
ROUND(AVG_TIMER_WAIT / 1000000000000, 3) AS avg_seconds,
ROUND(MAX_TIMER_WAIT / 1000000000000, 3) AS max_seconds,
SUM_ROWS_EXAMINED,
SUM_ROWS_SENT,
FIRST_SEEN,
LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IS NOT NULL
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 20;
TIMER_WAIT通常以皮秒计,上例除以1000000000000后得到秒。若Performance Schema未启用,或者语句摘要表没有数据,应以慢日志、应用APM和数据库监控中的SQL聚合结果替代,不要把“没有统计数据”误判为“没有慢查询”。
二、建立慢查询原因树
慢查询可以按请求链路拆成四层。每一层都需要对应的观察依据,不能仅凭CPU高或某条SQL慢就直接归因。
| 层级 | 典型原因 | 主要观察依据 | 下一步检查 |
|---|---|---|---|
| 网络与连接 | 连接建立慢、连接池等待、网络重传、结果集传输慢 | 应用连接池等待时间、连接错误、接口耗时与慢日志耗时差值 | 检查连接池、ss、网络延迟和返回数据量 |
| Linux系统 | CPU饱和、内存不足、Swap、磁盘延迟、容器资源限制 | vmstat、iostat、free、容器或systemd资源指标 | 判断是计算瓶颈、IO瓶颈还是内存回收 |
| MySQL服务 | 行锁等待、活跃线程过多、临时表落盘、缓冲池命中下降 | SHOW PROCESSLIST、InnoDB状态、全局状态计数器 | 检查锁、并发度、Buffer Pool和临时表 |
| SQL与应用 | 缺少索引、索引失效、估算错误、重复查询、返回结果过大 | EXPLAIN、EXPLAIN ANALYZE、慢日志参数 | 优化访问路径、SQL写法或调用频率 |
连接池等待高而MySQL执行时间低,不应通过增加索引解决。相反,MySQL中Threads_running持续升高、慢日志扫描行数明显异常,且应用连接池并没有等待,则应优先看SQL和资源。
三、逐层排除外部与系统资源问题
1. 先看应用连接池和网络差值
应用侧至少需要区分三个时间:
- 获取数据库连接的等待时间;
- SQL从发送到收到结果的时间;
- 结果集反序列化和业务处理时间。
如果连接池等待时间占总耗时的大部分,常见原因包括连接池上限过小、连接没有及时归还、事务持有连接时间过长,或者数据库并发已经超过承载能力。盲目增大连接池可能让更多请求同时进入MySQL,导致CPU、锁和磁盘压力进一步增加。
Linux侧可以先查看连接概况:
ss -s
ss -ant state established '( sport = :3306 or dport = :3306 )'
如果MySQL使用非默认端口,应替换端口号。ss看到的连接数量不能直接等于正在执行的SQL数量,还需要与MySQL的Threads_connected和Threads_running对照。
结果集过大时,即使执行计划合理,也可能在网络传输和应用解析阶段耗时。慢日志中的Rows_sent、应用返回体大小和接口序列化时间应一起查看。
2. 查看CPU、内存和磁盘指标
以下命令用于采集一个短时间窗口。iostat和pidstat通常由sysstat包提供,未安装时先确认系统软件管理策略,不要在生产高峰临时安装造成额外影响。
vmstat 1 5
free -h
iostat -xz 1 5
如果需要观察mysqld进程,可以先确认进程名和PID:
pgrep -a mysqld
PID=$(pgrep -xo mysqld)
if [ -n "$PID" ]; then
pidstat -p "$PID" 1 5
fi
参考判断方式如下:
vmstat的r持续高于可运行CPU数量,且用户态或内核态CPU接近上限,说明计算资源紧张;si、so持续出现,或者free -h显示Swap使用量不断增加,说明内存压力需要优先处理;iostat中的await明显升高、%util长期接近设备承载上限,说明磁盘IO可能成为瓶颈;wa较高但磁盘指标正常时,还要检查网络存储、容器限额或其他块设备;- 系统负载高不等于一定是CPU不足,IO等待和不可中断睡眠同样会推高负载。
例如,下面是一组用于演示判断的模拟数据:

| 指标 | 现象示例 | 初步含义 |
|---|---|---|
| CPU使用率 | 35% | 不支持“CPU已经打满”的判断 |
| iowait | 28% | 需要重点检查磁盘延迟 |
磁盘await | 80 ms | 读写等待明显,需要关联MySQL读IO |
| Swap in/out | 持续非零 | 需要排查内存不足或异常回收 |
Threads_running | 从8升至70 | 可能存在并发堆积、锁等待或慢SQL |
这些指标必须与慢查询发生的同一时间窗口对齐。只看当前状态,无法解释已经结束的故障。

3. 用MySQL状态计数器观察趋势
许多MySQL状态变量是累计值,单次执行结果没有太大意义。应在故障窗口前后各采集一次,计算差值和每秒增量:
SHOW GLOBAL STATUS
WHERE Variable_name IN (
'Threads_connected',
'Threads_running',
'Connections',
'Threads_created',
'Created_tmp_tables',
'Created_tmp_disk_tables',
'Innodb_buffer_pool_read_requests',
'Innodb_buffer_pool_reads',
'Innodb_row_lock_current_waits',
'Innodb_row_lock_time',
'Innodb_row_lock_time_max',
'Innodb_row_lock_waits',
'Questions',
'Slow_queries'
);
几个常见判断点:
Threads_connected高,表示连接多;Threads_running高,才更接近正在执行或等待的活跃线程数量;Created_tmp_disk_tables在窗口内增长较快,说明部分临时表落到了磁盘,需要结合排序、分组和内存配置分析;Innodb_buffer_pool_reads相对于Innodb_buffer_pool_read_requests增长较快,说明部分数据无法直接从缓冲池命中,但不能单凭一个比例决定要扩大内存;Innodb_row_lock_current_waits或锁等待计数增长时,应检查长事务和写入冲突;Slow_queries只表示超过阈值的语句数量,不能代替按SQL摘要分析。
例如在一个采样窗口内,若缓冲池逻辑读请求增加100万次,物理读增加2万次,可用一个近似比例表示缓冲池命中情况:
“近似命中率 = 1 - 物理读次数 ÷ 逻辑读请求次数”
代入示例后为:
“1 - 20,000 ÷ 1,000,000 = 98%”
这只是该时间窗口的参考值,不能证明所有表、所有查询都具有相同命中率。
四、用执行计划判断是否真的缺少索引
1. 先确认版本和表结构
执行计划与MySQL版本有关,先确认版本、存储引擎、索引和字符集等信息:
SELECT VERSION();
SHOW CREATE TABLE orders\G
SHOW INDEX FROM orders;
执行计划必须尽量使用真实SQL模板和具有代表性的参数。参数选择性不同,优化器可能选择不同索引。涉及敏感数据时应脱敏,但不要把所有参数都替换成同一个值,否则可能失去诊断意义。
2. 使用EXPLAIN查看访问路径
以一个订单查询为例:
EXPLAIN
SELECT id, status, created_at
FROM orders
WHERE tenant_id = 18
AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;
重点观察以下字段:
type:ALL通常代表全表扫描,range、ref、eq_ref通常表示使用了更有针对性的访问方式,但不能脱离数据分布判断;possible_keys:优化器认为可能使用的索引;key:实际选择的索引,若为NULL,说明本次计划未使用索引;rows:优化器估算需要读取的行数;filtered:经过条件过滤后预计保留的比例;Extra:是否出现Using temporary、Using filesort、Using index condition等信息。
例如,下面是模拟的执行计划片段:

| table | type | possible_keys | key | rows | filtered | Extra |
|---|---|---|---|---|---|---|
| orders | ALL | NULL | NULL | 2,843,150 | 0.01 | Using where; Using filesort |
这个结果与“返回20行、检查数百万行”的慢日志能够相互印证,说明当前访问路径存在明显优化空间。但Using filesort并不意味着一定错误,排序的数据量、是否有合适的索引以及查询选择性都要一起判断。
3. MySQL 8.0.18及以上使用EXPLAIN ANALYZE验证估算
在支持该语法的MySQL 8.0版本中,可以对只读查询使用:
EXPLAIN ANALYZE
SELECT id, status, created_at
FROM orders
WHERE tenant_id = 18
AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;
EXPLAIN ANALYZE会实际执行查询。生产环境使用前需要确认:
- 语句是只读查询,不能把可能修改数据的语句直接拿来测试;
- 查询不会触发长时间锁等待或返回大量结果;
- 最好在只读副本、测试环境或低峰期执行;
- 采集前确认查询参数不会暴露敏感数据;
- 语句执行本身可能增加CPU、IO和Buffer Pool压力。
输出中的actual time、actual rows和loops用于对照优化器估算。如果估算返回几百行,但实际扫描数达到数百万行,通常需要检查统计信息、数据倾斜或谓词条件。如果估算和实际行数接近,但实际耗时仍高,则应继续查看磁盘、锁和并发。
MySQL 5.7或不支持EXPLAIN ANALYZE时,可使用EXPLAIN结合慢日志、状态计数器和测试环境执行结果进行判断,不要因为缺少该语法就直接修改索引。
4. 判断索引是否匹配查询条件
对于下面的查询:
SELECT id, status, created_at
FROM orders
WHERE tenant_id = 18
AND status = 'paid'
AND created_at >= '2025-01-01 00:00:00'
ORDER BY created_at DESC
LIMIT 20;
一个可能的候选索引是:
CREATE INDEX idx_orders_tenant_status_created
ON orders (tenant_id, status, created_at);
这个索引只是候选方案,不是可以直接复制到所有环境的固定答案。是否有效需要结合以下条件验证:
tenant_id是否具有足够选择性;status是否只有少数几个值;created_at范围是否很大;- 是否有其他查询依赖不同的索引顺序;
- 索引增加后写入、更新和磁盘空间成本是否可接受;
ORDER BY是否能够利用索引顺序,还是仍需要额外排序。
常见导致索引失效或效果不佳的写法包括:
- 对索引列使用函数或表达式,例如
DATE(created_at) = ...; - 字符串列与数字参数隐式转换;
LIKE '%keyword'这类前导通配符;- 联合索引只使用了非最左列;
- 用低选择性列单独建索引,却没有结合租户、状态或时间范围;
- 统计信息过期,导致优化器错误估算;
OR条件使优化器难以选择单一路径。
如果确认统计信息异常,可在备份、监控和低峰条件下考虑:
ANALYZE TABLE orders;
该操作会更新表统计信息,可能改变执行计划,也可能带来额外资源消耗。它不是数据备份,也没有简单的“撤销”操作,因此应先记录变更前后的执行计划,必要时在测试环境验证。
创建大表索引前,应确认数据备份可恢复、磁盘有足够空间、表的存储引擎和当前MySQL版本支持预期的在线DDL方式,并评估元数据锁风险。索引创建期间可能消耗CPU、IO并影响写入。若新索引效果不佳,回滚通常是删除该索引,但删除索引同样可能消耗资源并等待元数据锁,不能在高峰期直接执行。MySQL 8.0可以在测试环境使用不可见索引验证优化器行为,但最终仍需结合生产流量确认。
五、排查锁等待和并发堆积
如果执行计划看起来合理,但慢日志中的锁相关时间升高,或Threads_running与锁等待计数同时增长,应把重点转向事务。
先查看当前线程:
SHOW FULL PROCESSLIST;
再查看InnoDB状态:
SHOW ENGINE INNODB STATUS\G
重点寻找:
- 长时间处于
Waiting for ... lock的线程; - 持有锁但长时间不提交的事务;
- 大批量更新与在线查询争用同一批记录;
- 应用连接池中的事务连接没有及时归还;
- 事务范围过大,单次修改行数过多。
不要因为看到一个长事务就直接执行KILL。终止会话可能触发回滚,回滚时间甚至长于原事务执行时间,还可能对业务造成部分请求失败。处理前应确认会话归属、事务影响范围、是否有备份或重试机制,并由业务负责人确认。若确需终止,应先记录线程ID、SQL和事务状态,随后观察回滚与锁是否释放。
锁等待与索引问题也可能同时存在。例如更新语句扫描大量记录,会持有更大范围的锁;查询缺少索引,也可能在高并发下造成大量行访问。此时应先缩小扫描范围,再评估事务边界和提交频率。
六、定位根因的判断组合
单项指标很少足以定案,可以使用以下组合判断:
| 观察组合 | 更可能的根因 | 验证方向 |
|---|---|---|
Query_time高、Rows_examined远大于Rows_sent、key为NULL | 全表扫描或索引不匹配 | 检查谓词、联合索引顺序和数据选择性 |
Query_time高、Lock_time和InnoDB锁等待同步升高 | 事务或行锁竞争 | 查长事务、更新范围和提交时间 |
| 接口耗时高、慢日志耗时低、连接池获取连接耗时高 | 连接池或数据库并发控制问题 | 对照Threads_connected、Threads_running和池等待 |
CPU不高、iowait和磁盘await高、物理读增加 | 存储IO或内存命中不足 | 检查Buffer Pool、数据冷热分布和磁盘设备 |
| 执行计划估算行数与实际行数差异很大 | 统计信息或数据分布问题 | 更新统计信息并在测试环境复核计划 |
SQL执行快但接口返回慢、Rows_sent很大 | 结果集传输和应用处理 | 分页、限制字段、检查序列化和网络 |
缓存可以减少重复读取,但不应作为无法解释慢查询的替代方案。对于稳定的只读数据,可以在应用层设计有失效时间和一致性策略的缓存;对于实时性要求高、数据变化频繁的查询,缓存命中率、失效风暴和回源压力都要纳入评估。MySQL 8.0已移除查询缓存,不应把旧版本查询缓存配置当作通用优化手段。
七、用一个模拟链路完成验证
下面是一条用于说明方法的模拟诊断链路,不代表某个读者环境的实测结果:
- 接口P95从约300毫秒升至1.8秒,但连接池等待仍低于20毫秒。
- 慢日志显示同一SQL的
Query_time约1.5秒,Rows_sent为20,Rows_examined约280万。 - Linux侧CPU约35%,iowait约4%,磁盘延迟没有明显升高。
EXPLAIN显示type=ALL、key=NULL,并出现Using filesort。EXPLAIN ANALYZE显示实际扫描行数远高于返回行数。- 根据租户、状态和时间范围设计候选联合索引,在测试环境验证写入成本和查询计划。
- 变更后用相同参数再次执行,观察扫描行数、P95、CPU、磁盘和锁等待,而不是只看一次执行耗时。
如果加索引后查询变快,但写入延迟、磁盘空间或其他SQL计划明显恶化,则不能把该变更视为完整修复。需要比较索引维护成本、受影响SQL数量和业务高峰下的整体表现。
复发监控点与告警边界
慢查询修复后,至少保留以下监控:
- 按SQL摘要统计P50、P95、P99、最大耗时和调用次数;
- 统计每个SQL的平均扫描行数、返回行数及二者比例;
- 监控
Threads_running、Threads_connected、连接错误和连接池等待; - 监控InnoDB行锁等待、长事务和事务持续时间;
- 监控CPU、iowait、内存、Swap、磁盘延迟、磁盘利用率和文件系统空间;
- 监控临时表落盘、Buffer Pool物理读和慢日志增长速度;
- 对新增或修改的索引记录创建时间、用途、占用空间和验证结果。
告警阈值应以业务基线为主。例如,某SQL的P95连续5分钟达到过去稳定基线的两倍,或者扫描行数与返回行数比例突然扩大,就值得触发排查。iowait超过20%可以作为需要调查的参考信号,但不应脱离磁盘类型、业务负载和历史基线直接判定故障。
最终要保留一次变更前后的对照记录:SQL模板、代表性参数、执行计划、慢日志指标、系统资源、锁等待和回滚方式。这样下一次出现相同SQL变慢时,可以快速判断是数据规模变化、统计信息变化、索引计划变化,还是服务器资源和并发模型发生了变化。
面向Linux服务器上的MySQL业务,A5数据提供中国香港、美国、日本等地区的物理服务器,涵盖不同档位的CPU、内存及SSD、NVMe存储配置,为数据库查询处理、内存缓存和数据读写提供硬件资源。各地区还有相应的线路与带宽方案,可结合应用与数据库的部署地点、访问人群及业务负载,配置计算、存储和网络资源。
本文由A5数据原创发布,转载请注明出处:https://www.a5idc.com/helpcontent/5890.html



