Dejan Bosanac是一個軟件開發者，技術顧問和作家。他關注不同技術的集成和互操作，尤其是與Java以及Web開發相關的領域。
數據庫在操作少量測試數據和大量數據的時候，表現行為上有很大的差異。通常，在開發過程前期，人們不會關注數據庫性能的問題，但是隨著時間的發展，人們必須采取一些措施來保證數據庫在大量數據的情況下正常工作。
Derby這個完全Java開發的開源的數據庫也不例外，因此你必須保證它不會成為你程序的一個瓶頸。盡管人們可以在Derby的手冊中找到關于這 個話題全面的資料，我還是想更詳盡的關注一下這些問題，基于我的經驗提供一些具體的例子。本文將著重于那些由在大的數據表中選擇查詢數據而產生的程序性能 問題。
首先，有很多關于調整Derby屬性（諸如頁面大小和緩存大小等）的技巧。修改這些參數可以在一定程度上調整數據庫的性能，但是在通常情況下，更主要的問題來自與你的程序和數據庫的設計，因此，我們必須首先關注這些問題，最后再來考慮Derby的屬性。
在接下來的段落里，我將介紹一些能夠優化程序中有問題部分的技術。但是，和其他性能優化操作一樣，我們需要在優化前先測量并確認問題所在。
一個簡單的例子
讓我們從一個簡單的例子開始：假設我們Web程序中擁有一個“search/list”的頁面，要處理一個有接近100，000行的表，并且那個表 不是很小的（至少有10欄）。用簡單的JDBC來寫一個例子，這樣我們可以專注在數據庫和JDBC問題上來。這篇文章中介紹的所有準則對所有的面向對象的 映射工具都適用。
為了使得用戶能夠列出一個大的表，通常使用下面簡單的查詢語句。 select * from tbl
對應的JDBC語句如下：Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
Connection connection = DriverManager.getConnection (
"jdbc:derby://localhost:1527/testDb;");
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("select * from tbl");
ArrayList allResults = new ArrayList();
while (rs.next()) {
// Object-Relation mapping code to populate your
// object from result set row
DomainObject domainObject = populate(rs);
allResults.add(modelObject);
}
System.out.println("Results Size: " + allResults.size());
在這兒，我們碰到了第一個問題。執行這樣的代碼，并產生100，000（或更多）個domain對象將肯定會導致java用完堆棧空間，產生一個 “java.lang.OutOfMemoryError”的錯誤。對于初學者來說，我們首先必須找到一個方法來使得這個程序工作。
分頁Result Sets
隨著程序中數據量的增多，你首先想到的應該做的事就是為特定的記錄（通常是視圖）提供分頁支持。正如你在這個介紹性的例子中看到的，簡單地去獲取龐大的result sets很容易導致 out of memory的錯誤。
許多數據庫服務器支持特定的SQL結構，它們可以用于獲得一個查詢結果的特定的子集。例如，在MySQL中，提供了LIMIT和OFFSET關鍵字，它們可以用于select查詢。因此，如果你執行類似下面的查詢：select * from tbl LIMIT 50 OFFSET 100
你的結果集將包含從第100個結果開始的50行，即使原先的查詢返回了100，000行。許多其他的數據庫提供商通過不同的結構提供了相似的功能。 不幸的是，Derby并沒有提供這樣的功能，所以你必須繼續使用原先的“select * from tbl”查詢語句，然后在應用程序中實現一個分頁的機制。讓我們來看下面的例子：Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
Connection connection = DriverManager.getConnection(
"jdbc:derby://localhost:1527/testDb;");
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM tbl");
ArrayList allResults = new ArrayList();
int i = 0;
while (rs.next()) {
if (i > 50 && i <= 100) {
// O-R mapping code populate your row from result set
DomainObject domainObject = populate(rs);
allResults.add(modelObject);
}
i++;
}
System.out.println("Results Size: " + allResults.size());
通過這些額外的語句，我們提供了“分頁”的功能。盡管所有的結果都從數據庫服務器中取出了，但是只有那些我們感興趣的行才真正的映射到了Java的 對象中。現在我們避免了先前碰到的“OutOfMemoryError”的問題了，這樣保證了我們的程序可以真正的工作在大的數據表上。
然而，通過這個解決方案，數據庫仍然會掃描整個表，然后返回所有的行，這還是一個非常消耗時間的任務。對于我的事例數據庫來說，這個操作的執行要花費10秒鐘，這在程序中顯然是不可接受的。
因此，我們必須給出一個解決方案；我們并不需要返回所有的數據庫行，而只需要那些我們感興趣的（或者至少是所有行的最小可能子集）。我們這兒使用的 技巧就是顯式的告訴JDBC驅動我們需要多少行。我們可以使用java.sql.Statement接口提供的setMaxRows（）函數來完成這個任 務。看下面的例子：Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
Connection connection = DriverManager.getConnection(
"jdbc:derby://localhost:1527/testDb;");
Statement stmt = connection.createStatement();
stmt.setMaxRows(101);
ResultSet rs = stmt.executeQuery("SELECT * FROM tbl");
ArrayList allResults = new ArrayList();
int i = 0;
while (rs.next()) {
if (i > 50 && i <= 100) {
// O-R mapping code populate your row from result set
DomainObject domainObject = populate(rs);
allResults.add(modelObject);
}
}
System.out.println("Results Size: " + allResults.size());
值得注意的是，我們把最大行的值設置為了我們需要的最后一行（增加了1）。因此，通過這樣的解決方案，我們不是僅僅取得了我們想要的50行，而是先 獲取了100行，然后從中篩選出我們感興趣的50行。不幸的是，我們沒有辦法告訴JDBC驅動從一個具體的行開始，因此我們必須說明要顯示的記錄的最大行 數。這就意味著返回最初的一些記錄的操作的性能是很好的，但是隨著用戶瀏覽的結果的增多，性能也會下降。好消息就是在大多數的情形下，用戶不會瀏覽的太多 的記錄，他們會在前幾條記錄重獲得他們尋找的行，或者改變查詢策略。在我本人的環境中，上述的例子的執行時間從8秒降到了0.8秒。
這是一個描述如何瀏覽整個表的簡單的例子。但是當查詢語句中增加了特定的where條件和排序信息時，事情又開始變化了。在接下來的部分里，我將解釋為什么這種情況會發生，以后我們如何保證在那些例子中獲得可接受的性能。
確保使用索引（避免全表掃描）
索引在數據庫設計中是一個非常重要的概念。因為本文所涉及的范圍有限，我并不會詳細的介紹索引理論。簡單來說，索引是特定的數據庫結構，能夠允許對 表中的行進行快速訪問。索引通常是在一欄或多欄上創建的，因為他們比整個表小了很多，他們的主要用處就是快速搜索一欄（多欄）中的值。
Derby自動的為主鍵和外鍵的欄以及具有唯一性限制的欄創建索引。對于其他任何欄，我們必須顯式的創建索引。在接下來的段落中，我們將研究一些例子來介紹索引在什么時候有用以及為什么有用。
但是首先，我們必須做一些準備。在我們開始優化之前，我們需要能夠了解我們執行查詢操作的時候數據庫中發生了什么。Derby提供了 derby.language.logQueryPlan這個參數。如果設置了這個參數，Derby將會把所有執行的查詢的查詢計劃（query plan）記錄在derby.log這個文件中（這個文件在derby.system.home文件夾中）。我們可以在啟動服務器之前通過合適的 derby.properties文件或者執行如下的java語句來設置該參數。 System.setProperty("derby.language.logQueryPlan", "true");
通過檢查查詢計劃，我們可以觀察Derby在查詢中是使用了索引還是進行了全表查詢，全表查詢是一個很耗時間的操作。
既然我們已經設置好了環境，我們可以開始我們的例子了。假設我們先前使用的表 tb1中有一個沒有索引的欄叫做owner。因為對查詢結果的排序通常是查詢性能低下的主要原因，我將介紹所有與排序有關的優化。現在，如果我們希望修改 先前的例子來根據這一欄的值來排序我們的結果，我們需要把我們的查詢語句改成如下的樣子： SELECT * FROM tbl ORDER BY owner
如果我們用這個查詢語句代替先前的語句，執行的時間將是先前的好多倍。盡管我們分頁（paginated）了所有的結果，并小心的設置了要獲取的行數，總的執行時間將會是8秒。
如果我們查看derby.log文件中查詢執行計劃，我們可以輕易的發現問題：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)
如果我們重復我們先前的例子，我們將得到一個和我們沒有做排序前的那個例子相似的結果（在我的機器上是不到1秒）。
同樣，如果你現在查詢derby.log，你將看到下面的信息（而不是和上面的一樣的）：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這個關鍵字，該關鍵字將按降序來排序我們的結果。如果我們執行這個新修改過的查詢語句，將會發現整個執行的時間又增加到先前的8－9秒。并且，在日志文件中，你將會發現又是執行了全表掃描。
解決的方法就是為這一欄創建一個降序的索引。對于我們的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
這使我們確信我們使用了新建的索引。因此，如果你經常要對結果進行降序排序的話，你應該考慮創建一個合適的索引來獲取更高的性能。
轉自：
http://space.itpub.net/?uid-47598-action-viewspace-itemid-207379
posted on 2009-11-24 16:40
chatler 閱讀(1836)
評論(0) 編輯 收藏 引用 所屬分類:
Database