面向万亿级规模的MySQL device_events大表结构优化咨询

适配场景：未来单表规模达万亿级、90%以上查询筛选近30天事件、高频执行JSON提取操作、已完成查询逻辑层优化、无法按设备/日志来源做自定义固定分区

1. 时间维度RANGE分区改造（优先级最高） #

• 放弃按设备、日志来源做分区的思路，完全以file_date为分区键做按日粒度的RANGE分区。MySQL原生支持日期类型的范围分区，无自定义分区的规则限制，且适配现有绝大多数按时间筛选的查询逻辑。
• 针对当前主键结构调整：将原主键(id)修改为联合主键(file_date, id)，满足MySQL分区要求“分区键必须包含在所有唯一键中”的约束。调整后自增id的全局唯一性不受影响，同时InnoDB聚簇索引会按时间顺序连续存储数据，近30天的查询扫描可直接通过分区裁剪跳过全量历史分区的扫描，IO开销下降1~2个数量级。
• 分区运维适配：历史数据归档时直接DROP超过保留周期的旧分区，操作秒级完成，远快于逐行DELETE的清理效率。

2. JSON字段的结构化索引优化 #

• 针对查询中高频提取的JSON路径（如示例中的$.policy_identity），在表结构中新增对应的虚拟生成列：

  ALTER TABLE device_events ADD COLUMN policy_identity VARCHAR(255) 
  GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(message, '$.policy_identity'))) VIRTUAL;
  

  生成列由数据库自动维护值，无需修改现有写入逻辑。
• 给上述生成列添加普通索引，查询时直接命中索引完成分组、聚合计算，无需全量解析30天范围内的所有JSON字段，JSON提取环节的耗时可下降90%以上。
• 保留原message JSON字段存储全量日志内容，仅对高频访问的JSON路径做拆分，兼顾灵活查询能力和性能。

3. 现有索引结构裁剪与适配 #

• 由于已经实现按file_date的分区裁剪，原有针对created_at、file_date的单列索引可直接删除，避免索引冗余占用存储、拖慢写入性能。
• 低基数字段类型调整：现有source为TEXT类型、仅建了255位前缀索引，由于其基数仅3000余、长度可控，直接修改为VARCHAR(255)类型，消除前缀索引的命中率损耗，同时降低索引存储开销。
• 适配高频查询的联合索引：在每个分区内部建立贴合实际过滤条件的联合索引，如(device_id, file_date)、(source, file_date)，比原有单列索引的查询效率更高，且单分区内的索引体积更小，缓冲池命中率更高。
• 删除无意义字段：日志类数据为追加写入、无后续更新操作，删除从未被查询使用的updated_at字段，减少行存储宽度，提升单页内存的数据缓存效率。

4. 冷热数据结构隔离 #

• 划分热、冷表结构：
  • 热表device_events_hot：仅保留近30天的可写入数据，按日做RANGE分区，保留所有必要的查询索引，承接所有实时指标计算的查询。
  • 冷表device_events_cold：存储30天以上的历史归档数据，开启行压缩（COMPRESSED行格式），仅保留历史指标计算需要的拆分字段与最小化索引，不承接实时查询请求。
• 定时迁移：每日将热表中超过30天的分区直接交换至冷表，操作为元数据级修改，无数据拷贝开销，秒级完成。

5. 避坑提示 #

• 不尝试按device_id、source做列表分区：现有device_id基数超4万、source基数超3000，远超出MySQL单表最多8192个分区的上限，维护成本极高且无实际收益。
• 不直接在JSON字段上建全局多值索引：JSON内容的多变性会导致索引维护开销呈指数级上升，仅针对明确的高频固定路径拆分生成列建索引，性价比最高。

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