在随机字段上随意加二级索引正在悄悄地杀死你的数据库

Source: Dev.to

概述

我们为什么要给 SQL 字段添加索引？是为了让搜索更快，对吧？

但你知道它有一个巨大的缺点吗？写入会变慢，迫使开发者在决定哪些字段应该标记为二级索引时必须更加策略性。

示例查询

SELECT * FROM book WHERE author = 'C.S. Lewis';

假设有一个 book 表，字段为 (id, title, author, pub_date, isbn)，且包含 100 万行记录，其中只有 20 行的作者是 C.S. Lewis。没有索引时，数据库会扫描整个堆（行的物理存储），导致 全表扫描，复杂度为 O(n)——非常慢。

解决方案

当你在 author 列上添加索引时：

CREATE INDEX idx_author ON book(author);

数据库会构建一个单独的 B‑Tree 结构，按字母顺序存储作者姓名，并附带指向堆中相应行的指针。查询时会在 O(log n) 时间内遍历 B‑Tree，检索到 20 条匹配指针，然后直接获取这些行。这就是二级索引让读取更快的原因。

为什么写入会变慢

插入或更新一行现在涉及两个步骤：

1. 将行数据写入堆。
2. 更新所有包含受影响列的二级 B‑Tree。

如果一个表有五个二级索引，每次 INSERT 或 UPDATE 必须在磁盘上修改堆 以及 五个 B‑Tree。这个隐藏的成本会显著降低写入性能。

二级索引的建议

• 避免对经常更新的列建立索引（例如页面访问次数、浏览量），以减少写入开销。
• 仅对在 WHERE、JOIN 或 ORDER BY 子句中使用的列建立索引，当性能收益大于写入成本时才这样做。