一、从「翻完整本书」到「一次定位」

一张 20 万行的表，按 k 找一个值。 没有索引时，数据库只能从头扫到尾，执行计划里写的就是那个词：

SCAN t

建上索引再跑同一条语句，计划变成：

SEARCH t USING INDEX ix_t_k (k=?)

SCAN 是扫、SEARCH 是定位。 差的不是「快一点」， 是「换了一种找法」——从「每行比一次」变成「沿着树往下走几层」。

约 4711 倍。 这两个数的口径要说清楚：单次都太快，计时器抖得厉害， 所以两边都是跑很多次取平均——不是「测一次就下结论」。

二、索引的优点：快在哪，代价在哪

教材列了三条优点，说到底都归到一件事：少做无用功。

第四条容易被忽略：索引不只是「查得快」，还是「约束」。 唯一索引让数据库替你守住「这一列不许重复」—— 这件事靠应用层代码是守不住的（两个人同时插同一个值，代码都判断「不存在」）。 代价也有两条，而且都躲不掉： 一是每次增删改，索引都得跟着改；二是索引自己占磁盘。 一张表建五个索引，写入就是五份维护开销。 所以「索引越多越好」是错的。 后面 14.2 会给出判断标准， 这一节先记住那句账：读省下来的时间，是从写上挪过去的。

三、索引的分类

「覆盖索引」不是一种特殊的索引，是一种「用法」。 任何索引，只要你要的列恰好都在里面，它就是覆盖索引——因为它不用回表，所以特别快。 这也是优化查询的一个常见手法： 把 SELECT * 改成只取索引里那几列， 同一张表、同一个索引，速度能差一截。

四、聚集索引与非聚集索引：一张表只有一种排法

理解它的关键是「一张表只能有一种物理顺序」。 行在磁盘上排成一条， 你按 id 排了，就不可能同时按 姓名 排——所以聚集索引只能有一个。 非聚集索引是另一码事： 它是一本「键 → 行在哪」的小册子， 册子可以有很多本，但每本都得回表去取真正的行。 「回表」就是这个意思：先查册子拿到位置，再按位置去表里取一次。 SQL Server 里主键默认建聚集索引（除非你写明 NONCLUSTERED）， 所以「主键就是聚集索引」在多数表上成立。 SQLite 里 INTEGER PRIMARY KEY 是 rowid 的别名， 行就按它排——效果上很接近聚集索引，但它没有「聚集/非聚集」这个说法。 这是硬差异，别硬套。

五、真测代价：插 2 万行，多花 47%

同一张表，插 2 万行，一次不带索引、一次带一个索引：

多花 47%。 只加了一个索引就这样，加五个大概就是「写入翻倍」的量级。 所以「给每个字段都建索引」是最典型的过度设计。 该建的是「经常用来查、不同取值多」的列，别的都算浪费。 提醒一句口径： 这个 47% 是本地内存库、批量插入、单索引的数字。 换成真磁盘、单条插入、五个索引，比例会难看很多。 要记住的是「方向」，不是「47% 这个数」。

六、动手 Demo：从 18.744 毫秒到 0.0040 毫秒

这一段建一张 20 万行的表，先在没索引的情况下按 k 查一行、计时， 再建索引、用同一条语句再测一次，最后测「加索引之后写变慢多少」。 每一侧都跑很多次取平均——单次十几毫秒的数，测一次不算数。

import sqlite3, time

c = sqlite3.connect(u":memory:", isolation_level=None)
c.execute(u"CREATE TABLE t (id INTEGER PRIMARY KEY, k INTEGER,"
          u" name TEXT, v REAL)")
c.execute(u"WITH RECURSIVE x(a) AS (SELECT 1 UNION ALL"
          u" SELECT a + 1 FROM x WHERE a < 200000)"
          u" INSERT INTO t SELECT a, (a * 7919) % 200000,"
          u" 'name' || a, a * 1.5 FROM x")

q = u"SELECT name, v FROM t WHERE k = 12345"


def batch(n):
    for _ in range(n):
        c.execute(q).fetchall()


print(u"计划", [r[3] for r in c.execute(u"EXPLAIN QUERY PLAN " + q)])
t1 = clock(lambda: batch(100)) / 100      # 没索引：跑 100 次

c.execute(u"CREATE INDEX ix_t_k ON t(k)")
print(u"计划", [r[3] for r in c.execute(u"EXPLAIN QUERY PLAN " + q)])
t2 = clock(lambda: batch(5000)) / 5000    # 有索引：跑 5000 次

print(u"没索引 %.3f ms，有索引 %.4f ms，快 %.0f 倍"
      % (t1, t2, t1 / t2))

实跑输出：

--------------------------------------------------------------------
索引是什么：一次定位还是翻完整本书
--------------------------------------------------------------------


  ① 没有索引：翻完整本书
  --------------------------------------------------------------------
  20 万行的表，按 k 找一行。k 是随便算出来的数，没有排过序。

  执行计划：
    SCAN t
  SCAN t 就是「从头扫到尾」。20 万行，每行比一次，找到为止——找不到就扫完。
  单次才十几毫秒，计时器抖得厉害，所以下面一律跑多次取平均。
  平均一次 18.744 毫秒。

  ② 加上索引：一次定位
  --------------------------------------------------------------------

  1> CREATE INDEX ix_t_k ON t(k)

  执行计划：
    SEARCH t USING INDEX ix_t_k (k=?)
  SEARCH t USING INDEX ix_t_k 就是「按索引定位」。同一个查询，平均一次 0.0040 毫秒。
  有索引这一侧跑 5000 次取平均——单次已经短到计时器几乎看不见了。
| 20 万行，按 k 查一行 | 平均一次 |
|---|---:|
| 没有索引（SCAN，100 次） | 18.744 ms |
| 有索引（SEARCH，5000 次） | 0.0040 ms |

  快了约 4711 倍。这就是索引最直观的价值——它是「书的目录」，不是「把书变薄」。

  ③ 代价：写入变慢、空间变大
  --------------------------------------------------------------------
  索引不是白来的。每次 INSERT／UPDATE／DELETE，索引也得跟着改。
| 插 2 万行 | 最快一次 |
|---|---:|
| 没有索引 | 31.4 ms |
| 有一个索引 | 46.2 ms |

  多花 47%。而且索引本身还占磁盘——索引越多，写入越慢、空间越大。
  ! 所以索引不是越多越好。「每个字段都建一个」是最典型的过度设计。

  ④ 索引的分类
  --------------------------------------------------------------------
| 分法 | 类型 | 说明 |
|---|---|---|
| 按存储 | 聚集索引 | 行数据本身就按这个键排（一本书只有一种排法） |
|  | 非聚集索引 | 另建一份「键 → 行位置」的表（可以有很多个） |
| 按唯一性 | 唯一索引 | 键值不许重复，主键默认就是 |
| 按列数 | 单列索引 / 组合索引 | 组合索引的列顺序很关键 |
| 按用途 | 覆盖索引 | 要查的列都在索引里，不用回表 |

  聚集索引决定「行在磁盘上怎么排」，所以一张表只能有一个——物理上只能有一种顺序。

  ⑤ 聚集与非聚集：一张表只有一种排法
  --------------------------------------------------------------------
| 对比 | 聚集索引 | 非聚集索引 |
|---|---|---|
| 数量 | 每表最多 1 个 | 可以很多个 |
| 存什么 | 整行数据 | 键值 + 行定位符 |
| 查到之后 | 直接就是数据 | 可能还要「回表」再查一次 |
| 典型 | 主键 | 经常用来查的列 |

  「回表」这个词要理解：非聚集索引里只有键和位置，要的列不在索引里，就得按位置再去表里取一次。
  所以「把要查的列都放进索引」，查询就只需要读索引——这叫覆盖索引，是常见的提速手段。

同一句话，两次计划不一样： 第一次是 SCAN t，第二次是SEARCH t USING INDEX ix_t_k (k=?)。 索引改变了「怎么找」，没有改变「找什么」。 两侧跑的次数不一样不是作弊，是必须。 有索引那一侧单次只有几微秒， 跑 100 次连 1 毫秒都不到，计时器根本分辨不出来——所以要多跑，让总时间落到能量准的量级上。 「快了约 4711 倍」这个数的意义在于数量级，不在于精确值。换台机器、换个表大小， 它会变成 3000 或 6000，但「三个数量级」这个结论不会变。 第三个现象更值得记：插入变慢了 47%。 索引不是免费的午餐， 它是把所有「写」的活儿挪一部分到「建索引」和「维护索引」上——读多写少的表才划算。

30 秒实验

1. 把表从 20 万行改成 2 万行，重测两个数。 倍数会明显变小——表越小，扫全表的代价越低，索引的优势越不明显。 这也是「小表不必建索引」的原因。
2. 把查询从 WHERE k = 12345 改成 WHERE id = 12345，重测。 这次两侧都快——因为 id 是主键，本来就有索引。主键默认带索引，别重复建。
3. 想一想：一张只读的配置表（几百行、几乎不改），值不值得建索引？ 多半不值得。 几百行扫一遍也就微秒级，索引反而多占空间、多一层维护。

常见误区

1. 「索引能压缩数据、让表变小」。恰恰相反——索引让库变大。 它省的是「查找时间」，不是「存储空间」。
2. 「索引越多查询越快」。建索引要花写入代价：实测插 2 万行多花 47%，只加了一个索引。 十个索引的表，写入能慢一倍以上，而大部分索引根本没人用。
3. 「聚集索引和非聚集索引只是名字不同」。差别是物理的： 聚集索引就是行数据本身的排列顺序（每表只能一个）， 非聚集索引是另建的一本册子（可以有多个，但要回表）。

下一篇 14.2 讲怎么建： 单列、唯一、组合三种写法，重点是组合索引的「最左前缀」——(k, v) 建好之后， 只按 v 查是用不上的。

• ☐ （本篇完，下一篇 14.2 索引的创建）

相关笔记

• 索引的创建
• 索引的删除
• 查询进阶的综合用法
• 47 课 Oracle从入门到精通 · 索引
• 🪟 SQL Server从入门到精通 · 索引