Linux服务器MySQL慢查询如何定位,先看执行计划还是索引问题

发布人:A5数据发布时间:2026-11-08 11:55阅读量:69

线上接口出现 P95 延迟升高、页面偶发超时,Linux 服务器上的 mysqld CPU 或磁盘 I/O 同时上升时,不应直接把问题归结为“缺少索引”。正确顺序通常是先确认慢的是数据库执行、连接排队、锁等待还是应用侧耗时,再对具体 SQL 查看执行计划,最后结合表结构和数据分布判断是否需要调整索引。

正文开头配图

如果已经拿到明确的慢 SQL,优先看执行计划,而不是先创建索引。执行计划能说明优化器实际选择了什么访问路径;索引定义只能说明“有哪些候选路径”。不过,当执行计划显示 SQL 长时间处于锁等待,或者请求根本还没拿到数据库连接时,索引并不是第一排查对象。

面向 MySQL 数据库及业务后台的部署,A5idc数据提供中国香港、美国、日本等地区的物理服务器,涵盖 Xeon Gold、AMD EPYC 等平台,并有配备较大内存与 SSD、NVMe 存储的方案,可为数据库运行和多任务业务提供硬件资源基础。不同地区的线路与配置各有侧重,也能满足不同访问区域和数据规模下的部署需求。

先确认慢查询的真实位置

运维排查先从现象和时间范围开始。记录接口开始变慢的时间、影响范围、是否集中在某个租户或参数,以及慢请求是持续发生还是周期性出现。数据库中的执行时间与应用观察到的接口时间并不相同,一次请求的总耗时可能包含以下部分:

  • 应用线程等待连接池可用连接的时间;
  • 数据库建立连接或认证的时间;
  • SQL 等待行锁、表锁或元数据锁的时间;
  • SQL 真正执行和磁盘读写的时间;
  • 数据返回到应用后的网络传输和业务处理时间。

可以先在 Linux 服务器上进行只读观察。下面的命令适用于常见的 systemd 环境,iostat 和 pidstat 如果未安装,只需先确认是否有对应工具,不要为了排查直接修改生产环境软件源。

date
uptime
free -h
vmstat 1 5
iostat -xz 1 5
pidstat -dru 1 5

重点关注以下现象:

观察结果更可能的方向对索引判断的影响
r 持续高于 CPU 核数,CPU 使用率接近满载CPU 计算、全表扫描、排序或并发过高需要继续看执行计划,不能直接认定缺索引
await 明显升高,设备 %util 接近满载磁盘 I/O、缓冲池未命中或大范围扫描索引可能减少读盘,但需区分数据冷缓存和索引设计
内存紧张并伴随 swap 增长内存不足、连接过多或缓冲区配置过大先处理资源和连接问题,盲目加索引可能进一步占用磁盘和内存
Linux 负载不高,但应用请求仍慢锁等待、连接池排队、网络或应用处理耗时执行计划可能不是首要问题
只有某类参数慢,其他参数正常数据分布倾斜、参数选择性不同或执行计划不稳定需要结合实际参数和统计信息分析

确认 MySQL 服务名和版本,避免把 mysql、mysqld、MariaDB 的配置路径混为一谈:

systemctl list-units --type=service | grep -Ei 'mysql|mariadb'
mysql -NBe "SELECT VERSION();"

执行 mysql 命令时建议使用已有的客户端配置文件或安全凭据管理方式,不要把数据库密码直接写在命令行中。

先排除连接排队和锁等待

进入 MySQL 后,先观察连接与线程状态:

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

SHOW VARIABLES
WHERE Variable_name IN
(
  'max_connections',
  'thread_cache_size',
  'wait_timeout',
  'interactive_timeout'
);

SHOW FULL PROCESSLIST;

这些指标需要连续采样,单次结果只能反映一个瞬间。可以每隔几秒执行一次,观察 Threads_running 是否长期接近 max_connections,以及是否存在大量处于 Sleep、Locked 或长时间执行状态的连接。

判断时不要把 Threads_connected 高等同于数据库已经超载:

  • Threads_connected 高但 Threads_running 低,常见于连接池保留了较多空闲连接,未必是 SQL 执行慢;
  • Threads_running 高且 CPU、磁盘同时繁忙,可能是并发执行、全表扫描或排序造成资源争用;
  • Threads_running 不高,但某些线程的 Time 很大并处于锁等待状态,应先定位阻塞事务;
  • Aborted_connects 增加,说明连接建立、认证或网络存在异常,不能只通过加索引解决。

如果安装并启用了 sys schema,可以使用以下视图查看 InnoDB 锁等待:

SELECT *
FROM sys.innodb_lock_waits\G

如果视图不存在,先确认 MySQL 版本和 sys schema 是否可用。MySQL 8.0 也可以从 Performance Schema 的数据锁表中排查,但不同小版本字段可能存在差异,建议先查看表结构:

SHOW TABLES FROM performance_schema LIKE '%lock%';

若发现一个事务长时间持有锁,优先确认事务所属应用、事务开始时间和业务影响。紧急情况下可以使用 KILL QUERY <线程ID> 中止当前语句,但这可能导致语句失败;如果线程正在事务中,后续回滚也可能继续占用资源。不要未经确认直接使用 KILL CONNECTION,更不要批量杀掉所有长连接。执行前应记录线程 ID、SQL、所属账号和影响范围,并准备好应用重试或回滚方案。

定位具体的慢 SQL

通过慢查询日志确认原始语句

先查看当前慢查询配置:

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

如果慢查询日志已经开启,可以读取变量中显示的路径:

sudo tail -n 100 /path/from/slow_query_log_file

也可以使用 MySQL 自带工具按总耗时排序:

mysqldumpslow -s t -t 20 /path/from/slow_query_log_file

long_query_time 的单位是秒。例如设置为 1,表示执行时间达到 1 秒的语句可能被记录。生产环境临时降低阈值前,要先确认磁盘空间和日志轮转策略,否则高并发系统可能快速产生大量日志。

如果现有配置无法覆盖故障时段,可以在确认磁盘容量后临时调整:

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

SET GLOBAL 通常只影响新建会话,已存在连接的会话变量可能仍保持原值。临时调整结束后,可以按原值恢复:

SET GLOBAL slow_query_log = OFF;

如果原来慢查询日志就是开启状态,不要简单关闭,而应恢复到排查前的原始值。需要持久化到配置文件时,先备份配置,再在维护窗口修改,并确认不同发行版的配置文件路径。log_queries_not_using_indexes 不建议长期打开,它会把大量本来合理的查询也记录下来,造成日志噪声;是否使用要结合数据量和业务负载判断。

通过 Performance Schema 查看聚合结果

慢查询日志适合获取原始 SQL,Performance Schema 适合发现哪个 SQL 模板消耗了最多总时间。以下查询以 MySQL 8.0 常见表结构为例:

SELECT
    DIGEST_TEXT,
    COUNT_STAR AS exec_count,
    ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_ms,
    ROUND(MAX_TIMER_WAIT / 1000000000, 2) AS max_ms,
    ROUND(SUM_TIMER_WAIT / 1000000000, 2) AS total_ms,
    SUM_ROWS_EXAMINED AS rows_examined,
    SUM_ROWS_SENT AS rows_sent
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

这里的时间字段以皮秒计,除以 1,000,000,000 后约为毫秒。结果需要区分三个概念:

  • avg_ms 高,说明单次执行普遍慢;
  • total_ms 高,可能是单次不算特别慢,但调用次数非常多;
  • rows_examined 远高于 rows_sent,说明数据库读取了大量不需要返回的行,值得重点查看过滤条件、索引和分页方式。

Performance Schema 的统计通常会受到重启、配置或统计清理影响,因此不要把一次查询结果当成完整历史。排查时应记录采样时间,并与应用监控中的请求量、错误率和连接池等待时间对照。

已知 SQL 时,先看执行计划

拿到具体 SQL 和一组具有代表性的参数后,先执行普通 EXPLAIN:

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

需要使用与生产相近的数据分布和参数。只用一组返回很少数据的参数,可能掩盖某些参数下的慢查询。

重点观察这些列:

列关注点常见异常含义
type访问类型是否为 const、eq_ref、ref、合理的 rangeALL 表示全表扫描,但小表全表扫描不一定是问题
possible_keys优化器认为可能使用哪些索引为空可能是没有合适索引,也可能是条件无法利用索引
key实际选中的索引为 NULL 说明本次没有使用索引
key_len实际使用了联合索引的多少部分小于预期时,可能只利用了联合索引的前缀
rows优化器估算需要读取的行数估算很大时,要结合表总量和实际返回量判断
filtered读取后预计通过过滤的比例很低且 rows 很大,通常意味着过滤发生得太晚
Extra是否出现临时表、文件排序或回表等信息Using filesort、Using temporary 不是单独的故障结论

例如,以下是一个用于说明判断路径的示例结果,并非某个实际环境的执行结果:

table   type   possible_keys                         key                         rows    filtered   Extra
orders  range  idx_tenant_status_created             idx_tenant_status_created   185000  12.50      Using index condition

这个结果表示优化器选择了范围扫描,但仍估计需要读取约 18.5 万行,之后只有约 12.5% 的行通过过滤。此时不能因为“已经使用索引”就认为索引没有问题,也不能因为 rows 较大就立即再建一个索引。还需要确认表总行数、参数对应的数据量、实际执行时间以及排序是否能利用索引。

如果使用 MySQL 8.0.18 或更高版本,可以在低峰期或测试环境执行 EXPLAIN ANALYZE,观察真实执行行数和各步骤耗时:

EXPLAIN ANALYZE
SELECT id, status, created_at
FROM app.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 会实际执行查询,不是纯估算。即使是 SELECT,也可能读取大量数据、持有一致性读资源或放大生产负载。对于大表和高峰期请求,不建议直接在主库执行;可以使用结构和数据分布接近的测试库或只读副本。若版本不支持,应使用普通 EXPLAIN、慢查询日志和 Performance Schema 进行对比。

再检查索引定义和数据分布

执行计划确认了访问路径后,再检查表结构和索引:

SHOW CREATE TABLE app.orders\G

SHOW INDEX FROM app.orders;

也可以查看索引基数、顺序和列信息:

SELECT
    INDEX_NAME,
    SEQ_IN_INDEX,
    COLUMN_NAME,
    NON_UNIQUE,
    CARDINALITY,
    INDEX_TYPE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'app'
  AND TABLE_NAME = 'orders'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

索引判断通常遵循以下路径:

再检查索引定义和数据分布配图

  1. key 为 NULL,且 type=ALL,表规模较大、过滤条件选择性较高时,优先检查是否缺少匹配索引。
  2. key 已选中但 rows 估算很大,检查联合索引列顺序、过滤条件顺序、数据分布和排序需求。
  3. EXPLAIN 估算行数很小,但 EXPLAIN ANALYZE 实际行数很大,优先怀疑统计信息过期、数据倾斜或参数分布变化。
  4. possible_keys 有多个候选但 key 选择不稳定,检查不同参数的选择性,不要只根据一条 SQL 的结果强制指定索引。
  5. 出现 Using filesort 或 Using temporary,先确认排序和分组的数据量。小结果集出现这些信息不一定需要调整索引。

常见的索引失效或效果不佳原因包括:

  • 对索引列使用函数或表达式,例如 DATE(created_at)=...;
  • 对数字列传入字符串,触发隐式类型转换;
  • 使用 LIKE '%keyword',无法利用普通 B-Tree 索引的前缀;
  • 联合索引没有遵循最左匹配原则;
  • 条件中的范围列过早出现,使后续列难以继续用于过滤或排序;
  • OR 两侧条件差异很大,优化器选择了成本更高的路径;
  • 统计信息不能反映最新的数据分布;
  • 返回列过多,虽然过滤利用了索引,但仍需要大量回表读取。

联合索引不是把所有查询字段简单拼在一起。比如查询固定使用 tenant_id 和 status 过滤,并按 created_at 做范围筛选和排序,可以评估 (tenant_id, status, created_at) 是否更匹配;但如果排序字段与范围字段不同,是否能够同时满足过滤和排序,要以执行计划和实际数据量验证,不能仅凭列名顺序下结论。

结果分支:索引不是唯一答案

执行计划异常,且资源主要消耗在扫描

如果查询在大表上使用 ALL,读取行数远大于返回行数,且 CPU 或磁盘读明显升高,可以从查询改写和索引设计两方面处理。

优先采用低风险的查询改写,例如避免对索引列做函数运算、减少无意义字段、使用基于索引列的分页。应用代码改动建议通过灰度或功能开关发布,保留原 SQL 的回退路径,并对新旧 SQL 使用同一组参数进行对比。

确认索引确实缺失后,再考虑新增索引。新增前检查磁盘空间、表大小、写入峰值和复制延迟:

SHOW TABLE STATUS FROM app LIKE 'orders'\G
SHOW VARIABLES LIKE 'datadir';
SHOW REPLICA STATUS\G

不同 MySQL 版本、存储引擎和表结构对在线 DDL 的支持不同。确认版本和 InnoDB 条件后,才可以评估类似操作:

ALTER TABLE app.orders
  ADD INDEX idx_orders_tenant_status_created
  (tenant_id, status, created_at);

LOCK=NONE 不是“绝对不阻塞”。在线创建索引仍可能消耗大量 CPU、磁盘和 I/O,也可能在开始或提交阶段等待元数据锁。执行前需要安排维护窗口或限流,确认备份可用,并监控复制延迟和业务错误率。

新增索引的回滚只针对明确新增、且没有业务依赖的索引:

ALTER TABLE app.orders
  DROP INDEX idx_orders_tenant_status_created;

删除索引同样属于 DDL,可能耗时并影响资源。不要把已有索引直接删除来“试效果”,尤其要先确认 ORM、SQL Hint、报表和其他业务是否依赖它。

执行计划合理,但实际耗时仍高

如果执行计划显示索引访问、实际读取行数也不多,而应用仍然慢,继续检查以下分支:

  • SQL 在等待行锁或元数据锁;
  • 应用连接池已耗尽,请求排队在数据库之外;
  • 数据库返回结果较大,网络或应用序列化耗时;
  • 机器磁盘延迟高,导致少量随机读也变慢;
  • 数据库 CPU 被其他 SQL、后台任务或排序操作占用。

连接池侧应查看活动连接数、空闲连接数、等待连接数、获取连接耗时和连接超时次数。不要只看到数据库连接数高就直接调大 max_connections。连接上限增大后,每个连接可能带来线程、排序缓冲区和临时表内存开销,在内存不足时反而可能触发 swap。

估算与实际差异很大

当 EXPLAIN 估算读取几百行,而 EXPLAIN ANALYZE 实际读取几十万行时,可以考虑统计信息问题。确认没有长事务和高峰 DDL 后,再评估:

ANALYZE TABLE app.orders;

这个操作不会删除业务行,但会更新优化器统计信息,执行后可能改变多个 SQL 的执行计划,也没有一个通用的“事务回滚”按钮。执行前应记录原有执行计划,低峰期操作,并在完成后重新观察核心 SQL。如果新计划变差,应优先通过查询改写、经过验证的临时优化器策略或应用回退处理,而不是连续反复执行 ANALYZE TABLE。

对于数据分布严重倾斜的列,还可以在确认版本支持后评估直方图,但应只针对确实影响优化器判断的列,并记录变更前后的计划。统计信息调整不应与新增索引、连接池调整同时进行,否则很难判断到底是哪项变更产生了效果。

查询重复执行但数据变化不频繁

缓存可以降低重复查询压力,但它不是索引问题的替代方案。适合缓存的通常是读取频繁、结果变化有明确周期、允许短时间旧数据的查询。引入缓存前要明确:

  • 缓存键是否包含租户、权限和查询条件;
  • 数据更新后如何失效;
  • 缓存击穿、集中过期时是否会同时回源数据库;
  • 缓存未命中时,数据库是否仍能承受峰值流量;
  • 缓存命中率和回源耗时能否单独监控。

如果没有稳定的失效策略,缓存可能掩盖慢查询,最终在缓存集中失效时造成更大的数据库压力。

复测时要同时比较计划和业务指标

修复不要只看一次 EXPLAIN。应针对同一组典型参数,分别记录变更前后的:

  • P50、P95、P99 延迟;
  • SQL 平均耗时和最大耗时;
  • 每次执行的 rows_examined 与 rows_sent;
  • 执行次数和总耗时;
  • CPU、磁盘延迟、缓冲池读请求;
  • 锁等待次数和等待时长;
  • 连接池等待时间、数据库错误率和复制延迟。

例如,新增索引后如果单次 SQL 从约 800 毫秒降到约 80 毫秒,但磁盘写入、复制延迟和其他核心 SQL 明显恶化,就不能只按“这条查询变快了”判定成功。索引占用的磁盘空间、写入维护成本和其他查询的计划变化也属于修复结果的一部分。

复测时还要区分冷缓存和热缓存。刚重启数据库后的第一次查询可能包含磁盘读,连续执行后的结果则更接近热数据场景。建议在测试环境先验证,再通过灰度流量观察生产表现;每次只改变一个主要变量,并保留原执行计划、配置值和 DDL 记录。

如果结果仍异常,回到最初的分层路径:先确认请求是否拿到连接,再确认是否等待锁,随后检查 SQL 的实际执行计划和资源消耗。只有当问题稳定地落在扫描行数、过滤选择性或排序成本上时,才把索引作为主要修复手段。完成修复后继续观察慢查询日志、Performance Schema 聚合数据、Linux 磁盘延迟和连接池等待,避免一次优化后又在新的数据规模和访问模式下重新出现同类问题。

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

相关推荐