高并發數據庫下的索引創建和使用技巧

摘要：對于高并發高訪問的Web應用程序來說，數據庫存取瓶頸一直是個令人頭疼的問題。特別當程序架構還是建立在單數據庫模式上時。本論文主要介紹了通過創建索引的方式對數據庫進行優化，重點介紹了B-tree的原理和實現方式，從數據庫底層結構出發，闡明了索引對數據庫優化的作用，并介紹了索引創建及利用的一些規律和經驗，通過對規律的綜合運用，達到數據庫優化的目的。

關鍵詞：高并發 B-tree 索引

0 引言

數據庫優化有很多的技巧和方法，而建立索引則是其中經常使用的手段。創建索引一般有以下兩個目的：維護被索引列的唯一性和提供快速訪問表中數據的策略。索引是從數據庫中獲取數據的最高效方式之一，95%的數據庫性能問題都可以采用索引技術得到解決。作為一條規則，通常對邏輯主鍵使用唯一的成組索引，對系統鍵（作為存儲過程）采用唯一的非成組索引，對任何外鍵列[字段]采用非成組索引。不過，索引就像是鹽，太多了菜就咸了。你得考慮數據庫的空間有多大，表如何進行訪問，還有這些訪問是否主要用作讀寫，而本文的目的也正是討論如何通過創建合適的索引來提高數據庫的效能。

1 原理簡介

讓我們先來看一個案例：

Disks：% tm_act Kbps tpsKb_readKb_wrtn

……

hdiskpower84 70.4 1187.3 69.4 3984 1952

hdiskpower85 50.4 489.6 30.2 1904 544

hdiskpower86 84.4 1801.7 93.2 6512 2496

……

hdiskpower101 80.8 1430.5 86.0 6016 1136

hdiskpower102 62.2 931.3 56.6 3776 880

hdiskpower105 78.8 1388.9 86.6 6416 528

……

hdiskpower114 92.6 2406.6 107.0 9824 2208

hdiskpower115 88.4 3187.4 104.4 15152 784

hdiskpower116 98.2 6730.0 126.8 15280 18368

……

hdiskpower120 81.4 867.3 53.8 3616 720

hdiskpower121 97.2 2721.6 220.4 6848 6759

hdiskpower122 48.6 576.0 34.2 2480 400

% tm_act：磁盤繁忙程度 Kbps：每秒磁盤的數據吞吐量 tps：每秒磁盤I/O請求。

在這個系統中：

①使用高端存儲，底層已經做了優化：多磁盤RAID10，條帶化，雙4GB光纖通道，存儲cache命中率正常。②高配小機，操作系統級已經優化：做了多個PV，aio server配置已優化。③數據庫實例層已經優化：I/O參數已經優化；數據分布較平均，沒有出現磁盤間的I/O過于集中的情況。

這樣的配置，每個磁盤達到幾十至上百MB的I/O吞吐量不成問題，為何現實情況是1M左右的吞吐量，磁盤繁忙程度即80%，是為什么？因為沒有使用到索引嗎？

下面我們再來看一個SQL語句：

select c1，c2，c3 from t1 where c4 between 1000 and 2000

全表掃描是多塊讀，即一次I/O讀取多個數據塊。以ORACLE數據庫為例，假設db_file_multiblock_read_count

為16，表有3200個數據塊，則全表掃描為共需要3200/

16=200次I/O。那是否加大db_file_multiblock_read_

count就可以優化全表掃描了呢？答案是否定的，這個參數不能無限加大，多處有瓶頸限制。

現在我們再來看看這句sql：

select c1，c2，c3 from t1 where c4=1000

假設索引為三層，返回一行數據，數據掃描應該是這樣的：

①讀取c4字段上索引的根數據塊。②讀取由根指向的下一級——枝數據塊。③讀取由枝指向的下一級——葉數據塊。④處理葉數據塊，根據取值找到對應的index entry，根據rowid讀取表的數據塊。

共四次I/O。

根據上面所說的，是否有時查詢會因為使用索引反而大大降低性能？一般不會。數據庫通過clustering_factory（聚簇因子）來記錄和指示表中行相對于索引的有序程度，優化器會據此和其它一些條件來選擇使用索引還是使用全表掃描。

那么創建索引是否寧濫勿缺？絕對不是，我們來看看索引過多的不利影響：

①索引帶來更新時的額外開銷。②插入、刪除記錄和更新索引字段時，需要同時修改索引的葉塊。③大批量數據庫更新時，為更新索引還需要進行大量排序。④由于優化器計算缺陷、統計信息不及時等原因，數據庫也會不當的使用索引。

由于全表掃描返回的數據很多，對于I/O系統的帶寬和緩存壓力很大，應盡量避免不必要的全表掃描和對大表的全表掃描。但索引掃描對于存儲來說是離散讀，對I/O系統IO響應次數也有不小的壓力，所以也應避免不必要的索引掃描和索引更新。

我們再回到之前展示的那個案例，是因為是數據庫中創建了過多的索引，查詢和表連接時不適當的使用了索引，同時更新時維護索引帶來額外I/O開銷，導致存儲上的離散讀相當多，致使存儲后端繁忙，影響了系統性能。索引缺少或者過多都會導致SQL執行效率不必要的下降，在高并發系統下，這個問題會得到成倍的放大。讀取或者修改記錄，數據庫都需要將相應的數據塊讀入緩存中。在數據庫最底層，對緩存結構的保護是串行的（通過latch實現）。在高并發系統下這個等待對性能的影響可能是致命的。因此，索引的創建必須遵循一定的規律，不能過多也不能過少。

2 索引創建規律

根據創建B-tree索引的場景，我們提出一些創建索引的規律：

①“選擇性”是判斷是否創建B-tree索引的重要依據。選擇性越高，以該字段作為條件查詢出的結果集越小，使用索引效率就越高。②如果一個表很“胖”，即一條記錄數很長，則在一個數據塊中存放的記錄數就少，表占的數據塊就越多，全表掃描的代價就越大。這時如果通過該字段的條件選擇的數據占總量的20%以下，就可以考慮建立索引。③如果一個表很“瘦”，情況正好相反，如果通過該字段的條件選擇的數據占總量的5%以下，再考慮創建索引。④數據量小于5000的表，在oracle數據庫中可以不創建索引。⑤如果一個選擇性高的字段經常作為查詢的條件，但同時又經常被更新，就要評估對查詢和更新的影響哪個更重要。一般來說，創建索引對查詢時間的提升很大，而對更新的延遲相對較小。⑥選擇性不高的字段在事務型數據庫（OLTP）中不要單獨創建索引。在報表型數據庫（OLAP）中的靜態表，可以適當使用Bitmap索引。對于常發生DML操作的表，不能建立位圖索引，否則該表的相關操作很容易造成鎖等待；其次，位圖索引列需要低基數，只有幾個數值，比如性別列（男，女）和學歷列（大專，本科，研究生，博士生）。⑦復合索引只有在該種復合常被和該表相關的大多數SQL使用時才建立。⑧復合索引的列數不要過多，例如不超過3個，否則既影響更新性能，又影響該索引的使用。⑨復合索引ABC，完全可以替代索引AB和索引A。⑩復合索引的第一列，可以通過不使用該種復合的SQL來確定。假設一些SQL的WHERE中復合使用列為ABC，而其他一些SQL的WHERE中常使用的是C列，那么該復合索引可以按照CAB的順序建立，這樣上述兩種SQL都能使用該索引。把查詢頻率比較高的字段排在復合索引的最前面。把選擇性最高的字段排在復合索引的最前面。如果一種查詢使用ABCDE，一種查詢使用ABCFG，而ABC選擇性已經較高，則只創建索引ABC就足夠。對于不能把握好的復合索引，可以在選擇性大的列上分別建立單列索引。切忌不能將表相關的所有SQL中WHERE涉及到的列復合起來建立復合索引。查詢時，能加入選擇性高的字段條件且不影響業務邏輯的，一定要加入。在大量裝載數據時，可以先刪除索引，數據裝載后再重建索引，以避免裝載時的排序，能減少資源消耗和提高效率。無論表的大小，外鍵列上都要建立索引。除了為子父表關聯查詢的性能考慮，主要是為了避免修改父表而鎖死子表。DB2 V8及以前版本，select操作會阻塞其它DML操作。因此不論表大小，都應在常用where條件的字段上創建索引。

以上兩種情況常常也是造成死鎖的原因，一定要注意。

3 結語

總體來說，使用索引的目的是為了提高訪問速度、減少數據訪問和傳輸量、減少不必要的排序、優化表連接。可根據上述的原理、規律結合實踐來創建和使用索引。

參考文獻：

[1]薩師煊，王珊編著.數據庫系統概論[M].北京：高等教育出版社，2001：264-279.

[2]吳國鳳，顧巍.高并發環境下多維索引結構的算法研究[J].計算機技術與應用進展，2006，260-265.

[3]http：//wenku.baidu.com/view/7acfb91fc5da50e2524d7f9f.html高并發高負載數據庫架構策略.

作者簡介：劉軍（1978-），男，天津人，本科，天津市和平區審計局，研究方向：固定資產投資審計。