一、语法：一句 CREATE INDEX

教材给的原码长这样（SQL Server）：

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX ix_name
    ON tb_goods (gname [ASC | DESC], price [ASC | DESC])
    WITH (FILLFACTOR = 80);
GO

方括号里的都可省。 全省就是「普通、非聚集、升序」的单列索引。 UNIQUE 加约束，CLUSTERED 抢那个唯一的聚集位置。 FILLFACTOR 是给页留空的比例， 留空是为了以后插入时少分裂页——读多的表可以低一点（页满、少占空间），写多的表留空多一点。 多数场景不写它，用默认值就行。 GO 不是 SQL 语句，是 SSMS 的「批分隔符」， 服务端根本收不到它——用程序连库时写 GO 会直接报语法错。 SQLite 这边的写法只有一种，而且没有聚集/非聚集之分：

CREATE [UNIQUE] INDEX ix_name ON tb_goods (gname, price)

查索引的目录也不一样： SQL Server 用 sys.indexes 配 sp_helpindex '表名'， SQLite 直接查 sqlite_master，里面的 sql 字段就是建索引那句原话。

二、单列索引：最常见的那一种

一句 CREATE INDEX ix_t_k ON t(k) 之后，索引就躺在 sqlite_master 里了：

注意只建了一个索引，表里就是多了一行记录。 索引不是什么玄学， 它就是一张「键 → 行号」的有序表，由数据库自动维护。 建完立刻能用，不用重启、不用刷新。 唯一的代价是建索引那一刻要扫一遍全表—— 几十万行就是几秒钟，上百万行就该挑业务低峰做。

三、唯一索引：既加速又约束

加一个 UNIQUE：

CREATE UNIQUE INDEX ux_t_name ON t(name)

这时候插重复值会直接被拒：

消息 102，级别 15，状态 1
UNIQUE constraint failed: t.name

这跟主键的效果一样，差别只有一条： 主键不许 NULL，唯一索引允许（而且允许多个 NULL）。 因为 NULL 跟谁都「不相等」，所以两个 NULL 不算重复。 这个差别在业务上很有用： 比如「身份证号」——绝大多数人必须有、且不能重复， 那就用唯一索引，让个别没登记的人留 NULL；换成主键，这些人就插不进去了。 反过来也要当心： 唯一索引一旦建上，历史脏数据会立刻暴露——建的时候如果表里已经有重复值， 语句直接失败，得先清洗。 所以正确顺序是：先查重复，再建索引。

四、组合索引：列顺序决定能不能用上

建一个 (k, v) 的组合索引，然后拿三条查询去试：

为什么只按 v 查就用不上？ 因为组合索引是「先按 k 排、 k 相同的再按 v 排」——v 只在同一个 k 内部有序，全局是乱跳的。 跳过第一层直接找第二层，等于在一本按「省」再按「市」排的册子里， 只按市名找——每个省都得翻一遍。 这就是「最左前缀」：组合索引只能从最左边的列开始用，缺一列就用不上。 (k, v) 能支持 k、能支持 k + v，但不支持单独 v。 所以建组合索引的第一条规矩：把最常单独拿来查的列放最左边。 第二条：别建 (k, v) 又建 (k)——后者是多余的，前者已经能当它用。

五、选择性：不是所有列都值得建

10 万行的表，三列的选择性差得很远：

选择性 = 不同取值数 ÷ 总行数。越接近 1 越值得建索引。 sex 只有两种取值，查一次命中一半行—— 这时候走索引反而更慢：要先读索引、再回表取一半的行，还不如直接扫全表。 优化器其实懂这件事， 所以它对低选择性的列往往「不用索引」——你建了，它也不用，白占空间、白拖写入。 低选择性的列不是完全没用，正确的用法是放进组合索引的后半段：比如「按城市 + 性别查」， (city, sex) 就比 (sex) 有用得多—— 因为 city 已经把范围缩小到三十分之一，再按 sex 过一遍就很快。

六、建索引时该考虑的四条

第四条最容易被忽略。 一张每天插入百万行的日志表， 建五个索引就是五倍的索引维护开销——而它多半只被「按时间查最近一小时」用到， 那一个索引就够了，甚至可以只建在时间列上。 经验值：读多写少的表可以多建，写多的表只建最关键的那一两个。 报表库、历史库可以放开建；订单、日志这种高频写的表，每加一个索引都该问一句「值不值」。

七、动手 Demo：三种索引各建一遍，再看三条执行计划

这一段把单列、唯一、组合三种索引依次建起来，每建一个就试一下它管不管用； 最后拿 10 万行数据数一遍「每列有几个不同值」，看选择性长什么样。 注意中间那句 DROP INDEX ix_t_k——不删掉单列索引，就看不出来组合索引到底管不管用。

import sqlite3

c = sqlite3.connect(u":memory:", isolation_level=None)
# ... 建一张 5 万行的 t(id, k, name, v) ...

# ① 单列
c.execute(u"CREATE INDEX ix_t_k ON t(k)")

# ② 唯一：既加速又约束
c.execute(u"CREATE UNIQUE INDEX ux_t_name ON t(name)")
c.execute(u"INSERT INTO t (k, name, v) VALUES (1, 'name100', 0)")
# -> UNIQUE constraint failed: t.name

# ③ 组合：先删掉单列那个，才看得出是谁在干活
c.execute(u"DROP INDEX ix_t_k")
c.execute(u"CREATE INDEX ix_t_kv ON t(k, v)")

for tag, q in ((u"WHERE k = ?", u"SELECT * FROM t WHERE k = 777"),
               (u"WHERE v = ?", u"SELECT * FROM t WHERE v = 777"),
               (u"ORDER BY k", u"SELECT * FROM t ORDER BY k")):
    plan = c.execute(u"EXPLAIN QUERY PLAN " + q).fetchall()
    print(tag, u"->", plan[0][3])

实跑输出：

--------------------------------------------------------------------
创建索引：单列、唯一、组合
--------------------------------------------------------------------


  ① 单列索引
  --------------------------------------------------------------------

  [教材原码 · 本沙箱不执行]
  | CREATE INDEX ix_goods_gname ON tb_goods (gname);
  | GO


  1> CREATE INDEX ix_t_k ON t(k)

  1> SELECT name, tbl_name FROM sqlite_master WHERE type = 'index' AND tbl_name = 't'
  name    tbl_name
  ----------------
  ix_t_k  t
  (1 行受影响)
  SQLite 的索引也躺在 sqlite_master 里；SQL Server 查 sys.indexes 配 sp_helpindex。
  「使用界面方式创建」那一小节跳过——SSMS 里右键点出来的东西，本质就是这句 CREATE INDEX。

  ② 唯一索引：既加速又约束
  --------------------------------------------------------------------

  1> CREATE UNIQUE INDEX ux_t_name ON t(name)
  唯一索引不只是快，它还保证「这一列不许重复」——插入重复值会直接报错：

  1> INSERT INTO t (k, name, v) VALUES (1, 'name100', 0)
  消息 102，级别 15，状态 1
  UNIQUE constraint failed: t.name
  报的是 UNIQUE constraint failed。这跟主键的效果一样，区别是主键每表只能一个。

  ③ 组合索引：列顺序决定能不能用上
  --------------------------------------------------------------------

  1> DROP INDEX ix_t_k
  先把刚才那个单列索引删掉，免得它抢了风头——下面三条查询只看 ix_t_kv 管不管用。

  1> CREATE INDEX ix_t_kv ON t(k, v)
  建了 (k, v) 这个组合索引。先看它能不能被用上：

  WHERE k = ?    -> SEARCH t USING INDEX ix_t_kv (k=?)
  WHERE v = ?    -> SCAN t
  ORDER BY k     -> SCAN t USING INDEX ix_t_kv
  ! 只有第一个用上了 ix_t_kv。第二个（只按 v 查）用不上——这就是「最左前缀」。
  组合索引 (k, v) 相当于按 k 排好序、k 相同的再按 v 排。
  只按 v 找，等于跳过了第一层排序，没法定位。
  所以建组合索引时，把「最常单独用来查的列」放在最左边。

  ④ 选择性：不是所有列都值得建索引
  --------------------------------------------------------------------
| 列 | 不同取值有几种 | 查一个值命中多少行 | 占全表 |
|---|---:|---:|---:|
| sex | 2 | 50000 | 50% |
| city | 3 | 33333 | 33% |
| id（主键） | 100000 | 1 | 0% |

  选择性 = 不同取值数 / 总行数。越接近 1 越值得建索引——
  像 sex 这种只有两个值的列，查一次命中一半行，索引反而更慢（要先读索引再回表，还不如直接扫）。
  这类列叫「低选择性」。它们适合放在组合索引的后半段，不适合单独建索引。

  ⑤ 建索引时该考虑的四条
  --------------------------------------------------------------------
| 考虑点 | 怎么判断 |
|---|---|
| 查询频率 | 天天被 WHERE 用到的列优先 |
| 选择性 | 不同取值多的列才值得单独建 |
| 组合顺序 | 最常单独查的列放最左边 |
| 写入代价 | 写多读少的表，索引要克制 |

  第四条常被忽略：一张每天插入百万行的日志表，建五个索引就是五倍的索引维护开销。
  经验值：读多写少的表可以多建，写多的表只建最关键的那一两个。

WHERE v = ? 那条是 SCAN t，这就是最左前缀的现场。 索引在，但用不上——因为组合索引的第一层是 k，只给 v 它没法定位。 ORDER BY k 那条是 SCAN t USING INDEX ix_t_kv，注意它没有 SEARCH。 意思是：还是要扫一遍，但扫的是索引、而且本来就是有序的—— 省掉了「排序」这一步，这本身就是提速。 唯一索引那条报错值得多看一眼： 报的是 UNIQUE constraint failed: t.name， 不是「违反了索引」——在你看来它是约束，在数据库看来它是索引的副产品。 所以「怎么让某一列不许重复」的标准答案是「建唯一索引」，不是「写触发器去查」。 选择性那张表是数出来的： sex 只有 2 种、city 只有 3 种、id 有 10 万种。 前两个建单独索引基本是白建，id 那种才值得。

30 秒实验

1. 把 DROP INDEX ix_t_k 那行注释掉，重跑。 WHERE k = ? 会变成 ix_t_k 而不是 ix_t_kv——同一个查询，优化器挑了更「窄」的那个。 这也说明多个索引并存时，不一定用你刚建的那个。
2. 把组合索引改成 (v, k)，重跑三条。 这次 WHERE k = ? 变成 SCAN，WHERE v = ? 用上了——列顺序换了，能用的查询也跟着换。
3. 给 sex 列建一个单独索引，跑 WHERE sex = '男' 看计划。 很可能还是 SCAN——优化器算过账：一半的行都要取，走索引不划算。建了也白建。

常见误区

1. 「组合索引的两列谁在前面都一样」。差得远了： (k, v) 支持按 k 查，(v, k) 支持按 v 查，不可互换。 列顺序按「最常单独查的列放左边」定。
2. 「给每个常用于查询的列都建单列索引最保险」。列多的时候， 优化器一次只能用一个（多数情况），剩下的全是纯负担： 占空间、拖慢写入、还让优化器多算一遍。
3. 「唯一索引和主键完全等价」。主键不许 NULL、每表只能一个； 唯一索引允许 NULL、可以建多个。 业务上的「不许重复但可以不填」就该用唯一索引。

下一篇 14.3 讲删除与维护：DROP INDEX 的两种写法、主键索引为什么删不掉，以及「删了一半行， 索引页还是 280 页」——为什么重建才真正紧凑。

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

相关笔记

• 索引的概念
• 索引的删除
• 子查询基础
• 47 课 Oracle从入门到精通 · 索引
• 🪟 SQL Server从入门到精通 · 索引