如何缩减含多列索引的十亿级Message数据表体积？

针对你10亿行的Message表因联合索引导致体积翻倍的问题，以下是几个实用的优化方案：

1. 将联合索引设为聚簇主键（组合唯一时优先使用） #

InnoDB的聚簇索引会把数据行直接存储在索引结构中，二级索引则会包含主键列。如果(userId, campaignId, notificationId)的组合是唯一的，可以直接将其设为主键——主键索引本身就能支持你的两种过滤查询，同时避免了数据行与二级索引重复存储这三列的问题，从根源上减少冗余。

执行语句：

ALTER TABLE Message DROP PRIMARY KEY, ADD PRIMARY KEY (userId, campaignId, notificationId);

注意：如果该组合不唯一，InnoDB会自动添加6字节的隐藏rowid作为主键的一部分，反而增加存储开销，这种情况不适用此方案。

2. 构建覆盖索引替代普通联合索引 #

如果无法将联合索引设为主键，可以把isOpened列加入联合索引，做成覆盖索引。这样查询时直接从索引就能获取所需数据，不需要回表，同时可以删除原有的非覆盖联合索引，减少索引的冗余存储。

执行语句：

ALTER TABLE Message DROP INDEX idx_user_campaign_notification; -- 删除原索引
CREATE INDEX idx_user_campaign_notification_covering ON Message (userId, campaignId, notificationId, isOpened);

3. 启用InnoDB数据压缩 #

InnoDB支持行级或页级压缩，对于存在大量重复值的列（比如userId、campaignId），压缩能显著减少存储体积。代价是会增加少量CPU开销，需要根据业务的性能容忍度调整。

执行语句：

ALTER TABLE Message ENGINE=InnoDB ROW_FORMAT=COMPRESSED;

4. 水平分表拆分数据 #

10亿行的单表本身属于超大规模表，按userId或campaignId进行水平分表，既能缩减单表的索引体积，也能降低整体存储的冗余度。比如按userId哈希取模分成100个分表，每个分表仅存储对应区间的用户数据，每个分表的索引规模仅为原单表的1/100，总存储体积会大幅下降。

示例分表逻辑：将userId % 100的结果作为分表后缀，创建Message_00到Message_99的分表，每个表结构与原表一致，查询时根据userId路由到对应分表。

5. 优化数据类型 #

检查各列的数据类型是否存在冗余：

• 如果userId的实际取值范围不超过2^31-1（约21亿），可以将bigint改为int，每行节省4字节，10亿行就能节省40GB左右的空间；
• campaignId和notificationId如果用int足够，无需调整；isOpened的bit类型已经是最小存储单位，无法再优化。

内容的提问来源于stack exchange，提问作者Anh Duc Ng