如何加速MySQL时间范围查询？3000万记录表distinct计数慢问题

问题根源 #

单独给create_time建索引后变慢，是因为MySQL使用该索引时，需要通过索引条目回表查询owner_id，3000万条数据的随机IO开销远大于全表扫描的顺序IO，导致性能不升反降。

具体解决办法 #

• 创建覆盖联合索引
  建立包含查询所需字段的联合索引，彻底避免回表操作：

  CREATE INDEX idx_create_time_owner_id ON code_orange_checkpointrecord(create_time, owner_id);
  

  该索引按create_time排序，同时内嵌owner_id，MySQL可以直接在索引中完成时间过滤和去重计数，无需访问主表，大幅降低IO开销。

• 优化时间范围写法
  将BETWEEN替换为>=和<，避免遗漏毫秒级数据，同时保持索引兼容性：

  SELECT COUNT(DISTINCT owner_id) 
  FROM code_orange_checkpointrecord 
  WHERE create_time >= '2023-01-01' AND create_time < '2023-01-31';
  

• 近似计数（业务允许时）
  若业务不需要绝对精确的计数，MySQL 8.0+支持的APPROX_COUNT_DISTINCT函数能大幅提升速度，误差通常在1%以内：

  SELECT APPROX_COUNT_DISTINCT(owner_id) 
  FROM code_orange_checkpointrecord 
  WHERE create_time >= '2023-01-01' AND create_time < '2023-01-31';
  

• 时间分区优化
  针对时间维度的查询，可对表按create_time做RANGE分区（比如按月分区），查询指定时间范围时仅扫描对应分区，减少数据扫描量：

  ALTER TABLE code_orange_checkpointrecord 
  PARTITION BY RANGE (TO_DAYS(create_time)) (
      PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
      PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
      -- 按需添加其他分区
  );
  

• 验证执行计划
  用EXPLAIN查看查询是否使用了正确的索引，确认type为range，key为创建的联合索引，Extra包含Using index（覆盖索引标志）：

  EXPLAIN SELECT COUNT(DISTINCT owner_id) 
  FROM code_orange_checkpointrecord 
  WHERE create_time >= '2023-01-01' AND create_time < '2023-01-31';
  

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