如何利用索引提高性能

在關(guān)系數(shù)據(jù)庫(kù)中，表中數(shù)據(jù)普遍以無(wú)序的狀態(tài)存儲(chǔ)在磁盤上，在沒(méi)有相應(yīng)索引時(shí)，若要對(duì)表中數(shù)據(jù)進(jìn)行查詢，就只能全表檢索，將所有記錄挨個(gè)讀取，然后和查詢條件進(jìn)行比較，顯然，這種方式會(huì)導(dǎo)致大量的磁盤 I/O 操作和 CPU 計(jì)算，消耗大量的系統(tǒng)時(shí)間，因此，建立索引就成了一個(gè)必須考慮的選項(xiàng)。

使用 CREATE INDEX [索引名] on 表名 (列名，……) 語(yǔ)句可以為表中數(shù)據(jù)建立最常用的鍵值索引，而鍵值索引的實(shí)現(xiàn)大都采用 B+ 樹數(shù)據(jù)結(jié)構(gòu)，它有以下一些性質(zhì)：

1、 是一棵平衡樹，即從根節(jié)點(diǎn)到葉子節(jié)點(diǎn)的深度相差不超過(guò) 1；

2、 非葉子節(jié)點(diǎn)只保存鍵值和指向子節(jié)點(diǎn)的指針，不保存數(shù)據(jù)；

3、 葉子節(jié)點(diǎn)保存鍵值、對(duì)應(yīng)記錄的地址及葉子節(jié)點(diǎn)的鏈表指針，鏈表中葉子節(jié)點(diǎn)是鍵值有序的

但這些性質(zhì)就一定能保證查詢性能滿足用戶的需求嗎？下面，我們以對(duì)銀行賬戶進(jìn)行時(shí)間段查詢?yōu)槔?，探討索引的性能?wèn)題。

為了方便說(shuō)明問(wèn)題，我們?cè)谶@里把 B+ 樹簡(jiǎn)化為 下圖所示的B+ 樹，以賬號(hào)和交易日期作為鍵值，如下圖所示：

?

如果我們要查詢賬號(hào) A002 從 2000-01-01 到 2000-01-07 的交易流水，數(shù)據(jù)庫(kù)系統(tǒng)會(huì)首先要查找賬號(hào)為 A002、日期不早于 2000-01-01 的鍵值所在的葉子節(jié)點(diǎn)，結(jié)果是依次讀取索引塊 A、B、C，然后找出索引塊 C 中滿足條件的鍵值對(duì)應(yīng)的記錄地址并讀出記錄返回，若索引塊 C 中最后一個(gè)日期早于或等于 2001-01-07，則可以根據(jù)葉子節(jié)點(diǎn)的鏈表直接讀取索引列 D，以此類推，直到某個(gè)索引塊的某個(gè)日期比 2001-01-07 大為止。

觀察上述過(guò)程，我們發(fā)現(xiàn) 2000-01-01 對(duì)應(yīng)的記錄在數(shù)據(jù)頁(yè) 1，2000-01-02 和 2000-01-03 對(duì)應(yīng)的記錄在數(shù)據(jù)頁(yè) 2，2000-01-04 對(duì)應(yīng)的記錄則在數(shù)據(jù)頁(yè) 3，4 條記錄需要讀取 3 個(gè)數(shù)據(jù)頁(yè)，極端情況下甚至任意一條記錄都在不同的數(shù)據(jù)頁(yè)，而此時(shí)如果數(shù)據(jù)區(qū)中記錄已按鍵值序存儲(chǔ)則可以顯著減少磁盤 IO。更進(jìn)一步，如果記錄數(shù)據(jù)直接保存在葉子節(jié)點(diǎn)，則可以減少查詢過(guò)程中索引頁(yè)與數(shù)據(jù)頁(yè)之間的跳讀，這對(duì)于機(jī)械硬盤的性能影響尤甚。

這些問(wèn)題對(duì)于集算器的組表來(lái)說(shuō)，可以非常輕松地得到解決。

我們還是以股票交易數(shù)據(jù)為例講解組表的使用。

A2: 創(chuàng)建數(shù)據(jù)結(jié)構(gòu)為 (sid,tdate,open,close,volume) 的組表，且指定 sid 和 tdate 為鍵，@r 指定數(shù)據(jù)按行存儲(chǔ)

A5: 將按 sid 和 tdate 有序的數(shù)據(jù)追加到組表中

A6: 以 sid 和 tdate 為鍵值建立索引 idx1

A1: 讀取組表

A2: 定義根據(jù)索引 idx1 查詢數(shù)據(jù)的游標(biāo)

A3: 取出游標(biāo)中的數(shù)據(jù)

?? 在建立索引 idx1 時(shí)，也可以將所需的數(shù)據(jù)都儲(chǔ)存在索引里，譬如要將 open、close、volume 這 3 列也儲(chǔ)存在索引 idx1 里，只需將前面表格里的A2.index(idx1;sid,tdate)改為A1.index(idx1; sid,tdate; open,close,volume)即可，這樣查詢時(shí)就可以不讀數(shù)據(jù)文件、只讀取索引文件，使查詢速度更快。