MySQL 8.0窗口函数优化SQL一例-阿里云开发者社区

开发者社区> -技术小能手-> 正文

MySQL 8.0窗口函数优化SQL一例

简介: MySQL 8.0窗口函数优化SQL一例
+关注继续查看

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 :)

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
10081 0
PolarDB-X 1.0-SQL 手册-函数-函数
DRDS 支持的函数分为日期时间函数、字符串函数、转换函数、聚合函数、数学函数、比较函数、位函数、控制流程函数、信息函数、加密和压缩函数以及其他函数;JSON 函数和地理信息函数的下推执行。
16 0
MySQL8.0 · 优化器新特性 · Cost Model, 直方图及优化器开销优化
MySQL当前已经发布到MySQL8.0版本,在新的版本中,可以看到MySQL之前被人诟病的优化器部分做了很多的改动,由于笔者之前的工作环境是5.6,最近切换到最新的8.0版本,本文涵盖了一些本人感兴趣的和优化器相关的部分,主要包括MySQL5.7的cost model以及MySQL8.0的直方图功能。
2421 0
对pos搜索函数的研究以及优化思路···
代码摘自delphi的Pos函数。。。总的来说,若我理解无误的话,该函数才用的搜索机制并不是非常高明。
603 0
PostgreSQL sharding : citus 系列3 - 窗口函数调用限制 与 破解之法(套用gpdb执行树,分步执行)
标签 PostgreSQL , citus , 窗口函数 背景 窗口函数是分析场景常用的,目前(citus 7.5)仅支持两种场景使用window函数, 1、partition by 必须是分布键。
874 0
MySQL函数
原文链接:http://www.cnblogs.com/kissdodog/p/4168721.html
4766 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
13885 0
SQL SERVER CHARINDEX函数
        CHARINDEX函数常常用来在一段字符中搜索字符或者字符串。如果被搜索的字符中包含有要搜索的字符,那么这个函数返回一个非零的整数,这个整数是要搜索的字符在被搜索的字符中的开始位数。即CHARINDEX函数返回字符或者字符串在另一个字符串中的起始位置。         CHARINDEX函数调用方法如下: CHARINDEX ( expression1 , expres
886 0
1727
文章
0
问答
来源圈子
更多
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载