一、索引有没有用：直接量

五万行、cat 上有索引。先看有索引时的计划和耗时，再把索引删掉量一次。

这就是索引的全部意义：把「从头扫五万行」变成「按索引定位几百行」。 注意上面那张表第二列只有两个词——SEARCH 和 SCAN。 整章的判断都可以从这两个词出发。 这里有个坑必须单独说：删了索引之后，原连接给出的计划还是 SEARCH。 那是语句缓存里的旧计划，不是真的。换一个新连接才看得到 SCAN。 这个坑的可怕之处在于「它让你以为改动生效了」。 你删了索引、看计划没变、以为是别的原因，于是一路查错方向。 规矩很简单：改结构之后，用新连接看计划。

二、复合索引：最左前缀是物理顺序

建一个 (cat, stock) 的复合索引，然后三种查询各量一次：

第三行是重点：只用 stock 时，索引变成「整段扫」。 它扫的还是索引（比表小），但已经不是「定位」了。 原因不神秘：复合索引是按 (cat, stock) 这个顺序物理排的。 cat 相同的一批里，stock 才有顺序；cat 不定，stock 在整个索引里就是乱的。 所以「最左前缀」不是规定，是排序的必然结果。 这条也解释了另一个常见困惑：为什么 (a, b) 和 (b, a) 是两个不同的索引。 它们不是「同一份数据的两种叫法」，是两份不同顺序的副本。 所以「多建几个复合索引覆盖各种顺序」这个想法，代价是成倍增长的写开销。 实用做法是：把「等值查询用得最多的列」放最左，把「范围查询的列」放最后。 因为一旦用了范围（>、<、BETWEEN），它右边的列就没法再用来定位了。

三、LIKE 前缀：这次实测没走索引

教材说「LIKE 'abc%' 能用索引，LIKE '%abc' 不能」。这一条在 SQLite 上要打个折扣。

第一行仍是 SCAN——这次实测里，LIKE 前缀 并没有变成范围查找。 改成 >= 加 < 之后，计划立刻变成 SEARCH，快了两个数量级。 SQLite 对 LIKE 走索引是有条件的，大小写敏感设置、列的比较规则都要对上。 条件不满足就退回全扫，而且不提示。 所以「LIKE 前缀能用索引」这句话，得加上「在这个引擎、这个配置下」。 实用结论：能改写成范围的，就改写成范围。 它不依赖任何开关，两边引擎都认，行为还更可预测。 而且范围写法顺便解决了「大小写敏感」这个隐藏变量——LIKE 的行为跟它绑在一起。

四、函数包裹列：最隐蔽的一种失效

三种写法，第一个是裸列比较，后两个把列包在函数里：

只要列被函数包住，索引就用不上了。 原因是索引里存的是「原值」，不是「函数算完之后的值」。 引擎没法反推「哪个原值算出来等于这个」，只能一行行算一遍。 这条规则在 MySQL 上一样成立，而且更常见。最经典的失效写法是日期：

-- 索引失效：列被 DATE() 包住了
SELECT * FROM tb_order WHERE DATE(create_time) = '2026-11-01';

-- 索引有效：列裸露，条件变成范围
SELECT * FROM tb_order
 WHERE create_time >= '2026-11-01'
   AND create_time <  '2026-11-02';

两句话在业务上完全等价，在计划上一个全扫一个定位。 而且第二种写法还顺便避开了「时区」这个坑——DATE() 用的是会话时区，范围比较用的是原值。

五、覆盖索引：少回一次表

三种取法，计划一模一样，耗时却差了一倍：

三行计划一模一样，都是走 i_cat 定位，差别在「定位之后还要不要去主表取数」。 取一列和取全部，回来搬的东西不一样多。 这就是覆盖索引的思路：如果查询要的列全在索引里，就不用回表。 所以「别写 SELECT *」不只是风格问题，它在有索引的查询上是实打实的开销。 但这条建议经常被当成教条，因为「没索引的时候两者一样」。 准确的说法是：SELECT * 的代价取决于「有没有索引」和「索引覆盖了几列」。 没有索引时全扫，反正要把整行读出来，取几列都一样。

六、动手 Demo：索引、复合索引、LIKE、函数包裹、覆盖，五组对照

这一段五组对照，每组都是「同一张表、同一个结果、不同写法」。 先量有索引和没索引（顺便演示「旧计划骗人」那个坑），再看复合索引的最左前缀， 然后把 LIKE 前缀 改写成范围，再试函数包裹列，最后量覆盖索引。

import sqlite3, time

c = sqlite3.connect(u"shop.db", isolation_level=None)
c.execute(u"CREATE TABLE tb_g (id INTEGER PRIMARY KEY, name TEXT, "
          u"cat INTEGER, price REAL, stock INTEGER)")
c.execute(u"CREATE INDEX i_cat ON tb_g(cat)")
c.execute(u"CREATE INDEX i_name ON tb_g(name)")
c.execute(u"BEGIN")
c.executemany(u"INSERT INTO tb_g VALUES (?,?,?,?,?)",
              [(i, u"商品%05d" % i, i % 100, 1.0 * i, i % 50)
               for i in range(1, 50001)])
c.execute(u"COMMIT")

def best(fn, n=5):
    t = None
    for _ in range(n):
        t0 = time.perf_counter(); fn()
        dt = time.perf_counter() - t0
        t = dt if t is None else min(t, dt)
    return t

def plan(sql):
    return u" / ".join(r[-1] for r in c.execute(u"EXPLAIN QUERY PLAN " + sql))

# ① 有索引 vs 没索引
q = u"SELECT count(*) FROM tb_g WHERE cat = 42"
t1 = best(lambda: c.execute(q).fetchone())
c.execute(u"DROP INDEX i_cat")
t2 = best(lambda: c.execute(q).fetchone())      # 故意不换连接
c2 = sqlite3.connect(u"shop.db", isolation_level=None)
print(plan(q), plan(q))                          # 一个旧一个不准
print(c2.execute(u"EXPLAIN QUERY PLAN " + q).fetchall()[0][-1])

# ② 复合索引：最左前缀
c.execute(u"CREATE INDEX i_cat ON tb_g(cat)")
c.execute(u"CREATE INDEX i_cs ON tb_g(cat, stock)")
for cond in (u"cat=42 AND stock=3", u"cat=42", u"stock=3"):
    s = u"SELECT count(*) FROM tb_g WHERE " + cond
    print(cond, best(lambda s=s: c.execute(s).fetchone()) * 1000, plan(s))

# ③ LIKE 前缀 vs 范围改写
for s in (u"SELECT count(*) FROM tb_g WHERE name LIKE '商品012%'",
          u"SELECT count(*) FROM tb_g WHERE name >= '商品012' "
          u"AND name < '商品013'"):
    print(best(lambda s=s: c.execute(s).fetchone()) * 1000, plan(s))

实跑输出：

--------------------------------------------------------------------
优化查询：把 SCAN 变成 SEARCH
--------------------------------------------------------------------


  索引有没有用：直接量
  --------------------------------------------------------------------
**五万行、`cat` 上有索引。先看有索引时的计划和耗时：**
SEARCH tb_g USING COVERING INDEX i_cat (cat=?)
耗时 0.0632 毫秒
**把索引删掉，换一个新连接再看——同一个连接会复用旧计划，看到的还是老样子：**
SCAN tb_g
耗时 4.1870 毫秒
**同一个查询，差 66.2 倍。**
**注意上面那个坑：删了索引之后，原连接给出的计划还是 `SEARCH`——那是语句缓存里的旧计划，**
**不是真的。**
**所以「改完看计划」这件事，得换一个连接看，否则会被旧计划骗过去。**
**这一点在第 11 章删索引时也踩过，是同一类问题。**

  复合索引：最左前缀不是口诀，是物理顺序
  --------------------------------------------------------------------
查询条件    毫秒    执行计划
-------------------------------------------------------------------
两列都用    0.0314  SEARCH tb_g USING COVERING INDEX i_cs (cat=? A…
只用 cat    0.0649  SEARCH tb_g USING COVERING INDEX i_cat (cat=?)
只用 stock  3.6031  SCAN tb_g USING COVERING INDEX i_cs
**第三行是重点：只用 `stock` 时，索引变成「整段扫」——`SCAN ... USING COVERING INDEX i_cs`。**
**它扫的还是索引（比表小），但已经不是「定位」了。**
**原因不神秘：复合索引是按 `(cat, stock)` 这个顺序物理排的。**
**`cat` 相同的一批里，`stock` 才有顺序；`cat` 不定，`stock` 在整个索引里就是乱的。**
**所以「最左前缀」不是规定，是排序的必然结果。**
**这条也解释了另一个常见困惑：为什么 `(a, b)` 和 `(b, a)` 是两个不同的索引。**
**它们不是「同一份数据的两种叫法」，是两份不同顺序的副本。**

  LIKE 前缀：这次实测没走索引，改写才行
  --------------------------------------------------------------------
**教材说「`LIKE 'abc%'` 能用索引，`LIKE '%abc'` 不能」。这一条在 SQLite 上要打个折扣。**
写法       毫秒    执行计划
----------------------------------------------------------------
LIKE 前缀  5.7525  SCAN tb_g USING COVERING INDEX i_name
范围改写   0.0388  SEARCH tb_g USING COVERING INDEX i_name (nam…
**第一行仍是 `SCAN`——这次实测里，`LIKE 前缀` 并没有变成范围查找。**
**改成 `>=` 加 `<` 之后，计划变成 `SEARCH ... (name>? AND name<?)`，快了上百倍。**
**SQLite 对 `LIKE` 走索引是有条件的（大小写敏感设置、列的比较规则都要对上），**
**条件不满足就退回全扫，而且不提示。**
**所以「LIKE 前缀能用索引」这句话，得加上「在这个引擎、这个配置下」。**
**实用结论：能改写成范围的，就改写成范围。**
**它不依赖任何开关，两边引擎都认，行为还更可预测。**

  函数包裹列：最隐蔽的一种失效
  --------------------------------------------------------------------
写法                毫秒     执行计划
------------------------------------------------------------------------
裸列比较            0.0318   SEARCH tb_g USING COVERING INDEX i_name (n…
包了函数            12.8469  SCAN tb_g USING COVERING INDEX i_name
包了函数（范围大）  9.4105   SCAN tb_g USING COVERING INDEX i_name
**第一行 `SEARCH`，后两行 `SCAN`——只要列被函数包住，索引就用不上了。**
**原因是索引里存的是「原值」，不是「函数算完之后的值」。**
**引擎没法反推「哪个原值算出来等于这个」，只能一行行算一遍。**
**这条规则在 MySQL 上一样成立，而且更常见：**
**`WHERE DATE(create_time) = '2026-11-01'` 是最经典的失效写法。**
**改成 `WHERE create_time >= '2026-11-01' AND create_time < '2026-11-02'`，索引立刻回来。**

  覆盖索引：少回一次表就快一截
  --------------------------------------------------------------------
取哪些列    毫秒    执行计划
---------------------------------------------------------
只取 name   0.5506  SEARCH tb_g USING INDEX i_cat (cat=?)
取三列      0.7577  SEARCH tb_g USING INDEX i_cat (cat=?)
`SELECT *`  1.0090  SEARCH tb_g USING INDEX i_cat (cat=?)
**三行的计划一模一样，都是走 `i_cat` 定位，耗时却差了一倍。**
**差别在「定位之后还要不要去主表取数」——取一列和取全部，回来搬的东西不一样多。**
**这就是覆盖索引的思路：如果查询要的列全在索引里，就不用回表。**
**所以「别写 `SELECT *`」不只是风格问题，它在有索引的查询上是实打实的开销。**
**当然，没索引的时候两者一样——这也是为什么这条建议经常被当成「教条」。**

第①步那两个 plan(q) 打印出来会是两个不同的值——因为第一个来自缓存。 而新连接给出的那个，才是「索引真的没了」之后的计划。 这个演示故意把坑摆在明面上：同一句、同一个对象，两个连接看到两个世界。 第②步三行的耗时单调上升、计划从 SEARCH 滑到 SCAN。 「只用 stock」那一行是最值得盯的：它扫的是索引不是表，所以比全表扫快一些，但仍然不是定位。 这种「半好不坏」的状态最容易被误判成「已经优化了」。 第③步两组数差了两个数量级，而写法只差一个 LIKE 和两个比较符。 这就是为什么值得花时间把 LIKE 前缀 改写成范围——一次改写，长期受益。

30 秒实验

1. 给 (stock, cat) 建一个反向的复合索引，重复第②步的三条查询，看计划怎么变。
2. 把第③步的 LIKE 改成 LIKE '%商品012'（后缀匹配），看计划是不是还是 SCAN。
3. 在 tb_g 上加一个 CREATE INDEX i_cs2 ON tb_g(cat, stock, price)， 查 SELECT price FROM tb_g WHERE cat=42 AND stock=3，看是不是 COVERING。

常见误区

1. 删了索引之后看计划，发现没变，以为删除失败。其实是语句缓存给出了旧计划，换连接才准。
2. 以为 LIKE 'abc%' 一定走索引。要看引擎和配置。这次实测就没走，改写成范围才走。
3. 以为多建几个复合索引就能覆盖各种查询。每个索引都要在每次写入时维护， 写多读少的表上这是净亏。

查询优化讲完了。下一篇往上走一层：表结构本身怎么设计得更省。

• ☐ （本篇完，下一篇 19.3 优化数据库结构）

相关笔记

• 优化概述
• 删除索引
• 🛢️ MySQL从入门到精通 · 索引