MySQL百万行表重复字符串列：拆表JOIN与索引的性能对比及多列影响

单个重复列（fruit）的性能对比 #

针对1000万行的stuff表，fruit列75%为高重复值（apple、orange、pear各占25%），其余为零散词汇且已建索引的场景：

• 保留原索引的性能更优：
  1. 原表查询时，MySQL可直接利用fruit列的二级索引执行覆盖索引扫描——若查询仅涉及fruit及少量其他字段，无需回表即可返回结果，效率极高；即便需要回表，InnoDB二级索引的叶子节点存储主键，回表操作的IO开销也可控。
  2. 拆分独立表后，每次查询都需额外执行JOIN关联：先在独立表中匹配对应ID，再回到主表关联数据，多了一次索引查找+关联计算的开销，反而拉高CPU与IO消耗。
  3. MySQL的B+树索引对高重复值的处理已十分成熟，不会因重复率高出现性能瓶颈，拆分JOIN带来的额外开销远大于原索引的维护成本。

多重复列（car、toy、color等）带索引的影响 #

若表中存在多个类似fruit的高重复列且均已建索引：

• 写操作性能急剧下降：
  每次对stuff表执行INSERT/UPDATE/DELETE时，所有二级索引都需同步更新。1000万行的大表中，多索引会导致写操作的IO与CPU开销成倍增加——每个写操作要修改多个B+树结构，维护成本飙升。
• 查询性能分化明显：
  • 若查询仅用到1-2个索引列，MySQL能选择最优索引，性能尚可；但如果查询需同时命中多个索引列，MySQL可能选择索引合并或直接走全表扫描，性能大幅下滑。
  • 过多索引会占用大量Buffer Pool内存，挤压热数据的缓存空间，间接降低所有查询的响应速度。
• 多表JOIN的风险：
  若将这些列全拆为独立表并关联，每次查询可能需要JOIN3-4个甚至更多表，查询优化器极易选错执行计划，导致查询性能崩盘。

总结建议 #

1. 单个高重复列：保留原索引是最优方案，拆分JOIN无性能收益，反而增加额外开销。
2. 多个高重复列：
   • 避免给每个列单独建索引，仅为查询频率最高的1-2个列创建索引；
   • 若存在组合查询需求，优先创建组合索引替代多个独立索引；
   • 仅当列的重复率接近100%且写操作极少时，才考虑拆分维度表，否则不建议通过JOIN关联。

内容的提问来源于stack exchange，提问作者Ethan Allen