集合查询性能最优方案是关联表：建中间表、联合主键按查询频次排序、COUNT(role_id)替代COUNT(*)、JOIN时为role.name建索引，可支撑千万级数据毫秒响应。
用 JSON 字段存集合，查询性能会明显下降
MySQL 5.7+ 支持 JSON 类型，很多人用它存标签、权限列表、多选值等集合数据，比如 {"roles": ["admin", "editor"]}。但直接用 JSON_CONTAINS() 或 JSON_EXTRACT() 做 WHERE 条件，基本无法走索引——除非你建了生成列 + 函数索引。否则每次查询都要全表解析 JSON 文本，数据量一过十万，响应就明显变慢。
实操建议：
- 避免在
WHERE中对JSON字段做动态路径提取，例如JSON_EXTRACT(meta, '$.tags[0]')—— 这种写法完全不可索引 - 如果必须用 JSON，把高频查询的字段单独抽成生成列，例如：
ALTER TABLE users ADD role_list JSON AS (meta->'$.roles');
ALTER TABLE users ADD INDEX idx_role_list ((CAST(role_list AS CHAR(255)))); - 更稳妥的做法是放弃 JSON 存集合，改用关联表（如
user_roles），配合JOIN或EXISTS查询，性能和可维护性都更可控
用逗号分隔字符串存集合，LIKE 查询几乎必然慢
类似 role = 'admin,editor,viewer' 这种设计，常见于老系统迁移或图省事的场景。想查“是否含 editor”，写 role LIKE '%editor%' 看似简单，实则灾难：无法使用索引、易误匹配（比如匹配到 supereditor）、不支持原子更新。
实操建议：
- 绝对不要用
LIKE '%xxx%'在长文本字段上做集合成员判断 - 哪怕加了前缀索引（如
INDEX(role(10))），也对LIKE '%...'无效 - 如果真要保留字符串结构，至少用定长分隔（如
|admin|editor|）并配合正则：role REGEXP '(^|\\|)editor(\\||$)'，但仍是全表扫描，仅比LIKE少点误匹配
关联表 + 覆盖索引是集合查询最稳的方案
标准的多对多关系（用户-角色、文章-标签、订单-商品）应该用中间表，这是 MySQL 最擅长的模式。只要索引设计得当，即使千万级数据也能毫秒响应。
实操建议：
- 中间表主键设为联合主键，顺序按查询频次排，例如常用「查某用户所有角色」，就设
PRIMARY KEY (user_id, role_id)；若常用「查某角色下所有用户」，则反过来 - 需要统计数量时，避免
SELECT COUNT(*) FROM user_roles WHERE user_id = ?，改用SELECT COUNT(role_id) FROM user_roles WHERE user_id = ?并确保role_id非空（NOT NULL），这样能走索引覆盖，无需回表 - 如果还要查带名称的集合（如用户+角色名），用
JOIN比多次查询快得多，但注意别漏掉role.name上的索引
全文索引只适合模糊搜索，不适合精确集合判断
有人试过把集合字段（如逗号字符串）设为 TEXT + FULLTEXT 索引，再用 MATCH ... AGAINST 查关键词。这在搜索场景有用，但对「是否包含某值」这类确定性判断，结果不可靠（停用词、分词逻辑、最小词长限制都会干扰），且无法保证一致性语义。
实操建议：
-
FULLTEXT不适用于权限校验、状态枚举、ID 列表等需要精确匹配的集合字段 - 如果业务同时需要「精确包含」和「模糊搜索」，拆成两个字段：一个用关联表做精确查询，另一个用
JSON或字符串 + 全文索引做搜索补充 - 注意
innodb_ft_min_token_size默认是 3，意味着单字符值（如a,Y）根本不会被索引进去
