一、DROP INDEX：一条语句，立刻见效

删索引和建索引一样，是一条 DDL，执行完立刻生效，数据一条不动。 但它的影响不是「删的那一刻」，是「删之后每一条相关查询」。

同一句 SQL，删前 SEARCH、删后 SCAN，耗时差 47 倍。 而且它不会报错，只会让页面一点点变卡——这类退化最难查。 这里有个坑要专门说：用同一个连接反复取执行计划，可能读到旧计划。 实测删完索引后 EXPLAIN QUERY PLAN 还显示 SEARCH ... USING INDEX i_code， 换成新开的连接才看到真正的 SCAN。 原因是连接的语句缓存：同一段 SQL 文本会复用之前准备好的语句，表结构变了也不重新编译。 所以凡是拿执行计划当依据的脚本，取计划这一步要新开连接，别图省事复用。

二、删之前先看谁在用

做法很朴素：把线上常跑的查询列出来，逐条看执行计划。 哪条的计划里出现了 USING INDEX i_xxx，就说明它在被用。

五行里三行用上了索引，两行没有。这两行要分开看。 第四行 LIKE 'C0100%' 是老问题：LIKE 默认不区分大小写，大小写折叠之后命中的行 在索引里不再是连续的一段，区间查找失效。 所以它不是「索引没用」，是「这条写法用不上索引」。 第五行按 body 查本来就没建索引，SCAN 是正常结果。 检查的时候要分清「该用没用上」和「本来就没有」，这两种情况的处理方式完全不同。

三、有些索引删不掉：自动索引

建一张带唯一约束和主键的表，PRAGMA index_list 会多出两条自动索引。

删它直接报错：index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped。 这句报错把原因说得很清楚：它是约束的一部分，不能单独删。 要删它，得先把那个约束去掉——也就是改表结构，不是删索引。 判断办法：sqlite_master 里 sql 字段是空的那几条，就是自动生成的。 自动生成的删不掉，手写的才删得掉，这一条在 MySQL 里也一样。 MySQL 里对应的是主键索引和唯一约束建的索引，删法各不一样： 主键要 ALTER TABLE ... DROP PRIMARY KEY， 唯一索引要 ALTER TABLE ... DROP INDEX 名字。

四、索引失效的三种情形

除了删掉，索引还会「白建」——建了但用不上。常见三种，实测结果和书上不太一样。

第一种最常见：substr(code,1,6) = 'C01000' 这样的条件，索引里存的是 code 的原值。 解法是建表达式索引，或者把条件改写到列这一侧。 第二种 LIKE '%10000'：开头不确定，索引没法定位起点。 只有 '前缀%' 这种才可能用上，而它还要看大小写折叠那一条。 第三种要特别说明：沙箱这里其实还是用上了索引。 因为 SQLite 会按列的类型做隐式转换再查，而 MySQL 遇到「字符串列和数字比」 可能直接把列转成数字，那就用不上索引了。 这一格值得记成一句话：同一句 SQL 在两边「用没用上索引」可能不同，只能看计划。 凭「我学过索引失效的几种情形」去猜，会猜错。

五、删索引前的检查清单

把前面几节收成一张可以照着做的单子。

第一行和最后一行是重点：先有依据，再有观察期。 「先删了看看有没有人报错」这种办法在生产上等于拿用户做实验。 更稳的做法是「先加一个新索引，确认用上之后再删旧的」。 中间那段时间多占一点地方，换来的是「随时可以退回去」。 第 11 章到这里结束。这一章真正要带走的不是语法，是这张清单的思路： 建索引看「查询要不要」，删索引看「计划还认不认」。 11.5 的练习可以挑这三道：给 code 列建前缀索引并量体积、 把 (a, b) 换成 (b, a) 再看计划、 删掉 i_cat 前后各量一次 WHERE cat = 3。 三道题都动手做过，这一章就算真读完了。

六、动手 Demo：删索引，以及删前删后的对照

这一段先删掉一个索引，量删前删后的计划与耗时；再演示「同一连接读到旧计划」这个坑； 然后列出删前检查的做法，最后看自动索引为什么删不掉。

import sqlite3
c, p, _ = build(u"d053.db", idx=True)
q = u"SELECT body FROM tb_big WHERE code = 'C010000'"

# ① 删之前
print(c.execute(u"EXPLAIN QUERY PLAN " + q).fetchone()[-1])

# ② 删掉，同一个连接取计划
c.execute(u"DROP INDEX i_code")
c.commit()
print(c.execute(u"EXPLAIN QUERY PLAN " + q).fetchone()[-1])

# ③ 新开连接取计划
c2 = sqlite3.connect(p)
print(c2.execute(u"EXPLAIN QUERY PLAN " + q).fetchone()[-1])

# ④ 删前检查：把常跑的查询列出来，逐条看计划
for s in [u"SELECT body FROM tb_big WHERE code = 'C010000'",
          u"SELECT body FROM tb_big WHERE cat = 3",
          u"SELECT body FROM tb_big WHERE code LIKE 'C0100%'"]:
    print(c.execute(u"EXPLAIN QUERY PLAN " + s).fetchone()[-1])

# ⑤ 自动索引删不掉
c.execute(u"CREATE TABLE tb_v (a TEXT UNIQUE, b TEXT, PRIMARY KEY (b))")
print(c.execute(u"PRAGMA index_list(tb_v)").fetchall())
try:
    c.execute(u"DROP INDEX sqlite_autoindex_tb_v_1")
except sqlite3.OperationalError as e:
    print(e)

实跑输出：

--------------------------------------------------------------------
第五十三课：删索引，和「删了之后谁受影响」
--------------------------------------------------------------------

  **教材 11.3 只有一句 `DROP INDEX`，语法一分钟学完。**
  **难的是删之前的判断：这个索引还有没有查询在靠它。**
  **这一课把「删前看计划、删后看变化」做成一套可复用的动作。**
  **顺带厘清一件常被误解的事：有些索引根本删不掉。**

  一、DROP INDEX：一条语句，立刻见效
  --------------------------------------------------------------------

  时机              执行计划                                   耗时
  ----------------------------------------------------------------------
  删之前            SEARCH tb_big USING INDEX i_code (code=?)  53 微秒
  删之后（原连接）  SEARCH tb_big USING INDEX i_code (code=?)  2.50 毫秒
  删之后（新连接）  SCAN tb_big                                2.50 毫秒
  **第二行和第三行是同一时刻、同一句 SQL，计划却不一样。**
  **第二行那个 `SEARCH` 是假的——索引已经删了，它不可能还在。**
  **原因是连接的语句缓存。**
  **同一个连接反复执行一模一样的文本，会复用之前准备好的语句，表结构变了也不重新编译。**
  **换一句文本（哪怕多一个空格），或者干脆新开一个连接，读到的才是真计划。**
  **这一条不只影响看计划，也影响「刚改完表结构就查」的脚本。**
  **凡是拿执行计划当依据的地方，取计划这一步要新开连接，别图省事复用。**
  **看第三行：删掉之后执行计划从 `SEARCH` 变回 `SCAN`，耗时涨了 47 倍。**
  **这就是「删索引」的代价：不是删的那一刻慢，是之后每一条相关查询都变慢。**
  **而且它不会报错，只会让页面一点点变卡。**
  **所以删索引必须有依据，不能凭「这个索引好像没人用」。**
  **依据就是执行计划：哪条查询的计划里出现了 `USING INDEX i_xxx`，就说明它在被用。**

  二、删之前先看谁在用
  --------------------------------------------------------------------

  查询             索引  执行计划
  -------------------------------------------------------------------------
  按 code 精确查   用了  SEARCH tb_big USING INDEX i_code (code=?)
  按 code 取 code  用了  SEARCH tb_big USING COVERING INDEX i_code (code=?)
  按 cat 查        用了  SEARCH tb_big USING INDEX i_cat (cat=?)
  按 code 前缀查   没用  SCAN tb_big
  按 body 查       没用  SCAN tb_big
  **这张表就是「删前检查」的样子：把线上常跑的查询列出来，逐条看计划。**
  **五行里三行用上了索引，两行没有。**
  **第四行那个 `LIKE 'C0100%'` 又是老问题：`LIKE` 默认不区分大小写，索引区间失效。**
  **所以它不是「索引没用」，是「这条写法用不上索引」。**
  **第五行按 `body` 查，本来就没建索引，`SCAN` 是正常结果。**
  **检查的时候要分清「该用没用上」和「本来就没有」，两种情况处理方式完全不同。**

  三、有些索引删不掉：隐式索引
  --------------------------------------------------------------------

  索引名                   唯一  来源
  -----------------------------------
  sqlite_autoindex_tb_v_2  是    pk
  sqlite_autoindex_tb_v_1  是    u
  **删它直接报错：`index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped`。**
  **这句报错把原因说得很清楚：这个索引是唯一约束（或主键）的一部分，不能单独删。**
  **要删它，得先把那个约束去掉——也就是改表结构，不是删索引。**
  **判断办法：`sqlite_master` 里 `sql` 字段是空的那几条，就是自动生成的。**
  **自动生成的删不掉，手写的才删得掉，这一条在 MySQL 里也一样。**

  四、索引失效的三种情形
  --------------------------------------------------------------------

  情形          执行计划
  -------------------------------------------------------
  列被函数包住  SCAN tb_big
  前缀通配符    SCAN tb_big
  隐式类型转换  SEARCH tb_big USING INDEX i_code (code=?)
  **第一种最常见：`substr(code,1,6)` 这样的条件，索引里存的是 `code` 的原值，用不上。**
  **解法是建表达式索引，或者把条件改写到列这一侧。**
  **第二种是 `LIKE '%...'`：开头不确定，索引没法定位起点。**
  **只有 `'前缀%'` 这种才可能用上索引，而它还要看大小写折叠那一条。**
  **第三种要说明一下：沙箱这里其实还是用上了索引。**
  **因为 SQLite 会按列的类型做隐式转换再查，而 MySQL 遇到「字符串列和数字比」**
  **可能直接把列转成数字，那就用不上索引了。**
  **这一格值得记成一句话：同一句 SQL 在两边「用没用上索引」可能不同，只能看计划。**
  **凭「我学过索引失效的几种情形」去猜，会猜错。**

  五、删索引前的检查清单
  --------------------------------------------------------------------

  问题              怎么查
  ------------------------------------------------------
  这个索引有人用吗  扫常跑查询的执行计划
  删了会慢多少      删之前先量基线，删之后再量
  是自动索引吗      看 `sqlite_master` 的 `sql` 是否为空
  是唯一约束吗      `PRAGMA index_list` 的第三列
  临时删还是永久删  先删、观察一周、没问题再确认
  **第一行和最后一行是重点：先有依据，再有观察期。**
  **「先删了看看有没有人报错」这种办法在生产上等于拿用户做实验。**
  **更稳的做法是「先加一个新索引，确认用上之后再删旧的」。**
  **中间那段时间多占一点地方，换来的是「随时可以退回去」。**
  **第 11 章到这里结束。这一章真正要带走的不是语法，是这张清单的思路：**
  **建索引看「查询要不要」，删索引看「计划还认不认」。**

第一组三行里，第二行是假的：索引已经删了，计划却还说 USING INDEX i_code。 第三行新开连接之后才变成 SCAN，耗时也从微秒级涨到毫秒级。 这个坑很值得记：它不影响业务，只影响「你看到的东西」。 而调索引的人恰恰是靠「看到的东西」做判断的，所以它危险。 第四组三行就是「删前检查」的雏形，一行用了索引、一行用另一个索引、一行没用。 把线上 Top 20 的查询都这么过一遍，就有一张可以照着决策的单子。 第五组两行：PRAGMA index_list 列出两条自动索引，删第一条时报错。 报错原文里明说了「与唯一约束或主键关联的索引不能删」。 整段跑完能看出一个规律：删索引的风险不在删的动作，在删之后的沉默。 建索引有报错、有反馈，删索引只有变慢，没有提示。

30 秒实验

1. 删掉 i_cat 再重跑「删前检查」，看按 cat 查那一行怎么变。
2. 把 DROP INDEX 换成 DROP INDEX IF EXISTS，对不存在的名字试一次，看会怎样。
3. 用同一个连接连删两个索引再取计划，看缓存的影响是一次性的还是持续的。

常见误区

1. 看某个索引名字眼生就直接删，不问有没有查询在靠它。
2. 删完立刻用同一个连接看执行计划，看到 SEARCH 就以为没事。
3. 想删掉 sqlite_autoindex_* 这类索引，反复试语法，其实是约束的一部分。

第 11 章到这里结束。下一篇进第 12 章：视图，一种「存起来的查询」。

• ☐ （本篇完，下一篇 12.1 视图概述）

相关笔记

• 索引概述
• 创建索引
• 单表查询
• 🛢️ MySQL从入门到精通 · 索引