ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL优化之把运算放在列的另一端

SQL优化之把运算放在列的另一端

原创 Linux操作系统 作者:space6212 时间:2019-05-29 22:45:04 0 删除 编辑

今天帮朋友解决oracle进程异常中断后不能登录的问题后,顺便帮他看了一下造成IDLE几乎为0的原因。
通过分析发现,都是因为不良SQL造成的。这些不良SQL的一个共同点就是:把运算放在了列端。
朋友的数据量非常大,我在本机上简单模拟现场,数据量上会小很多,但是仍然可以不同的写法对执行效率的差别。


1、准备数据
SQL> create table test(name varchar2(100),code number(6),birth date);

Table created
--这个表中,code表示地区编码,前两位表示所属省份;birth表示出生日期

SQL> insert into test select 'suk',110000,trunc(sysdate-30000)+rownum from dual connect by rownum<30000;

29999 rows inserted

SQL> insert into test values('suk',120000,trunc(sysdate-10000));

1 row inserted

SQL> commit;

Commit complete

SQL> create index idx_test_name_code on test(name,code);

Index created

SQL> create index idx_test_name_birth on test(name,birth);

Index created

SQL> analyze table test compute statistics for table for all indexes for all indexed columns;

Table analyzed

2、查询
在本文中,举两个例子:
SQL1)查询在12这个省中与'suk'同名的人的个数
SELECT COUNT(1) FROM TEST WHERE SUBSTR(CODE,1,2)=12 AND NAME='suk';
SQL2)查询名字叫'suk'且出生日期是'1980-03-11'的人的个数
SELECT COUNT(1) FROM TEST WHERE NAME='suk' AND TO_CHAR(BIRTH,'YYYY-MM-DD')='1980-03-11';

这两个SQL有一个共同点:就是把运算都放在了列上:SUBSTR(CODE,1,2)、TO_CHAR(BIRTH,'YYYY-MM-DD')。
我们在(name,code),(name,birth)都建立的组合索引,这两个查询都直接可以从查询得到结果,而不用在回表查询了。
但是,CODE的数据类似的NUMBER,那么,规矩oracle数据类似转换的规则可知,在实际执行的时候,SUBSTR(CODE,1,2)=12会转换成:
TO_NUMBER(SUBSTR(TO_CHAR(CODE),1,2))=12。
在SQL1中,根据索引,oracle很快可以定位到name='suk'的叶块(在本例中占了绝大部分叶块),但因为对CODE进行了各种的函数转换,oracle无法快速确定SUBSTR(CODE,1,2)=12所在也块,它需要在name='suk'得到的叶块上一一排查,再结合根据数据分布情况分析,SQL1的查询计划应该是全索引快速扫描。
我们可以查看一下执行计划:
suk@ORACLE9I> SELECT COUNT(1) FROM TEST WHERE SUBSTR(CODE,1,2)=12 AND NAME='suk';

COUNT(1)
----------
1

已用时间: 00: 00: 00.17

执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=5)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'IDX_TEST_NAME_CODE' (NON-UNIQ
UE) (Cost=4 Card=300 Bytes=1500)


统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
81 consistent gets

要调整这个SQL就要让条件中出现的索引字段不经过任何转换,这样就可以快速定位记录了。SQL1可以等价改写为如下:
suk@ORACLE9I> SELECT COUNT(1) FROM TEST WHERE CODE>=120000 AND CODE<130000 AND NAME='suk';

COUNT(1)
----------
1

已用时间: 00: 00: 00.12

执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=5)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NAME_CODE' (NON-UNIQUE)
(Cost=2 Card=1 Bytes=5)

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets

同理可以调整SQL2:
--调整前
suk@ORACLE9I> SELECT COUNT(1) FROM TEST WHERE NAME='suk' AND TO_CHAR(BIRTH,'YYYY-MM-DD')='1980-03-11
';

COUNT(1)
----------
2

已用时间: 00: 00: 00.17

执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=12)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'IDX_TEST_NAME_BIRTH' (NON-UNI
QUE) (Cost=5 Card=300 Bytes=3600)

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
104 consistent gets

--调整后
suk@ORACLE9I> SELECT COUNT(1) FROM TEST WHERE NAME='suk' AND BIRTH=TO_DATE('1980-03-11','YYYY-MM-DD'
);

COUNT(1)
----------
2

已用时间: 00: 00: 00.14

执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=12)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NAME_BIRTH' (NON-UNIQUE)
(Cost=1 Card=300 Bytes=3600)


统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets

可以看到,调整后的SQL用索引范围扫描代替了全索引快速扫描,逻辑读大大降低。

3、简单总结

SQL调优的一个原则就是不要在条件中出现的列上做任何操作(包括不要加函数、不要发生数据隐式转换等),我们要把操作尽量放在常量端。
换一句话说就是:建立索引时索引列是怎么定义的,在where条件中列也要按相同的方式写。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/231499/viewspace-63840/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2005-01-25

  • 博文量
    245
  • 访问量
    167442