MySQL基于同表24小时子查询的全表统计慢查询优化问题

核心性能瓶颈 #

你当前的慢查询问题主要来自两点：

1. 两个业务查询重复执行了相同的24小时活跃IP子查询，且MySQL对IN子查询的处理需要为每个匹配到的IP做索引查找，200万行数据的聚合、排序开销被重复计算了两次
2. 如果你的实际需求是仅统计24小时内的访问数据，现有查询逻辑存在冗余，不需要嵌套子查询，直接限制主查询的时间范围即可，会减少大量历史数据扫描开销

优化方案 #

• 优先做逻辑简化：如果只需要统计24小时内的访问数据
  直接去掉子查询，在主查询加时间范围限制，查询速度会提升10倍以上，改写后示例：

  -- 统计24小时内的活跃service
  SELECT service, COUNT(*) AS total 
  FROM `archive` 
  WHERE date > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 24 HOUR)
  GROUP BY service 
  HAVING total > 1
  ORDER BY total DESC, service ASC 
  LIMIT 10
  

  IP统计的查询同理改写即可，不需要嵌套子查询。

• 如果确实需要统计24小时活跃IP的全量历史访问数据

  1. 先单独执行一次24小时IP的子查询，把结果存入PHP数组（子查询仅需0.03秒，结果最多几万条，内存占用极低），后续两个查询直接用拼接好的IP列表做IN匹配，避免重复执行子查询的开销。你之前担心子查询关闭后无法获取结果是误解，完全可以先单独拉取IP列表复用。
  2. 把IN子查询改写为JOIN实现，同表也可以做JOIN，MySQL优化器对JOIN的处理效率远高于嵌套IN子查询，示例：

  SELECT a.service, COUNT(*) AS total 
  FROM archive a
  INNER JOIN (
      SELECT DISTINCT ip FROM archive WHERE date > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 24 HOUR)
  ) t ON a.ip = t.ip
  GROUP BY a.service 
  HAVING total > 1
  ORDER BY total DESC, a.service ASC 
  LIMIT 10
  

  3. 调整MySQL配置：将tmp_table_size和max_heap_table_size调整为64M~128M，确保聚合产生的临时表可以放在内存中，避免写入磁盘带来的额外开销。
  4. 清理冗余索引：你当前创建了大量重复的前缀索引，单独的service、ip、date单字段索引可以直接删除，现有联合索引已经可以覆盖这些单字段的查询需求，能减少40%以上的索引体积，同时提升写入性能。

• 缓存优化
  这类统计数据对实时性要求不高，可以用Redis或者本地文件缓存查询结果，缓存有效期设置1~5分钟即可，用户访问时直接返回缓存结果，页面加载速度可以降到100ms以内。

• 长期优化
  可以新增离线汇总表，用定时任务每小时/每天计算一次每个IP、service的总访问次数，查询时直接读取汇总表，完全避免实时扫描200万行数据的开销。

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