MySQL 8.0窗口函数优化SQL一例

悠悠 2022-12-31 13:24 117阅读 0赞

> 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: ALL
    possible_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: ref
    possible_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) 2W
    from global_query_review_history t2
     where ts_min>= date_sub(now(), interval 14 day)
    group by hostname_max, db_max
    order by 1W desc limit 20;

再看下执行计划：

*************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: t2
       partitions: NULL
             type: ALL
    possible_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 :)

## 延伸阅读 ##

*  [MySQL 8.0中对EXISTS、NOT EXISTS的持续优化][MySQL 8.0_EXISTS_NOT EXISTS]
 *  [MySQL 8 Anti-Join 几点总结][MySQL 8 Anti-Join]
 *  [SQL优化很难怎么办？给你一个简单暴力的办法][SQL]
 *  [SQL 优化案例一则][SQL 1]
 *  [MySQL Join竟然可以这么玩？根据条件进行复制][MySQL Join]

扫码关注松华老师「深入SQL编程开发与优化」课程

![format_png][]

或者点击文末“**阅读原文**”直达

[MySQL 8.0_EXISTS_NOT EXISTS]: https://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&idx=1&mid=2653933591&scene=21&sn=ae0b2dd707e26a2487bef13fe80f6ae2#wechat_redirect
[MySQL 8 Anti-Join]: https://mp.weixin.qq.com/s?__biz=Mzg5NDI2NjczOA%3D%3D&idx=1&mid=2247483723&scene=21&sn=187229e3aa7f5bf3e17a7a65d5fe36ad#wechat_redirect
[SQL]: https://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&idx=1&mid=2653932953&scene=21&sn=9001b247b1eabec21a8af930e6b6e84c#wechat_redirect
[SQL 1]: https://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&idx=1&mid=2653932922&scene=21&sn=e4666a9e4e9d04309cac0f8931ae4348#wechat_redirect
[MySQL Join]: https://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&idx=1&mid=2653932917&scene=21&sn=c6330f6509c137679ea66961be287f17#wechat_redirect
[format_png]: /images/20221120/37896c6b0f604022bc75c0928e4090ee.png