MySQL 实战：动态多 “是 / 否” 字段巧妙存储！ 位图压缩 vs 倒排索引

MySQL 实战：动态多 “是 / 否” 字段巧妙存储！ 位图压缩 vs 倒排索引

场景

朋友们，是否遇到一个表有多个‘是/否’字段，并且都需要进行限制查询？为了提升查询效率只能分别建索引，但是这种‘是/否’字段是动态的后面会随时添加MySQL 实战：动态多 “是 / 否” 字段巧妙存储！ 位图压缩 vs 倒排索引，而且索引太多了还会影响维护性能…

下面以一个实际的场景进行举例说明。

某医院的 患者管理系统，MySQL数据库，指标表 如下，要查询各种异常情况的患者，如：

-- 指标表
CREATE TABLE `t_indicator` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `dossier_id` bigint NOT NULL COMMENT '患者主键',
  `name` varchar(255) NOT NULL COMMENT '指标名',
  `result` int NOT NULL COMMENT '指标值',
  `a_exception` int NOT NULL DEFAULT 0 COMMENT '触发A异常',
  `b_exception` int NOT NULL DEFAULT 0 COMMENT '触发B异常',
  `c_exception` int NOT NULL DEFAULT 0 COMMENT '触发C异常',
  PRIMARY KEY (`id`),
  KEY `idx_aexception` (`a_exception`),
  KEY `idx_bexception` (`b_exception`),
  KEY `id_cexception` (`c_exception`)
) ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='检查指标表'

分析

上面的场景中，A异常、B异常、C异常… 这种异常是动态的，后面可能会有 D异常、F异常…mysql fetch into，查询条件就会相应增多，这种需求就比较复杂了….

方案一（多字段多索引）

就是文中采用的方案：每个异常一个字段，为每个异常字段分别建立索引。如果异常字段很少 且 不怎么变化，这是最优的一种方式（逻辑清晰、查询简单、效能高）。但目前要求：异常字段动态添加mysql fetch into，这种方案就不大合适了。

方案二（位图压缩）

-- 指标表
CREATE TABLE `t_indicator` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `dossier_id` bigint NOT NULL COMMENT '患者主键',
  `name` varchar(255) NOT NULL COMMENT '指标名',
  `result` int NOT NULL COMMENT '指标值',
  `exception_flags` int NOT NULL DEFAULT 0 COMMENT '异常位图',
  PRIMARY KEY (`id`),
  KEY `idx_exception` (`exception_flags`)  -- 只需1个索引
) ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='检查指标表'

说明：

A、：异常位图MySQL 实战：动态多 “是 / 否” 字段巧妙存储！ 位图压缩 vs 倒排索引，其实就是用不同的二进制位表示不同的异常，对于每一位：1代表异常，0代表不异常。如：第1位代表A是否异常，第2位代表B是否异常，第3位代表C是否异常… ，这样不同的异常组合都有一个唯一的数来定义，如下面的 A异常且B异常：就是 第1位 和 第2位 都为1，也就是 011，对应的十进制数就是3。

B、用一个字段就能表达出所有异常的组合了，只需要对这一个字段建索引，查询时只需要限制这一个字段即可。

C、这种方案比较灵活，后面再出现新异常只要用不同的 2的幂 表示即可，数据结构不用动。虽然查询每种异常时 IN 的值会变多，但还是1个索引，1次索引范围扫描。

D、这个方案唯一不好的的地方是如果 IN 的值很多了查询效能会有降低，但类似的情况 99.9% 的场景里不会太多的 IN 值。

E、数据操作实例

插入数据：

-- 插入：A异常
INSERT INTO t_indicator (dossier_id, name, result, exception_flags) 
VALUES (201, '指标名', 15, 1);  -- 二进制 001
-- 插入：B异常
INSERT INTO t_indicator (dossier_id, name, result, exception_flags) 
VALUES (201, '指标名', 8, 2);   -- 二进制 010
-- 插入：A+B异常（1+2=3）
INSERT INTO t_indicator (dossier_id, name, result, exception_flags) 
VALUES (201, '指标名', 120, 3);  -- 二进制 011
-- 插入：A+B+C异常（1+2+4=7）
INSERT INTO t_indicator (dossier_id, name, result, exception_flags) 
VALUES (201, '指标名', 50, 7);  -- 二进制 111
-- 插入：无异常
INSERT INTO t_indicator (dossier_id, name, result, exception_flags) 
VALUES (201, '指标名', 10, 0);  -- 二进制 000

查询数据：

-- 查A异常（4种可能）
SELECT * FROM t_indicator WHERE exception_flags IN (1, 3, 5, 7);
-- 查B异常（4种可能）
SELECT * FROM t_indicator WHERE exception_flags IN (2, 3, 6, 7);
-- 查C异常（4种可能）
SELECT * FROM t_indicator WHERE exception_flags IN (4, 5, 6, 7);

方案三（倒排索引）

-- 主表：去掉异常字段，保持精简
CREATE TABLE `t_indicator` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `dossier_id` bigint NOT NULL COMMENT '患者主键',
  `name` varchar(255) NOT NULL,
  `result` int NOT NULL,
  PRIMARY KEY (`id`)
);
-- 异常关系表：哪条指标、哪种异常
CREATE TABLE `t_indicator_exception` (
  `indicator_id` bigint NOT NULL,
  `exception_type` tinyint NOT NULL COMMENT '1=A异常,2=B异常,3=C异常...',
  PRIMARY KEY (`indicator_id`, `exception_type`),
  KEY `idx_type_id` (`exception_type`, `indicator_id`)  -- 覆盖查询
) ENGINE=InnoDB;

说明:

A、剥离出 指标异常表（n），每条数据代表某个指标的一个异常。

B、这种方案也非常灵活，后面再出现新异常只要往 指标异常表 添加数据即可，不用动表结构。而且查询的时候能很好地使用覆盖索引 。

C、独立出 指标异常表 来是非常棒的想法mysql fetch into，查询触发某异常的指标时可以只扫描 指标异常表 即可，减轻了主表的压力。

D、数据操作实例

插入数据：

-- 插入：A异常
INSERT INTO t_indicator (id, dossier_id, name, result) VALUES (101, 201, '指标名', 15);  
INSERT INTO t_indicator_exception (indicator_id, exception_type) VALUES (101, 1);  
-- 插入：A+B异常
INSERT INTO t_indicator (id, dossier_id, name, result) VALUES (101, 201, '指标名', 15);  
INSERT INTO t_indicator_exception (indicator_id, exception_type) VALUES (101, 1);
INSERT INTO t_indicator_exception (indicator_id, exception_type) VALUES (101, 2);

查询数据：

-- 查A异常
SELECT i.*
FROM t_indicator i
  INNER JOIN t_indicator_exception e ON e.indicator_id = i.id AND e.exception_type = 1;

总结

方案一（多字段多索引）：仅适用于异常字段少、固定不变的场景，优势是逻辑清晰、查询效率高。

方案二（位图压缩）：通过二进制位映射多个异常状态，仅需 1 个字段 + 1 个索引，适配字段动态新增，查询仅需 IN 筛选，仅在 IN 值极多时略有性能损耗（99.9% 场景无影响）。

方案三（倒排索引）：拆分异常关系表，主表精简，通过关联查询实现筛选，扩展性极佳，索引利用率高，还能减轻主表查询压力。

建议：动态新增异常字段的场景下，优先选择位图压缩（实现简单、查询便捷）或倒排索引（架构更优、性能稳定），替代传统多字段多索引方案，平衡查询效率与维护成本。

朋友们，你们还有什么其他的方案？欢迎评论区交流！