做后端这几年，最常被问到也最容易翻车的需求就是“多选项存储”：一个用户可能同时挂着一二十个标签，一个活动可能挂在多个分类下，一张工单会叠加多种处理状态。一开始大家都会随手用逗号把这些值拼进一个字符串字段，看着挺省事，等数据量上来，慢查询日志天天报警的时候才意识到，能存和高效存、高效查完全是两码事。
这篇不是讲枯燥理论，是把这几年沉淀下来的多选项高效存取方案完整拆一遍，核心思路是用一个整数作为选项位图，把整个选项集合压缩进 8 字节，查询用位与运算变成一次整数比较。我在真实千万级用户表上落地过这套方案，原本 2 秒多的用户圈选接口，优化后降到几十毫秒。无论你是做后端、做数据建模，还是维护 MySQL/PostgreSQL 时被“多选项怎么存”坑过，这篇都值得往下看。
1. 先说清楚：多选项到底难在哪
1.1 业务里常见的多选项长什么样
先对齐一下场景，不是那种“性别男女”的单选项，而是同一个业务对象身上会挂多个值的情况。
- 用户标签：一个用户可能同时是“新客”“高活跃”“深圳”“偏好数码”，运营后台要按任意标签组合圈人。
- 商品属性：一件商品可能同时属于“夏季”“断码”“清仓”等多个活动维度和属性集合。
- 权限点：一个管理员拥有多个权限点，每次接口调用都要快速校验是否拥有指定权限。
- 工单标记：同一张工单可能同时处于“已审核”“待处理”“加急”等互不排斥的标记状态。
这些业务的共同特征非常明显：一个主对象对应一个选项集合，选项总量往往在几十个以内，但主对象数量常常是百万、千万级。这种“选项少、主数据多”的形态，决定了设计目标不是把数据塞进去，而是让按选项筛选、按组合筛选足够快。
1.2 高不高效，得从四个维度看
判断一套存储方案是不是“高效”，只看某个指标会偏。我通常用四个维度来打分：
- 存储空间：数据膨胀多少，深度影响缓存命中率和存储成本。
- 查询性能：按单个选项、按多个选项组合过滤时，是否能利用索引、是否稳定。
- 写入更新成本：选项变更时是局部更新还是要整行重写。
- 扩展性：选项数量增加、跨库之后，方案会不会失效。
真正落地时这四个维度不可能全胜，一定是有取舍的。下面每个方案过一遍，你就知道为什么位图整数方案会胜出。
2. 常见方案逐个过一遍：各有各的坑
2.1 逗号分隔字符串：最容易写出，最难维护
这是新人最爱用的一招：
CREATE TABLE user_tag ( user_id BIGINT, tags VARCHAR(255) -- 存成 "0,2,5,13" );写入倒是简单，查询就尴尬了。想查“包含标签 3”的用户，最直觉的写法是WHERE tags LIKE '%3%'，这一写就是全表扫描。更致命的是误匹配：'13,30'里也包含字符'3'，你还得在字符串两侧拼上分隔符才能勉强精确，SQL 写出来长到没法看。
更新时同样难受，想给某个用户追加一个标签，得把整条字符串读出来，在应用层拆数组、追加、再写回去。一个高频变更接口，硬生生被字符串字段拖成了慢 SQL 常客。
提示：如果只是存不查，逗号分隔无所谓；只要涉及按选项筛选，这个方案直接排除，别犹豫。
2.2 JSON 数组：可读性好，索引很憋屈
MySQL 5.7 之后很多人把多选项改成 JSON 数组：
CREATE TABLE user_profile ( user_id BIGINT, tags JSON );查询可以写成WHERE JSON_CONTAINS(tags, '"3"')，语义确实比逗号分隔清楚。但坑在后面：JSON_CONTAINS 属于函数表达式，普通索引帮不上忙。你要么建虚拟列 + 函数索引，要么接受每次查询都做一次 JSON 解析。一个百万行表，只要条件里带上 JSON_CONTAINS，响应时间立刻拉胯。
JSON_SET 更新也是全量重写，数据量大之后，写放大的问题比逗号分隔还严重。我见过有团队上线两个月后，JSON 字段的表把磁盘 IO 直接打满，最后不得不做数据迁移。
2.3 中间关联表：范式最正，查询要多绕一步
教科书标准答案是拆两张表加一张关联表：
SELECT u.* FROM users u INNER JOIN user_tags ut ON u.id = ut.user_id WHERE ut.tag_id = 3;查单个标签挺好，但想查“同时含标签 3 和标签 5”的用户，SQL 就变成：
SELECT u.id FROM users u INNER JOIN user_tags ut1 ON u.id = ut1.user_id AND ut1.tag_id = 3 INNER JOIN user_tags ut2 ON u.id = ut2.user_id AND ut2.tag_id = 5;条件组合一多，JOIN 数量跟着涨，执行计划越来越复杂。千万级用户 + 上亿关联记录时，这种查询大概率变成索引合并或者临时表，慢查询日志基本天天有它。
关联表的优势是聚合统计非常方便，比如按标签统计用户量，一条 GROUP BY 就能出来。但如果你核心诉求是“快速按组合筛主键”，关联表的查询成本是硬伤。
2.4 位图整数：用一个整数装下所有选项
位图方案的核心：用一个整数里的每一个二进制位代表一个选项。第 0 位代表选项 0，第 1 位代表选项 1，以此类推。选了哪些选项，就置位哪些位。
存储上它只是一个 BIGINT，占 8 字节；查询上它是整数按位与运算，天然吃索引和 CPU 的整数比较。我把它放到下一章完整展开，先看一张对比表，结论就很明显了。
| 方案 | 存储占用 | 按单选项查询 | 按组合查询 | 更新成本 | 扩展性 |
|---|---|---|---|---|---|
| 逗号分隔字符串 | 高 | 差 | 差 | 高 | 差 |
| JSON 数组 | 中 | 中 | 中 | 高 | 中 |
| 中间关联表 | 高 | 较好 | 一般 | 中 | 好 |
| 位图整数 | 极低 | 极好 | 极好 | 低 | 有限，可多列扩容 |
3. 位图方案不玄乎：一比特位代表一个选项
3.1 从二进制位权说起
计算机底层本来就是二进制。一个 64 位的 BIGINT，最多可以表达 64 个独立选项。约定第 i 位为 1 表示“选了编号为 i 的选项”，为 0 表示“没选”。
位权和选项编号的映射关系长这样：
| 选项编号 | 位权值（十进制） | 二进制表示 |
|---|---|---|
| 0 | 1 | 0001 |
| 1 | 2 | 0010 |
| 2 | 4 | 0100 |
| 3 | 8 | 1000 |
| ... | ... | ... |
| n | 1 << n | 第 n 位为 1 |
假如某用户选了选项 0、2、5，那么标签掩码就是：
1 + 4 + 32 = 37
这个 37 就是最终落库的字段值。看起来像魔法，其实就是小学数学：二进制位权相加。
3.2 为什么它能走索引
BIGINT 是数据库原生基础类型，整数等值和范围判断在优化器眼里非常友好。比较一下三种写法的执行开销：
LIKE '%3%'：字符串匹配，全表扫描，CPU 开销随行数线性增长。JSON_CONTAINS(tags, '"3"')：每次解析 JSON，函数计算，索引基本失效。(tags & 4) = 4：一次整数按位与 + 一次整数比较，整列扫描也很快，配合过滤条件还能走索引。
有些 MySQL 版本对(col & const) = const的形式可以优化成索引范围扫描。即使优化器在个别版本里选择了全表扫描，纯整数比较的吞吐量也比字符串匹配高一个数量级。我多次实测，千万行级单表再加点过滤条件，位运算筛选基本都在几十毫秒到一两百毫秒的区间，完全够用。
3.3 写入端：选项数组转整数的算法
落库之前，要在服务端把业务传过来的“选项数组”转成一个整数。以 PHP 为例：
function tagsToMask(array $tagOffsets): int { $mask = 0; foreach ($tagOffsets as $offset) { $mask |= (1 << $offset); } return $mask; }Java 也一样：
public long tagsToMask(int[] tagOffsets) { long mask = 0L; for (int offset : tagOffsets) { mask |= (1L << offset); } return mask; }如果业务里的选项 ID 是字符串编码，比如new_user、high_active，那么先用一个映射表把字符串编码换成位偏移：
const BIT_MAP = [ 'new_user' => 0, 'high_active' => 1, 'shenzhen' => 2, ]; function encodeTags(array $tags): int { $mask = 0; foreach ($tags as $tag) { if (isset(BIT_MAP[$tag])) { $mask |= (1 << BIT_MAP[$tag]); } } return $mask; }这层映射放在配置中心或字典表里，不要写死在业务代码里，后面会专门讲。
3.4 查询端：SQL 怎么写才高效
这是全篇最核心的部分，三种查询需求对应三种写法。
需求一：查出包含选项 2 的所有用户。
SELECT user_id FROM user_profile WHERE (tags & 4) = 4;注意选项 2 的位权是 4，判断“包含”用(tags & 位权) = 位权，不是<> 0。用<> 0只会误伤，判断条件必须是掩码全部置位。
需求二：查出同时包含选项 0 和选项 2 的用户，组合掩码是 1 | 4 = 5。
SELECT user_id FROM user_profile WHERE (tags & 5) = 5;这里 5 就是“选项 0 和选项 2”的位掩码，= 5表示两个位都必须为 1，语义是 AND。
需求三：查出包含选项 0 或者选项 2 至少之一的用户。
SELECT user_id FROM user_profile WHERE (tags & 5) <> 0;<> 0表示只要任一位置命中了组合掩码里的位就命中，语义是 OR。
再补一个“不包含某个选项”的写法：
SELECT user_id FROM user_profile WHERE (tags & 4) = 0;这在实际业务里很常用，比如“筛掉已经打过标记的用户”。
3.5 读出来怎么还原成选项列表
接口返回给前端时，不能把 37 直接甩给前端让人家去猜，要还原成选项数组：
function maskToArray(int $mask): array { $result = []; $offset = 0; while ($mask > 0) { if (($mask & 1) === 1) { $result[] = $offset; } $mask >>= 1; $offset++; } return $result; }原理就是不断右移检查最低位。实际项目中我会把编码映射表倒过来，把[0, 2, 5]转成['new_user', 'shenzhen', 'xxx']，前端拿到的永远是语义化的标签数组。
4. 完整落地：从建表到接口的一整套实现
4.1 建表怎么建
实际项目中不要只放一个 tags 字段，建议把常用的其他过滤条件也加到表里做联合索引。下面是一张用户画像表的建表 SQL：
CREATE TABLE user_profile ( user_id BIGINT UNSIGNED NOT NULL COMMENT '用户ID', tags BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '标签位图，0~63位对应64个标签', user_city VARCHAR(32) NOT NULL DEFAULT '' COMMENT '城市', source_channel VARCHAR(32) NOT NULL DEFAULT '' COMMENT '注册渠道', updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (user_id), KEY idx_city_tags (user_city, tags) ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;idx_city_tags (user_city, tags)这个联合索引很有用。运营后台常见的筛选条件是“深圳 + 组合标签”，先按 city 等值过滤缩小范围，再在位图上做位与，执行效率非常高。这里 tags 必须是 BIGINT UNSIGNED，避免符号位带来负数判断问题。
4.2 提交与更新逻辑
后端接口接收到的入参是tagIds: [0, 2, 5]，写库流程走三步：
- 参数校验：tagIds 每个值必须在合法范围内，比如 0 到 63。
- 转掩码：调用
tagsToMask得到整数 37。 - 写入或更新：新增用 INSERT，变更更新用 UPDATE。
更新接口要注意一个细节：前端传的 tagIds 如果代表“最终完整集合”，直接整字段覆盖就行；如果代表“追加”或“删除”，要给出独立语义。我自己习惯在接口层拆分两个操作：
// 追加标签 $mask = tagsToMask($input['tagIds']); UPDATE user_profile SET tags = tags | $mask WHERE user_id = ?; // 移除标签 UPDATE user_profile SET tags = tags & ~$mask WHERE user_id = ?;追加用位或，移除用按位取反后再位与，这两条 SQL 都是原子操作，不需要先读后写，彻底解决了并发下丢失更新问题。这也是位图方案在写路径上比 JSON、逗号分隔更强的关键点。
4.3 筛选接口的逻辑组装
运营后台“用户圈选”是典型组合查询，页面可能勾 “深圳渠道 + 含标签 0 + 不含标签 2”。对应 SQL：
SELECT user_id FROM user_profile WHERE user_city = '深圳' AND (tags & 1) = 1 AND (tags & 4) = 0 LIMIT 200;条件组装端收到参数后，把“包含”和“不包含”分别拼成两个掩码组，循环生成条件段。关键是所有条件都是整数比较，条件再多也只是多个 AND，执行计划依然可控。
4.4 返回前把掩码还原成数组
查询结果只是 user_id 和掩码，不能直接输出给前端。这里建议在服务端统一转换成标准响应结构：
{ "user_id": 1001, "tags": ["new_user", "shenzhen"] }前端永远不感知内部存的是整数还是字符串，后续存储方案即使再调整，接口结构不变，调用方零改动。这是我这几年很注重的一点：存储细节只存在于数据访问层。
5. 扩容、跨库和周边配合
5.1 超过 64 个选项怎么办
64 个选项对于绝大多数业务够用，但碰上选项超过 64 的情况，有两条路可走。
第一，加列扩容。如果第一组选项已经用了 60 多个，再加一列tags_ext BIGINT UNSIGNED，原来的tags存 0 到 63 号选项，tags_ext存 64 到 127 号选项。查询跨两组时写成：
SELECT user_id FROM user_profile WHERE (tags & mask1) = mask1 AND (tags_ext & mask2) = mask2;第二，混合方案。超出核心限额的少数选项（比如低频运营位）丢给关联表，高频选项继续走位图。我实际项目里就是这么干的：前 64 个高频标签走位图，剩下每年新增的少量低频活动标签走关联表。灵活用混合方案，反而比死守单一方案更靠谱。
5.2 选项字典与编码映射一定要维护好
位图方案最大的风险不是技术，是“时间长了没人知道第 37 位是什么标签”。所以选项字典必须独立维护：
| 位偏移 | 选项编码 | 选项名称 | 状态 |
|---|---|---|---|
| 0 | new_user | 新客 | 启用 |
| 1 | high_active | 高活跃 | 启用 |
| 2 | shenzhen | 深圳 | 启用 |
这张字典表建议放进配置中心或独立数据表，并且禁止随意删除项。位图方案里，位偏移一旦被占用，删除会造成历史数据语义错乱。稳妥做法是新选项永远追加新位偏移，废弃选项只置“停用”，不下线。这个约束必须写进开发规范，否则半年后绝对有人踩到坑。
5.3 与 Redis、Elasticsearch 的配合
位图方案解决的是数据库层的边界问题。如果热点标签组合被反复查询，可以在 Redis 里加一层缓存，直接用掩码做部分 Key：
user:tag_sel:{mask}:{city}这里的 mask 算好之后拼进 Key，Redis 里存 user_id 数组，设置 5 分钟过期。热点筛选命中缓存时，连数据库都不用碰。
至于 Elasticsearch，它适合做全文检索和复杂分词场景。纯标签筛选用 ES 有点杀鸡用牛刀，而且 ES 的存储压缩率、查询延迟都不如整数字段有优势。如果是“标签 + 全文搜索”叠加，可以考虑把标签同步成 ES 的 keyword 字段做 post_filter，但核心维度筛选我建议仍然以数据库位图为主。
5.4 聚合统计不能直接按位分组
位图方案有一个明显弱项：没法做GROUP BY 标签这种统计。你不能对单个 bit 做 GROUP BY，所以“每个标签多少用户”这种报表需求会难受。
我的做法是独立维护一张标签计数表，在标签追加和移除的更新逻辑里同时增减计数：
// 事务内执行 UPDATE user_profile SET tags = tags | $mask WHERE user_id = ?; UPDATE tag_counter SET cnt = cnt + 1 WHERE tag_offset = 0 AND ...;如果历史存量数据需要补数，写一次性脚本来扫描 user_profile 表，对每条掩码按位遍历后累加计数。数据量不大时脚本跑几分钟，数据量大时建议拆批执行，避免长事务锁表。
6. 实战中踩过的坑和排查套路
6.1 位运算的括号别省，别赌数据库优先级
写 SQL 时我强烈建议一律显式加括号：
WHERE (tags & 16) = 16不要写WHERE tags & 16 = 16。虽然部分数据库的&优先级高于比较运算符，但不同数据库、不同版本、不同 ORM 拼接方式对运算符优先级的解析并不一致。我见过不止一次因为少括号，位运算被转成布尔运算，结果把整张表数据都导出来的事故。养成加括号的习惯，一次事故就省回所有成本。
6.2 注意 32 位系统和语言溢出
位运算的高性能建立在“整数原生支持”之上，但有些老服务器的 32 位环境会让你吃暗亏。PHP 在 32 位系统上 int 只有 32 位，左移超过 31 位就变负数或者溢出。Java 的 long 虽然 64 位但有符号位，第 63 位置位后会变成负数。
这类问题在测试环境不会立刻暴露，通常到了生产环境由某个高编号选项触发。建议：
- 服务端统一部署在 64 位环境，PHP 用
PHP_INT_SIZE >= 8做启动检查。 - Java 侧用
Long.rotateLeft或干脆限制选项编号在 0 到 62 之间，把符号位空出来。 - MySQL 侧字段一律用 BIGINT UNSIGNED，避免符号位干扰判断。
6.3 不要对列套函数，也别在一个字段里塞太杂的东西
位图方案的性能优势很大程度来自“整数字段可以直接比较”。如果你写出：
WHERE BIT_COUNT(tags) = 2BIT_COUNT 是对列做函数运算，索引直接失效，千万级表又是一条慢查询。需要统计“恰好含 N 个标签”时，与其用 BIT_COUNT，不如在应用层维护一个独立计数列，或者接受一个可控范围的全表扫描。
还有一种我常看到的问题：一个字段位图承载两种完全不相关语义，比如把“用户等级”和“用户标签”混在一个 BIGINT 里。这会极大降低代码可读性，后续维护的人根本分不清第几位代表什么。位图字段必须语义单一，不同领域模型拆成不同字段或不同表。
6.4 数据迁移：老系统字符串转位图
如果当前线上是逗号分隔字符串，想迁移到位图方案，不能直接 UPDATE。正确流程是先建好新字段，写一次性脚本逐行读取旧字符串，在应用层拆分成数组，再调用tagsToMask转整数回填新字段。
迁移过程中重点核对两项数据：
- 原始标签总数是否等于位图还原后的标签总数，可以用 BIT_COUNT 或应用层遍历对齐。
- 抽样选几个典型用户，人工比对迁移前后筛选结果是否一致。
千万级数据建议分批跑，每批 5000 到 10000 行，观察数据库负载。迁移完成后保留旧字段两到四个星期作为回滚依据，确认线上无异常再删。这类迁移我做过很多次，最大的教训就是别在迁移脚本里做复杂 SQL 拼接，拆在应用层做逐行处理，速度慢一点但结果可控。
6.5 常见问题速查表
| 现象 | 可能原因 | 处理方式 |
|---|---|---|
| 查询结果包含未选标签的用户 | 位运算没加括号，被当成布尔运算 | 所有条件显式加(tags & mask) = mask |
| 高编号选项写入后变成负数 | 32 位系统或 Java 符号位溢出 | 统一 64 位环境，限制位偏移 0~62 |
BIT_COUNT查询超时 | 对列套函数，索引失效 | 拆成计数列或接受可控全表扫描 |
| 迁移后标签数量对不上 | 旧字符串里有重复值或空格 | 应用层先清洗、去重再转掩码 |
| 新增选项后老数据查不到 | 字典表被删除编号导致的语义错乱 | 新选项只追加位偏移，不下线旧项 |
| 组合筛选很慢 | 缺联合索引 | 建(city, tags)或类似联合索引 |
最后分享一点个人体会
这套位图方案我在真实项目里用得最多的地方就是用户标签和权限码，可以说帮我扛过了不少高并发标签筛选场景，也让我少写了几十段烦人的 JOIN 查询。它最大的魅力不是“炫技”，而是把多选项问题压缩成一个原生整数，让存储、查询、更新都变得极其干脆。
但我也不建议所有场景都硬套位图。如果你大量依赖按选项做聚合报表，或者选项本身需要不断动态新增并展示成各种维度，那么关联表仍然是更合理的底座。技术选型从来不是比谁更高端，而是看哪个方案跟你的业务最好配合。位图方案的优势窗口很清晰：选项集合预先可控、主数据量大、查询压力集中、更新频繁，这时候就放心用，它不会让你失望。
