數(shù)據(jù)庫性能調(diào)優(yōu)技術(shù)

一、概述 這些內(nèi)容，我會在后續(xù)的幾篇文章中進(jìn)行詳細(xì)的描述。在此之前，我們首先需要掌握如何理解數(shù)據(jù)庫執(zhí)行計劃。這篇文章講解只涉及單表操作的執(zhí)行計劃。達(dá)夢數(shù)據(jù)庫、oracle 數(shù)據(jù)庫、sql server 數(shù)據(jù)庫都可以顯示給定語句的執(zhí)行計劃。我詳細(xì)分析了這三個數(shù)

一、概述

這些內(nèi)容，我會在后續(xù)的幾篇文章中進(jìn)行詳細(xì)的描述。在此之前，我們首先需要掌握如何理解數(shù)據(jù)庫執(zhí)行計劃。這篇文章講解只涉及單表操作的執(zhí)行計劃。達(dá)夢數(shù)據(jù)庫、oracle 數(shù)據(jù)庫、sql server 數(shù)據(jù)庫都可以顯示給定語句的執(zhí)行計劃。我詳細(xì)分析了這三個數(shù)據(jù)庫的執(zhí)行計劃，三者之間并無本質(zhì)區(qū)別。所以本文的內(nèi)容適合于這三個數(shù)據(jù)庫。同樣，也應(yīng)該適合絕大多數(shù)其它的數(shù)據(jù)庫。

單表執(zhí)行的深入理解，是了解多表執(zhí)行計劃的基礎(chǔ)。達(dá)夢數(shù)據(jù)庫顯示的執(zhí)行計劃時顯示的信息會多一些。因此，這篇文章中我選擇達(dá)夢數(shù)據(jù)庫作為實(shí)例數(shù)據(jù)庫來講解執(zhí)行計劃的原理。讀完本文后，應(yīng)該能夠讀懂這三個數(shù)據(jù)庫的單表執(zhí)行計劃。

二、深入理解數(shù)據(jù)庫執(zhí)行計劃

達(dá)夢數(shù)據(jù)庫的執(zhí)行計劃有兩種顯示方式：第一種為圖形化的顯示方式；第二種為文本式的顯示方式。這里采用第二種方式進(jìn)行講解。理解執(zhí)行計劃，是邁向理解數(shù)據(jù)庫性能調(diào)優(yōu)的重要一步。從執(zhí)行計劃中，我們可以看出數(shù)據(jù)庫是如何執(zhí)行查詢語句，并根據(jù)執(zhí)行計劃判斷出該查詢語句的執(zhí)行是否高效，以及如何進(jìn)行優(yōu)化。

下面我們將通過一些例子來理解數(shù)據(jù)庫執(zhí)行計劃。

1.沒有索引的全表掃描過濾如何執(zhí)行？

構(gòu)造處執(zhí)行場景：

create table t1(c1 int,c2 int);

insert into t1 values(1,1);

insert into t1 values(2,2);

insert into t1 values(3,3);

insert into t1 values(4,4);

insert into t1 values(5,5);

insert into t1 values(6,6);

查詢語句為：

select * fromt1 wherec1=2;

該語句的執(zhí)行過程，如果用語言描述可以描述成這樣：

1）如果是第一次執(zhí)行該步驟，則取得表的第一條記錄；否則取得當(dāng)前記錄的下一條記錄。如果記錄已經(jīng)掃描結(jié)束，則執(zhí)行步驟 4，否則執(zhí)行步驟 2。

2）判斷該記錄是否滿足過濾條件c1=2，滿足則執(zhí)行步驟 3，否則執(zhí)行步驟 1。

3）把該記錄放到結(jié)果集中，執(zhí)行步驟1。

4）將結(jié)果集返回給客戶端。

實(shí)際上，數(shù)據(jù)庫執(zhí)行查詢語句的過程也是類似的，下面是該查詢語句的執(zhí)行計劃：

#RSET:[21, 1, 1];

#XFLT:[0, 0, 0]; EXPR0 = 2

#CSEK:[21, 1, 1]; INDEX33555545(T1),FULL_SCAN

該執(zhí)行計劃中出現(xiàn)的內(nèi)容，在此做出解釋：

1)CSEK（查找）類似于上文中描述的步驟1，方括號中的內(nèi)容是執(zhí)行該操作的評估代價，本文不作分析。“INDEX33555545(T1)”說明使用了 T1 表的聚集索引， “FULL_SCAN”表示對聚集索引 INDEX33555545(T1)進(jìn)行全掃描。這里需要注意的是，達(dá)夢數(shù)據(jù)庫中的表默認(rèn)情況下是索引組織的。如果建表時指定了 cluster primary key，那么數(shù)據(jù)以該 clsuter primary key 組織數(shù)據(jù)，否則以 rowid 組織數(shù)據(jù)。

2）XFLT（過濾）類似于上文中描述的步驟 2，“EXPR0 = 2”是過濾條件。

3）RSET（結(jié)果集）類似于上文中描述的步驟 3，用來存放符合條件的記錄集。

我們可以看出，數(shù)據(jù)庫的執(zhí)行過程和我們用語言描述的步驟是一致的。

該查詢語句完整的執(zhí)行流程如下：

1）CSEK 取得第一條記錄（1，1）傳給 XFLT，將控制權(quán)傳給 XFLT。

2）XFLT 發(fā)現(xiàn)該記錄（1，1）不符合條件，將控制權(quán)傳給 CSEK。

3）CSEK 取得下一條記錄（2，2）傳給 XFLT，將控制權(quán)傳給 XFLT。

4）XFLT 發(fā)現(xiàn)記錄（2，2）符合條件，將該記錄傳給 RSET，將控制權(quán)傳給 RSET。

5）RSET 將記錄（2，2）放入結(jié)果集，將控制權(quán)傳給 XFLT。

6）XFLT 給控制權(quán)傳給 CSEK。

7）CSEK 取得下一條（3，3）傳給 XFLT，將控制權(quán)傳給 XFLT。

8）XFLT 發(fā)現(xiàn)該記錄（3，3）不符合條件，將控制權(quán)傳給 CSEK。

9）CSEK 取得下一條（4，4）傳給 XFLT，將控制權(quán)傳給 XFLT。

10）XFLT 發(fā)現(xiàn)該記錄（4，4）不符合條件，將控制權(quán)傳給 CSEK

11）CSEK 取得下一條（5，5）傳給 XFLT，將控制權(quán)傳給 XFLT。

12）XFLT 發(fā)現(xiàn)該記錄（5，5）不符合條件，將控制權(quán)傳給 CSEK。

13）CSEK 取得下一條（6，6）傳給 XFLT，將控制權(quán)傳給 XFLT。

14）XFLT 發(fā)現(xiàn)該記錄（6，6）不符合條件，將控制權(quán)傳給 CSEK。

15）CSEK 發(fā)現(xiàn)描述操作已經(jīng)結(jié)束，通知 XFLT 結(jié)束。將控制權(quán)傳給 XFLT。

16）XFLT 得知查詢操作結(jié)束，通知 RSET 結(jié)束。將控制權(quán)傳給 RSET。

17）RSET 得知操作結(jié)束。

18）發(fā)送結(jié)果集（包含記錄（2，2））給客戶端。

2.如果表 t1 上的 c1 列有非唯一索引，如何執(zhí)行呢？

表 t1 的定義以及數(shù)據(jù)和 1 中描述的一樣。

創(chuàng)建索引： create index it1_c1 on t1(c1);

查詢語句“select* from t1 where c1=2;”對應(yīng)的執(zhí)行計劃為：

#RSET:[201, 2, 1];

#CSEK(SECOND):[201, 2, 1]; IT1C1(T1), INDEX_EQU_SEARCH

CSEK 行的“SECOND”表示使用非聚集索引“IT1C1”，對該索引進(jìn)行索引等值（INDEX_EQU_SEARCH）查找。

該執(zhí)行計劃的執(zhí)行流程為：

1）CSEK 使用 c1=2 查找非聚集索引，得到第一條 c1=2 的索引記錄（2，rowid1）中的 rowid1（為數(shù)值）。使用 rowid1 查找聚集索引得到對應(yīng)的數(shù)據(jù)記錄（2，2）傳遞給 RSET，將控制權(quán)傳給 RSET。

2）RSET 將記錄（2，2）放入結(jié)果集，將控制權(quán)傳給CSEK。（因為 c1 上的索引是非唯一的，所以可能出現(xiàn)兩條以上的記錄滿足c1=2，所以需要將控制權(quán)傳給CSEK）。

3）CSEK 取得當(dāng)前非聚集記錄的下一條記錄（3，rowid2），因為3!=2，所以掃描結(jié)束。將控制權(quán)傳給RSET。（如果滿足 c1=2 的記錄數(shù)大于1條，需要繼續(xù)傳遞記錄給 RSET，以此類推，直到遇到不滿足c1=2 的那條記錄，結(jié)束操作。）

4）RSET 得知操作結(jié)束。

5）發(fā)送結(jié)果集（包含記錄（2，2））給客戶端。

3.如果表 t1 上的 c1 列有唯一索引，如何執(zhí)行呢？

首先刪除 c1 列上的非唯一索引，然后在 c1 列上創(chuàng)建唯一索引：

drop indexit1 c1;

create uniqueindex uit1 c1 on t1(c1);

查詢語句“select* from t1 where c1=2;”對應(yīng)的執(zhí)行計劃為：

#RSET:[201, 2, 1];

#CSEK(SECOND):[201, 2, 1]; UIT1C1(T1), INDEX_EQU_SEARCH

該執(zhí)行計劃的執(zhí)行流程為：

1）CSEK 使用 c1=2 查找非聚集索引，得到 c1=2 的索引記錄（2，rowid1）中的rowid1（為數(shù)值）。使用 rowid1 查找聚集索引得到對應(yīng)的數(shù)據(jù)記錄（2，2）傳遞給 RSET， 將 控制權(quán)傳給 RSET。（當(dāng)然，有人也許會問，如果沒有記錄滿足 c1=2怎么辦呢？那么， 此處什么記錄都不傳遞給RSET，通知RSET查詢操作結(jié)束，最后返回空集給客戶端）。

2）RSET 將記錄（2，2）放入結(jié)果集，操作結(jié)束（因為是唯一索引，所以最多只有 1 條記錄滿足 c1=2）。

3）發(fā)送結(jié)果集（包含記錄（2，2））給客戶端。

這里我們發(fā)現(xiàn)，例3使用了唯一索引，例2使用了非唯一索引。例3的執(zhí)行速度大于例2的執(zhí)行速度。

4.如何理解執(zhí)行計劃中的 top n 操作？

查詢語句“select top 10 * from t1 wherec1>2;”對應(yīng)的執(zhí)行計劃為：

#RSET:[21, 1, 1];

#XTOP:[0, 0, 0]; top_off(0), top_num(10)

#XFLT:[0, 0, 0]; EXPR1 > 2

#CSEK:[21, 1, 1]; INDEX33555545(T1),FULL_SCAN

XTOP（取得前 N 條記錄）：將 XFLT 操作符傳遞來的記錄放入到 RSET（結(jié)果集）中，并判斷記錄數(shù)是否已經(jīng)等于給定值 10（語句中的 top 10）。如果已經(jīng)等于 10，則查詢已經(jīng)執(zhí)行成功，退出。否則將控制權(quán)限傳給 XFLT，繼續(xù)執(zhí)行。依次執(zhí)行，直到取得 10 條記錄，或者表CSEK操作已經(jīng)查詢結(jié)束（即符合條件的記錄不滿 10 條）。

5.如何理解執(zhí)行計劃中的 order by 操作？

查詢語句“select top 10 * from t1 where c2>2order by c1;”對應(yīng)的執(zhí)行計劃為：

#RSET:[21, 1, 1];

#XSORT:[0, 0, 0]; keys_num(1),is_distinct(FALSE)

#XFLT:[0, 0, 0]; EXPR1 > 2

#CSEK:[21, 1, 1]; INDEX33555545(T1),FULL_SCAN

XSORT（對記錄進(jìn)行排序）：將 XFLT 操作符傳遞來的記錄插入到 XSORT 維護(hù)的臨時空間中的合理位置，按 c1 進(jìn)行有序排列。然后將控制權(quán)傳給 XFLT 以取得下一條符合條件的記錄。等處理完所有符合條件的記錄。XSORT操作符才會將控制權(quán)限傳給 RSET。

6．是不是查詢語句中一旦出現(xiàn) order by 字句，執(zhí)行計劃中就會出現(xiàn) XSORT 操作

符？

不是。比如，查詢語句“selectc1 from t1 order by c1;”對應(yīng)的執(zhí)行計劃為：

#RSET:[0, 0, 0];

#CSEK:[0, 0, 0]; UIT1C1(T1), FULL_SCAN

從執(zhí)行中我們可以看出，達(dá)夢直接對索引 UIT1C1進(jìn)行全索引掃描，對于得到的每一條記錄不需要進(jìn)行 XSORT 排序操作，直接放入 RSET（結(jié)果集）中。因為索引 UIT1C1 本身就是按照 c1 進(jìn)行排序的。

7．有文檔說，對于語句“select max(c1) from t1”，可以在 c1 列上創(chuàng)建索引從而查詢速度變快。那么在執(zhí)行計劃中是如何體現(xiàn)的呢？

查詢語句“selectmax(c1) from t1”對應(yīng)的執(zhí)行計劃：

#RSET:[0, 0, 0];

#XEVL:[0, 0, 0];

#FAGR:[0, 0, 0]; function_num(1)

在這個執(zhí)行計劃中，我們沒有看到 CSEK 操作符。因為 c1 上存在索引 UIT1C1，該索引葉子節(jié)點(diǎn)的最右端就是 c1 的最大值。FARG 直接返回該最大值。語句“select min(c1) from t1;”、語句“select count(*) fromt1;”的執(zhí)行原理一樣。XEVL是表達(dá)式計算，本文不進(jìn)行講解。

8．如果列上存在索引，如何理解中的 group by 操作？

查詢語句“selectc1,count(*) from t1 where c1>=2 group by c1;”對應(yīng)的執(zhí)行計劃為：

#RSET:[11, 1, 1];

#XEVL:[0, 0, 0];

#SAGR:[0, 0, 0]; group_by_num(1),function_num(1)

#CSEK:[11, 1, 1]; UIT1C1(T1), INDEX_GE_SEARCH

我們可以得到，CSEK使用了索引UIT1C1進(jìn)行了范圍查找。首先傳遞給SARG的是連續(xù)的 c1=2 的記錄組，然后是 c1=3 的記錄組，然后是 c1=4 的記錄組，……

此處 SARG 的執(zhí)行流程是

1）從 CSEK 取得一條 c1=2 記錄，將計數(shù)加 1，

2）從 CSEK 取得下一條記錄，如果該記錄滿足 c1，將計數(shù)+1。

3）重復(fù)執(zhí)行步驟 2，直到取得第一條不滿足 c1=2 的記錄，將(2,對應(yīng)的計算)傳遞給 XEVL，再傳給 RSET（結(jié)果集）。接著對 c1=3 的記錄組執(zhí)行同樣的流程。依此類推，直到處理完所有符合條件的記錄。

這里我們的分組函數(shù)是count(*)，如果是其它的分組函數(shù)，處理過程類似。

9．如果列上不存在索引，如何理解中的 group by 操作？

查詢語句“selectc2,count(*) from t1 where c2>=2group by c2;”對應(yīng)的執(zhí)行計劃為：

#RSET:[21, 1, 1];

#XEVL:[0, 0, 0];

#HAGR:[0, 0, 0]; group_by_num(1),function_num(1)

#XFLT:[0, 0, 0]; EXPR0 >= 2

#CSEK:[21, 1, 1]; INDEX33555550(T1),FULL_SCAN

這里因為 c2 上沒有索引，HARG 的作用是 HASH 分組。

HARG 的執(zhí)行流程是：

1）從 XFLT 取得一條記錄

2）記錄的 c1=m，如果在 hash 表中已經(jīng)對應(yīng)項，計數(shù)+1，如果不存在對應(yīng)項，在創(chuàng)建一個新的 hash 項。

3）所有的符合過濾條件的記錄處理完成之后，HARG 才會將控制權(quán)限傳給上層操作符，HARG 每次向上層操作符傳遞一條（m,m 對應(yīng)的計數(shù)）。

這里我們的分組函數(shù)是 count(*)，如果是其它的分組函數(shù)，處理過程類似。

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com