12.4 删除索引 · 波仔的 AI 编程实战课

📄 一句话概括

DROP INDEX 索引名，只给索引名，不给表名——因为索引名全库唯一。 删索引不动数据。 5 万行一行不少，最坏结果是「某个查询变回全表扫」，不是「数据没了」。 约束带来的自动索引删不掉， 报 index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped。

📝 本章和前面几章的关系

第 12 章的最后一篇。建、看、删，索引的三件事就齐了。 删索引比删表安全得多，但不是没有风险： 如果那个索引正被一个高频查询靠着，删掉就是一次性能事故。 所以这一篇的重点不是语法，是「判断该不该删」。

一、基本写法

drop index idx_big_name。 注意只给索引名、不给表名—— 因为索引名在全库唯一，引擎不需要知道它在哪张表上。 这和 drop table 表名 不一样： 表名要带，因为表名是「数据库内唯一」的，索引名是「全库唯一」的。 这个差别正好反映了两种对象的命名空间。 这也是「索引名要带表名」的另一个理由： 删的时候只有名字，名字里看不出归属就容易删错。 idx_big_name 和 idx_name，哪个更让人放心？

二、删索引不动数据

Demo 里删完索引再数一遍：select count(*) 还是 50000。 一行不少。 索引只是一份附加的目录，删了数据还在。 所以「删索引」比「删表」安全得多—— 最坏情况是「某个查询变慢了」，而不是「数据没了」。 但反过来说，删索引之后那个查询会立刻变回全表扫。 如果它是个高频查询，这就是一次性能事故： 接口从 5 毫秒变 500 毫秒，然后雪崩。 所以删之前先确认「没人靠它」： 翻一翻慢查询日志、问一问业务方、或者在测试环境先删掉观察几天。 别在生产库上「先删了看看」。

三、if exists：删不存在的索引不报错

删一个不存在的索引，报 no such index: idx_big_name。 加 if exists 就不报： drop index if exists 索引名。 这和前面几章的 if exists 一个道理： 让脚本能重复跑。 create index if not exists 加 drop index if exists，一套建表脚本才算能反复用。 这里有个容易忽略的点： if exists 吞掉的是「不存在」这个错，不是所有错。 名字拼错了也会被吞掉—— 脚本没报错，你以为删了，其实删的是空气。 所以调试阶段建议先不带 if exists 跑一遍。

四、约束带来的自动索引删不掉

Demo 里对 goods 的自动索引执行 drop index， 报 index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped。 意思是「这个索引跟约束绑在一起，不能单独删」。 想明白这一条，得先明白自动索引是干什么的： unique 要保证「不许重复」，靠的就是这份索引—— 插入时先查索引，有就拒。 删了索引，约束就没法执行了。 所以要删它，只能删约束本身。 而删约束在这个沙箱里往往意味着重建表（第 9.10 篇）。 MySQL 上 drop index 也删不掉主键索引，得用 alter table … drop primary key。

五、什么时候该删索引

一是重复的： (a) 和 (a, b) 同时存在，前者多余—— 最左前缀决定了 (a, b) 已经能覆盖「只查 a」的查询。 二是从来没被用过的： 建的时候想着「以后可能要用」，用了两年没人用。 这种索引只贡献成本、不贡献收益。 三是区分度太低的： 一列只有两三个不同值（性别、是否删除、状态位），建了索引也筛不掉多少行—— 优化器可能干脆不用它，白占空间。 但「区分度低」有个例外：数据倾斜。 99% 的行是「未删除」、1% 是「已删除」时，查那 1% 用索引是有效的—— 因为筛掉 99% 的收益很大。 这种情况下用「部分索引」更划算，只给那 1% 建。

六、删之前先留一条可逆的路

删之前把建索引的语句抄下来： 从 sqlite_master 的 sql 列取出来，存一份到文档或者迁移脚本里。 万一删错了，一句 create index 就能补回来。 注意补回来要时间： 大表上重建索引可能要几十分钟，这期间性能是掉的。 所以「可逆」不等于「无代价」， 只是「有退路」。 这一步和「删表前先改名」是同一个思路： 能回退的操作才敢做。 反过来说，没有备份语句就删索引，等于在赌。

七、动手 Demo：删一个索引，看数据还在不在，再撞两次删不掉

Demo 五段：基本写法、删完数一遍行数、试 if exists、撞自动索引、最后取回建索引的语句。 第二段那个 50000 是全篇的定心丸——删索引真的不动数据，但它会让某个查询变慢，这是两件事。

import _q46 as Q
c = Q.blank()
c.execute("CREATE TABLE big (id INTEGER PRIMARY KEY, name TEXT)")
c.executemany("INSERT INTO big VALUES (?,?)",
              [(i, '用户%05d' % i) for i in range(1, 50001)])
c.commit()
c.execute("CREATE TABLE goods (id INTEGER PRIMARY KEY, "
          "name TEXT UNIQUE)")
c.execute("CREATE INDEX idx_big_name ON big (name)")
c.execute("CREATE INDEX idx_big_city ON big (city)")

# 1) 基本写法：只给索引名，不给表名
Q.show(c, "PRAGMA index_list(big)")
Q.run(c, "DROP INDEX idx_big_name")
Q.show(c, "PRAGMA index_list(big)")

# 2) 删索引不动数据
Q.show(c, "SELECT COUNT(*) AS rows_left FROM big")

# 3) 删不存在的索引；带 if exists 就不报
c.execute("DROP INDEX idx_big_name")
c.execute("DROP INDEX IF EXISTS idx_big_name")

# 4) 约束带来的自动索引删不掉
Q.show(c, "PRAGMA index_list(goods)")
c.execute("DROP INDEX sqlite_autoindex_goods_1")

# 5) 删之前先把建索引的语句抄下来，删错了能补回来
Q.show(c, "SELECT sql FROM sqlite_master WHERE name = 'idx_big_city'")

实跑输出：

--------------------------------------------------------------------
第六十六课：删除索引——删对了省空间，删错了掉性能
--------------------------------------------------------------------

  基本写法
  SQL> PRAGMA index_list(big)
  seq     name             unique     origin     partial
  -------------------------------------------------------
  0       idx_big_name     0          c          0
  （1 行）
  SQL> DROP INDEX idx_big_name
  完成
  SQL> PRAGMA index_list(big)
  seq     name     unique     origin     partial
  -----------------------------------------------
  （0 行）
  **`drop index 索引名`。** **注意只给索引名、不给表名**——
  **因为索引名在全库唯一，引擎不需要知道它在哪张表上。**
  **这也是「索引名要带表名」的另一个理由**：**删的时候只有名字，名字看不出归属就容易删错。**

  删索引不影响数据
  SQL> SELECT COUNT(*) AS rows_left FROM big
  rows_left
  ----------
  50000
  （1 行）
  **5 万行一行不少。** **索引只是一份附加的目录，删了数据还在。**
  **所以「删索引」比「删表」安全得多**，**最坏情况是「某个查询变慢了」，而不是「数据没了」。**
  **反过来说，删索引之后那个查询会立刻变回全表扫**——
  **如果它是个高频查询，这就是一次性能事故。** **所以删之前先确认「没人靠它」。**

  if exists：删不存在的索引不报错
  SQL> DROP INDEX idx_big_name
  报错：no such index: idx_big_name
  **第一次报 `no such index: idx_big_name`，加 `if exists` 就不报。**
  **和前面几章的 `if exists` 一个道理。**

  自动索引删不掉
  SQL> PRAGMA index_list(goods)
  seq     name                         unique     origin     partial
  -------------------------------------------------------------------
  0       sqlite_autoindex_goods_1     1          u          0
  （1 行）
  SQL> DROP INDEX sqlite_autoindex_goods_1
  报错：index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped
  **报 `index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped`**——
  **「这个索引是跟约束绑在一起的，不能单独删」。**
  **自动索引是约束的一部分**：
  **`UNIQUE`、`PRIMARY KEY` 会自动建索引，删了约束就没了。** **要删它，只能删约束本身**——
  **而删约束在这个沙箱里往往意味着重建表（第 9.10 篇）。**
  **MySQL 上 `DROP INDEX` 也删不掉主键索引，得用 `ALTER TABLE … DROP PRIMARY KEY`。**

  什么时候该删索引
  **一是重复的**：`(a)` 和 `(a, b)` 同时存在，前者多余。
  **二是从来没被用过的**：建的时候想着「以后可能要用」，用了两年没人用。
  **这种索引只贡献成本、不贡献收益。**
  **三是区分度太低的**：
  **一列只有两三个不同值（性别、是否删除、状态位），建了索引也筛不掉多少行**——
  **优化器可能干脆不用它，白占空间。** **例外是「数据倾斜」的情况**：99% 是「未删除」、
  1% 是「已删除」时，**查那 1% 用索引是有效的**，这是部分索引的用武之地。
  **删之前先备份建索引语句**：**从 `sqlite_master` 的 `sql` 列取出来存一份**，
  **万一删错了，一句 `create index` 就能补回来。**
  **这一步和「删表前先改名」是同一个思路：留一条可逆的路。**

第一段删前一行、删后 0 行，idx_big_name 没了。 第二段 rows_left 还是 50000，数据一行没动。 第三段第一次报 no such index: idx_big_name，带 if exists 就不报。 第四段自动索引报 index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped。 第五段取回 CREATE INDEX idx_big_city ON big (city)，就是那条「退路」。

30 秒实验

删掉一个高频查询正用着的索引，接口耗时大概会变成几倍？拿第 12.1 篇的数字估一下。
为什么自动索引不能单独删？它和约束是什么关系？
drop index if exists 会吞掉哪一类错误？这有什么风险？

常见误区

在生产库上「先删了看看」。 高频查询会立刻退化成全表扫，要删先在测试环境观察。
删索引时不记建索引的语句。 删错了没法快速补回来，重建大表索引要几十分钟。
以为 drop index 要带表名。 索引名全库唯一，只给名字就行；但也正因如此，名字里不带表名就容易删错。

自测三问（点开对答案）

1. `drop index` 为什么不带表名？ —— 索引名在整个库里唯一，不像表名只在库内唯一，所以引擎不需要表名就能定位。 2. 删索引会丢数据吗？ —— 不会。索引只是附加的目录，数据还在表里。但依赖它的查询会变回全表扫，性能可能大幅下降。 3. 为什么 `UNIQUE` 自动建的索引删不掉？ —— 那份索引就是约束的执行机制，靠它判断重复。删了约束就没法保证唯一性，所以只能删约束本身。

第 12 章完了： 索引的原理、建法、查法、删法都过了。 下一篇进第 13 章——存储程序，把一段 SQL 存进数据库里。

☐ （本篇完，下一篇 13.1 存储程序简介）