改进数据库的查询性能

导读：数据库的查询功能，其性能终究是有限的。即使我们对数据库进行了***配置，对数据表设计再三斟酌，然而一旦面临海量数据，且返回结果集较大的时候，常规的查询语句就无能为力了。一般说来，当返回的结果集超过总数量的40%时，数据库层面上的优化就显得束手无策了。此时，我们应该考虑从sql语句和程序业务上着手。下文中就改进数据库查询性能为大家作出了详细的分析方案。
一、总体思路

通过SQL语句“set rowcount 每页记录数”，并指定每页记录数，每次只查询符合条件记录集中指定的记录数，以达到分页的目的。由于查询功能一般应用在平台界面中，如果通过分页的方式，可以使得单位查询的速度显著提高。同时，返回的结果集也显著减少，这降低了一次查询消耗内存的容量，对于界面的刷新速度也有明显的提高。由于分页查询将原来一次查询的总时间，通过分页的方式，分割为每个小段，因此对于用户而言，每次获得结果的时间就很短了，这在界面与交互设计中，从考虑用户体验的角度出发，也是非常合理的。
由于该方法需要指定每页记录数，因此需要被查询的目的表必须具备一个标识唯一值的字段，并将该字段建立索引，以作为查询和排序的条件。在数据库设计中，有很多种创建标识字段的方法。最简单地莫过于创建Identity字段。当然这种方式的问题也多多，这里不再赘述。也可以写一个存储过程，负责生成唯一标识的ID。

二、实现方案

要进行分页查询，首先需要确定每页的记录数。根据各种业务和局方的不同需求，同时各个局方话单量也各有不同，所以，每页记录数值应放到AAA.ini配置文件中，便于灵活配置。
在分页查询之前，我们需要知道每个月的话单应该的总页数，可以先获得查询目的表的总记录数（以Ctsi业务 (固网点对点短信)为例，下同），SQL语句如下：
select count(1) from CtsiInfoRecord where 条件
注：后面的查询语句中均应包括查询条件，为清楚表现sql语句，本文一律省略该条件。
然后通过总记录数和每页记录数，获得每个月分页查询的总页数。
由于我们的业务主要使用微软的Sql Server2000和sybase。因此，实现分页查询有两种方式。具体实现方案如下：
方案一：通过建立临时表结合分页查询

在微软的Sql Server中，在其T-SQL中引入了top语法，通过该语法可以非常方便的实现分页查询，sql语句为（以Ctsi业务为例）：
select top 每页记录数 * from CtsiInfoRecord01 where IdCdr not in
(select top 页数*每页记录数 IdCdr from CtsiInfoRecord01 order by IdCdr)
order by IdCdr
在实际查询时，只需要修改子查询的top记录数即可。
遗憾的是，该top语法在sybase中并不支持。相对应的语法为set rowcount 记录数。但该语法不能放在子查询语句中，因此，上述的方法无法实现。
根据该方法的实现思路，引入临时表，并结合分页查询来实现，sql语句如下：
set rowcount页数*每页记录数
select IdCdr into #ctsitable from CtsiInfoRecord01 order by IdCdr
set rowcount 每页记录数
select * from CtsiInfoRecord01 where IdCdr not in
(select IdCdr from #ctsitable ) order by IdCdr
drop table #ctsitable
注：#ctsitable为临时库tempdb中的临时表；
在sybase中，不支持在子查询中引入order by；
如果查询***页，则不需要建立临时表，直接查询即可：
set rowcount 每页记录数 select * from CtsiInfoRecord01 order by IdCdr

方案二：直接根据IdCdr条件分页查询

假定话单表的唯一标识字段为IdCdr。如果通过order by进行排序（默认升序），在每页记录数固定以及查询条件相同的前提下，下一页查询的所有记录，其IdCdr值必然大于上一页末记录的IdCdr。如果我们每次查询后，获得了末记录的IdCdr值，然后在下一次查询时，引入该条件，得到的结果必然是根据条件查询出来的下一页结果。方法如下：
set rowcount 每页记录数
select * from CtsiInfoRecord where IdCdr > 上一页末记录IdCdr值 order by IdCdr
如果是上一页查询，则刚好相反，需要获得下一页首记录的IdCdr值：
set rowcount 每页记录数
select * from CtsiInfoRecord where IdCdr < 下一页首记录IdCdr值
注：如果查询首页，则将IdCdr值条件删掉。
如果查询末页，在删掉IdCdr值条件的同时，将排序改为降序的方式。

两种方案实现方式的比较

从Sql语句的角度来看，方案二更简单，也更容易理解。不过相对麻烦的就是需要每次去获得上一页末记录的IdCdr值（或下一页首记录IdCdr值）。前一次查询时，还需要记录首记录和末记录值。另外，方案二是根据上页首记录（或末记录）IdCdr值作为查询条件，它与具体的页数无关，因此，无法直接定位显示某页的结果，除非在之前将各页的首、末记录放到数组中保存下来，但这就要耗费一定的时间。一旦改变了查询条件，数组中保存的值，还需要更新。
方案一，Sql语句较复杂，但并不影响查询的程序。同时，由于其引入了临时表机制，该临时表是放到tempdb数据库中。如果多次查询，则必然会多次删除和创建临时表，带来的结果是tempdb数据库的日志会不段增长。同时由于日志的增长，也会影响使用临时表的性能。如果要具体实现，必须在上述的sql语句中，实时地清除tempdb库中的日志。
总体说来，方案一，Sql语句复杂，但程序设计简单；而方案二则刚刚相反。

两种方案性能的比较

由于上述两种方案都是对sql语句进行改进，因此我在测试时，直接运行sql语句来计算其查询所消耗的时间。如果是在具体的业务界面中，还应加上一些前置、后置操作的耗时，尤其是界面显示结果集的时间。但由于每页记录数相对较小，返回的结果集也较小，因此这些耗时可以忽略不计。
另外，测试记录的时间只包括了查询语句的时间（方案一还包括了建立临时表，并插入记录的时间），没有包含计算符合条件的总记录数时间。

从上文中的分析可以总结出两种方案，各有优势。另外，对于分页查询时，我们还可以使用游标来实现。但是如果是多种数据库，使用游标的方式不便于数据库脚本的移植，应该慎用。大家在选用改进数据库查询性能的方案时要根据自己的实际情况作出***的选择，希望上文中涉及到的内容对大家能够有所帮助。

【编辑推荐】