一、没有索引时，找一行要两万次比较

先看没有索引的样子。 两万行的表，按 code 找一行：没有索引是毫秒级，建了索引之后是微秒级。

计划栏那两个词要认得：SCAN 是从头到尾一行行比，SEARCH 是按索引直接定位。 两万行找一行，SCAN 就得比两万次，所以它叫全表扫描。 这两个词的差别不是「快一点」和「慢一点」，是两种增长方式： SCAN 的耗时跟行数成正比，SEARCH 跟行数的对数成正比。 两万行差几十倍，两百万行会差到几个数量级。 索引到底是什么？一句话：按某一列排好序的一份副本，副本里每一条记着「值 + 行号」。 要找 code = 'C010000'，先在副本里定位到这个值，拿到行号，再去原表取整行。 所以它本质上是「用空间换时间」，而且换的不只是空间，还有写入的速度。 这一点后面第二节就要算账。

二、第一笔账：占多少地方

dbstat 是 SQLite 自带的虚拟表，能按对象统计占了多少页。 有了它，「索引占地方」这句话就不用估，可以直接看数。

两个索引加起来占了 29%，接近三成。 这不奇怪：i_code 存了全部两万个 code 加行号，i_cat 也存了两万条。 所以「给每列都建索引」的代价不是「多几个文件」，是「数据文件多出几成」。 磁盘、备份、内存缓存，三处都要跟着涨。 这一条最容易被忽略，因为查得快是「看得见的好处」，占地方是「看不见的成本」。 所以建索引的预算要连着磁盘一起算，不能只算「查得快多少」。

三、第二笔账：写入变慢

同一份数据建两遍，一遍不带索引、一遍带两个索引，只量插入。

插入耗时涨到 1.8 倍，文件大了 41%。 原因是每写一行，两个索引都要各维护一次——插入不是「往末尾追加」，是「插到排好的位置里」。 所以索引不是「查的时候才算」的，是「写的时候就排好」。 写多读少的表要克制：日志表、流水表、埋点表，索引要精不要多。 读多的表该建就建：商品表、用户表、字典表，查询远比写入频繁，多几个索引划算。 判断标准就这么朴素，不用算得很精。

四、索引有哪几类

教材按用途分了六类，对照着看一遍就知道自己常用的是哪几种。

前四种两边都有，后两种是 MySQL 的专项能力。 全文索引给「一段话里找关键词」用，空间索引给地图坐标用，本书后面都不涉及。 还有一组分类是按「数据放哪」分的，这一组更重要：聚簇索引和二级索引。 聚簇索引的叶子节点就是整行数据，二级索引的叶子节点只有「索引列 + 行号」。 InnoDB 一定有一个聚簇索引，默认就是主键。 所以主键选得短，所有二级索引都会跟着变小——因为每个二级索引里都存着主键值。 沙箱这边同样是主键聚簇：id INTEGER PRIMARY KEY 就是 rowid 的别名，数据按它排。 其他索引一律是二级索引，叶子节点存「索引列 + rowid」。

五、回表与覆盖索引

二级索引里没有整行，所以要的列不在索引里时，得拿着行号回原表取一次，这叫回表。 如果 SELECT 要的列索引里全有，就不用回表，这叫覆盖索引。

计划里出现 COVERING INDEX 就是覆盖索引生效了，这一条最值得记。 它和普通 USING INDEX 的区别，一眼就能看出来。 这次没量出「覆盖比回表快多少」：两千行、字段才五十字节，回表这一趟还够便宜， 两次跑出来的差都在 0.2 毫秒以内，连方向都会翻。 所以这一节不给倍数，只给结论：字段变长、行数变多，回表才会成为主要开销。 到那时候，覆盖索引的差距就出来了。 「别写 SELECT *」在索引这一层还有一个理由：它一定要回表，覆盖索引永远没机会生效。 把需要的列写出来，是让覆盖索引有可能生效的第一步。

六、索引设计原则

教材给了几条原则，我按「能不能用实测支撑」重新排了一下。

第四行「区分度高」这件事，我原来也以为是「低基数就别建」。 实测发现这句口诀太绝对：cat 只有十个不同值，走索引仍然快了 8 倍。

为什么低基数也有用？因为它省掉的是「全表扫」这一趟。 只要命中的行不到全表的一大半，走索引就比扫全表划算。 准确的说法是：基数越低，收益越小。 code 两万个不同值，快了 36 倍；cat 十个值，快了 8 倍。 最后那条「别给每列都建」才是硬原则。 每多一个索引，每次写入都要多维护一份，文件也要多涨一截。

七、动手 Demo：把索引的三笔账都量出来

这一段建两份一模一样的两万行数据，一份不带索引、一份带两个，然后依次量「查得快多少、占多少地方、 写慢多少」，最后单独看回表与低基数列。

import sqlite3, os, time

ROWS = [(None, u"C%06d" % i, i % 10, u"x" * 50) for i in range(1, 20001)]

def build(name, idx):
    p = os.path.join(TMP, name)
    if os.path.exists(p):
        os.remove(p)
    c = sqlite3.connect(p)
    c.execute(u"CREATE TABLE tb_big (id INTEGER PRIMARY KEY, "
              u"code TEXT, cat INTEGER, body TEXT)")
    if idx:
        c.execute(u"CREATE INDEX i_code ON tb_big (code)")
        c.execute(u"CREATE INDEX i_cat ON tb_big (cat)")
    t0 = time.perf_counter()
    c.executemany(u"INSERT INTO tb_big VALUES (?, ?, ?, ?)", ROWS)
    return c, p, time.perf_counter() - t0

c0, p0, t0 = build(u"no.db", False)
c1, p1, t1 = build(u"yes.db", True)

# ① 查得快多少
q = u"SELECT body FROM tb_big WHERE code = 'C010000'"
print(c0.execute(u"EXPLAIN QUERY PLAN " + q).fetchone()[-1])
print(c1.execute(u"EXPLAIN QUERY PLAN " + q).fetchone()[-1])

# ② 占多少地方
for r in c1.execute(u"SELECT name, SUM(pgsize) FROM dbstat GROUP BY name"):
    print(r)

# ③ 写慢多少、文件大多少
print(t0, t1, os.path.getsize(p0), os.path.getsize(p1))

# ④ 回表 vs 覆盖
for s in [u"SELECT body FROM tb_big WHERE cat = 3",
          u"SELECT id, cat FROM tb_big WHERE cat = 3"]:
    print(c1.execute(u"EXPLAIN QUERY PLAN " + s).fetchone()[-1])

实跑输出：

--------------------------------------------------------------------
第五十一课：索引是排好序的目录，代价写在另一头
--------------------------------------------------------------------

  **教材 11.1 讲三件事：索引是什么、有哪几类、怎么设计。**
  **这一课把前两件跑出来，第三件留到最后用实测数据支撑。**
  **核心只有一句话：索引让「找」变快，让「写」变慢，还要占地方。**
  **三笔账都要算，只算第一笔就会建一堆没用的索引。**

  一、没有索引时怎么找
  --------------------------------------------------------------------

  条件      执行计划                                   耗时
  --------------------------------------------------------------
  没有索引  SCAN tb_big                                1.97 毫秒
  有索引    SEARCH tb_big USING INDEX i_code (code=?)  55 微秒
  **没有索引时执行计划是 `SCAN`，意思是从头到尾一行行比。**
  **两万行里找一行，就得比两万次，耗时 1.97 毫秒。**
  **有索引之后是 `SEARCH`，直接定位到那一条，快了 36 倍。**
  **而且这个倍数是随行数增长的：两万行差 36 倍，两百万行会差几个数量级。**
  **因为 `SCAN` 是线性增长，`SEARCH` 是对数增长。**

  二、索引是排好序的目录，占多少地方
  --------------------------------------------------------------------

  对象           字节     占比
  -----------------------------
  tb_big         1376256  70.7%
  i_code         348160   17.9%
  i_cat          217088   11.2%
  sqlite_schema  4096     0.2%
  **`dbstat` 是 SQLite 自带的虚拟表，能按对象统计占了多少页。**
  **它把「索引占地方」这件事变成了一个可以直接看的数。**
  **两个索引加起来占了 29%。**
  **这不奇怪：`i_code` 存了全部两万个 code 加行号，`i_cat` 也存了两万条。**
  **所以「给每列都建索引」的代价不是「多几个文件」，是「数据文件多出几成」。**
  **磁盘、备份、内存缓存，三处都要跟着涨。**

  三、代价：写入变慢、体积变大
  --------------------------------------------------------------------

  项          无索引     两个索引
  --------------------------------
  插入两万行  0.046 秒   0.083 秒
  文件大小    1348.0 KB  1900.0 KB
  **插入耗时涨到 1.8 倍：每写一行，两个索引都要各维护一次。**
  **索引不是「查的时候才算」，是「写的时候就排好」，所以写多读少的表要克制。**
  **文件大了 41%，这一条最容易被忽略。**
  **因为查得快是「看得见的好处」，占地方是「看不见的成本」。**
  **判断标准很朴素：这张表是读多还是写多。**
  **日志表、流水表是写多，索引要精不要多；商品表、用户表是读多，该建就建。**

  四、聚簇索引与二级索引：回表与覆盖
  --------------------------------------------------------------------

  查询                          执行计划                                           耗时
  ------------------------------------------------------------------------------------------
  按主键取一行                  SEARCH tb_big USING INTEGER PRIMARY KEY (rowid=?)  52 微秒
  按索引取 2000 行的 body       SEARCH tb_big USING INDEX i_cat (cat=?)            1.80 毫秒
  按索引取 2000 行的 id 和 cat  SEARCH tb_big USING COVERING INDEX i_cat (cat=?)   1.69 毫秒
  **第一行是「聚簇索引」：数据本身就按主键排着，找到主键就等于找到整行。**
  **所以它最快，而且 `SELECT *` 也不用二次找。**
  **第二行是「二级索引 + 回表」：先在 `i_cat` 里找到两千个行号，再逐行回表取 `body`。**
  **第三行不用回表，因为要的列索引里都有，这叫「覆盖索引」。**
  **第三行的计划里写的是 `COVERING INDEX`，第二行只是 `INDEX`，这个区别是真的。**
  **但耗时分不出高下：1.80 毫秒 对 1.69 毫秒，差不到 0.2 毫秒，连跑几次方向还会翻。**
  **所以这里不给「覆盖索引快几倍」的数。**
  **两千行、字段才五十字节，回表这一趟还够便宜，这个规模量不出差别。**
  **等字段变长（比如存一段 JSON）、行数变多，回表才会成为主要开销。**
  **这一节要带走的是「`COVERING INDEX` 这个字样」和「怎么让它出现」，不是某个倍数。**
  **所以「别写 `SELECT *`」在索引这一层还有一个理由：它一定会回表。**
  **只要把需要的列写出来，覆盖索引就有机会生效。**

  五、低基数的列值得建索引吗
  --------------------------------------------------------------------

  条件                         耗时       倍数
  ----------------------------------------------
  `cat` 有索引（10 个不同值）  217 微秒   —
  `cat` 没索引                 1.74 毫秒  8.0 倍
  **`cat` 只有 10 个不同值，是典型的低基数列。**
  **但它仍然快了一截——所以「低基数就别建索引」这句口诀要打个折。**
  **准确的说法是：基数越低，收益越小。**
  **`code` 有两万个不同值，快了 36 倍；`cat` 只有十个，快了 8 倍。**
  **为什么低基数也有用？因为它省掉的是「全表扫」这一趟。**
  **只要查询命中的行不到全表的一大半，走索引就比扫全表划算。**
  **命中范围再大，优化器就会主动放弃索引——回表的代价超过了省下来的扫描。**
  **这一点不用背百分比，`EXPLAIN` 一看就知道它有没有放弃。**

第一组两行是 SCAN 和 SEARCH，耗时差了几十倍。 这一组是全章的地基：认得这两个词，才看得懂后面所有执行计划。 第二组四行是 dbstat 的统计，两个索引加起来 29%。 同一份数据，多建两个索引就多出近三成体积，这个比例很有代表性。 第三组两对数是插入耗时和文件大小，涨到 1.6 倍和 1.41 倍。 注意这两个数是一起涨的：写入慢和体积大是同一件事的两个侧面。 第四组两行只差一个词：USING INDEX 和 USING COVERING INDEX。 要的列在不在索引里，就决定了要不要回表。

30 秒实验

1. 把 WHERE code = 'C010000' 换成 WHERE body = 'xxx'，看计划退回 SCAN。
2. 把 i_cat 换成 (cat, code) 联合索引，再看 SELECT id, cat 还是不是覆盖。
3. 把两万行改成两千行，量一下 SCAN 与 SEARCH 的倍数掉了多少。

常见误区

1. 以为「有索引就一定快」，不看执行计划就断定索引生效了。
2. 给每一列都建索引，查询没快多少，写入慢了一截、备份也大了。
3. 背下「低基数别建索引」，结果把一个只有几个状态值的列排除在外。

这一篇讲清了「为什么要索引」。下一篇讲写法：三种建法，加最左前缀这条硬规矩。

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

相关笔记

• MySQL 数据类型
• 单表查询
• 创建数据表
• 🛢️ MySQL从入门到精通 · 索引