MySQL状态字段位图索引替代方案设计

MySQL 和 Oracle 不同，它并不原生支持位图索引。因此像 status 这类低基数字段（通常只有几个固定取值），如果直接创建单列索引，往往收效甚微——查询优化器大概率会跳过该索引，转而选择全表扫描。这并非索引本身有问题，而是 B+ 树索引在这种场景下，单打独斗确实难以发挥优势。

为什么 status = 'active' 用不上索引？

优化器经过成本估算会发现，status 字段的选择率偏高——例如数据库中 30% 的行都符合条件。此时走索引再回表，反而比直接全表扫描更慢。即便你强行加上单列索引，EXPLAIN 输出的结果中大概率仍是 type: ALL。

• 如何验证？执行 SELECT COUNT(DISTINCT status)/COUNT(*) FROM orders;，如果结果大于 0.1，基本就会被优化器“放弃”。
• 不是索引无效，而是用了反而更慢——B+ 树索引需要按主键回表，大量的随机 I/O 会拖垮整体性能。
• 一个小技巧：WHERE status IN ('active') 有时比 = 更容易触发索引，因为优化器对 IN 的选择率估算更为保守。

用复合索引让低基数字段“重获新生”

单列索引虽然失效，但如果将它放在复合索引的最左列，后面再跟上高基数列，索引就能真正派上用场。

• 推荐写法：ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);
• 适用场景：WHERE status = 'shipped' AND created_at > '2026-01-01' —— 索引的两列都能被充分利用。
• 尽量避免 SELECT *，只查询 id、user_id 等必要字段，减少不必要的回表开销。
• 如果经常查询 status + user_id，而且 user_id 基数较高，建议将索引调整为 (status, user_id)。

用生成列 + 函数索引实现“软位图”

MySQL 8.0 及以上版本支持生成列，并且可以在其上创建函数索引，相当于把多个查询条件压缩成一个可索引的表达式。

• 示例：高频查询 WHERE status = 'completed' AND amount >= 100。
• 添加生成列：ALTER TABLE orders ADD COLUMN is_high_value_completed TINYINT GENERATED ALWAYS AS ((status = 'completed') AND (amount >= 100)) STORED;
• 再建索引：CREATE INDEX idx_hvc ON orders (is_high_value_completed);
• 查询时直接使用 WHERE is_high_value_completed = 1，索引可以百分百命中。

分区表实现物理隔离，减少重复值扫描

对于极低基数的字段（比如 is_deleted TINYINT 只有 0 和 1），使用 LIST 分区可以直接将数据扫描量减半。

• 建表时分区：CREATE TABLE orders (...) PARTITION BY LIST (is_deleted) (PARTITION p0 VALUES IN (0), PARTITION p1 VALUES IN (1));
• 查询 WHERE is_deleted = 0 时，优化器只会读取 p0 分区，I/O 开销直接降低一半。
• 需要注意：分区字段必须是整型或枚举类型，不能是 VARCHAR；同时查询条件要能精确匹配分区值。

真正有挑战的不是“如何加索引”，而是判断哪些查询路径值得投入精力去优化。比如统计类查询（COUNT(*) WHERE status = ...）更适合使用物化视图或定时汇总表，而不是硬扛索引。位图索引的本质是空间换时间，而 MySQL 的替代方案，核心在于通过结构设计提前将“空间”收拢进来，把“时间”留给查询那一刻。