mysql 优化查询问题
我们公司是做线上广告投放的,我负责用laravel做一些数据统计,现在数据量大了之后查询特别耗时,想问下各位大佬有没有好的查询方式,或者设计新的数据表结构.
业务模型
- 投手 负责创建广告
- 广告 线上推广的广告
- 广告数据 每条广告每天会产生一些数据,像展示次数,点击次数等,每天至少写入5000条数据
简易数据表
广告表(ads)
| 字段 | 描述 |
|---|---|
| id | 主键 |
| name | 广告名称 |
广告数据表(ad_reports)
| 字段 | 描述 |
|---|---|
| id | 主键 |
| ad_id | 广告id |
| date | 日期 ad_id和date组合唯一 |
| show_cnt | 展示次数 |
| click_cnt | 点击次数 |
| stat_cost | 费用 |
| ctr | 点击率 |
业务需求
由于 广告 太多,需要帮 投手 筛选出优秀的有潜力的 广告.
比如要查看某段时间内的广告数据,并且需要按 展示次数 或 点击次数 各种效果数据做排序
我的方法
1. group by + order by
在 ad_reports 表有五六万条数据时,仅查询这三个字段需要二十秒以上,服务器是4核+8G
SELECT
ad_id,
SUM(show_cnt) as show_cnt,
SUM(click_cnt) as click_cnt
FROM
ad_reports
WHERE
`date` BETWEEN '2022-01-01' AND '2022-01-11'
GROUP BY
ad_id
ORDER BY
show_cnt DESC
LIMIT 20执行日志
1.group by 后仅查询 ad_id
这种情况下可以使用到索引
MySQL [mysql]> explain SELECT ad_id FROM `ad_reports` WHERE date BETWEEN '2021-12-01' AND '2021-12-28' GROUP BY ad_id;
+----+-------------+------------+------------+-------+------------------+------------------+---------+------+--------+----------+-------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+------------------+------------------+---------+------+--------+----------+-------------------------------------------+
| 1 | SIMPLE | ad_reports | NULL | range | date_ad_id_index | date_ad_id_index | 3 | NULL | 409398 | 100.00 | Using where; Using index; Using temporary |
+----+-------------+------------+------------+-------+------------------+------------------+---------+------+--------+----------+-------------------------------------------+
group by 后增加聚合数据的查询
增加聚合之后,索引失效了
MySQL [mysq]> explain SELECT ad_id,SUM(stat_cost) as cost FROM `ad_reports` WHERE date BETWEEN '2021-12-01' AND '2021-12-28' GROUP BY ad_id;
+----+-------------+------------+------------+------+------------------+------+---------+------+---------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+------------------+------+---------+------+---------+----------+------------------------------+
| 1 | SIMPLE | ad_reports | NULL | ALL | date_ad_id_index | NULL | NULL | NULL | 1226539 | 33.38 | Using where; Using temporary |
+----+-------------+------------+------------+------+------------------+------+---------+------+---------+----------+------------------------------+
给聚合字段 stat_cost 也添加索引
这种应该是最优的索引了,
话说为什么在命令行直接运行 sql, 和使用 Navicat 工具运行 sql 时间差的好多
MySQL [xhlmedia]> explain SELECT ad_id,SUM(stat_cost) FROM `ad_reports` WHERE date BETWEEN '2021-12-01' AND '2021-12-28' GROUP BY ad_id;
+----+-------------+------------+------------+-------+---------------------------------------------+----------------------------+---------+------+--------+----------+-------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------------------------------------+----------------------------+---------+------+--------+----------+-------------------------------------------+
| 1 | SIMPLE | ad_reports | NULL | range | ad_id_date_index,date_ad_id_stat_cost_index | date_ad_id_stat_cost_index | 3 | NULL | 447802 | 100.00 | Using where; Using index; Using temporary |
+----+-------------+------------+------------+-------+---------------------------------------------+----------------------------+---------+------+--------+----------+-------------------------------------------+
关于 LearnKu
粤公网安备 44030502004330号
最终优化-分页
仅查询主键+需要排序的字段,查询所有,使用php进行排序,取分页数量的主键,然后按主键再去查一次,排序,查完使用
with关键字