所有列均可搜索的人员大表应当如何设计索引？

避免全字段建单列索引的核心原因 #

全字段建单列索引会带来严重的写入放大问题，每次插入、更新数据都要修改所有对应索引，写入性能会随索引数量增加线性下降；同时多条件查询时，数据库即便能用索引合并特性，查询效率也远低于针对性优化的索引方案，投入产出比极低。

可落地的索引优化方案 #

• 优先为高频查询组合建联合索引：先统计历史搜索日志，提取Top 20%的高频查询组合，按照「等值条件在前、区分度高的字段在前」的原则构建联合索引，比如蓝眼睛+会弹吉他是高频组合，可建(eye_color, can_play_guitar, age, hairstyle)这类联合索引，联合索引的前缀还可以被其他包含前缀字段的查询复用，索引利用率远高于零散单列索引，写入开销也低很多。
• 低基数字段适配专用索引类型：眼睛颜色、发型、是否会某类乐器这类低基数（不同枚举值少）、布尔类型的字段，不要建普通B树索引：
  • 用PostgreSQL等支持位图索引的数据库时，给这类字段建位图索引，多条件查询时数据库可直接做位图的与或运算，查询效率高，写入开销远低于同数量的B树索引
  • 用MySQL 8.0以上版本时，可把所有技能标签存入JSON数组，为数组字段建多值索引，比如can_play字段存["piano","guitar"]，查询会弹吉他的人员时用JSON_CONTAINS(can_play, '["guitar"]')即可走索引，无需为每个技能单独建字段和索引。
• 无规律灵活搜索场景走异构索引：如果业务确实需要支持任意字段的随机组合查询，没有明显的高频规律，不要硬在关系型数据库上实现，可把需要搜索的字段异步同步到Elasticsearch这类搜索引擎，由搜索引擎通过全量倒排索引支持多条件组合查询，底层业务库只保留主键索引和少量必要的核心业务索引即可，同时兼顾写入性能和搜索灵活性。

写入性能补充优化建议 #

• 定期清理无用、重复的索引，避免不必要的索引维护开销
• 写入压力大的场景可优化数据库的索引缓存策略，比如开启MySQL的change buffer，把非唯一索引的修改缓存起来批量刷盘，降低单次写入的索引更新开销
• 区分度低于10%的字段不要单独建索引，这类字段单独走索引的效率甚至不如全表扫描，还会额外占用写入资源。

内容的提问来源于stack exchange，提问作者William Martins