第5章 索引原理与优化

第5章 · 索引原理与优化

章节摘要：第 2 章算清了树的账，本章把账用在决策上：索引到底怎么把查询变快、什么情况下反而变慢。我们画出索引 B-Tree 的查找路径，给出选择性、统计信息与 EXPLAIN QUERY PLAN 的完整解读流程，讲透覆盖索引与表达式索引两类进阶形态，最后对照 SQLite FTS5、MySQL 全文索引与 PostgreSQL GIN 三种倒排方案。读完本章，你应当能对任何一条慢查询说出"该建什么索引、为什么、代价是什么"。

学习目标

读完本章，你应当能够：

1. 描述索引 B-Tree 叶子页与表叶子页的内容差异，解释"回表"在 SQLite 里对应的两次树查找。
2. 用选择性公式（区分值数除以总行数）评估一列该不该建索引，并用 ANALYZE 后的 sqlite_stat1 内容验证判断。
3. 逐行解读 EXPLAIN QUERY PLAN 的输出：SCAN 与 SEARCH 的区别、USING INDEX 的含义、嵌套循环的顺序。
4. 判断一条查询能否被覆盖索引加速，会写 INCLUDE 式的表达式索引与部分索引。
5. 对照三种全文索引方案的存储与查询模型，为"站内搜索"类需求选型。

核心概念速览

一句金句：索引不是越多越好——每加一个索引，读路径多一条捷径，写路径多一棵必维护的树；好的索引设计是"用最少的树服务最多的查询"。

子章节导航

5.1 索引是怎么工作的：索引树与表树的内容差异、点查与范围扫描的完整路径、ORDER BY 消除与 WITHOUT ROWID 表，从机制上理解"索引为什么快"，并给出复合索引列顺序的黄金法则。

5.2 选择性与查询计划解读：选择性的算术、sqlite_stat1 的读法、EXPLAIN QUERY PLAN 逐行精读，以及小表全扫反而更快的成本逻辑——配一条完整的计划树成本对比，让"该不该建索引"变成可计算的题。

5.3 全文与倒排对照：LIKE 为什么用不上普通索引，FTS5 的倒排与分词，MySQL ngram 与 PostgreSQL GIN 的对应方案，中文分词的三库短板与应对，三库全文选型对照表。

子章节之间的逻辑关系

三节从"机制"走到"度量"再走到"特化"。5.1 给出索引加速的微观原因（树矮、回表、排序消除），5.2 提供判断工具（选择性与计划解读），5.3 把普通 B-Tree 索引覆盖不了的全文场景单独拉出来对照。三者共享同一个决策框架——先看计划，再算选择性，最后动手建索引：

5.1 索引机制 ──▶ 5.2 选择性与计划 ──▶ 5.3 全文特化场景
    为什么快        该不该建               B-Tree 之外的树
                        │
                        ▼
          第6章：优化器就是用本章的概念做选择题
​

前置知识与后续延伸

前置：第 2 章的叶子页布局与 rowid 概念（索引叶子里存的就是它）；第 3 章的 EXPLAIN 读法。

后续延伸：第 6 章的优化器用选择性与行数估算驱动成本模型——本章的 ANALYZE 正是给它的"情报供给"。第 7 章的页缓存命中率直接决定索引查找的实际延迟。带着问题读：为什么说"覆盖索引是 SQLite 上最接近服务端 Index-Only Scan 的武器"？

本章的动手底线：三节读完，你应当亲手完成一次闭环——给一张真实表建一个复合索引、跑 ANALYZE、用 EXPLAIN QUERY PLAN 对比前后的计划变化，再用 dbstat 看一眼索引占了多少页。没有做过这四步，选择性公式和计划读法都只是纸面知识。