常见的几种索引扫描的类型

标签： 常见 索引 类型 | 发表时间：2013-03-16 23:26 | 作者：ziwen00
出处：http://blog.csdn.net
一、如何查看SQL语句的执行计划

1、在SQL*PLUS环境中

执行SET AUTOTRACE ON 可以打开启动自动跟踪功能，如下(下面的红色字体是敲入的命令)：
ChenZw>  set autotrace on
ChenZw>  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
      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
      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
      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
      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 |   518 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


统计信息
----------------------------------------------------------
        476  recursive calls
          0  db block gets
         90  consistent gets
          9  physical reads
          0  redo size
       1423  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
         14  rows processed
    如同 describe命令可以省略为 desc一样， set autotrace off命令也可以省略为 set autot off。如果，执行计划中的行太宽的话，可以执行如下命令，将行的宽度设置为200，命令如下：
ChenZw>  set linesize 200
ChenZw>

2、在PL/SQL Developer软件中

在PL/SQL Developer窗口中，点击“文件”的“新建”菜单中选择“SQL窗口”,在里面输入自己要执行的SQL，例如上面的
SELECT * FROM EMP;
然后点击“F5”按钮，系统会自动弹出“解释计划窗口”，并且将该SQL的执行计划在“解释计划窗口”中显示出来
或者直接创建“解释计划窗口”也可以进入解释计划窗口。
    现在回过头来看这个SQL语句，使用的是“ TABLE ACCESS FULL”的方式进行查询，那么优化器为什么会使用全表查询呢？有这样几个情况下，数据库会选择全表索引的。
第一、高度并发的时候，如果在 DBA_TABLES视图里面的 DEGREE（The number of threads per instance for scanning the table）字段的值比较高的时候，优化器会选择全表扫描的方式。
第二、在该表上，如果没有索引的时候，正所谓“巧妇难为无米之炊”，优化器也只能选择全表扫描 FULL TABLE SCAN的方式了。
第三、查询大数据量的时候，比如上面的SQL语句，优化器一看后面一个WHERE条件都没有，或者优化器认为该次查询将会查询到大部分的数据，则会使用全表扫描，这种情况下，就算是该SQL能够用到索引，也不会使用的。
第四种，查询小标的时候，在初始化参数里面有一个 DB_FILE_MULTIBLOCK_READ_COUNT，这个参数指定了每次读数据块的数量，但是如果发现当前要查询的表的数据表小于该参数的值，优化器也会选择全表扫描的。

二、SQL语句的几种常见的索引扫描类型

    首先，有一个概念，就是除了获取索引本身的值之外，使用索引最终还是要使用表的 ROWID进行获取数据的。也就是 TABLE ACCESS BY ROWID。什么是ROWID呢？下面的查询：

ChenZw>  select t.rowid,t.* from emp t;

ROWID              EMPNO ENAME  JOB       MGR HIREDATE    SAL  COMM DEPTNO
------------------ ----- ------ ------------- --------------- ----- ------
AAAQ+jAAEAAAAAeAAA  7369 SMITH  CLERK    7902 17-12月-80  800        20
AAAQ+jAAEAAAAAeAAB  7499 ALLEN  SALESMAN 7698 20-2月 -81 1600   300  30
AAAQ+jAAEAAAAAeAAC  7521 WARD   SALESMAN 7698 22-2月 -81 1250   500  30
AAAQ+jAAEAAAAAeAAD  7566 JONES  MANAGER  7839 02-4月 -81 2975        20
AAAQ+jAAEAAAAAeAAE  7654 MARTIN SALESMAN 7698 28-9月 -81 1250  1400  30
AAAQ+jAAEAAAAAeAAF  7698 BLAKE  MANAGER  7839 01-5月 -81 2850        30
AAAQ+jAAEAAAAAeAAG  7782 CLARK  MANAGER  7839 09-6月 -81 2450        10
AAAQ+jAAEAAAAAeAAH  7788 SCOTT  ANALYST  7566 19-4月 -87 3000        20
AAAQ+jAAEAAAAAeAAI  7839 KING   PRESIDENT     17-11月-81 5000        10
AAAQ+jAAEAAAAAeAAJ  7844 TURNER SALESMAN 7698 08-9月 -81 1500     0  30
AAAQ+jAAEAAAAAeAAK  7876 ADAMS  CLERK    7788 23-5月 -87 1100        20
AAAQ+jAAEAAAAAeAAL  7900 JAMES  CLERK    7698 03-12月-81  950        30
AAAQ+jAAEAAAAAeAAM  7902 FORD   ANALYST  7566 03-12月-81 3000        20
AAAQ+jAAEAAAAAeAAN  7934 MILLER CLERK    7782 23-1月 -82 1300        10

    ROWID字段中保存了很多信息，例如所在数据块、数据文件、行在数据块中的信息等等，可以非常方便的通过ROWID的信息找到该记录所在的位置，在oracle中，使用ROWID来查找某条记录是最快的。在使用索引的时候，如果不是查询索引本身的值（例如主键），而是查询主键所在行的其他字段信息的话，Oracle也是首先得到ROWID的信息，然后再去找到其他字段的信息，并且返回。

在网上找到这样几种最常见的扫描的类型：

第一种： index unique  scan
    索引唯一扫描，当可以优化器发现某个查询条件可以利用到主键、唯一键、具有外键约束的列，或者只是访问其中某行索引所在的数据的时候，优化器会选择这种扫描类型。

第二种： index range scan
    索引范围扫描，当优化器发现在UNIQUE列上使用了大于、小于、大于等于、小于等于以及BETWEEN等就会使用范围扫描，在组合列上只使用部分进行查询，导致查询出多行数据。对非唯一的索引列上进行任何活动都会使用 index range scan。

第三种： index full scan
    全索引扫描，如果要查询的数据可以全部从索引中获取，则使用全索引扫描。

第四种： index fast full scan
    索引快速扫描，扫描索引中的全部的数据块，与全索引扫描的方式基本上类似。两者之间的明显的区别是，索引快速扫描对查询的数据不进行排序，数据返回的时候不是排序的。“在这种存取方法中，可以使用多块读功能，也可以使用并行读入，从而得到最大的吞吐量和缩短执行时间”。

三、几种常见的索引类型的模拟

创建测试表以及测试数据的SQL：

CREATE TABLE TBL(
    USERID NUMBER(20),
    NAME VARCHAR2(20),
    DEPTNO NUMBER(20)
)
;
--模拟数据
BEGIN
    FOR I IN 0..1000 LOOP
        INSERT INTO TBL VALUES(
        I,CONCAT('TBL',I),MOD(I,2));
    END LOOP;
END;

SELECT * FROM TBL;

1） TABLE ACCESS BY USER ROWID
执行下面的语句得到几个ROWID：
ChenZw> select rowid,tbl.* from tbl where rownum<10;

ROWID                  USERID NAME                     DEPTNO
------------------ ---------- -------------------- ----------
AAAUhAAAEAAAAIcAAA        796 TBL796                        0
AAAUhAAAEAAAAIcAAB        797 TBL797                        1
AAAUhAAAEAAAAIcAAC        798 TBL798                        0
AAAUhAAAEAAAAIcAAD        799 TBL799                        1
AAAUhAAAEAAAAIcAAE        800 TBL800                        0
AAAUhAAAEAAAAIcAAF        801 TBL801                        1
AAAUhAAAEAAAAIcAAG        802 TBL802                        0
AAAUhAAAEAAAAIcAAH        803 TBL803                        1
AAAUhAAAEAAAAIcAAI        804 TBL804                        0

然后带着rowid去查询，看这个时候的执行计划：
ChenZw> select * from tbl where rowid = 'AAAUhAAAEAAAAIcAAA';
执行计划
----------------------------------------------------------
Plan hash value: 2623095911

-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |    50 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY USER ROWID| TBL  |     1 |    50 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
上面的TABLE ACCESS BY USER ROWID是一种效率最高的获取数据的方法。

2） index unique  scan
修改上面的表，增加一个主键，等于自动会增加一个唯一键约束：
ALTER TABLE TBL ADD CONSTRAINT TBLID PRIMARY KEY (USERID);
执行下面的SQL语句：
ChenZw> select * from tbl where userid = 100;
执行计划
----------------------------------------------------------
Plan hash value: 1167568666

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    38 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TBL   |     1 |    38 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | TBLID |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

从上面的执行计划可以看出，优化器首先是根据为刚才建立的索引TBLID来找到100的ROWID，然后根据ROWID去找到100所在的行数据。

2） index full scan
为上面的表增加一个联合索引，在TBL.NAME和TBL.DEPTNO两个列上，SQL如下：
CREATE INDEX INDEX_TBL_NAME_DEPTNO ON TBL(NAME,DEPTNO);

3） index range scan
执行下面的语句：
ChenZw> select * from tbl where userid between 10 and 100;
已选择91行。
执行计划
----------------------------------------------------------
Plan hash value: 2314926374

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |    91 |  3458 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TBL   |    91 |  3458 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TBLID |    91 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
在索引列上使用大于等于，小于等于，between等的时候，优化器会首先在索引列上进行操作，进行一个索引范围扫描得到ROWID之后，再根据ROWID来找到相应的数据。

这个里面有一个很有意思的问题：
ChenZw> select * from tbl where userid between 10 and 100 order by 1;
已选择91行。
执行计划
----------------------------------------------------------
Plan hash value: 2314926374

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |    91 |  3458 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TBL   |    91 |  3458 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TBLID |    91 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
看执行计划，我们虽然在SQL语句指定了需要将结果集进行排序，但是在执行计划中，并没有进行排序的动作，这个是为什么呢？
ChenZw> select * from tbl where userid between 10 and 100 order by 1 desc;
已选择91行。
执行计划
----------------------------------------------------------
Plan hash value: 4193471386

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |    91 |  3458 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | TBL   |    91 |  3458 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN DESCENDING| TBLID |    91 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
这个执行计划和上面的执行计划有什么区别呢？其中第一个执行计划是索引范围扫描（INDEX RANGE SCAN），但是第二个执行计划是索引范围降序扫描（INDEX RANGE SCAN DESCENDING）。由此可以看出对于唯一索引，ORACLE是已经进行了正序排列了。也就是唯一索引在进行排序的时候消耗不是很大的，因为它在保存的时候就按照升序进行保存的。

4） index fast full scan
假如我们只需要得到所有的USERID的时候，优化器会如何进行查询呢？我们要查的USERID是全部在索引里面的，执行一下下面的语句，得到下面的执行计划：
ChenZw> select userid from tbl;
已选择100001行。
执行计划
----------------------------------------------------------
Plan hash value: 3798421822

------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |   115K|  1471K|    63   (2)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| TBLID |   115K|  1471K|    63   (2)| 00:00:01 |
------------------------------------------------------------------------------
看到上面的执行计划是索引快速扫描（INDEX FAST FULL SCAN），因为需要得到的所有的数据都是在索引里面的，因此只需要将所需要的数据从索引中取出来就ok了。











作者：ziwen00 发表于2013-3-16 23:26:55 原文链接
阅读：0 评论：0 查看评论

相关 [常见 索引 类型] 推荐：

常见的几种索引扫描的类型

- - CSDN博客推荐文章
一、如何查看SQL语句的执行计划. 1、在SQL*PLUS环境中. 执行SET AUTOTRACE ON 可以打开启动自动跟踪功能，如下(下面的红色字体是敲入的命令)：.      EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO.

4种常见的MySQL日志类型 - siqiniao

- - 博客园_思齐
4种常见的MySQL日志类型. 1．错误日志 记录启动、运行或停止mysqld时出现的问题. 记录建立的客户端连接和执行的所有语句（包括错误的）. 记录所有更改数据的语句、还用于主从复制. 记录所有执行时间超过long_query_time秒的所有查询. long_query_time =1 #时间.

一个Date类型的ibatis查询走不上索引的问题

- - 数据库 - ITeye博客
        实际工作中，发现Date类型作为条件查询走不上索引的问题，由于问题完全和 http://blog.csdn.net/zldeng19840111/article/details/6721589一样，为简便起见，直接采用它的实例说明.         以下为简化后的场景：通过时间范围作一个邮件发送数量的统计.

ElasticSearch 索引 VS MySQL 索引

- - crossoverJie's Blog
这段时间在维护产品的搜索功能，每次在管理台看到 elasticsearch 这么高效的查询效率我都很好奇他是如何做到的. 这甚至比在我本地使用 MySQL 通过主键的查询速度还快. 这类问题网上很多答案，大概意思呢如下：. Lucene 的全文检索引擎，它会对数据进行分词后保存索引，擅长管理大量的索引数据，相对于.

SQL Server--索引

- - CSDN博客推荐文章
         1,概念:  数据库索引是对数据表中一个或多个列的值进行排序的结构,就像一本书的目录一样,索引提供了在行中快速查询特定行的能力..             2.1优点:  1,大大加快搜索数据的速度,这是引入索引的主要原因..                             2,创建唯一性索引,保证数据库表中每一行数据的唯一性..

MongoDB 索引

- - 博客园_首页
索引是用来加快查询的,数据库索引与数据的索引类似,有了索引就不需要翻遍整本书,数据库可以直接在索引中查找,. 使得查询速度很快,在索引中找到条目后,就可以直接跳转到目标文档的位置.. 要掌握如何为查询配置最佳索引会有些难度.. MongoDB索引几乎和关系型数据库的索引一样.绝大数优化关系型数据库索引的技巧同样适用于MongoDB..

倒排索引

- - ITeye博客
倒排索引是文档检索系统中最常见的数据结构，被广泛的应用于搜索引擎. 它是一种根据内容查找文档的方式. 由于不是根据文档来找内容，而是根据进行了相反的操作，因此叫做倒排索引. 倒排索引的一个简单结构如下图所示：. 最常见的是使用词频作为权重，即单词在一个文档中出现的次数. 因此，当搜索条件为“MapReduce”“is”“simple”的时候，对应的集合为{(0.txt,1),(1.txt,1),(2.txt,2)}且{(0.txt,1),(1.txt,2)}且{(0.txt,1),(1.txt,1)}={0.txt,1.txt}.

索引原理

- - ITeye博客
索引是存储引擎用于快速找到记录的一种数据结构. 也就会说索引也是一种数据结构，也占用磁盘空间. 索引是对查询优化最有效的手段，可以将查询提升几个数量级，相当牛掰啊. 1）索引大大减少了服务器需要扫描的数据量. 2）索引可以帮助服务器避免排序和临时表. 3）索引可以将随机IO变为顺序IO. 数据库索引可以想象成一本书的目录，如果想在一本书中找到某个主题，那么先到书的目录中找到这个主题，然后根据目录提供的页码，找到要找的主题.

倒排索引

- - CSDN博客推荐文章
倒排索引（英语：Inverted index），也常被称为反向索引、置入档案或反向档案，是一种索引方法，被用来存储在全文搜索下某个单词在一个文档或者一组文档中的存储位置的映射. 它是文档检索系统中最常用的数据结构. 有两种不同的反向索引形式：. 一条记录的水平反向索引（或者反向档案索引）包含每个引用单词的文档的列表.

oracle 索引

- - 数据库 - ITeye博客
        自动：在使用primary和unique后系统会自动创建唯一索引.         手动：create   index   索引名  on 表名(字段1,....). 查询表上有哪些索引（网上找的，能用，表名和索引名要大写）. 1、查找表的所有索引（包括索引名，类型，构成列）：. select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name = 要查询的表.