AP與DBMaker的搭配 – Performance Tuning初探
1.
前提假設
你為圖書館的管理員，能在最快的速度找到顧客所要的資料，也能頗具巧思地設計一些方法，加快找書的速度。
(1) 工作的環境：
- 圖書館內共有三層樓，每樓都有一大堆的書架，藏書計有500萬本。
- 顧客詢問的問題千奇百怪，從「我要借倚天屠龍記」，到「有一本書，作者姓黃，有點像是偶像自傳一類的」；有人對於自己要借的書很清楚，有人則是什麼都很糢糊。
- 有一個工作平台，任何資料上的查詢動作或交付給客戶的書藉，都必須先放在工作平台上，這個平台最大就只能堆放3000本書。
- 每本書的頁數都是500頁。
- 每一本書，不論顧客要求的多麼不明確，一定都要找到。
試用方法，可以在這樣的環境下，來提昇搜尋書藉的效率。
Database的原理，與上述的情形頗為類似，其中所謂的三層館藏，指的是實際存放資料的硬碟或其他儲存媒體，「工作平台」指的是記憶體。所有資料庫的搜尋或傳給客戶端的資料，都必須一定要先放在記憶體，搜尋動作會損耗CPU資源，但資料的處理、搜尋都必須先放在記憶體中，此處意指記憶體有3000個單位(pages)。
所謂「客戶詢問」，指的便是由客戶端來的各式各樣的query，而無論客戶端的query如何，資料庫一定得將資料找到，並傳回客戶端。
(2) 建立索引：
來找書的顧客，多對於要找尋的書藉，大概都有個譜，所以多是直接以「書名」、「作者」，這類的關鍵字來查詢，久而久之，你便想到，將一些重要的關鍵字，編輯成冊，當作索引，如此一來，將可加速查詢的速度。如下表：
表一、書名索引表
|
書名
|
所放位置
|
|
我在死牢的日子
|
第一樓、B書架、第三層
|
|
倚天屠龍記
|
第二樓、C書架、第五層
|
|
笑傲江湖
|
第三樓、F書架、第三層
|
|
DBA管理手冊
|
第三樓、T書架、第三層
|
|
怪博士與機械娃娃
|
第二樓、S書架、第二層
|
|
異域
|
第一樓、Z書架、第四層
|
表二、作者索引表
|
作者
|
所放位置
|
|
佚名
|
第一樓、B書架、第三層
|
|
金庸
|
第二樓、C書架、第五層
|
|
金庸
|
第三樓、F書架、第三層
|
|
DBMaker Support
|
第三樓、T書架、第三層
|
|
島山明
|
第二樓、S書架、第二層
|
|
柏楊
|
第一樓、Z書架、第四層
|
由於有500萬本藏書，所以你必須對「書名索引表」、「作者索引表」各編500萬筆記錄，假設「書名索引表」一頁可記載50筆「書名-位置」記錄，500萬筆記錄就要用去10萬頁(500萬/50筆)，而在前提中，一本書只能有500頁，最後建了200本的「書名索引表」(10萬/500頁)。
在資料庫的搜尋過程中，也是常使用「索引」來加速查詢效率，如上面引諭，所碰到的問題，也與上述相同，例如，索引表也是一種資料，也須佔用空間。DBMaker的索引表使用B+
Tree架構，會先將鍵值(key)排序，再將鍵值及資料儲放位置(oid)成對記錄下來，在邏輯架構上，與上述表格十分相似。
在這裏，我們可以想成，500萬本藏書(pages)，針對其中一欄作索引，產生的索引表為200本(pages)
(3) 狀況演練
現在來看新的作業流程，當有人詢問：「我要倚天屠龍記」時，你要：
- 到一樓將200本的「書名索引表」拿出來，放到工作平台上，
- 查詢「倚天屠龍記」所在位置，
- 再到所放位置中，將「倚天屠龍記」拿出
- 交予客戶。
由於所有的查詢工作都必須在工作平台上，所以應儘可能將書放在工作平台上，加速查詢，例：當下一個客戶再問：「怪博士與機械娃娃」時，因為之前已經有人用「書名」來查詢過了，你就可省略上述第一個步驟，直接從工作平台上開始翻閱「書名索引表」。
索引如同其他資料頁一樣，必須先載入至記憶體後，CPU才能針對其中的內容，進行「比較」運算；而由於磁碟速度極慢，DBMaker會將常用的資料頁(含索引頁)儘量放在記憶體中，如此一來，當要再搜尋某資料頁時，就有相對較大的可能，可以在記憶體中找到，而不須到磁碟上去搜尋。
方才說明的是用「書名索引表」來作查詢，假設現在有個客戶，要尋找作者為「金庸」的所有藏書，與上述相同，會利用「作者索引表」找到金庸作著的所有小說；但若是客戶詢問：「我要找金庸所作，內容有『成吉思汗』的書藉」呢??
這時的作法會變成：
- 先到一樓將200本的「作者索引表」拿出來，放到工作平台上，
- 查詢「作者金庸」的書藉及所在位置
- 再到所放位置中，將所有金庸所作的書藉拿出，放到工作平台上
- 在工作平台上查詢所有的書藉，找尋哪一本有「成吉思汗」內容
- 交予客戶。
當一個query為：「select * from
books where 作者=’金庸’ and 內容 contains ‘成吉思汗’」，若是「作者欄位」已有索引，DBMaker也會先用索引，把符合「作者=金庸」的資料先挑出。接下來，對這些被挑出的資料頁，再比對是否有「contain
成吉思汗」。
這裏要注意的是，第一、在條件判斷時，不是只有搜尋索引頁，索引頁能大幅汰除不須要的搜尋動作，為了符合「contain
成吉思汗」，還是要實際抓資料頁(就如上文去館藏拿實際的書藉一樣)。
第二、在抓取資料時，一定得再去實際抓取資料頁，所以上面的query完成後，會出現的結果可能是：
| 作者 |
出版日期 |
書名
|
出版社
|
摘要 |
… |
| 金庸 |
1971/5/4 |
大漠英雄傳 |
遠流 |
… |
|
如果一個query是「select 作者 from
books where 作者 like ‘金%’」
當然也會先將作者索引載入記憶體，然後在索引頁中找尋符合條件的，但這個query就無須再去抓「資料頁」，原因在於這個query要的只是「作者」，而不是「全部欄位」，而作者的資訊已記錄在索引頁中了。所以不須產生抓取資料頁的動作。
一個query的結構中，select
[Projection] from table_name where [Predicate]，隨著Projection、Predicate的不同，便會造成實際抓取資料動作的不同。使用者無須為此擔心，DBMaker的引擎會負責這種搜尋動作的控制，但使用者須了解這種觀念，在遇到query過慢或系統效能調整時，方能知道從何處著力。
動動腦，該怎麼去搜尋以下顧客的要求：
(1) 客戶詢問：「我要金庸的【大漠英雄傳】」(作者、書名)時，會怎麼作?
(2) 客戶詢問：「我要書名有『大』這個字，而作者姓【金】的書」
(3) 客戶詢問：「我要書名有『大』這個字，作者姓『金』，約1960-1980年間出版，書的內容含有「宋朝」、「成吉思汗」等字，價錢約在300元至400元間」
注意，現在手邊已有「作者索引集」、「書名索引集」兩套手冊，各計200本，在此前提下，可再編成其他索引，或是其他方法，目的就是要儘速地找到想要的書藉。
(1) 金庸的【大漠英雄傳】：在此詢問下，你可以去找「作者索引集」，也可找「書名索引集」，可以想見，從「書名索引集」開始找尋起，會比「作者索引集」來得好，因為可能從書名索引集，可以直接找到10本「大漠英雄傳」，(注意，書名索引表中並未記錄作者名)，再到圖書樓層中找到相對的書藉，看看這些書的作者是否為『金庸』，若是，便找到完全符合顧客需求(作者、書名)的書藉。
相對地，若是去找「作者索引表」，可能會找出200多本由金庸撰寫的小說，此時去圖書樓層中，找到相對的書藉，再去看看書名是否為「大漠英雄傳」，若是則符合需求。
相較兩法，自然是用「書名索引集」較好
在定義索引時，自然也是以「排除效果」(filter
effect)大的為佳，在條件判斷過程中，並不是只搜尋索引頁，而是有可能進一步搜尋資料頁，所以要想辦法讓第一次搜尋索引頁時，儘可能第一時間排除所有「不可能」的資料，上述中，作者的排除效果顯然就沒有書名好，也可以想作，書名比較「unique」
(2) 書名『大』字，作者姓【金】：其實上面的劇本中，還有另外一個方法，就是再去建立一個「書藉-作者-索引集」，那麼這個表格會如下表所示：
表三、書名-作者-索引表
|
書名
|
作者
|
所放位置
|
|
我在死牢的日子
|
佚名
|
第一樓、B書架、第三層
|
|
倚天屠龍記
|
金庸
|
第二樓、C書架、第五層
|
|
笑傲江湖
|
金庸
|
第三樓、F書架、第三層
|
|
DBA管理手冊
|
DBMaker Support
|
第三樓、T書架、第三層
|
|
怪博士與機械娃娃
|
島山明
|
第二樓、S書架、第二層
|
|
異域
|
柏楊
|
第一樓、Z書架、第四層
|
同樣地，這個表格將兩個重要的關鍵字集結成索引表，與(1)相同的詢問(金庸的【大漠英雄傳】)，在這個設定下，只要搜尋一次，便可找到正確的結果，因為你不需把實際書藉搬到工作平台上，才來比對「作者」、「書藉」是否正確。
但原來一本索引只需200本，現在因為一筆記錄變成原來的3/2倍，所以這本「書名-作者-索引表」，現在則需要300本，想一想，事實上，並不是想像中那麼省事，因為使用「書藉-索引表」，只要拿取200本(索引)+10本(實際書藉)，也許找尋這10本後，真正符合作者為「金庸」的，只有7本；但使用「書藉-作者-索引表」，則要拿取300本(索引)+7本(實際符合的書藉)。
有許多SA，在設計之初，將所有query可能用到的欄位，都一一設定成索引。由上述的推演可以看出，這樣的作法不見得明智，理論上，DBMaker會從最符合的索引開始搜尋(如上述的書藉-作者索引)，但搜尋所花的工夫，卻還不如用單一的作者索引來得快。在設計索引時，必須要將這些因素考量在內，複合鍵有時會造成索引變大，甚至過肥，
I/O加重的結果，反而拖慢了performance。
(3) 書名有『大』這個字，作者姓『金』，約1960-1980年間出版，書的內容含有「宋朝」、「成吉思汗」等字，價錢約在300元至400元間：像類似複雜的要求，常常會讓你不知如何是好；最笨的方法，便是把館藏的500萬本書，三千本、三千本的拿出來(因為要搜尋書本內容，必須先將書放置在工作平台上，再實際查詢每本書的實際內容，在這種方法上，並沒有使用到索引表。)
第二種方法，則是將詢問的所有條件：「書名-作者-發行年份-摘要-價錢」再編成一份索引，那麼類似客戶作這樣的查詢時，便只要查詢這份索引表，便可直接找到符合需要的書藉。
但別忘了在前面的例子中，以這樣來編輯索引的話，可能你的索引頁會變得很厚很多，(2)的例子用了300本，在此的例子，可能用到了1000本。
另外一種作法，則是用現成的索引，現在我們的手上有「書名索引表」，從這裏可以查出有「大」字的書，可能只有1萬本，再把這1萬本搬動到工作平台中，作進一步查詢，這樣的動作介於上述兩者間。
由上看來，再建立一個新的索引表似乎是最聰明的作法囉? 並不盡然，上述的推演過程，只有談到「查詢」的部份，而沒有對「新增」的部份作說明。「索引表」將所有「關鍵的欄位」與「實際存放位置」成對放在一起，所以一旦有新書進來，勢必要將新的資料放進索引表中，假設現在新增了一本書「倚天屠龍記」，若在「書名索引表」，那麼你的工作便是在此索引表中加一筆新的記錄，記載書名及放置的位置，如此即可。但是若有上述的「書名-作者-發行年份-摘要-價錢」索引表的話，要記載的項目就變成很多了。
想想，若是您一天有一萬筆新書進來，又有這「書名-作者-發行年份-摘要-價錢」索引表的話，反而會更痛苦不堪!
有些query是很難去定義出一個好的index，這時只要是「還好」的索引，可能就夠了，上述的query，若將全部欄位建立成索引，可能會造成索引肥大，不見得好，建議將排除效果的欄位先建立好，若是能將所有搜尋的資料頁降低到25%以下，就算是不錯的索引，有些SA甚至會定義「血型」、「性別」為索引，這種索引的排除效果都不是甚佳，除非必要，這樣的索引應儘量避免。
另外常見的情形，便是一個常被變動的表格，本身資料量也很大，也常被查詢，表格本身可能有十個以上的索引，因為常變動，任何的變動都會造成索引值須被更新，十個以上的索引頁，都須被放入記憶體以便進行「新增索引」的動作，想想，這會造成系統效能多大的負擔?!
2.
調整工作平台：
所有的查詢動作都得在工作平台上執行，若是工作平台只能放200本的量，那麼當查詢「作者索引表」時，會將200本的作者索引表放置到工作平台上，找到要查的資料後(金庸著)，再去把符合的書藉拿來。
但是要交付給客戶的書藉也要放在工作平台上，這時就發生了一個問題，工作平台已經滿了(放了200本的索引表)，勢必一定要先將部份索引表拿下來，然後再將找到的書藉放上來。
若是第二個客戶進來，你可能由得去把剛拿下來的索引表再拿回來，這個動作就變成很無謂，原因便出在工作平台不夠大。
用極端的例子來看，若是工作平台大到可容納所有館藏，代表所有資料你都可以直接查詢，不用再有「搬書」的這個動作
記憶體的大小調整，是所有DBA在系統效能調整時，必須先考量的，有些評比報告，常將兩個不同記憶體大小的資料庫效能加以比較，在先天上將造成了不公平，評比的結果自然也讓人存疑，在linux
journal中文版雜誌2001年9月號，針對某家資料庫的效能調整，全文都在探討如何放大記憶體，由此可見記憶體大小對performance的重要。
記憶體當然能放大就儘量放大，讓所有資料能儘量在記憶體中，減少I/O的次數。但這樣也須付出代價，便是當系統毀損時，變動的資料都可能還在記憶體中，雖然DBMaker有LOG可記錄所有變動，但還是會讓recovery的時間拉長。有些個案甚至造成了資料庫的毀損，所以在設計系統時，亦將此因素一併考量，DBMaker在4.0後，會針對系統可用的資源，自動幫使用者劃好可用記憶體，但若使用者本身有其他考量，當然也可自己設定，而不依賴系統的調整；DBMaker要調整記憶體大小，相當容易，只要去調整dmconfig.ini中的DB_NBUFS即可，詳細內容，請參閱Administrator
Guide。
3.
問清楚一點：
有時候，即使你建立了數個有效率索引表，無論新增書藉、查詢書藉，都有頗佳的效率，還是偶爾會有一些客戶，提出了一些匪夷所思的要求。可能某位仁兄，提出了「把所有的書都拿出來瞧瞧」這種要求。你還是得把全部的書都搬出來給他。有些客人的要求又臭又長，其實他的需求卻可能非常的簡單。
這種情形下，即使你已經將所有因素的考慮進去，仍會遇到這樣的客人，則可以要求客人說得明確點，或者換一個聽得懂的方式來問。
Query與索引是相對關係，query是客戶端的需求，而index是query可使用的環境；若是缺少好的index，query寫得再好，效能提昇的也有限。但有的時候正好相反，即使index建的再好，query若不變，效能也無法提昇。由其在optimizer決定execution
plan時，會針對現有環境的index的資料，決定使用哪個index，用merge join或是nest
join，若是query寫得不好，環境再好，還是得作苦工。例如：select * from
big_table，這個query就無可避免的無法使用index。
有關execution plan及optimizer的細節，會在「Performance再探」中探討。
4.
實戰演練：
有一資料庫，記載的dmconfig.ini有下面記述：
[DB]
…
DB_NBUFS=30000 ;120M記憶體
(1) Query垤chema搜集
有一table schema如下：
create index Qtc1 on
SYSADM.Trans( RecePK asc ) fillfactor 100 ;
create index X1 on SYSADM.Trans(
GroupID asc , CustomerIDNo asc ) fillfactor
100 ;
create index X2 on SYSADM.Trans(
GroupID asc , SalItem asc ) fillfactor 100 ;
create index X3 on SYSADM.Trans(
Net asc ) fillfactor 100 ;
create index X4 on SYSADM.Trans(
PreReceNo asc ) fillfactor 100 ;
create index X5 on SYSADM.Trans(
TransNo asc ) fillfactor 100 ;
create index X6 on SYSADM.Trans(
CustomerIDNo asc ) fillfactor 100 ;
create index idxchno
on SYSADM.Trans( CheckNo asc ) fillfactor 100
;
alter table SYSADM.Transprimary
key ( PK) ;
首先就以第一個INDEX X1(GroupID,CustomerIDNo)及第六個INDEX
X6(CustomerIDNo)來看，這就好像一本有「作者-書名」的索引，又有一本有「書名」的索引，事實上，這多的一本「書名」索引，可能並不是那需要，那麼，是不是要把其中一個索引給拿掉呢?這就必須參酌所使用的Query是不是那麼需要這個GroupID，在監控server端所收到的query(「select
SQL_CMD from SYSUSER;」)一個小時後，發現使用到：CustmerIDNo=?有15次，GroupID=?
and CustomerIDNo=? 有10次，GroupID=?約有3次。
(2) 分析與調整
再來，就是看看GroupID的排除效果有多強，使用「select count(*) from
Trans;」及「select count(distinct GroupID) from
Trans」，結果分別是50萬筆及1萬筆，也就說平均50筆會對1個GroupID，這種排除效果倒也頗佳。
有這兩種因素考量下，首先我們可砍掉X6，因為事實上使用X1也有一樣的效果，只是X1可能稍大了點(有兩個欄位)，但GroupID的欄位型態為INT，只佔4個bytes，因此多出來的size應該還在可忍受範圍內。
再者，對於X1的次序也應作調整，建立成(CustomerIDNo,GroupID)的話，一方面可讓CustomerIDNo=?的query獲利，即使GroupID=?，仍可使用此索引。而且若此table常被更動的話，那麼每新增一筆資料，X1與X6都需同步更新，若只留X1，就可少去一次更新動作。
利用select * from SYSINDEX;，發現其中：
|
