编程笔记

lifelong learning & practice makes perfect

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

发表于 更新于 分类于 ，
本文字数： 2.3k 阅读时长 ≈ 2 分钟

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

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

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

为什么会有“不要给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_banned 是 false。
    • 只有 0.01% 的用户 is_banned 是 true（被封禁的用户）。
    • 此时，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/低基数列加索引”是一个有用的“经验法则”，它提醒我们警惕低基数列的索引陷阱。但它绝不是一条必须遵守的铁律。 真正的数据库优化需要结合具体业务场景、查询模式和数据分布来进行综合判断。

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