mysql 优化查询问题

我们公司是做线上广告投放的,我负责用laravel做一些数据统计,现在数据量大了之后查询特别耗时,想问下各位大佬有没有好的查询方式,或者设计新的数据表结构.

业务模型

  • 投手 负责创建广告
  • 广告 线上推广的广告
  • 广告数据 每条广告每天会产生一些数据,像展示次数,点击次数等,每天至少写入5000条数据

简易数据表

广告表(ads)

字段 描述
id 主键
name 广告名称

广告数据表(ad_reports)

字段 描述
id 主键
ad_id 广告id
date 日期 ad_iddate组合唯一
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  ·  10个月前

执行日志

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 |
+----+-------------+------------+------------+-------+------------------+------------------+---------+------+--------+----------+-------------------------------------------+
附言 2  ·  10个月前

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 |
+----+-------------+------------+------------+------+------------------+------+---------+------+---------+----------+------------------------------+
附言 3  ·  10个月前

给聚合字段 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 |
+----+-------------+------------+------------+-------+---------------------------------------------+----------------------------+---------+------+--------+----------+-------------------------------------------+
最佳答案

最终优化-分页

仅查询主键+需要排序的字段,查询所有,使用php进行排序,取分页数量的主键,然后按主键再去查一次,排序,查完使用 with关键字

SELECT
    ads.id AS id,
    SUM( show_cnt ) AS show_cnt 
FROM
    `ads`
    LEFT JOIN `ad_reports` ON `ad_reports`.`ad_id` = `ads`.`ad_id` 
    AND `ad_reports`.`date` BETWEEN '2022-01-01' 
    AND '2022-01-16' 
WHERE
    `ads`.`aweme_id` = '102803713960' 
    AND (
        `ads`.`status` IN ()) 
    AND ( `ad_reports`.`stat_cost` > '0' ) 
    AND `ads`.`marketing_goal` = 'LIVE_PROM_GOODS' 
GROUP BY
    `ads`.`id`
10个月前 评论
讨论数量: 9
yangweijie

加没加索引

10个月前 评论
echopeimeng (楼主) 10个月前

仅5、6万数据不至于20秒,通过 explain 优化下索引,首先索引 datead_id 字段再看效果,show_cnt click_cnt 无需索引。

10个月前 评论
echopeimeng (楼主) 10个月前
php_yt (作者) 10个月前
echopeimeng (楼主) 10个月前
php_yt (作者) 10个月前
php_yt (作者) 10个月前

最终优化-分页

仅查询主键+需要排序的字段,查询所有,使用php进行排序,取分页数量的主键,然后按主键再去查一次,排序,查完使用 with关键字

SELECT
    ads.id AS id,
    SUM( show_cnt ) AS show_cnt 
FROM
    `ads`
    LEFT JOIN `ad_reports` ON `ad_reports`.`ad_id` = `ads`.`ad_id` 
    AND `ad_reports`.`date` BETWEEN '2022-01-01' 
    AND '2022-01-16' 
WHERE
    `ads`.`aweme_id` = '102803713960' 
    AND (
        `ads`.`status` IN ()) 
    AND ( `ad_reports`.`stat_cost` > '0' ) 
    AND `ads`.`marketing_goal` = 'LIVE_PROM_GOODS' 
GROUP BY
    `ads`.`id`
10个月前 评论

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!