手机登录不了建设银行网站,搜索图片识别,国产化网站建设,网页设计页面链接数据库的查询功能#xff0c;其性能终究是有限的。即使我们对数据库进行了最优配置#xff0c;对数据表设计再三斟酌#xff0c;然而一旦面临海量数据#xff0c;且返回结果集较大的时候#xff0c;常规的查询语句就无能为力了。一般说来#xff0c;当返回的结果集超过总… 数据库的查询功能其性能终究是有限的。即使我们对数据库进行了最优配置对数据表设计再三斟酌然而一旦面临海量数据且返回结果集较大的时候常规的查询语句就无能为力了。一般说来当返回的结果集超过总数量的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值故在查询之前的初始化过程需要耗费较长的时间。 两种方案各有优势。另外对于分页查询时我们还可以使用游标来实现。但是如果是多种数据库使用游标的方式不便于数据库脚本的移植应该慎用。 转载于:https://www.cnblogs.com/kikee/archive/2005/04/19/140783.html
