新加坡服务器上的SQL查询变慢,怎样从执行计划和索引入手优化?
新加坡服务器上的 SQL 查询变慢,不能只盯着“数据库 CPU 是否打满”。同一条订单列表查询,可能因为索引失效而扫描大量行,也可能执行只花几十毫秒,却在连接池里排队数百毫秒。优化的起点是把请求总延迟拆开,再用执行计划确认数据库实际读取了多少行、是否排序,以及这些读取是否与查询目标相称。
如果慢的是某一类 SQL,优先固定查询条件和数据规模,对照执行计划中的扫描路径、估算行数与实际行数,再决定索引列及顺序。索引调整后,要在相同业务负载下复测延迟、吞吐、CPU 和 I/O;单次查询变快,不等于高并发时整站容量也提高。
测试目标:先确定要优化哪段时间
以部署在新加坡服务器上的订单列表接口为例:应用和数据库位于同一地域,用户按店铺、订单状态和时间范围查询最近订单。某时段接口 P95 延迟升到 420 ms,数据库主机 CPU 约 75%。此时“CPU 高”只是现象,测试目标应进一步限定为:这条 SQL 是否读取了远超返回结果所需的数据,以及减少扫描后,接口延迟能否下降。
采集数据时,至少区分四个时间段:应用获取数据库连接的等待时间、SQL 在数据库内的执行时间、结果传输时间,以及应用处理和网络往返时间。若应用不在新加坡,而数据库在新加坡,跨地域往返还可能成为显著部分;即使索引把 SQL 执行时间降到 20 ms,也无法消除每次请求固有的网络往返。
下面是一组用于说明判断方法的对照数据。它不是服务器性能承诺,关键在于各指标如何互相解释。
| 指标 | 优化前示例 | 优化后示例 | 观察重点 |
|---|---|---|---|
| 相同请求组合下的接口 P95 | 420 ms | 90 ms | 用户实际感受到的尾部延迟 |
| 目标 SQL 的数据库执行时间 P95 | 180 ms | 35 ms | 索引是否改善了数据库内的耗时 |
| 获取连接的等待时间 P95 | 110 ms | 15 ms | 查询占用连接的时间是否缩短 |
| 数据库 CPU 使用率 | 75% | 48% | 扫描与排序带来的计算负担 |
| 数据盘读取 IOPS | 约 6000 | 约 2300 | 是否减少了物理读取压力 |
| 测试请求速率 | 均为 120 次/秒 | 均为 120 次/秒 | 避免用不同负载比较延迟 |
各项 P95 不能直接相加:它们可能来自不同请求,采样边界也不同。但在请求速率、查询条件和数据量接近时,如果 SQL 执行时间与连接等待时间同时下降,接口 P95 随之下降,就比单看一次 EXPLAIN 更能支持优化有效的判断。
指标含义:执行计划要回答“读了什么、读了多少”
以下以 MySQL 8.0 的 InnoDB 表为例。查询返回某店铺、某状态在指定时间范围内的最近 50 笔订单:
SELECT id, created_at, amount
FROM orders
WHERE tenant_id = 42
AND status = 'paid'
AND created_at >= '2025-04-01 00:00:00'
AND created_at < '2025-05-01 00:00:00'
ORDER BY created_at DESC, id DESC
LIMIT 50;
先确认数据库版本及表上已有的索引,再查看计划。EXPLAIN 不执行这条查询;MySQL 8.0.18 及以上支持的 EXPLAIN ANALYZE 会实际执行查询,应只用于确认安全的只读 SELECT,并优先在预发布环境或低峰期使用。大表、宽时间范围的查询即使带有 LIMIT,在缺少合适索引时仍可能读取大量行。
SELECT VERSION();
SHOW INDEX FROM orders;
EXPLAIN
SELECT id, created_at, amount
FROM orders
WHERE tenant_id = 42
AND status = 'paid'
AND created_at >= '2025-04-01 00:00:00'
AND created_at < '2025-05-01 00:00:00'
ORDER BY created_at DESC, id DESC
LIMIT 50;
读计划时,不宜仅凭“用了索引”就判定查询健康。重点看以下几组关系:
- 访问路径与候选索引:实际使用的是哪个索引?它是否覆盖了等值过滤列和时间范围列?只使用
tenant_id索引,仍可能需要在该店铺的大量订单中逐行筛选状态和时间。 - 扫描行数与返回行数:返回 50 行却扫描十几万行,通常比“执行时间偶尔达到 100 ms”更能指出结构性问题。优化器的
rows是估算值;可用EXPLAIN ANALYZE对照实际读取行数与耗时。 - 排序与回表:计划中的
Using filesort表示需要额外排序,不等于一定使用磁盘;是否昂贵要结合待排序行数看。二级索引找到记录后读取未包含在索引中的amount,属于回表;只回表 50 次与回表数万次,代价不同。
如果估算只需读取数百行,实际却读取数十万行,还要检查统计信息、数据分布和参数值。一个店铺有几百笔订单,另一个店铺有几百万笔订单,同一 SQL 模板可能走出不同的成本路径。因此,应分别选取常见参数和高数据量参数测试,不要只用一组容易命中缓存的样本。
影响变量:索引列顺序要服从过滤和排序
对上述查询,可评估联合索引 (tenant_id, status, created_at DESC, id DESC)。前两列对应等值条件,后两列与时间范围和排序方向一致。满足条件的记录足够多时,数据库有机会沿索引顺序取得最近 50 行,而不必先筛出大量记录再排序。id 同时为相同创建时间提供稳定的排序次序;示例中的表仍需确认 id 是主键或具有符合业务要求的唯一性。
索引是否合适,取决于真实查询组合,而不是列越多越好。若主要查询不带 status,上述索引未必能充分利用后面的时间列;若大量请求按金额排序,时间索引也不能直接解决金额排序。可把最频繁、最耗资源的 SQL 分组,分别核对其等值条件、范围条件与 ORDER BY,再评估能否共用索引。
数据分布同样会改变收益。某状态占该店铺订单的 90% 时,单列 status 索引的区分能力有限;在按店铺和时间查找的查询中,联合索引仍可能有价值,但要以计划中的实际扫描量验证。反过来,如果时间范围很窄、原有 (tenant_id, created_at) 已能快速取到 50 行,增加状态列未必值得承担额外写入成本。
还有两类写法容易让看似合适的索引难以发挥作用。一类是在索引列上计算,例如对 created_at 使用日期函数再过滤;在业务允许时,改用明确的起止时间范围。另一类是深分页:即使索引匹配,LIMIT 50 OFFSET 100000 仍可能需要越过大量索引记录。列表业务若允许,可用上一页最后一条记录的时间和 ID 做游标分页,但必须保持排序条件一致。
结果解释:把执行计划变化与资源曲线对应起来
可以把“原计划扫描约 18 万行、返回 50 行;调整索引后扫描约 60 行、返回 50 行”作为一个需要验证的目标,而不是预期每次都会得到的固定结果。如果复测确实看到扫描量大幅下降,SQL 执行时间、CPU 或读取 IOPS 也同步下降,那么索引改善了主要工作量的解释较有说服力。

若执行计划已改善,而接口 P95 基本不变,就应停止继续堆索引,检查其他耗时来源。例如,应用连接池已耗尽,请求仍在排队;接口在取得列表后又逐条查询订单详情,形成多次 SQL 往返;或者数据库执行很快,但应用与新加坡服务器之间的网络延迟占了主要部分。此时增加同一条列表 SQL 的索引,难以解决端到端延迟。

若单次查询变快,高峰期数据库 CPU 却没有下降,也不一定是优化失败。系统可能因此承接了更多请求,或其他 SQL 成为新的资源消耗来源。要同时记录每秒请求数、每秒执行次数、数据库 CPU、内存、磁盘读写和连接数,在相同到达负载下比较资源消耗,再单独提高并发测试容量边界。不能把“优化前 120 次/秒、优化后 200 次/秒”的 CPU 数值直接当作索引效果对比。
缓存也会影响解释。预热后反复查询同一店铺、同一月份,数据页可能大多留在内存中,掩盖了磁盘读取成本。测试应包含符合生产访问分布的热门与非热门数据,并记录数据库内存和磁盘读取变化;不必为了制造冷缓存而在生产环境清空缓存。
决策边界:什么时候创建索引,什么时候该停手
当目标 SQL 在代表性参数下持续扫描远多于返回量的记录、现有索引无法兼顾过滤与排序,且该查询在总负载中占有足够比例时,才值得进入索引变更评估。以下语句仅对应前述 MySQL 表结构示例,不应未经核对直接在生产库执行:
CREATE INDEX idx_orders_tenant_status_created_id
ON orders (tenant_id, status, created_at DESC, id DESC);
创建索引会读取表数据、占用存储与 I/O,并可能影响同期写入及其他查询。实施前应核对现有索引,避免重复;确认备份及恢复能力、可用磁盘空间、复制延迟和业务低峰窗口,先在数据规模与分布接近的环境验证建索引耗时及执行计划。上线后观察目标 SQL,也要观察订单写入延迟和整体资源指标。若收益不及预期或写入代价不可接受,确认没有其他查询依赖该新索引后,可在变更窗口撤销:
DROP INDEX idx_orders_tenant_status_created_id ON orders;
撤销本身也是数据库结构变更,应走相同的影响评估与备份流程。若计划显示原有索引已将扫描控制在很小范围,而主要延迟发生在连接等待、跨地域通信或应用处理阶段,应先解决对应瓶颈,不为“让执行计划更好看”增加索引。
复测时固定 SQL 模板、参数分布、数据量级和请求到达速率,分别记录优化前后的扫描行数、接口与 SQL 的 P50/P95/P99、连接等待、CPU、内存、I/O 及写入延迟。确认同负载下的收益后,再逐步提高并发;当 P95 持续越过业务可接受值,或 CPU、I/O、连接等待出现持续积压时,就达到了当前配置与查询组合下需要重新评估的容量边界。
A5idc数据提供新加坡等地区的物理服务器资源,可用于部署订单系统、数据库和接口服务。针对SQL查询、缓存与并发访问等业务负载,平台提供不同CPU、内存及SSD或NVMe存储组合,并结合业务访问地域配置相应网络线路,为数据库读写、应用接口和多任务运行提供硬件与网络基础。
本文由A5数据原创发布,转载请注明出处:https://www.a5idc.com/helpcontent/7552.html



