8.0的窗口函数真香
1. 问题描述
最近在折腾把所有mysql slow query log写入到数据库中,再集中展示,向业务部门开放,也方便业务部门的同学自行查看并优化各自业务内的慢SQL。增加了定期生成报表的功能,统计最近1~2周内的慢查询数量变化情况,给业务方同学更直观的数据对比,了解最近这段时间的慢查询数量变化情况,是多了还是少了。于是有了下面这一坨SQL:
select hostname_max , db_max, sum(ts_cnt) as 1W(select ifnull(sum(t1.ts_cnt),0) as ts_cnt from global_query_review_history t1 where t1.hostname_max=t2.hostname_max and t1.ts_min>= date_sub(now(), interval 14 day) and t1.ts_max<= date_sub(now(), interval 7 day)) AS 2W from global_query_review_history t2 where ts_min>= date_sub(now(), interval 7 day) group by hostname_max, db_max order by 1W desc limit 20;
当前 global_query_review_history 表约有2.5万条记录,这条SQL耗时 1.16秒,显然太慢了。下面是SQL执行计划:
*************************** 1. row *************************** id: 1 select_type: PRIMARY table: t2 partitions: NULL type: ALLpossible_keys: ts_min key: NULL key_len: NULL ref: NULL rows: 25198 filtered: 41.09 Extra: Using where; Using temporary; Using filesort*************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: t1 partitions: NULL type: refpossible_keys: hostname_max,ts_min key: hostname_max key_len: 258 ref: func rows: 20 filtered: 14.90 Extra: Using where
可以看到需要进行一次子查询(无法自动优化成JOIN)。
SQL执行后的status统计值:
+-----------------------+--------+| Variable_name | Value |+-----------------------+--------+| Handler_read_first | 0 || Handler_read_key | 17328 || Handler_read_last | 0 || Handler_read_next | 809121 || Handler_read_prev | 0 || Handler_read_rnd | 0 || Handler_read_rnd_next | 25380 |+-----------------------+--------+
可以看到除了有全表扫描外,还要根据索引的多次逐行扫描(Handler_read_next = 809121,子查询引起的)。
2. SQL优化
上面的SQL主要瓶颈在于嵌套子查询,去掉子查询,即便是全表扫描也还是很快的。
[root@yejr.run]> select ......20 rows in set (0.08 sec)[root@yejr.run]> show status like 'handler%read%';+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| Handler_read_first | 0 || Handler_read_key | 16910 || Handler_read_last | 0 || Handler_read_next | 0 || Handler_read_prev | 0 || Handler_read_rnd | 0 || Handler_read_rnd_next | 25380 |+-----------------------+-------+
SQL优化有困难自然先想到了松华老师,在得知我用的MySQL 8.0之后,他帮忙给改造成了基于窗口函数的写法:
select hostname_max , db_max,sum( case when ts_min>= date_sub(now(), interval 7 day) then ts_cnt end ) as 1W,ifnull(sum(case when ts_min>= date_sub(now(), interval 14 day) and ts_max<= date_sub(now(), interval 7 day) then ts_cnt end ) over(partition by hostname_max),0) 2Wfrom global_query_review_history t2 where ts_min>= date_sub(now(), interval 14 day)group by hostname_max, db_maxorder by 1W desc limit 20;
再看下执行计划:
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t2 partitions: NULL type: ALLpossible_keys: ts_min key: NULL key_len: NULL ref: NULL rows: 25198 filtered: 44.88 Extra: Using where; Using temporary; Using filesort
新SQL比较取巧,只需要读取一次数据,利用窗口函数直接计算出需要的统计值。虽然有可用索引,但因为要扫描的数据量比较大,所以最后还是变成全表扫描。新SQL耗时和status统计值见下:
20 rows in set (0.08 sec)[root@yejr.run]> show status like 'handler%read%';+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| Handler_read_first | 0 || Handler_read_key | 24396 || Handler_read_last | 0 || Handler_read_next | 0 || Handler_read_prev | 0 || Handler_read_rnd | 886 || Handler_read_rnd_next | 26703 |+-----------------------+-------+
和之前那个SQL差距太大了,优化效果杠杠滴。
全文完。
Enjoy MySQL 8.0 :)
延伸阅读
扫码关注松华老师「深入SQL编程开发与优化」课程
或者点击文末“阅读原文”直达
本文分享自微信公众号 - 老叶茶馆(iMySQL_WX)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。