Quick answer: what is SQL query optimization? 快速答案：什么是 SQL 查询优化？
SQL query optimization is the evidence-based process of reducing latency, reads, CPU, memory, or cost while preserving the intended result. It combines semantic validation, query-structure review, actual execution plans, indexing and statistics, controlled rewrites, and representative benchmarks.
SQL 查询优化是在保持预期结果不变的前提下，依据证据降低延迟、读取、CPU、内存或成本的过程。 它结合语义验证、查询结构审查、实际执行计划、索引与统计信息、受控重构和代表性基准测试。
Start with a written definition of output grain and invariants. A rewrite that runs twice as fast but removes unmatched customers, multiplies revenue, changes NULL to zero, or chooses a different “latest” row is a defect. Performance is one acceptance dimension, not permission to change the question.
首先书面定义输出粒度与不变量。某个重构即使快一倍，但删除未匹配客户、放大收入、把 NULL 改成零，或选择不同“最新”记录，仍然是缺陷。性能只是验收维度之一，并不授权改变问题。
Capture a reproducible correctness and performance baseline 建立可重复的正确性与性能基线
Record SQL text, parameters, database version, schema and statistics state, data cutoff, result row count, distinct business keys, reconciled metrics, actual plan, elapsed time, CPU, logical and physical reads, memory, spills, and concurrency context. Run enough repetitions to separate stable behavior from cache or network noise.
记录 SQL 文本、参数、数据库版本、表结构与统计状态、数据截止点、结果行数、不同业务键、核对指标、实际计划、耗时、CPU、逻辑与物理读取、内存、溢写和并发环境。重复运行足够次数，以区分稳定行为与缓存或网络噪声。
Use several parameter classes: selective, typical, broad, skewed, empty, and worst credible. A single fast parameter can hide parameter sensitivity, while one cold run can overstate storage cost. Define the optimization target—interactive p95, batch completion, warehouse credits, or concurrency capacity—before comparing changes.
应使用高选择性、典型、宽范围、倾斜、空结果和可信最差情况等多类参数。单个快速参数会掩盖参数敏感性，一次冷运行则可能夸大存储成本。比较改动前先定义优化目标，例如交互 p95、批处理完成时间、仓库额度或并发能力。
Find where the plan spends work, not where SQL looks complex 定位计划实际消耗，而不是只看 SQL 外观
Read the actual plan from the operators that dominate time, rows, loops, reads, memory, or network. A visually large plan branch may be cheap; one small inner probe executed millions of times may dominate. Compare estimated and actual rows at every important boundary, especially after filters, joins, aggregates, and correlated predicates.
从主导时间、行数、循环、读取、内存或网络的算子阅读实际计划。视觉上庞大的分支可能很便宜，而一个小型内层探测重复数百万次可能主导成本。应在过滤、连接、聚合和相关条件等重要边界比较估算与实际行数。
| Signal 信号 | Possible mechanism 可能机制 | Evidence 证据 |
|---|---|---|
| Actual rows greatly exceed estimate 实际行数远超估算 | Skew, correlation, stale statistics 倾斜、相关或统计过期 | Key frequencies and statistics 键频率与统计信息 |
| Large spill 大量溢写 | Underestimated sort/hash or wide rows 低估排序/哈希或记录过宽 | Grant, width, actual volume 内存分配、宽度与实际量 |
| High repeated loops 重复循环极高 | Nested or correlated access 嵌套或相关访问 | Rows, reads, time per loop 每循环行数、读取与时间 |
Repair estimation evidence before forcing a plan 强制计划前先修复估算证据
Cardinality estimates influence join algorithms, order, parallelism, and memory grants. Errors can come from stale statistics, correlated columns modeled as independent, expressions without statistics, type mismatches, skewed values, temporary objects, or parameters unlike the compiled value. Diagnose the first large divergence because downstream estimates inherit earlier mistakes.
基数估算会影响连接算法、顺序、并行度和内存分配。错误可能来自统计过期、相关字段被视为独立、表达式缺少统计、类型不匹配、数值倾斜、临时对象或参数与编译值不同。应定位第一个大幅偏差，因为后续估算会继承早期错误。
Update or improve statistics where appropriate, make types and predicates sargable, expose stable intermediate results, and test parameter classes. Hints and forced plans can be temporary controls, but they encode assumptions that may fail as data changes. Document review and expiry conditions.
在适当情况下更新或改善统计，让类型与条件可索引，暴露稳定中间结果，并测试不同参数类别。提示和强制计划可以临时控制，但它们编码的假设可能随数据变化失效，因此必须记录复核与到期条件。
Design indexes around access patterns and total workload 围绕访问模式与总体负载设计索引
A useful index can support selective equality and range predicates, join keys, grouping, ordering, or coverage. Composite order matters: equality keys often precede range or ordering columns, but selectivity, scan direction, included columns, clustering, and engine rules matter. Implicit casts and functions on indexed keys can block efficient access.
有用索引可支持高选择性等值与范围条件、连接键、分组、排序或覆盖。复合顺序很重要：等值键通常位于范围或排序字段之前，但选择性、扫描方向、包含字段、聚簇和引擎规则同样重要。隐式转换与索引键上的函数可能阻碍高效访问。
Every index consumes storage, cache, build time, and write maintenance. Check overlap with existing indexes and benefit across the workload. An index created for one report may slow ingestion or never be selected under realistic parameters. Validate with usage evidence and a rollback plan.
每个索引都会消耗存储、缓存、构建时间和写入维护成本。应检查与现有索引的重叠以及对总体负载的收益。为单个报表创建的索引可能拖慢摄取，或在真实参数下从未使用，因此需要使用证据与回滚计划。
Simplify work while preserving query grain 在保持查询粒度的同时简化工作
Safe candidates include removing unused columns before sorts, eliminating proven redundant joins, aggregating one-to-many branches before combining, replacing existence joins with EXISTS, calculating repeated expressions once, and splitting named stages for validation. None is universally safe: join elimination depends on constraints, pre-aggregation can remove detail, and predicate pushdown can change outer-join preservation.
候选改动包括排序前删除不用字段、移除已证明冗余连接、组合前预聚合一对多分支、用 EXISTS 替代纯存在性连接、一次计算重复表达式，以及拆分命名阶段用于验证。它们并非普遍安全：连接消除依赖约束，预聚合可能删除明细，下推条件可能改变外连接保留语义。
Apply one material change at a time. Reconcile row counts, distinct keys, null populations, and metrics before reading the new runtime. If several changes land together, the performance cause and correctness regression become difficult to isolate.
每次只应用一个重要改动。阅读新运行时间前，先核对行数、不同键、NULL 总体和指标。多个改动一起落地时，性能原因与正确性回归都难以隔离。
Reduce input only when the predicate belongs at that stage 只有条件属于该阶段时才缩减输入
Early reduction can be powerful, but moving a filter changes meaning when it crosses an outer join, aggregation, window, or deduplication boundary. A right-table predicate in ON defines eligible matches; in WHERE it may remove unmatched rows. A WHERE predicate controls rows entering GROUP BY; HAVING controls groups after metrics. A pre-window filter changes the ranking population.
提前缩减输入很有效，但条件跨越外连接、聚合、窗口或去重边界时会改变含义。右表条件在 ON 中定义合格匹配，在 WHERE 中可能删除未匹配记录；WHERE 控制进入 GROUP BY 的记录，HAVING 控制指标计算后的分组；窗口前过滤会改变排名总体。
Write the question before moving the predicate, then test edge cases that distinguish locations. Performance pushdown is accepted only after semantic equivalence is proven.
移动条件前先写出问题，再用能区分不同位置的边界案例测试。只有证明语义等价后才能接受性能下推。
Benchmark representative workloads and resource tradeoffs 基准测试代表性负载与资源权衡
Measure distributions, not one stopwatch result. Repeat runs, separate compilation where relevant, record warm and cold states, and compare elapsed time, CPU, reads, writes, memory, spills, temp space, network, and concurrency impact. A rewrite that reduces elapsed time by using far more CPU may hurt shared workload capacity.
应测量分布，而不是单次秒表结果。重复运行，在适当时分离编译成本，记录冷热状态，并比较耗时、CPU、读写、内存、溢写、临时空间、网络和并发影响。某个重构通过大幅增加 CPU 来降低耗时，可能损害共享负载容量。
Use production-like data distribution without exposing sensitive data. Synthetic data must preserve cardinality, skew, correlation, and value ranges. Run equivalence checks on every benchmark output, not just before performance testing.
使用接近生产的数据分布但不要暴露敏感数据。合成数据必须保持基数、倾斜、相关性与取值范围。每次基准输出都要运行等价检查，而不只是性能测试前检查一次。
Use an optimization acceptance checklist 使用优化验收清单
- Define output grain, preservation, NULL meaning, and reconciled metrics.
- Save actual baseline plans and resource measures.
- Identify the first material estimate error and dominant operator.
- Change one mechanism at a time and explain why it should help.
- Test selective, typical, broad, empty, and skewed parameters.
- Compare row-level differences plus aggregate totals.
- Measure concurrency and write impact for indexes.
- Document rollback, monitoring, and plan-regression thresholds.
- 定义输出粒度、保留语义、NULL 含义和核对指标。
- 保存实际基线计划和资源度量。
- 定位第一个重要估算错误与主导算子。
- 每次改变一个机制并解释为何有效。
- 测试高选择性、典型、宽范围、空结果和倾斜参数。
- 比较记录级差异与汇总总量。
- 测量索引对并发和写入的影响。
- 记录回滚、监控与计划回归阈值。
Diagnose a faster rewrite that changed revenue 诊断改变收入的“更快”重构
Suppose a rewrite pre-aggregates orders before joining payments and cuts runtime by 70 percent, but reported revenue falls. The original output was at payment grain and allocated order revenue across payments; the rewrite moved to order grain and discarded that rule. Compare one multi-payment order, document intended allocation, and rebuild stages at compatible grains. Speed did not cause the defect; an unstated grain contract did.
假设某重构在连接付款前预聚合订单，运行时间降低 70%，但收入下降。原输出是付款粒度，并把订单收入分摊到付款；重构改为订单粒度并删除了分摊规则。应比较一个多付款订单，记录预期分摊，并在兼容粒度上重建阶段。缺陷并非速度造成，而是粒度契约未声明。
-- Find orders where an order-grain rewrite can hide payment-grain behavior
SELECT order_id,
COUNT(*) AS payment_rows,
SUM(amount) AS total_paid
FROM payments
WHERE order_id = :representative_order_id
GROUP BY order_id
HAVING COUNT(*) > 1;Inspect structure before deeper performance testing 深入性能测试前检查查询结构
Paste a sanitized complete statement into the InfiniSynapse SQL Complexity Checker to expose joins, nesting, windows, aggregates, repeated logic, and maintainability risks. Then use the target database's actual plan and benchmarks for execution evidence.
将脱敏后的完整语句粘贴到 InfiniSynapse SQL Complexity Checker，识别连接、嵌套、窗口、聚合、重复逻辑与可维护性风险，再使用目标数据库实际计划与基准获取执行证据。
Open SQL Complexity Checker 打开 SQL 复杂度检查器 Remove credentials, secrets, personal data, and sensitive literals. 请移除凭据、密钥、个人数据和敏感字面值。SQL query optimization frequently asked questions SQL 查询优化常见问题
Reducing resource use or latency while preserving intended results.
在保持预期结果的前提下降低资源或延迟。
Define correctness, capture a baseline, and inspect the actual dominant work.
定义正确性、建立基线并检查实际主导工作。
No. Benefit depends on access and total workload cost.
不一定，收益取决于访问模式与总体负载成本。
A material difference between estimated and actual row counts.
估算行数与实际行数之间的重要差异。
Use representative parameters, repeated measurements, resource metrics, and equivalence checks.
使用代表性参数、重复测量、资源指标和等价检查。
Official sources and optimization documentation 查询优化官方来源与参考资料
Prevent SQL performance regression after deployment 防止 SQL 优化上线后再次变慢
A query that is fast today can slow down as data volume, parameter distribution, concurrency, or database statistics change. After deployment, monitor user-facing latency together with rows read, CPU, memory, temporary spills, lock waits, and the chosen execution plan.
今天运行很快的 SQL，可能随着数据量、参数分布、并发量或统计信息变化再次变慢。上线后除了观察用户实际延迟，还应同时记录扫描行数、CPU、内存、临时文件溢写、锁等待和执行计划。
Recheck important queries after rapid data growth, schema changes, database upgrades, or major changes in business filters.
当数据量快速增长、表结构改变、数据库升级或业务筛选条件明显变化时，重新测试关键查询。
Keep the previous SQL or index definition and define rollback thresholds for wrong results, latency, CPU, reads, spills, and write impact.
保留修改前的 SQL 或索引定义，并针对结果错误、延迟、CPU、读取量、溢写和写入影响设定回滚阈值。