Derby這個完全Java開發的開源的數據庫也不例外因此你必須保證它不會成為你程序的一個瓶頸盡管人們可以在Derby的手冊中找到關於這個話題全面的資料我還是想更詳盡的關注一下這些問題基於我的經驗提供一些具體的例子本文將著重於那些由在大的數據表中選擇查詢數據而產生的程序性能問題
首先有很多關於調整Derby屬性（諸如頁面大小和緩存大小等）的技巧修改這些參數可以在一定程度上調整數據庫的性能但是在通常情況下更主要的問題來自與你的程序和數據庫的設計因此我們必須首先關注這些問題最後再來考慮Derby的屬性
在接下來的段落裡我將介紹一些能夠優化程序中有問題部分的技術但是和其他性能優化操作一樣我們需要在優化前先測量並確認問題所在
一個簡單的例子
讓我們從一個簡單的例子開始假設我們Web程序中擁有一個search/list的頁面要處理一個有接近行的表並且那個表不是很小的（至少有欄）用簡單的JDBC來寫一個例子這樣我們可以專注在數據庫和JDBC問題上來這篇文章中介紹的所有准則對所有的面向對象的映射工具都適用
為了使得用戶能夠列出一個大的表通常使用下面簡單的查詢語句
select * from tbl
對應的JDBC語句如下
ClassforName(orgapachederbyjdbcClientDriver)newInstance();
Connection connection = DriverManagergetConnection (
jdbc:derby://localhost:/testDb;);
Statement stmt = connectioncreateStatement();
ResultSet rs = stmtexecuteQuery(select * from tbl);
ArrayList allResults = new ArrayList();while (rsnext()) {
// ObjectRelation mapping code to populate your
// object from result set row
DomainObject domainObject = populate(rs);
allResultsadd(modelObject);
}
Systemoutprintln(Results Size: + allResultssize());
在這兒我們碰到了第一個問題執行這樣的代碼並產生（或更多）個domain對象將肯定會導致java用完堆棧空間產生一個javalangOutOfMemoryError的錯誤對於初學者來說我們首先必須找到一個方法來使得這個程序工作
分頁Result Sets
隨著程序中數據量的增多你首先想到的應該做的事就是為特定的記錄（通常是視圖）提供分頁支持正如你在這個介紹性的例子中看到的簡單地去獲取龐大的result sets很容易導致 out of memory的錯誤
許多數據庫服務器支持特定的SQL結構它們可以用於獲得一個查詢結果的特定的子集例如在MySQL中提供了LIMIT和OFFSET關鍵字它們可以用於select查詢因此如果你執行類似下面的查詢
select * from tbl LIMIT OFFSET
你的結果集將包含從第個結果開始的行即使原先的查詢返回了行許多其他的數據庫提供商通過不同的結構提供了相似的功能不幸的是Derby並沒有提供這樣的功能所以你必須繼續使用原先的select * from tbl查詢語句然後在應用程序中實現一個分頁的機制讓我們來看下面的例子
ClassforName(orgapachederbyjdbcClientDriver)newInstance();
Connection connection = DriverManagergetConnection(
jdbc:derby://localhost:/testDb;);
Statement stmt = connectioncreateStatement();
ResultSet rs = stmtexecuteQuery(SELECT * FROM tbl);
ArrayList allResults = new ArrayList();int i = ;
while (rsnext()) {
if (i > && i <= ) {
// OR mapping code populate your row from result set
DomainObject domainObject = populate(rs);
allResultsadd(modelObject);
}
i++;
}
Systemoutprintln(Results Size: + allResultssize());
通過這些額外的語句我們提供了分頁的功能盡管所有的結果都從數據庫服務器中取出了但是只有那些我們感興趣的行才真正的映射到了Java的對象中現在我們避免了先前碰到的OutOfMemoryError的問題了這樣保證了我們的程序可以真正的工作在大的數據表上
然而通過這個解決方案數據庫仍然會掃描整個表然後返回所有的行這還是一個非常消耗時間的任務對於我的事例數據庫來說這個操作的執行要花費秒鐘這在程序中顯然是不可接受的
因此我們必須給出一個解決方案我們並不需要返回所有的數據庫行而只需要那些我們感興趣的（或者至少是所有行的最小可能子集）我們這兒使用的技巧就是顯式的告訴JDBC驅動我們需要多少行我們可以使用javasqlStatement接口提供的setMaxRows（）函數來完成這個任務看以下下面的例子
ClassforName(orgapachederbyjdbcClientDriver)newInstance();
Connection connection = DriverManagergetConnection(
jdbc:derby://localhost:/testDb;);
Statement stmt = connectioncreateStatement();
stmtsetMaxRows();
ResultSet rs = stmtexecuteQuery(SELECT * FROM tbl);
ArrayList allResults = new ArrayList();
int i = ;while (rsnext()) {
if (i > && i <= ) {
// OR mapping code populate your row from result set
DomainObject domainObject = populate(rs);
allResultsadd(modelObject);
}
}
Systemoutprintln(Results Size: + allResultssize());
值得注意的是我們把最大行的值設置為了我們需要的最後一行（增加了）因此通過這樣的解決方案我們不是僅僅取得了我們想要的行而是先獲取了行然後從中篩選出我們感興趣的行不幸的是我們沒有辦法告訴JDBC驅動從一個具體的行開始因此我們必須說明要顯示的記錄的最大行數這就意味著返回最初的一些記錄的操作的性能是很好的但是隨著用戶浏覽的結果的增多性能也會下降好消息就是在大多數的情形下用戶不會浏覽的太多的記錄他們會在前幾條記錄重獲得他們尋找的行或者改變查詢策略在我本人的環境中上述的例子的執行時間從秒降到了秒
這是一個描述如何浏覽整個表的簡單的例子但是當查詢語句中增加了特定的where條件和排序信息時事情又開始變化了在接下來的部分裡我將解釋為什麼這種情況會發生以後我們如何保證在那些例子中獲得可接受的性能
確保使用索引（避免全表掃描）
索引在數據庫設計中是一個非常重要的概念因為本文所涉及的范圍有限我並不會詳細的介紹索引理論簡單來說索引是特定的數據庫結構能夠允許對表中的行進行快速訪問索引通常是在一欄或多欄上創建的因為他們比整個表小了很多他們的主要用處就是快速搜索一欄（多欄）中的值
Derby自動的為主鍵和外鍵的欄以及具有唯一性限制的欄創建索引對於其他任何欄我們必須顯式的創建索引在接下來的段落中我們將研究一些例子來介紹索引在什麼時候有用以及為什麼有用
但是首先我們必須做一些准備在我們開始優化之前我們需要能夠了解我們執行查詢操作的時候數據庫中發生了什麼Derby提供了derbylanguagelogQueryPlan這個參數如果設置了這個參數Derby將會把所有執行的查詢的查詢計劃（query plan）記錄在derbylog這個文件中（這個文件在derbysystemhome文件夾中）我們可以在啟動服務器之前通過合適的derbyproperties文件或者執行如下的java語句來設置該參數
SystemsetProperty(derbylanguagelogQueryPlan true);
通過檢查查詢計劃我們可以觀察Derby在查詢中是使用了索引還是進行了全表查詢全表查詢是一個很耗時間的操作
既然我們已經設置好了環境我們可以開始我們的例子了假設我們先前使用的表 tb中有一個沒有索引的欄叫做owner因為對查詢結果的排序通常是查詢性能低下的主要原因我將介紹所有與排序有關的優化現在如果我們希望修改先前的例子來根據這一欄的值來排序我們的結果我們需要把我們的查詢語句改成如下的樣子
SELECT * FROM tbl ORDER BY owner
如果我們用這個查詢語句代替先前的語句執行的時間將是先前的好多倍盡管我們分頁（paginated）了所有的結果並小心的設置了要獲取的行數總的執行時間將會是秒
如果我們查看derbylog文件中查詢執行計劃我們可以輕易的發現問題
Table Scan ResultSet for TBL at read committed isolation
level using instantaneous share row locking chosen
by the optimizer
這意味著Derby為了將記錄排序是在整個表中執行了查找這個操作那我們可以做些什麼來改善這個情況呢？答案很簡單在這一欄上創建一個索引我們可以通過如下的SQL語句來做這件事
CREATE INDEX tbl_owner ON tbl(owner)
如果我們重復我們先前的例子我們將得到一個和我們沒有做排序前的那個例子相似的結果（在我的機器上是不到秒）
同樣如果你現在查詢derbylog你將看到下面的信息（而不是和上面的一樣的）
Index Scan ResultSet for TBL using index TBL_OWNER
at read committed isolation level using share row locking
chosen by the optimizer
這就意味著我們可以確保Derby使用了剛創建的索引來獲取合適的行
使用合適的索引順序
我們已經看到了索引是如何幫助我們改善了排序某一欄數據時的性能但是如果我們嘗試去反轉排序的順序的時候會發生什麼呢？假設我們希望根據owner欄降序分類我們的數據在這種情況下我們原先的查詢就會變成如下的語句
SELECT * FROM tbl ORDER BY owner DESC
注意我們增加了DESC這個關鍵字該關鍵字將按降序來排序我們的結果如果我們執行這個新修改過的查詢語句將會發現整個執行的時間又增加到先前的－秒並且在日志文件中你將會發現又是執行了全表掃描
解決的方法就是為這一欄創建一個降序的索引對於我們的owner欄我們執行如下的SQL語句
CREATE INDEX tbl_owner_desc ON tbl(owner desc)
現在我們對這一欄有兩個索引了（兩個順序）因此查詢性能又恢復到了可接受的范圍了注意查詢日志中這一行
Index Scan ResultSet for TBL using index TBL_OWNER_DESC
at read committed isolation level using share row locking
chosen by the optimizer
這使我們確信我們使用了新建的索引因此如果你經常要對結果進行降序排序的話你應該考慮創建一個合適的索引來獲取更高的性能
重建索引
隨著時間的流逝索引記錄將產生碎片這將導致嚴重的性能下降例如如果我們有一個很久以前創建的索引例如tb表的time_create欄
如果我們執行如下的查詢
SELECT * FROM tbl ORDER BY time_create
我們得到很差的性能很大可能是因為我們根本沒有一個索引不過如果我們看了以下日志就可以發現問題所在你會發現我們使用了索引但是將看到和下面相似的信息
Number of pages visited=
這意味著數據庫在索引查詢過程中執行了大量的IO操作這就是這個查詢過程的瓶頸所在
這種情況的解決方法就是重建索引（drop然後重建它）這將使得索引進行整理碎片從而節省我們大量的IO操作時間我們可以通過下面的SQL語句來重建索引
DROP INDEX tbl_time_createCREATE INDEX tbl_time_create ON tbl(time_create)
你將發現執行時間又降到一個可接受的值（秒以內）
同樣你在日志文件中將發現如下的行
Number of pages visited=
正如你看到的執行時間明顯的下降了是因為數據庫執行了很少的IO操作
因此通常的規則就是讓你的程序定期重建索引最好是在程序計劃任務一個後台的工作來不時的完成這個工作
多欄索引
到目前為止我們專注於簡單的單欄的索引和簡單的查詢創建owner和time_create的單欄索引可以幫助我們進行過濾和排序即使時下面的查詢語句也具有可接受的性能
SELECT * FROM tbl WHERE owner = dejanAND time_create > ::ORDER BY time_create
但是如果你嘗試執行如下的查詢
SELECT * FROM tbl WHERE owner = dejan ORDER BY time_create
那又會是一個漫長的執行過程這是因為數據庫為了排序數據需要執行額外的排序步驟
解決這種類型的查詢的辦法就是創建一個包含owner和time_create的索引我們可以通過執行下面的查詢來創建索引
CREATE INDEX tbl_owner_time_create ON tbl(owner time_create)
通過使用這個索引查詢的性能將會得到很大的改善現在注意下面的分析日志
Index Scan ResultSet for TBL using index TBL_OWNER_TIME_CREATE
at read committed isolation level using share row locking
chosen by the optimizer
我們通過使用一個便利的索引來使得數據庫可以快速的找到已經排好序的數據
這個例子中值得注意的是在create index語句中的欄的順序是非常重要的多欄索引只有通過在創建索引時定義的第一個欄時才是可優化的因此如果我們創建了如下的索引
CREATE INDEX tbl_time_create_owner ON tbl(time_create owner)
而不是先前我們使用的索引我們將不會發現什麼性能的優化那是因為derby的優化器不認為這個索引是最好的解決方案從而忽略了它
索引的缺點
索引可以幫助我們在選擇數據的時候改善性能當然這也減慢了數據庫插入刪除以及一些更新操作因為我們不僅僅有表結構還有很多的索引結構所以當數據發生變化時維護所有的結構是很耗時間的
例如當我們在表中插入一行數據的時候數據庫必須更新和這個表的欄有關的所有的索引這就意味著它必須將一個已索引的欄的數據插入到合適的索引中這將很花時間同樣的事也會在你刪除一個特定的行的時候發生因為索引必須保證順序對於更新操作來說只有當你更新了已索引的欄的時候受到影響因為數據庫必須重新定位這些索性來保持索引的順序
因此優化數據庫和程序設計的關鍵在於你的需要不要索引每一個欄你不一定會要用到這些索引而且你可能需要優化你的數據庫來進行快速的插入在早期就開始測試數據庫的性能並發現瓶頸只有那時你才該去應用本文中提到的技術
結論
在本文中我們研究了一些在日常開發過程中遇到的關於性能的問題大多數的准則（或進行適當的修改）都可用於任何關系數據庫系統還有很多其他的技術可以幫助你改善你程序的性能緩存當然是最有效和應用最廣泛的方法之一了對於Java程序員來說許多的緩存解決方案（部分如OSCache或者EHCache等開源許可的下的方案）都可以看作是程序和數據庫之前的緩存從而提高整個程序的性能同樣Java項目中用到的許多面向對象的框架（如Hibernate）都擁有內置的緩存能力所以你應該考慮這些解決方案不過那是另一個討論文章的內容了
From:http://tw.wingwit.com/Article/program/Java/hx/201311/25975.html
