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 阅读(1782)
评论(0) 编辑 收藏 引用 所属分类:
Database