从12s到300ms：千万级日志统计SQL调优实战

千万级日志表统计慢查询的问题背景

在用户行为分析的业务场景中，我们维护了一张存储全量埋点日志的InnoDB表，单表数据量突破1200万条，日均新增日志量超过80万条。业务侧每日凌晨需要生成前一日的用户行为统计报表，核心统计维度包含各渠道的访问PV、UV、平均停留时长、转化事件数四个核心指标。上线初期该统计SQL的执行时长仅为2s，随着数据量持续增长，执行时长逐步攀升至12s以上，业务高峰期甚至出现超时报错，导致日度报表无法按时产出。最初的临时解决方案是临时加读写分离从库扛压力，但随着数据量进一步上涨，从库的负载也持续超过80%，必须从SQL本身做根因层面的优化。

调优方案横向对比

我们先后评估了三种主流的大表统计场景优化方案，最终选择了投入产出比最高的基于EXPLAIN执行计划的原生SQL调优路径：

预聚合数仓方案：需要改造现有数据链路，新增日度聚合中间表，开发周期超过7天，且无法支持临时自定义维度的实时统计，灵活性不足；
分库分表方案：需要对现有千万级存量数据做数据迁移，改造成本极高，且会引入分布式事务等额外复杂度；
EXPLAIN诊断+索引优化+SQL改写方案：无需改动数据链路，仅通过现有MySQL能力即可实现性能百倍提升，开发周期仅需2小时，同时保留了全维度统计的灵活性。

基于EXPLAIN的慢SQL根因拆解与实操

原始慢SQL与执行计划诊断

首先我们先展示原始业务表的建表核心结构，该表按日期字段dt做了范围分区，存量数据覆盖近180天的行为日志：

sql

-- 用户行为日志表核心结构
CREATE TABLE `user_behavior_log` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `dt` date NOT NULL COMMENT '日志日期',
  `user_id` varchar(64) NOT NULL COMMENT '用户唯一标识',
  `channel_id` int NOT NULL COMMENT '渠道ID',
  `action_type` int NOT NULL COMMENT '行为类型：1访问/2停留/3转化',
  `duration` int DEFAULT '0' COMMENT '停留时长 单位秒',
  `page_id` int DEFAULT '0' COMMENT '页面ID',
  PRIMARY KEY (`id`),
  KEY `idx_channel_action` (`channel_id`,`action_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户行为日志表';

最初业务同学写的慢SQL如下，该SQL执行时长稳定在12s以上：

sql

-- 原始慢SQL：统计前一日各渠道核心指标
SELECT 
  channel_id,
  COUNT(*) AS pv,
  COUNT(DISTINCT user_id) AS uv,
  AVG(IF(action_type=2, duration, 0)) AS avg_duration,
  SUM(IF(action_type=3, 1, 0)) AS convert_cnt
FROM user_behavior_log 
WHERE dt = '2024-06-01'
GROUP BY channel_id;

我们使用EXPLAIN命令拿到该SQL的执行计划，核心字段诊断结果如下：

type字段显示为ALL，代表全表扫描，没有命中任何可用索引；
rows字段估值为1200万，代表优化器预估需要扫描全表所有数据行；
Extra字段显示为Using where，代表需要在服务层过滤所有符合dt条件的数据，没有用到索引做过滤。根因拆解后我们发现，之前创建的联合索引idx_channel_action完全没有包含过滤条件dt字段，MySQL优化器判断使用该索引的代价比全表扫描更高，直接放弃了索引选择，导致每次统计都要扫描全表千万级数据。

索引优化与SQL改写落地

针对根因我们做了两处核心优化：第一是按照最左匹配原则新建覆盖索引，把过滤性最强的等值过滤字段dt放在索引最左侧，后续依次放分组字段channel_id、聚合条件用到的action_type、duration、user_id，确保整个查询可以直接通过索引完成，不需要回表访问主键索引：

sql

-- 新建覆盖联合索引 完全匹配查询的过滤、分组、聚合逻辑
CREATE INDEX idx_dt_channel_action ON user_behavior_log(dt, channel_id, action_type, duration, user_id);

第二是对原始SQL做轻量化改写，去掉不必要的逻辑判断，把COUNT DISTINCT的去重逻辑放到索引有序的场景下优化，避免随机排序开销：

sql

-- 优化后的统计SQL 执行时长仅为287ms
SELECT 
  channel_id,
  COUNT(*) AS pv,
  COUNT(DISTINCT user_id) AS uv,
  AVG(CASE WHEN action_type=2 THEN duration ELSE 0 END) AS avg_duration,
  SUM(CASE WHEN action_type=3 THEN 1 ELSE 0 END) AS convert_cnt
FROM user_behavior_log 
WHERE dt = '2024-06-01'
GROUP BY channel_id;

再次使用EXPLAIN查看执行计划，核心指标已经完全符合预期：type升级为range，rows字段估值仅为82万，Extra字段显示为Using index，代表整个查询完全命中覆盖索引，不需要回表扫描任何主键数据，性能直接提升40倍以上。

调优避坑总结

我们在本次调优过程中踩过多个典型的MySQL统计SQL优化的坑，整理出3条可直接复用的实践注意事项：

联合索引顺序不可随意排布：很多开发者会习惯性把分组字段放在索引最左侧，反而把日期等过滤字段放到索引末尾，导致索引失效。本次优化前我们曾错误创建过idx(channel_id,action_type,dt)索引，执行计划依旧走全表扫描，修正方法是遵循「过滤字段优先、分组字段次之、聚合字段最后」的索引顺序规则，确保最左前缀匹配可以快速过滤掉99%的无关数据。
EXPLAIN的rows估值不准时要刷新统计信息：我们初期测试时发现EXPLAIN显示rows仅为1000行，但实际执行还是扫全表，排查后发现是表的统计信息超过3个月没有更新，优化器拿到的行数估值严重失真，直接选择了错误的执行计划。修正方法是执行ANALYZE TABLE user_behavior_log;命令，刷新InnoDB的统计信息，确保EXPLAIN的诊断结果和实际执行路径完全一致。
聚合统计场景禁止添加无意义的ORDER BY字段：不少业务同学写完统计SQL后会顺手加上ORDER BY pv DESC排序逻辑，本次测试时我们发现加了该逻辑后执行时长直接从300ms回升到3s，EXPLAIN的Extra字段出现Using filesort，引发内存临时表排序开销。修正方法是如果业务侧没有明确要求排序，直接删除多余的ORDER BY逻辑，如果确实需要排序，把排序字段放到覆盖索引的末尾，利用索引的有序性直接完成排序，避免filesort开销。经过全量压测验证，优化后的SQL在1200万数据量下，单条统计耗时稳定在300ms以内，完全满足日度报表的产出时效要求，同时支持临时自定义任意日期的维度统计，没有引入任何额外的架构复杂度。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

从12s到300ms：千万级日志统计SQL调优实战 ​

千万级日志表统计慢查询的问题背景 ​

调优方案横向对比 ​

基于EXPLAIN的慢SQL根因拆解与实操 ​

原始慢SQL与执行计划诊断 ​

索引优化与SQL改写落地 ​

调优避坑总结 ​