转自：http://www.cnblogs.com/wayfarer/archive/2005/04/19/140609.html

数据库的查询功能，其性能终究是有限的。即使我们对数据库进行了最优配置，对数据表设计再三斟酌，然而一旦面临海量数据，且返回结果集较大的时候，常规的查询语句就无能为力了。一般说来，当返回的结果集超过总数量的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值，故在查询之前的初始化过程需要耗费较长的时间。

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

# re: 怎样改进数据库的查询性能? 2005-04-19 17:13 none

请问：

数据查询的时候数据会更新吗？

如果更新的话，更新数据的入口点，你可以控制吗？也即使你是否能让所有更新数据的入口都在某个程序中？

最重要的一点，需要对所有数据进行动态排序吗？（就是用户会更换排序条件）如果会，那么提供排序的列有几个？  回复  更多评论   

# re: 怎样改进数据库的查询性能? 2005-04-19 17:33 听棠.NET

楼主的思考是有一定的道理的。
我一般是使用GUID做主键的。那怎么办呢？？  回复  更多评论   

# re: 怎样改进数据库的查询性能? 2005-04-19 17:47 lay

方案2确实效率高，但对表设计有要求，不光是程序上的处理  回复  更多评论   

# re: 怎样改进数据库的查询性能? 2005-04-19 18:10 Austin leng

方案2的确可以达到较高的效率，也是可行的。
但是，使用频率应该是不高，道理其实只有一个，因为大多数的排序都不是按主键来排序的。
所以，分页存储过程，我认为，使用得最多的倒是SQL语句拼凑的方法来分页，和使用表变量（或临时表）的方法来分页的最多。
  回复  更多评论   

# re: 怎样改进数据库的查询性能? 2005-04-19 20:44 黄金狮子旗下

插临时表的办法分页效率不好。还是SQL语句嵌套方式好。  回复  更多评论   

# re: 怎样改进数据库的查询性能? 2005-04-19 21:22 dudu

博客园采用的是方案一, 看来需要改进一下。  回复  更多评论   

# re: 怎样改进数据库的查询性能? 2005-04-19 22:23 wayfarer

None问得很有道理。在查询的时候，如果有最新的更新，是否能显示，要看这条新记录插入的时机。然而由于每页查询耗时比较少，且每次查询都会根据条件和排序进行select，因此影响不会太大。

然而方案二的排序方式，必然要受到排序的影响。至少在进行排序时，IdCdr必须是排序的主字段。

另外，GUID使用这种方式是可以的。虽然GUID是随机产生的，但它仍然有顺序。只是比起Identity字段，要慢一些。而且如果有实时插入的新纪录的话，可能会在查询的时候会漏掉。

@dudu
如果记录不时太多，比如达到百万级。且使用Sql Server的话，我觉采用方案一结合top就够了。  回复  更多评论   

# re: 怎样改进数据库的查询性能? 2005-04-19 23:39 湘南和也

楼主的第二个方案和我用的完全一样。这个写起来很方便，我就一直这么用了。

但如果真从数据库性能来讲它并不是很好的，特别是用到了not in的时候，最好连in都不要用。

我目前正在思考怎样优化这种sql语句。  回复  更多评论   

# re: 怎样改进数据库的查询性能? 2005-04-20 08:45 none

楼主是否考虑过在程序中实现缓存呢？

比如说：把IdCdr索引完全的读到程序中，缓存起来（比如ArrayList，当然有泛型更好），下次要查，在这里得到ID号，然后根据ID号直接到数据库查出数据，数据总记录数量也可以统计缓存中的索引数量得出，并且做任何更新的时候，都需要同步这个索引，如果有多个排序，那就给每个排序维护一个索引，楼主是否也考虑到内存的问题了，是的，这样相当消耗内存，我做过，大约1000W的ID存于ArrayList中会使用大约160M的内存，如果有泛型就好很多了！不过就便是160M，对于1000W数据的应用来说又算得了什么呢？这样做的话，在1600+512内存的情况下，任何一页数据的获取都只需要15-60ms就够了，而对用户来说，完全察觉不到。。。  回复  更多评论   

# re: 怎样改进数据库的查询性能? 2005-04-20 09:27 wayfarer

@None

正如你说的，还是内存的问题。消耗这么多内存，以换取几毫秒，或者几秒钟的性能，不划算。

因为程序是作为客户端安装在客户机上，机器的配置也不会太高。  回复  更多评论   

# re: 怎样改进数据库的查询性能? 2005-04-20 09:52 none

恩那，我觉得如果数据库在服务器上呢，就比较适合，如果数据库在客户端确实就没有必要了，如果客户端只负责显示，而服务器上负责业务逻辑，可能也比较适合实现。  回复  更多评论   

# re: 怎样改进数据库的查询性能? 2005-04-20 09:53 none

对了，关键在于，这样实现之后，数据库服务器的开销将会大大降低，我记得我测试的时候CPU基本没超过2%过，如果不用，则一直在40%以上  回复  更多评论   

# re: 怎样改进数据库的查询性能? 2005-04-20 10:11 cmoremore

这个对数据库表又要求吧？我的表没有唯一标示的字段怎么办？  回复  更多评论   

# re: 怎样改进数据库的查询性能? 2005-04-20 12:00 none

呵呵，这个是有假设基础的，假设基础就是肯定有唯一标识  回复  更多评论   

# re: 怎样改进数据库的查询性能? 2005-04-20 16:14 Da.Feng

哎，偶看来已经固步自封了  回复  更多评论   

# re: 怎样改进数据库的查询性能? 2005-06-09 18:01 BOBO

我的表也没有唯一的标识符，我准备在方法二的基础上＋一个identity的临时表来实现，不知道可不可以？  回复  更多评论   

# re: 怎样改进数据库的查询性能? 2005-06-30 21:23 er

er  回复  更多评论   

# re: 怎样改进数据库的查询性能? 2005-06-30 21:32 iww

各位大侠：
我有一个问题，请问在sql server中百万数据量的表上

针对一个或两个字段进行LIKE条件检索的时间大概是多少亚？

谢谢！

例如：
select title,author,abstract
from books
where ((books.abstract like '%你好%')
and (books.titile like '%题目%'))


我这里没有这种规模的数据库，望各位大侠帮忙一下。
能把结果发到我的信箱吗？ happyiww◎126.com  回复  更多评论   

# re: 怎样改进数据库的查询性能? 2005-08-19 11:41 爱天

我是初学者,请作者帮帮忙,我现在做了一个简单的客户资料档案系统,请问怎样才能让他有外部连接呀,谢谢指教.  回复  更多评论   

# re: 怎样改进数据库的查询性能? 2005-08-19 11:42 爱天

我的QQ:42575475 邮箱:wang7261712@126.com  回复  更多评论   

# re: 怎样改进数据库的查询性能? 2006-12-15 23:26 yunhuasheng

写的好