1. 问题描述
最近在折腾把所有mysql slow query log写入到数据库中，再集中展示，向业务部门开放，也方便业务部门的同学自行查看并优化各自业务内的慢SQL。增加了定期生成报表的功能，统计最近1~2周内的慢查询数量变化情况，给业务方同学更直观的数据对比，了解最近这段时间的慢查询数量变化情况，是多了还是少了。于是有了下面这一坨SQL：
当前 global_query_review_history 表约有2.5万条记录，这条SQL耗时 1.16秒，显然太慢了。下面是SQL执行计划：
*************************** 1. row ***************************
id: 1select_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 :)
</div>