索引，使用索引可快速訪問資料庫表中的特定資訊。索引是對資料庫表中一列或多列的值進行排序的一種結構。

在關聯式資料庫中，索引是一種與表有關的資料庫結構，它可以使對應於表的語句執行得更快。索引的作用相當於圖書的目錄，可以根據目錄中的快速找到所需的內容。當表中有大量記錄時，若要對錶進行查詢，第一種搜尋資訊方式是全表搜尋，是將所有記錄一一取出，和查詢條件進行一一對比，然後返回滿足條件的記錄，這樣做會消耗大量時間，並造成大量磁碟I/O操作；第二種就是在表中建立索引，然後在索引中找到符合查詢條件的索引值，最後透過儲存在索引中的ROWID（相當於頁碼）快速找到表中對應的記錄。

索引是一個單獨的、物理的資料庫結構，它是某個表中一列或若干列值的集合和相應的指向表中物理標識這些值的資料頁的邏輯清單。

索引提供指向儲存在表的指定列中的資料值的指標，然後根據您指定的排序順序對這些指標排序。資料庫使用索引的方式與您使用書籍中的索引的方式很相似：它搜尋索引以找到特定值，然後順指標找到包含該值的行。

點評：索引是提高語句效能的關鍵，而且不用修改程式！

該實驗的目的是深刻體會索引對資料庫的巨大影響。

索引在資料庫中是很重要的。沒有索引的資料庫是不可想象的,我們普通的表是無序的,也叫做堆表(heap table),一句話概括索引,索引是有序的結構,透過索引可以快速定位我們要找的行,避免全表掃描。索引的訪問模式有八種。

1.INDEX UNIQUE SCAN 效率最高，主鍵或唯一索引，走樹結構。

2.INDEX FAST FULL SCAN 讀所有塊，可以並行訪問索引，但輸出不按順序。

3.INDEX FULL SCAN 有順序的輸出，不能並行讀索引，走連結串列結構。

4.INDEX RANGE SCAN 給定的區間查詢，最常見的訪問模式。

5.INDEX SKIP SCAN 聯合索引的第二列為條件，不同值越少的列，越要放在前面。

6. SCAN DESCENDING 降序掃描，自動選擇降序使用索引。

7. index join 索引的連線，透過索引獲得全部資料，可以不掃描表。

8. bitmap join 索引的點陣圖連線，多個條件上的列都有索引的情況。

SQL> conn scott/tiger

Connected.

SQL> drop table t1 purge;

Table dropped.

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> analyze table t1 compute statistics;

收集表t1的統計資訊

Table analyzed.

SQL> create unique index i2t1 on t1(object_id);

Index created.

SQL>set autot traceonly explain

1.INDEX UNIQUE SCAN 效率最高，主鍵或唯一索引

SQL> select * from t1 where object_id=9999;

Execution Plan

----------------------------------------------------------

Plan hash value: 1026981322

------------------------------------------------------------------------------------

------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 87 | 2 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | I2T1 | 1 | | 1 (0)| 00:00:01 |

執行計劃為唯一定位,最快。

2.INDEX FAST FULL SCAN 讀的最塊，可以並行訪問索引，但輸出不按順序

SQL> select object_id from t1 ;

Execution Plan

----------------------------------------------------------

Plan hash value: 3617692013

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 49859 | 194K| 337 (1)| 00:00:07 |

| 1 | TABLE ACCESS FULL| T1 | 49859 | 194K| 337 (1)| 00:00:07 |

為什麼沒有使用索引,而進行了全表掃描。因為object_id可能有null值。因為null不入普通索引。我們進行全索引的掃描就會得到錯誤的結果。這是全表掃描是正確的。雖然我們的的查詢僅包含了索引中的值。

我們如果有非空約束就會極大的提高效能。

SQL> delete t1 where object_id is null;

SQL> alter table t1 modify (OBJECT_ID not null);

SQL> select object_id from t1 ;

Execution Plan

----------------------------------------------------------

Plan hash value: 2003301201

-----------------------------------------------------------------------------

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 49859 | 194K| 51 (2)| 00:00:02 |

| 1 | INDEX FAST FULL SCAN| I2T1 | 49859 | 194K| 51 (2)| 00:00:02 |

計劃僅掃描了索引,代價為51.因為所有的行都在索引中了,使用索引不會造成錯誤的結果。因為我們的輸出沒有要求有序,所以資料庫將高水位下所有的索引塊都讀一遍就可以了,這就叫索引的快速全掃描。

3.INDEX FULL SCAN 有順序的輸出，不能並行讀索引

SQL> select object_id from t1 order by object_id ;

Execution Plan

----------------------------------------------------------

Plan hash value: 1111347323

-------------------------------------------------------------------------

-------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 49859 | 194K| 106 (2)| 00:00:03 |

| 1 | INDEX FULL SCAN | I2T1 | 49859 | 194K| 106 (2)| 00:00:03 |

-------------------------------------------------------------------------

執行計劃為全掃描索引,含義是按葉子的大小順序來讀索引,因為我們要求輸出是有序的。

代價為106,高於快速全掃描,因為我們不是將高水位的塊連續讀,而是按照葉子的順序讀。正因為是按照葉子的順序讀,所以不能並行操作。

4.INDEX RANGE SCAN 給定的區間查詢

SQL> select * from t1 where object_id between 300 and 400;

Execution Plan

----------------------------------------------------------

Plan hash value: 1490405106

------------------------------------------------------------------------------------

------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 93 | 8091 | 4 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 93 | 8091 | 4 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | I2T1 | 93 | | 2 (0)| 00:00:01 |

------------------------------------------------------------------------------------

當我們的索引為非唯一,或者我們的索引唯一但查詢的條件為一個範圍的時候資料庫會選擇範圍定位。

代價的大小取決於你所查詢行的多少。

5.INDEX SKIP SCAN 聯合索引，不同值越少的列，越要放在前面

在下一個實驗聯合索引中有詳細的描述。

資料庫的主鍵,唯一約束和外來鍵的使用也要索引的參與。

SQL> drop table t2 purge;

Table dropped.

SQL> create table t2 as select distinct owner from dba_objects;

建立一個含有owner的表。

Table created.

SQL> alter table t2 add constraint pk_t2 primary key (owner);

建立主鍵

Table altered.

SQL> alter table t1 add constraint fk_t1 foreign key (owner)

references t2(owner) on delete cascade;;

建立一個級聯刪除的外來鍵

Table altered.

SQL>DELETE T2 WHERE OWNER='SYS';

檢視計劃,我們發現

Rows Row Source Operation

------- ---------------------------------------------------

1 DELETE T2 (cr=726 pr=0 pw=0 time=16740731 us)

1 INDEX UNIQUE SCAN PK_T2 (cr=1 pr=0 pw=0 time=52 us)(object id 54503)

Rows Row Source Operation

------- ---------------------------------------------------

0 DELETE T1 (cr=725 pr=0 pw=0 time=16714571 us)

22984 TABLE ACCESS FULL T1 (cr=690 pr=0 pw=0 time=160995 us)

在刪除t2的同時,要全表掃描t1表,因為我們建立了外來鍵。如果在外來鍵上有索引,那麼就很可能走索引,會極大的提高資料庫的效能。

6. SCAN DESCENDING 降序使用索引

SQL> SELECT * FROM EMP ORDER BY 1 DESC;

執行計劃

----------------------------------------------------------

Plan hash value: 3088625055

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 546 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 546 | 2 (0)| 00:00:01 |

| 2 | INDEX FULL SCAN DESCENDING| PK_EMP | 14 | | 1 (0)| 00:00:01 |

7. index join

SQL> spool c:\1.txt

SQL> conn scott/tiger

已連線。

SQL> drop table t1 purge;

表已刪除。

SQL> create table t1 as select * from dba_objects;

表已建立。

SQL> create index i1 on t1(object_name);

索引已建立。

SQL> create index i2 on t1(object_type);

索引已建立。

SQL> exec dbms_stats.gather_table_stats('SCOTT','T1');

PL/SQL 過程已成功完成。

SQL> set autot trace expl

SQL> SELECT OBJECT_NAME,OBJECT_TYPE FROM T1

2 WHERE OBJECT_NAME LIKE 'E%' AND OBJECT_TYPE='TABLE';

執行計劃

----------------------------------------------------------

Plan hash value: 1423132391

---------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 21 | 714 | 11 (10)| 00:00:01 |

|* 1 | VIEW | index$_join$_001 | 21 | 714 | 11 (10)| 00:00:01 |

|* 2 | HASH JOIN | | | | | |

|* 3 | INDEXRANGESCAN| I2 | 21 | 714 | 5 (0)| 00:00:01 |

|* 4 | INDEXRANGESCAN| I1 | 21 | 714 | 6 (17)| 00:00:01 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_TYPE"='TABLE' AND "OBJECT_NAME" LIKE 'E%')

2 - access(ROWID=ROWID)

3 - access("OBJECT_TYPE"='TABLE')

4 - access("OBJECT_NAME" LIKE 'E%')

8. bitmap join

SQL> SELECT COUNT(*) FROM T1 WHERE OBJECT_NAME LIKE 'E%' OR OBJECT_TYPE='TABLE';

執行計劃

----------------------------------------------------------

Plan hash value: 1019523335

-----------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 34 | 11 (10)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 34 | | |

| 2 | BITMAP CONVERSION COUNT | | 2172 | 73848 | 11 (10)| 00:00:01 |

| 3 | BITMAP OR | | | | | |

| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |

|* 5 | INDEX RANGE SCAN | I2 | | | 5 (0)| 00:00:01 |

| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |

| 7 | SORT ORDER BY | | | | | |

|* 8 | INDEX RANGE SCAN | I1 | | | 5 (0)| 00:00:01 |

-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

5 - access("OBJECT_TYPE"='TABLE')

8 - access("OBJECT_NAME" LIKE 'E%')

filter("OBJECT_NAME" LIKE 'E%' AND "OBJECT_NAME" LIKE 'E%')

索引使用總論:

能用唯一索引，一定用唯一索引

能加非空，就加非空約束

一定要統計表的資訊，索引的資訊，柱狀圖的資訊。

聯合索引的順序不同，影響索引的選擇，儘量將不同值少的列放在前面

如果你需要的列都存在於索引中，那麼資料庫只需要查詢索引而不去查詢表，大大提高系統的效能。

只有做到以上四點，資料庫才會正確的選擇執行計劃。

索引是在不修改程式碼的情況下提高效能的重要手段。索引也是約束的維護紐帶。在外來鍵上最好建立索引。

引數optimizer_index_cost_adj定義了索引的權重,該值越大,資料庫認為使用索引的成本越高,預設值為100,如果設定為50,那麼資料庫認為使用索引的代價比它計算出來的少一半,如果你設定為1000,那麼認為你使用索引的成本為計算出來的10倍,該值最大為10000,最小為1.

SQL> conn scott/tiger

Connected.

SQL> set autot on

SQL> alter session set optimizer_index_cost_adj = 100;

SQL> select * from emp order by empno;

這句話可以走索引,也可以不走索引。預設為100的情況。

Execution Plan

----------------------------------------------------------

Plan hash value: 4170700152

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 532 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 532 | 2 (0)| 00:00:01 |

| 2 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 |

--------------------------------------------------------------------------------------

走了索引

SQL> select * from emp where empno between 1 and 1000;

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 169057108

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 38 | 4 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 4 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | PK_EMP | 1 | | 2 (0)| 00:00:01 |

我們做了一個區間的範圍查詢,走了索引,進行了索引的範圍查詢。

SQL> alter session set optimizer_index_cost_adj=1000;

Session altered.

將該引數的值改為1000,資料庫評估索引的時候認為成本很高。

SQL> select * from emp order by empno;

Execution Plan

----------------------------------------------------------

Plan hash value: 150391907

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 532 | 5 (20)| 00:00:01 |

| 1 | SORT ORDER BY | | 14 | 532 | 5 (20)| 00:00:01 |

| 2 | TABLE ACCESS FULL| EMP | 14 | 532 | 4 (0)| 00:00:01 |

---------------------------------------------------------------------------

所以改為全表掃描了。

SQL> select * from emp where empno between 1 and 1000;

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 38 | 4 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| EMP | 1 | 38 | 4 (0)| 00:00:01 |

認為索引的成本高,改為全表掃描了。

索引的八種使用模式

相關文章