Dejan Bosanac是一個(gè)軟件開發(fā)者，技術(shù)顧問和作家。他關(guān)注不同技術(shù)的集成和互操作，尤其是與Java以及Web開發(fā)相關(guān)的領(lǐng)域。
數(shù)據(jù)庫在操作少量測(cè)試數(shù)據(jù)和大量數(shù)據(jù)的時(shí)候，表現(xiàn)行為上有很大的差異。通常，在開發(fā)過程前期，人們不會(huì)關(guān)注數(shù)據(jù)庫性能的問題，但是隨著時(shí)間的發(fā)展，人們必須采取一些措施來保證數(shù)據(jù)庫在大量數(shù)據(jù)的情況下正常工作。
Derby這個(gè)完全Java開發(fā)的開源的數(shù)據(jù)庫也不例外，因此你必須保證它不會(huì)成為你程序的一個(gè)瓶頸。盡管人們可以在Derby的手冊(cè)中找到關(guān)于這 個(gè)話題全面的資料，我還是想更詳盡的關(guān)注一下這些問題，基于我的經(jīng)驗(yàn)提供一些具體的例子。本文將著重于那些由在大的數(shù)據(jù)表中選擇查詢數(shù)據(jù)而產(chǎn)生的程序性能 問題。
首先，有很多關(guān)于調(diào)整Derby屬性（諸如頁面大小和緩存大小等）的技巧。修改這些參數(shù)可以在一定程度上調(diào)整數(shù)據(jù)庫的性能，但是在通常情況下，更主要的問題來自與你的程序和數(shù)據(jù)庫的設(shè)計(jì)，因此，我們必須首先關(guān)注這些問題，最后再來考慮Derby的屬性。
在接下來的段落里，我將介紹一些能夠優(yōu)化程序中有問題部分的技術(shù)。但是，和其他性能優(yōu)化操作一樣，我們需要在優(yōu)化前先測(cè)量并確認(rèn)問題所在。
一個(gè)簡(jiǎn)單的例子
讓我們從一個(gè)簡(jiǎn)單的例子開始：假設(shè)我們Web程序中擁有一個(gè)“search/list”的頁面，要處理一個(gè)有接近100，000行的表，并且那個(gè)表 不是很小的（至少有10欄）。用簡(jiǎn)單的JDBC來寫一個(gè)例子，這樣我們可以專注在數(shù)據(jù)庫和JDBC問題上來。這篇文章中介紹的所有準(zhǔn)則對(duì)所有的面向?qū)ο蟮?映射工具都適用。
為了使得用戶能夠列出一個(gè)大的表，通常使用下面簡(jiǎn)單的查詢語句。 select * from tbl
對(duì)應(yīng)的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());
在這兒，我們碰到了第一個(gè)問題。執(zhí)行這樣的代碼，并產(chǎn)生100，000（或更多）個(gè)domain對(duì)象將肯定會(huì)導(dǎo)致java用完堆棧空間，產(chǎn)生一個(gè) “java.lang.OutOfMemoryError”的錯(cuò)誤。對(duì)于初學(xué)者來說，我們首先必須找到一個(gè)方法來使得這個(gè)程序工作。
分頁Result Sets
隨著程序中數(shù)據(jù)量的增多，你首先想到的應(yīng)該做的事就是為特定的記錄（通常是視圖）提供分頁支持。正如你在這個(gè)介紹性的例子中看到的，簡(jiǎn)單地去獲取龐大的result sets很容易導(dǎo)致 out of memory的錯(cuò)誤。
許多數(shù)據(jù)庫服務(wù)器支持特定的SQL結(jié)構(gòu)，它們可以用于獲得一個(gè)查詢結(jié)果的特定的子集。例如，在MySQL中，提供了LIMIT和OFFSET關(guān)鍵字，它們可以用于select查詢。因此，如果你執(zhí)行類似下面的查詢：select * from tbl LIMIT 50 OFFSET 100
你的結(jié)果集將包含從第100個(gè)結(jié)果開始的50行，即使原先的查詢返回了100，000行。許多其他的數(shù)據(jù)庫提供商通過不同的結(jié)構(gòu)提供了相似的功能。 不幸的是，Derby并沒有提供這樣的功能，所以你必須繼續(xù)使用原先的“select * from tbl”查詢語句，然后在應(yīng)用程序中實(shí)現(xiàn)一個(gè)分頁的機(jī)制。讓我們來看下面的例子：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());
通過這些額外的語句，我們提供了“分頁”的功能。盡管所有的結(jié)果都從數(shù)據(jù)庫服務(wù)器中取出了，但是只有那些我們感興趣的行才真正的映射到了Java的 對(duì)象中。現(xiàn)在我們避免了先前碰到的“OutOfMemoryError”的問題了，這樣保證了我們的程序可以真正的工作在大的數(shù)據(jù)表上。
然而，通過這個(gè)解決方案，數(shù)據(jù)庫仍然會(huì)掃描整個(gè)表，然后返回所有的行，這還是一個(gè)非常消耗時(shí)間的任務(wù)。對(duì)于我的事例數(shù)據(jù)庫來說，這個(gè)操作的執(zhí)行要花費(fèi)10秒鐘，這在程序中顯然是不可接受的。
因此，我們必須給出一個(gè)解決方案；我們并不需要返回所有的數(shù)據(jù)庫行，而只需要那些我們感興趣的（或者至少是所有行的最小可能子集）。我們這兒使用的 技巧就是顯式的告訴JDBC驅(qū)動(dòng)我們需要多少行。我們可以使用java.sql.Statement接口提供的setMaxRows（）函數(shù)來完成這個(gè)任 務(wù)。看下面的例子：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());
值得注意的是，我們把最大行的值設(shè)置為了我們需要的最后一行（增加了1）。因此，通過這樣的解決方案，我們不是僅僅取得了我們想要的50行，而是先 獲取了100行，然后從中篩選出我們感興趣的50行。不幸的是，我們沒有辦法告訴JDBC驅(qū)動(dòng)從一個(gè)具體的行開始，因此我們必須說明要顯示的記錄的最大行 數(shù)。這就意味著返回最初的一些記錄的操作的性能是很好的，但是隨著用戶瀏覽的結(jié)果的增多，性能也會(huì)下降。好消息就是在大多數(shù)的情形下，用戶不會(huì)瀏覽的太多 的記錄，他們會(huì)在前幾條記錄重獲得他們尋找的行，或者改變查詢策略。在我本人的環(huán)境中，上述的例子的執(zhí)行時(shí)間從8秒降到了0.8秒。
這是一個(gè)描述如何瀏覽整個(gè)表的簡(jiǎn)單的例子。但是當(dāng)查詢語句中增加了特定的where條件和排序信息時(shí)，事情又開始變化了。在接下來的部分里，我將解釋為什么這種情況會(huì)發(fā)生，以后我們?nèi)绾伪ＷC在那些例子中獲得可接受的性能。
確保使用索引（避免全表掃描）
索引在數(shù)據(jù)庫設(shè)計(jì)中是一個(gè)非常重要的概念。因?yàn)楸疚乃婕暗姆秶邢蓿也⒉粫?huì)詳細(xì)的介紹索引理論。簡(jiǎn)單來說，索引是特定的數(shù)據(jù)庫結(jié)構(gòu)，能夠允許對(duì) 表中的行進(jìn)行快速訪問。索引通常是在一欄或多欄上創(chuàng)建的，因?yàn)樗麄儽日麄€(gè)表小了很多，他們的主要用處就是快速搜索一欄（多欄）中的值。
Derby自動(dòng)的為主鍵和外鍵的欄以及具有唯一性限制的欄創(chuàng)建索引。對(duì)于其他任何欄，我們必須顯式的創(chuàng)建索引。在接下來的段落中，我們將研究一些例子來介紹索引在什么時(shí)候有用以及為什么有用。
但是首先，我們必須做一些準(zhǔn)備。在我們開始優(yōu)化之前，我們需要能夠了解我們執(zhí)行查詢操作的時(shí)候數(shù)據(jù)庫中發(fā)生了什么。Derby提供了 derby.language.logQueryPlan這個(gè)參數(shù)。如果設(shè)置了這個(gè)參數(shù)，Derby將會(huì)把所有執(zhí)行的查詢的查詢計(jì)劃（query plan）記錄在derby.log這個(gè)文件中（這個(gè)文件在derby.system.home文件夾中）。我們可以在啟動(dòng)服務(wù)器之前通過合適的 derby.properties文件或者執(zhí)行如下的java語句來設(shè)置該參數(shù)。 System.setProperty("derby.language.logQueryPlan", "true");
通過檢查查詢計(jì)劃，我們可以觀察Derby在查詢中是使用了索引還是進(jìn)行了全表查詢，全表查詢是一個(gè)很耗時(shí)間的操作。
既然我們已經(jīng)設(shè)置好了環(huán)境，我們可以開始我們的例子了。假設(shè)我們先前使用的表 tb1中有一個(gè)沒有索引的欄叫做owner。因?yàn)閷?duì)查詢結(jié)果的排序通常是查詢性能低下的主要原因，我將介紹所有與排序有關(guān)的優(yōu)化。現(xiàn)在，如果我們希望修改 先前的例子來根據(jù)這一欄的值來排序我們的結(jié)果，我們需要把我們的查詢語句改成如下的樣子： SELECT * FROM tbl ORDER BY owner
如果我們用這個(gè)查詢語句代替先前的語句，執(zhí)行的時(shí)間將是先前的好多倍。盡管我們分頁（paginated）了所有的結(jié)果，并小心的設(shè)置了要獲取的行數(shù)，總的執(zhí)行時(shí)間將會(huì)是8秒。
如果我們查看derby.log文件中查詢執(zhí)行計(jì)劃，我們可以輕易的發(fā)現(xiàn)問題：Table Scan ResultSet for TBL at read committed isolation
level using instantaneous share row locking chosen
by the optimizer
這意味著Derby為了將記錄排序，是在整個(gè)表中執(zhí)行了查找這個(gè)操作。那我們可以做些什么來改善這個(gè)情況呢？答案很簡(jiǎn)單，在這一欄上創(chuàng)建一個(gè)索引。我們可以通過如下的SQL語句來做這件事： CREATE INDEX tbl_owner ON tbl(owner)
如果我們重復(fù)我們先前的例子，我們將得到一個(gè)和我們沒有做排序前的那個(gè)例子相似的結(jié)果（在我的機(jī)器上是不到1秒）。
同樣，如果你現(xiàn)在查詢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使用了剛創(chuàng)建的索引來獲取合適的行。
使用合適的索引順序
我們已經(jīng)看到了索引是如何幫助我們改善了排序某一欄數(shù)據(jù)時(shí)的性能。但是如果我們嘗試去反轉(zhuǎn)排序的順序的時(shí)候會(huì)發(fā)生什么呢？假設(shè)我們希望根據(jù)owner欄降序分類我們的數(shù)據(jù)。在這種情況下，我們?cè)鹊牟樵兙蜁?huì)變成如下的語句： SELECT * FROM tbl ORDER BY owner DESC
注意，我們?cè)黾恿薉ESC這個(gè)關(guān)鍵字，該關(guān)鍵字將按降序來排序我們的結(jié)果。如果我們執(zhí)行這個(gè)新修改過的查詢語句，將會(huì)發(fā)現(xiàn)整個(gè)執(zhí)行的時(shí)間又增加到先前的8－9秒。并且，在日志文件中，你將會(huì)發(fā)現(xiàn)又是執(zhí)行了全表掃描。
解決的方法就是為這一欄創(chuàng)建一個(gè)降序的索引。對(duì)于我們的owner欄，我們執(zhí)行如下的SQL語句。 CREATE INDEX tbl_owner_desc ON tbl(owner desc)
現(xiàn)在我們對(duì)這一欄有兩個(gè)索引了（兩個(gè)順序），因此查詢性能又恢復(fù)到了可接受的范圍了。注意查詢?nèi)罩局羞@一行：Index Scan ResultSet for TBL using index TBL_OWNER_DESC
at read committed isolation level using share row locking
chosen by the optimizer
這使我們確信我們使用了新建的索引。因此，如果你經(jīng)常要對(duì)結(jié)果進(jìn)行降序排序的話，你應(yīng)該考慮創(chuàng)建一個(gè)合適的索引來獲取更高的性能。
轉(zhuǎn)自：
http://space.itpub.net/?uid-47598-action-viewspace-itemid-207379
posted on 2009-11-24 16:40
chatler 閱讀(1840)
評(píng)論(0) 編輯 收藏 引用 所屬分類:
Database