一、概述

隨著數(shù)據(jù)庫在各個(gè)領(lǐng)域的使用不斷增長，越來越多的應(yīng)用提出了高性能的要求。數(shù)據(jù)庫性能調(diào)優(yōu)是知識密集型的學(xué)科，需要綜合考慮各種復(fù)雜的因素：數(shù)據(jù)庫緩沖區(qū)的大小、索引的創(chuàng)建、語句改寫等等。總之，數(shù)據(jù)庫性能調(diào)優(yōu)的目的在于使系統(tǒng)運(yùn)行得更快。

調(diào)優(yōu)需要有廣泛的知識，這使得它既簡單又復(fù)雜。

說調(diào)優(yōu)簡單，是因?yàn)檎{(diào)優(yōu)者不必糾纏于復(fù)雜的公式和規(guī)則。許多學(xué)術(shù)界和業(yè)界的研究者都在嘗試將調(diào)優(yōu)和查詢處理建立在數(shù)學(xué)基礎(chǔ)之上。

稱調(diào)優(yōu)復(fù)雜，是因?yàn)槿绻耆斫獬ＷR所依賴的原理，還需要對應(yīng)用、數(shù)據(jù)庫管理系統(tǒng)、操作系統(tǒng)以及硬件有廣泛而深刻的理解。

數(shù)據(jù)庫調(diào)優(yōu)技術(shù)可以在不同的數(shù)據(jù)庫系統(tǒng)中使用。如果需要調(diào)優(yōu)數(shù)據(jù)庫系統(tǒng)，最好掌握如下知識：1）查詢處理、并發(fā)控制以及數(shù)據(jù)庫恢復(fù)的知識；2）一些調(diào)優(yōu)的基本原則。

這里主要描述索引調(diào)優(yōu)。

二、索引調(diào)優(yōu)

索引是建立在表上的一種數(shù)據(jù)組織，它能提高訪問表中一條或多條記錄的特定查詢效率。因此，適當(dāng)?shù)乃饕{(diào)優(yōu)是很重要的。

對于索引調(diào)優(yōu)存在如下的幾個(gè)誤區(qū)：

誤區(qū)1：索引創(chuàng)建得越多越好？

實(shí)際上：創(chuàng)建的索引可能建立后從來未使用。索引的創(chuàng)建也是需要代價(jià)的，對于刪除、某些更新、插入操作，對于每個(gè)索引都要進(jìn)行相應(yīng)的刪除、更新、插入操作。從而導(dǎo)致刪除、某些更新、插入操作的效率變低。

誤區(qū)2：對于一個(gè)單表的查詢，可以索引1進(jìn)行過濾再使用索引2進(jìn)行過濾？

實(shí)際上：假設(shè)查詢語句如下select * from t1 where c1=1 and c2=2，c1列和c2列上分別建有索引ic1、ic2。先使用ic1(或ic2)進(jìn)行過濾，產(chǎn)生的結(jié)果集是臨時(shí)數(shù)據(jù)，不再具有索引，所以不可使用ic2(或ic1)進(jìn)行再次過濾。

索引優(yōu)化的基本原則：

1.將索引和數(shù)據(jù)存放到不同的文件組

沒有將表數(shù)據(jù)和索引數(shù)據(jù)存儲到不同的文件組，而不加區(qū)別地將它們存儲到同一文件組。這樣，不但會造成I/O競爭，也為數(shù)據(jù)庫的維護(hù)工作帶來不變。

2.組合索引的使用

假設(shè)存在組合索引it1c1c2(c1,c2)，查詢語句select * from t1 where c1=1 and c2=2能夠使用該索引。查詢語句select * from t1 where c1=1也能夠使用該索引。但是，查詢語句select * from t1 where c2=2不能夠使用該索引，因?yàn)闆]有組合索引的引導(dǎo)列，即，要想使用c2列進(jìn)行查找，必需出現(xiàn)c1等于某值。

根據(jù)where條件的不同，歸納如下：

1）c1=1 and c2=2：使用索引it1c1c2進(jìn)行等值查找。

2） c1=1 and c2>2：使用索引it1c1c2進(jìn)行范圍查找，可以有兩種方法。

方法1，使用通過索引鍵(1,2)在B樹中命中一條記錄，然后向后掃描找出 第一條符合條件的記錄，從此記錄往后的每一條記錄都是符合條件的。這種方法的弊端在于：如果c1=1 and c2=2對應(yīng)的記錄數(shù)很多，會產(chǎn)生很多無效的掃描。

方法2，如果c2對應(yīng)的int型數(shù)據(jù)，可以使用索引鍵(1,3)在B樹中命中一條記錄，從此記錄往后的每一條記錄都是符合條件的。

本文中的例子均采用方法1。

3）c1>1 and c2=2：因?yàn)樗饕牡谝粋€(gè)列不是等于號的，索引即使后面出現(xiàn)了c2=2，也不能將c2=2應(yīng)用于索引查找。這里，通過索引鍵(1,- ∞)在B樹中命中一條記錄，向后掃描找出第一條符合c1>1的記錄，此后的每一條記錄判斷是否符合c2=2，如果符合則輸出，否則過濾掉。這里我們稱c2=2沒有參與到索引運(yùn)算中去。這種情況在實(shí)際應(yīng)用中經(jīng)常出現(xiàn)。

4）c1>1：通過索引鍵(1,- ∞) 在B樹中命中一條記錄，以此向后掃描找出第一條符合c1>1的記錄，此后的每條記錄都是符合條件的。

3.唯一索引與非唯一索引的差異

假設(shè)索引int1c1(c1)是唯一索引，對于查詢語句select c1 from t1 where c1=1，達(dá)夢數(shù)據(jù)庫使用索引鍵(1)命中B樹中一條記錄，命中之后直接返回該記錄（因?yàn)槭俏ㄒ凰饕宰疃嘀荒苡幸粭lc1=1的記錄）。

假設(shè)索引it1c2(c2)是非唯一索引，對于查詢語句select c2 from t2 where c2=2，達(dá)夢數(shù)據(jù)庫使用索引鍵(2)命中B樹中一條記錄，返回該記錄，并繼續(xù)向后掃描，如果該記錄是滿足c=2，返回該記錄，繼續(xù)掃描，直到遇到第一條不符合條件c2=2的記錄。

于是，我們可以得知，對于不存在重復(fù)值的列，創(chuàng)建唯一索引優(yōu)于創(chuàng)建非唯一索引。

4.非聚集索引的作用

每張表只可能一個(gè)聚集索引，聚集索引用來組織真實(shí)數(shù)據(jù)。語句“create table employee (id int cluster PRimary key,name varchar(20),addr varchar(20))”。表employee的數(shù)據(jù)用id來組織。如果要查找id=1000的員工記錄，只要用索引鍵（1000）命中該聚集索引。但是，對于要查找name=’張三’的員工記錄就不能使用該索引了，需要進(jìn)行全表掃描，對于每一條記錄判斷是否滿足name=’張三’，這樣會導(dǎo)致查詢效率非常低。

要使用聚集索引，必需提供id，我們只能提供name，于是需要引入一個(gè)輔助結(jié)構(gòu)實(shí)現(xiàn)name到id的轉(zhuǎn)換，這就是非聚集索引的作用。該非聚集索引的鍵是name，值是id。于是語句“select * from employee where name=’張三’”的執(zhí)行流程是：通過鍵（’張三’）命中非聚集索引，得到對應(yīng)的id值3（假設(shè)’張三’對應(yīng)的id為3），然后用鍵(3)命中聚集索引，得到相應(yīng)的記錄。

5.是不是使用非聚集索引的查詢都需要進(jìn)行聚集的查詢？

不是的，雖然在上一點(diǎn)中查詢轉(zhuǎn)換為聚集索引的查找，有時(shí)候可以只需要使用非聚集索引。

創(chuàng)建表并創(chuàng)建相應(yīng)的索引：create table t1(c1 int,c2 int,c3 int);create index it1c2c3 on t1(c2,c3)。查詢語句為：select c3 from t1 where c2=1。

因?yàn)樗饕齣t1c2c3(c2,c3)覆蓋查詢語句中的列(c2,c3)。所以，該查詢語句的執(zhí)行流程為：通過索引鍵(1,- ∞)命中索引it1c2c3，對于該記錄直接返回c3對應(yīng)的值，繼續(xù)向后掃描，如果索引記錄中c1還是等于1，那么輸出c3，以此類推，直到出現(xiàn)第一條c1不等于1的索引記錄,結(jié)束查詢。

6.創(chuàng)建索引的規(guī)則

創(chuàng)建索引首先要考慮的是列的可選擇性。比較一下列中唯一鍵的數(shù)量和表中記錄的行數(shù)，就可以判斷該列的可選擇性。如果該列的“唯一鍵的數(shù)量/表中記錄行數(shù)”的比值越接近于1，則該列的可選擇行越高。在可選擇性高的列上進(jìn)行查詢，返回的數(shù)據(jù)就較少，比較適合索引查詢。相反，比如性別列上只有兩個(gè)值，可選擇行就很小，不適合索引查詢。