Linux服务器上的MySQL出现慢查询,如何结合执行计划和资源指标定位瓶颈?

发布人:A5数据发布时间:2026-10-26 16:59阅读量:89

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. 先看应用连接池和网络差值

应用侧至少需要区分三个时间:

  1. 获取数据库连接的等待时间;
  2. SQL从发送到收到结果的时间;
  3. 结果集反序列化和业务处理时间。

如果连接池等待时间占总耗时的大部分,常见原因包括连接池上限过小、连接没有及时归还、事务持有连接时间过长,或者数据库并发已经超过承载能力。盲目增大连接池可能让更多请求同时进入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已经打满”的判断
iowait28%需要重点检查磁盘延迟
磁盘await80 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等信息。

例如,下面是模拟的执行计划片段:

用执行计划判断是否真的缺少索引配图

tabletypepossible_keyskeyrowsfilteredExtra
ordersALLNULLNULL2,843,1500.01Using 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已移除查询缓存,不应把旧版本查询缓存配置当作通用优化手段。

七、用一个模拟链路完成验证

下面是一条用于说明方法的模拟诊断链路,不代表某个读者环境的实测结果:

  1. 接口P95从约300毫秒升至1.8秒,但连接池等待仍低于20毫秒。
  2. 慢日志显示同一SQL的Query_time约1.5秒,Rows_sent为20,Rows_examined约280万。
  3. Linux侧CPU约35%,iowait约4%,磁盘延迟没有明显升高。
  4. EXPLAIN显示type=ALL、key=NULL,并出现Using filesort。
  5. EXPLAIN ANALYZE显示实际扫描行数远高于返回行数。
  6. 根据租户、状态和时间范围设计候选联合索引,在测试环境验证写入成本和查询计划。
  7. 变更后用相同参数再次执行,观察扫描行数、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

相关推荐