一个简单的sql审核案例 (r8笔记第90天)

今天开发的同学发来一封邮件,希望我帮忙对一个sql语句做一个评估。他们也着急要用,但是为了稳妥起见,还是希望我来审核一下,这是一个好的习惯。 打开邮件,看到的语句是下面这样的形式。 select a.cout1+b.cout2 from (select count(*) as cout1 from TEST_ONLINE where CN='' and to_char(LOGIN_TIME,'yyyymmdd') = to_char(sysdate,'yyyymmdd') and rownum = 1) a,(select count(*) as cout2 from TEST_USER_CENTER where CN='' and to_char(LAST_LOGOUT,'yyyymmdd') = to_char(sysdate,'yyyymmdd') and rownum = 1) b; 看到这个语句,确实需要审核。 首先从sql语句结构上来说,实在不够好。 如果两个子查询的结果集条数大于1,很可能走笛卡尔积,貌似开发的同学也注意到了这一点,在两个子查询的末尾都加了rownum=1的字样,这样就肯定能够保证语句能够始终有1条以内的记录显示。所以这个语句看起来可以调整的空间不大。 但是我们做sql审核,也离不开表的属性信息。这两个表是OLTP的数据表,里面会有大量的实时数据变化,看看两个子查询中的过滤条件,是根据日期来作为 单位统计的,而一个核心字段就是CN了。看到这种情况,如果每日存在大量的数据,使用to_char(LAST_LOGOUT,'yyyymmdd')这 种方式肯定是有弊端,但是看需求是想精确到日为单位的数据,那么在这种情况下的关键就是CN了。 带着疑问继续查看,发现CN在两个表中都是主键,那么这种情况就好办多了。对于日期带来的困扰,其实影响不大,而且根据数据的分布,一个CN对应的数据是 唯一性的,那么使用rownum=1就有些多余了,然后再来看日期的过滤,有了CN的唯一性约束过滤,数据要么有匹配的是1条,要么就是没有匹配的0条。 结果也是显而易见,明白了这一点,这个时候看起来思路就清晰多了,这个查询的结果应该是在0~2之间。 对于这个语句有了更深入一步的认识,我们就来简单的改造一下。 这样的形式: select a.cout1+b.cout2 from (select count(*) as cout1 from TEST_ONLINE where CN='xxx' and to_char(LOGIN_TIME,'yyyymmdd') = to_char(sysdate,'yyyymmdd')) a,(select count(*) as cout2 from TEST_USER_CENTER where CN='xxx' and to_char(LAST_LOGOUT,'yyyymmdd') = to_char(sysdate,'yyyymmdd') ) b; 或者: select (select count(*) as cout1 from TEST_ONLINE where CN='xxx' and to_char(LOGIN_TIME,'yyyymmdd') = to_char(sysdate,'yyyymmdd') ) +(select count(*) as cout2 from TEST_USER_CENTER where CN='xxx' and to_char(LAST_LOGOUT,'yyyymmdd') = to_char(sysdate,'yyyymmdd') ) from dual; 或者使用with with a as (select count(*) as cout1 from TEST_ONLINE where CN='xxx' and to_char(LOGIN_TIME,'yyyymmdd') = to_char(sysdate,'yyyymmdd') and ), b as (select count(*) as cout2 from TEST_USER_CENTER where CN='xxx' and to_char(LAST_LOGOUT,'yyyymmdd') = to_char(sysdate,'yyyymmdd') ) select a.count1+b.count2 from a,b; 在目前满足条件的情况下,性能差别应该不大。如果CN为非唯一性约束,这个问题还是需要好好斟酌一下了,如果在 LOGIN_TIME,LOGOUT_TIME上有索引还是需要避免使用日期的二次格式化,而且在这个基础上,我应该在末尾使用group by而不是rownum=1了。 这样语句可能就变成了下面的形式。 select a.cout1+b.cout2 from (select count(*) as cout1 from TEST_ONLINE where CN='xxx' and LOGIN_TIME between trunc(sysdate) and to_date(sysdate,'yyyy-mm-dd hh24:mi:ss') group by LOGIN_TIME) a,(select count(*) as cout2 from TEST_USER_CENTER where CN='xxx' and LOGOUT_TIME between trunc(sysdate) and to_date(sysdate,'yyyy-mm-dd hh24:mi:ss') group by LOGOUT_TIME) b; 还有其它更多的改进方法,暂且讨论到这里。

本文分享自微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2016-05-05

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

一条delete语句的调优(r4笔记第86天)

今天刚上上班,就接到客户的邮件,说生产环境中执行某一条delete sql语句的时间超过了3个小时。最后客户无奈取消了这次数据清理,准备今天在申请时间重做。所以...

38750
来自专栏WindCoder

网易MySQL微专业学习笔记(十一)-MySQL业务优化与设计

这个系列属于个人学习网易云课堂MySQL数据库工程师微专业的相关课程过程中的笔记,本篇为其“MySQL业务优化与设计”中的MySQL数据类型相关笔记。

14610
来自专栏大白虾谈架构

数据库主外建适用场景

10750
来自专栏维C果糖

史上最简单的 MySQL 教程(十九)「范式」

在数据存储之后,凡是能够通过关系寻找出来的数据,坚决不再重复存储,范式的终极目标是减少数据冗余。

43180
来自专栏文渊之博

列存储索引1:初识列存储索引

     2012以后提供了一种不同于传统B树结构的索引类型,就是内存列存储索引。这种索引应用了一种基于列的存储模式,也是一种新的查询执行的批处理模式,并且为特...

25750
来自专栏杨建荣的学习笔记

用Oracle的眼光来学习MySQL 5.7的sys(上)(r11笔记第24天)

sys的初衷 MySQL 5.7的sys自从推出以来,整体的反响似乎没有预期的那么高,而我看到这个sys库的时候,第一感觉是越发和Oracle像了,不是...

39390
来自专栏bboysoul

给自己的主机测个速度

昨天有人问我,为什么他的国外服务器看有土鳖的视频那么卡,其实归纳一下有下面这么几点导致你的服务器速度不高

14020
来自专栏数据分析

SQL Server 性能优化之——系统化方法提高性能

1. 概述 在比较大的范围内找出能够大幅提高性能的区域,并且专注于分析这个区域,这是最有效的优化SQL Server性能的方式。否则,大量的时间和精力可能被浪费...

53260
来自专栏杨建荣的学习笔记

复杂SQL性能优化的剖析(一)(r11笔记第36天)

今天本来是处理一个简单的故障,但是发现是一环套一环,花了我快一天的时间。 开始是早上收到一条报警: 报警内容: CPUutilization is too hi...

373120
来自专栏杨建荣的学习笔记

MySQL里面的JSON特性

在我们梳理的开发规范里面,明确规定对于lob类型的使用原则只有一个,那就是尽量不要使用。但是很明显,开发同学走到了我们前面,如果你碰到开发同学使用JSON数据类...

16100

扫码关注云+社区

领取腾讯云代金券