一、第一种：建表时一起建

建表时把约束写进去，索引由引擎自动生成，这是最省事的写法。 PRIMARY KEY、UNIQUE、INDEX 三种约束都会生成索引，但生成的东西不一样。

第二行的名字是系统起的，格式固定：sqlite_autoindex_表名_序号。 看到这个前缀就知道「这是约束带出来的，不是我手写的」。 主键那一行特殊：SQLite 里 INTEGER PRIMARY KEY 就是 rowid 的别名， 不会单独生成一条索引记录。 所以查 sqlite_master，主键不会单独占一条记录： 一条表、一条唯一约束的自动索引、一条手写索引，一共三条。 手写的索引在 sqlite_master 里 sql 字段存着原句，这一点很有用： 想知道某个索引当初是怎么建的，查它就行，不用翻代码。

二、第二种：建表之后补建

CREATE INDEX 一条语句就完事，表里的数据一条不动。 它只是多了一份排好序的目录，所以对已有数据是「补建」，不是「重建」。

CREATE INDEX i_code ON tb_big (code);
CREATE UNIQUE INDEX i_code ON tb_big (code);

这两行的区别只在 UNIQUE 一个词，但行为差很远： 普通索引允许重复值，唯一索引不允许。 「不允许」是靠「建的时候先查一遍」实现的，所以对重复值建唯一索引会直接报错。 实测拿 cat（只有十个值、重复两万次）建唯一索引， 报的是 UNIQUE constraint failed: tb_big.cat。 这一条反过来用就是「去重检查」：不确定某列有没有重复，试着建个唯一索引就知道。 但要注意它是「全表扫描 + 排序」的动作，大表上得挑时间做。 重名也会报错：index i_b_code already exists。 而且沙箱里索引名是全库唯一的：给另一张表也起 idx，照样报 index idx already exists。 MySQL 不是这样，它的索引名只在本表内唯一，两张表可以各有一个 idx。 所以「索引名带表名前缀」这个习惯在沙箱里是必须的，在 MySQL 里是可选的。 两边都要跑的脚本还是带上，省得换引擎时才撞上。

三、第三种：改表时加（MySQL 专有）

教材给的第三种写法是 ALTER TABLE ... ADD INDEX，沙箱不支持。

ALTER TABLE tb_big ADD INDEX i_code (code);

沙箱报的是 near "INDEX": syntax error。 SQLite 的 ALTER TABLE 只能改名、加列、改列，不能加索引。 要加索引就得单独一句 CREATE INDEX，效果一样。 MySQL 里这两种写法是等价的，教材列两种是为了说明「索引也是表结构的一部分」。 不是「必须用哪一种」，是「你可以从两个角度去理解它」。 顺带一句：ALTER TABLE 加索引在 MySQL 里可以指定算法，ALGORITHM=INPLACE 能不锁表。 大表上这个参数决定「能不能白天做」，这一条书上没细讲但很实用。

四、最左前缀：联合索引的硬规矩

联合索引 (a, b) 是先按 a 排、a 相同再按 b 排。 所以只给 b 的时候，b 在这一堆里是乱序的，没法定位。

四行里三行走索引，只有第三行全表扫。这一行就是「最左前缀」的全部内容。 第四行值得多看两眼：WHERE b = 2 AND a = 1 也能走索引。 所以这条规矩说的是「用到了哪几列」，不是「写在前面的是哪几列」。 写 WHERE 的顺序随便，索引能不能用只看「有没有带上最左边那一列」。 这一条能解掉一大半「我明明建了索引」的疑惑。 那为什么还要讲究顺序？因为联合索引的列序是建的时候定的，改一次要重建。 所以建联合索引之前先想清楚：哪个条件一定会带上，把它放最左边。

五、前缀索引与表达式索引

长字符串列上，MySQL 支持「只索引前几个字符」，写法是 列名(4)。 沙箱不支持这种写法，报的是 no such function: code——它把 code(4) 当成了函数调用。

CREATE INDEX i_code4 ON tb_big (code(4));
CREATE INDEX i_sub ON tb_big (substr(code, 1, 4));

第一行是 MySQL 的前缀索引，第二行是 SQLite 的表达式索引。 两者想解决的是同一个问题：只给列的一部分建索引。

表达式索引有一个坑：它精确匹配那个表达式，不是匹配前缀。 写 substr(code, 1, 5) 就用不上了，因为那是另一个表达式。 所以用它的时候，WHERE 里的写法和索引里的写法必须一字不差。 前缀索引的代价是选择性下降：code(4) 只区分前四个字符，重复值会变多。 长文本列（比如 URL、邮箱）上这一招很省地方，但要接受「定位到一小段再逐条比」。

六、建完之后怎么确认

建了不等于生效，要回头查一遍。两个引擎各有一套查法。

最后一行才是关键：前面三行只告诉你「有没有」，最后一行告诉你「用不用」。 索引存在但不被使用，是最常见的一种浪费。 确认的顺序我习惯这么走：先 index_list 看建没建，再 EXPLAIN 看用没用， 最后看计划里 USING INDEX 后面跟的名字对不对。 名字不对说明用上了别的索引，或者根本没用——这两种情况要分开处理。

七、动手 Demo：三种建法与最左前缀

这一段从建表时建索引开始，走一遍三种写法，最后用四组 WHERE 把最左前缀量出来。 第五段换到前缀索引，看沙箱用什么办法替代。

import sqlite3
c = sqlite3.connect(u":memory:")

# ① 建表时建：三种约束各生成什么
c.execute(u"CREATE TABLE tb_a (id INTEGER PRIMARY KEY, "
          u"code TEXT NOT NULL UNIQUE, cat INTEGER)")
c.execute(u"CREATE INDEX i_a_cat ON tb_a (cat)")
print(c.execute(u"SELECT type, name FROM sqlite_master").fetchall())

# ② 建表后建：唯一索引会先查一遍重不重复
try:
    c.execute(u"CREATE UNIQUE INDEX i_b_cat ON tb_big (cat)")
except sqlite3.IntegrityError as e:
    print(e)

# ③ 改表加索引：沙箱不支持
try:
    c.execute(u"ALTER TABLE tb_big ADD INDEX i_code (code)")
except sqlite3.OperationalError as e:
    print(e)

# ④ 最左前缀：索引 (a, b)，四组条件
c.execute(u"CREATE TABLE tb_u (a INTEGER, b INTEGER, c INTEGER)")
c.execute(u"CREATE INDEX i_ab ON tb_u (a, b)")
for w in [u"a = 1", u"a = 1 AND b = 2", u"b = 2", u"b = 2 AND a = 1"]:
    print(w, c.execute(u"EXPLAIN QUERY PLAN SELECT * FROM tb_u "
                        u"WHERE " + w).fetchone()[-1])

实跑输出：

--------------------------------------------------------------------
第五十二课：三种建索引的写法，和最左前缀
--------------------------------------------------------------------

  **教材 11.2 给了三种建法：建表时建、建表后建、改表加索引。**
  **沙箱只有前两种，第三种是 MySQL 独有的语法。**
  **这一课真正要记住的是第四节：联合索引的最左前缀。**
  **它是「为什么我建了索引还是全表扫」的头号原因。**

  一、建表时建：三种约束各生成什么
  --------------------------------------------------------------------
  type  name
  -----------------------------
  index i_a_cat
  index sqlite_autoindex_tb_a_1
  table tb_a
  3 rows in set
  **三条记录：一条表、一条自动索引、一条手写索引。**
  **主键在 SQLite 里就是 rowid 的别名，不单独生成索引记录。**
  **唯一约束会生成一条 `sqlite_autoindex` 开头的自动索引，名字是系统起的。**
  **普通索引用 `CREATE INDEX` 建，名字自己起，`sql` 字段里存着原句：**
  CREATE INDEX i_a_cat ON tb_a (cat)
  **这一列 `sql` 很有用：想知道某个索引当初是怎么建的，查它就行，不用翻文档。**

  二、建表后建：CREATE INDEX 与 UNIQUE INDEX
  --------------------------------------------------------------------

  项            值
  ----------------
  建之前索引数  2
  建之后索引数  3
  **`CREATE INDEX` 一条语句就完事，表里的数据一条不动。**
  **它只是多了一份「排好序的目录」，所以对已有数据是「补建」，不是「重建」。**

  情形                结果
  --------------------------------------------------------------
  重名再建一次        报错：index i_b_code already exists
  对重复值建唯一索引  报错：UNIQUE constraint failed: tb_big.cat
  **第二行才是重点：唯一索引不是「建了就唯一」，是「建的时候先查一遍」。**
  **`cat` 只有十个值、重复两万次，所以直接报唯一约束冲突。**
  **这也说明建唯一索引是一个「全表扫描 + 排序」的动作，大表上要挑时间做。**

  三、改表加索引：MySQL 有，沙箱没有
  --------------------------------------------------------------------

  [教材原码 · 本沙箱不执行]
  | ALTER TABLE tb_big ADD INDEX i_code (code);
  教材原码，SQLite 不支持在 ALTER TABLE 里加索引

  **沙箱报错：`near "INDEX": syntax error`。**
  **SQLite 的 `ALTER TABLE` 只能改名、加列、改列，不能加索引。**
  **要加索引就得单独一句 `CREATE INDEX`，效果一样。**
  **MySQL 的 `ALTER TABLE ... ADD INDEX` 和 `CREATE INDEX` 是等价的两种写法。**
  **教材给两种是为了说明「索引也是表结构的一部分」，不是说必须用哪一种。**

  四、联合索引的最左前缀
  --------------------------------------------------------------------

  条件                     执行计划
  -------------------------------------------------------------------
  `WHERE a = 1`            SEARCH tb_u USING INDEX i_ab (a=?)
  `WHERE a = 1 AND b = 2`  SEARCH tb_u USING INDEX i_ab (a=? AND b=?)
  `WHERE b = 2`            SCAN tb_u
  `WHERE b = 2 AND a = 1`  SEARCH tb_u USING INDEX i_ab (a=? AND b=?)
  **四行里三行是 `SEARCH`，只有第三行是 `SCAN`。**
  **这一行就是「最左前缀」的全部内容。**
  **索引 `(a, b)` 是先按 `a` 排、`a` 相同再按 `b` 排。**
  **只给 `b` 的时候，`b` 在这一堆里是乱序的，没法定位，只能全表扫。**
  **第四行值得注意：`WHERE b = 2 AND a = 1` 也能走索引。**
  **所以「最左前缀」说的是「用到了哪几列」，不是「写在前面的是哪几列」。**
  **写 `WHERE` 的顺序随便，索引能不能用只看「有没有带上最左边那一列」。**
  **这一条能解掉一大半「我明明建了索引」的疑惑。**

  五、前缀索引与表达式索引
  --------------------------------------------------------------------

  [教材原码 · 本沙箱不执行]
  | CREATE INDEX i_code4 ON tb_big (code(4));
  教材原码，MySQL 的前缀索引写法

  **MySQL 支持「只索引前几个字符」，写法是 `列名(4)`。**
  **长字符串列上这一招很省地方，代价是选择性下降。**
  **沙箱不支持，报错：`no such function: code`。**

  写法                               沙箱对应
  -----------------------------------------------------------------------------
  `code(4)` 前缀索引                 不支持
  `(substr(code, 1, 4))` 表达式索引  SEARCH tb_big USING INDEX i_sub (<expr>=?)
  **SQLite 走的是另一条路：给「表达式」建索引，叫表达式索引。**
  **建了之后，`WHERE substr(code, 1, 4) = ...` 这种写法也能走索引。**
  **注意它是「精确匹配那个表达式」，不是「前缀」——表达式必须和索引里写的一模一样。**
  **写 `substr(code, 1, 5)` 就用不上了，因为那是另一个表达式。**
  **MySQL 8.0 也有表达式索引，两边的思路在这一处是通的。**
  **只是 MySQL 多一种「前缀索引」的写法，SQLite 没有。**

第一组三条记录：一条表、一条自动索引、一条手写索引。 主键没单独出现，因为 INTEGER PRIMARY KEY 就是 rowid 本身。 第二组和第三组都是报错，但性质不同：一个是「数据不满足约束」，一个是「语法不支持」。 前者说明唯一索引在建立时会先扫一遍数据，后者说明沙箱和 MySQL 的语法分叉。 第四组四行是这一篇的核心，三行 SEARCH、一行 SCAN。 最后一行 WHERE b = 2 AND a = 1 照样走索引，说明顺序不影响。 第五组见正文最后一节：code(4) 报 no such function: code， 改成 substr(code,1,4) 就能建。

30 秒实验

1. 把 i_ab 改成 (b, a) 重建，看四组条件的 SEARCH／SCAN 怎么对调。
2. 对 code 列建 UNIQUE INDEX，看它能不能建成功，说明原因。
3. 把表达式索引写成 substr(code, 1, 5)，用 substr(code, 1, 4) 去查，看还能不能走索引。

常见误区

1. 建了联合索引 (a, b)，查询只带 b，然后奇怪为什么还是全表扫。
2. 以为 WHERE 里的顺序要和索引列序一致，白改了一堆 SQL。
3. 唯一索引建失败时反复试，其实是数据本身有重复，该先查重。

建完了，还得会删。下一篇讲 DROP INDEX，以及删之前该看什么。

• ☐ （本篇完，下一篇 11.3 删除索引）

相关笔记

• 索引概述
• 修改表结构
• 单表查询
• 🛢️ MySQL从入门到精通 · 索引