Query Optimization: Understand Plans, Then Tune Safely查询优化：先理解执行计划，再安全调优

What Is Query Optimization?什么是查询优化？

“Optimal” does not mean mathematically fastest in every future run. A complex query can have an enormous plan space, while planning time must stay bounded. The optimizer uses statistics, metadata, cost constants, rules and heuristics to search a practical subset. Its choice can be excellent for one parameter value and weak for another, or change after data distribution, indexes, engine version, cache state and concurrency change.

“最优”并不表示在未来每次运行中都能数学意义上最快。复杂查询可能拥有极其庞大的计划空间，而规划时间必须受限。优化器依靠统计信息、元数据、成本常量、规则和启发式方法搜索可行子集。一个计划可能很适合某个参数值，却不适合另一个；当数据分布、索引、引擎版本、缓存状态或并发发生变化时，选择也可能改变。

How Query Optimization Works in a DBMSDBMS中的查询优化如何工作

1. Parse, bind and validate.解析、绑定与验证。

   The engine recognizes tokens and expressions, resolves object and column names, checks types and permissions, and builds an internal representation. Syntax validity says nothing about performance or business correctness.

   引擎识别Token与表达式，解析对象和列名，检查类型与权限，并构建内部表示。语法有效并不能证明性能或业务正确性。

2. Normalize and rewrite the logical expression.规范化并重写逻辑表达式。

   Semantics-preserving rules can simplify constants, unnest subqueries, eliminate redundant work, push predicates, reorder associative operations or substitute a materialized structure. Exact transformations vary by engine and version.

   保持语义的规则可以简化常量、展开子查询、消除冗余工作、下推谓词、重排满足结合律的操作或替换物化结构。准确转换因引擎和版本而异。

3. Enumerate legal alternatives.枚举合法备选方案。

   Alternatives include scans and index paths, join orders, nested-loop/hash/merge algorithms, partial aggregation, sorting, exchange and parallel operators. Constraints, required order and available structures prune the search.

   备选方案包括扫描与索引路径、连接顺序、嵌套循环/哈希/归并算法、局部聚合、排序、数据交换和并行算子。约束、所需顺序和可用结构会裁剪搜索空间。

4. Estimate cardinality and cost.估算基数与成本。

   Statistics estimate rows flowing through predicates and operators. Cost models translate estimated I/O, CPU, memory, communication and startup or total work into engine-specific units. Estimated cost is not elapsed milliseconds and is not comparable across products.

   统计信息估算通过谓词与算子的行数；成本模型把预计I/O、CPU、内存、通信以及启动或总工作量转换成引擎特定单位。估算成本不是实际毫秒，也不能跨产品比较。

5. Select, execute and observe.选择、执行与观察。

   The chosen physical plan runs through the execution engine. Actual row counts, loops, buffers, spills, waits, memory, CPU and elapsed time—where the product exposes them—show where estimates and reality diverge.

   选定物理计划由执行引擎运行。产品能够提供的实际行数、循环次数、缓冲区、溢写、等待、内存、CPU和耗时会揭示估算与现实在哪里分离。

This model is intentionally cross-engine. A PostgreSQL plan node, MySQL EXPLAIN column, SQL Server operator and Oracle plan statistic do not map one-for-one. Use the model to ask consistent questions, then read the exact engine documentation before interpreting fields or changing controls.

这是一个有意保持跨引擎的模型。PostgreSQL计划节点、MySQL EXPLAIN列、SQL Server算子与Oracle计划统计并非一一对应。可以用该模型提出一致问题，但在解释字段或修改控制项前，必须查阅准确引擎文档。

Read a Query Execution Plan from Evidence Outward从证据出发阅读查询执行计划

Begin with the operator that consumed work, not the visually largest percentage or the SQL line that looks complicated. Walk from leaves to root and record estimated versus actual rows, loops, rows filtered, pages or bytes read, join inputs, sorts, hash tables, spills, remote exchanges, memory grants and waits. One bad estimate near a leaf can multiply through several joins and make the expensive root operator a symptom.

应从真正消耗工作的算子开始，而不是先看视觉上最大的百分比或外观最复杂的SQL行。由叶节点向根节点阅读，记录估算行与实际行、循环次数、过滤行、读取页面或字节、连接输入、排序、哈希表、溢写、远程交换、内存授予和等待。叶节点附近的一次错误估算可能被多次连接放大，使根部昂贵算子只是症状。

Estimated plans avoid executing a statement but cannot show actual runtime rows or waits. Actual plans or runtime profiles execute work and may add instrumentation overhead. PostgreSQL explicitly warns that EXPLAIN ANALYZE executes the statement; data-changing tests need a safe transaction and rollback strategy where supported.

估算计划不会执行语句，但无法展示实际运行行数或等待；实际计划或运行Profile会执行工作，也可能增加插桩开销。PostgreSQL明确警告，EXPLAIN ANALYZE会实际执行语句；数据修改测试应在支持时使用安全事务与回滚策略。

Cardinality Estimation Is the Optimizer's Load-Bearing Guess基数估算是优化器最关键的推测

Cardinality is the number of rows expected to enter or leave an operator. It influences join order, join algorithm, access path, memory, parallelism and whether intermediate results are materialized. An estimate can fail because statistics are stale or sampled, values are skewed, columns are correlated, predicates wrap columns in expressions, parameters differ from the compilation case, or the optimizer lacks a model for the expression.

基数是预计进入或离开算子的行数，它会影响连接顺序、连接算法、访问路径、内存、并行度以及是否物化中间结果。估算可能因统计信息过期或抽样、值分布倾斜、列相关、谓词用表达式包裹列、参数不同于编译场景，或优化器缺少表达式模型而失败。

Repair evidence before forcing behavior. Confirm table and index sizes, statistics age and coverage, most-common values, histograms or engine equivalents, NULL fraction, distinct counts and correlations used together. PostgreSQL's planner statistics documentation explains that row estimates drive plan choices and that multivariate statistics can represent selected cross-column dependencies. Other engines expose different facilities.

应先修复证据，再强制行为。检查表与索引规模、统计信息年龄与覆盖范围、最常见值、直方图或引擎等价物、NULL比例、去重计数，以及一起使用的列相关性。PostgreSQL的规划器统计信息文档说明行数估算如何驱动计划选择，以及多变量统计如何表示选定的跨列依赖；其他引擎提供不同机制。

Choose Access Paths for the Whole Workload为完整工作负载选择访问路径

An index is useful when its key order, predicates and included data serve a measured access pattern. It may reduce pages read, avoid a sort, support a selective join or cover required columns. It also consumes storage, increases write and maintenance work, can extend transactions, and gives the optimizer another candidate to cost. Duplicate or overlapping indexes can cost more than they save.

当索引键顺序、谓词与包含数据服务于实测访问模式时，索引才有价值。它可能减少页面读取、避免排序、支持高选择性连接或覆盖所需列；同时也占用存储、增加写入与维护工作、延长事务，并让优化器多一个需要估算的候选方案。重复或重叠索引可能得不偿失。

A scan is not inherently a failure. MySQL documents how its optimizer uses table, column, index and WHERE-clause details to form an EXPLAIN plan. For each candidate index, test representative reads and writes, cold and warm cache behavior, storage growth, build time, replication or log impact, lock behavior and rollback.

扫描并不天然等于失败。MySQL说明优化器如何使用表、列、索引及WHERE条件生成EXPLAIN计划。对每个候选索引，都应测试代表性读写、冷热缓存行为、存储增长、构建时间、复制或日志影响、锁行为与回滚。

Join Order and Physical Operators Depend on Input Shape连接顺序与物理算子取决于输入形态

Nested-loop, hash and merge joins are tools, not rankings. A nested loop can excel when a small outer set probes a selective inner index. A hash join can suit larger equality inputs if memory is sufficient. A merge join can benefit ordered inputs but may require sorting. The correct choice depends on estimated and actual cardinality, row width, ordering, memory, parallelism, data distribution and engine implementation.

嵌套循环、哈希连接和归并连接是工具，并不是优劣排名。外侧集合很小且内侧索引查找有选择性时，嵌套循环可能很好；较大的等值输入在内存充足时可能适合哈希连接；已有序输入可能适合归并连接，但也可能需要额外排序。正确选择取决于估算与实际基数、行宽、顺序、内存、并行度、数据分布和引擎实现。

For aggregation and windows, ask how many rows enter, how many groups or partitions exist, what order is required, and whether the same sort is repeated. Push a filter only when it is semantically legal at that stage. Moving a predicate below an outer join or below aggregation can change preserved rows or totals. Removing a join is safe only when keys and required existence semantics prove it redundant.

对于聚合与窗口函数，应确认输入行数、分组或分区数量、所需顺序，以及是否重复排序。只有在当前阶段语义合法时才能下推过滤；把谓词移到外连接或聚合之前，可能改变保留行或总额。只有当键与所需存在语义证明连接冗余时，才可安全删除。

A Repeatable Query Optimization Workflow可重复执行的查询优化流程

1. Define the pass condition.定义通过条件。

   Specify result invariants, latency and throughput objectives, acceptable resource use, representative parameter classes and concurrency. Decide whether the target is first-row latency, complete result latency, throughput, cost or tail behavior.

   明确结果不变量、延迟与吞吐目标、可接受资源使用、代表性参数类别和并发。确认目标是首行延迟、完整结果延迟、吞吐、成本还是尾部行为。

2. Capture an unchanged baseline.采集未修改基线。

   Run enough repetitions to separate noise from a stable signal. Record plan identity, actual rows, reads, CPU, waits, memory, spills and returned bytes. Keep cold and warm cache trials distinct.

   执行足够次数以区分噪声与稳定信号，记录计划身份、实际行数、读取、CPU、等待、内存、溢写与返回字节，并区分冷热缓存试验。

3. Find the earliest material divergence.寻找最早的实质偏差。

   Locate where estimates depart from actual rows or where work grows sharply. Trace that node back to predicates, statistics, data types, parameters, access paths and upstream row multiplication.

   定位估算开始偏离实际行数或工作量突然增长的位置，再追溯到谓词、统计信息、数据类型、参数、访问路径和上游行倍增。

4. Form one falsifiable hypothesis.形成一个可证伪假设。

   Examples: a correlated predicate is underestimated; an implicit conversion blocks an access path; a repeated sort dominates CPU; or a lookup runs once per unexpectedly large outer row. State what plan and metric should change.

   例如：相关谓词被低估、隐式转换阻止访问路径、重复排序主导CPU，或内侧查找因外侧行数意外增大而重复执行。必须说明预计哪个计划与指标会变化。

5. Apply the smallest reversible change.应用最小可逆变更。

   Refresh or extend statistics, correct a type mismatch, test a purpose-built index, remove provably redundant work, or rewrite one boundary. Avoid simultaneous SQL, index and server configuration changes because attribution disappears.

   可刷新或扩展统计、修正类型不匹配、测试专用索引、删除可证明的冗余工作，或重写一个边界。不要同时修改SQL、索引和服务器配置，否则无法归因。

6. Verify results before performance.先验证结果，再验证性能。

   Compare keys, duplicates, NULLs, aggregates, precision, ordering and time cutoff against the approved fixture. Then repeat performance trials across parameter classes and concurrency.

   先把键、重复、NULL、聚合、精度、排序与时间截止点同获批样本比较，再针对不同参数类别与并发重复性能试验。

7. Release with monitoring and rollback.带监控与回滚上线。

   Canary the change where possible. Watch plan identity, p95/p99 latency, errors, waits, CPU, I/O, memory, write amplification and plan regressions. Roll back on the declared threshold, not after user reports accumulate.

   尽可能进行Canary发布。监控计划身份、p95/p99延迟、错误、等待、CPU、I/O、内存、写入放大与计划回归，并按预先声明的阈值回滚，而不是等用户报告累积。

Query Optimization in DBMS: A Hypothetical ExampleDBMS查询优化：一个假设示例

The following names and numbers are illustrative, not a customer case or benchmark. A report asks for paid order revenue by region for the previous 30 days. It joins `orders`, `order_items`, `customers` and `regions`, then groups by region. The approved fixture says order-item grain is expected before aggregation, cancelled orders are excluded, NULL regions remain “Unassigned,” and decimal totals must match to two places.

以下名称与数字仅为说明，不是客户案例或性能基准。某报表需要计算过去30天已支付订单按区域汇总的收入。查询连接`orders`、`order_items`、`customers`与`regions`，再按区域分组。获批样本规定聚合前粒度应为订单明细，取消订单排除，NULL区域保留为“未分配”，小数总额必须精确到两位。

The baseline plan estimates 80 recent paid orders but actually reads 48,000. That gap leads to repeated customer lookups and a small-memory aggregation that spills. A diagnostic check finds the status and date predicates are correlated in current data, while statistics model them too independently. The first experiment updates supported statistics for that predicate combination. A second, separate experiment tests a composite access path beginning with the selective status/date pattern and includes the join key.

基线计划估算近期已支付订单为80行，实际却读取48,000行；该差距导致客户查找重复执行，并让内存较小的聚合发生溢写。诊断发现当前数据中的状态与日期谓词相关，而统计模型过度按独立条件估算。第一次实验更新引擎支持的相关谓词统计；第二次独立实验测试以高选择性状态/日期模式开头、并包含连接键的复合访问路径。

Each candidate is compared against the fixture first. Illustrative warm-cache median latency changes from 1.8 seconds to 0.7 seconds, but the team does not ship on that number alone. It tests peak concurrency, rare status values, the first day of a month, write throughput, index build and storage cost, p99 latency and rollback. If the rewrite accidentally changes the left join to an inner join, the missing “Unassigned” rows fail correctness even if it runs in 0.2 seconds.

每个候选方案都先与正确性样本比较。示例中的热缓存中位延迟从1.8秒降到0.7秒，但团队不会只凭该数字上线；还会测试高峰并发、罕见状态值、月初边界、写入吞吐、索引构建与存储成本、p99延迟以及回滚。如果重写误把左连接变成内连接，即使运行时间为0.2秒，缺失“未分配”行仍会导致正确性失败。

Validate Correctness, Performance and Operations Together同时验证正确性、性能与运维

Benchmark variance is data, not clutter. Keep repetitions, environment, client behavior, cache state and observation window visible. Do not average away a bimodal parameter-sensitive query or a p99 regression that harms users while the median improves.

基准波动是数据，不是杂音。应保留重复次数、环境、客户端行为、缓存状态与观察窗口；不要用平均值掩盖双峰参数敏感查询，也不要在中位数改善时忽略伤害用户的p99回归。

Common Query Optimization Mistakes查询优化的常见错误

• Tuning from SQL text alone: readability matters, but the execution plan and runtime work identify the constraint.只看SQL文本调优：可读性重要，但执行计划与运行工作量才能定位约束。
• Indexing every filter: low selectivity, leading-key order, covering needs and write cost decide usefulness.为每个过滤条件建索引：低选择性、前导键顺序、覆盖需求和写入成本共同决定价值。
• Believing lower estimated cost proves faster runtime: cost units are model outputs; measure actual work under controlled inputs.认为更低估算成本必然更快：成本单位只是模型输出，必须在受控输入下测量实际工作。
• Forcing a plan before fixing evidence: a hint can stabilize a symptom while distribution and workload evolve.修复证据前先强制计划：Hint可能固定症状，但数据分布与负载仍会变化。
• Benchmarking one literal: typical, rare, empty, large and boundary parameter classes can need different plans.只测试一个字面值：典型、罕见、空、大结果与边界参数可能需要不同计划。
• Changing several variables: simultaneous rewrites, indexes and configuration prevent causal attribution.同时改变多个变量：同时重写SQL、添加索引与修改配置会阻止因果归因。
• Ignoring writes and concurrency: a fast single read may reduce total throughput through write amplification, memory or locks.忽略写入与并发：单次读取更快，仍可能因写放大、内存或锁降低总体吞吐。
• Running actual plans unsafely: commands that execute a statement can change data, acquire locks and create load.不安全地运行实际计划：会执行语句的命令可能改变数据、获取锁并产生负载。

Use Static SQL Complexity Analysis Before Database Testing在数据库测试前使用静态SQL复杂度分析

Prepare sanitized SQL text and identify the intended dialect. The InfiniSynapse SQL Complexity Checker visibly analyzes structural patterns such as nested queries, CTEs, joins, window functions, aggregations and dialect-specific functions. Its disclosure says the score is a static heuristic performed in the browser. It does not execute SQL, inspect schema or indexes, read database statistics, calculate an engine cost, or produce an actual execution plan.

请先准备脱敏SQL文本并确认目标方言。InfiniSynapse SQL Complexity Checker可见功能会分析嵌套查询、CTE、连接、窗口函数、聚合和方言特定函数等结构模式。其披露说明评分是在浏览器中完成的静态启发式结果；它不会执行SQL、检查Schema或索引、读取数据库统计信息、计算引擎成本，也不会生成实际执行计划。

Use the result as a review queue: a complex section may deserve clearer boundaries or a focused plan inspection, but complexity does not prove poor performance. After structural review, collect engine-native evidence with the exact schema, data distribution, parameters and workload. For a practical implementation checklist, continue to the existing InfiniSynapse SQL query optimization guide.

可把结果作为审查队列：复杂片段可能值得更清晰的边界或重点计划检查，但复杂度本身不能证明性能差。结构审查后，应使用准确Schema、数据分布、参数和工作负载采集引擎原生证据。实际实施还可阅读现有InfiniSynapse SQL查询优化指南。

Query Optimization FAQ查询优化常见问题

Official Query Optimization Sources查询优化官方来源

• PostgreSQL documentation: Using EXPLAIN and reading plan nodesPostgreSQL文档：使用EXPLAIN与阅读计划节点
• PostgreSQL documentation: Statistics used by the plannerPostgreSQL文档：规划器使用的统计信息
• MySQL Reference Manual: Understanding the query execution planMySQL参考手册：理解查询执行计划
• Microsoft Learn: SQL Server query processing architectureMicrosoft Learn：SQL Server查询处理架构
• Microsoft Learn: Execution plan overviewMicrosoft Learn：执行计划概览
• Oracle: Query optimization, cost, cardinality and plan choicesOracle：查询优化、成本、基数与计划选择

These sources describe different products and current documentation families, not one portable EXPLAIN format. Before applying a command, hint, statistic, index or configuration, verify the exact engine version, permissions, execution side effects, licensing, support and rollback behavior.

这些来源描述的是不同产品与当前文档体系，而不是统一可移植的EXPLAIN格式。应用命令、Hint、统计、索引或配置前，必须核对准确引擎版本、权限、执行副作用、许可、支持与回滚行为。

Query optimization also interacts with topology. If a plan crosses partitions or nodes, the Distributed SQL guide explains locality, network and failure considerations that a single-node plan view may not reveal.

查询优化也会与拓扑交互。如果计划跨分区或节点，分布式SQL指南解释了单节点计划视图可能无法揭示的就近性、网络与故障因素。