GreatSQL 优化技巧:将 MINUS 改写为标量子查询

GreatSQL 优化技巧:将 MINUS 改写为标量子查询

前言

minus 指令运用在两个 SQL 语句上,取两个语句查询结果集的差集。它先找出第一个 SQL 所产生的结果,然后看这些结果有没有在第二个 SQL 的结果中,如果在,那这些数据就被去除,不会在最后的结果中出现,第二个 SQL 结果集比第一个SQL结果集多的数据也会被抛弃。 这两天的优化工作中遇到这样一种案例,第一个SQL语句结果集很小,第二个SQL语句结果集很大,这种情况下我们怎么来优化处理呢?

实验

创建测试表

CREATE TABLE t1(id int primary key auto_increment,
subscriber_id decimal(20, 0) not null,
member_num varchar(20) not null,
effectdate datetime,
expirydate datetime,
create_date datetime,
key idx_subscriber(subscriber_id));

创建存储过程,向测试插入50万数据。(实际生产案例中表中数据有几千万)

注意下面的存储过程中,是GreatSQL在Oracle模式下创建的,GreatSQL实现了大量的Oracle语法兼容,比如下面存储过程中遇到的日期加减,add_months函数,while loop循环等,数据库由Oracle向GreatSQL迁移时,会节省很多代码改造工作。

set sql_mode=oracle;
delimiter //
create or replace procedure p1() as
 p1 int :=1;
 n1 int;
 d1 datetime;
begin
   while p1<=500000 loop
       n1:=round(rand()*500000);
       d1:=to_date('2016-01-01','yyyy-mm-dd') + round(rand()*3000);
       insert into t1(subscriber_id,member_num,effectdate,expirydate,create_date) values(n1,concat('m_',n1),last_day(d1)+1,add_months(last_day(d1)+1,100),d1);
       set p1=p1+1;
   end loop;
end;
//
delimiter ;

这个表create_date列的数据是从2016年1月到2024年3月的数据,使用了随机值,保证每个月的数据量相近,subscriber_id也是随机值生成的,选择性很好,这个模型数据与生产环境差不多。

执行下面这个SQL语句:

SELECT DISTINCT subscriber_id, member_num
  FROM t1 
 WHERE create_date >= '2024-02-01'
   AND create_date < '2024-03-01'
   AND to_char(effectdate, 'yyyymm') > '202402'
minus
SELECT DISTINCT subscriber_id, member_num
  FROM t1 
 WHERE 202402 BETWEEN to_char(effectdate, 'yyyymm') AND
       to_char(expirydate, 'yyyymm');

这条SQL是根据生产环境使用的语句简化而来的,只为突出本文要说明的知识点。

此SQL的执行计划如下:

greatsql> explain analyze
    -> select distinct subscriber_id, member_num
    ->   from t1
    ->  where create_date >= '2024-02-01'
    ->    and create_date < '2024-03-01'
    ->    and to_char(effectdate, 'yyyymm') > '202402'
    -> minus
    -> select distinct subscriber_id, member_num
    ->   from t1
    ->  where 202402 between to_char(effectdate, 'yyyymm') and
    ->        to_char(expirydate, 'yyyymm')\G
*************************** 1. row ***************************
EXPLAIN: -> Table scan on <except temporary>  (cost=168492.31..169186.99 rows=55375) (actual time=2420.123..2420.896 rows=1758 loops=1)
    -> Except materialize with deduplication  (cost=168492.30..168492.30 rows=55375) (actual time=2420.121..2420.121 rows=4855 loops=1)
        -> Table scan on <temporary>  (cost=55858.24..56552.91 rows=55375) (actual time=221.965..223.384 rows=4855 loops=1)
            -> Temporary table with deduplication  (cost=55858.23..55858.23 rows=55375) (actual time=221.962..221.962 rows=4855 loops=1)
                -> Filter: ((t1.create_date >= TIMESTAMP'2024-02-01 00:00:00') and (t1.create_date < TIMESTAMP'2024-03-01 00:00:00') and (to_char(t1.effectdate,'yyyymm') > '202402'))  (cost=50320.70 rows=55375) (actual time=0.118..217.497 rows=4875 loops=1)
                    -> Table scan on t1  (cost=50320.70 rows=498477) (actual time=0.084..179.826 rows=500000 loops=1)
        -> Table scan on <temporary>  (cost=100168.41..106401.86 rows=498477) (actual time=1520.965..1571.682 rows=307431 loops=1)
            -> Temporary table with deduplication  (cost=100168.40..100168.40 rows=498477) (actual time=1520.963..1520.963 rows=307431 loops=1)
                -> Filter: (202402 between to_char(t1.effectdate,'yyyymm') and to_char(t1.expirydate,'yyyymm'))  (cost=50320.70 rows=498477) (actual time=0.123..934.617 rows=492082 loops=1)
                    -> Table scan on t1  (cost=50320.70 rows=498477) (actual time=0.104..716.919 rows=500000 loops=1)

1 row in set (2.47 sec)

从执行计划看出,SQL总体耗时2.47s。 第一部分的查询结果集有4855条,耗时221.962ms,第二部分的查询结果集有307431条,耗时1571.682ms。

优化分析:

首先第一部分create_date加上索引会提升查询效率,因为只需要查询一个月的数据,而此SQL耗时最多的是第二部分,重在第二部分的优化处理。

第二部分查询结果集在做minus运算时大部分记录都是要被抛弃的,查询出来再被抛弃相当于做了无用功,而SQL优化的核心思想就是在于减少IO,那我们要做的就是想办法省去第二部分SQL的全面查询,只需要验证第一部分的查询结果集是否在第二部分查询结果中存在就好了。

那如何验证呢?

把第一部分select的列值传给第二部分作为where条件去查找,只要能查到,无论几条都算在第二部分存在,这部分数据就要被去除,查不到就是在第二部分不存在,数据保留在最终结果集。根据这个逻辑我想到了标量子查询的妙用。

标量子查询改写参考:

select distinct subscriber_id, member_num
  from (select a.subscriber_id,
               a.member_num,
               (select count(*) cnt
                  from t1 b
                 where a.subscriber_id = b.subscriber_id
                   and a.member_num = b.member_num
                   and 202402 between to_char(effectdate, 'yyyymm') and
                       to_char(expirydate, 'yyyymm')) as cnt
          from t1 a
         where create_date >= '2024-02-01'
           and create_date < '2024-03-01'
           and to_char(effectdate, 'yyyymm') > '202402')
 where cnt = 0

改后SQL的执行计划如下:

greatsql> explain analyze
    -> select distinct subscriber_id, member_num
    ->   from (select a.subscriber_id,
    ->                a.member_num,
    ->                (select count(*) cnt
    ->                   from t1 b
    ->                  where a.subscriber_id = b.subscriber_id
    ->                    and a.member_num = b.member_num
    ->                    and 202402 between to_char(effectdate, 'yyyymm') and
    ->                        to_char(expirydate, 'yyyymm')) as cnt
    ->           from t1 a
    ->          where create_date >= '2024-02-01'
    ->            and create_date < '2024-03-01'
    ->            and to_char(effectdate, 'yyyymm') > '202402')
    ->  where cnt = 0\G
*************************** 1. row ***************************
EXPLAIN: -> Table scan on <temporary>  (cost=3172.53..3235.95 rows=4875) (actual time=168.555..168.775 rows=1758 loops=1)
    -> Temporary table with deduplication  (cost=3172.51..3172.51 rows=4875) (actual time=168.553..168.553 rows=1758 loops=1)
        -> Index lookup on alias_temp_-1556603461854822391 using <auto_key0> (cnt=0)  (cost=2681.86..2685.01 rows=10) (actual time=166.656..167.178 rows=1765 loops=1)
            -> Materialize  (cost=2681.51..2681.51 rows=4875) (actual time=166.649..166.649 rows=4875 loops=1)
                -> Filter: (to_char(a.effectdate,'yyyymm') > '202402')  (cost=2194.01 rows=4875) (actual time=0.380..45.477 rows=4875 loops=1)
                    -> Index range scan on a using idx_creatdate over ('2024-02-01 00:00:00' <= create_date < '2024-03-01 00:00:00'), with index condition: ((a.create_date >= TIMESTAMP'2024-02-01 00:00:00') and (a.create_date < TIMESTAMP'2024-03-01 00:00:00'))  (cost=2194.01 rows=4875) (actual time=0.344..43.143 rows=4875 loops=1)
                -> Select #3 (subquery in projection; dependent)
                    -> Aggregate: count(0)  (cost=0.42 rows=1) (actual time=0.022..0.022 rows=1 loops=4875)
                        -> Filter: ((a.member_num = b.member_num) and (202402 between to_char(b.effectdate,'yyyymm') and to_char(b.expirydate,'yyyymm')))  (cost=0.40 rows=0.2) (actual time=0.019..0.021 rows=1 loops=4875)
                            -> Index lookup on b using idx_subscriber (subscriber_id=a.subscriber_id)  (cost=0.40 rows=2) (actual time=0.018..0.019 rows=2 loops=4875)

1 row in set, 2 warnings (0.26 sec)

从执行计划可以看出,子查询执行次数依赖于主查询,执行了4875次,因为subscriber_id列选择性很好,所以每次查询效率很高。SQL总体耗时0.26秒,而原SQL耗时2.47s,性能提升了将近10倍。在实际生产案例中第二部分结果集有5000万左右,第一部分结果集只有几十条,SQL执行半天都跑不出结果,改造后几乎秒出。

提醒一点,注意NULL值比较,当select 列表中的部分列存在NULL值时就不能直接用等号(=)关联来判断了,得用is NULL来判断,本案例不涉及此问题,语句是否等价有时需要结合业务,具体情况具体分析。

结论:

本文提供了一种minus语句的优化方法,将minus转化为标量子查询表达,这种优化方式适用于第一部分查询结果集比较小,查询的列比较少的情况,且要结合业务判读是否需要对NULL值进行判断。优化时一般避免使用标量子查询,因为标量子查询会构造天然的嵌套循环连接,但也并不是说标量子查询一定不可用,还是要从根儿上考虑,优化核心思想,减少IO是要点。

Enjoy GreatSQL :)

关于 GreatSQL

GreatSQL是适用于金融级应用的国内自主开源数据库,具备高性能、高可靠、高易用性、高安全等多个核心特性,可以作为MySQL或Percona Server的可选替换,用于线上生产环境,且完全免费并兼容MySQL或Percona Server。

相关链接: GreatSQL社区 Gitee GitHub Bilibili

GreatSQL社区:

image

社区有奖建议反馈: https://greatsql.cn/thread-54-1-1.html

社区博客有奖征稿详情: https://greatsql.cn/thread-100-1-1.html

(对文章有疑问或者有独到见解都可以去社区官网提出或分享哦~)

技术交流群:

微信&QQ群:

QQ群:533341697

微信群:添加GreatSQL社区助手(微信号:wanlidbc )好友,待社区助手拉您进群。

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/506641.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

2024年山东临沂教育人才引进报名流程

2024年山东临沂教育人才引进报名流程

表单全选反选(前端)

1.Html和JavaScript <table><tr><th class"allCheck"><input type"checkbox" name"" id"checkAll"> <span class"all">全选</span></th><th>商品</th><th>商…

Hive函数笔试题(简单)

第1题 有如下的用户访问数据 userId visitDate visitCount u01 2017/1/21 5 u02 2017/1/23 6 u03 2017/1/22 8 u04 2017/1/20 3 u01 2017/1/23 6 u01 2017/2/21 8 u02 2017/1/23 6 u01 2017/2/22 4 要求使用SQL统计出每个用户的累积访问次数&…

Qt中继承QCheckBox的类结合QTableWidget实现多选并且每个多选的id都不一样

1.相关描述 继承QCheckBox的类MyCheckBox&#xff0c;利用QTableWidget的setCellWidget方式添加MyCheckBox类的对象 2.相关页面 3.相关代码 mycheckbox.h #ifndef MYCHECKBOX_H #define MYCHECKBOX_H#include <QCheckBox> #include <QObject>class MyCheckBox : pu…

DSSS-UQPSK学习笔记

文章目录 非平衡四相键控-直接序列扩频&#xff08;UQPSK-DSSS&#xff09;信号因其能同时传输两路不同功率、不同速率信号的特点&#xff0c;在需要图象和数据综合业务传输的领域得到了广泛应用。 系统信号的调制方式为非平衡四相键控&#xff08;Unbalanced Quadrature Phase…

SpringBoot 整合Redis第1篇

SpringBoot是一个开发框架&#xff0c;Redis是一个高性能的键值存储数据库&#xff0c; 常用于缓存、会话管理、消息队列等应用场景。 定义 Redis是什么&#xff1f; 它是一个存储层级&#xff0c; 在实际项目中&#xff0c;位于关系数据库之上&#xff0c; 类似Android分为5…

vue3封装Element导航菜单

1. 导航外层布局 AsideView.vue <template><el-menu:default-active"defaultActive"class"my-menu":collapse"isCollapse":collapse-transition"false"open"handleOpen"close"handleClose"><menu…

【机器学习入门】拥抱人工智能,从机器学习开始

拥抱人工智能&#xff0c;从机器学习开始 目录&#xff1a; 1. 机器学习&#xff1a;一种实现人工智能的方法2. 机器学习算法&#xff1a;是使计算机具有智能的关键3. Anaconda&#xff1a;初学Python、入门机器学习的首选4. 总结 转载链接&#xff1a;文章-阿里云开发者社区…

PyTorch深度学习入门-1

PyTorch深度学习快速入门教程&#xff08;绝对通俗易懂&#xff01;&#xff09;【小土堆】_哔哩哔哩_bilibili \ PyTorch 和 TensorFlow 是两个深度学习框架&#xff0c;TensorBoard 是 TensorFlow 提供的可视化工具&#xff0c;Transforms是 PyTorch 中用于数据预处理的工具…

可视化图表:K线图,快速搞清价格波动。

2023-08-21 21:20贝格前端工场 Hi&#xff0c;我是贝格前端工场的老司机&#xff0c;本文分享可视化图表设计的K线图设计&#xff0c;欢迎老铁持续关注我们。 一、K线图的含义 K线图&#xff08;K Line Chart&#xff09;是一种常用于股票、期货等金融市场的可视化图表&…

如何将图片识别转文字?这3种工具简单易操作

如何将图片识别转文字&#xff1f;在数字化时代&#xff0c;图片识别转文字技术的需求愈发凸显。无论是处理海量的扫描文档&#xff0c;从中迅速提取关键信息&#xff0c;还是通过照片轻松记录菜单上的文字&#xff0c;这一技术都展现出了其强大的实用性。它极大地提高了我们的…

计算机网络—VLAN 间路由配置

目录 1.拓扑图 2.实验环境准备 3.为 R3 配置 IP 地址 4.创建 VLAN 5.配置 R2 上的子接口实现 VLAN 间路由 6.配置文件 1.拓扑图 2.实验环境准备 配置R1、R3和S1的设备名称&#xff0c;并按照拓扑图配置R1的G0/0/1接口的IP地址。 [Huawei]sysname R1 [R1]interface Giga…

机器视觉/将HIK海康面阵相机连接Halcon软件

文章目录 概述工业相机客户端动态库拷贝Halcon连接HIK相机的配置相机参数其他 概述 本文简述了如何将海康面阵相机连接到Halcon软件中进行实时取图的过程。 补充&#xff0c; 整个实践过程使用 17.12 / x64-win64 Halcon 软件版本 海康 MV-CE200-10GM 面阵相机。从左到右简解…

机器学习周报第35期

目录 一、文献阅读&#xff1a;You Only Look Once: Unified, Real-Time Object Detection1.1 摘要1.2 背景1.3 论文模型1.4 网络设计1.5 YOLO的局限性1.6 实现代码 target 7*7*30 值域为0-1 一、文献阅读&#xff1a;You Only Look Once: Unified, Real-Time Object Detection…

C/C++ 之 GSL 数学运算库使用笔记

Part.I Introduction 本文主要记录一下笔者使用 GSL 过程当中所做的一些笔记。 Chap.I 传送门 一些传送门 GSL源码&#xff08;CMakeList 版本-Windows&#xff09;GSL源码&#xff08;configure 版本-Linux&#xff09;GSL 在线文档GSL 文档下载 Chap.II GSL 简介 GSL 全…

【Java EE】多线程(一)

&#x1f4da;博客主页&#xff1a;爱敲代码的小杨. ✨专栏&#xff1a;《Java SE语法》 | 《数据结构与算法》 | 《C生万物》 |《MySQL探索之旅》 |《Web世界探险家》 ❤️感谢大家点赞&#x1f44d;&#x1f3fb;收藏⭐评论✍&#x1f3fb;&#xff0c;您的三连就是我持续更…

Python爬虫验证码识别——手机验证码的自动化处理

手机验证码的自动化处理 有一种验证码就是手机验证码&#xff0c;如果在PC上出现了一个手机验证码&#xff0c;需要先在PC上输入手机号&#xff0c;然后把短信验证码发到手机上&#xff0c;再在PC上输入收到的验证码&#xff0c;才能通过验证。 遇到这样的情况&#xff0c;如…

【Linux在程序运行时打印调用栈信息(函数名,文件行号等)】

在程序运行时打印相关调用栈信息&#xff08;函数名&#xff0c;文件行号等&#xff09;,便于梳理调用逻辑等 //stack.c #include <stdio.h> #include <execinfo.h> #include <stdlib.h> #include <string.h> #include <stdbool.h>#define MAX_…

vue cesium heatmap 热力图

实现效果 引入 heatmap index.html 中引入 heatmap <script src"./heatmap.min.js"></script>使用 <script lang"ts" setup> import * as Cesium from cesium import cesium/Build/Cesium/Widgets/widgets.cssdefineOptions({ name: …

MySQL count函数的使用

count&#xff08;&#xff09;函数在使用时参数好像不能设置为表达式&#xff0c;只能设置成指定字段或* 比如在查询性别为男的成员数目时不能写&#xff1a; select count(gendermale) from user_profile ; 否则直接得到6&#xff0c;也就是等价于select count(gender) fro…