常与无常:SQL语句中常量的处理及性能差异解析

杨廷琨,网名 yangtingkun

云和恩墨技术总监,Oracle ACE Director,ACOUG 核心专家

在ITPUB论坛上看到一个有意思的问题:两个SQL语句的功能相同,执行结果相同,连执行计划也完全相同,但是两者的执行时间相差了将近一倍。

其实导致这个问题的原因是很多程序员在SQL时经常会遇到的常量处理问题。借此机会说说如何处理常量才可以使SQL语句运行得更快。

当CBO发现表达式中存在常量或常量表达式时,优化器会在SQL执行之前将表达式的值计算出来,避免在表达式中进行多次计算。但是优化器无法将等号一边的常量移动到等号的另一边。这里所说的等号是泛指,还包括不等号、大于号和小于号等。

举例如下:

COL = 1000 COL = 500 + 500 COL – 500 = 500

对于SQL而言,虽然上面的三个等式是等价的,但是CBO优化器只能将第二个等式转化为第一个等式,而对于第三个等式,优化器是没有办法优化的。

第三个等式由于对列进行了运算,因此不能使用这个列上的常规索引。当然这种情况可以使用函数索引,但是显然函数索引的通用性不好,而且要求函数索引的表达式与查询的表达式要完全匹配。对于这种情况,完全没有必要使用函数索引,而且如果使用函数索引除了增加系统的开销外,没有任何的好处。

CBO不使用索引本身就会极大地影响性能,但这还只是第三个等式的一个缺点而已。即使不考虑索引的因素,上面的第三个等式仍然是效率最低的。

假设上面例子中的COL列上没有索引,这样上面三个查询都必须执行全表扫描操作,这时第三个等式仍然是最费时的。执行全表扫描时,Oracle会根据等式的条件对表中每条记录进行过滤,对于等式1和等式2而言,Oracle进行的只是一个比较的操作。而对于等式3而言,Oracle必须将每条记录的值执行一个“—500”的操作,然后再与500进行比较。简单地说,全表扫描多少记录,就会执行多少次的减法操作,因此当数据量大的时候,必然会带来一定的性能损害。

下面通过一个简单的例子来直观地说明问题,首先构造一个大数据量的测试用表。

SQL> create tablet as select * from dba_objects;

表已创建。

SQL> insert into tselect * from t;

已创建6273行。

SQL> insert into tselect * from t;

已创建12546行。

SQL> insert into tselect * from t;

已创建25092行。

SQL> insert into tselect * from t;

已创建50184行。

SQL> insert into tselect * from t;

已创建100368行。

SQL> insert into tselect * from t;

已创建200736行。

SQL> commit;

提交完成。

SQL> select count(*)from t;

COUNT(*)

----------

401472

用于测试的表是根据DBA_OBJECTS视图创建的,一个普通的小数据库中80%以上的对象都是数据字典对象,而且这些对象是在数据库创建的那一天创建的:

SQL> select trunc(created),count(*) from t group by trunc(created)

2 having count(*) > 10000;

TRUNC(CREATED) COUNT(*) ------------------- ---------- 2004-06-29 00:00:00 358144 2004-11-15 00:00:00 34304

下面分别测试4条SQL语句,这4条SQL语句完全是等价的,都是统计2004年6月29日这一天每个用户下的对象个数。它们的执行计划也完全一样,都是全表扫描,然后分别执行这些语句并记录所需的时间。

为了避免数据缓存带来的误差,每个SQL都执行两次,这里列出的都是第二次执行的时间。

语句1:推荐写法,也是标准的写法。

SQL> set timing on

SQL> select count(*)

2 from t

3 where created >= to_date('2004-06-29 00:00:00','yyyy-mm-dd hh24:mi:ss')

4 and created < to_date('2004-06-30 00:00:00','yyyy-mm-dd hh24:mi:ss')

5 group by owner;

COUNT(*)

---------- 448 99968 223872 25472 8384

已用时间: 00: 00: 00.07

语句2:如果不能避免常量的计算或类型的转化,那么尽量让计算或转化在常量上执行,而不要对列进行计算或转化。

SQL> select count(*)

2 from t

3 where created >= to_date(to_char(to_date('2004-06-2900:00:00', 'yyyy-mm-dd hh24:mi:ss') - 123.456, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-ddhh24:mi:ss') + 123.456

4 and created < to_date(to_char(to_date('2004-06-3000:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1000, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-ddhh24:mi:ss') - 1000

5 groupby owner;

COUNT(*)

---------- 448 99968 223872 25472 8384

已用时间: 00: 00: 00.08

上面这个例子包含了比较复杂的运算和多次数据转化,但是常量的计算是在执行开始之前就计算好的。因此这些复杂的计算实际上只进行了一次,从而对查询带来的影响也是很有限的。

语句3:很多人都喜欢用的一个SQL语句,看上去最简单,通过一个限制条件就实现了SQL功能,但是运行结果如何呢:

SQL> select count(*)

2 from t

3 where to_char(created, 'yyyy-mm-dd') = '2004-06-29'

4 group by owner;

COUNT(*)

---------- 448 99968 223872 25472 8384

已用时间: 00: 00: 01.03

这条SQL与前两条SQL相比,效率简直是天壤之别。执行计划都是全表扫描,而SQL的运行时间差距居然如此之大,是由于这个SQL对列进行了转换操作,导致表中的每条记录都要对CREATED列的值进行TO_CHAR函数的调用,显然无论是对列进行运算,还是函数的调用都是相当耗时的。

语句4:最差的一种写法。

SQL> select count(*)

2 from t

3 where to_char(created, 'yyyy-mm-dd hh24:mi:ss')>= '2004-06-29 00:00:00'

4 and to_char(created, 'yyyy-mm-dd hh24:mi:ss') <='2004-06-29 23:59:59'

5 group by owner;

COUNT(*)

---------- 448 99968 223872 25472 8384

已用时间: 00: 00: 02.04

将语句4和语句3进行对比,就更能说明问题了。由于当前的SQL包含了两个查询条件,所以对于每条记录而言,要对CREATED列进行两次转化,而最终的SQL运行时间也恰好是两倍的关系。

这个例子说明在写SQL语句时,应该尽量避免列的操作。对列进行操作不仅会导致无法使用索引,而且还会增加执行过程中的成本,导致SQL语句执行速度变慢。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2016-04-11

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏PPV课数据科学社区

【学习】七天搞定SAS(一):数据的导入、数据结构

SAS的数据类型 ? 首先,sas的编程大概就两块:Data和PROC,这个倒是蛮清晰的划分。然后目前关注data部分。 SAS的数据类型还真的只有两种:数字和...

33912
来自专栏数据和云

深入剖析-关于分页语句的性能优化

分页语句是数据库开发和应用场景比较常见的需求,即按照特定的where条件进行过滤,然后在按照一个或者多个条件进行排序(如果不进行排序无法确执行时候无法返回相同的...

2659
来自专栏文渊之博

SQL中几个常用的排序函数

最近使用窗口函数的频率越来越高,这里打算简单介绍一下几个排序的函数,做一个引子希望以后这方面的问题能够更深入的理解,这里先简单介绍一下几个简单的排序函数及其相...

1735
来自专栏CSDN技术头条

解决程序慢,要学会预测表容积,不能一味地加索引

索引是应用程序设计和开发的一个重要方面。如果索引过多,应用程序中的更新、删除等操作会变慢,性能会受到影响;如果索引过少,对查询性能又会产生影响。

895
来自专栏Java Edge

MySQL索引及其实现原理(基于MyISAM及InnoDB引擎)1 数据结构及算法基础2. MySQL索引实现3. 索引使用策略及优化Hash索引的特点Hash索引的限制

5178
来自专栏王旭的专栏

Web 开发 MYSQL 常用方法整理 (上)

最近在记录Web开发MYSQL常用方法,本篇记录希望数据插入、数据查询这两方面,,后面还会对数据更新以及如何在SQL中实现排行进行整理,希望对初期接触Web 开...

1960
来自专栏文渊之博

理解OVER子句

    简介     Over子句在SQLServer 2005中回归,并且在2012中得到了扩展。这个功能主要结合窗口函数来使用;也可以在序列函数“NEXT ...

1939
来自专栏孙银行的专栏

当谈 SQL 优化时谈些什么?

Mysql数据库作为数据持久化的存储系统,在实际业务中应用广泛。在应用也经常会因为SQL遇到各种各样的瓶颈。增删改查等操作最经常遇到的问题是“查”,查询又以索引...

2.8K2
来自专栏抠抠空间

MySQL 之 索引原理与慢查询优化

浏览目录 一 索引介绍 二 索引方法 三 索引类型 四 聚合索引和辅助索引  五 测试索引 六 正确使用索引 七 组合索引 八 注意事项 九 查询计划 十 慢日...

3507
来自专栏MongoDB中文社区

优化MongoDB复合索引

对于一个MongoDB的复杂查询,如何才能创建最好的索引?在本篇文章中,我将展现一种给读请求定制的索引优化方法,这种方法会考虑读请求中的比较,排序以及范围过滤运...

551

扫描关注云+社区