MySQL 8.0.35中JSON列引发Filesort及查询缓慢问题求助

问题原因 #

• JSON列的off-page存储开销：payload是单条30-50KB的大JSON字段，InnoDB会将这类超过阈值的大字段存储在溢出页（off-page），仅在数据行中保留指向溢出页的指针。执行SELECT *时，需先通过联合索引定位目标行，再逐个读取溢出页的JSON数据，767条数据的总IO量约23-38MB，这是executing阶段耗时0.5s的核心原因。
• 回表操作的放大效应：虽然id_customer,status,created_at联合索引能快速过滤并排序数据，但因需回表获取所有列，每一行都要额外执行溢出页读取操作，IO开销的累积导致整体耗时剧增。
• 优化器统计偏差：OPTIMIZER TRACE中显示的row_size=4GB是优化器对JSON列最大可能长度的估算误差，虽不直接引发慢查询，但反映出优化器无法准确评估大JSON字段的实际开销。
• 表复制后的体积变化：原表可能存在磁盘碎片，复制过程中碎片被整理导致表体积增至470MB，但JSON的off-page存储结构未改变，因此单独查询payload仍耗时0.5s。

解决办法 #

• 避免SELECT *，仅查询业务必需字段：若无需payload列，直接指定所需字段，已验证此操作可将耗时降至0.01s。
• 拆分JSON列的常用字段：将payload中业务频繁使用的字段（如交易金额、渠道信息等）提取为独立的普通列，既避免读取整个大JSON，还能为这些字段创建索引提升其他查询性能。
• 启用行压缩减少IO开销：将表的行格式改为COMPRESSED，压缩JSON数据的磁盘占用，降低读取时的IO压力：

  ALTER TABLE finance_trans_deposits ROW_FORMAT=COMPRESSED;
  

  注意：压缩会增加CPU开销，需根据服务器资源情况权衡。
• 归档历史数据：若表中存在大量不常用的历史交易数据，将其归档至单独的历史表，减少当前表的数据量，降低回表时的IO压力。

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