香港服务器运行MySQL 8.4时查询变慢,如何判断索引是否需要优化?

发布人:A5数据发布时间:2026-10-14 13:20阅读量:123

单看香港服务器上的 CPU、磁盘利用率或某一条 SQL 的响应时间,不能直接判断 MySQL 8.4 是否需要优化索引。更可靠的判断是:在同一观察窗口内,确认慢查询的 SQL 指纹、实际扫描行数、执行计划是否变化,再把数据库执行时间与连接池等待、锁等待、I/O 延迟、缓存命中率和网络耗时对齐分析。

通常可以这样形成判断:如果同一类查询的 rows examined 持续上升,EXPLAIN ANALYZE 显示实际扫描行数远高于返回行数,执行计划出现全表扫描或低效范围扫描,同时连接池等待和网络延迟没有同步升高,那么索引缺失、联合索引顺序不合适或统计信息失真就是重点方向。反过来,如果数据库执行只占总响应时间的一小部分,而连接池等待、锁等待或跨地域网络耗时明显增加,单纯增加索引往往不能解决问题。

先建立可比较的观察窗口

索引判断的第一步不是马上执行 CREATE INDEX,而是先把“正常”和“变慢”放到同一时间轴上比较。建议至少保留一个正常窗口和一个异常窗口,例如各持续 15~30 分钟;如果流量有明显日周期,则应选择业务量级相近的时段。

观察窗口需要尽量固定以下条件:

  • 相同的接口、SQL 指纹或业务操作;
  • 相近的请求量、返回数据量和分页参数;
  • 相同的应用版本、数据库节点和配置;
  • 相同的连接池上限、缓存策略及上游依赖;
  • 统一时间基准,避免应用日志、数据库日志和服务器监控存在时间偏移。

不要只记录平均响应时间。平均值可能掩盖少量请求极慢的情况,至少应同时保留平均值、P95、P99、最大值和错误率。对于数据库内部,还要记录执行次数、平均执行时间、扫描行数、返回行数和等待事件。

可以先从 Performance Schema 的 SQL 摘要中找出消耗时间较多的 SQL:

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
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

TIMER_WAIT 通常以皮秒记录,除以 1000000000000 后可近似换算为秒。这里更重要的是比较趋势,而不是把某个平均值当作固定告警线。

如果 Performance Schema 没有启用,应先确认状态,不要直接假设系统一定采集了完整数据:

SHOW VARIABLES LIKE 'performance_schema';

慢查询日志也可以作为补充。修改 slow_query_log 或 long_query_time 前,应确认权限、磁盘空间和日志轮转策略;短时间提高采集强度可能增加日志写入量,长期配置则应通过配置管理系统变更,并准备恢复原配置的方法。

把多个指标放到同一时间轴

下面是一组用于解释判断方法的模拟数据,并非某台实际香港服务器的监控结果。它展示的是同一 SQL 指纹在正常窗口与异常窗口的典型变化。

把多个指标放到同一时间轴配图

指标正常窗口 10:00~10:15异常窗口 10:15~10:30观察意义
请求量220 QPS225 QPS业务量基本相近
应用 P95 响应时间180 ms1.8 s用户侧明显变慢
数据库平均执行时间120 ms1.55 s慢点主要发生在数据库执行阶段
单次平均扫描行数1,200180,000访问路径可能恶化
单次平均返回行数4850返回结果规模没有同步扩大
CPU 使用率43%88%大量扫描或排序可能消耗 CPU
磁盘读延迟1.5 ms1.7 ms存储延迟变化不大
连接池等待3 ms5 ms连接获取不是主要等待
错误率0.08%0.12%有轻微波动,但不是主因
锁等待很少很少暂无明显锁竞争证据

这个组合比“CPU 达到 88%”更有判断价值。请求量没有明显增加,返回行数基本不变,但扫描行数从 1,200 增至 180,000,数据库执行时间同步扩大,连接池、磁盘延迟和锁等待没有相同幅度的变化。此时可以优先检查索引和执行计划。

还要注意指标的时间关系。例如:

  • 先出现缓存命中率下降,随后数据库 QPS 上升、CPU 和 I/O 增加,说明缓存失效可能是源头;
  • 先出现连接池等待升高,随后应用响应时间升高,但数据库实际执行时间稳定,说明索引不一定是主因;
  • 先出现锁等待,再出现 Threads_running 和请求队列增长,说明查询可能是在等待事务,而不是扫描速度本身变慢;
  • 数据库执行时间稳定,但应用总耗时增加,同时跨地域网络 RTT 或重传升高,问题更接近网络链路;
  • 扫描行数增加、临时表落盘增加、CPU 升高,但磁盘延迟变化不大,可能是排序、聚合或非索引条件导致的计算开销。

先区分数据库执行时间和端到端时间

在香港服务器环境中,应用与 MySQL 可能位于同一台服务器、同一机房,也可能位于不同地域。应用日志中的接口耗时不等于 MySQL 执行耗时,至少应拆成:

先区分数据库执行时间和端到端时间配图

  1. 从应用获取连接的等待时间;
  2. 向数据库发送请求前后的网络耗时;
  3. MySQL 实际执行时间;
  4. 结果集传输和应用序列化时间;
  5. 应用等待线程、缓存或下游服务的时间。

如果应用记录的总耗时为 1.8 秒,而 MySQL 执行耗时只有 120 毫秒,就不应先创建索引。应先检查连接池等待、网络 RTT、线程调度和下游依赖。

服务器侧可以用以下命令观察系统趋势。iostat 通常由 sysstat 软件包提供,若系统没有该命令,应先确认发行版和运维安装规范,不要在高峰期临时安装不熟悉的软件包。

vmstat 1 5
iostat -xz 1 5
free -h
ss -s

重点看变化方向,而不是单个采样值:

  • vmstat 中 si、so 持续非零,说明可能存在换入换出,内存压力会放大数据库延迟;
  • iostat 中设备利用率、平均请求等待时间和队列长度同时升高,说明 I/O 可能成为瓶颈;
  • free -h 中可用内存减少并不一定代表故障,Linux 会利用空闲内存作为缓存,应结合交换活动和 MySQL 指标判断;
  • ss -s 只能说明连接和套接字概况,不能代替应用端连接池指标或网络质量监控。

用执行计划确认索引是否真正参与

先看估算计划,再看实际执行

对只读查询,可以先执行:

EXPLAIN
SELECT
    order_id,
    total_amount,
    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;

关注以下字段:

  • type:ALL 通常意味着全表扫描,但小表全表扫描不一定是问题;
  • possible_keys:优化器认为可能使用的索引;
  • key:实际选择的索引;
  • key_len:实际使用的索引长度;
  • rows:优化器估算需要检查的行数;
  • filtered:估算经过条件过滤的比例;
  • Extra:是否出现 Using filesort、Using temporary 或其他访问特征。

possible_keys 不为空但 key 为 NULL,不代表索引必然失效。表很小、索引选择性低、统计信息不准确或全表扫描成本更低,都可能使优化器选择扫描。相反,key 不为空也不等于索引设计合理,可能只是使用了一个选择性很差的索引。

MySQL 8.4 可以使用 EXPLAIN ANALYZE 获取实际执行信息:

EXPLAIN ANALYZE
SELECT
    order_id,
    total_amount,
    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;

该命令会实际执行查询。只应对确认无副作用的只读 SQL 使用,避免直接对包含写操作、复杂存储过程或大范围扫描的语句执行;生产环境最好先在测试环境或只读副本验证,并限制时间窗口和并发影响。

重点比较估算值与实际值:

计划现象可能含义下一步
估算扫描 1,000 行,实际扫描 200,000 行统计信息或数据分布判断失真检查统计信息、数据倾斜和直方图
key 为 NULL,实际扫描行数很大可能缺少合适索引或条件不可索引检查过滤条件、函数、类型和联合索引
使用索引,但实际扫描行数仍很大索引选择性低、列顺序不合适或范围过宽调整索引顺序或重写查询
实际扫描行数很少,但响应仍很慢索引可能不是主因检查锁、连接池、网络和结果传输
出现 Using filesort需要额外排序,不一定代表磁盘排序结合排序行数、CPU和临时表指标判断
出现 Using temporary可能存在分组、去重或排序中间结果观察临时表是否落盘及中间结果规模

估算行数与实际行数偏差较大时,可以先刷新表统计信息:

ANALYZE TABLE orders;

这不是数据删除操作,但会改变优化器对数据分布的判断,可能导致执行计划变化。应在低峰期执行,并在操作前记录原有执行计划,之后观察慢查询是否改善。如果表非常大或复制链路敏感,应先评估执行时间、元数据锁和副本延迟。

对于某些列值分布严重倾斜的场景,可以考虑直方图。例如 status 中绝大多数记录都是 PAID,少量记录是 CANCELLED,仅凭普通索引统计可能无法准确估算:

ANALYZE TABLE orders
UPDATE HISTOGRAM ON status
WITH 64 BUCKETS;

直方图主要改善基数估算,不会自动创建索引,也不保证一定改变执行计划。上线前应记录前后 EXPLAIN 和 EXPLAIN ANALYZE 结果。

检查实际存在的索引

SHOW INDEX FROM orders;

需要关注:

  • Key_name:索引名称;
  • Seq_in_index:列在联合索引中的顺序;
  • Column_name:具体列;
  • Cardinality:基数估算,通常不是实时精确值;
  • Sub_part:是否使用了前缀索引;
  • Index_type:InnoDB 常见为 BTREE;
  • Visible:索引是否对优化器可见。

如果一个索引已经存在,但查询仍然扫描很多行,应继续检查列顺序、查询条件形式和数据分布,而不是重复创建相似索引。

从扫描行数判断索引改进方向

联合索引要匹配查询形状

上面的查询包含租户过滤、状态过滤、时间范围和排序。一个可能的索引示例是:

CREATE INDEX idx_orders_tenant_status_created
ON orders (tenant_id, status, created_at DESC);

这只是示例,不能脱离实际查询直接套用。设计时通常按以下关系分析:

  • 等值过滤列通常优先考虑;
  • 需要范围扫描的列会影响后续列的利用方式;
  • 排序列只有在前置条件和范围关系合适时,才可能帮助减少额外排序;
  • 多租户表常把租户隔离列放在前面,但要结合每个租户的数据规模;
  • 低基数列并非绝对不能建索引,关键是它与前置列组合后的选择性。

联合索引遵循左前缀原则。索引为 (tenant_id, status, created_at) 时,以下查询更容易利用它:

WHERE tenant_id = 1001
  AND status = 'PAID'
  AND created_at >= '2025-01-01 00:00:00'

只有 status 条件、没有 tenant_id 条件时,通常不能充分利用该索引的前导部分。即使优化器能够通过其他策略使用索引,也不应把这种偶然选择当成设计依据。

ORDER BY created_at 是否能够由索引完成,还取决于过滤条件、排序方向和范围条件。看到 Using filesort 不应立即认定索引错误,应该结合实际排序行数和耗时判断。如果排序的数据只有几十行,额外排序可能比增加一个宽索引更划算。

注意让条件保持可索引形式

以下写法可能使普通索引难以直接用于范围定位:

WHERE DATE(created_at) = '2025-01-15'

更适合改成时间范围:

WHERE created_at >= '2025-01-15 00:00:00'
  AND created_at <  '2025-01-16 00:00:00'

还应检查这些情况:

  • 对索引列使用函数或表达式;
  • 字符串列与数值参数发生隐式类型转换;
  • 字符集或排序规则不一致;
  • 使用 LIKE '%keyword' 这类前导通配符;
  • OR 条件使优化器难以选择单一路径;
  • 连接两侧列类型、长度或字符集不一致;
  • 分页使用很大的 OFFSET,即使有索引也要跳过大量记录。

对于必须按表达式查询的场景,可以评估生成列或函数索引,但应先确认 MySQL 8.4 的语法、索引长度和写入开销,并通过实际执行计划验证。

不要为了覆盖查询无限增加列

如果查询只返回索引中已有的列,可能出现覆盖索引,减少回表读取。例如:

CREATE INDEX idx_orders_tenant_status_created_id
ON orders (tenant_id, status, created_at DESC, order_id);

覆盖索引在高频、小结果集查询中可能降低随机读取,但索引越宽,带来的成本也越明显:

  • 插入、更新和删除需要维护更多索引页;
  • 索引占用更多磁盘和缓冲池空间;
  • 变更大字段时写放大更明显;
  • 在线创建索引会产生额外 I/O 和临时空间;
  • 过多相似索引会增加优化器选择复杂度。

因此,应该先比较“增加覆盖列”与“保留较窄索引后回表”的实际耗时,不能仅凭 Using index 就认定宽索引一定更好。

排除几个容易误判的替代原因

连接池等待上升

模拟场景中,应用 P99 从 400 毫秒升到 2 秒,但 SQL 在数据库端仍稳定在 100~150 毫秒;同时连接池等待从 5 毫秒升到 1.6 秒,Threads_connected 接近连接池上限。这时扩大索引通常不会改善接口延迟。

应同时查看:

  • 连接池活跃连接数、空闲连接数和等待获取连接数;
  • 连接建立耗时;
  • MySQL 的 Threads_connected、Threads_running;
  • 应用线程池和请求队列;
  • 是否存在连接未及时归还或事务长时间占用连接。

不要只因为数据库变慢就盲目提高连接池上限。连接数增加可能使 CPU、内存、锁竞争和上下文切换更加严重。连接池参数应结合数据库并发能力、单连接内存和业务请求模型调整。

缓存命中率下降

MySQL 8.4 不应按旧资料中的 Query Cache 机制判断缓存效果。实际场景中更常见的是应用缓存或独立缓存层的命中率变化。

如果缓存命中率先下降,随后数据库 QPS 从 2,000 增至 5,000,SQL 指纹比例没有明显改变,每次查询的扫描行数仍稳定,那么数据库压力增加可能来自缓存穿透或失效,而不是索引突然失效。

这时应把以下指标放在同一时间轴:

  • 缓存命中率和未命中请求数;
  • 对应业务接口 QPS;
  • MySQL SQL 摘要执行次数;
  • 单次扫描行数和单次返回行数;
  • 数据库 CPU、I/O 和连接数;
  • 缓存重建、批量失效或热点键过期事件。

如果缓存恢复后数据库压力和响应时间同步下降,索引优化应放在后续专项评估,而不是作为第一修复动作。

内存和 InnoDB 缓冲池不足

单看内存使用率也容易得出错误结论。需要观察缓冲池读取请求、物理读取、等待空闲页和系统交换活动的变化:

SHOW GLOBAL STATUS
WHERE Variable_name IN (
    'Innodb_buffer_pool_read_requests',
    'Innodb_buffer_pool_reads',
    'Innodb_buffer_pool_wait_free',
    'Threads_running',
    'Created_tmp_tables',
    'Created_tmp_disk_tables',
    'Select_scan',
    'Select_full_join',
    'Innodb_row_lock_current_waits'
);

应使用一段时间内的增量进行比较。例如,缓冲池命中率可以用近似关系观察:

近似命中率 = 1 - 物理读取增量 ÷ 缓冲池读取请求增量

这个比例只能作为趋势参考。它不代表所有查询都能从缓冲池受益,也不能替代单条 SQL 的执行计划。如果物理读取、磁盘延迟和扫描行数同时升高,索引或数据访问路径值得优先检查;如果物理读取不变但锁等待上升,增加索引未必有效。

锁等待和事务队列

更新、删除或带锁读取可能因为事务阻塞而变慢。此时 SQL 本身可能只需要几十毫秒,但实际等待了几秒。

MySQL 8.4 可在 Performance Schema 中查看数据锁等待关系:

SELECT
    REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx,
    REQUESTING_THREAD_ID AS waiting_thread,
    BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx,
    BLOCKING_THREAD_ID AS blocking_thread
FROM performance_schema.data_lock_waits;

如果锁等待与响应时间在同一时间段同步升高,应检查阻塞事务的持续时间、访问顺序和提交行为。索引确实可能影响锁范围:例如更新语句缺少合适条件索引,扫描和锁定的记录范围更大。但此时优化目标是减少扫描范围并改善事务边界,不能只依据某条查询的 type=ALL 就直接创建索引。

临时表、排序和结果集过大

如果 Created_tmp_disk_tables、CPU 和响应时间同时增加,而扫描行数没有明显上升,问题可能出在分组、排序、去重或结果集处理,而非过滤索引。

还要确认应用是否突然取消分页限制、一次性导出大量数据,或因为字段增加导致结果集膨胀。数据库执行时间正常、网络传输时间增长时,增加索引不会减少返回数据的传输成本。

形成索引判断的条件化结论

可以用下面的组合快速区分方向:

形成索引判断的条件化结论配图

指标组合更可能的原因索引处理建议
执行计划全表扫描,实际扫描行数大,返回行数小,连接池和网络正常缺少合适索引或条件不可索引优先设计与查询形状匹配的索引
已使用索引,但扫描行数仍远高于返回行数选择性低、联合索引顺序不合理或数据倾斜比较列顺序、统计信息和数据分布
扫描行数增加,缓冲池物理读取和磁盘延迟同步升高随机回表或缓冲池命中下降评估缩小扫描范围、覆盖索引和内存容量
SQL 执行时间稳定,连接池等待显著升高连接池或应用线程资源不足先处理连接生命周期和并发控制
SQL 执行时间稳定,网络 RTT、重传或结果传输时间升高网络链路或结果集传输问题检查应用与香港服务器的部署位置和返回数据量
锁等待、事务持续时间和队列同时升高事务阻塞优先处理事务边界、访问顺序和阻塞源
缓存未命中先升高,数据库 QPS随后增加缓存失效或击穿先核查缓存策略,不把缓存问题误判为索引问题
优化器估算与实际行数差距很大统计信息或数据分布失真评估 ANALYZE TABLE 和直方图,之后复测

真正需要优化索引时,通常应同时满足几个条件:SQL 具有稳定的业务价值和较高执行频率;慢查询发生在数据库执行阶段;实际扫描行数明显过大;执行计划能够解释这种扫描;新索引可以减少扫描、排序或回表;新增索引的写入和存储成本在可接受范围内。

创建索引前后的安全验证

在生产表上创建索引前,应完成以下准备:

  • 确认备份可用,并知道恢复路径;
  • 在测试环境或结构相近的副本验证执行计划;
  • 评估表数据量、索引大小、磁盘剩余空间和副本延迟;
  • 确认业务低峰期和可接受的锁等待范围;
  • 记录原始 EXPLAIN、EXPLAIN ANALYZE 和关键监控数据;
  • 确认索引名称不会与现有索引冲突,也没有重复索引;
  • 明确失败时如何停止变更,如何恢复应用查询路径。

InnoDB 支持不少在线 DDL 场景,但“在线”不等于没有影响。创建索引仍可能消耗 CPU、I/O、磁盘临时空间,并对复制延迟和元数据锁产生影响。示例语句如下,具体算法和锁级别应先根据表结构与 MySQL 8.4 文档、测试结果确认:

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

如果数据库不接受指定的算法或锁级别,不要直接强制改用未知方案。应先记录错误原因,确认当前表结构、存储引擎和版本能力,再决定是否在维护窗口执行。

创建完成后,复测不能只看一条 SQL 是否变快,还要检查:

  • SQL 的实际扫描行数;
  • 应用 P95、P99 和错误率;
  • 数据库 CPU、I/O 延迟和缓冲池物理读取;
  • Threads_running、连接池等待和锁等待;
  • 副本延迟、磁盘空间和写入吞吐;
  • 其他高频 SQL 是否因优化器选择变化而变慢。

如果新索引造成写入延迟、空间压力或其他查询计划恶化,回滚通常是删除该索引:

ALTER TABLE orders
    DROP INDEX idx_orders_tenant_status_created;

删除索引同样可能消耗资源并产生 DDL 影响,不能在高峰期无评估执行。删除前应确认没有其他查询依赖该索引,并保留重新创建索引的完整语句和验证记录。对于关键业务,优先在测试环境、只读副本或可控流量范围验证,而不是直接在主库反复创建和删除。

MySQL 8.4 还支持不可见索引,用于观察优化器在不使用某索引时的计划变化。不过不可见索引会改变优化器可见范围,仍应先在非生产环境验证,不能把它当作没有影响的开关。

复测时避免只看“变快了”

索引变更后的复测应使用与原观察窗口相近的流量和参数。至少保留以下对照:

  1. 相同 SQL 指纹的执行次数;
  2. 平均、P95、P99 和最大执行时间;
  3. 单次扫描行数与返回行数;
  4. 执行计划中的 key、rows、排序和临时表信息;
  5. CPU、磁盘延迟、缓冲池物理读取;
  6. 连接池等待、Threads_running 和锁等待;
  7. 应用错误率、缓存命中率和网络耗时;
  8. 写入延迟、复制延迟和磁盘空间变化。

例如,新增索引后 SQL 平均耗时从 1.5 秒降至 120 毫秒,但数据库写入延迟从 20 毫秒升至 300 毫秒,副本延迟持续增长,这不能算作无条件成功。应进一步评估索引宽度、写入量、覆盖列数量和业务收益。

相反,如果执行计划和扫描行数明显改善,但接口 P99 没有下降,说明数据库可能只是总链路中的一个环节。此时应回到连接池、网络、缓存、锁和应用队列的时间分解,而不是继续堆叠索引。

下一次应同时观察的指标组合

下一次香港服务器上的 MySQL 8.4 查询出现变慢时,建议把下面几组指标放在同一个时间窗口中,而不是分散查看:

  • 查询层:SQL 指纹、执行次数、平均/P95/P99、实际扫描行数、返回行数;
  • 计划层:key、rows、EXPLAIN ANALYZE 实际耗时、排序和临时表;
  • 资源层:CPU、内存压力、交换活动、缓冲池物理读取、磁盘队列和读延迟;
  • 并发层:连接池等待、Threads_running、锁等待、事务持续时间和应用队列;
  • 缓存层:命中率、未命中请求、缓存失效时间与数据库 QPS;
  • 网络层:应用到数据库的 RTT、重传、结果集传输时间和跨地域链路变化;
  • 可靠性层:错误率、超时数、副本延迟和磁盘剩余空间。

当“扫描行数增加、执行计划恶化、数据库执行时间升高”,并且连接池、锁等待、网络和缓存指标没有先发生变化时,索引优化通常具备较强证据基础。只有把这组关联关系复测一遍,才能判断新索引是真正减少了数据库工作量,还是仅仅让某一次请求暂时变快。

在香港服务器运行 MySQL 8.4 的业务场景中,A5数据提供从入门建站到 Xeon Gold、AMD EPYC 的物理服务器资源,配备 SSD 或 NVMe 存储及不同内存规格,可承载数据库、业务后台和接口服务等工作负载。结合香港 CN2 与国际带宽方案,以及美国、日本、新加坡等地区的服务器布局,A5数据能够为多地域访问、跨境业务和数据库部署提供相应的计算、存储与网络资源基础。

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

相关推荐