6.2 成本模型与计划选择

6.2 成本模型与计划选择

本节摘要：成本模型是优化器的评分尺。SQLite 的模型以"估算页访问次数"为主轴，PostgreSQL 显式给随机读定四倍于顺序读的价，MySQL 混合读页与比较的代价。本节拆开三把尺子的构造，解释估算行数从哪来、什么时候失真，并给出让尺子保持准确的运维习惯。

三把尺子的构造

三种成本模型的哲学差异，可以用它们"最害怕什么"来概括：

SQLite 害怕页访问。它的成本近似等于预期的页读取次数：全扫按"行数除以每页行数"估页数；索引查找按 log 级估算树高加命中页；回表按命中行数乘最坏随机性估。模型小到可以装进脑子——这正是嵌入式取舍：成本模型简单到不会失控，宁可粗糙不可复杂。

PostgreSQL 害怕随机 IO。顺序读一页 cost=1.0，随机读一页 cost=4.0（可调参数），再叠加元组处理与比较的 CPU 因子。这个"随机四倍价"显式编码了机械盘时代的世界观——SSD 上它显得保守，这也是 PG 优化器在闪存上常被抱怨"太早放弃索引"的根源。它的对策是把随机页因子调低，或依赖更聪明的统计。

MySQL 害怕算不准。InnoDB 的代价模型在 8.0 里引入了基于直方图与统计的代价框架，混合页读取、行比较、内存排序的权值。它的特色是"计划可以由 hint 直接改判"——三个引擎里 MySQL 的 hint 体系最重，本质是对模型不自信的补偿。

估算行数：账本从哪来

成本模型的每个数字都源自"这个条件命中几行"的估算。第 5 章讲过 sqlite_stat1 的结构，这里补全它的用法闭环：

ANALYZE;
-- stat: events idx_dev_ts  10000000 25 1
-- 含义：1000 万行；按 device 前缀平均命中 25 行；
--       按 device 加 ts 前缀平均命中 1 行
​

优化器用它回答三个问题：这个索引值得走吗（命中数乘回表价 与 全扫页数比）；用前缀还是全键（两个前缀的命中数比）；连接时哪层驱动哪层（中间结果规模相乘）。没有 ANALYZE 的库，这些数全是默认假设——SQLite 对无统计的索引按对数递减猜行数， PostgreSQL 回到固定选择率（等值 0.005 之类），MySQL 用持久统计兜底。三种兜底都不如新鲜统计可靠。

失真的三种典型场景

场景一：偏斜分布。status 列 99% 的行是 0，查询 WHERE status = 1 想要的那 1%。sqlite_stat1 只存平均命中数，优化器按平均数选了索引，实际却要扫索引里几乎所有条目。PostgreSQL 的 MCV 直方图能识别这类高频值并纠正计划；SQLite 的解法在应用层——用 PRAGMA optimize 的思路定期 ANALYZE，或把热点值改写成排除式条件（status != 0 反而能让优化器看到选择性）。

场景二：统计过期。大批量导入后不跑 ANALYZE，行数估算停在旧世界。典型症状：导入后同一个查询计划突变。把 ANALYZE 挂进导入流程的收尾步骤，是三个引擎通用的纪律（PG 的 autovacuum 里 analyze 也好、MySQL 的自动重统计也好，批量大导入都建议手动补一次）。

场景三：超出行宽假设。行宽变大（加了宽列）后每页行数下降，旧统计的"每页行数"不再成立，全扫成本被低估。对策同上：导入与结构变更后 ANALYZE。

💡 关键直觉：优化器不是在优化查询，而是在优化它对查询的想象。统计信息就是想象力的边界——你的职责是让边界贴近现实。

对照实验：一把尺子两次称重

把同一查询放在"有统计"与"无统计"的两个库上称重：

-- 库 A：从未 ANALYZE；库 B：导入后 ANALYZE 过
EXPLAIN QUERY PLAN
SELECT * FROM events WHERE device = 'sensor-07' AND ts > 1725600000;
-- 库 A 可能输出：SCAN events（按默认假设猜索引不划算）
-- 库 B 大概率输出：SEARCH events USING INDEX idx_dev_ts
​

一次 ANALYZE 前后的计划差异，是检验"成本模型如何思考"最直观的实验。做完这个实验再回头看 5.2 的计划解读四步法，你会发现自己已经能预判每一步的改判理由——这就是把黑盒读成说明书的过程。

常见问题速答

**统计信息会自动更新吗？**SQLite 没有后台线程，永远不会自动 ANALYZE——这是它与服务端最本质的运维差异之一（PostgreSQL 有 autovacuum 顺带更新统计，MySQL 有自动重统计触发器）。手动纪律：批量数据操作后 ANALYZE；3.46 前的版本可用 PRAGMA optimize 在连接关闭时按需增量更新。把 ANALYZE 挂进数据流水线的收尾步骤，比记住"该跑一次"可靠得多。

**为什么同样的查询，开发环境快、生产环境慢？**把三样东西摆到一起对比就能定位：两边的 sqlite_stat1 内容、两边的 EXPLAIN QUERY PLAN 输出、两边的库规模。九成情况答案是统计差异——开发库是抽样的小数据，统计干净且规模小，计划怎么选都快；生产库数据真实分布加统计过期，计划分道扬镳。做法：从生产导出 sqlite_stat1 内容（它是普通表）在开发环境复现同款计划，调优后再把改写带回生产验证。

本节要点回顾

• SQLite 的尺子是页访问次数，PostgreSQL 显式罚随机 IO 四倍，MySQL 混合多种代价——尺子不同，同一条查询的最优计划可能不同。
• 行数估算全部来自统计：没有 ANALYZE 的库，优化器在用默认假设写小说。
• 三种失真要记住：偏斜分布、统计过期、行宽假设漂移；前两种靠 ANALYZE 纪律，第一种在 SQLite 里要靠改写补偿。
• SQLite 不做运行时反馈，计划干预靠 CROSS JOIN 与 INDEXED BY；MySQL 与 PostgreSQL 各有 hint 体系。

尺子和套路都齐了，下一节盘点优化器内置的重写技术，看它平时都自动帮你做哪些题。