怎样改进数据库的查询性能?
来源:互联网 发布:如何打底妆 知乎 编辑:程序博客网 时间:2024/04/28 14:39
在我参与开发的业务里,主要是在通讯行业,如移动、电信或网通,其中数据表数量最多的就是话单记录。通常都会在每个月达到百万级的数量,一年合计就达到千万级了。在这种情况下,除了进行定期备份和清除无效数据等措施,以减少话单总量。在进行话单的查询设计时,仍然需要进行设计上的改进,以满足客户的需求。
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
假定话单表的唯一标识字段为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语句来计算其查询所消耗的时间。如果是在具体的业务界面中,还应加上一些前置、后置操作的耗时,尤其是界面显示结果集的时间。但由于每页记录数相对较小,返回的结果集也较小,因此这些耗时可以忽略不计。
另外,测试记录的时间只包括了查询语句的时间(方案一还包括了建立临时表,并插入记录的时间),没有包含计算符合条件的总记录数时间。
操作系统:Windows
数据库:Sql Server 2000
访问方式:本机直接访问数据库(非客户端访问方式)
总记录数:9,001,789条
每页记录数:2,000条
2.4.2 测试结果
- 怎样改进数据库的查询性能?
- 怎样改进数据库的查询性能?
- 怎样改进数据库的查询性能
- 数据库查询慢的原因及改进
- C# 中 Linq查询结果的循环性能改进
- 提升数据库查询的性能
- 提升数据库查询的性能
- 查询MySQL数据库的性能
- SQL Server性能的改进得益于逻辑数据库设计 (转)
- SQL Server性能的改进得益于逻辑数据库设计
- 隔振器性能的改进
- 对多表联合查询的SQl语句的改进,大幅提高查询性能
- 数据库查询性能提高的方法
- Hibernate如何提升数据库查询的性能
- Hibernate如何提升数据库查询的性能
- Hibernate如何提升数据库查询的性能
- Hibernate如何提升数据库查询的性能
- Hibernate如何提升数据库查询的性能
- NBA Live 2008 新秀猜想
- 解析#pragma指令(转自CSDN)
- SUN开源基于TET的CTI和iSCSI测试工具
- C#的6种常用集合类大比拼
- Windows程序中DC的介绍
- 怎样改进数据库的查询性能?
- Symbian OS 开发初级手册 (2)基本数据类型
- 向其他进程注入代码的三种方法
- GPS导航仪价格到底还要降多少?
- 如何加快Windows的安装过程
- 《商业周刊》评出的2007年IT100强排名
- 小技巧让浏览器远离流氓软件
- shw32.dll,为什么还不更新
- 给Windows目录加口令的特殊方法