Oracle SQL调优系列之访问数据的方法

我会带着你远行 2021-09-18 03:46 181阅读 0赞

### 文章目录 ###

*   *  一、访问数据的方法
     *   *  1、直接访问数据
         *   *  1.1 全表扫描
             *  1.2 ROWID扫描
         *  2、访问索引
         *   *  2.1 索引唯一扫描
             *  2.2 索引范围扫描
             *  2.3 索引全扫描
             *  2.4 索引快速全扫描
             *  2.5 索引跳跃式扫描
             *  拓展补充

## 一、访问数据的方法 ##

Oracle访问表中数据的方法有两种，一种是直接表中访问数据，另外一种是先访问索引，如果索引数据不符合目标SQL，就回表，符合就不回表，直接访问索引就可以。  
本博客先介绍直接访问数据的方法，下一篇博客在访问索引的方法

### 1、直接访问数据 ###

Oracle直接访问表中数据的方法又分为两种：一种是全表扫描；另一种是ROWID扫描

#### 1.1 全表扫描 ####

全表扫描是Oracle直接访问数据的一种方法，全表扫描时从第一个区(EXTENT)的第一个块(BLOCK)开始扫描，一直扫描的到表的高水位线(High Water Mark)，这个范围内的数据块都会扫描到

全表扫描是采用多数据块一起扫的，并不是一个个数据库扫的，然后我们经常说全表扫描慢是针对数据量很多的情况，数据量少的话，全表扫描并不慢的，不过随着数据量越多，高水位线也就越高，也就是说需要扫描的数据库越多，自然扫描所需要的IO越多，时间也越多

注意：数据量越多，全表扫描所需要的时间就越多，然后直接删了表数据呢？查询速度会变快？其实并不会的，因为即使我们删了数据，高位水线并不会改变，也就是同样需要扫描那么多数据块

#### 1.2 ROWID扫描 ####

ROWID也就是表数据行所在的物理存储地址，所谓的ROWID扫描是通过ROWID所在的数据行记录去定位。ROWID是一个伪列，数据库里并没有这个列，它是数据库查询过程中获取的一个物理地址，用于表示数据对应的行数。  
用sql查询：

select t.* , rowid from 表格

随意获取一个ROWID序列：AAAWSJAAFAAAWwUAAA，前6位表示对象编号(Data Object number)，其后3位文件编号(Relative file number)，接着其后6位表示块编号(Block number)， 再其后3位表示行编号(Row number)  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70]

**ROWID编码方法是：A ~ Z表示0到25；a ~ z表示26到51；0~9表示52到61；+表示62；/表示63；刚好64个字符。**

这里随意找张表查一下文件编号、区编号、行编号，查询后会返回rowid的一系列物理地址和文件编号(rowid\_relative\_fno(rowid))、块编号(rowid\_block\_number(rowid))、行编号(rowid\_row\_number(rowid))

select t.seq,
           rowid,
           dbms_rowid.rowid_relative_fno(rowid),
           dbms_rowid.rowid_block_number(rowid),
           dbms_rowid.rowid_row_number(rowid)
      from t_info t

SQL查询一下表格名称为TABLE的对象编码

select owner,object_id,data_object_id,status from dba_objects where object_name='TABLE';

**相对文件id和绝对文件编码**  
相对文件id是指相对于表空间，在表空间唯一;绝对文件编码是指相当于全局数据库而言的，全局唯一；下面SQL查询一下相对文件id和绝对文件编码

select file_name,file_id,relative_fno from dba_data_files;

### 2、访问索引 ###

对于Oracle数据库来说，B树索引是最常见的了，下面给出B树索引的图，图来自《基于Oracle的SQL优化》一书：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]

对于B树索引，分成两种类型的数据块，一种是索引分支块，另外一种是索引叶子块，索引根块是一种特殊的索引分支块。

影响逻辑读的缓存:

清Buffer Cache

alter system flush buffer_cache;//请勿随意在生产环境执行此语句

清数据字典缓存(Data Dictionary Cache)

alter system flush shared_pool;//请勿随意在生产环境执行此语句

#### 2.1 索引唯一扫描 ####

索引唯一性扫描(INDEX UNIQUE SCAN)是针对唯一性索引(UNIQUE INDEX)来说的，也就是建立唯一性索引才能索引唯一性扫描，唯一性扫描，其结果集只会返回一条记录。  
创建唯一性索引SQL是：

create unique index 索引名 on 表名(列名)

例子，例子来自《基于Oracle的SQL优化》一书：

scott用户登录

SQL> create table emp_temp as select * from emp;//创建一个测试表
    SQL> create unique index idx_emp_temp on emp_temp(empno);//创建唯一型索引
    SQL> set autotrace on//设置执行计划显示
    SQL> select * from emp_temp where empno =7369;//走索引唯一性扫描
    
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
    ---------- ---------- --------- ---------- -------------- ---------- ----------
        DEPTNO
    ----------
          7369 SMITH      CLERK           7902 17-12月-80            800
            20
    
    
    
    执行计划
    ----------------------------------------------------------
    Plan hash value: 3451700904
    
    --------------------------------------------------------------------------------
    
    ------------
    
    | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)
    
    | Time     |
    
    --------------------------------------------------------------------------------
    
    ------------
    
    |   0 | SELECT STATEMENT            |              |     1 |    87 |     1   (0)
    
    | 00:00:01 |
    
    |   1 |  TABLE ACCESS BY INDEX ROWID| EMP_TEMP     |     1 |    87 |     1   (0)
    
    | 00:00:01 |
    
    |*  2 |   INDEX UNIQUE SCAN         | IDX_EMP_TEMP |     1 |       |     0   (0)
    
    | 00:00:01 |
    
    --------------------------------------------------------------------------------
    
    ------------
    
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("EMPNO"=7369)

#### 2.2 索引范围扫描 ####

索引范围扫描(INDEX RANGE SCAN)适用于所有类型的B树索引，一般不包括唯一性索引，因为唯一性索引走索引唯一性扫描。 当扫描的对象是非唯一性索引的情况，where谓词条件为Between、=、<、>等等的情况就是索引范围扫描，注意，可以是等值查询，也可以是范围查询。**如果where条件里有一个索引键值列没限定为非空的，那就可以走索引范围扫描，如果改索引列是非空的，*那就走索引全扫描***

前面说了，同样的SQL建的索引不同，就可能是走索引唯一性扫描，也有可能走索引范围扫描。在同等的条件下，索引范围扫描所需要的逻辑读和索引唯一性扫描对比，逻辑读如何？索引范围扫描可能返回多条记录，所以优化器为了确认，肯定会多扫描，***所以在同等条件，索引范围扫描所需要的逻辑读至少会比相应的唯一性扫描的逻辑读多1***

继续上面的例子：

SQL> drop index idx_emp_temp;//删了刚才的唯一性索引
    
    索引已删除。
    
    SQL> create index idx_emp_temp on emp_temp(empno);//创建一个B树索引
    
    索引已创建。
    
    SQL> select * from emp_temp where empno=7369;//走索引范围扫描，因为empno没有限定为非空
    
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
    ---------- ---------- --------- ---------- -------------- ---------- ----------
        DEPTNO
    ----------
          7369 SMITH      CLERK           7902 17-12月-80            800
            20
    
    
    
    执行计划
    ----------------------------------------------------------
    Plan hash value: 351331621
    
    --------------------------------------------------------------------------------
    
    ------------
    
    | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)
    
    | Time     |
    
    --------------------------------------------------------------------------------
    
    ------------
    
    |   0 | SELECT STATEMENT            |              |     1 |    87 |     2   (0)
    
    | 00:00:01 |
    
    |   1 |  TABLE ACCESS BY INDEX ROWID| EMP_TEMP     |     1 |    87 |     2   (0)
    
    | 00:00:01 |
    
    |*  2 |   INDEX RANGE SCAN          | IDX_EMP_TEMP |     1 |       |     1   (0)
    
    | 00:00:01 |
    
    --------------------------------------------------------------------------------
    
    ------------
    
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("EMPNO"=7369)
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)

#### 2.3 索引全扫描 ####

索引全扫描(INDEX FULL SCAN)适用于所有类型的B树索引(包括唯一性索引和非唯一性索引)。

索引全扫描过程简述：索引全扫描是指扫描目标索引所有叶子块的索引行，但不意思着需要扫描所有的分支块，索引全扫描时只需要访问必要的分支块，然后定位到位于改索引最左边的叶子块的第一行索引行，就可以利用改索引叶子块之间的双向指针链表，从左往右依次顺序扫描所有的叶子块的索引行

索引全扫描的例子：直接查emp表就好，因为empno是非空的

SQL> select * from emp;
    
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
    ---------- ---------- --------- ---------- -------------- ---------- ----------
        DEPTNO
    ----------
          7369 SMITH      CLERK           7902 17-12月-80            800
            20
    
          7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300
            30
    
          7521 WARD       SALESMAN        7698 22-2月 -81           1250        500
            30
    
    
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
    ---------- ---------- --------- ---------- -------------- ---------- ----------
        DEPTNO
    ----------
          7566 JONES      MANAGER         7839 02-4月 -81           2975
            20
    
          7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400
            30
    
          7698 BLAKE      MANAGER         7839 01-5月 -81           2850
            30
    
    
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
    ---------- ---------- --------- ---------- -------------- ---------- ----------
        DEPTNO
    ----------
          7782 CLARK      MANAGER         7839 09-6月 -81           2450
            10
    
          7788 SCOTT      ANALYST         7566 19-4月 -87           3000
            20
    
          7839 KING       PRESIDENT            17-11月-81           5000
            10
    
    
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
    ---------- ---------- --------- ---------- -------------- ---------- ----------
        DEPTNO
    ----------
          7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0
            30
    
          7876 ADAMS      CLERK           7788 23-5月 -87           1100
            20
    
          7900 JAMES      CLERK           7698 03-12月-81            950
            30
    
    
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
    ---------- ---------- --------- ---------- -------------- ---------- ----------
        DEPTNO
    ----------
          7902 FORD       ANALYST         7566 03-12月-81           3000
            20
    
          7934 MILLER     CLERK           7782 23-1月 -82           1300
            10
    
    
    已选择14行。
    
    
    执行计划
    ----------------------------------------------------------
    Plan hash value: 3956160932
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |    14 |   532 |     3   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
    --------------------------------------------------------------------------

#### 2.4 索引快速全扫描 ####

索引快速全扫描和索引全扫描很类似，也适用于所有类型的B树索引(包括唯一性索引和非唯一性索引)。和索引全扫描类似，也是扫描所有叶子块的索引行，这些都是索引快速全扫描和索引全扫描的相同点

索引快速全扫描和索引全扫描区别：

*  索引快速全扫描只适应于CBO(基于成本的优化器)
 *  索引快速全扫描可以使用多块读，也可以并行执行
 *  索引全扫描会按照叶子块排序返回，而索引快速全扫描则是按照索引段内存储块顺序返回
 *  索引快速全扫描的执行结果不一定是有序的，而索引全扫描的执行结果是有序的，因为索引快速全扫描是根据索引行在磁盘的物理存储顺序来扫描的，不是根据索引行的逻辑顺序来扫描的

条件是使用复合索引，而且使用Hint

/*+ index_ffs(表名 索引名) */

select /*+ index_ffs(emp_test pk_emp_test) */ empno from emp_test;

例子：

SQL> create table emp_test (empno number,ename varchar2(50));
    
    表已创建。
    
    SQL> alter table emp_test add constraint pk_emp_test primary key (empno,ename);
    
    表已更改。
    
    SQL> insert into emp_test select empno,ename from emp;
    
    已创建14行。
    
    SQL> commit;
    
    提交完成。
    
    SQL> select count(1) from emp_test;
    
    SQL> select /*+ index_ffs(emp_test pk_emp_test) */ empno from emp_test;
    
         EMPNO
    ----------
          7369
          7499
          7521
          7566
          7654
          7698
          7782
          7788
          7839
          7844
          7876
    
         EMPNO
    ----------
          7900
          7902
          7934
    
    已选择14行。
    
    
    执行计划
    ----------------------------------------------------------
    Plan hash value: 3550420785
    
    --------------------------------------------------------------------------------
    
    ----
    
    | Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time
       |
    
    --------------------------------------------------------------------------------
    
    ----
    
    |   0 | SELECT STATEMENT     |             |    14 |   182 |     2   (0)| 00:00:
    
    01 |
    
    |   1 |  INDEX FAST FULL SCAN| PK_EMP_TEST |    14 |   182 |     2   (0)| 00:00:
    
    01 |
    
    --------------------------------------------------------------------------------
    
    ----
    
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)

#### 2.5 索引跳跃式扫描 ####

索引跳跃式扫描(INDEX SKIP SCAN)适用于所有类型的\*\*\*复合B树索引\*\*\*(包括唯一性索引和非唯一性索引)，索引跳跃式扫描可以使那些在where条件中没有目标索引的前导列指定查询条件但是有索引的非前导列指定查询条件的目标SQL依然可以使用跳跃索引，定义解释有点绕，举个例子说明

假如新建了复合索引:

create index 索引名 on 表名(列名1,列名2)

这里新建了复合索引，假如查询如：

select * from 表名 where 列名1 = 条件1

假如改目标SQL符合索引跳跃式扫描的条件，即使，只有列名1这个前导列，还是可以走索引跳跃式扫描的，这个就是跳跃式扫描，不需要如下sql，全部索引列都定位到

select * from 表名 where 列名1 = 条件1 and 列名2 = 条件2

当然索引跳跃式扫描并不是说适用所有情况，不加前导列，有时候是不走跳跃式扫描的，***Oracle中的索引跳跃式扫描仅仅适用于那些目标索引前导列的distinct值数量较少，后续非导列的可选择性又非常好的情况，索引跳跃式扫描的执行效率一定会随着目标索引前导列的distinct值数量的递增而递减的***

#### 拓展补充 ####

对于索引来说，如果索引条件有null值，是不走索引的

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70]: /images/20210812/80700375cc4445f88ad278afd033123a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]: /images/20210812/e0b87e6bb143451bbf1952f5b961a26f.png