了解数据库索引：它们的工作原理以及何时会影响性能
Source: Dev.to
噩梦：全表扫描
想象你走进一个有 10 万本书的图书馆，想找一本特定的书。如果这些书随意堆在房间中间，没有任何顺序，你就得把每一本书都捡起来，直到找到正确的那本。
在数据库中，这叫做 全表扫描。
如果你的表有 100 万行且在你搜索的列上没有索引，数据库引擎必须从磁盘读取每一行。这是 O(N) 复杂度——慢、耗费 I/O，且难以扩展。
“字典”类比
解释索引最常用的方式是把它比作书后面的 目录，但 字典 更贴切。
在字典里，单词按字母顺序排列。因为是排序的，你不会从第一页开始找单词 “Node”。你会跳到中间，看到 “N” 在当前页之后，然后逐步缩小搜索范围。
索引的作用： 创建一个单独的、已排序的数据结构，指向实际数据所在的位置。
内部结构：B‑树
如果你想从初级晋升为高级，必须了解 B‑Tree（平衡树）这个名字。大多数现代数据库（PostgreSQL、MySQL、SQL Server）默认使用 B‑Tree 作为索引结构。
B‑Tree 通过将数据组织成 节点 的层次结构，解决了简单排序列表的写入速度问题。
- 根节点（Root）： 入口点。
- 内部节点（Internal Nodes）： 像指示牌一样（“左边是 A‑M，右边是 N‑Z”）。
- 叶子节点（Leaf Nodes）： 包含指向磁盘上行的实际指针。
因为树是 平衡的，从顶部到任意数据的距离大致相同。搜索 B‑Tree 的复杂度是 O(log N)。对于 100 万行数据，只需要大约 20 次“跳转”。
概念搜索示例
假设我们在一个大型 users 表中搜索 email = 'chris@example.com'，并且在 email 列上已有索引。
数据库不会读取一百万行，而是概念上执行以下几个逻辑步骤：
-
从根节点开始（跳转 1）： 根节点保存边界（例如每个子分支的最小值和最大值）。它判断
chris@example.com落在中间内部节点的范围之间。 -
移动到内部节点（跳转 2）： 数据库加载中间节点，可能显示：
* Emails O: Go Right因为
c在F之前，数据库选择 左侧叶子节点 的指针。 -
移动到叶子节点（跳转 3）： 叶子节点是一个密集、已排序的列表，包含电子邮件和主键 ID（指针）。数据库在这个小列表中执行二分搜索：
[ { "email": "adam@example.com", "userId": 101 }, { "email": "ben@example.com", "userId": 450 }, { "email": "chris@example.com", "userId": 221 }, // 找到了！ { "email": "diana@example.com", "userId": 890 } ] -
获取数据： 使用
userId: 221，数据库从主表中检索完整的用户记录。
搜索只用了 四次磁盘操作（log N），而不是一百万次。这就是 B‑Tree 索引的威力。
聚簇索引 vs. 非聚簇索引
聚簇索引（物理顺序）
把它想象成字典本身。数据在磁盘上按照索引的顺序物理存储——几乎总是你的 主键。
规则： 每个表只能有 一个 聚簇索引（因为同一批数据不能以两种不同方式物理排序）。
非聚簇索引（“电话簿”）
这是一种独立于实际表的结构。它保存索引列的副本以及指向行所在位置的“指针”（类似 GPS 坐标）。
规则： 可以拥有多个非聚簇索引，但每增加一个都会给数据库增加“重量”。
成本：索引何时会拖慢性能
如果索引是魔法，为什么不对每一列都建索引？因为每个索引都是 权衡：读取更快，但写入更慢，还会占用存储空间。
写入惩罚
每当修改被索引的列时，数据库必须更新 B‑Tree：
INSERT：添加新值并可能重新平衡树。UPDATE：删除旧条目并插入新条目。DELETE：定位并移除条目。
如果一个表有五个索引，单次 INSERT 会触发五次索引写入操作，外加主记录的写入。在高写入表（例如日志或遥测）上，过多索引会严重降低性能。
存储和内存开销
索引是独立的数据结构，既占用磁盘空间 又 占用内存。数据库会尝试把经常访问的索引节点保存在 RAM 中。如果索引大于可用内存，系统必须从磁盘读取索引节点，抵消速度优势并导致 I/O 瓶颈。更大的索引也会减少用于缓存实际数据的内存。
高级概念：精确索引
复合索引（顺序重要）
复合索引在特定顺序下使用 多个列。它们对支持复杂的 WHERE 和 ORDER BY 子句至关重要。
示例查询
SELECT * FROM orders
WHERE customer_id = 123 AND order_date > '2023-01-01'
ORDER BY order_date DESC;创建一个匹配模式的复合索引：(customer_id, order_date)。
为什么顺序重要
- 第一个列 (
customer_id) 缩小数据切片。 - 第二个列 (
order_date) 满足额外过滤条件并且满足ORDER BY，无需额外排序。
如果把顺序改为 (order_date, customer_id)，索引对仅按 customer_id 过滤的查询就失效了。规则： 把选择性最高的列放在最前。
基数陷阱
基数 指的是某列的唯一值数量相对于总行数的比例。
- 高基数： 如
email或SSN（每个值都是唯一的）。索引极其有效。 - 低基数： 如
(内容已截断)