77k行含大字段的InnoDB表SUM查询缓慢优化求助

核心问题分析 #

COUNT(*)查询快是因为可以利用fast_sdate索引做覆盖查询，无需读取主表数据；而SUM(total)需要读取包含total的完整数据行，但cart_data_json作为大字段，导致每个InnoDB数据页能存储的行数极少（77k行占237MB，平均每行约3KB，若cart_data_json占大部分，每页仅能存几十行），查询时需要扫描大量磁盘数据页，IO开销巨大，所以速度慢。

具体优化方案 #

1. 创建覆盖索引（优先推荐） #

针对SUM(total)的查询场景（通常带时间范围过滤），创建包含过滤字段和聚合字段的覆盖索引，让查询无需回表读取大字段数据：

CREATE INDEX idx_sdate_total ON commerce_sales_test(commerce_sales_date DESC, total);

当执行类似SELECT SUM(total) FROM commerce_sales_test WHERE commerce_sales_date BETWEEN 1609459200 AND 1640995200;的查询时，MySQL会直接使用这个索引完成计算，速度可提升至与COUNT(*)相当。

2. 调整InnoDB内存配置 #

通过增大内存缓存减少磁盘IO：

• 增大innodb_buffer_pool_size：将其设置为服务器物理内存的50%-70%（例如8GB内存服务器设置为5GB），让更多数据页缓存到内存，避免重复磁盘读取。修改my.cnf/my.ini后重启MySQL生效：

  innodb_buffer_pool_size = 5G
  

• 提升IO线程数：如果使用机械硬盘，调整以下参数提升并发IO处理能力：

  innodb_read_io_threads = 8
  innodb_write_io_threads = 8
  

• 优化日志刷新策略：若业务允许牺牲一点实时数据安全性换取性能，设置：

  innodb_flush_log_at_trx_commit = 2
  

3. 拆分大字段（可选，覆盖索引+内存调整仍不足时） #

将大字段cart_data_json拆分到独立表，主表仅保留小字段，减少SUM查询时扫描的数据量：

-- 主表：仅保留核心字段
CREATE TABLE `commerce_sales_main` (
  `commerce_sales_id` varchar(32) NOT NULL,
  `commerce_sales_date` bigint unsigned NOT NULL DEFAULT '0',  
  `total` bigint NOT NULL DEFAULT '0',
  PRIMARY KEY (`commerce_sales_id`),
  KEY `fast_sdate` (`commerce_sales_date` DESC),
  KEY `idx_sdate_total` (`commerce_sales_date` DESC, total)
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;

-- 大字段表：存储购物车JSON数据
CREATE TABLE `commerce_sales_cart_data` (
  `commerce_sales_id` varchar(32) NOT NULL,
  `cart_data_json` mediumtext,
  PRIMARY KEY (`commerce_sales_id`),
  FOREIGN KEY (`commerce_sales_id`) REFERENCES `commerce_sales_main`(`commerce_sales_id`)
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;

后续查询SUM(total)仅需操作主表，需要查看购物车数据时再通过commerce_sales_id关联查询大字段表。

为什么之前的尝试无效？ #

• 将cart_data_json改为varchar(64000)：本质还是大字段，数据页存储行数没有明显提升，IO开销依旧大；
• 缩短cart_data_json至100字符：减少了单条记录的大小，每个数据页能存储更多行，扫描IO次数大幅减少，所以性能提升，但不符合业务需求。

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