数据库索引优化实战

引言

索引是数据库性能优化的第一道防线。一个恰当的索引可以让查询从全表扫描（O(n)）降到对数级查找（O(log n)），性能提升数千倍不是夸张。但错误的索引设计同样能让数据库性能雪崩。本文从 B-Tree 的内部结构讲起，带你理解索引优化的底层逻辑。

一、B-Tree 索引结构

大多数关系型数据库（MySQL InnoDB、PostgreSQL）默认使用 B+Tree 作为索引结构。B+Tree 是一种平衡多路搜索树，它的关键特性：

所有数据存储在叶子节点：非叶子节点只存储键值和指针，不存储实际数据
叶子节点形成有序链表：便于范围查询（BETWEEN、>、<）
树的高度很低：通常 3-4 层就能索引数百万行数据
磁盘 I/O 友好：每个节点通常对应一个磁盘页（16KB），一次 I/O 读取一个节点

聚簇索引 vs 非聚簇索引

-- MySQL InnoDB：主键就是聚簇索引，叶子节点存储完整行数据
CREATE TABLE users (
  id INT PRIMARY KEY,        -- 聚簇索引：数据按 id 物理排序
  email VARCHAR(255),
  name VARCHAR(100),
  INDEX idx_email (email)    -- 二级索引：叶子节点存储主键值
);

-- 通过 email 查询的流程：
-- 1. 在 idx_email 中查找 email，得到主键 id
-- 2. 回表：用主键 id 在聚簇索引中查找完整行（这就是"回表"）

二、EXPLAIN：读懂执行计划

优化索引的第一步是学会看执行计划。

-- PostgreSQL
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 123
  AND status = 'paid'
  AND created_at > '2026-01-01';

-- 关注的关键指标：
-- Seq Scan vs Index Scan：全表扫描 vs 索引扫描
-- Index Only Scan：覆盖索引扫描（最优，不需要回表）
-- Bitmap Index Scan：位图索引扫描（多个索引组合）
-- rows vs actual rows：估算行数 vs 实际行数（差距大说明统计信息过时）
-- cost：第一个值是启动成本，第二个值是总成本

MySQL EXPLAIN 关键字段

EXPLAIN SELECT * FROM orders WHERE user_id = 123;

-- type（访问类型）：从好到差
-- const/system > eq_ref > ref > range > index > ALL
-- 目标至少是 range，最好是 ref 或 const

-- key：实际使用的索引
-- rows：扫描行数估算
-- Extra：Using index（覆盖索引）> Using where > Using filesort > Using temporary

三、复合索引的列顺序

复合索引（多列索引）的列顺序是优化中最容易被忽视的细节。

-- 假设最常见的查询是：
-- WHERE user_id = ? AND status = ? ORDER BY created_at DESC

-- 正确的索引顺序：等值条件在前，范围条件在后，排序字段在最后
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);

-- 错误的索引顺序：
CREATE INDEX idx_status_user_created ON orders(status, user_id, created_at);
-- 如果查询只带 user_id 不带 status，这个索引无法被使用

最左前缀原则

-- 索引：idx(a, b, c)
-- 可以使用索引的查询：
WHERE a = 1                    -- 使用 a
WHERE a = 1 AND b = 2          -- 使用 a, b
WHERE a = 1 AND b = 2 AND c = 3 -- 使用 a, b, c
WHERE a = 1 AND c = 3          -- 只使用 a（跳过 b 后无法继续）

-- 无法使用索引的查询：
WHERE b = 2                    -- 不满足最左前缀
WHERE b = 2 AND c = 3          -- 不满足最左前缀

四、覆盖索引

覆盖索引（Covering Index）是指查询所需的所有列都在索引中，不需要回表查询。

-- 需要回表的查询
SELECT * FROM orders WHERE user_id = 123;

-- 覆盖索引：查询的列都在索引中
CREATE INDEX idx_user_status_amount ON orders(user_id, status, amount);

SELECT user_id, status, amount
FROM orders
WHERE user_id = 123;  -- Extra: Using index（覆盖索引扫描）

五、索引何时适得其反

写入性能下降：每个索引都会在 INSERT/UPDATE/DELETE 时被维护，索引越多写入越慢
小表不需要索引：几百行的表，全表扫描可能比索引查找更快
低选择性的列：性别（男/女）、状态（0/1）等只有几个值的列，索引效果很差
频繁更新的列：索引列的每次更新都需要维护索引结构
NULL 值处理：MySQL 中 NULL 值也会存储在索引中，大量 NULL 会浪费空间

六、部分索引

-- PostgreSQL 部分索引：只索引符合条件的行
CREATE INDEX idx_active_orders ON orders(created_at)
WHERE status = 'active';

-- 这个索引只包含活跃订单，查询活跃订单时更快，且索引更小
SELECT * FROM orders WHERE status = 'active' AND created_at > '2026-01-01';

七、PostgreSQL vs MySQL 索引差异

PostgreSQL 支持更多索引类型：B-Tree、Hash、GiST、GIN、BRIN、SP-GiST
MySQL InnoDB 必须有主键，没有指定主键时会自动创建隐藏的 row_id
PostgreSQL 的 EXPLAIN ANALYZE 输出比 MySQL 更详细，包含实际执行时间
MySQL 的 EXPLAIN FORMAT=JSON 提供更丰富的执行计划信息
PostgreSQL 支持表达式索引，MySQL 8.0+ 支持函数索引（Functional Index）

总结

索引优化的核心原则很简单：让数据库尽可能少地扫描数据。具体来说就是：理解 B-Tree 结构知道为什么索引快，用 EXPLAIN 验证索引是否被使用，设计复合索引时遵循最左前缀原则，尽量使用覆盖索引避免回表，在写入和查询之间找到平衡点。记住，没有银弹索引——每个索引都是根据你的查询模式量身定制的。

返回文章列表

标签：数据库索引优化SQL