當(dāng)前位置：首頁 > IT技術(shù) > 數(shù)據(jù)庫 > 正文

Oracle之索引
2021-09-06 19:04:34

索引是關(guān)系型數(shù)據(jù)庫的一個(gè)重要的優(yōu)化手段，可以極大地提高數(shù)據(jù)的查詢效率。Oracle作為關(guān)系型數(shù)據(jù)庫也不能免俗。

注：雖然索引專注于查詢效率，但是索引也存在一定弊端。索引會(huì)在數(shù)據(jù)表文件之外專門建立一份字段的映射文件(文件包括索引字段每行的內(nèi)容和對(duì)應(yīng)rowid)，它會(huì)隨數(shù)據(jù)的增加而增加。另外，一張表不是越多索引越好，索引越多維護(hù)起來也會(huì)很麻煩，對(duì)一些數(shù)據(jù)表結(jié)構(gòu)的操作如交換分區(qū)等也很讓人頭疼。

還有索引字段不能出現(xiàn)null值，出現(xiàn)null值，查詢時(shí)不會(huì)經(jīng)過索引的。

Oracle中的索引有如下幾種：

Btree索引、位圖索引、函數(shù)索引、反向索引、降序索引、interMedia全文索引等


索引

Btree索引

基礎(chǔ)(默認(rèn))索引，最常見的索引。類似于二叉樹結(jié)構(gòu)(非二叉樹)，通過rowid快速定位記錄。Btree索引很適合于字段內(nèi)容重復(fù)率相當(dāng)?shù)偷那樾?。?dāng)查詢目標(biāo)數(shù)據(jù)占全表總數(shù)據(jù)的一小部分時(shí)，Btree提供的效率時(shí)高于全表檢索的；但是當(dāng)查詢目標(biāo)占全表總數(shù)據(jù)的10%，Btree索引的效率達(dá)到瓶頸。

Btree的結(jié)構(gòu)中每個(gè)節(jié)點(diǎn)存放索引列值范圍和子節(jié)點(diǎn)索引鍵值位置，最終葉子結(jié)點(diǎn)才是存放索引值。所以可以知道一點(diǎn)：Btree索引的高度是一次查詢的最大查詢次數(shù)，遠(yuǎn)低于全表檢索。

對(duì)于范圍查詢，不需要分兩次從根結(jié)點(diǎn)開始查，可以先定位第一個(gè)條件范圍節(jié)點(diǎn)再在這個(gè)節(jié)點(diǎn)橫向定位第二個(gè)條件范圍。

位圖索引

使用位圖管理數(shù)據(jù)記錄的關(guān)系。與Btree索引相反，適合于字段重復(fù)率高的情形，最好內(nèi)容枚舉。比如性別，只有男女兩個(gè)選項(xiàng)。

位圖索引基于位圖，位圖是一種鍵值形式(類似二維數(shù)組)，橫向表示數(shù)據(jù)行，縱向表示有限的值選項(xiàng)，每行根據(jù)值圈定對(duì)應(yīng)值選項(xiàng)為1(true)，其他選項(xiàng)為0(false)。這種形式的位圖存放在Btree結(jié)構(gòu)的葉子結(jié)點(diǎn)，查詢相當(dāng)快速便捷。而且，位圖是以一種壓縮格式保存，還不會(huì)占用太大空間。

create bitmap index indexName on table(col);

而且面對(duì)查詢索引列值，Oracle內(nèi)部也會(huì)將位圖中的信息轉(zhuǎn)換為rowid獲取值。

另外位圖索引不應(yīng)該用于頻繁修改的字段，因?yàn)槲粓D索引不支持行級(jí)鎖定，所以當(dāng)更改某條記錄的索引字段值時(shí)，其他同等值的字段都將被鎖定，除非commit，否則其他需要更改的用戶操作就不能執(zhí)行。

函數(shù)索引

對(duì)函數(shù)建立索引，當(dāng)以該函數(shù)作為篩選條件時(shí)可以提高查詢效率。因?yàn)楹瘮?shù)具有計(jì)算的能力并且容易使用，可以不修改程序邏輯(邏輯可以編入到自定義函數(shù)中)就提高查詢效率。

但是使用函數(shù)索引需要滿足條件：

  1. 基于成本優(yōu)化器(cost)，否則將被忽略。
  2. 必須要有query rewrite 和 global query rewrite 權(quán)限
  3. 設(shè)置系統(tǒng)參數(shù)：QUERY_REWRITE=TRUE; QUERY_REWRITE_INTEGRITY=TRUSTED?？梢栽趇nit.ora文件中修改；也可以通過alter system/session set xxx=xxx來更改。

舉例：

create index test.ind_fun on test.testindex(upper(a));
insert into testindex values('a',2);
commit;

select /*+ RULE*/* from test/testindex where upper(a) = 'A'; //臨時(shí)使用規(guī)則優(yōu)化器會(huì)發(fā)現(xiàn)查詢未使用函數(shù)索引
A　　     B
-- ----------
a　　     2
Execution Plan
----------------------------------------------------------
   0　　SELECT STATEMENT Optimizer=HINT: RULE
   1    0   TABLE ACCESS (FULL) OF 'TESTINDEX'
--------------------------------------------------------------------

select * from test.testindex where upper(a) = 'A';  //使用默認(rèn)成本優(yōu)化器會(huì)發(fā)現(xiàn)函數(shù)索引生效
A　　     B
-- ----------
a　　     2
Execution Plan
----------------------------------------------------------
   0　　SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=5)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX' (Cost=2 Card=
　　  1 Bytes=5)
   2    1     INDEX (RANGE SCAN) OF 'IND_FUN' (NON-UNIQUE) (Cost=1 Car
　　    d=1)
--------------------------------------------------------------------

反向索引

將Btree索引中的字節(jié)反轉(zhuǎn)，可以均勻分配索引條目，適用于并行服務(wù)器，可以有效減少索引葉的競(jìng)爭(zhēng)。

想象一下，如果Btree索引列恰好有一組值遞增的記錄，則它們按范圍分大概率會(huì)分到一棵子樹下，這樣當(dāng)多個(gè)查詢或修改同時(shí)操作對(duì)應(yīng)的數(shù)據(jù)，可能會(huì)對(duì)這棵子樹進(jìn)行爭(zhēng)搶。反向索引會(huì)將索引碼反轉(zhuǎn)將數(shù)據(jù)打散均勻到不同位置，減少爭(zhēng)搶的可能性。

下面可以看到索引碼反轉(zhuǎn)后相鄰值的索引碼相差甚遠(yuǎn)

select 'number',dump(1,16) from dual
union all select 'number',dump(2,16) from dual
union all select 'number',dump(3,16) from dual;


select 'number',dump(reverse('1'),16) from dual
union all select 'number',dump(reverse('2'),16) from dual
union all select 'number',dump(reverse('3'),16) from dual;
//
number Typ=2 Len=2: 2,c1
number Typ=2 Len=2: 3,c1
number Typ=2 Len=2: 4,c1

但是反向索引不能建立在已經(jīng)建立其他索引的字段上，因?yàn)椴粫?huì)生效。這也是必然的，畢竟它是與Btree相反的。

降序索引

面向逆序查詢的索引。一般面對(duì)逆序查詢，數(shù)據(jù)查出來之后會(huì)經(jīng)過一個(gè)排序的過程，如果使用降序索引，會(huì)跳過排序。

舉例：

select * from test where a between 1 and 100 order by a desc,b asc;
Execution Plan
----------------------------------------------------------
   0    SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=100 Bytes=400)
   1  0  SORT(ORDER BY)(Cost=2 Card=100 Bytes=400)
   2  1 INDEX (RANGE SCAN) OF 'IND_BT' (NON-UNIQUE) (Cost=2 Card=100 Bytes=400)
---------------------------------------------------------------

create index test.ind_desc on test.testrev(a desc,b asc);
commit;

select * from test where a between 1 and 100 order by a desc,b asc;
Execution Plan
----------------------------------------------------------
   0    SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=100 Bytes=400)
1  0 INDEX (RANGE SCAN) OF 'IND_DESC' (NON-UNIQUE) (Cost=2 Card=100 Bytes=400)
----------------------------------------------------------------

注：安裝Oracle時(shí)要保證compatible參數(shù)為8.1.0及以上，否則創(chuàng)建索引時(shí)desc關(guān)鍵字會(huì)被忽略。

索引掃描

基于成本的優(yōu)化器(cost)會(huì)根據(jù)統(tǒng)計(jì)數(shù)值的方式推斷當(dāng)索引掃描比全表掃描更有效時(shí)就采用索引掃描。且查詢結(jié)果完全由索引得到。

Oracle中有4種索引掃描方式，主要有where的篩選條件選擇

在實(shí)際執(zhí)行語句前，可以通過執(zhí)行計(jì)劃(explain plan)查看該語句是否使用了索引掃描以及使用了哪種掃描和花費(fèi)時(shí)間等。

遠(yuǎn)程登錄工具(如plsql developer)一般會(huì)自帶執(zhí)行計(jì)劃按鈕，在命令行窗口需要自行開啟。

索引唯一掃描(index unique scan)

一般預(yù)期返回一條記錄的篩選條件會(huì)觸發(fā)唯一掃描，包括一個(gè)字段(單列索引)和多個(gè)字段(組合索引)。

唯一掃描也是比較常用的一種索引掃描。

create index index_xxx on tableName(name,class,school);

select * from tableName where name='xxx' and class='xxx' and school='xxx';

注：組合索引想使用索引掃描必須保證條件包含左邊字段。如組合索引(col1,col2,col3)，則只有條件 where col1=xxx where col1=xx and col2=xx where col1=xx and col2=xxx and col3=xx 這三種情況才能索引掃描生效。

對(duì)個(gè)單個(gè)字段，如果出現(xiàn)unique或primary key等保證唯一性的約束，也是可以使用系統(tǒng)的唯一掃描。

索引范圍掃描(index range scan)

面向組合索引，但是預(yù)期結(jié)果是多行記錄。比較典型的有通過 < ,> ,<> , between and等篩選條件。

另外在所有非唯一索引上也都會(huì)使用范圍掃描。

索引全掃描(index full scan)

對(duì)應(yīng)全表掃描，實(shí)際上就是從左到右挨個(gè)掃描索引樹的每個(gè)葉子索引，出來的結(jié)果是有序的。

原理是從根結(jié)點(diǎn)先定位至索引樹最左葉子(樹遍歷不難),然后由節(jié)點(diǎn)的雙向鏈表依次向右掃描其他葉子。

但是這種掃描方式是基于成本優(yōu)化器(CBO)，因?yàn)樾枰鶕?jù)統(tǒng)計(jì)值比較決定是否使用全掃描還是全表掃描。

索引快速掃描(index fast full scan)

與全掃描類似，只是這種掃描是并行掃描索引塊，目的是大吞吐量和短時(shí)間。因此不會(huì)照顧到查詢結(jié)果是否有序。

全表掃描 vs 索引掃描

全表掃描就是一條一條訪問每條記錄，雖然Oracle采取一次讀入多個(gè)數(shù)據(jù)塊方式優(yōu)化，但是對(duì)于大數(shù)據(jù)量來說效率仍然低下。

索引掃描是采用基于rowid方式訪問數(shù)據(jù)，直接接觸物理內(nèi)存地址，效率很高。Oracle實(shí)現(xiàn)了數(shù)據(jù)內(nèi)容與物理地址的聯(lián)系，而索引就是實(shí)現(xiàn)快速訪問rowid。

對(duì)于索引掃描范圍唯一掃描和其他掃描，首先會(huì)通過唯一掃描刷掉一批，剩下再通過其他索引掃描。

PS：索引操作

創(chuàng)建索引

create index index_name on tableName(col/function/...);

空值不能被索引

一張表不必建立多個(gè)索引，否則適得必反

修改索引

alter index index_name rebuild storage(initial 1m next 512k);//重構(gòu)存儲(chǔ)
alter index index_name rebuild reverse;//(https://www.imooc.com/article/279505)
alter index index_name coalesce;//重構(gòu)合并索引無用空間

oracle中修改索引的概念是重構(gòu)索引以保證適應(yīng)索引存儲(chǔ)參數(shù)的增長(zhǎng)和數(shù)據(jù)的增加和清除無用的空間

rebuild相當(dāng)于truncate，經(jīng)歷了一個(gè)刪除重新建立的過程。

刪除索引

drop index index_name;

查看索引

Oracle中的系統(tǒng)表user_indexes和user_ind_columns存有當(dāng)前用戶下的表的索引信息

select * from user_indexes/user_ind_columns where table_name='表名大寫'

本文摘自 ：https://www.cnblogs.com/

開通會(huì)員，享受整站包年服務(wù)立即開通 >
推薦內(nèi)容