怎样改进数据库的查询性能?

数据库的查询功能，其性能终究是有限的。即使我们对数据库进行了最优配置，对数据表设计再三斟酌，然而一旦面临海量数据，且返回结果集较大的时候，常规的查询语句就无能为力了。一般说来，当返回的结果集超过总数量的40%时，数据库层面上的优化就显得束手无策了。此时，我们应该考虑从sql语句和程序业务上着手。
在我参与开发的业务里，主要是在通讯行业，如移动、电信或网通，其中数据表数量最多的就是话单记录。通常都会在每个月达到百万级的数量，一年合计就达到千万级了。在这种情况下，除了进行定期备份和清除无效数据等措施，以减少话单总量。在进行话单的查询设计时，仍然需要进行设计上的改进，以满足客户的需求。

1． 总体思路

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

2． 实现方案

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

在微软的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

2.2 方案二：直接根据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值条件的同时，将排序改为降序的方式。

2.3 两种方案实现方式的比较

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

2.4 两种方案性能的比较

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

2.4.1 测试环境

操作系统：Windows
数据库：Sql Server 2000
访问方式：本机直接访问数据库（非客户端访问方式）
总记录数：9,001,789条
每页记录数：2,000条

2.4.2 测试结果

	方案一（耗时：秒）	方案二（耗时：秒）
第1页	0.1~0.2	0.1~0.2
第3页（4,000条记录后）	11	0.1~0.2
第10页（20,000条记录后）	12	0.1~0.2
第50页（100,000条记录后）	14	0.1~0.2
第100页（200,000条记录后）	15	0.1~0.2
第1000页（2,000,000条记录后）	47	0.1~0.2

 从测试结果看，方案二在性能上有非常大的优势。由于IdCdr建立了索引，且该值为int类型，因此，查询条件中，IdCdr具体的值对查询没有影响。而方案一由于是通过临时表方式，且临时表的记录数会根据页数的增加而增加，这在一定程度上影响了查询性能。（注：如果是在Sql Server中，且数据量不太大，选择方案一并采用top的方法还是比较优秀的。一般的网页设计时，分页查询均采用这种方式）不过，如果我们不仅是实现上、下页翻页，还要实现指定页查询，则第二种方案由于需要获得所有页首、末记录的IdCdr值，故在查询之前的初始化过程需要耗费较长的时间。

    两种方案，各有优势。另外，对于分页查询时，我们还可以使用游标来实现。但是如果是多种数据库，使用游标的方式不便于数据库脚本的移植，应该慎用