这是一个非常经典并且常常被误解的数据库优化问题。
简短的回答是:这个说法不完全正确。 更准确地说,它是一个需要根据具体情况分析的优化建议,而不是一个绝对的规则。
下面我将详细解释“为什么会有这个说法”以及“什么时候应该或不应该加索引”。
为什么会有“不要给ENUM/低基数字段加索引”的说法?
这个建议的核心在于一个概念:索引的基数(Cardinality)。
- 基数:指一个列中不重复值的数量。
- 高基数列:列中的值几乎都是唯一的,比如
user_id
,email
。 - 低基数列:列中只有很少几个固定的、重复值,比如
status
(‘pending’, ‘approved’, ‘rejected’),gender
(‘male’, ‘female’, ‘other’),is_active
(true, false)。ENUM 和 BOOLEAN 类型天生就是低基数的。
低基数列作为索引的主要问题是“筛选效率低下”:
索引选择性差 (Poor Selectivity)
数据库优化器在决定是否使用索引时,会评估其“选择性”。一个好的索引应该能帮你快速排除掉大部分不符合条件的数据。举个例子:
假设一个orders
表有1000万条记录,其中status
列有3个值:’processing’, ‘shipped’, ‘completed’。- 如果你执行
SELECT * FROM orders WHERE status = 'shipped';
- 数据库会估算,
status = 'shipped'
的记录可能占了总数的三分之一,也就是大约333万条。 - 在这种情况下,优化器很可能会认为:通过索引找到这333万条记录的地址,然后再逐一回表(回到主表去读取完整的行数据),这个过程的随机I/O成本可能比直接全表扫描(从头到尾读一遍表)还要高。
- 因此,即使你建了索引,数据库也可能放弃使用它,导致索引白建了。
- 如果你执行
索引维护成本 (Maintenance Overhead)
索引不是免费的。每次对表进行INSERT
,UPDATE
,DELETE
操作时,如果动到了索引列,数据库也需要同步更新索引本身。对于一个写操作频繁的表,一个几乎用不上的低效索引反而会拖慢整体的写入性能。
结论: 当一个索引不能有效地区分数据时,它的存在就弊大于利。这就是“不要给低基数列加索引”这个建议的根本原因。
那么,什么时候应该给ENUM/低基数列加索引?
虽然低基数索引通常是低效的,但在以下几种情况下,给它们建立索引却是非常明智和高效的:
数据分布极不均衡 (Skewed Data Distribution)
这是最常见也最重要的例外情况。虽然列的基数很低,但如果某个值的出现频率极低,为它建索引就非常有用。举例:
一个users
表有1亿用户,有一个is_banned
(true/false) 字段。- 99.99% 的用户
is_banned
是false
。 - 只有 0.01% 的用户
is_banned
是true
(被封禁的用户)。 - 此时,
SELECT * FROM users WHERE is_banned = true;
这个查询的选择性就极高。索引可以快速定位到那一小部分被封禁的用户,性能会远超全表扫描。
- 99.99% 的用户
作为覆盖索引 (Covering Index) 的一部分
当一个查询所需的所有列都包含在索引中时,数据库引擎就无需回表,可以直接从索引中获取所有数据返回。这被称为“覆盖索引”。举例:
你想统计每种订单状态的数量:SELECT status, COUNT(*) FROM orders GROUP BY status;
- 如果你只在
status
列上建立一个索引INDEX(status)
。 - 数据库可以只扫描这个小得多的
status
索引,而完全不需要触碰庞大的主表数据,就能完成统计。这会带来巨大的性能提升。
- 如果你只在
作为复合索引 (Composite Index) 的一部分
当低基数列是复合索引的非前导列时,它通常是非常有用的。举例:
你的查询条件通常是WHERE corp_id = ? AND status = ?
。corp_id
是一个高基数列(有很多公司)。status
是一个低基数列。- 建立一个复合索引
INDEX(corp_id, status)
是非常高效的。 - 查询时,索引会首先通过
corp_id
快速筛选出很少一部分数据,然后再在这个小范围内根据status
进行查找。此时status
的低基数问题就不再是瓶颈。
总结与建议
场景 | 是否建议加索引 | 原因 |
---|---|---|
单独查询,且数据均匀分布 | 不建议 | 索引选择性差,优化器可能放弃使用,且有维护成本。 |
单独查询,但数据分布极不均衡 | 强烈建议 | 对稀有值的查询效率极高。 |
查询只涉及该列和少数几列 | 建议 | 可以创建覆盖索引,避免回表,极大提升性能。 |
作为复合索引的一部分,与其他高选择性列组合 | 强烈建议 | 复合索引可以大大提高多条件查询的效率。 |
实践法则:
- 不要盲从规则:先分析你的业务场景和查询模式。
- 检查数据分布:使用
SELECT status, COUNT(*) FROM your_table GROUP BY status;
来了解值的分布情况。 - 使用
EXPLAIN
:在你认为需要加索引的查询前加上EXPLAIN
,看看数据库的执行计划。确认它是否会使用你创建的索引(key
列),以及扫描的行数(rows
列)是否显著减少。 - 优先考虑复合索引和覆盖索引:在设计索引时,优先考虑如何让索引服务于一类查询,而不仅仅是单个列。
总而言之,“不要给ENUM/低基数列加索引”是一个有用的“经验法则”,它提醒我们警惕低基数列的索引陷阱。但它绝不是一条必须遵守的铁律。 真正的数据库优化需要结合具体业务场景、查询模式和数据分布来进行综合判断。