MySQL 8以下版本两张表用户出现频次统计查询优化问题
MySQL旧版本(8.0前)频次统计优化方案
问题原因分析
- 原SQL存在多余的嵌套括号,会导致执行计划解析异常,执行效率低下
- 未给
user字段添加索引,分组、关联操作均为全表扫描,数据量大时性能极差 user是MySQL关键字,未用反引号包裹可能引发语法错误
第一步:优先添加索引
给两张表的user字段添加普通索引,可实现分组操作的索引覆盖扫描,性能提升最明显:
ALTER TABLE table1 ADD INDEX idx_user(`user`); ALTER TABLE table2 ADD INDEX idx_user(`user`);
第二步:优化后的查询语句
修正语法错误,去掉冗余嵌套,直接关联两个分组结果即可:
SELECT COUNT(*) AS n_users, t1.countt1 AS count_t1, t2.countt2 AS count_t2 FROM (SELECT `user`, COUNT(*) AS countt1 FROM table1 GROUP BY `user`) t1 INNER JOIN (SELECT `user`, COUNT(*) AS countt2 FROM table2 GROUP BY `user`) t2 ON t1.`user` = t2.`user` GROUP BY count_t1, count_t2;
如果你的场景需要包含仅在单表出现的用户,可以把INNER JOIN换成LEFT JOIN+RIGHT JOIN合并的方式实现全外连接,符合8.0前版本的语法要求。
后续区间分组实现
按1-10、11-20这类区间分组时,调整分组逻辑即可,示例如下:
SELECT COUNT(*) AS n_users, -- 生成t1的区间标识,可根据需求调整步长 CONCAT(FLOOR((count_t1 - 1)/10)*10 + 1, '-', FLOOR((count_t1 - 1)/10)*10 + 10) AS t1_interval, -- 生成t2的区间标识 CONCAT(FLOOR((count_t2 - 1)/10)*10 + 1, '-', FLOOR((count_t2 - 1)/10)*10 + 10) AS t2_interval FROM ( SELECT t1.`user`, t1.countt1 AS count_t1, t2.countt2 AS count_t2 FROM (SELECT `user`, COUNT(*) AS countt1 FROM table1 GROUP BY `user`) t1 INNER JOIN (SELECT `user`, COUNT(*) AS countt2 FROM table2 GROUP BY `user`) t2 ON t1.`user` = t2.`user` ) AS user_freq GROUP BY t1_interval, t2_interval;
内容的提问来源于stack exchange,提问作者dc2
相关产品推荐
相关产品推荐

