4. 数据库索引优化：索引类型详解（B+树、哈希索引），如何为查询创建高效索引

索引这东西，说白了就是数据库的「目录」。没有索引，查询就是全表扫描——数据量小还好，一旦上了百万级，响应时间直接崩。我在项目中见过太多因为索引没建好导致的慢查询，有的甚至把数据库CPU打满。

这一节，我们重点聊两种最常见的索引类型：B+树索引和哈希索引。搞懂它们，你就能为查询选对索引。

4.1 B+树索引：最通用的索引结构

B+树是MySQL InnoDB的默认索引结构。它长什么样？你可以想象成一个倒过来的树，叶子节点存数据，非叶子节点只存「路标」。

为什么B+树这么流行？因为它支持范围查询。比如 WHERE age > 20 AND age < 30，B+树可以沿着叶子节点的链表顺序扫描，效率极高。

我个人习惯，只要查询涉及范围、排序、模糊匹配（前缀），优先选B+树索引。它几乎能覆盖80%的业务场景。

4.2 哈希索引：精确匹配的利器

哈希索引的原理很简单：对索引列计算哈希值，然后直接定位到数据行。你想想看，这就像查字典时直接翻到拼音索引，而不是一页页翻。

但哈希索引有个致命短板——不支持范围查询。为什么？因为哈希值是无序的，age > 20这种条件没法用哈希索引加速。

对比维度	B+树索引	哈希索引
查询类型	等值、范围、排序、模糊匹配	仅等值匹配
查询速度	稳定（O(log n)）	极快（O(1)，无冲突时）
存储引擎	InnoDB、MyISAM等	Memory、NDB（InnoDB自适应哈希）
排序支持	支持	不支持
冲突处理	无冲突	链地址法，冲突多时退化为链表

嗯，这里要注意：InnoDB内部有一个自适应哈希索引，它会自动为频繁访问的热点数据建立哈希索引。但这是引擎自己决定的，我们无法手动创建。所以日常开发中，我们主要打交道的还是B+树。

4.3 如何为查询创建高效索引

光知道类型还不够，关键是怎么用。我总结了几个实战原则，都是踩过坑之后才明白的。

4.3.1 最左前缀原则

联合索引（多列索引）遵循最左前缀原则。比如你建了一个索引 (a, b, c)，那么查询条件必须从a开始才能用到索引。

-- 能用到索引
SELECT * FROM t WHERE a = 1;
SELECT * FROM t WHERE a = 1 AND b = 2;
SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3;

-- 用不到索引（跳过了a）
SELECT * FROM t WHERE b = 2;
SELECT * FROM t WHERE c = 3;

我曾经接手过一个慢查询，开发建了 (status, created_at) 索引，但查询条件是 WHERE created_at > '2024-01-01'——跳过了status，索引完全没用上。全表扫描跑了3秒多。

4.3.2 区分度高的列放前面

联合索引中，把区分度（cardinality）高的列放在前面。区分度越高，索引过滤掉的数据越多。

举个例子：(gender, user_id) 和 (user_id, gender) 哪个好？gender只有男女两种值，区分度极低；user_id几乎唯一。所以应该把user_id放前面。

4.3.3 覆盖索引：避免回表

如果索引中已经包含了查询需要的所有列，MySQL就不需要回表查数据行。这叫覆盖索引，性能提升非常明显。

-- 假设有索引 (user_id, name)
-- 这个查询只需要 user_id 和 name，索引里都有，直接返回
SELECT user_id, name FROM t WHERE user_id = 100;

我在优化一个报表查询时，把 SELECT * 改成了只查索引包含的列，查询时间从800ms降到了50ms。你想想看，少了多少次磁盘IO？

4.3.4 避免在索引列上做函数操作

这个坑我踩过好几次。在索引列上使用函数，会让索引失效。

-- 索引失效
SELECT * FROM t WHERE DATE(created_at) = '2024-01-01';

-- 正确写法（范围查询）
SELECT * FROM t WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02';

4.4 索引选择的核心逻辑

下面这张图，是我做索引设计时脑子里过的流程。你可以参考一下。

4.5 实战：一个慢查询的索引优化案例

最后分享一个真实案例。某订单查询接口，响应时间一直在1.5秒以上。

-- 原始查询
SELECT * FROM orders
WHERE status = 'PAID'
  AND created_at > '2024-06-01'
  AND created_at < '2024-07-01'
ORDER BY created_at DESC
LIMIT 20;

原索引是 (created_at)。问题在哪？status没有走索引，先按时间范围扫出大量数据，再过滤status。

优化方案：

1. 新建联合索引 (status, created_at)
2. status区分度不高，但作为等值条件放前面，能快速定位到目标数据
3. created_at放后面，支持范围查询和排序

优化后，查询时间降到了30毫秒。效果立竿见影。