MySQL大数据量关联场景下复合主键的索引策略咨询

嘿，先给你划个关键结论：你的Purchase表已经把(CustomerID, ItemID, Date)设为复合主键了，MySQL会自动为这个组合创建一个唯一的聚集索引——所以你完全没必要手动再给这三个字段加唯一索引，纯粹是做无用功，还会浪费存储和维护资源。

接下来结合你说的「数据量大、大量关联查询」的业务场景，咱们好好唠唠为什么不能用三个单独的非唯一索引，以及复合主键索引到底好在哪：

为什么绝对不推荐三个单独的非唯一索引？ #

• 查询效率拉胯：MySQL在执行查询时，通常只能选用一个单字段索引，剩下的条件得靠回表扫描来过滤。比如你要查「北京客户张三在2024年5月买的红色T恤」，就算给CustomerID、ItemID、Date各加了索引，MySQL大概率只会挑一个索引（比如CustomerID）先筛选出张三的记录，然后再逐条扫描这些记录找匹配的ItemID和Date——数据量大的时候，这个扫描过程慢到能让用户骂娘。
• 维护成本爆炸：每次对Purchase表做插入、更新、删除操作，三个索引都得跟着更新，写操作的开销直接翻三倍，对于大数据量的表来说，这会严重拖慢业务的响应速度，甚至可能引发锁等待问题。

复合主键索引恰好适配你的场景 #

• 完美支撑关联查询：你平时的关联查询大多是通过CustomerID连Customer表，或者ItemID连Item表吧？复合主键的前缀字段（CustomerID、ItemID）正好能被MySQL高效利用。比如你要查「所有上海客户的购买记录」，MySQL先通过Customer表的city索引找到所有上海客户的CustomerID，然后直接用Purchase的复合主键索引快速定位到对应的Purchase记录，完全不用回表瞎扫。
• 多条件查询秒出结果：如果业务里经常有「某客户某天买了某件商品」这类精确查询，这个复合主键索引能直接覆盖整个查询条件，一步到位找到目标记录，速度快得飞起。
• 省空间又省IO：一个复合索引的存储空间比三个单独索引加起来小得多，对于大数据量的表来说，能大幅减少磁盘IO的压力，毕竟磁盘读写是数据库性能的最大瓶颈之一。

额外给你两个优化小技巧 #

• 如果你的业务里有高频查询，比如「查某客户最近30天的所有购买商品」，可以考虑加个覆盖索引。比如MySQL 8.0.14及以上版本可以这么建：CREATE INDEX idx_purchase_cust_date ON Purchase(CustomerID, Date) INCLUDE (ItemID);，这样查询的时候直接从索引里拿数据，不用回表查原数据；要是用的低版本MySQL，就把ItemID加到索引里，变成(CustomerID, Date, ItemID)。
• 定期清理冗余索引：用SHOW INDEX FROM Purchase;或者查sys.schema_unused_indexes看看哪些索引从来没被用过，直接删掉，别让它们占着茅坑不拉屎。

总结下来：别折腾额外的唯一索引，也千万别搞三个单独的非唯一索引。你现有的复合主键索引已经是最适合你当前场景的选择，按需加个覆盖索引就够了。

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