编程笔记

lifelong learning & practice makes perfect

mysql| enum 字段不要加索引( status 、type...)是正确的吗,为什么

这是一个非常经典并且常常被误解的数据库优化问题。

简短的回答是:这个说法不完全正确。 更准确地说,它是一个需要根据具体情况分析的优化建议,而不是一个绝对的规则。

下面我将详细解释“为什么会有这个说法”以及“什么时候应该或不应该加索引”。


为什么会有“不要给ENUM/低基数字段加索引”的说法?

这个建议的核心在于一个概念:索引的基数(Cardinality)

  • 基数:指一个列中不重复值的数量。
  • 高基数列:列中的值几乎都是唯一的,比如 user_id, email
  • 低基数列:列中只有很少几个固定的、重复值,比如 status (‘pending’, ‘approved’, ‘rejected’),gender (‘male’, ‘female’, ‘other’),is_active (true, false)。ENUM 和 BOOLEAN 类型天生就是低基数的。

低基数列作为索引的主要问题是“筛选效率低下”:

  1. 索引选择性差 (Poor Selectivity)
    数据库优化器在决定是否使用索引时,会评估其“选择性”。一个好的索引应该能帮你快速排除掉大部分不符合条件的数据。

    举个例子:
    假设一个 orders 表有1000万条记录,其中 status 列有3个值:’processing’, ‘shipped’, ‘completed’。

    • 如果你执行 SELECT * FROM orders WHERE status = 'shipped';
    • 数据库会估算,status = 'shipped' 的记录可能占了总数的三分之一,也就是大约333万条。
    • 在这种情况下,优化器很可能会认为:通过索引找到这333万条记录的地址,然后再逐一回表(回到主表去读取完整的行数据),这个过程的随机I/O成本可能比直接全表扫描(从头到尾读一遍表)还要高
    • 因此,即使你建了索引,数据库也可能放弃使用它,导致索引白建了。
  2. 索引维护成本 (Maintenance Overhead)
    索引不是免费的。每次对表进行 INSERT, UPDATE, DELETE 操作时,如果动到了索引列,数据库也需要同步更新索引本身。对于一个写操作频繁的表,一个几乎用不上的低效索引反而会拖慢整体的写入性能。

结论: 当一个索引不能有效地区分数据时,它的存在就弊大于利。这就是“不要给低基数列加索引”这个建议的根本原因。


那么,什么时候应该给ENUM/低基数列加索引?

虽然低基数索引通常是低效的,但在以下几种情况下,给它们建立索引却是非常明智和高效的:

  1. 数据分布极不均衡 (Skewed Data Distribution)
    这是最常见也最重要的例外情况。虽然列的基数很低,但如果某个值的出现频率极低,为它建索引就非常有用。

    举例:
    一个 users 表有1亿用户,有一个 is_banned (true/false) 字段。

    • 99.99% 的用户 is_bannedfalse
    • 只有 0.01% 的用户 is_bannedtrue(被封禁的用户)。
    • 此时,SELECT * FROM users WHERE is_banned = true; 这个查询的选择性就极高。索引可以快速定位到那一小部分被封禁的用户,性能会远超全表扫描。
  2. 作为覆盖索引 (Covering Index) 的一部分
    当一个查询所需的所有列都包含在索引中时,数据库引擎就无需回表,可以直接从索引中获取所有数据返回。这被称为“覆盖索引”。

    举例:
    你想统计每种订单状态的数量:SELECT status, COUNT(*) FROM orders GROUP BY status;

    • 如果你只在 status 列上建立一个索引 INDEX(status)
    • 数据库可以只扫描这个小得多的 status 索引,而完全不需要触碰庞大的主表数据,就能完成统计。这会带来巨大的性能提升。
  3. 作为复合索引 (Composite Index) 的一部分
    当低基数列是复合索引的非前导列时,它通常是非常有用的。

    举例:
    你的查询条件通常是 WHERE corp_id = ? AND status = ?

    • corp_id 是一个高基数列(有很多公司)。
    • status 是一个低基数列。
    • 建立一个复合索引 INDEX(corp_id, status) 是非常高效的。
    • 查询时,索引会首先通过 corp_id 快速筛选出很少一部分数据,然后再在这个小范围内根据 status 进行查找。此时 status 的低基数问题就不再是瓶颈。

总结与建议

场景 是否建议加索引 原因
单独查询,且数据均匀分布 不建议 索引选择性差,优化器可能放弃使用,且有维护成本。
单独查询,但数据分布极不均衡 强烈建议 对稀有值的查询效率极高。
查询只涉及该列和少数几列 建议 可以创建覆盖索引,避免回表,极大提升性能。
作为复合索引的一部分,与其他高选择性列组合 强烈建议 复合索引可以大大提高多条件查询的效率。

实践法则:

  1. 不要盲从规则:先分析你的业务场景和查询模式。
  2. 检查数据分布:使用 SELECT status, COUNT(*) FROM your_table GROUP BY status; 来了解值的分布情况。
  3. 使用 EXPLAIN:在你认为需要加索引的查询前加上 EXPLAIN,看看数据库的执行计划。确认它是否会使用你创建的索引(key 列),以及扫描的行数(rows 列)是否显著减少。
  4. 优先考虑复合索引和覆盖索引:在设计索引时,优先考虑如何让索引服务于一类查询,而不仅仅是单个列。

总而言之,“不要给ENUM/低基数列加索引”是一个有用的“经验法则”,它提醒我们警惕低基数列的索引陷阱。但它绝不是一条必须遵守的铁律。 真正的数据库优化需要结合具体业务场景、查询模式和数据分布来进行综合判断。

欢迎关注我的其它发布渠道