mysql的界面怎么优化_MySQL 8.0窗口函数优化SQL一例

原标题：MySQL 8.0窗口函数优化SQL一例

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 limit20;

当前 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 |

+-----------------------+--------+

2. SQL优化

上面的SQL主要瓶颈在于嵌套子查询，去掉子查询，即便是全表扫描也还是很快的。

[root@yejr.run]> select ...

...

20 rows inset(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( casewhen ts_min>= date_sub(now, interval 7 day) thents_cnt end ) as 1W,

ifnull(sum( casewhen ts_min>= date_sub(now, interval 14 day)

and ts_max<= date_sub(now, interval 7 day) thents_cnt end ) over(partition by hostname_max),0) 2W

from global_query_review_history t2

wherets_min>= date_sub(now, interval 14 day)

group by hostname_max, db_max

order by 1W desc limit20;

再看下执行计划：

*************************** 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 inset(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编程开发与优化」课程 返回搜狐，查看更多

责任编辑：