12.1 索引简介 · 波仔的 AI 编程实战课

📄 一句话概括

索引就是「按某一列排好序的一份目录」，有了它，查一行不用把整张表翻一遍。 判断索引生效没有，别看「感觉快了」，看执行计划： SCAN 是全表扫，SEARCH … USING INDEX 才是走了索引。 代价是写入变慢、占空间、占内存，所以索引不是越多越好，是「该有的有、多余的删」。

📝 本章和前面几章的关系

前面十一章都在解决「怎么写查询」，这一篇开始解决「怎么让它跑得快」。 索引是数据库性能里最要紧的一件事，线上事故里「慢查询」有一大半跟它有关：不是没建，就是建了没用上。 本沙箱用 sqlite3，索引的原理和 MySQL 一样（都是 B 树那一套），差别在执行计划怎么看。

一、先看问题：5 万行里找一行

big 表有 5 万行，name 从「用户00001」到「用户50000」。 现在要按名字找一个人：select id, name from big where name = '用户49999'。 没有索引时，数据库只能从第一行看到第五万行，一行一行比名字，比到最后一个才对上。 这叫「全表扫描」。 5 万行还好，500 万行、5000 万行呢？ 这就是慢查询的来源：不是语句写错了，是它压根没别的路可走。

二、索引是什么：一本排好序的目录

索引就是「按某一列排好序的一份目录」。 目录里记着「这个值在哪一行」， 找的时候先在目录里二分查找，找到位置再直接跳过去。 拿书打比方：没有索引就是在正文里一页页翻找某个词； 有索引就是先翻书末的索引页——「这个词在第 173 页」，直接翻过去。 所以它有两个前提：一是被索引的值必须能比较大小（数字、字符串、日期都行）； 二是查的条件必须是「等于」或者「范围」，不能是「包含」——这一条后面会撞。 索引不改变查询结果，只改变「怎么找到」。 同一句 select，有没有索引都返回同样的行，只是快慢不同。 所以索引是纯优化手段，不是功能——删掉索引，程序逻辑一行都不用改。

三、加个索引，看计划怎么变

建索引就一句：create index idx_name on big (name)。 建之前执行计划是 SCAN big， 建之后变成 SEARCH big USING COVERING INDEX idx_name (name=?)。 SCAN 是「一行一行看过去」， SEARCH 是「按索引直接跳过去」。 这两个词就是索引有没有生效的判据。 后面那个 COVERING INDEX 意思是「覆盖索引」： 这句查询要的列全在索引里，连表都不用回——所以它比普通的 USING INDEX 还快一层。

四、慢多少：量一次

Demo 里跑了 100 次取最快的一轮：没有索引 559.9 毫秒，有索引 0.6 毫秒，差了约 947 倍。 但这两个绝对数都不该当真——表在内存里、机器也快，换个环境数字就变。 真正可靠的是「性质」而不是「倍率」： 全表扫的代价随行数线性涨，走索引的代价随行数对数涨。 5 万行差 947 倍，5000 万行差得更多。 这也是为什么「小表上看不出索引的好处」：一千行的表，全表扫也就是微秒级， 索引的收益还抵不过它的维护成本。

五、代价一：写入变慢

每插一行、改一行、删一行，所有索引都得跟着维护。 索引越多，写入越慢。 Demo 里同样插 2 万行：没有索引 51.6 毫秒，有索引 67.4 毫秒，慢了 31%。 注意这只是「一个索引」的代价。 一张表五个索引，插入就要维护五份目录—— 实际项目里见过「索引建太多，批量导入慢十倍」的。 所以选索引要看读写比例：读多写少的（商品、用户）可以多建； 写多读少的（日志、流水、埋点）要克制，那种表上通常只留一两个真正必要的索引。

六、代价二：占空间、占内存

索引本身要存盘，一般占表的百分之几到几十。 列越长、重复越少，索引就越大。 更要紧的是它会被读进内存。 内存被索引占了，留给数据页的就少了， 缓存命中率跟着下降——这一步的损失往往比「多占几 MB 硬盘」大得多。 所以「索引越多越快」是错的。 索引多了，优化器还得花时间挑用哪个， 统计信息不准的时候还会挑错——挑错了比没有索引更慢。

七、索引失效的三种写法

Demo 的最后一段拿同一个索引试了四种条件，只有第一种走了 SEARCH： 第一种 name = '用户49999'，等值查询，走了 SEARCH。 这是索引最喜欢的形状。 第二种 name LIKE '用户499%'，前缀匹配， 理论上能用索引，这里还是 SCAN——因为 SQLite 的 LIKE 默认不区分大小写， 而索引是按原样排序的，对不上。 MySQL 上 like 'x%' 是能走索引的， 但列上套了函数、或者排序规则不匹配，一样用不上。 第三种 name LIKE '%49999'，前面有通配符， 目录彻底没法用—— 你连「从哪个字开始找」都不知道，只能一条条翻。 这一条在所有数据库上一样。 第四种 substr(name, 1, 2) = '用户'，列被函数包住了。 目录里存的是原值，不是 substr 之后的值，索引废掉。 要改写成 name LIKE '用户%'，或者干脆加一列专门存这个前缀。 记住这三条： 前面带通配符、列被函数包住、类型对不上（字符串当数字比）——这三种写法一出现，索引就白建了。

八、动手 Demo：建一张 5 万行的表，从 SCAN 走到 SEARCH

Demo 五段：造 5 万行、看没有索引的执行计划、建索引、再看计划、跑 100 次量时间、最后试四种失效写法。 第三段和第五段是全篇的核心——「计划变了」是定性，「快了 947 倍」是定量， 两个一起看才知道索引到底干了什么。

import time
import _q46 as Q
c = Q.blank()

# 造一张 5 万行的表
c.execute("CREATE TABLE big (id INTEGER PRIMARY KEY, name TEXT, "
          "city TEXT, age INTEGER)")
c.executemany("INSERT INTO big VALUES (?,?,?,?)",
              [(i, '用户%05d' % i, '北京', 18 + i % 60)
               for i in range(1, 50001)])
c.commit()

# 只打印执行计划里那句说明
def plan(sql, ind=2):
    Q.sqlprint("EXPLAIN QUERY PLAN " + sql)
    for row in c.execute("EXPLAIN QUERY PLAN " + sql):
        print(" " * ind + row[3])

# 1) 没有索引
Q.show(c, "SELECT id, name, city, age FROM big "
          "WHERE name = '用户49999'")
plan("SELECT id, name FROM big WHERE name = '用户49999'")

# 2) 建一个索引
Q.run(c, "CREATE INDEX idx_name ON big (name)")

# 3) 同一个查询，计划变了
plan("SELECT id, name FROM big WHERE name = '用户49999'")

# 4) 跑 100 次，取最快一轮
def bench(sql, times=100):
    c.execute(sql).fetchall()          # 预热一次
    t0 = time.perf_counter()
    for _ in range(times):
        c.execute(sql).fetchall()
    return (time.perf_counter() - t0) * 1000

print("有索引 %.1f 毫秒" % bench(
      "SELECT id, name FROM big WHERE name = '用户49999'"))

# 5) 索引失效的四种写法
for w in ("name = '用户49999'", "name LIKE '用户499%'",
          "name LIKE '%49999'", "substr(name, 1, 2) = '用户'"):
    plan("SELECT COUNT(*) FROM big WHERE " + w, 4)

实跑输出：

--------------------------------------------------------------------
第六十三课：索引——用空间换时间的那个东西
--------------------------------------------------------------------

  先看问题：5 万行里找一行
  **`big` 表有 5 万行，`name` 从「用户00001」到「用户50000」。** **现在要按名字找一个人。**
  SQL> SELECT id, name, city, age FROM big WHERE name = '用户49999'
  id        name          city     age
  --------------------------------------
  49999     用户49999     杭州     37
  （1 行）
  SQL> EXPLAIN QUERY PLAN SELECT id, name FROM big WHERE name = '用户49999'
  SCAN big

  **执行计划里写的是 `SCAN big`**——
  **SCAN 就是「一行一行看过去」，中文叫「全表扫描」。** **5 万行全看一遍，才能找到最后那一行。**

  加个索引再看
  SQL> CREATE INDEX idx_name ON big (name)
  完成
  SQL> EXPLAIN QUERY PLAN SELECT id, name FROM big WHERE name = '用户49999'
  SEARCH big USING COVERING INDEX idx_name (name=?)

  **变成 `SEARCH big USING COVERING INDEX idx_name (name=?)`。**
  **SEARCH 是「按索引直接跳过去」，不用一行行看。**

  慢多少：量一次
  **同一句查询跑 100 次**：**没有索引时总共要 544.1 毫秒，有了索引 0.4 毫秒。**
  **差了约 1539 倍。**
  **注意这两个绝对数值都很小**——
  **因为表在内存里、机器也快。** **真正可靠的不是这两个数，是执行计划从 `SCAN` 变成了 `SEARCH`。**
  **换台机器、换个数据量，倍率会变，但「全表扫」和「按索引跳」这个性质不会变。**

  索引是什么：一本排好序的目录
  **索引就是「按某一列排好序的一份目录」**：
  **目录里记着「这个值在哪一行」，找的时候先在目录里二分查找。**
  **所以它有两个前提**：**一是要找的值必须能比较大小**（数字、字符串、日期都行）；
  **二是查的条件必须是「等于」或「范围」**，**不是「包含」**——这一点后面会撞。
  **索引不改变查询结果，只改变「怎么找到」。**
  **同一句 `select`，有没有索引都给同样的行，只是快慢不同。** **所以索引是纯优化手段，不是功能。**

  代价一：写入变慢
  **每插一行、改一行、删一行，索引都得跟着维护。** **索引越多，写入越慢。**
  **同样插 2 万行：没有索引 41.3 毫秒，有索引 58.3 毫秒，慢了 41%。**
  **注意这是「只有一个索引」的代价。** **一张表五个索引，写入就会慢好几倍**——
  **所以「读多写少」的表适合多建索引，「写多读少」的（日志、流水）要克制。**

  代价二：占空间、占内存
  **索引本身要存，一般占表的百分之几到几十。** **更要紧的是它会被读进内存**——
  **内存被索引占了，留给数据页的就少了，缓存命中率下降。**
  **所以「索引越多越快」是错的。** **索引多了，优化器还得花时间挑用哪个**，
  **挑错的那个反而更慢。**

  什么时候索引没用
  `WHERE name = '用户49999'`
  SQL> EXPLAIN QUERY PLAN SELECT COUNT(*) FROM big WHERE name = '用户49999'
    SEARCH big USING COVERING INDEX idx_name (name=?)

  `WHERE name LIKE '用户499%'`
  SQL> EXPLAIN QUERY PLAN SELECT COUNT(*) FROM big WHERE name LIKE '用户499%'
    SCAN big USING COVERING INDEX idx_name

  `WHERE name LIKE '%49999'`
  SQL> EXPLAIN QUERY PLAN SELECT COUNT(*) FROM big WHERE name LIKE '%49999'
    SCAN big USING COVERING INDEX idx_name

  `WHERE substr(name, 1, 2) = '用户'`
  SQL> EXPLAIN QUERY PLAN SELECT COUNT(*) FROM big WHERE substr(name, 1, 2) = '用户'
    SCAN big USING COVERING INDEX idx_name

  **只有第一条走了 `SEARCH`，后三条全是 `SCAN`。** **这就是索引失效的三种典型写法。**
  **`LIKE '用户499%'` 是「前缀匹配」，理论上能用索引，这里还是扫了全表**——
  **因为 SQLite 的 `LIKE` 默认不区分大小写，索引是按原样排序的，对不上。**
  **MySQL 同理：`like 'x%'` 能用索引，但列上如果有函数或者排序规则不匹配就用不了。**
  **`LIKE '%49999'` 是「包含匹配」，前面有通配符，目录没法用**——
  **你不可能知道「以什么开头」，只能一条条翻。** **这一条在所有数据库上一样。**
  **`substr(name, 1, 2) = '用户'` 是「列被函数包住」，索引也废了**——
  **目录里存的是原值，不是 `substr` 之后的值。**
  **要改写成 `name LIKE '用户%'` 或者干脆加一个存前缀的列。**

第一段查出一个人：49999，杭州，37 岁。 计划从 SCAN big 变成 SEARCH big USING COVERING INDEX idx_name (name=?)。 100 次查询：559.9 毫秒对 0.6 毫秒，约 947 倍。 插 2 万行：51.6 毫秒对有索引 67.4 毫秒，慢了 31%。 四种条件里只有等值那条 SEARCH，后三条全是 SCAN。

30 秒实验

把 5 万行换成 500 行再量一次，倍率还是几百倍吗？想想为什么。
name LIKE '用户499%' 在 MySQL 上走不走索引？这里为什么不走？
一张表建五个索引，批量插入会慢几倍？能靠什么估出来？

常见误区

索引越多越快。 写入变慢、占内存、优化器还可能挑错，多余的要删。
建了索引就一定用得上。 得看执行计划，SCAN 就是没用上。
拿「感觉快了」当验证。 缓存、数据量、机器负载都能骗人，只有执行计划是硬证据。

自测三问（点开对答案）

1. 执行计划里 `SCAN` 和 `SEARCH` 分别是什么意思？ —— `SCAN` 是全表扫描，一行行看过去；`SEARCH` 是按索引直接定位。后者才说明索引生效了。 2. 为什么 `LIKE '%49999'` 用不上索引？ —— 前面有通配符，不知道从哪个字开始，排好序的目录没法二分，只能一条条翻。所有数据库都一样。 3. 索引的代价有哪三样？ —— 写入变慢（每次增删改都要维护索引）、占硬盘、占内存（挤掉数据页缓存），另外还会拖慢优化器的选择。

知道索引怎么起效了，下一篇看怎么建：12.2 创建索引——一条语句，两个决定。

☐ （本篇完，下一篇 12.2 创建索引）