阅读 269

【Oracle性能优化】!=、NOT NULL、+ 优化分析

接下来小编会做很多很多小实验,有些前提要说明一下,oracle 11g优化器模式Optermizer Mode默认为all_rows,也就是基于cost和统计信息的模式,我们就选它做实验。因为不同的优化器模式,同样的sql语句输出的执行计划是不一样的,我们会穿插一些其他模式的实验。

一、优化技巧及演示

  • 1、!=是不会走索引的,建议使用><进行代替,性能会更好?

网络上有些文章建议说!=运算符是不走索引的,建议使用><进行代替,性能会更好。小编想了想,真的是这样的吗?下面我们做实验,先执行下面的前置脚本:

-- 如果存在,则删除该表
DROP TABLE TEST_TABLE;
-- 基于DBA_OBJECTS建立一张测试表,这张表是没有任何主键、外键、索引的
CREATE TABLE TEST_TABLE AS (SELECT * FROM DBA_OBJECTS);
-- 此时我们表中有7W多条数据,我们建立一个唯一索引
CREATE UNIQUE INDEX TEST_TABLE_UNIQUE_INDEX ON TEST_TABLE(OBJECT_ID); 

-- 开启SQL追踪
SET AUTOTRACE ON;
复制代码

接着执行下面SQL:

SELECT * FROM TEST_TABLE T WHERE T.OBJECT_ID != 10

我们发现确实!=是不走索引的,而是走全表扫描,我们换几种优化器模式:

我们发现,无论哪种优化器模式,都是不走索引的,也就说明!=不走索引是正确的。那么,!=的对立条件是> or <,这种是不是就会走索引呢?执行下面的脚本:

SELECT * FROM TEST_TABLE T WHERE T.OBJECT_ID > 1 OR T.OBJECT_ID < 1;

我们发现确实> or <也是不走索引的,而是走全表扫描,我们换几种优化器模式:

从上面三张图看,当优化器模式分别为CHOOSERULE才会走索引,而FIRST ROWS同样也是不走索引的,因此,我们说在这种情况下,选择优化器模式分别为CHOOSERULE的性能会相比FIRST_ROWALL ROWS好一些。

总结一下:

操作 优化器模式 结果
!= 任何一种模式 都不走索引
> xx or < xx ALL ROWS、FIRST ROWS 全表扫描
> xx or < xx CHOOSE、RULE 索引扫描

下面小编解释一下,为什么当优化器模式选择ALL ROWS时,下面的脚本会走全表扫描:

SELECT * FROM TEST_TABLE T WHERE T.OBJECT_ID > 1;

首先ALL ROWS是基于代价的优化器模式,我们表中有7W多条数据,而我们现在却想查询OBJECT_ID从1到7W多的数据,也就是说相当于查询99.99%的数据,此时oracle优化器会计算我们查询的数据占总数据的比例,如果超过某个阈值,就会走全表扫描,因为如果此时走索引,那么oracle需要先扫描99.99%的索引块,再还要扫描99.99%的段数据块,那还不如直接扫99.99%的数据块就行,节省性能。

  • 2、+是数学函数,是不会走索引的,真的是这样的吗?

我们首先执行下面脚本,对比其执行计划: SELECT * FROM TEST_TABLE T WHERE T.OBJECT_ID + 10 > 70000;

从上面的图,我们可以看出,四种优化器模式均不走索引,原来说法是正确的,因此,我们可以得出下面结论,oracle中,函数是不走索引的,包括+-等数学运算。那么上面的sql该怎么改呢?其实很简单,做一下变换就可以了,脚本如下: SELECT * FROM TEST_TABLE T WHERE T.OBJECT_ID > 69990;

还是那句话,走索引不一定就能增加性能,要看你查询的数据量,如果你查询的数据量超过表中总数据的一定数值(这个数值是优化器运算的),oracle会默认执行全表扫描,因为此时走索引反而会降低性能。

  • 3、避免在索引列上使用is nullis not null,真的是这样的吗?

首先我们执行前置脚本,大致意思就是创建一个表,然后插入10,000,000条数据,并在DATA_OBJECT_ID_INDEX列上建立一个索引DATA_OBJECT_ID_INDEX

-- 如果存在,则删除该表
DROP TABLE TEST_TABLE;
-- 基于DBA_OBJECTS建立一张测试表,这张表是没有任何主键、外键、索引的
CREATE TABLE TEST_TABLE AS (SELECT * FROM DBA_OBJECTS);
-- 此时我们表中有7W多条数据,我们建立一个索引 
CREATE  INDEX DATA_OBJECT_ID_INDEX ON TEST_TABLE(DATA_OBJECT_ID); 
-- 赋值1000W条数据
BEGIN
FOR OBJECT_ID IN 80000..10000000 LOOP
insert into TEST_TABLE (OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME)
values ('SYS', 'C_OBJ#', null, OBJECT_ID, OBJECT_ID, 'CLUSTER', to_date('15-08-2009 00:16:51', 'dd-mm-yyyy hh24:mi:ss'), to_date('15-08-2009 00:16:51', 'dd-mm-yyyy hh24:mi:ss'), '2009-08-15:00:16:51', 'VALID', 'N', 'N', 'N', 5, null);
END LOOP;
END;
commit;
复制代码

其中DATA_OBJECT_ID这个列是有很多的null值的,我们执行下面的脚本,并查看其执行计划:

SELECT * FROM TEST_TABLE T WHERE T.DATA_OBJECT_ID IS NULL;
复制代码

查看V$SQL查看我们刚刚执行的SQL_ID

SELECT SQL_ID,SQL_TEXT 
    FROM V$SQL 
   WHERE SQL_TEXT LIKE '%SELECT * FROM TEST_TABLE T WHERE T.DATA_OBJECT_ID IS NULL%';
复制代码

最后通过查询DBMS_XPLAN.DISPLAY_CURSOR来获取该SQL的实际执行计划:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('8v9nk8bdhq30r',0));

我们发现该sql实际耗时差不多是6分多钟,接下来我们建立一个函数索引,并使用nvl()函数代替IS NULL进行查询,我们看看其耗时:

CREATE INDEX DATA_OBJECT_ID_FUN_INDEX ON TEST_TABLE(nvl(DATA_OBJECT_ID,0));

SELECT * FROM TEST_TABLE T WHERE NVL(T.DATA_OBJECT_ID,0)=0;
SELECT SQL_ID,SQL_TEXT 
    FROM V$SQL 
   WHERE SQL_TEXT LIKE '%SELECT * FROM TEST_TABLE T WHERE NVL(T.DATA_OBJECT_ID,0)=0%';
复制代码

同样查看其SQL_ID,并查看其实际执行计划:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('0rkf81cym166f',0));

我们发现加了索引之后,同样功能的查询语句,耗时居然变成6s,不可思议。

最后说明一下,小编之所以通过V$SQL查找SQL_ID之后再查看执行计划,实际上这是有讲究的,你使用plsql工具按F5或者执行EXPLAIN PLAN FOR XXXXXX查看到的执行计划只是Oracle优化器预估出来的,可能会有实际偏差所以说执行计划有时候是假的,哈哈!不过一般差别不大。下面贴出在plsql工具上的执行计划:

二、总结

从上文可以看出,优化器选择不同,执行计划性能也大有差别,有时候某条sql需要显示告诉oracle相应的优化器模式怎么办呢?比如我们可以在sql层面上使用/*+ RULE */显示指定优化器走RULE模式,如SELECT /*+ RULE */ * FROM TEST_TABLE T

调优时,我们要选择最佳的优化器模式进行调优,根据具体业务场景,数据选择性高低,是否要走索引等等因素,视情况而定选择最优的方法。

最后的最后,我只是个开发,还要我学DBA干的活,还要学运维的docker,我太难了,哈哈,老铁,关注走一波。

关注下面的标签,发现更多相似文章
评论