超大规模MySQL表COUNT(*)优化咨询：精确/近似/异步选型

问题核心分析 #

你的campaign表有7000万+行且按月份分区，但COUNT(*)频繁超时的根本原因是：

• 即使分区裁剪生效，现有索引无法覆盖所有过滤条件（如BrandnameId、CreationTime、模糊查询），导致扫描大量索引行后还要回表过滤，耗时剧增
• 深度OFFSET分页本身效率极低，同时COUNT查询和分页查询无法复用优化路径

下面针对精确实时、近似、异步三种方案逐一分析，结合你的约束条件给出最优选择：

1. 精确实时方案（适合必须要求精确计数的场景） #

核心思路：让COUNT查询直接走覆盖索引，避免回表和无效扫描

• 优化多场景覆盖索引：
  根据你的过滤条件组合，针对性创建覆盖索引：

  • 无模糊查询时：创建HIX_Campaign_TenantId_IsDeleted_CreationTime_BrandnameId联合索引，包含所有过滤字段（TenantId, IsDeleted, CreationTime, BrandnameId），这样COUNT(*)可以直接通过索引统计，无需回表扫描数据行
  • 有模糊查询时：将Name/Content字段改为全文索引（替代低效的LIKE '%keyword%'），再创建联合索引结合TenantId、IsDeleted等过滤字段，让COUNT查询基于全文索引的过滤结果统计，避免全表扫描

  注意：索引创建需在业务低峰期操作，避免锁表影响生产

• 替换深度OFFSET为Keyset分页：
  深度OFFSET（如第400000页）本身效率极低，改成基于CreationTime + 主键的keyset分页：

  -- 原OFFSET分页（低效）
  SELECT * FROM campaign WHERE TenantId=? AND IsDeleted=0 LIMIT 10 OFFSET 4000000;
  -- 改为Keyset分页（高效）
  SELECT * FROM campaign WHERE TenantId=? AND IsDeleted=0 AND CreationTime < ? AND Id < ? ORDER BY CreationTime DESC, Id DESC LIMIT 10;
  

  这种方式分页查询更快，同时COUNT查询可以复用相同的索引路径，减少资源消耗

• 分区级精准统计：
  利用表的月份分区特性，直接针对命中的分区做COUNT，结合覆盖索引进一步缩小扫描范围：

  SELECT COUNT(*) FROM campaign PARTITION(p202605) WHERE TenantId=? AND IsDeleted=0 AND ...;
  

2. 近似方案（适合对计数精度要求不高的场景） #

如果前端分页仅需要大概的总页数，不需要精确到个位，直接用MySQL内置统计信息，几乎零耗时：

• 分区统计累加：从INFORMATION_SCHEMA.PARTITIONS获取对应分区的近似行数，再结合过滤条件估算：

  SELECT SUM(ROWS) FROM INFORMATION_SCHEMA.PARTITIONS 
  WHERE TABLE_SCHEMA='你的库名' AND TABLE_NAME='campaign' 
  AND PARTITION_NAME IN ('p202605', ...); -- 匹配查询时间范围对应的分区
  

  该值是MySQL的统计估算值，误差通常在10%以内，足够大部分分页场景使用
• EXPLAIN快速估算：执行EXPLAIN SELECT COUNT(*) FROM ...，取输出中的rows字段作为近似计数，适合单次查询的快速估算

3. 异步方案（适合需要精确计数但可接受最终一致性的场景） #

彻底隔离计数逻辑与主业务，避免COUNT查询占用主库资源：

• 定时预计算：
  用定时任务（如.NET的Quartz）在低峰期计算不同维度（TenantId、BrandnameId、时间范围）的COUNT值，存储到专门的统计表campaign_statistics中，结构示例：

  CREATE TABLE campaign_statistics (
      TenantId INT,
      BrandnameId INT NULL,
      PartitionName VARCHAR(20),
      TotalCount BIGINT,
      UpdateTime DATETIME,
      PRIMARY KEY(TenantId, BrandnameId, PartitionName)
  );
  

  API查询时直接从该表取数，同时用Redis缓存热门维度的统计结果，进一步提升响应速度

• 实时异步更新：
  用Canal监听MySQL的binlog，当campaign表发生插入、删除、更新（涉及IsDeleted、TenantId等过滤字段）时，异步更新campaign_statistics中的计数，保证数据最终一致性，延迟一般在秒级内

最优方案选择 #

结合你的约束条件，优先级推荐：

1. 如果业务必须精确实时：优先做覆盖索引优化+Keyset分页替换，这是生产环境最安全的实时优化方案，能把COUNT查询耗时降到秒级
2. 如果可以接受近似值：直接用分区统计累加，零成本解决超时问题
3. 如果可以接受最终一致性：用异步预计算+缓存，彻底隔离计数逻辑和主业务，适合高并发场景

内容的提问来源于stack exchange，提问作者Nguyễn Quốc Việt