业务系统性能改造手册 · 执行提速
业务系统性能改造手册/执行提速

让 SQL 只做必要的工作

2026-08-055 min read执行提速
摘要

从调用频次、执行耗时和数据工作量筛出值得改造的订单查询，再收窄过滤范围、返回字段、排序分页与对象装配，并用真实执行计划和压测结果决定索引是否保留。

前一章已经尽量让重复请求、热点读取和并发回源消失。剩下的订单查询仍然要访问数据库，这时再用缓存命中率解释延迟就不够了：一条查询到底读取了多少候选行、排序了多少结果、返回了多少字节，应用又装配了多少无用字段？

举个简化场景。订单列表页面只展示订单号、状态、金额和创建时间，旧接口却执行 SELECT *，允许不受约束的历史范围，使用深分页，再把完整实体转换成列表 DTO。即使最后只返回几十行，数据库和 Java 代码都可能做了更多工作。

小编更倾向于先缩小工作范围，再讨论索引：把业务真正需要的过滤、排序、返回字段和分页契约写清楚，随后用执行计划判断索引是否匹配。本文不处理 N+1 与批量访问，也不调整线程池和连接池；它们分别留给 03-02 和 03-03。

一、先选一条值得改的查询

“慢 SQL”列表通常混着两类问题：单次很慢但很少执行的查询，以及单次不算慢、却在核心接口上高频执行的查询。优先级不能只按最大耗时排。

从 01-03 的调用链和 02-01 的请求成本链中，为候选查询补齐同一采样窗口内的证据：

yaml
1queryCandidate: 2 queryId: ${NORMALIZED_SQL_OR_DIGEST} 3 caller: ${ENDPOINT_AND_CODE_LOCATION} 4 experimentId: ${EXPERIMENT_ID} 5 sampleWindow: ${START_END_TIME} 6 executions: ${COUNT} 7 latency: 8 p50: ${DURATION} 9 p95: ${DURATION} 10 p99: ${DURATION} 11 databaseWork: 12 rowsExaminedOrEquivalent: ${VALUE_OR_UNKNOWN} 13 rowsReturned: ${COUNT} 14 sortOrTemporaryEvidence: ${PLAN_OR_METRIC_REFERENCE} 15 bytesReturned: ${VALUE_OR_UNKNOWN} 16 applicationWork: 17 mappedObjects: ${COUNT_OR_UNKNOWN} 18 responseRows: ${COUNT} 19 selectedButUnusedFields: ${FIELD_LIST_OR_UNKNOWN} 20 business: 21 completedActions: ${COUNT} 22 resultContract: ${FILTER_SORT_FIELDS_AND_PAGE_SEMANTICS} 23 evidenceQuality: ${COMPLETE_PARTIAL_OR_UNKNOWN}

rowsExaminedOrEquivalent 的取得方式会随 MySQL 版本、采集工具和权限变化，拿不到就保留 unknown。不要把返回行数当成扫描行数，也不要把某次偶发慢调用乘以全站 QPS，拼出一个看似精确的“总成本”。

候选查询至少满足两个条件才进入改造：它在可复现负载中贡献了可见的数据库或应用成本；业务结果契约能够说清楚。过滤条件、排序规则和分页语义仍在争论时，先改 SQL 往往只是把需求歧义埋进索引。

二、先收窄查询，再让索引匹配它

SQL 优化先缩小读取排序返回和装配的数据范围再让索引匹配真实查询契约 下面使用一个待适配的订单表示例。项目尚未确定真实表结构和 MySQL 版本，因此列名、类型、数据分布、SQL 模式和执行计划都要在案例工程落地时重新确认。

假设旧查询如下：

sql
1SELECT * 2FROM orders 3WHERE tenant_id = ? 4 AND status = ? 5ORDER BY created_at DESC 6LIMIT ?, ?;

这段 SQL 的问题不能只用“没走索引”概括。它没有表达调用方实际需要的字段；页码越深，offset 之前的结果仍要被定位和跳过；created_at 可能重复，只按它排序无法给游标提供稳定边界；查询也没有限制允许查看的历史区间。

2.1 返回列表契约需要的字段

列表只展示四个字段，就直接投影四个字段：

sql
1SELECT id, status, total_amount, created_at 2FROM orders 3WHERE tenant_id = ? 4 AND status = ? 5 AND created_at >= ? 6 AND created_at < ? 7ORDER BY created_at DESC, id DESC 8LIMIT ?;

时间范围不是性能层偷偷添加的限制。它必须来自已确认的产品契约，例如页面默认只展示某个可配置历史窗口，并提供单独的归档查询入口。业务确实要求检索全部历史时，应保留这个能力并单独测量，不能为了执行计划好看而截断正确结果。

id 作为第二排序列，是为了在多个订单拥有相同 created_at 时保持确定顺序。MySQL 官方文档也提醒：ORDER BY 列存在相同值时，加入 LIMIT 后这些行之间的返回顺序可能变化；需要稳定顺序就要补充能打破并列的排序列。MySQL LIMIT Query Optimization

2.2 深页改为稳定游标，但先确认页面契约

第一页返回最后一行的 (created_at, id)。下一页使用它限定后续范围：

sql
1SELECT id, status, total_amount, created_at 2FROM orders 3WHERE tenant_id = ? 4 AND status = ? 5 AND created_at >= ? 6 AND created_at < ? 7 AND ( 8 created_at < ? 9 OR (created_at = ? AND id < ?) 10 ) 11ORDER BY created_at DESC, id DESC 12LIMIT ?;

这个游标与排序方向必须成对修改。只传 created_at 会在时间相同时漏行或重复；只把 OFFSET 换成游标参数，却继续按另一组字段排序，同样不成立。

游标分页适合“继续向后浏览”，不支持廉价地跳到任意页，也不能直接给出精确总页数。若页面必须跳页或展示精确总数，应把它当成独立业务需求评估，不能静默更改接口语义。并发写入期间页面看到的是按游标边界推进的结果，不等同于数据库快照；是否允许这种可见性也要写进接口契约。

2.3 可选条件不要藏进一条万能 SQL

常见写法是：

sql
1WHERE tenant_id = ? 2 AND (? IS NULL OR status = ?)

它省了一段 Java 分支，却把“按状态查询”和“查询所有状态”混成同一个 SQL 形状。优化器最终选择什么计划取决于版本、统计信息、参数和数据分布，不能凭代码外观断言一定失效。

更容易验证的做法是为已确认的查询形状使用显式 SQL：有状态条件时使用 tenant_id + status + 时间范围，没有状态条件时使用 tenant_id + 时间范围。两种查询分别记录频次、计划和收益；极少使用的形状不一定值得单独增加索引。

三、索引是查询契约的结果

索引价值要用真实过滤排序与执行证据验证同时记录写入和存储代价 对于“租户内、固定状态、按创建时间和 ID 倒序向后浏览”的示例，可以把下面的索引作为实验候选：

sql
1CREATE INDEX idx_orders_tenant_status_created_id 2 ON orders (tenant_id, status, created_at, id);

前两列对应等值过滤，后两列对应时间边界和稳定排序。这个排列只是从示例查询推导出的候选，不代表优化器一定选它，也不代表真实项目应该照搬。MySQL 能否使用索引完成过滤与排序，要看版本、存储引擎、字段类型、排序方向、统计信息和实际数据分布；官方文档建议通过 EXPLAIN 检查 ORDER BY 的索引使用情况。MySQL ORDER BY Optimization

不要为了把查询变成“覆盖索引”就把所有响应字段塞进索引。total_amount 是否值得加入，要比较回表成本与索引体积、写放大和缓存占用。MySQL 官方文档明确说明，多余索引会占用空间，并增加插入、更新和删除时维护索引的成本。MySQL Optimization and Indexes

如果“所有状态”的查询也是高频路径，可能需要另一个候选：

sql
1CREATE INDEX idx_orders_tenant_created_id 2 ON orders (tenant_id, created_at, id);

先分别执行实验，再决定是否同时保留两个索引。索引数量不是完成标志；查询计划、数据库工作量、写入代价和磁盘增长才是证据。

3.1 EXPLAIN 看估算，EXPLAIN ANALYZE 看实际执行

先在与基线数据分布一致的安全环境中记录估算计划：

sql
1EXPLAIN FORMAT=TREE 2SELECT id, status, total_amount, created_at 3FROM orders 4WHERE tenant_id = ${TENANT_ID} 5 AND status = ${STATUS} 6 AND created_at >= ${FROM_TIME} 7 AND created_at < ${TO_TIME} 8ORDER BY created_at DESC, id DESC 9LIMIT ${PAGE_SIZE};

MySQL 8.4 的 EXPLAIN ANALYZE 会实际运行语句，并报告迭代器的估算行数、实际返回行数、耗时和循环次数；它不是无副作用的“只看计划”命令。应先确认目标版本支持情况，在预发布、只读副本或经过风险评估的受控环境中使用，并为参数选择和执行时间设置边界。MySQL EXPLAIN Statement

计划核对不只看 key 名称。至少要回答：

  • 过滤条件实际读取了多少候选行，最终返回多少行；
  • 排序是否利用预期索引，还是出现额外排序证据；
  • 估算行数与实际行数偏差是否大到足以改变计划判断；
  • 首行时间、总耗时和循环次数是否符合查询结构；
  • 不同租户、状态、时间范围和冷热数据下，计划是否仍稳定。

没有真实输出时，报告只保存命令、参数范围和待填字段，不伪造 rows、actual time 或“优化后走了某索引”的截图。

四、应用层也要停止装配无用对象

SQL 只投影四列，Java 代码却先构造完整 OrderEntity，再转成 OrderListRow，数据库减少的工作会被应用层重新补回来。下面使用标准 JDBC 展示边界；真实项目可以换成现有数据访问框架，但最终发出的 SQL 和映射对象应保持等价。

java
1import java.math.BigDecimal; 2import java.sql.Connection; 3import java.sql.PreparedStatement; 4import java.sql.ResultSet; 5import java.sql.SQLException; 6import java.sql.Timestamp; 7import java.time.Duration; 8import java.time.Instant; 9import java.util.ArrayList; 10import java.util.List; 11import javax.sql.DataSource; 12 13record AccessScope(long tenantId) {} 14 15record OrderCursor(Instant createdAt, long orderId) {} 16 17record OrderListQuery( 18 String status, 19 Instant fromInclusive, 20 Instant toExclusive, 21 OrderCursor cursor, 22 int pageSize) {} 23 24record OrderListRow( 25 long orderId, 26 String status, 27 BigDecimal totalAmount, 28 Instant createdAt) {} 29 30interface OrderListAuthorizer { 31 void requireListOrders(AccessScope scope, String status); 32} 33 34final class OrderListRepository { 35 private static final String FIRST_PAGE_SQL = """ 36 SELECT id, status, total_amount, created_at 37 FROM orders 38 WHERE tenant_id = ? 39 AND status = ? 40 AND created_at >= ? 41 AND created_at < ? 42 ORDER BY created_at DESC, id DESC 43 LIMIT ? 44 """; 45 46 private static final String NEXT_PAGE_SQL = """ 47 SELECT id, status, total_amount, created_at 48 FROM orders 49 WHERE tenant_id = ? 50 AND status = ? 51 AND created_at >= ? 52 AND created_at < ? 53 AND (created_at < ? OR (created_at = ? AND id < ?)) 54 ORDER BY created_at DESC, id DESC 55 LIMIT ? 56 """; 57 58 private final DataSource dataSource; 59 private final OrderListAuthorizer authorizer; 60 private final int maxPageSize; 61 private final Duration maxQueryRange; 62 63 OrderListRepository( 64 DataSource dataSource, 65 OrderListAuthorizer authorizer, 66 int maxPageSize, 67 Duration maxQueryRange) { 68 if (maxPageSize <= 0 69 || maxQueryRange == null 70 || maxQueryRange.isZero() 71 || maxQueryRange.isNegative()) { 72 throw new IllegalArgumentException("invalid query boundaries"); 73 } 74 this.dataSource = dataSource; 75 this.authorizer = authorizer; 76 this.maxPageSize = maxPageSize; 77 this.maxQueryRange = maxQueryRange; 78 } 79 80 List<OrderListRow> findPage(AccessScope scope, OrderListQuery query) 81 throws SQLException { 82 validate(query); 83 authorizer.requireListOrders(scope, query.status()); 84 85 String sql = query.cursor() == null ? FIRST_PAGE_SQL : NEXT_PAGE_SQL; 86 try (Connection connection = dataSource.getConnection(); 87 PreparedStatement statement = connection.prepareStatement(sql)) { 88 int index = 1; 89 statement.setLong(index++, scope.tenantId()); 90 statement.setString(index++, query.status()); 91 statement.setTimestamp(index++, Timestamp.from(query.fromInclusive())); 92 statement.setTimestamp(index++, Timestamp.from(query.toExclusive())); 93 if (query.cursor() != null) { 94 Timestamp cursorTime = Timestamp.from(query.cursor().createdAt()); 95 statement.setTimestamp(index++, cursorTime); 96 statement.setTimestamp(index++, cursorTime); 97 statement.setLong(index++, query.cursor().orderId()); 98 } 99 statement.setInt(index, query.pageSize()); 100 101 try (ResultSet resultSet = statement.executeQuery()) { 102 List<OrderListRow> rows = new ArrayList<>(query.pageSize()); 103 while (resultSet.next()) { 104 rows.add(new OrderListRow( 105 resultSet.getLong("id"), 106 resultSet.getString("status"), 107 resultSet.getBigDecimal("total_amount"), 108 resultSet.getTimestamp("created_at").toInstant())); 109 } 110 return List.copyOf(rows); 111 } 112 } 113 } 114 115 private void validate(OrderListQuery query) { 116 if (query == null 117 || query.status() == null 118 || query.status().isBlank() 119 || query.fromInclusive() == null 120 || query.toExclusive() == null 121 || !query.fromInclusive().isBefore(query.toExclusive())) { 122 throw new IllegalArgumentException("invalid order list query"); 123 } 124 if (query.pageSize() <= 0 || query.pageSize() > maxPageSize) { 125 throw new IllegalArgumentException("page size exceeds configured boundary"); 126 } 127 if (Duration.between(query.fromInclusive(), query.toExclusive()) 128 .compareTo(maxQueryRange) > 0) { 129 throw new IllegalArgumentException("time range exceeds configured boundary"); 130 } 131 if (query.cursor() != null 132 && (query.cursor().createdAt() == null 133 || query.cursor().orderId() <= 0 134 || query.cursor().createdAt().isBefore(query.fromInclusive()) 135 || !query.cursor().createdAt().isBefore(query.toExclusive()))) { 136 throw new IllegalArgumentException("cursor is outside query range"); 137 } 138 } 139}

租户范围来自可信的 AccessScope，调用方传入的普通查询参数不能直接替代它；列表权限也在执行 SQL 前显式检查。代码只创建响应需要的 OrderListRow，没有完整实体和二次 DTO 转换。若项目使用 JPA 投影、MyBatis result map 或其他 ORM，要查看实际生成 SQL、实际选择列和实际对象数量，不能看到“Projection”类名就假定数据库与映射工作已经减少。

示例假设数据库时间列、JDBC 驱动和应用已经约定了可一致还原的 Instant 映射。真实列若使用 DATETIME、本地业务时区或其他精度，应沿用项目已验证的类型与时区契约，并确保游标序列化后没有丢失数据库用于排序的精度；不要照抄 Timestamp 转换后再补一个隐蔽的跨页边界错误。

这个示例只覆盖“状态必填”的查询形状。状态可选时应调用另一条显式 SQL，并为它单独验证计划；不要重新塞回 (? IS NULL OR status = ?) 以换取表面上的单方法复用。

五、用同一负载验收收益和代价

改造前后复用 01-01 的数据快照、预热、采样窗口和负载模型。第一次实验只切换查询与映射代码；索引实验再单独切换候选索引，避免把字段裁剪、游标分页和索引收益揉成一个无法归因的数字。

yaml
1sqlChangeAcceptance: 2 experimentId: ${EXPERIMENT_ID} 3 database: 4 productAndVersion: ${MYSQL_VERSION} 5 schemaVersion: ${SCHEMA_VERSION} 6 dataSnapshot: ${SNAPSHOT_ID} 7 query: 8 queryId: ${QUERY_ID} 9 queryShape: ${STATUS_REQUIRED_OR_OTHER} 10 parameters: ${TENANT_STATUS_TIME_RANGE_CURSOR_PAGE_SIZE_DISTRIBUTION} 11 selectedColumnsBefore: ${COUNT} 12 selectedColumnsAfter: ${COUNT} 13 executions: ${COUNT} 14 plan: 15 before: ${EXPLAIN_REFERENCE} 16 after: ${EXPLAIN_REFERENCE} 17 analyzeEnvironment: ${STAGING_REPLICA_OR_OTHER} 18 estimatedRows: ${BEFORE_AFTER} 19 actualRowsAndLoops: ${BEFORE_AFTER_OR_UNAVAILABLE} 20 sortEvidence: ${BEFORE_AFTER} 21 databaseWork: 22 rowsExaminedOrEquivalent: ${BEFORE_AFTER_OR_UNKNOWN} 23 rowsReturned: ${BEFORE_AFTER} 24 bytesReturned: ${BEFORE_AFTER_OR_UNKNOWN} 25 queryP95: ${BEFORE_AFTER} 26 queryP99: ${BEFORE_AFTER} 27 cpuOrIoEvidence: ${BEFORE_AFTER_OR_UNKNOWN} 28 application: 29 mappedObjects: ${BEFORE_AFTER} 30 allocatedBytesOrEquivalent: ${BEFORE_AFTER_OR_UNKNOWN} 31 requestP95: ${BEFORE_AFTER} 32 requestP99: ${BEFORE_AFTER} 33 terminalCounts: ${BEFORE_AFTER} 34 indexCost: 35 indexBytes: ${MEASURED_VALUE_OR_UNKNOWN} 36 writeLatency: ${BEFORE_AFTER} 37 rowsWritten: ${COMPARABLE_COUNT} 38 correctness: 39 resultDiff: ${PASS_FAIL_WITH_REFERENCE} 40 stableOrdering: ${PASS_FAIL} 41 cursorNoDuplicateOrGap: ${PASS_FAIL_WITH_TEST_WINDOW} 42 authorizationCases: ${PASS_FAIL} 43 decision: 44 keepChange: ${YES_NO_WITH_REASON} 45 acceptedCost: ${WRITE_STORAGE_AND_MAINTENANCE_COST} 46 rollbackTrigger: ${CONDITION}

正确性回归至少覆盖相同创建时间的多条订单、时间范围边界、空结果、最后一页、跨页期间插入新订单、不同租户和无权限主体。性能数据漂亮但结果漏行、重复或越权，这项改造仍然失败。

5.1 回退要先切代码，再处理索引

应用改造通过功能开关或可部署版本保留旧查询路径。出现结果差异、计划回退、尾延迟恶化或写入代价超出边界时，先切回旧 SQL 和旧分页契约；确认没有版本仍依赖新索引后，再在经过 DDL 风险评估的窗口执行：

sql
1DROP INDEX idx_orders_tenant_status_created_id ON orders; 2DROP INDEX idx_orders_tenant_created_id ON orders;

只删除本次确实创建的索引。执行前用 SHOW INDEX FROM orders 核对名称和当前消费者，并根据目标 MySQL 版本、表规模、复制拓扑和变更工具评估锁、日志与执行时长；本文不假设某种 DDL 在生产环境必然无阻塞。

最终交付的不是一条“优化后 SQL”，而是一组能够相互校验的材料：原查询与改造补丁、业务结果契约、前后执行计划、数据库与应用工作量、索引写入和存储代价、正确性回归，以及能实际执行的回退顺序。只有这些证据支持“读取、排序、返回和装配都减少了”，才说明这条必要查询真的少做了工作。