【SQL】索引 INDEX

LittleZhao 2021-08-16

380

提示

1.在表中定义主键约束或者唯一性约束的时候，Oracle 会自动创建该字段上的索引

2.用户也可自定义某些字段的索引

3.索引创建完成有数据库自动使用，不需要特别指定

1、创建索引

创建空测试表tb_ind01，并添加主键

SQL> show user
USER is "SCOTT"
SQL> create table tb_ind01 as select * from emp;

Table created.

SQL> alter table tb_ind01 add constraint pk_tb_ind0101 primary key (empno);

Table altered.

在ename上添加索引

SQL> alter table tb_ind01 add constraint pk_tb_ind0101 primary key (empno);

Table altered.

查看索引信息

SQL>  select index_name,index_type,table_name,uniqueness from user_indexes where table_name='TB_IND01';

INDEX_NAME           INDEX_TYPE TABLE_NAME UNIQUENES
-------------------- ---------- ---------- ---------
PK_TB_IND0101        NORMAL     TB_IND01   UNIQUE
IND_TB_IND01         NORMAL     TB_IND01   NONUNIQUE

查看表和索引的大小

SQL>

select segment_name,bytes/1024/1024||'MB' from user_segments

where segment_name in('TB_IND01','PK_TB_IND0101','IND_TB_IND01');

SEGMENT_NAME         BYTES/1024/1024
-------------------- ---------------
TB_IND01             .0625MB
PK_TB_IND0101        .0625MB
IND_TB_IND01         .0625MB

索引的优缺点

优点：通过索引可以提高检索数据的效率

缺点：索引也需要占据数据库空间，且需要维护（数据越多，索引越大，数据经常修改，索引也要对应修改）

2、修改索引

重建索引

SQL> alter index IND_TB_IND01 rebuild;

Index altered.

删除索引，并建立新索引

SQL> drop index IND_TB_IND01 ;

Index dropped.

SQL> create index ind_tb_ind02 on TB_IND01(ename,job,mgr);

Index created.

查询索引的相关信息

SQL>

select

c.index_name,c.column_name,c.column_position,x.uniqueness from user_indexes x,user_ind_columns c

where x.index_name=c.index_name

and c.table_name='TB_IND01';

INDEX_NAME           COLUMN_NAM COLUMN_POSITION UNIQUENES
-------------------- ---------- --------------- ---------
PK_TB_IND0101        EMPNO                    1 UNIQUE
IND_TB_IND02         JOB                      2 NONUNIQUE
IND_TB_IND02         ENAME                    1 NONUNIQUE
IND_TB_IND02         MGR                      3 NONUNIQUE

uniqueness 表示唯一性索引(主键自动生成)，还是非唯一性索引(手动生成，未添加唯一性约束)

导致索引失效的原因

1、where语句导致失效

某些select语句中where子句不适用索引，举例说明

1)‘！=’将不适用索引而'>'可以(索引只能查君什么在表中，而不能查询什么不在表里)

SQL> alter session set statistics_level=all;

Session altered.

SQL>

Session altered.

SQL> select empno,ename from tb_ind01 where empno!=0;

EMPNO ENAME
------ ----------
  7369 SMITH
  7499 ALLEN
  7521 WARD
  7566 JONES
  7654 MARTIN
  7698 BLAKE
  7782 CLARK
  7788 SCOTT
  7839 KING
  7844 TURNER
  7876 ADAMS
  7900 JAMES
  7902 FORD
  7934 MILLER

14 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3935626125

------------------------------------------------------------------------------
| Id | Operation         | Name     | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |    14 |   140 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TB_IND01 |    14 |   140 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPNO"<>0)

SQL>  select empno,ename from tb_ind01 where empno>0;

EMPNO ENAME
------ ----------
  7369 SMITH
  7499 ALLEN
  7521 WARD
  7566 JONES
  7654 MARTIN
  7698 BLAKE
  7782 CLARK
  7788 SCOTT
  7839 KING
  7844 TURNER
  7876 ADAMS
  7900 JAMES
  7902 FORD
  7934 MILLER

14 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2928769922

----------------------------------------------------------------------------------
|Id|Operation                           |Name         |Rows|Bytes|Cost(%CPU)|Time    |
----------------------------------------------------------------------------------
| 0|SELECT STATEMENT                    |             | 14 | 140 | 2   (0)| 00:00:01|
| 1| TABLE ACCESS BY INDEX ROWID BATCHED|TB_IND01     | 14 | 140 | 2   (0)| 00:00:01|
|*2|  INDEX RANGE SCAN                  |PK_TB_IND0101| 14 |     | 1   (0)| 00:00:01|
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO">0)

2)'||'字节连接符导致索引失效

SQL> select empno,ename from tb_ind01 where empno||ENAME='7839KING';

EMPNO ENAME
------ ----------
  7839 KING

Execution Plan
----------------------------------------------------------
Plan hash value: 3935626125

------------------------------------------------------------------------------
| Id | Operation         | Name     | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |    10 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TB_IND01 |     1 |    10 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_CHAR("EMPNO")||"ENAME"='7839KING')

2、索引列上使用not

not会产生和在索引列上使用函数相同的影响，因此当oracle遇到not的时候会停止使用索引，转而全表扫描

SQL> select empno,ename from tb_ind01 where empno not in 7839;

EMPNO ENAME
------ ----------
  7369 SMITH
  7499 ALLEN
  7521 WARD
  7566 JONES
  7654 MARTIN
  7698 BLAKE
  7782 CLARK
  7788 SCOTT
  7844 TURNER
  7876 ADAMS
  7900 JAMES
  7902 FORD
  7934 MILLER

13 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3935626125

------------------------------------------------------------------------------
| Id | Operation         | Name     | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |    13 |   130 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TB_IND01 |    13 |   130 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPNO"<>7839)

3、在索引列上使用计算

where子句中，如果索引列是函数的一部分，优化器将不适用索引而转用全表扫描

SQL> select EMPNO from tb_ind01 where empno*10 >7000;

EMPNO
------
  7369
  7499
  7521
  7566
  7654
  7698
  7782
  7788
  7839
  7844
  7876
  7900
  7902
  7934

14 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1033130739

----------------------------------------------------------------------------------
| Id | Operation        | Name          | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |               |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | PK_TB_IND0101 |     1 |     4 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPNO"*10>7000)

SQL> select EMPNO from tb_ind01 where empno>70000/10;

EMPNO
------
  7369
  7499
  7521
  7566
  7654
  7698
  7782
  7788
  7839
  7844
  7876
  7900
  7902
  7934

14 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1997662084

----------------------------------------------------------------------------------
| Id | Operation        | Name          | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |               |    14 |    56 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| PK_TB_IND0101 |    14 |    56 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("EMPNO">7000)

4、复合索引使用顺序错误

SQL> create table TB_IND02(id number(5),name varchar2(5),job varchar2(5),other varchar2(5));

Table created.

SQL> insert into TB_IND02 select 1,'AAA','AAA','OTHER' FROM DUAL;

1 row created.

SQL> insert into TB_IND02 select 2,'BBB','BBB','OTHER' FROM DUAL;

1 row created.

SQL> insert into TB_IND02 select 3,'CCC','CCC','OTHER' FROM DUAL;

1 row created.

SQL> insert into TB_IND02 select 4,'DDD','DDD','OTHER' FROM DUAL;

1 row created.

SQL> insert into TB_IND02 select 5,'EEE','EEE','OTHER' FROM DUAL;

1 row created.

SQL> SELECT * FROM TB_IND02;

        ID NAME JOB   OTHER
---------- ----- ----- -----
         1 AAA   AAA   OTHER
         2 BBB   BBB   OTHER
         3 CCC   CCC   OTHER
         4 DDD   DDD   OTHER
         5 EEE   EEE   OTHER

SQL> create index ind_tb_ind02_01 on tb_ind02(other,id,name);

Index created.

SQL>  select c.index_name,c.column_name,c.column_position,x.uniqueness from user_indexes x,user_ind_columns c  where x.index_name=c.index_name and c.table_name='TB_IND02';

INDEX_NAME           COLUMN_NAM COLUMN_POSITION UNIQUENES
-------------------- ---------- --------------- ---------
IND_TB_IND02_01      ID                       2 NONUNIQUE
IND_TB_IND02_01      NAME                     3 NONUNIQUE
IND_TB_IND02_01      OTHER                    1 NONUNIQUE

SQL> SELECT * FROM TB_IND02 WHERE ID=1 AND name='AAA';

        ID NAME JOB   OTHER
---------- ----- ----- -----
         1 AAA   AAA   OTHER

Execution Plan
----------------------------------------------------------
Plan hash value: 2925895819

------------------------------------------------------------------------------
| Id | Operation         | Name     | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |    25 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TB_IND02 |     1 |    25 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=1 AND "NAME"='AAA')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

SQL> SELECT * FROM TB_IND02 WHERE other='OTHER' AND ID=1 AND name='AAA';

        ID NAME JOB   OTHER
---------- ----- ----- -----
         1 AAA   AAA   OTHER

Execution Plan
----------------------------------------------------------
Plan hash value: 1452223693

--------------------------------------------------------------------------------------------
|Id |Operation                           |Name           |Rows|Bytes|Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|  0|SELECT STATEMENT                    |               |   1|   25|    2   (0)| 00:00:01 |
|  1| TABLE ACCESS BY INDEX ROWID BATCHED|TB_IND02       |   1|   25|    2   (0)| 00:00:01 |
|* 2|  INDEX RANGE SCAN                  |IND_TB_IND02_01|   1|     |    1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OTHER"='OTHER' AND "ID"=1 AND "NAME"='AAA')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

5、改变索引类型

当比较不同数据类型的数据时，oracle对列进行简单的类型转换。

查看TB_IND01表结构

创建测试表tb_ind03并插入数据

SQL> create table tb_ind03 (id1 number(5),id2 varchar2(5));

Table created.

SQL> insert into tb_ind03 values(1,'1');

1 row created.

SQL> insert into tb_ind03 values(2,'2');

1 row created.

SQL> insert into tb_ind03 values(3,'3');

1 row created.

SQL> create index ind_tb_ind0301 on tb_ind03(id1);

Index created.

SQL> create index ind_tb_ind0302  on tb_ind03(id2);

Index created.

SQL> desc tb_ind03;
Name                    Null?    Type
----------------------- -------- ----------------
ID1                              NUMBER(5)
ID2                              VARCHAR2(5)

SQL>  _IND03';

INDEX_NAME           COLUMN_NAM COLUMN_POSITION UNIQUENES
-------------------- ---------- --------------- ---------
IND_TB_IND0301       ID1                      1 NONUNIQUE
IND_TB_IND0302       ID2                      1 NONUNIQUE

id1 字符类型为number 而id2字符类型为varchar2

一下语句

select * from tb_ind03 where id1='1’;

会被转换成

select * from tb_ind03 where id1=to_number('1');

不过查询后貌似没有影响到索引

那么来试试id2吧

select * from tb_ind03 where id2=1;

将被转换成

select * from tb_ind03 where id2=to_number('1');

先看看正常情况，没有问题，用了索引

SQL> select * from TB_IND03 where id2='1';

       ID1 ID2
---------- -----
         1 1

Execution Plan
----------------------------------------------------------
Plan hash value: 372920922

------------------------------------------------------------------------------------------------------
| Id | Operation                           | Name           | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |     1 |    17 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TB_IND03       |     1 |    17 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IND_TB_IND0302 |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID2"='1')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

不用引号呢？果然无论是否使用字符转换，都发生了转换。导致索引失效。

SQL> select * from tb_ind03 where id2=1;

       ID1 ID2
---------- -----
         1 1

Execution Plan
----------------------------------------------------------
Plan hash value: 697787577

------------------------------------------------------------------------------
| Id | Operation         | Name     | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |    17 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TB_IND03 |     1 |    17 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("ID2")=1)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

SQL> select * from tb_ind03 where id2=to_number('1');

       ID1 ID2
---------- -----
         1 1

Execution Plan
----------------------------------------------------------
Plan hash value: 697787577

------------------------------------------------------------------------------
| Id | Operation         | Name     | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |    17 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TB_IND03 |     1 |    17 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("ID2")=1)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

因此一定要注意索引的使用规范

基于函数的索引

测试

创建测试表tbi01定义参照emp，保留数据

SQL> create table tbi01 as select * from emp;

Table created.

SQL> select * from tbi01;

EMPNO ENAME      JOB         MGR HIREDATE              SAL COMM DEPTNO
------ ---------- --------- ----- ------------------- ----- ----- -------
  7369 SMITH      CLERK      7902 1980-12-17 00:00:00   800            20
  7499 ALLEN      SALESMAN   7698 1981-02-20 00:00:00  1600   300      30
  7521 WARD       SALESMAN   7698 1981-02-22 00:00:00  1250   500      30
  7566 JONES      MANAGER    7839 1981-04-02 00:00:00  2975            20
  7654 MARTIN     SALESMAN   7698 1981-09-28 00:00:00  1250 1400      30
  7698 BLAKE      MANAGER    7839 1981-05-01 00:00:00  2850            30
  7782 CLARK      MANAGER    7839 1981-06-09 00:00:00  2450            10
  7788 SCOTT      ANALYST    7566 1987-04-19 00:00:00  3000            20
  7839 KING       PRESIDENT       1981-11-17 00:00:00  5000            10
  7844 TURNER     SALESMAN   7698 1981-09-08 00:00:00  1500     0      30
  7876 ADAMS      CLERK      7788 1987-05-23 00:00:00  1100            20
  7900 JAMES      CLERK      7698 1981-12-03 00:00:00   950            30
  7902 FORD       ANALYST    7566 1981-12-03 00:00:00  3000            20
  7934 MILLER     CLERK      7782 1982-01-23 00:00:00  1300            10

14 rows selected.

如果不知道 scott 的名字大小写的情况下，查询 scott 相关信息

SQL> select * from tbi01 where lower(ename)='scott';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------ ---------- --------- ----- ------------------- ----- ----- -------
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20

查看执行计划

在tbi01表上创建索引

SQL> create index ind_tbi01 on tbi01(ename);

Index created.

SQL> create index ind_tbi01 on tbi01(ename);

Index created.

SQL> select c.index_name,c.column_name,c.column_position,x.uniqueness from user_indexes x,user_ind_columns c where x.index_name=c.index_name and c.table_name='TBI01';

INDEX_NAME COLUMN_NAM COLUMN_POSITION UNIQUENES
---------- ---------- --------------- ---------
IND_TBI01 ENAME 1 NONUNIQUE

先用原始方法测试，生成执行计划

SQL>

explain plan

set statement_id='info 20210107' into plan_table

for select * from tbi01 where ename='SCOTT';

Explained.

SQL>

select lpad(' ',2*(LEVEL-1))||operation operation,options,object_name,position

from plan_table

start with id= 0 AND statement_id = 'info 20210107'

connect by prior id = parent_id

AND statement_id ='info 20210107';

OPERATION            OPTIONS                        OBJECT_NAM   POSITION
-------------------- ------------------------------ ---------- ----------
SELECT STATEMENT                                                        2
  TABLE ACCESS       BY INDEX ROWID BATCHED         TBI01               1
    INDEX            RANGE SCAN                     IND_TBI01           1

如果使用忽略大小写敏感性的查询呢，显示full，未使用索引

SQL> explain plan set statement_id='info 20210107' into plan_table for select * from tbi01 where lower(ename)='scott';

Explained.

SQL> select lpad(' ',2*(LEVEL-1))||operation operation,options,object_name,position from plan_table start with id= 0 AND statement_id = 'info 20210107' connect by prior id = parent_id AND statement_id ='info 20210107';

OPERATION            OPTIONS                        OBJECT_NAM   POSITION
-------------------- ------------------------------ ---------- ----------
SELECT STATEMENT                                                        2
  TABLE ACCESS       FULL                           TBI01               1

那么如何既能使用索引，又忽略大小写呢

就要使用基于函数的索引了

删除并从键索引

SQL> drop index ind_tbi01;

Index dropped.

SQL> create index ind_tbi01 on tbi01(lower(ename));

Index created.

SQL>  explain plan set statement_id='info 2021010702' into plan_table for select * from tbi01 where lower(ename)='scott';

Explained.

SQL> select lpad(' ',2*(LEVEL-1))||operation operation,options,object_name,position from plan_table start with id= 0 AND statement_id = 'info 2021010702' connect by prior id = parent_id AND statement_id ='info 2021010702';

OPERATION            OPTIONS                        OBJECT_NAM   POSITION
-------------------- ------------------------------ ---------- ----------
SELECT STATEMENT                                                        2
  TABLE ACCESS       BY INDEX ROWID BATCHED         TBI01               1
    INDEX            RANGE SCAN                     IND_TBI01           1

删除索引

SQL> drop index ind_tbi01;

Index dropped.

如果是因为主键或者唯一性约束而产生的索引，再删除相应约束或者删除表的时候，索引也会自动删除。

数据库

文章转载自LittleZhao，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

【SQL】索引 INDEX

提示

1、创建索引

索引的优缺点

2、修改索引

导致索引失效的原因

基于函数的索引

删除索引

评论