日本服务器上的MySQL出现慢查询,如何从索引、锁等待和资源指标定位原因?

发布人:A5数据发布时间:2026-11-10 13:54阅读量:81

日本服务器上的 MySQL 出现慢查询时,不能先认定为“缺少索引”。同一条请求的耗时可能来自应用连接池排队、跨地域网络往返、MySQL 等待行锁、磁盘 I/O、内存不足,或者执行计划扫描了过多数据。正确做法是先把请求耗时拆开,再按“应用与网络 → 主机资源 → MySQL 等待 → 执行计划与索引”的顺序缩小范围。

运维排查时,应优先采集只读指标和现场信息,避免一上来重建索引、调整连接数或终止数据库会话。下面以 Linux 服务器、MySQL 5.7/8.0、InnoDB 表为例,说明如何确认现象、判断分支、实施修复并完成复测。示例中的 SQL、日志和指标均用于说明判断路径,不代表已在某个实际环境中执行。

一、先确认慢在请求链路的哪一层

1. 区分网络耗时、连接池等待和数据库执行耗时

先从应用链路追踪或访问日志中记录以下时间点:

  • 请求进入应用的时间;
  • 从连接池获取 MySQL 连接的等待时间;
  • 执行 SQL 的开始和结束时间;
  • 结果返回应用的时间;
  • 应用向客户端返回响应的时间。

如果总请求耗时为 2 秒,但 MySQL 执行只占 100 毫秒,继续优化索引通常不能解决问题。此时应检查连接池是否耗尽、应用线程是否排队,以及应用服务器和日本服务器之间是否存在较高网络往返延迟。

如果应用与数据库不在同一地域,连接建立和多次 SQL 往返会被网络 RTT 放大。尤其是一个请求中连续执行几十条小 SQL 时,即使每条 SQL 本身只需要几毫秒,总耗时也可能明显增加。连接复用、合并查询和减少不必要的往返,往往比单纯增加数据库 CPU 更有效。

一、先确认慢在请求链路的哪一层配图

数据库服务器上可以先确认系统时间和 MySQL 时区,避免应用日志与慢日志无法对应:

timedatectl
date -Is
SHOW VARIABLES WHERE Variable_name IN ('system_time_zone', 'time_zone');

日本服务器通常使用 JST 或基于 UTC 的系统配置,但具体设置取决于镜像和运维规范。不要仅凭日志中的时间判断同一事件,应统一时区后再比对请求 ID、线程 ID 和 SQL 时间。

2. 确认慢查询日志和监控口径

先查看当前慢查询配置:

SHOW GLOBAL VARIABLES
WHERE Variable_name IN (
  'slow_query_log',
  'slow_query_log_file',
  'long_query_time',
  'log_queries_not_using_indexes',
  'min_examined_row_limit'
);

重点关注以下情况:

  • slow_query_log 为 OFF:当前没有持续记录慢查询;
  • long_query_time 过高:短于该阈值的慢查询不会进入日志;
  • log_queries_not_using_indexes 为 ON:可能产生大量日志,不能把“不使用索引”等同于“查询一定有问题”;
  • slow_query_log_file 指向空间较小的磁盘:开启日志前需要确认磁盘余量。

临时采集时,可以在业务低峰期开启慢日志并设置合理阈值。例如将 1 秒作为示例阈值:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

这类设置通常只影响当前实例运行状态,重启后是否保留取决于配置文件和版本。开启前应记录原值,采集结束后按原值恢复。慢日志会增加磁盘写入,不能长期无上限开启。

查看日志文件大小和磁盘空间:

df -h
du -sh /var/lib/mysql/* 2>/dev/null | sort -h | tail

如果 df -h 显示数据库所在分区接近满载,应先处理日志轮转和磁盘空间问题。磁盘满不仅会让查询变慢,还可能导致事务提交失败。

二、低风险采集主机和 MySQL 资源指标

确认问题确实与数据库执行有关后,先采集资源指标。不要先修改 max_connections、缓冲池或内核参数,因为资源指标能够帮助区分“CPU 计算不足”和“磁盘或锁等待”。

1. 查看 CPU、内存和磁盘 I/O

在 Linux 主机上执行:

uptime
vmstat 1 5
free -h
iostat -xz 1 5

如果系统未安装 iostat,先确认发行版和软件包管理器,不要直接猜测安装命令:

cat /etc/os-release
command -v iostat

指标可以按以下方式理解:

指标现象更可能的原因下一步
vmstat 的 r 长期高于 CPU 逻辑核数,us 较高查询计算、排序、全表扫描或其他进程消耗 CPU对照 SQL 的扫描行数和排序情况
wa 持续较高,iostat 中 await、%util 明显升高磁盘读写拥堵、临时表落盘或缓冲池命中不足检查数据盘、临时目录和缓冲池指标
free -h 中可用内存很低,同时出现 swap 使用内存压力,可能导致数据库和系统频繁换页先确认是否有其他进程抢占内存,避免盲目增加连接数
CPU、I/O 都正常,但请求仍慢锁等待、网络延迟、连接池排队或执行计划问题继续查看进程列表、锁和执行计划
磁盘空间或 inode 不足日志、临时文件或数据文件无法正常写入先清理可安全清理的文件并确认备份,再处理 SQL

iostat 中 %util 接近 100%只能说明设备繁忙,不能单独证明磁盘已经成为唯一瓶颈。还需要结合 await、读写吞吐量和 MySQL 的临时表、缓冲池指标判断。

2. 查看连接数和正在运行的线程

MySQL 层面先采集连接和线程状态:

SHOW GLOBAL STATUS
WHERE Variable_name IN (
  'Threads_connected',
  'Threads_running',
  'Max_used_connections',
  'Connections',
  'Aborted_connects',
  'Created_tmp_tables',
  'Created_tmp_disk_tables',
  'Sort_merge_passes'
);

判断重点如下:

  • Threads_connected 高,Threads_running 低:连接很多,但不一定有大量 SQL 在执行,可能是连接池保留连接过多;
  • Threads_running 持续高:可能是高并发、慢查询、锁等待或资源不足;
  • Max_used_connections 接近 max_connections:存在连接上限风险,但不能直接通过提高上限解决;
  • Created_tmp_disk_tables 增长较快:排序、分组或临时结果可能落盘;
  • Sort_merge_passes 持续增加:排序内存不足或排序数据量较大,需要结合具体 SQL 分析。

应用连接池耗尽和 MySQL 连接数过高是两个不同问题。应用可能在等待连接池,而 MySQL 端的 Threads_connected 并不高;也可能连接池配置过大,导致大量空闲连接占用内存。调整连接池前,应同时查看应用侧的 active、idle、等待队列和获取连接超时。

3. 观察缓冲池命中情况

如果使用 InnoDB,可以查看缓冲池相关状态:

SELECT
  MAX(CASE WHEN VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
           THEN VARIABLE_VALUE + 0 END) AS logical_reads,
  MAX(CASE WHEN VARIABLE_NAME = 'Innodb_buffer_pool_reads'
           THEN VARIABLE_VALUE + 0 END) AS physical_reads,
  ROUND(
    100 * (
      1 - MAX(CASE WHEN VARIABLE_NAME = 'Innodb_buffer_pool_reads'
                   THEN VARIABLE_VALUE + 0 END)
      / NULLIF(
          MAX(CASE WHEN VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
                   THEN VARIABLE_VALUE + 0 END),
          0
        )
    ),
    2
  ) AS hit_ratio_percent
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN (
  'Innodb_buffer_pool_read_requests',
  'Innodb_buffer_pool_reads'
);

如果 performance_schema.global_status 不可用,可以使用以下方式分别查看:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';

缓冲池命中率只能作为参考。命中率下降可能是内存不足、工作集扩大、刚重启导致缓存变冷,也可能是某条 SQL 突然扫描大量数据。不能只因为命中率低就直接增加 innodb_buffer_pool_size,还要确认主机可用内存、实例数量以及是否存在 swap。

三、判断是否为锁等待,而不是索引问题

锁等待是最容易被误判为“SQL 执行慢”的情况。查询本身可能只需要几十毫秒,但因为等待其他事务提交,最终耗时达到数秒甚至更长。

1. 查看当前会话状态

先执行:

SHOW FULL PROCESSLIST;

重点观察 State 字段中是否出现类似状态:

  • Waiting for row lock;
  • Waiting for table metadata lock;
  • Waiting for table flush;
  • 长时间处于 Sleep,但对应事务仍未结束;
  • SQL 文本为空,但连接持有未提交事务。

SHOW FULL PROCESSLIST 需要足够权限。生产环境中不要只看一眼就执行 KILL,应先记录线程 ID、用户、来源地址、数据库、当前 SQL 和事务开始时间。

2. 查看长事务和 InnoDB 锁等待

查看当前事务:

SELECT
  trx_id,
  trx_state,
  trx_started,
  trx_wait_started,
  trx_mysql_thread_id,
  trx_query,
  trx_tables_locked,
  trx_rows_locked,
  trx_rows_modified
FROM information_schema.innodb_trx
ORDER BY trx_started;

如果实例安装了 sys Schema,可以进一步查看行锁等待关系:

SELECT *
FROM sys.innodb_lock_waits
ORDER BY waiting_trx_started
\G

查看元数据锁等待:

SELECT *
FROM sys.schema_table_lock_waits
\G

如果上述视图不存在,应先确认 MySQL 版本和 sys Schema 是否安装,不要直接把视图定义复制到生产环境。也可以查看 InnoDB 状态摘要:

SHOW ENGINE INNODB STATUS\G

不同结果对应不同处理路径:

三、判断是否为锁等待,而不是索引问题配图

  • 存在明确的阻塞事务,且阻塞事务持续时间很长:检查应用是否忘记提交或回滚;
  • 阻塞事务是批量更新或批量删除:确认批次大小、执行时段和锁覆盖范围;
  • 出现元数据锁等待:重点检查是否有长事务占用表,或有人正在执行 DDL;
  • 没有锁等待,但 SQL 仍慢:转向执行计划、资源和网络方向;
  • 出现死锁:查看 InnoDB 状态中的 LATEST DETECTED DEADLOCK,重点修正事务访问表和行的顺序,而不是简单提高锁等待超时。

3. 终止阻塞会话前的风险控制

在确认阻塞会话可以终止、并获得业务负责人授权后,才考虑:

KILL 12345;

这里的 12345 只是示例线程 ID,不能照抄。终止连接会触发事务回滚,回滚时间可能比原事务执行时间更长,期间仍可能继续持有部分资源。正在执行核心订单、支付、库存或结构变更的会话,不应仅凭“运行时间长”就终止。

终止前应完成以下确认:

  • 记录线程 ID、SQL、事务开始时间和业务来源;
  • 确认该会话确实是阻塞源,而不是被阻塞者;
  • 确认应用具备重试或失败处理逻辑;
  • 确认终止操作不会造成批处理重复执行;
  • 准备观察回滚产生的磁盘 I/O 和锁释放情况。

长期修复通常包括缩短事务范围、确保异常路径执行回滚、避免事务中进行外部网络调用、减少批量更新的单批行数,并让不同事务按一致顺序访问资源。直接提高 innodb_lock_wait_timeout 只能延长等待时间,不能消除阻塞关系。

四、通过执行计划判断索引是否真正有效

1. 获取完整 SQL 和实际参数

慢日志中的 SQL、应用链路中的 SQL 模板和数据库现场执行的 SQL 可能不完全一致。排查时要保留:

  • 完整 SQL;
  • 实际参数类型和典型参数值;
  • 执行时间;
  • 返回行数;
  • 扫描行数;
  • 是否在事务中执行;
  • 执行时是否出现锁等待。

参数分布不均时,同一个 SQL 模板可能对应不同执行计划。例如某个租户只有少量数据,另一个租户占据大部分表数据,优化器对两组参数的选择可能不同。

查看表结构和索引:

SHOW CREATE TABLE orders\G
SHOW INDEX FROM orders;

对只读查询先执行:

EXPLAIN
SELECT id, user_id, status, created_at
FROM orders
WHERE tenant_id = 1001
  AND status = 'paid'
  AND created_at >= '2025-01-01 00:00:00'
ORDER BY created_at DESC
LIMIT 50;

MySQL 8.0.18 及更高版本可以在测试环境或低风险场景下使用:

EXPLAIN ANALYZE
SELECT id, user_id, status, created_at
FROM orders
WHERE tenant_id = 1001
  AND status = 'paid'
  AND created_at >= '2025-01-01 00:00:00'
ORDER BY created_at DESC
LIMIT 50;

EXPLAIN ANALYZE 会实际执行查询,可能读取大量数据。不要对生产环境中的高负载查询直接使用,也不要对包含写入行为的语句随意执行。涉及 UPDATE、DELETE 或复杂存储过程时,应先在脱敏测试库验证。

2. 重点看哪些执行计划字段

常用字段可以按以下方式判断:

  • type 为 ALL:通常表示全表扫描,但小表全表扫描不一定是问题;
  • key 为空:没有使用索引,需结合过滤条件和表规模判断;
  • rows 很大:优化器估算需要检查的行数较多;
  • filtered 较低:扫描了很多行,最终只有少量数据符合条件;
  • Extra 出现 Using filesort:排序没有完全利用索引,不代表一定需要加索引;
  • Extra 出现 Using temporary:可能需要临时表,需结合 GROUP BY、DISTINCT 或排序逻辑分析;
  • possible_keys 有值但 key 为空:存在候选索引,但优化器认为使用它的代价更高。

索引设计应与查询条件和排序方式匹配。对于示例查询,tenant_id 和 status 是等值条件,created_at 是范围条件并参与排序,可能需要评估类似以下组合索引:

(tenant_id, status, created_at)

但这只是候选方案,不是可以直接执行的固定答案。最终顺序还要结合数据分布、其他查询、索引大小和写入成本。索引列越多,读查询可能受益,但插入、更新、删除需要维护的索引页也越多。

四、通过执行计划判断索引是否真正有效配图

以下写法经常让已有索引失效或效果下降:

WHERE DATE(created_at) = '2025-01-01'
WHERE name LIKE '%keyword%'
WHERE user_id = '1001'

如果 user_id 是数值列却长期以字符串传入,可能发生隐式类型转换。更合适的做法通常是改为范围查询、调整参数类型,或根据实际检索需求选择其他索引结构。是否失效必须通过 EXPLAIN 验证,不能只凭 SQL 文字判断。

3. 更新统计信息前要记录原计划

表数据分布发生明显变化后,优化器统计信息可能过期。可以在业务低峰期评估:

ANALYZE TABLE orders;

该操作不是删除数据,但会更新统计信息,并可能产生 I/O 或短暂的元数据影响。执行前应记录原执行计划和当前负载。统计信息更新没有像普通事务那样简单的回滚按钮,如果执行后计划变差,应立即对比新旧计划,暂停继续变更,并通过更准确的索引设计、参数验证或版本支持的优化器策略处理。

五、按结果分支定位根因

当现场数据采集完成后,可以用下面的分支快速归类:

主要证据更可能的根因优先处理方向
应用等待连接时间高,MySQL 执行时间低连接池过小、连接未及时归还、应用线程排队检查连接池 active/idle、超时和连接泄漏
SHOW PROCESSLIST 长时间显示等待行锁长事务、批量更新或事务提交异常找出阻塞事务,修正事务边界和批次
iostat 的 I/O 等待明显,扫描行数很高全表扫描、缓存命中不足、临时结果落盘优先优化 SQL 和索引,再评估存储与内存
CPU 使用率高,rows examined 远大于返回行数过滤条件缺少有效索引、排序或函数计算开销大查看 EXPLAIN,减少扫描和排序
执行计划良好、资源正常,但跨地域请求慢网络 RTT、连接新建过多、往返次数多使用连接复用,减少 SQL 往返,检查链路
Threads_connected 高但 Threads_running 低空闲连接过多,不一定是数据库执行瓶颈调整连接池保留连接数,不要直接提高上限
缓冲池命中下降且发生 swap内存不足或实例配置超过主机承载能力先控制连接和其他进程内存,再评估缓冲池

日本服务器场景还要特别注意“服务器内执行慢”和“访问服务器的请求慢”不是同一件事。可以在应用日志中分别记录 SQL 执行耗时和完整请求耗时,再从应用所在网络位置进行对比。如果数据库内部耗时稳定,而跨地域请求的总耗时波动明显,索引通常不是首要矛盾。

六、按照风险顺序实施修复

1. 优先修正查询和事务行为

风险相对较低的调整包括:

  • 避免在过滤列上使用不必要的函数;
  • 使用正确的参数类型;
  • 减少一次请求中的 SQL 往返次数;
  • 对大分页改用基于索引字段的范围分页;
  • 缩短事务持续时间;
  • 确保成功、异常和超时路径都能提交或回滚;
  • 将大批量写入拆分为可观察、可中断的小批次。

修改应用前应先在测试环境使用接近生产的数据量和参数分布验证。不能只用几百行测试数据判断索引有效,因为小表上全表扫描可能比索引访问更快。

2. 新增索引前确认备份、空间和回滚方式

索引属于结构变更。执行前需要确认:

  • 已有可恢复的备份或快照;
  • 表数据量、索引大小和磁盘余量足够;
  • 已在测试环境验证执行计划;
  • 明确业务低峰期和变更窗口;
  • 评估 DDL 对写入、复制延迟和磁盘 I/O 的影响;
  • 新索引名称不会与现有索引冲突。

示例 DDL 如下,仅用于说明形式:

ALTER TABLE orders
  ADD INDEX idx_orders_tenant_status_created (tenant_id, status, created_at),
  ALGORITHM=INPLACE,
  LOCK=NONE;

ALGORITHM=INPLACE 和 LOCK=NONE 是否适用于目标表、存储引擎和 MySQL 版本,需要先在测试环境确认。LOCK=NONE 不是所有场景都能实现无阻塞,服务器不支持时可能报错,不能把它理解为对业务完全没有影响。

如果新增索引确认无效,需要删除时,回滚操作同样可能产生 I/O 和锁影响:

ALTER TABLE orders
  DROP INDEX idx_orders_tenant_status_created;

不要在刚创建后发现一次查询未使用,就立即删除索引。应先确认没有其他 SQL 依赖它,并保留变更记录、执行计划和监控结果。删除索引前同样要安排窗口和回滚预案。

3. 最后再调整资源和连接参数

只有在确认资源是瓶颈后,才评估以下方向:

  • 适当调整 InnoDB 缓冲池大小;
  • 将临时文件目录放到性能和空间合适的磁盘;
  • 限制单个应用实例的连接池上限;
  • 拆分高峰批处理;
  • 优化存储设备或扩展主机资源。

增加 max_connections 之前,必须估算每个连接的内存开销,并确认主机不会因并发连接增加而触发 OOM。提高连接上限可能让数据库从“连接拒绝”变成“内存耗尽”,不能作为默认修复方案。

动态修改配置前应记录当前值、变更时间和操作者。需要重启才能生效的配置,应先确认复制、故障切换和业务维护流程。所有资源调整都应一次只改一个主要变量,方便判断结果和回退。

围绕日本服务器上的 MySQL 业务,A5idc数据提供采用 Xeon Gold、AMD EPYC 等平台的日本物理服务器,配有 NVMe 存储的产品,也有 SSD 和大容量 HDD 等不同存储方案。这些计算与存储资源可为数据库运行、数据存放及多任务业务提供部署基础;日本产品的不同线路和带宽选项,则可对应本地访问与跨境业务的网络需求。

七、复测时同时看耗时、计划和副作用

修复不能只通过一次手工执行来判断。应在变更前后采集同一组指标:

对比项目关注内容
请求耗时平均值、P95、P99,以及连接池等待和 SQL 执行耗时
SQL 执行计划key、rows、filtered、排序和临时表情况
扫描与返回Rows_examined 是否下降,返回行数是否符合预期
锁指标锁等待次数、等待时长、长事务数量
主机资源CPU、I/O wait、磁盘延迟、内存和 swap
MySQL 并发Threads_running、连接池等待、最大连接使用量
业务结果返回数据、写入数量、重复执行和错误率

例如,某个查询在测试中的假设结果可能是:rows 从约 120 万降到约 8 万,P95 从约 2.8 秒降到约 220 毫秒,同时 Created_tmp_disk_tables 不再快速增长。这只能说明该示例中的优化方向有效,不能替代目标环境的实际验证。

复测应覆盖缓存较热和缓存较冷的情况,也要覆盖业务高峰。对于日本服务器,还要分别观察数据库本机执行耗时和应用端完整请求耗时。如果数据库耗时已稳定下降,但端到端 P95 没有改善,应回到网络、连接复用和应用线程排队方向排查。

变更完成后不要立即关闭监控。至少持续观察慢日志、锁等待、复制延迟、磁盘空间、CPU、I/O 和连接数,并保留变更前后的执行计划。只有当查询性能改善、业务结果正常、资源没有转移成新的瓶颈,且高峰时段仍保持稳定,才可以把本次修复视为完成。

本文由A5数据原创发布,转载请注明出处:https://www.a5idc.com/helpcontent/8688.html

相关推荐