MySQL 8.0窗口函数优化SQL一例

Wesley13
• 阅读 695

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编程开发与优化」课程

MySQL 8.0窗口函数优化SQL一例

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

本文分享自微信公众号 - 老叶茶馆(iMySQL_WX)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
待兔 待兔
4个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Stella981 Stella981
3年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Stella981 Stella981
3年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置1、virsheditcentos7找到“memory”和“vcpu”标签,将<namecentos7</name<uuid2220a6d1a36a4fbb8523e078b3dfe795</uuid
Easter79 Easter79
3年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
Python进阶者 Python进阶者
10个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这