12.2 创建索引 · 波仔的 AI 编程实战课

📄 一句话概括

CREATE INDEX 索引名 ON 表名 (列名, …)，索引名在整个库里唯一，所以习惯上带表名，叫 idx_表_列。 加 UNIQUE 就是唯一索引：既查得快，又替数据库挡住重复值。 表里已有重复数据时，唯一索引建不上。 复合索引的列顺序决定它能用在哪些查询上，这就是「最左前缀」——跳过最左那列，索引用不上。

📝 本章和前面几章的关系

上一篇讲「索引为什么快」，这一篇讲「怎么建、建的时候要想清什么」。 建索引的语法只有一句，难的是两个决定：建在哪几列上、按什么顺序。 这一篇和 9.6「唯一键」是同一个东西的两面： 建表时写 UNIQUE 和事后 create unique index，底层是同一份索引。

一、最基本的写法

create index idx_big_name on big (name)。 索引名自己起，但要有规律—— idx_表_列 是最常见的命名法，一眼看出它是给谁建的。 索引是建在表上的，不是建在列上的： 一张表可以有多个索引，一个索引也可以包含多列。 所以名字里带上表和列，以后删的时候不用猜。 建完 pragma index_list(big) 能看到它： origin 那一列是 c，表示「用 create index 建的」（c 就是 create 的首字母）。 另外两种来源 u 和 pk 后面会讲。

二、索引名是全库唯一，不是表内唯一

这一点跟列名不一样：列名只在表内唯一，索引名是整个数据库里唯一。 Demo 里在另一张表 other 上再建一个叫 idx_big_name 的索引， 直接报 index idx_big_name already exists。 所以「idx_name」这种名字在第二张表上就撞了。 这也是为什么推荐把表名带上： 撞不了，而且一眼看得出归属。

三、建完立刻生效，但建的动作本身很慢

create index 一执行完，后面所有查询立刻就能用它。 没有「重启生效」这回事，也没有「下次查询才编译」这回事。 代价在「建」这个动作上： 它要把表里已有的行全部排一遍序。 5 万行是一瞬间，5000 万行可能就是几十分钟到几小时。 更麻烦的是「建的过程中会锁表」（不同数据库、不同版本差别很大）——这段时间写入可能被挡住。 所以建索引要挑低峰期， 或者在 MySQL 上用 ALGORITHM=INPLACE、LOCK=NONE 这类在线建索引的写法。

四、唯一索引：既查得快，又管不重复

create unique index uq_big_name on big (name)， 只多一个 unique 词。 建完 index_list 里 unique 那一列从 0 变成 1。 它干两件事：查得快（和普通索引一样）＋不许重复（插入或改成重复值会被拦）。 Demo 里插一个已经存在的「用户00001」，报 UNIQUE constraint failed: big.name。 第 9.6 篇讲的那个 UNIQUE 约束，底层就是一个唯一索引。 所以两种写法效果一样： 建表时写 name text unique，和事后补 create unique index。 区别只在「建表时就管住」还是「事后才管住」。

五、给已有重复数据的表加唯一索引

如果表里已经有两个「北京」，唯一索引建不上， 报 UNIQUE constraint failed: dup.city。 注意报的是「约束失败」，不是「语法错」—— 它在建索引的过程中发现了违规数据，于是整条语句回滚。 正确顺序是先清重复再建索引： select city, count(*) from dup group by city having count(*) > 1 先把重复找出来， 决定留哪条、删哪条或者改哪条，然后才建索引。 这一步和第 9.10 篇「重建表」是同一个思路： 先处理数据、再加约束——反过来做，约束会因为旧数据不干净而建不上。

六、复合索引与最左前缀

create index idx_big_city_age on big (city, age)， 一个索引包含两列。 pragma index_info 能看到它的两行：先 city、后 age。 顺序很关键： 索引先按 city 排，city 相同的再按 age 排。 就像电话簿先按姓排、同姓的再按名排。 于是它能用在三种查询上： 只查 city、city 加 age、city 加 age 的范围——这三种都能顺着目录走。 但「只查 age」用不上： 因为目录是按 city 先排的，age 在目录里是散的。 这就是「最左前缀」： 索引只能从最左边那列开始用，不能跳着用。 所以复合索引的列顺序要按「查询里最常出现的那个放左边」来排。 (city, age) 和 (age, city) 是两个完全不同的索引， 能支持的查询也不一样——建错了等于白建，还白占了空间。

七、if not exists：脚本里几乎都要带

重复建同名索引会报 index … already exists。 加 if not exists 就不报： create index if not exists idx on 表 (列)。 这和前面几章的 if not exists 一个道理： 让脚本能重复跑。 建表脚本、建视图脚本、建索引脚本，都应该能跑第二遍。 建索引尤其要注意这一点：大表上重复建一次索引，可能就是几十分钟的无效等待， 中间还锁着表。 这句 if not exists 只有五个词，能省一次事故。

八、动手 Demo：从一条最基本的语句，到唯一索引、复合索引和最左前缀

Demo 六段：基本写法、撞一次重名、建唯一索引并插重复值、给有重复数据的表加唯一索引、复合索引与三条执行计划、最后试 if not exists。 第五段那三条执行计划是全篇最该看的地方——同一份索引，三种条件，两种结果。

import _q46 as Q
c = Q.blank()
c.execute("CREATE TABLE big (id INTEGER PRIMARY KEY, name TEXT, "
          "city TEXT, age INTEGER)")

# 1) 最基本的写法
Q.run(c, "CREATE INDEX idx_big_name ON big (name)")
Q.show(c, "PRAGMA index_list(big)")

# 2) 索引名全库唯一：换张表也不能重名
c.execute("CREATE TABLE other (name TEXT)")
c.execute("CREATE INDEX idx_big_name ON other (name)")

# 3) 唯一索引：查得快 + 不许重复
Q.run(c, "CREATE UNIQUE INDEX uq_big_name ON big (name)")
Q.show(c, "PRAGMA index_list(big)")
c.execute("INSERT INTO big (id, name) VALUES (90001, '用户00001')")

# 4) 表里已经有重复数据，唯一索引建不上
c2 = Q.blank()
c2.execute("CREATE TABLE dup (id INTEGER PRIMARY KEY, city TEXT)")
c2.executemany("INSERT INTO dup VALUES (?,?)",
               [(1, '北京'), (2, '上海'), (3, '北京')])
c2.execute("CREATE UNIQUE INDEX uq_dup_city ON dup (city)")

# 5) 复合索引：(city, age) 和 (age, city) 不是一回事
Q.run(c, "CREATE INDEX idx_big_city_age ON big (city, age)")
Q.show(c, "PRAGMA index_info(idx_big_city_age)")

# 6) 脚本里带 if not exists 才能重复跑
c.execute("CREATE INDEX idx_big_name ON big (name)")
c.execute("CREATE INDEX IF NOT EXISTS idx_big_name ON big (name)")

实跑输出：

--------------------------------------------------------------------
第六十四课：创建索引——一条语句，两个决定
--------------------------------------------------------------------

  最基本的写法
  SQL> CREATE INDEX idx_big_name ON big (name)
  完成
  SQL> PRAGMA index_list(big)
  seq     name             unique     origin     partial
  -------------------------------------------------------
  0       idx_big_name     0          c          0
  （1 行）
  **`create index 索引名 on 表名 (列名)`。** **索引名自己起，但要有规律**——
  **`idx_表_列` 是最常见的命名法**，一眼看出它是给谁建的。
  **索引是建在表上的，不是建在列上的**：
  **一张表可以有多个索引，一个索引可以包含多列。** **所以命名里带上表和列，以后删的时候不用猜。**

  索引名是全局的，不是表内唯一
  SQL> SELECT name, tbl_name FROM sqlite_master WHERE type = 'index'
  name             tbl_name
  --------------------------
  idx_big_name     big
  （1 行）
  SQL> CREATE INDEX idx_big_name ON other (name)
  报错：index idx_big_name already exists
  **在另一张表上建同名索引会报 `index idx_big_name already exists`。**
  **索引名在整个库里唯一，不像列名是表内唯一。**
  **所以「`idx_name`」这种名字在第二个表上就撞了**——**这也是为什么推荐把表名带上。**

  建完就生效，不用重启
  SQL> EXPLAIN QUERY PLAN SELECT id FROM big WHERE name = '用户40000'
  SEARCH big USING COVERING INDEX idx_big_name (name=?)

  **`CREATE INDEX` 一执行完，后面所有查询立刻就能用它。** **没有「重启生效」这回事。**
  **代价是「建索引这个动作本身很慢」**：
  **它要把已有的 5 万行全部排一遍序。** **大表上建索引可能几分钟到几小时，期间还会锁表**——
  **所以建索引要挑低峰期，或者用 MySQL 的 `ONLINE` 选项。**

  唯一索引：既查得快，又管不重复
  SQL> CREATE UNIQUE INDEX uq_big_name ON big (name)
  完成
  SQL> PRAGMA index_list(big)
  seq     name             unique     origin     partial
  -------------------------------------------------------
  0       uq_big_name      1          c          0
  1       idx_big_name     0          c          0
  （2 行）
  **`unique` 那一列是 1 了。** **唯一索引干两件事：查得快 + 不许重复。**
  SQL> INSERT INTO big (id, name) VALUES (90001, '用户00001')
  报错：UNIQUE constraint failed: big.name
  **插重复的名字被拦，报 `UNIQUE constraint failed: big.name`。**
  **第 9.6 篇讲的 `UNIQUE` 约束，底层就是一个唯一索引**——
  **建表时写 `UNIQUE` 和事后 `CREATE UNIQUE INDEX`，效果一样。**

  唯一索引建不上的情况
  SQL> CREATE UNIQUE INDEX uq_dup_city ON dup (city)
  报错：UNIQUE constraint failed: dup.city
  **报 `UNIQUE constraint failed: dup.city`。** **表里已经有两个「北京」了，唯一索引建不上。**
  **所以「给老表加唯一约束」要先清重复**：
  `SELECT city, COUNT(*) FROM dup GROUP BY city HAVING COUNT(*) > 1`，
  **先查出来，决定留哪个、删哪个，再建索引。**
  **这一步和第 9.10 篇「重建表」是同一个思路：先处理数据，再加约束。**

  复合索引与最左前缀
  SQL> CREATE INDEX idx_big_city_age ON big (city, age)
  完成
  SQL> PRAGMA index_info(idx_big_city_age)
  seqno     cid     name
  ------------------------
  0         2       city
  1         3       age
  （2 行）
  **`(city, age)` 这个顺序很关键**：**索引先按 `city` 排，`city` 相同的再按 `age` 排。**
  SQL> EXPLAIN QUERY PLAN SELECT COUNT(*) FROM big WHERE city = '北京'
  SEARCH big USING COVERING INDEX idx_big_city_age (city=?)

  SQL> EXPLAIN QUERY PLAN SELECT COUNT(*) FROM big WHERE city = '北京' AND age = 30
  SEARCH big USING COVERING INDEX idx_big_city_age (city=? AND age=?)

  SQL> EXPLAIN QUERY PLAN SELECT COUNT(*) FROM big WHERE age = 30
  SCAN big USING COVERING INDEX idx_big_city_age

  **前两条走 `SEARCH`，第三条退化成 `SCAN`。** **这就是「最左前缀」**：
  **索引只能从最左边那列开始用，跳过 `city` 直接查 `age` 用不上。**
  **所以复合索引的列顺序要按「查询里最常用的那个放左边」来排。**
  **`(city, age)` 和 `(age, city)` 是两个不同的索引，能用的查询也不一样。**

  IF NOT EXISTS：脚本里几乎都要带
  SQL> CREATE INDEX idx_big_name ON big (name)
  报错：index idx_big_name already exists
  **第二句不报错。** **和第 8、11 章的 `if not exists` 一样，目的是让脚本能重复跑。**
  **建索引的脚本尤其要注意这一点**——**大表上重复建一次索引，可能就是几十分钟的无效等待。**

第一段 index_list 一行：idx_big_name，origin 是 c。 第二段报 index idx_big_name already exists，跨表也撞。 第三段建唯一索引后 unique 变 1，插重复名字被拦。 第四段唯一索引建不上，报 UNIQUE constraint failed: dup.city。 第五段三条计划：查 city 走 SEARCH、city 加 age 也走 SEARCH、 只查 age 退回 SCAN。 第六段第二句不报错，if not exists 生效。

30 秒实验

(city, age) 这个索引能不能支持 where age = 30 and city = '北京'？（注意条件顺序变了，但列还是那两列）
给一张 100 万行的表建索引，期间写入会怎样？怎么避开？
为什么给老表加唯一约束之前要先清重复数据？

常见误区

索引名起成 idx_name、idx_age。 索引名全库唯一，第二张表上就撞，必须带表名。
复合索引按「查询里写的顺序」排列。 要按「最常用的列放最左」排，否则那列单独查时用不上。
在有重复数据的表上直接建唯一索引。 会整条回滚，得先清重复。

自测三问（点开对答案）

1. 索引名的唯一性范围是什么？ —— 整个数据库唯一，不是表内唯一。所以不同表上也不能同名，习惯上带表名。 2. `(city, age)` 这个复合索引，什么查询用不上？ —— 只按 `age` 查的用不上。最左前缀原则：不能跳过 `city` 直接用 `age`。 3. 建表时写 `UNIQUE` 和事后 `create unique index` 有区别吗？ —— 底层是同一份唯一索引，效果一样。区别只是「建表时就管住」还是「事后补上」，后者要先清掉已有重复值。

索引建好了，下一篇看怎么查它：12.3 查看索引——三处能看到，一处能验。

☐ （本篇完，下一篇 12.3 查看索引）