Linux服务器MySQL慢查询如何定位,先看执行计划还是索引问题
线上接口出现 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、合理的 range | ALL 表示全表扫描,但小表全表扫描不一定是问题 |
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;
索引判断通常遵循以下路径:

key为NULL,且type=ALL,表规模较大、过滤条件选择性较高时,优先检查是否缺少匹配索引。key已选中但rows估算很大,检查联合索引列顺序、过滤条件顺序、数据分布和排序需求。EXPLAIN估算行数很小,但EXPLAIN ANALYZE实际行数很大,优先怀疑统计信息过期、数据倾斜或参数分布变化。possible_keys有多个候选但key选择不稳定,检查不同参数的选择性,不要只根据一条 SQL 的结果强制指定索引。- 出现
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



