香港服务器MySQL查询变慢怎么办?如何从慢查询与索引使用定位瓶颈

发布人:A5数据发布时间:2026-10-23 10:22阅读量:117

不要先把页面响应时间直接归因于香港服务器或 MySQL。一次请求的总耗时可能同时包含网络往返、连接池排队、连接建立、SQL执行、锁等待、结果传输和应用层处理;其中只有一部分属于数据库真正执行查询的时间。判断“查询变慢”的第一步,是把这些时间拆开,而不是看到接口从 100 毫秒变成 500 毫秒就立即添加索引。

更有效的定位路径是:先确定慢的是单条 SQL、数据库整体吞吐,还是连接池与网络;再从慢查询日志或 Performance Schema 找出高耗时、高频率和高扫描量的语句;随后用 EXPLAIN 或 EXPLAIN ANALYZE核对索引是否被使用;最后结合 CPU、内存、磁盘 I/O、锁等待和并发复测。只有当执行计划、资源指标和复测结果相互印证时,才适合修改索引或查询。

先确定测试目标:慢的是 SQL 还是请求

区分四类延迟

建议把一次请求拆成以下几个时间段:

先确定测试目标:慢的是 SQL 还是请求 / 区分四类延迟配图

时间段说明常见问题
网络往返应用服务器与数据库之间的通信耗时应用和数据库不在同一地区、链路抖动
连接池等待请求等待可用数据库连接的时间连接池过小、连接未及时归还
SQL执行MySQL解析、优化、扫描、排序、连接和锁等待缺少索引、执行计划不佳、锁竞争
结果处理结果传输、对象映射和业务逻辑处理返回行数过多、应用序列化耗时

MySQL慢查询日志中的 Query_time 主要反映数据库端查询处理时间,并不等于用户从浏览器感知到的完整接口耗时。相反,应用访问日志中的接口耗时可能包含连接池排队和网络等待。

香港服务器场景尤其要注意应用与数据库的部署位置。如果应用在香港、数据库在其他地区,小查询本身可能只执行 10 毫秒,但网络往返已经增加几十毫秒;这不会导致索引“不生效”,却会抬高接口延迟。可以在应用侧分别记录以下时间:

  • 从请求开始到获取数据库连接的时间;
  • 从发送 SQL 到收到数据库结果的时间;
  • 从收到结果到接口返回的时间。

如果数据库端慢查询记录为 80 毫秒,而应用侧等待数据库结果为 220 毫秒,优先检查网络、连接建立和结果传输,不要直接修改索引。

用分位延迟代替平均值

平均耗时容易掩盖少量严重慢请求。建议至少观察:

  • p50:典型请求耗时;
  • p95:大多数用户能接受的上界;
  • p99:高峰和异常请求的尾部延迟;
  • QPS 或每秒查询量;
  • 并发请求数;
  • 数据库端平均执行时间与最大执行时间。

例如,一组查询平均耗时 60 毫秒,但 p95 为 180 毫秒、p99 为 1.2 秒,通常说明部分参数触发了不同执行计划、锁等待或磁盘读取。此时只看平均值,容易误判为“整体性能还可以”。

建立基线:先知道什么叫变慢

慢查询阈值不是性能目标

long_query_time 只是记录门槛,不代表所有低于这个值的查询都合理。例如,一个每秒执行 2000 次、每次 80 毫秒的查询,可能比一个每分钟执行一次、耗时 2 秒的后台查询更值得优化。

可以先在低风险时段临时收集数据。以下示例适用于具备相应权限的 MySQL 环境:

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;
SET GLOBAL min_examined_row_limit = 1000;

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

这组设置表示记录执行时间达到 0.5 秒,或扫描行数达到一定规模的查询。不同版本、托管环境和权限策略可能不允许动态修改;重启后是否保留也取决于配置方式。不要在高流量环境长期把阈值设得过低,否则日志量会快速增加,反过来消耗磁盘 I/O。

log_queries_not_using_indexes 不建议一开始就打开。很多合法查询并不需要索引,例如小表全表扫描、低选择性字段过滤和统计任务。应该先根据 Query_time、Rows_examined 和执行次数筛选,再针对具体语句分析。

用 Performance Schema 找高成本语句

如果已启用 Performance Schema,可以按累计耗时查看规范化后的 SQL。下面是一个参考查询:

SELECT
    SCHEMA_NAME,
    DIGEST_TEXT,
    COUNT_STAR,
    ROUND(SUM_TIMER_WAIT / 1000000000000, 2) AS total_seconds,
    ROUND(AVG_TIMER_WAIT / 1000000, 2) AS avg_milliseconds,
    SUM_ROWS_EXAMINED,
    SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

这里的换算需要注意单位:

  • TIMER_WAIT 的基础单位通常是皮秒;
  • 总耗时除以 1,000,000,000,000 得到秒;
  • 平均耗时除以 1,000,000 得到毫秒;
  • SUM_ROWS_EXAMINED 是累计扫描行数,不是返回给应用的行数。

优先关注三类语句:

  1. 单次平均耗时高,且执行频率不低;
  2. 单次扫描行数远高于返回行数;
  3. 单次耗时不高,但调用次数极多,累计耗时排名靠前。

例如,某条查询每次执行 120 毫秒,每秒执行 30 次,数据库每秒要承担约 3.6 秒的累计执行时间;另一条查询每次执行 2 秒,但每天只运行几次,优化优先级可能反而较低。

从慢查询日志判断瓶颈位置

慢查询日志中常见的字段包括:

  • Query_time:查询整体耗时;
  • Lock_time:部分锁等待信息;
  • Rows_sent:返回行数;
  • Rows_examined:扫描或检查的行数;
  • SQL文本及执行时间。

可以用“扫描行数与返回行数的关系”进行初筛,但不要把某个固定比例当成绝对标准。

观察结果可能原因下一步
Rows_examined 很高,Rows_sent 很低过滤条件没有有效索引,或索引选择性不足检查 EXPLAIN 和联合索引顺序
两者都很高查询本身返回数据量大,或分页方式不合理限制列和行数,检查深分页
行数不高但 Query_time 高锁等待、磁盘延迟、排序或临时表查看锁、I/O和执行计划
SQL耗时低但接口耗时高连接池、网络、序列化或业务逻辑对比应用分段计时
同一模板不同参数耗时差异大数据分布倾斜或计划选择不稳定使用多组参数执行计划对比

需要特别注意,InnoDB行锁等待不一定完整体现在慢查询日志的 Lock_time 中。发现 SQL耗时明显升高但扫描量没有变化时,应同时查看:

SHOW ENGINE INNODB STATUS\G

在 MySQL 8.0 中,还可以结合 Performance Schema 的锁等待表查看阻塞关系。排查锁问题时不要只盯着索引,因为索引可能已经正确使用,真正的问题是事务持有锁的时间过长、更新顺序不一致或长事务未提交。

用 EXPLAIN 判断索引是否真正有帮助

先看关键字段

对慢查询使用 EXPLAIN:

EXPLAIN
SELECT
    id,
    status,
    created_at
FROM orders
WHERE user_id = 10086
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

重点观察以下字段:

字段关注点
typeALL通常表示全表扫描;但小表使用全表扫描不一定有问题
possible_keys优化器认为可能使用的索引
key实际选择的索引;为空不等于一定错误
key_len实际使用的索引长度,可辅助判断联合索引使用到哪一部分
rows优化器估算需要检查的行数
filtered经过条件过滤后预计保留的比例
Extra是否出现临时表、排序或覆盖索引等信息

对于 MySQL 8.0,可以在测试环境使用:

EXPLAIN ANALYZE
SELECT
    id,
    status,
    created_at
FROM orders
WHERE user_id = 10086
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

EXPLAIN ANALYZE 会实际执行语句,可能读取生产数据并产生真实负载。不要对带有写操作的语句直接执行,也不要在高峰期对大表反复运行。生产环境更适合先使用普通 EXPLAIN,将相同数据规模或脱敏副本用于实际分析。

联合索引要匹配查询路径

对于上面的条件,一个可能的索引是:

(user_id, status, created_at)

它的逻辑是先按 user_id 和 status 缩小范围,再利用 created_at 支持排序或快速取前 20 行。但这不是所有查询都适用,联合索引顺序需要结合实际条件:

用 EXPLAIN 判断索引是否真正有帮助 / 联合索引要匹配查询路径配图

  • 等值过滤字段通常放在范围条件之前;
  • 需要排序的字段要考虑排序方向和前置过滤条件;
  • 只把低选择性字段放在最前面,可能无法有效缩小扫描范围;
  • 联合索引遵循左前缀原则,单独查询后面的字段通常无法完整利用索引;
  • 过多索引会增加写入、更新、备份和存储成本。

例如,以下查询可能无法充分使用 (user_id, status, created_at):

SELECT *
FROM orders
WHERE status = 'paid';

因为缺少最左侧的 user_id。而以下写法也可能破坏普通索引的使用:

SELECT *
FROM orders
WHERE DATE(created_at) = '2025-01-01';

更适合改成范围条件:

SELECT *
FROM orders
WHERE created_at >= '2025-01-01 00:00:00'
  AND created_at <  '2025-01-02 00:00:00';

实际改写前要确认时区、字段类型和业务边界。日期范围的结束时间使用左闭右开,可以避免毫秒或微秒精度造成重复统计。

出现这些信息,不代表一定要加索引

type=ALL、Using filesort 或 Using temporary 都只是线索,不是单独的故障结论。

  • 小表全表扫描可能比走索引更快;
  • 低选择性字段,例如只有少数几种状态值,单列索引收益可能很低;
  • Using filesort表示需要额外排序,不必然表示使用了磁盘文件;
  • Using temporary可能来自分组、排序或去重,需要结合行数和实际耗时判断;
  • key为空可能是统计信息估算后认为全表扫描成本更低;
  • 表数据分布变化后,旧统计信息可能导致错误计划。

如果 EXPLAIN 估算行数明显不符合实际,可在确认业务低峰和资源余量后更新统计信息:

ANALYZE TABLE orders;

该操作会读取表和索引统计信息,通常不是破坏性操作,但仍可能产生 I/O和元数据影响。执行前应确认表名、备份状态、业务窗口及托管平台限制;完成后重新检查执行计划,而不是默认它一定会改善性能。

把执行计划和资源指标放在一起看

单条 SQL 的执行计划只能说明“可能怎么执行”,不能说明服务器当时是否被其他任务拖慢。至少应同步观察 MySQL状态和操作系统指标。

SHOW GLOBAL STATUS
WHERE Variable_name IN (
  'Threads_connected',
  'Threads_running',
  'Max_used_connections',
  'Questions',
  'Queries',
  'Created_tmp_tables',
  'Created_tmp_disk_tables',
  'Slow_queries'
);

SHOW GLOBAL VARIABLES
WHERE Variable_name IN (
  'max_connections',
  'innodb_buffer_pool_size'
);

Linux服务器上,如果已安装相应工具,可以在复测窗口观察:

vmstat 1
iostat -xz 1
pidstat -p "$(pidof mysqld)" 1

不同指标的含义如下:

指标表现更可能的瓶颈判断方向
CPU长期接近满载,I/O等待较低计算、排序、函数、连接或扫描查执行计划、排序和扫描行数
iowait升高,磁盘 await和利用率升高随机读、临时表、日志或存储能力不足查缓冲池命中和临时表落盘
内存紧张并出现交换缓存不足或连接/进程占用过高检查内存分配,避免盲目扩大连接数
Threads_connected高但Threads_running低连接空闲或连接池过大优先调整连接生命周期和池大小
Threads_running持续高SQL并发执行或锁等待积压结合锁、CPU和执行时间判断
临时磁盘表持续增加分组、排序或中间结果超出内存检查SQL和临时表配置
数据库执行时间低,应用延迟高网络、连接池或业务层不要先改索引

这些指标需要看趋势和关联关系。例如,磁盘利用率达到 90% 并不必然代表数据库性能异常;如果 await很低且查询延迟稳定,可能只是设备吞吐利用率较高。反过来,磁盘利用率不高但 await明显升高,也可能存在突发随机 I/O或存储抖动。

连接池不能替代查询优化

连接池过小会造成应用线程排队,过大则可能让 MySQL 同时执行过多查询,导致 CPU、内存和磁盘争用。可以把连接池等待时间单独记录,并与 Threads_running 对照:

  • 连接池等待时间高,Threads_running低:连接池可能过小,或连接没有及时归还;
  • 连接池等待时间低,Threads_running高:数据库正在承受较高并发,需要优化SQL或降低并发;
  • 两者都高:可能存在慢查询积压、锁阻塞或数据库容量不足;
  • Threads_connected高但业务请求不高:检查连接泄漏、空闲连接过多和连接超时设置。

不要只因为 max_connections 还没达到上限,就继续扩大连接池。连接数上限是保护阈值,不是吞吐能力。一个查询平均执行 100 毫秒,理论上单个数据库工作线程每秒最多完成约 10 次同类执行;增加连接数只能在 CPU、I/O和锁仍有余量时提高并行度,超过容量后通常会放大排队。

缓存可以减压,但不能修复错误计划

应用缓存或对象缓存可以降低重复读取,但应分别观察缓存命中率和数据库负载:

  • 命中率低,可能是键设计、过期策略或请求参数离散;
  • 命中率高但数据库仍慢,可能慢的是未缓存查询或写入链路;
  • 突发失效会形成缓存击穿,使大量请求同时访问数据库;
  • 数据更新后缓存未及时失效,会产生旧数据;
  • 缓存掩盖慢查询后,一旦命中率下降,原有问题会重新暴露。

MySQL 8.0 已移除传统查询缓存,不应围绕该功能设计优化方案。缓存适合减少确定的重复读取,不应替代正确的过滤条件、分页方式和索引设计。

根据结果选择优化动作

可以用下面的决策边界安排处理顺序:

证据组合优先动作不宜立即做的事
扫描行数大、返回行少、执行计划不理想改写条件、补充或调整联合索引盲目增加多个单列索引
扫描行数少但耗时高、锁等待明显缩短事务、调整更新顺序、定位阻塞会话继续添加索引
SQL执行耗时低、连接池排队高检查连接归还、池大小和连接建立提高数据库最大连接数
CPU高、扫描和排序多减少返回列、优化排序和过滤、限制分页范围只增加内存
I/O等待高、缓冲池读取多检查热点数据、索引大小、存储延迟和内存直接删除业务数据或重建所有表
数据库耗时低、跨地域请求耗时高检查应用与数据库位置、连接复用和链路把网络延迟归因于索引

如果确认需要增加索引,先执行以下核对:

  1. 确认表结构、字段类型、字符集和现有索引,避免重复索引。
  2. 根据真实查询模板和多组参数验证,而不是只针对一个参数。
  3. 确认磁盘剩余空间,索引可能接近相关字段数据量的一部分甚至更大。
  4. 在低峰期执行,并确认在线DDL能力、元数据锁风险和业务影响。
  5. 先完成备份或具备可验证的恢复方案,记录变更前后的执行计划。
  6. 复测读性能,同时观察写入延迟、锁等待、I/O和备份时长。

示例变更如下,但不应直接复制到生产环境执行:

ALTER TABLE orders
  ADD INDEX idx_orders_user_status_created
  (user_id, status, created_at);

是否能够在线执行、是否产生较长元数据锁等待,取决于 MySQL版本、存储引擎、表结构、数据量和平台实现。添加索引前应先核对:

SELECT VERSION();

SHOW CREATE TABLE orders\G

SHOW INDEX FROM orders;

如果新索引经过一段时间验证仍无收益,回滚可以考虑删除该索引:

ALTER TABLE orders
  DROP INDEX idx_orders_user_status_created;

删除前要确认没有其他查询依赖它,并保留变更前备份和执行计划。不要在发现一次慢查询后立即添加再删除索引,反复DDL会增加锁、I/O和维护成本。

复测时如何判断已经解决

优化前后必须保持测试条件尽量一致:

  • 使用相同数据集规模和相近的数据分布;
  • 使用相同SQL模板,替换多组典型参数和极端参数;
  • 区分冷缓存和热缓存结果;
  • 记录单次延迟、p95、p99、QPS和并发数;
  • 同时记录CPU、内存、磁盘延迟、Threads_running和锁等待;
  • 观察写入、备份和其他核心业务是否受到影响。

一个参考对照可以是:

复测时如何判断已经解决配图

项目优化前优化后解释
SQL平均耗时420 ms75 ms执行计划明显改善
SQL p95900 ms160 ms尾部延迟下降
平均扫描行数180000320过滤范围缩小
CPU利用率78%42%扫描和排序成本下降
磁盘 await18 ms7 ms随机读取减少
接口 p951.1 s360 ms仍包含网络和应用耗时

这类数据属于复测记录的示例,关键不在于某个固定数值,而在于多个指标是否同向改善。如果 SQL耗时下降但接口延迟不变,应继续看连接池和网络;如果平均耗时下降而 p99仍高,则要检查锁等待、参数倾斜和高峰并发。

容量判断可以用一个简单关系做初步估算:

数据库并发执行量 ≈ 每秒SQL请求数 × 单次数据库执行时间(秒)

例如,目标是每秒 100 次查询,单次数据库执行时间约 80 毫秒,即 0.08 秒,则理论并发执行量约为:

100 × 0.08 = 8

这只是平均值,不包含锁等待、I/O抖动和尾部延迟。若高峰期 p95达到 300 毫秒,则按尾部延迟估算为:

100 × 0.30 = 30

当高峰期实际 Threads_running长期接近或超过这一量级,同时CPU、I/O或锁等待持续上升,就不能只靠增加连接数支撑,应回到慢查询、索引和事务设计上继续优化。复测至少覆盖一次业务高峰或等效并发,确认延迟、吞吐和资源使用仍有余量,再决定是继续优化SQL、调整连接池,还是评估更高的计算、内存或存储容量。

针对 MySQL 数据库、业务后台与接口服务的运行需求,A5数据提供中国香港物理服务器资源,覆盖 Xeon Gold、AMD EPYC 等平台,并配备 SSD 或 NVMe 存储及不同内存配置,为数据库缓存、数据读写和多任务处理提供硬件基础。同时,香港产品提供 CN2 与国际带宽线路,能够承载面向本地及跨境访问的业务部署。

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

相关推荐