同一个 SELECT,跑了 0.4 秒;换一行 WHERE 条件,变成 47 秒。这不是玄学,是数据库查询优化器的代价模型在两种场景下给出了完全不同的执行计划。前者走索引扫描,后者掉进全表扫描的坑,差别就是 100 倍起跳。
查询优化器是数据库内核里最容易被低估的一段代码。它不直接读数据,不直接写数据,但它决定了数据库的命脉——同样的硬件、同样的数据、同样的 SQL,优化器聪明还是笨,直接决定你的应用是秒回还是卡死。市面上所有主流数据库(PostgreSQL、MySQL、Oracle、SQL Server、TiDB、ClickHouse)的内核里都有它,体积往往不到整个数据库的 5%,但工程难度排前三。
一、代价模型:数据库的”成本会计师”
优化器的核心是代价模型。它把每一种可能的执行方案翻译成数字——”扫描多少页磁盘、读多少行到内存、用多少 CPU 做比较”,然后挑总代价最低的那个方案。听起来像会计做账,但工程细节远比会计复杂,因为真实的代价模型要在几百种算子组合里找出最优解,而 SQL JOIN 一多,组合数就爆炸。
主流代价模型分两派:基于统计信息的(cost-based)和基于规则的(rule-based)。基于规则的年代早就过去了(Oracle 10g 起也基本切换到代价模型),现在所有主流数据库都用代价模型。代价模型要回答三个核心问题:扫描多少行、每行多大、用哪个算子。
代价模型最大的坑,是它对真实数据的”估计”和真实代价的偏差。偏差越大,优化器越可能选错执行计划,而选错执行计划的代价,经常是几倍到几百倍——这就是为什么你会看到同一个查询,跑出 10 倍甚至 100 倍的时间差。
二、基数估计:代价模型的阿喀琉斯之踵
代价模型里最薄弱的环节,是基数估计(cardinality estimation)——也就是估算”这一步会跑出多少行”。优化器对基数的估计,直接决定了它选嵌套循环 JOIN 还是哈希 JOIN,选索引扫描还是全表扫描。
数据库怎么估计基数?老办法是直方图(histogram):把每列的值分桶,记录每个桶里有多少行。比如 age 列有 100 万行,直方图告诉你 20-30 岁这桶有 12 万行。优化器拿到 WHERE age BETWEEN 20 AND 30,就估计这步扫描 12 万行。
问题来了:直方图只对单列条件准,一旦条件组合,优化器就抓瞎。比如 age BETWEEN 20 AND 30 AND gender = ‘M’,直方图没有”20-30 岁男性”的联合桶,优化器只能假设两个条件独立——把 12 万乘以男性比例 50% = 6 万行。但真实数据可能高度相关:30 岁男性的配偶 30 岁,30 岁女性的同事也是 30 岁,联合分布可能严重偏离独立假设。优化器估 6 万行,实际跑出 50 万行,代价模型彻底崩盘。
PostgreSQL 14 开始引入”扩展统计”(CREATE STATISTICS),允许 DBA 手动声明多列相关性,让优化器拿到联合分布信息。但默认不开——绝大多数 PostgreSQL 实例都没建过 CREATE STATISTICS,代价模型一直处于”独立假设”的裸奔状态。
三、代价模型的算子战争:Nested Loop / Hash / Merge
代价模型把所有可能的算子组合都翻译成数字。最经典的战场是 JOIN 算子——三巨头:Nested Loop、Hash Join、Merge Join。
Nested Loop 是双层 for 循环:外层逐行扫描驱动表,每行在内层查被驱动表。优点是实现简单、不需要预排序,对小数据量极快。代价是 O(M×N),M 和 N 是两张表的行数。如果驱动表 100 行、被驱动表 100 万行有索引,代价就是 100 次索引查找——秒回。但如果你没建索引,代价就变成 100 × 100 万 = 1 亿次磁盘读,几十分钟起跳。
Hash Join 是建哈希表的玩法:选一张较小的表,在内存里建哈希表,然后扫描大表,每行去哈希表里查匹配。代价主要是建哈希表的时间和探测的时间。优点是对大数据集极快,缺点是要把整张表加载到内存(work_mem 不够就溢出到磁盘,变成”grace hash join”,慢得让人想哭)。
Merge Join 是排序归并:两张表都按 JOIN 字段排序,然后双指针线性扫一遍。代价主要是排序的代价 O(M log M + N log N),匹配阶段是 O(M+N)。当数据已经有序(比如有索引)或 join 字段有强相关性,Merge Join 是最优解。当数据完全乱序,排序本身就成了瓶颈。
代价模型选哪个?PostgreSQL 的偏好是”小表驱动大表走 Nested Loop,大数据集走 Hash Join,数据有序走 Merge Join”。但这是经验法则,真实场景里 Hash Join 经常输给 Nested Loop——只要大表有索引,Nested Loop 利用索引的代价可能比 Hash Join 建哈希表的代价低 10 倍。
四、统计信息过期:优化器被骗的日常
代价模型依赖统计信息,统计信息依赖 ANALYZE(在 PostgreSQL 里是 ANALYZE,MySQL 里是 ANALYZE TABLE,Oracle 里是 DBMS_STATS.GATHER_)。但 ANALYZE 不会实时跑——它是定时任务,默认每周或每天跑一次,数据量大时甚至更久。
统计信息过期时,优化器会基于旧的直方图做决策,代价估计直接偏差 10-100 倍。最经典的场景:某张订单表昨晚插入了 500 万行新数据,ANALYZE 还是上周末跑的,直方图显示订单表有 100 万行。优化器按 100 万行算 Hash Join 的代价,选了这个方案——但真实数据是 600 万行,Hash Join 在 work_mem 不够时溢出到磁盘,跑 40 分钟。如果优化器知道真实数据量,它会选 Nested Loop + 索引,5 秒搞定。
这个坑在生产环境太常见了,所以 DBA 圈有个共识:”统计信息就是优化器的眼睛,眼睛瞎了它就是个白痴”。临时解决方案是手动 ANALYZE 一下,长期方案是把 ANALYZE 接入实时数据变更管道(MySQL 的 InnoDB 统计信息部分会自动更新,PostgreSQL 需要配置 autovacuum_analyze_scale_factor 调小阈值)。
五、执行计划的”岔路口”:为什么同一个 SQL 跑出两种结果
优化器给的执行计划不是”一个最优解”,而是”基于当前统计信息和代价模型的最优解”。当你跑 EXPLAIN ANALYZE 时,你看到的是优化器的决策——但旁边那栏”actual rows”(真实行数)和”estimated rows”(估计行数)的差距,就是代价模型说谎的现场。
真实行数和估计行数偏差 10 倍以上,基本可以判定优化器选错了执行计划。这时候你有两个选择:一是手动 ANALYZE 更新统计信息让优化器重新决策,二是用 SQL hint(在 MySQL 里是 /*+ INDEX(t idx_name) */,在 Oracle 里是 /*+ USE_NL(t) */)强行指定优化器走某个路径。前者是治本,后者是治标——治标只能解一时,数据一变又会选错。
PostgreSQL 13 之前不支持原生 SQL hint,只能通过 pg_hint_plan 扩展绕道。PostgreSQL 13 起官方开了 planner hinting 讨论,但至今没有官方 hint 语法——这是 PostgreSQL 社区的”哲学坚持”,认为优化器应该自己变聪明而不是被强制指挥。
六、自适应查询:代价模型从静态到动态
代价模型一直有个硬伤:它是基于”事前估算”的,跑起来才发现估算错就晚了。新一代数据库开始做自适应查询(adaptive query processing)——跑的过程中根据实际数据修正后续算子的选择。
TiDB 的 Cascades Planner 和 Volcano 模型让代价模型在执行阶段也能重新评估。Snowflake 的 Dynamic Result Cache 让重复查询跳过整个优化器。Microsoft SQL Server 的 Adaptive Joins 在 Nested Loop 和 Hash Join 之间动态切换——跑到一半发现行数爆了,自动从 Nested Loop 切换到 Hash Join。
ClickHouse 走另一条路:它默认不做 JOIN 优化,鼓励用户预先做反范式化,把代价模型踢给应用层。这种”砍掉代价模型”的极端做法在 OLAP 场景特别有效,因为 OLAP 查询经常涉及几十亿行,任何代价模型都估不准,不如直接全表扫 + 列式压缩 + SIMD。
七、代价模型的”硬骨头”:连接顺序的组合爆炸
代价模型最难的不是单个算子,是怎么把多个 JOIN 排序。3 张表 JOIN,排列有 3 种;4 张表有 12 种;10 张表有 360 万种。代价模型必须在这几百万种方案里找出最优解,而且每种方案都要算代价。
经典解法是动态规划:从两表 JOIN 开始,往上加表,记录每一步的最优解。这个算法叫 Selinger 算法(IBM 1979 年发明的),现在所有主流数据库的优化器核心都在用它。但动态规划的问题是状态空间爆炸——10 张表就是 360 万种方案,内存根本装不下。所以现代优化器会在搜索到一定程度后切换到启发式贪心,放弃全局最优。
新一代优化器(Apache Calcite、Orca、Greenplum 的 GPORCA)用 Cascades 框架替代 Selinger,核心思想是把代价模型和搜索分离,代价模型变差时不会拖累搜索速度。GPORCA 是 Greenplum 写的开源优化器,被用于 Greenplum、HAWQ、Apache HAWQ 等分布式数据库——它在 100 张表 JOIN 的场景下仍能在秒级返回执行计划。
八、代价模型的下一个十年:学习型优化器
代价模型的下一个突破口,是机器学习。传统的代价模型是”人工建模 + 统计信息”,新一代学习型优化器(learned query optimizer)用神经网络直接从历史查询里学代价。
典型代表:Google 的 Bao(2021 SIGMOD)让 PostgreSQL 的优化器在每次查询后被”奖励 / 惩罚”,跑了几千次后,它学会了在特定数据分布下选 Nested Loop 而不是 Hash Join。MIT 的 Neo(2019)直接把整个优化器换成神经网络,在 JOB-light 基准测试上比传统优化器快 2-3 倍。
学习型优化器的硬骨头,是泛化能力。训练好的模型在新数据集上经常失效——这就是为什么学习型优化器还没法取代传统代价模型,而是作为”补丁”存在:代价模型给一个候选执行计划,学习型优化器再过一遍微调。两者结合才是真未来。
它意味着什么
代价模型不是数据库内核里最炫的部分,但它是决定 90% 性能问题的”幕后黑手”。理解代价模型,你就能看懂 EXPLAIN ANALYZE 输出里”actual rows vs estimated rows”那一栏,就知道什么时候该 ANALYZE,什么时候该建索引,什么时候该拆 SQL。
代价模型的下一步是自适应和学习,但短期内不会取代人工调优。理解它的代价、偏差和适用范围,本身就是资深 DBA 和后端工程师的核心能力——这一能力,比任何 ORM 和任何花哨的 ORM 框架都更能决定一个系统在生产环境能不能跑稳。