輕松掌握Oracle索引（一）

1????? 引言

業務系統都需要數據庫，而數據庫都離不開索引。大部分的數據庫性能問題是由于索引使用不當導致的，因此索引的設計和使用非常重要。開發者在開發SQL和設計表時如果不考慮索引，或者隨便添加索引，將給系統埋下性能隱患。

為什么查詢這么慢，CPU這么高？

用了索引為什么還是這么慢？

應該用這個索引還是那個索引？

應該全表掃描還是索引掃描？

為什么明明有這個索引就是用不上？

應該創建單字段索引還是組合索引，組合索引是不是把查詢用到的列都用上？

索引范圍掃描、全索引掃描、快速全索引掃描、索引跳躍掃描，這么多索引掃描方式有什么不同，應該如何選擇？

……

初學者遇到這些問題往往一頭霧水，不知從何下手。這些問題大都跟索引相關，需要有索引的相關知識才能解釋清楚。

首先，必須了解索引和表的內部結構，知道數據在表和索引中是如何存放的；其次，需要知道每種訪問方式的不同特點，包括全表掃描、索引范圍掃描、全索引掃描等。再擴展一些，還需要了解組合索引、函數索引和表的連接方式等技術。

大家不要被這些專用名詞嚇倒，只要掌握原理，多實踐和思考，必能融會貫通、游刃有余。本文會用通俗的文字結合實例來講解Oracle索引的原理，希望你讀完后能有豁然開朗的感覺。

2????? 索引的結構

2.1????? 基礎概念

為了掌握索引的原理，首先需要掌握一些基本概念。

（1）索引和表在磁盤上是存放在一起的還是分開的？

（2）一個表上的所有索引是在一起存放的還是獨立的？

（3）索引和表在磁盤上的存放方式有什么不同？

（4）索引和表之間是如何關聯的？

（5）索引的作用是什么？

2.1.1??????? 索引和表是獨立的對象

Oracle中有很多種對象，包括表、索引、存儲過程、觸發器、序列、視圖等。這些對象都有自己的定義語句，保存在數據字典（Oracle系統創建的表）中。對于表和索引，數據字典中除了需要保存對象的定義，還需要保存對象的數據。

索引和表是獨立的對象，它們分別擁有自己的存儲空間，并不是放在一起的，所以它們可以存放在不同的表空間（每個表空間是一組數據文件的集合）中。一個表上如果有多個索引，那每個索引也是獨立的對象，擁有自己的存儲空間。兩個索引之間是沒有關系，好比字典的拼音索引和部首索引，在編排和使用時都是相互獨立的 。

2.1.2??????? 索引和表在磁盤上的存放方式

表和索引的數據都存放在磁盤的數據塊中，每個數據塊一般固定為8K。數據塊是數據讀寫的最小單位，即使只需要讀取一條數據，也會把整塊的數據讀到內存中，寫數據也一樣。

表中的數據是無序的存放在數據庫塊中的，兩個數據塊完全沒有關系，同一個數據塊中的數據也沒有關系。數據在表中的順序也不一定是錄入的順序，后錄入的數據可能在前面。

如果想從表中查詢某條數據，在沒有索引的情況下就只能掃描表的所有數據塊了，即全表掃描。這就好比一本書沒有目錄，要找某個內容只能把整本書翻一遍。

與表的無序存放不同，索引是有序存放的。在存放索引的數據塊上，數據都是有序的，并且數據塊之間使用指針進行關聯。

2.1.3??????? 索引和表之間是如何關聯的

索引中保存：索引的鍵值（索引列的值）；數據在表中的地址（rowid）。

查字典時，根據拼音索引可以找到某個字的頁碼然后直接到該頁查看詳細內容。Oracle索引與查字典也很類似，如果要找userid=10的數據，那么就到userid索引中先找到目標數據的地址（rowid），然后根據數據地址到表中查看目標數據的詳情。

那么，如果要找username=’abc’的數據呢，userid的索引就不好用了，需要使用username的索引了。這就好比你只知道漢字的寫法就不能使用拼音檢索，而只能用部首查字法了。

2.1.4??????? 索引的作用

使用索引是為了快速找到目標數據。因為全表掃描很慢，這個前面說過。

只要你有查字典的經歷，你就應該明白這個道理。一般而言，先根據拼音查到漢字的頁碼比直接翻字典內容快。有人可能會說，我查字典都是根據拼音直接翻字典的，也很快啊。那是因為漢語詞典的內容也是按照拼音排序的。如果只知道一個漢字的寫法，而不知道拼音，那么要從字典中找到這個字就沒有那么快了吧？這時候，根據部首查到頁碼再查內容就快的多了。

重要知識點

數據塊是數據讀寫的最小單位。

在表中數據是無序的堆放在一起的，而在索引中，數據塊之間使用指針關聯起來的，每個數據塊中的數據也是有序的。

索引中只保存索引鍵的值而不保存整條數據，而且索引中還保存了數據在表中的地址。

使用索引的作用是為了快速查找到目標數據。

2.2????? B+索引結構

Oracle有多種類型的索引，最常用的是B+樹索引。如下圖所示，這就是一個B+樹索引的例子。不用管B+是什么意思，它就是一種樹形結構，每個節點是一個數據塊，每個父節點可以有多個子節點。

2.2.1??????? 根節點、分支節點和葉子節點

與表不同的是，索引的數據塊會組織成一個樹形結構，索引塊之間以指針的形式鏈接（所謂指針就是保存了對方的地址）。索引樹有根節點塊、分支節點塊和葉子節點塊。圖中綠色部分表示表的數據，不是索引的結構，綠色部分實際是無序的，不要以為數據在表中也是排序的。

每個索引只有一個根節點塊，但可以有多個分支節點塊和葉子節點塊。索引的層高是從根節點到葉子節點的高度。每個索引可以包含多層分支節點，但只能包含一層葉子節點。當索引層高是1時，索引只有一個根節點，并且根節點也是葉子節點。

索引的所有葉子節點在同一層，這一點不太好理解，記住這個結論即可。

2.2.2??????? 索引塊的內容

每個索引塊包含N條數據。與表不同，它不需要包含一條完整的數據，只需要包含該條數據中索引的鍵值。例如，在userid列上創建的索引就只需要保存userid的值，而不用保存username的值。

除了鍵值外，每條索引數據還需要保存一個指針/地址，指向下一級的索引塊或數據塊。對于根節點和分支節點，它需要根據這個指針找到對應的子節點。對于葉子節點，它需要根據這個指針找到對應的數據地址，它保存的指針實際上就是rowid——數據在表中的地址。

在葉子節點中，每個數據塊還會保存相鄰的葉子節點的地址，這點對索引掃描非常重要，后面會詳細解釋。在每一層索引節點和每個索引塊中，索引的鍵值都是有序的，默認是升序。

重要知識點

索引樹有根節點塊、分支節點塊和葉子節點塊。索引的所有葉子節點在同一層。

每個父節點中如果有N條數據，那么就有N個指針指向子節點。

在葉子節點中，每個數據塊還會保存相鄰的葉子節點的地址。

在每一層索引節點和每個索引塊中，索引的鍵值都是有序的，默認是升序。

2.3????? 表和索引的存儲分析

表和索引結構對開發者來說是看不到的，很多人缺少一個感性認識：

（1）在表中，一個數據塊能放多少條數據？

（2）在索引中，一個數據塊能放多少條數據？

（3）索引的層高一般是多少？

（4）索引和表誰占的空間大？

我們可以找一個數據量比較大的表來分析下表和索引內部特征。通過下面的語句可以收集表的統計信息，從而了解表和索引的內部情況。

SQL> exec dbms_stats.gather_table_stats(ownname => user, tabname =>? 't_userserviceinfo');

2.3.1??????? 表的存儲結構分析

從統計信息查詢可以看到，該表有217萬條數據，占用了53912個數據塊，平均每行的字節數是164，平均每個塊中存放了40個行。

SQL> select num_rows "數據行數", blocks "數據塊數", avg_row_len "平均每行的字節

數", trunc(num_rows/blocks) "平均每個塊的行數" from user_tables x where x.table_

name = 'T_USERSERVICEINFO';

數據行數?? 數據塊數 平均每行的字節數 平均每個塊的行數

---------- -----? --- ---------? ------ ---------------- ?----------------------------

2170606????? 53912????????????? 164?????????????? 40

一個表占用多少數據塊由多方面的因素決定：

（1）數據行數，數據越多占用的空間越大。

（2）每條數據的長度，如果表的列很少，并且每個列的長度都很短，那么表占用的數據塊就少，反之則占用的數據塊多。

（3）如果很多數據被delete了，數據占用的數據塊并不會被釋放，這就是所謂的高水位，當有新數據insert時會優先占用空閑的數據塊。

我們還可以計算出平均每個數據塊被數據占用了多少字節：164*40=6560。數據塊的大小是8K，其中6K存放的是數據，這是正常的，因為數據塊還會有一些系統開銷，而且數據塊也會預留一部分空間（默認10%）以便數據被update的更長。

2.3.2??????? 索引的存儲結構分析

下面再來看看這個表上索引的情況。

SQL> select x.index_name, num_rows "數據行數", blevel +1"層高", leaf_blocks "葉子塊數",

2???????? trunc(num_rows/decode(leaf_blocks, 0, 1, leaf_blocks)) "平均每個葉子塊的行數", x.avg_leaf_blocks_per_key "每個鍵值占的葉子塊數", x.avg_data_blocks_per_key "每個鍵指向的表塊數"

3??? from user_indexes x

4?? where x.table_name = 'T_USERSERVICEINFO';

INDEX_NAME ???????????????????????數據行數 ??層高 ??葉子塊數? 平均每個葉子塊的行數? 每個鍵值占的葉子塊數? 每個鍵指向的表塊數

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

IX_USERSERVICEINFO_FBEOPSTATUS????????? 0?? ?1????????? 0???????? 0???????? 0??????? 0

IX_USERSERVICEINFO_FBECOSTATUS????????? 0??? 1????????? 0???????? 0???????? 0??????? 0

IX_USERSERV_CHANGETIME??????????? 1727428??? 3?????? 4625?????? 373???????? 1???? ???1

IX_USERSERVICEINFO_UPDATE???????? 2159099???? 3?????? 5787?????? 373???????? 1??????? 1

IX_USERSERVICEINFO_RESERVETIME??? 2152584??? 3?????? 5761?????? 373???????? 1??????? 4

IX_USERSERVICEINFO_PHONE????????? ?2124917??? 3?????? 6287?????? 337??????? ?1??????? 1

IX_USERSERVICEINFO_ID???????????? ???2253141?? 3?????? 4994?????? 451???????? 1??????? 1

IX_USERSERVICEINFO_BRANDMODDA??? 2099463?? 3?????? 5600?????? 374???????? 4????? 151

PK_T_USERSERVICEINFO???????????? ????2122099 ??3?????? 6869?????? 308 ????????1??????? 1

IX_USERSERVICEINFO_FSUSPSTATUS??????? 9811?? 2???????? 20?????? 490??????? 20???? 8729

IX_USERSERVICEINFO_FOPENSTATUS???????? 15??? 1????????? 1??????? 15???????? 1?????? 15

IX_USERSERVICEINFO_FDELSTATUS?????????? 0??? 1????????? 0????? ???0???????? 0??????? 0

（1）同一個表的索引存的數據并不一樣多：索引中不包含空的鍵值

對于字典來說，不管是拼音索引還是部首索引，保存的漢字是一樣多的。但是在Oracle索引卻不是這樣。通過上面的查詢結果可以看到，每個索引的數據量都不一樣，有的甚至是0。除了統計信息的誤差外，主要的原因是索引中不包含空的鍵值。例如，對于username索引，如果某條數據的username是空，那么索引中就沒有這條數據。

如果某個索引的大部分鍵值是空值，那么它占用的索引塊就很少。這個特性也告訴我們，查找空的鍵值是不能走索引的。比如，如果想找username為空的數據，那么是不能使用username索引的，因為該索引中根本不包含這種數據。

（2）索引的層高不會太高

對于200多萬數據的表，索引的層高只有3，為什么這么小呢？

我們可以算一下一個3層索引大概可以存放多少索引數據。從上面的查詢可以看到一個索引塊大概能存放400個左右的數據。當索引只有1層時，可以保存400條數據。因為每個索引數據都有一個指向子節點的指針。所以當索引有3層時，最大可以容納400*400*400=6400萬條數據。如果索引是4層，就可以容納上百億的數據了。所以索引樹的層級不會太高。

為什么一個數據塊只能存放大約40條數據，而一個索引塊卻可以存放400條數據。那是因為一條數據的長度比一個索引鍵值的長度大的多。

如果一個索引鍵值也很長呢，例如在一個description列上建索引，那么一個索引塊就只能存放幾條數據，導致索引的層高增長很快，從而導致索引查詢效率低。所以不建議在長度很大的列上創建索引。

索引的掃描一般是從根節點掃描到葉子節點，索引的層高對索引的掃描效率有很大影響。

（3）索引占的數據塊比較少

從上面的查詢可以看到，單個索引只占了幾千個葉子塊（非葉子塊可以忽略），比表占的數據塊（5萬多個塊）要少得多。這個原因也是顯而易見的，因為索引單條數據的長度比表單條數據的長度要小得多。雖然索引中還要保存rowid信息，葉子節點還要保存相鄰節點的指針，但一般而言，索引占的空間比表要小得多。

當然，這也不是絕對的。當表中的列比較少時，索引和表占的空間也可能是差不多的。而且一個表上索引可能有多個，索引占的總空間可能不比表的空間小。所以我們在分配表空間時，索引和表的表空間一般也需要分配差不多的大小。

如果一個索引大部分都是空值，那么它占的索引空間就會非常小。

（4）每個鍵值占的葉子的塊數

每個鍵值占的葉子塊數是個平均值，如果索引列是唯一的，那么每個鍵值最多占一個葉子塊，如果索引列的重復值很多，那么就可能占很多個葉子塊。

例如索引IX_USERSERVICEINFO_FSUSPSTATUS，它是創建在狀態列上的索引，平均每個鍵值占了20個葉子塊。實際上它總共也只有20個葉子塊，因為所有的鍵值都是一樣的。

如果一個鍵值的重復度很高，使用這個索引查詢的效率就可能很低。前面講過，索引高度決定了從根節點到葉子節點要查詢幾個索引塊。而鍵值的重復度決定了要查詢多少個葉子塊。一個是縱向查詢，一個是橫向查詢。

還用字典的例子，如果根據拼音查詢某個特定字常(CHANG)，那么它只會對應字典的一個字，如果要查所有拼音是CHANG的字呢，那就可能對應了幾十個字，如果要查所有CH開頭的字呢，那就更多了。同樣是索引掃描，但是鍵值的選擇不同，導致查詢的效率差距很大。

（5）每個鍵指向的表的塊數

每個鍵指向的表塊數也是個平均值。一個索引鍵如果有40條數據，它一般只需要一個索引葉子塊，但是在表中，這40條數據可能在1個塊中也可能在40個塊中。這個差別對查詢的效率影響很大。

就像查字典，如果通過拼音一次檢索出40個字，并且他們也都在一頁里，那么只需要查看一頁的內容。如果通過部首檢索出40個字，雖然在索引處他們是在一起的，但實際這40個字很可能位于不同的頁上，那么就需要翻40次字典，自然要費力很多。

重要知識點

索引中不包含空的鍵值，一般不能根據索引查詢空值。

索引每層能容納的數據隨層高增大呈指數級增加，所以索引的層高不會很大。

不建議在長度很大的列或者很多字段的組合索引上創建索引，這樣的索引樹會很高，查詢效率很低。

索引占的數據塊一般比表少的多，如果一個索引大部分都是空值，那么它占的索引空間就會非常小。

如果索引鍵值的重復度很高，那么查詢效率可能比較低。

當要查詢的鍵值對應很多個數據塊時，查詢的效率比較低。

3????? 性能調優基礎

當你提交一個任務給數據庫時，你肯定希望快速得到結果，并且你不希望它消耗過多的系統性能，不能影響其他人的使用。

怎樣才能快速得到結果？什么才算性能好呢？

快速得到結果很好理解，就是響應時間短，在最短的時間內完成數據庫任務。所謂性能好就是盡量少的消耗系統的資源，尤其是重要的資源，那么這些資源是什么呢？本章將闡述這些內容。雖然看起來跟索引關系不大，但卻是理解索引調優的基礎。

3.1????? IO，CPU和network的關系

數據庫運行資源主要有三部分：IO、CPU和network。IO是指把數據從磁盤讀到內存的過程；CPU主要是指處理內存中數據的過程，有時也包括等待IO的時間；network指數據在網絡中傳輸的過程。

首先我們來搞清楚它們之間的關系。如果系統的瓶頸在IO，即需要從磁盤讀取很多數據，那么CPU和network就比較空閑；如果系統的瓶頸在CPU，即有大量的數據在內存中需要被反復處理，那么IO和network就比較空閑；如果系統的瓶頸在network，即有大量的數據需要在客戶端和服務器間傳輸，那么IO和CPU就比較空閑。

這三種性能瓶頸是此消彼長的關系。在做性能測試的時候，有時候CPU始終壓不上去，原因是CPU不是系統的瓶頸，很可能IO是瓶頸。

實際應用中，網絡的開銷是比較小的，因為大部分的運算是在數據庫完成的，所以需要重點關注IO和CPU。

如何降低IO和CPU呢？

降低IO就是減少讀取的磁盤數據塊，降低CPU就是減少讀取的內存數據塊，所以讀取的數據塊越少，消耗的資源也就越少，性能越好。

重要知識點

數據庫運行性能瓶頸主要有三種：IO、CPU和network，它們是此消彼長的關系。

做性能測試的時候，如果CPU壓不上去，說明CPU不是系統的瓶頸，很可能IO是瓶頸。

讀取的數據塊越少，消耗的資源越少，性能越好。

3.2????? 邏輯讀與物理讀

邏輯讀和物理讀是影響性能的重要因素。

邏輯讀是指要讀的數據塊已經在緩存中而不需要發生實際的IO，這時主要消耗CPU；而物理讀是指緩存中沒有要讀的數據塊，需要發生實際的IO，這時消耗的主要是IO。如果一些數據經常被讀取，那么就很可能在內存中命中，不需要物理讀。

邏輯讀和物理讀的開銷差距很大，因為讀內存比讀磁盤要快得多，一般可以快10倍以上。那么同樣讀取一千個數據塊，如果全是物理讀可能需要1分鐘，而如果全是邏輯讀則可能只需要幾秒鐘。這也是為什么同樣的語句，第一次執行往往比第二次執行慢的原因了。

重要知識點

邏輯讀是指要讀的數據塊已經在緩存中并不需要發生實際的IO，這時主要消耗CPU；而物理讀是指緩存中沒有要讀的數據塊，需要發生實際的IO，這時消耗的主要是IO。

邏輯讀是指在內存中命中數據塊，它比物理讀快10倍以上。

3.3????? 多塊讀、隨機讀和順序讀

通過前面的分析，我們看到IO是最消耗資源的，減少IO的次數是有效的性能調優方法，那么哪些方法可以減少IO的消耗呢？

合理的業務需求：

如果每次查詢都要把一個千萬級的表統計一遍，那必然帶來大量的IO，即使是邏輯讀也是非常影響性能的。

多塊讀

如果操作系統的緩存比較大，而且要讀取的數據塊是相鄰的，那么就可以使用多塊讀，即一次IO讀取多個數據塊，這樣可以減少IO的消耗。

對表做全表掃描是可以使用多塊讀的，因為表每次申請的空間（extent）都是連續的，而普通的索引掃描就不適合，首先索引塊之間是指針相連的，而且同一個索引塊中的數據在表中也不一定是連續分布的。想想查字典的例子，你應該能明白為什么通過索引訪問內容不能使用多塊讀。

當然一次IO讀取128個塊的開銷要比只讀取1個塊的開銷大，但是肯定比使用128次IO小的多。

順序讀和隨機讀

順序讀和隨機讀也是一組重要概念。

順序讀比隨機讀的效率高的多。如果要讀100條數據，它們是連續分布的，那么只要讀取兩三個數據塊，如果是隨機分布在100個數據塊的，那么就要讀取100個數據塊。而且磁盤讀寫時需要轉動磁頭，當然是讀取連續的內容效率最高。

Redo的例子也可以說明這個問題，事務commit后，Oracle會確保數據更新信息寫入Redo文件，而不直接寫數據文件，由后臺進程定時批量寫入數據文件。為什么要多此一舉呢？這樣不是寫了兩遍磁盤嗎？我想Oracle也是出于性能考慮，IO是非常消耗性能的，尤其是隨機IO，而數據文件的寫入位置就是隨機的。Redo文件是由Oracle自己控制的，它可以順序的寫入，雖然多寫了一次磁盤，但總體的性能是提高的。

重要知識點

性能調優的根本方法就是減少讀取的數據塊。

全表掃描表可以支持多塊讀，而索引掃描一般只能支持單塊讀。

順序IO比隨機IO的效率高，索引掃描一般是隨機IO。

Oracle 存儲 數據庫

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。