Sherfiter's wiki for fun

第 9 章 · 索引与查询优化

讲一个我亲眼所见的最划算的技术决策。一张千万行的订单表,运营的一个统计查询要跑半分钟,业务方在催。加了一行 DDL:

CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);

同一个查询从 28 秒变成 6 毫秒,四千多倍的提速,成本是几 GB 磁盘和一点点写入开销。这辈子你可能写不出多少行「性价比超过这行 SQL」的代码,而理解它为什么有效,正是本章的目的:索引用空间和写入代价,把全表扫描换成树上的几次定位;查询优化就是让每条 SQL 都「落在索引上」。学完这章,前面所有章节里「连接太贵」「除法怎么算快」的性能伏笔,全部兑现。

9.1 没有索引的世界:全表扫描

无索引的等值查找只能从头读到尾,一亿行的表,平均读五千万行。磁盘时代这是灾难(随机 IO 按毫秒算),即便如今内存便宜,全扫也是「数据量 × 常数」的硬开销。索引的本质:对「某几列的值」预先建立有序结构,查找从「翻全书」变成「查字典」。

9.2 B+ 树:为磁盘而生的字典

数据库的默认索引是 B+ 树,先看结构:

                 [ 40 | 80 ]                ← 根(非叶子,只存键+路标)
               /     |      \
        [10|25]   [55|70]   [90|99]         ← 中间层
        /  |  \    ...
叶子: 5→10→25→40→55→70→80→90→99          ← 有序双向链表,数据(或主键)在这层

它为磁盘优化的两个细节值得单独点名。矮胖:每个节点(一页,16KB)塞几百个键,百万行数据的树高只有 34 层,一次查找 = 34 次页访问(根常驻内存,实际磁盘 IO 更少)。对比二叉平衡树,千万数据高 23 层,每层一次 IO,直接出局。叶子链表:所有叶子串成有序链表,范围查询(WHERE created_at BETWEEN ...)找到起点后顺着链扫就行,不用回树上蹿下跳。

哈希索引则把键散列到桶,等值查找 O(1) 理论更快,但天生残废:范围、排序、最左前缀全不支持(散列后顺序信息没了)。所以主流 OLTP 数据库(InnoDB)默认 B+ 树,哈希只在内存引擎和「纯等值」场景露脸。

9.3 聚簇与回表:InnoDB 的关键内幕

InnoDB 里「索引」和「数据」的关系有两层,理解它们,一半的索引疑难杂症自动解开:

聚簇索引:主键索引的叶子存的是整行数据,表本身就是按主键排好序的一棵 B+ 树。所以「主键查询」直接在树里摸到数据,没有中间商;「按主键范围扫」也是顺序 IO,快。一张表只能有一个聚簇索引(数据只能按一种顺序物理摆放)。

二级索引(给 name、created_at 建的):叶子存「索引键 + 主键值」。查到之后如果还要别的列,得拿主键回表(再去聚簇索引里摸整行)。回表次数 = 命中的行数,这就是为什么 SELECT * 配二级索引又慢又蠢:每行都要回一次。

由这两个结构推出几条铁律:主键要短(二级索引的每个叶子都藏着主键,主键 36 字节 UUID,千万行的二级索引白白膨胀几百 MB)、主键要递增(插入永远发生在树的最右侧,顺序写;随机的 UUID 主键让插入位置满天飞,页频繁分裂,写入性能腰斩)、「能用主键查就别用二级」。

9.4 联合索引与最左前缀:列顺序就是检索逻辑

给 (dept, age) 建联合索引,树里的排序规则是:先按 dept 排,dept 相同再按 age 排,类似「先按姓氏排、同姓按名字排」的电话簿。于是:

WHERE dept = 'CS'                 -- 用上(电话簿查姓)
WHERE dept = 'CS' AND age > 20    -- 用上,且 age 在 dept 内有序,范围扫描
WHERE age > 20                    -- 用不上!电话簿按姓排,不看姓找年龄只能全书翻

「必须从最左列开始连续命中」就是最左前缀原则。它直接给出建索引的决策顺序:等值条件列放前,范围条件列放最后,顺手把要 SELECT 的列挂在后面。最后一手叫覆盖索引:SELECT dept, age FROM student WHERE dept = 'CS' 所需列全在索引里,叶子直接出结果,零回表,EXPLAIN 的 Extra 列会显示 Using index——这是加索引能拿到的最高性价比形态。

9.5 EXPLAIN:别猜,看执行计划

优化器对每条 SQL 会自己选索引、排连接顺序,你猜的一律不算数,跑一遍:

mysql> EXPLAIN SELECT * FROM orders WHERE user_id = 42;
+----+-------+------+---------+---------+------+--------+-------------+
| id | table | type | key     | key_len | rows | filtered | Extra      |
+----+-------+------+---------+---------+------+--------+-------------+
|  1 | orders| ref  | idx_... | 8       |  137 |  100.00 | Using index |

必看的四列:type(访问类型,从好到坏 const > eq_ref > ref > range > index > ALL,ALL = 全表扫描,OLTP 查询里见到就红灯)、key(实际用的索引,NULL 即没用上)、rows(预估扫描行数,百万级要警惕)、Extra(Using index 好事,覆盖索引;Using filesort 要警惕,排序没吃到索引;Using temporary 更要警惕,临时表,常见于 GROUP BY 没吃到索引)。

慢查询的排查流水线也顺势定型:慢日志抓 SQL → EXPLAIN 看计划 → 缺索引补索引(按 9.4 的顺序)、索引不中被改写 SQL(函数包列 WHERE YEAR(created_at)=2024 会让索引失效,改成范围条件)→ 再不行才谈分库分表。九成慢查询死在「没有索引」和「索引失效」两关,别一上来就想架构。

常见错误

  • 给每个查询都单建一个单列索引。十个查询十个索引,写入每次要维护十棵树;先按业务找高频组合,建联合索引,一顶几。
  • 在区分度低的列上建索引(如性别)。B+ 树每个值对应半张表,优化器干脆不选它,白占空间。区分度(不重复值数 / 总行数)低的列,考虑联合索引里的陪跑位或干脆不建。
  • WHERE 里对索引列做函数或运算(WHERE id + 1 = 42)。索引存的是原始值,加工后的值在树里无序可循,直接失效;把运算挪到常量一侧(WHERE id = 41)。

练习

  1. 手算:B+ 树节点 16KB、键 + 指针 12 字节/项,一个节点约存多少键?三层树(根-中-叶)最多索引多少行?算出「为什么千万行的表 3 次够了」。
  2. 手算:表 orders(id 主键, user_id, amount, created_at),查询「某用户最近 30 天订单总金额」。写出覆盖索引的定义和 SQL,并用最左前缀原则解释列顺序为什么是 (user_id, created_at)。
  3. 动手:在 SQLite 建 10 万行表(用 Python 循环插),分别给「带索引」和「无索引」的列做等值查询,计时对比;再建 (a,b) 联合索引,试 WHERE b=... 单条件,用 EXPLAIN QUERY PLAN 看它是否用上索引。
  4. 思考:UUID 主键和自增主键,除了写入页分裂,还有哪些连锁差异?(提示:9.3 说过二级索引叶子藏主键;再想想分布式系统为什么偏爱 UUID,以及「雪花算法」试图兼得什么。)
豫ICP备2026027944号