Cost Based Optimizer: How Statistics Become a Query Plan基于成本的优化器：统计信息如何转化为查询计划

What Is a Cost Based Optimizer?什么是基于成本的优化器？

“Lowest cost” is not a promise of the fastest wall-clock time. The optimizer searches a bounded subset of an enormous plan space, uses estimates rather than future facts, and represents resource trade-offs in engine-specific units. The chosen plan can be rational given the model and still perform poorly when statistics, parameters, cache state, concurrency, memory or network conditions differ from its assumptions.

“最低成本”并不承诺实际耗时一定最短。优化器只会搜索巨大计划空间中的有限子集，用估算而不是未来事实做判断，并以引擎特定单位表示资源权衡。当统计信息、参数、缓存状态、并发、内存或网络条件偏离假设时，所选计划即使在模型中合理，也可能表现很差。

Cost Based vs Rule Based Optimization基于成本与基于规则的优化有什么区别？

Modern engines usually combine these approaches. A rule may push a predicate or simplify an expression; a cost model then compares scans, joins and orders; runtime logic may later change a distribution or join strategy. Query tuning is different again: it is the human workflow that improves evidence, changes a supported constraint and verifies results. Treating every plan choice as “the CBO alone” hides important boundaries.

现代引擎通常组合这些方法。规则可以下推谓词或简化表达式，成本模型再比较扫描、连接与顺序，运行时逻辑还可能改变数据分布或连接策略。查询调优则是另一层：它是由人改进证据、修改受支持约束并验证结果的流程。把每个计划选择都归因于“只有CBO”，会掩盖重要边界。

How a Cost Based Optimizer Works基于成本的优化器如何工作

1. Parse, bind and establish legality.解析、绑定并确认合法性。

   The engine resolves names and types, checks permissions and produces an internal expression. Correct SQL syntax does not prove business correctness or good performance.

   引擎解析名称与类型、检查权限并生成内部表达式。SQL语法正确并不能证明业务语义正确或性能良好。

2. Apply semantics-preserving transformations.应用保持语义的转换。

   Possible rewrites include constant folding, subquery unnesting, view merging, predicate movement and redundant-operation removal. Availability depends on the engine and query shape.

   可能的重写包括常量折叠、子查询展开、视图合并、谓词移动与冗余操作消除；是否可用取决于引擎和查询形态。

3. Estimate selectivity and cardinality.估算选择率与基数。

   Statistics predict what fraction of rows passes each condition and how many rows flow between operators. These estimates propagate upward and shape later choices.

   统计信息预测每个条件通过的行比例，以及算子之间流动的行数；这些估算向上传播并影响后续选择。

4. Enumerate physical alternatives.枚举物理候选方案。

   The optimizer considers available access paths, join orders, join algorithms, aggregation, sort, materialization, parallel and distribution choices, while pruning alternatives it deems dominated or too costly to explore.

   优化器考虑可用访问路径、连接顺序、连接算法、聚合、排序、物化、并行与分布方案，同时剪除被视为劣势或探索代价过高的候选方案。

5. Cost, compare and produce a plan.估算成本、比较并生成计划。

   The cost model combines estimated operator work and child-plan cost, then the search retains promising subplans. A stopping rule or time budget eventually yields one executable plan.

   成本模型组合算子估算工作量与子计划成本，搜索过程保留更有希望的子计划；最终由停止条件或时间预算产出一个可执行计划。

Statistics Are Evidence, Not a Copy of the Data统计信息是证据，不是数据副本

A cost based optimizer cannot scan every table while planning. It relies on compact summaries: approximate table and index size, row and page counts, distinct values, null fraction, frequent values, ranges, histograms and sometimes correlations across columns. Constraints and partitions can add exact boundaries. Statistics are sampled or refreshed on a schedule in many systems, so even fresh values are still models.

基于成本的优化器不能在规划时扫描每张表，因此依赖紧凑摘要：近似的表与索引大小、行数与页数、不同值数量、NULL比例、高频值、范围、直方图，有时还包括跨列相关性。约束与分区可以提供精确边界。许多系统通过抽样或定期任务刷新统计信息，所以即使刚更新的数据仍然是模型。

Single-column histograms cannot describe every relationship. City and postal code, product and category, or tenant and status can be correlated. If the model multiplies independent selectivities for correlated conditions, row estimates can collapse too far or grow too large. Where supported, targeted multicolumn statistics may address a proven error; collecting every possible combination is impractical and adds maintenance cost.

单列直方图无法描述所有关系。城市与邮编、产品与类别、租户与状态都可能相关。如果模型把相关条件当作独立选择率相乘，行数估算可能过低或过高。在引擎支持时，可针对已证实的偏差创建多列统计信息；为所有列组合采集统计既不现实，也会增加维护成本。

A Query Plan Cost Is Not Elapsed Time查询计划成本不等于耗时

A cost model maps estimated work to a comparison value. Depending on the engine and operator, it can represent sequential and random page access, row and expression processing, index traversal, sorting, hashing, memory pressure, temporary I/O, parallel setup, data transfer or remote round trips. Some costs are configurable; others are internal. An upper plan node often includes the cost of its descendants.

成本模型把估算工作量映射为可比较数值。根据引擎与算子不同，它可能表示顺序与随机页访问、行和表达式处理、索引遍历、排序、哈希、内存压力、临时I/O、并行启动、数据传输或远程往返。有些成本可配置，有些是内部实现；上层计划节点通常包含其所有子节点成本。

• Startup cost estimates work before the first row can appear, such as building or sorting an input.启动成本估算首行输出前的工作，例如构建或排序输入。
• Total cost estimates work when the node is consumed to completion; a LIMIT or early stop can change which objective matters.总成本估算节点被完整消费时的工作；LIMIT或提前停止会改变重要目标。
• Cost units are meaningful mainly within one optimization context. Do not convert them to milliseconds or compare numbers across engines.成本单位主要只在同一优化上下文中有意义，不应直接换算成毫秒，也不应跨引擎比较。

Changing global cost constants is a high-impact action. A value that fixes one query may distort thousands of others. First prove that observed hardware, cache or network behavior materially contradicts the model, test a representative workload, document the scope and keep a rollback path.

修改全局成本常量是高影响操作。修复一个查询的值可能扭曲成千上万个其他查询。应先证明实际硬件、缓存或网络行为确实与模型严重不符，再测试代表性工作负载、记录作用范围并准备回滚路径。

Plan Enumeration Must Trade Search Quality for Planning Time计划枚举必须权衡搜索质量与规划时间

A query that joins several tables can have many legal join trees. Each tree may pair with multiple access paths, join algorithms, sort strategies, materialization choices and parallel or distributed variants. Exhaustive enumeration can become more expensive than executing the query. Practical optimizers therefore reuse best-known subplans, restrict transformations, prune dominated alternatives, apply heuristics, stop when improvement appears unlikely, or switch search strategies for very large joins.

一个连接多张表的查询可能拥有大量合法连接树，而每棵树又可搭配多个访问路径、连接算法、排序策略、物化方案，以及并行或分布式变体。穷举成本可能超过执行查询本身，所以实际优化器会复用已知最佳子计划、限制转换、剪除劣势方案、应用启发式方法、在改进希望较小时停止，或对超大连接切换搜索策略。

This explains why “the optimizer chose the lowest cost” really means the lowest estimated cost among candidates it retained, not the mathematical minimum over every imaginable plan. A promising plan may never be generated, or it may be pruned after an early cardinality error makes one subplan look expensive. More planning effort can improve choices but also increases compile latency and plan-cache pressure.

因此，“优化器选择了最低成本”实际表示“在保留的候选方案中选择最低估算成本”，而不是在所有可想象计划中求得数学最小值。一个有希望的计划可能从未生成，也可能因早期基数错误让某个子计划显得昂贵而被剪枝。增加规划工作量可能改善选择，却也会增加编译延迟与计划缓存压力。

How the CBO Chooses Access Paths, Join Order and AlgorithmsCBO如何选择访问路径、连接顺序与算法

No operator name is inherently good or bad. Join order controls intermediate result size; the join algorithm determines how those rows are combined; access paths determine how base rows arrive. Evaluate the complete plan with actual rows, loops, reads, spills, memory, network, waits and concurrency.

任何算子名称都不是天然好或坏。连接顺序控制中间结果规模，连接算法决定如何组合这些行，访问路径决定基础行如何到达。应结合实际行数、循环次数、读取、溢写、内存、网络、等待与并发评价完整计划。

Cost Based Optimization in DBMS: An Illustrative ExampleDBMS中的基于成本优化：假设示例

The following values are illustrative, not benchmark claims. Imagine a query joining `orders`, `customers` and `regions`. Statistics say `orders` has 20 million rows, a date predicate retains 5%, and a status predicate retains 10%. If the model assumes independence, it predicts 100,000 qualifying orders. It may choose a hash join with filtered orders as the build input, then join customers and regions.

以下数字仅为说明性示例，不是基准声明。假设一个查询连接`orders`、`customers`和`regions`。统计信息显示`orders`有两千万行，日期谓词保留5%，状态谓词保留10%。如果模型假设两个条件独立，就会预测十万条符合条件的订单，并可能选择以过滤后的订单为构建端进行哈希连接，再连接客户与区域。

Now suppose recent orders are disproportionately “open,” so both predicates together actually return 1.8 million rows. That eighteen-fold underestimate can make the hash table spill, change which input should build, inflate the next join and delay the first row. The SQL is unchanged and the selected plan was internally consistent with its estimate; the broken link is evidence about correlation.

但如果近期订单中“open”状态占比异常高，两个谓词组合后实际返回180万行，就出现十八倍低估。这可能导致哈希表溢写、改变适合的构建端、放大下一次连接并延迟首行。SQL没有变化，所选计划在原估算下也自洽；真正断裂的是描述相关性的证据链。

A disciplined response compares estimated and actual rows at the earliest divergence, checks statistic age and sampling, investigates skew and correlation, then tests a supported targeted statistic or query/design alternative. Forcing another join before understanding the estimate can hide the symptom for one parameter and fail for another.

严谨处理方式是：在最早偏离节点比较估算行与实际行，检查统计信息年龄和抽样，调查倾斜与相关性，再测试受支持的目标统计或查询、设计替代方案。在理解估算前强制另一种连接，可能只会掩盖某个参数的症状，却在其他参数上失败。

Why a Cost Based Optimizer Can Choose a Bad Plan为什么基于成本的优化器会选择错误计划

• Stale or missing statistics: table growth, bulk loads or changed distributions are absent from the model.统计信息陈旧或缺失：表增长、批量加载或分布变化没有进入模型。
• Skew and correlation: averages or independent-column assumptions miss hot values and coupled predicates.倾斜与相关性：平均值或列独立假设遗漏热点值与耦合谓词。
• Unknown expressions: functions, casts, variables or cross-column expressions may force fallback estimates.未知表达式：函数、类型转换、变量或跨列表达式可能迫使引擎采用默认估算。
• Parameter sensitivity: one cached plan is reused for values needing materially different access paths.参数敏感性：一个缓存计划被复用于需要显著不同访问路径的参数值。
• Search pruning: a useful plan is never generated or is removed after an early misestimate.搜索剪枝：有用计划从未生成，或因早期误估而被删除。
• Model mismatch: configured I/O, CPU, memory or network assumptions do not reflect the workload.模型不匹配：配置的I/O、CPU、内存或网络假设不能代表实际负载。
• Runtime change: concurrency, cache, spills, locks, remote availability or adaptive behavior differs from compilation time.运行条件变化：并发、缓存、溢写、锁、远端可用性或自适应行为与编译时不同。

A Repeatable Workflow for Diagnosing CBO Decisions诊断CBO决策的可重复工作流

1. Prove the result contract.证明结果契约。

   Fix expected row grain, duplicates, NULL behavior, ordering, precision, time boundaries and isolation before comparing speed.

   比较速度前先固定预期行粒度、重复、NULL行为、排序、精度、时间边界与隔离要求。

2. Locate the earliest material estimate error.定位最早的实质估算偏差。

   Read the plan from base access upward. Compare estimated and actual rows after accounting for loops; later errors may only be consequences.

   从基础访问向上阅读计划，结合循环次数比较估算行与实际行；后续错误可能只是结果。

3. Explain the estimate.解释估算来源。

   Check statistic freshness, frequent values, histogram boundaries, nulls, expressions, constraints, correlations and parameter visibility.

   检查统计信息新鲜度、高频值、直方图边界、NULL、表达式、约束、相关性与参数可见性。

4. Connect the error to the physical choice.把偏差连接到物理选择。

   State how the estimate affected a scan, join order, join algorithm, memory grant, parallelism or data movement. Avoid tuning unrelated nodes.

   说明该估算如何影响扫描、连接顺序、连接算法、内存授予、并行或数据移动，避免调优无关节点。

5. Test one supported remedy.一次测试一个受支持的处理方法。

   Candidates include refreshing or extending statistics, exposing a searchable predicate, changing a justified index or partition design, handling parameter classes, or applying a narrowly governed plan constraint.

   候选方法包括刷新或扩展统计信息、暴露可搜索谓词、修改有依据的索引或分区设计、处理参数类别，或应用范围受控的计划约束。

6. Validate and monitor.验证并监控。

   Compare results, plan shape, actual work, repeated latency, resources, concurrency and variance across representative parameter classes; define rollback thresholds.

   跨代表性参数类别比较结果、计划形态、实际工作量、重复延迟、资源、并发与波动，并定义回滚阈值。

Parameter Sensitivity Can Make One Good Plan Look Bad参数敏感性会让一个好计划看起来很差

A predicate value that returns one row and another that returns half a table may not share an efficient plan. Compilation may use a known literal, a parameter estimate, an average distribution or an unknown-value fallback. A cached plan can then serve later executions. Depending on the engine, mitigation may include recompile behavior, custom versus generic plans, parameter-sensitive plan features, query variants, filtered structures or plan governance.

一个返回一行的谓词值与一个返回半张表的值，可能无法共享高效计划。编译时可能使用已知字面值、参数估算、平均分布或未知值默认值，随后缓存计划又服务后续执行。根据引擎不同，处理手段可能包括重编译行为、自定义与通用计划、参数敏感计划功能、查询变体、过滤结构或计划治理。

Do not benchmark only the slow literal or only the average one. Define parameter classes such as frequent, rare, empty, recent-range, historical-range and tenant extremes. Record which plan each class receives and whether compile overhead, cache churn or throughput offsets latency gains.

不要只测试慢字面值，也不要只测试平均值。应定义高频、罕见、空结果、近期范围、历史范围与租户极端等参数类别，记录每类参数获得的计划，并判断编译开销、缓存抖动或吞吐变化是否抵消延迟收益。

Use Hints and Plan Baselines as Governed Constraints把提示与计划基线作为受治理约束

Hints, guides, baselines or forced plans can be valuable during an incident, a known regression or a tightly controlled workload. They are not free corrections to the cost model. A fixed access path can become wrong after data growth; a forced join can block a new optimizer improvement; a baseline can preserve a plan whose original assumptions no longer hold.

提示、计划指南、基线或强制计划，在事故、已知回归或严格控制的负载中可能有价值，但它们不是对成本模型的免费修复。固定访问路径可能在数据增长后失效，强制连接可能阻止新的优化器改进，计划基线也可能保留已经不再满足原假设的方案。

Before constraining a plan, document the observed failure, tested alternatives, supported syntax, exact version scope, parameter coverage, owner, monitoring signal, expiration or review date and rollback. Prefer correcting proven evidence or physical design when that remedy is safer and maintainable.

约束计划前，应记录观察到的失败、测试过的替代方案、受支持语法、准确版本范围、参数覆盖、负责人、监控信号、到期或复查日期与回滚方法。当修正已证实的证据或物理设计更安全且可维护时，应优先采用这些方式。

Validate More Than Estimated Cost验证内容不能只有估算成本

Cost is useful for explaining why the optimizer preferred one retained candidate. Actual evidence decides whether the plan meets the workload objective. Keep warm and cold cache behavior, client transfer time, concurrency and observation windows explicit instead of averaging them into one reassuring number.

成本有助于解释优化器为什么偏好某个保留候选方案；实际证据才决定计划是否满足工作负载目标。应明确区分冷热缓存、客户端传输时间、并发和观察窗口，而不是把它们平均成一个看似安心的数字。

Review SQL Structure Before Engine-Native CBO Testing在引擎原生CBO测试前审查SQL结构

Prepare sanitized complete SQL and identify its intended dialect. The visible InfiniSynapse SQL Complexity Checker analyzes structural patterns in the browser, including nested queries, CTEs, joins, window functions, aggregations and dialect-specific constructs. It can help prioritize which sections deserve human review or plan inspection.

请准备经过脱敏的完整SQL，并确认预期方言。InfiniSynapse SQL Complexity Checker的可见功能会在浏览器中分析嵌套查询、CTE、连接、窗口函数、聚合与方言特定结构等模式，帮助安排哪些片段应优先由人工审查或检查计划。

The checker does not execute SQL, inspect tables, indexes or statistics, enumerate physical plans, calculate an engine-native cost, or prove performance. A complexity score is a static heuristic, not a CBO result. After structural review, return to the target database for statistics, estimated and actual plans, representative parameters and controlled benchmarks. The existing InfiniSynapse SQL query optimization guide provides a broader practical tuning workflow, while the local query optimization guide explains the surrounding optimizer-to-validation lifecycle.

该检查器不会执行SQL，不会检查表、索引或统计信息，不会枚举物理计划，也不会计算引擎原生成本或证明性能。复杂度评分是静态启发式结果，不是CBO输出。完成结构审查后，应回到目标数据库获取统计信息、估算与实际计划、代表性参数和受控基准。现有InfiniSynapse SQL查询优化指南提供更广泛的实用调优流程，本地查询优化指南则解释从优化器到验证的完整生命周期。

Cost Based Optimizer FAQ基于成本的优化器常见问题

Official Cost Based Optimizer Sources基于成本的优化器官方来源

• Oracle query optimization overview: cost, selectivity, cardinality, access and joinsOracle查询优化概览：成本、选择率、基数、访问与连接
• Microsoft Learn: SQL Server query processing and cost-based plan selectionMicrosoft Learn：SQL Server查询处理与基于成本的计划选择
• Microsoft Learn: SQL Server cardinality estimation and histogramsMicrosoft Learn：SQL Server基数估算与直方图
• PostgreSQL documentation: EXPLAIN costs, rows and actual-plan caveatsPostgreSQL文档：EXPLAIN成本、行数与实际计划注意事项
• PostgreSQL documentation: planner statistics and multicolumn correlationPostgreSQL文档：规划器统计信息与多列相关性
• PostgreSQL documentation: planner cost constants and search configurationPostgreSQL文档：规划器成本常量与搜索配置
• MySQL Reference Manual: optimizer cost model and cost tablesMySQL参考手册：优化器成本模型与成本表

These first-party sources describe distinct products and current documentation families. Features, defaults, costs, commands and side effects vary by engine, edition and release. Verify the exact environment before applying statistics changes, hints, cost settings or actual-plan commands.

这些第一方来源描述不同产品及其当前文档体系。功能、默认值、成本、命令与副作用会随引擎、版本和发行版变化。在应用统计信息变更、提示、成本设置或实际计划命令前，必须核对准确环境。