转载

大数据量实时统计排序分页查询(并发数较小时)的几点建议

大数据量实时统计排序分页查询的瓶颈不是函数(count,sum等)执行,

不是having, 也不是order by,甚至不是表join, 导致慢的原因就在于“数据量太大本身”

 

  1. 化整为零

就是将表划分为M份相互独立的部分,可以是分表,也可以是不分表但冗余一个取模结果字段

实际结果是不分表比分表更加灵活,只需稍加配置,就可以动态切分大表,随意更改M的大小。

 

将1条慢sql(大于30秒)拆分成为N条查询速度巨快的sql(单条sql执行时间控制在20毫秒以内)

然后再web应用中以适当的线程数去并发查询这些执行时间快的N条小sql再汇总结果

 

  1. 两步查询

第一步查询中去并发执行这N条小sql, 只取排序字段和标识字段,其他字段一律丢弃

汇总结果后定位出当前页面要显示的pageNum条数据,再进行第二步查询,取出页面上需要展示的所有字段

 

  1. web应用自身计算与数据库计算的折中

PS:这一点是至关重要的,其他几点都可以不看,这点是最关键的。慢慢解释一下:

有三种方式统计所有的记录,

a)         第一种方式是把数据库中所有记录(只取排序字段和标识字段并且不做任何sum,count having order by等操作)

全部拉到web应用中,在web应用中完成所有的计算

b)         第二种方式是把数据库中所有记录做sum count having等操作之后的所有行数拉到web应用中,在web应用中完成剩余计算

c)         第三种方式是把数据库中所有记录做sum count having order by等操作之后把limit后的数据拉到web应用中,

在web应用中对limit后的数据再计算

 

显然,第一种方式 数据库什么活都不做只取数据 是不可行的。以lg_order_count_seller为例,1500万行,

如果只算id, seller_id和order_count 这三个bigint类型,至少需要拉8*3*1500 0000 = 360000000=340M,

拉到内存中之后存储需要8*4*15000000= 460M,这还不算List是的2的n次方这个特点和计算排序等的内存开销,

不仅数据库与web应用机器IO扛不住,就是应用自身恐怕也要OOM了。

 

第二种方式,所有记录做sum count having等操作之后,由于是group by seller_id的,总得数据量变为100万(就是卖家总数),

这样子一来,共需要拉8*3*100 0000 = 23M,拉到内存之后,需要8*4*100 0000 = 30M, 再算上List是的2的n次方这个特点和

计算排序等的内存开销也不会超过100M, IO的时间和内存开销勉强可以考虑接受。

 

第三种方式,所有记录做sum count having order by等操作之后把limit后的数据拉到web应用中,因为做了limit,所以,

数据量很小了,无论是IO还是内存开销都已经很小了。可以忽略。

 

综合以上三种,第三种方式适用于页面的前n页和后n页,因为这个limit的数据量随着页数的增大而增大,

当大到每个切分后的小表的数据量时就转为第二种方式了。

第二种方式适用于页面的第[n+1, totaoPageNum-n]页。

  1. 切分成N条小sql后并行执行时排序不稳定性的解决办法

① 问题描述:

 

优化之前,还是是一条大慢sql查询时,由于数据库排序是稳定排序,

所以当两条记录排序字段值相同时他们在页面上的页码位置是固定的。

 

优化之后,当并行执行这N条小sql时,由于无法控制这些小sql的先后执行顺序,

导致在web应用中当两条记录的排序字段值相同时在页面上的页码位置是随机的。

 

② 解决办法:

除了拉标识字段(seller_id)和排序字段(order_count_sum)之外,再取一个unique(id)的字段,当两条记录的排序字段值相同时,

再用这个unique的字段(在卖家监控中这个字段是id)进行第二次排序.这样就解决了排序不稳定的问题。

   

③ 也许,看到这里会有疑问,为什么不用seller_id?seller_id也是唯一, 这样子不是少取id这个字段,减少IO了?

seller_id虽然也是唯一,可以辅助排序,但是不要忘记数据库的排序规则是:

如果两列的值相等,那么序号在前的排在前面,这里的序号就是主键(自动生成,autoincrement),

如果用seller_id的话还是不能保证排序的稳定性,只能用主键id.

 

  1. 优先加载页面上的主要元素,然后再去异步加载次要元素,

把数据库的连接,扫表,计算等资源优先让给用户关注的主要元素,次要元素可等主要元素加载完成之后再加载。

反应在卖家监控页面中,查数据和查页页码的sql语句基本相同,是在竞争同一资源,

所以,需要做一个策略,优先把资源让给查数,数据查完之后再去查页码。

 

  1. 限流

由于多线程取数据并没有从本质上提高数据库性能,所以必须针对大数据量实时统计排序分页查询做限流

我这里打个比方:食堂有6个窗口,物流团队吃饭要买6个菜,平均每买1个菜需要1分钟的时间,

如果派我一个人去一个窗口买的话需要6分钟的时间

假如派6个人分别去6个窗口买这6个菜,只需要1分钟的时间

但是,如果除了物流团队,再来其他5个团队呢,也就是说6个团队每个团队买6个菜共买36个菜,

这样子有的团队先买完,有的团队后买完,但平均时间还是6分钟。本质上没有变化。

   所以,对于特定的查询条件,必须进行限流。让每分钟至多有6个团队买菜,这样子能使得情况变得不至于太糟糕。

 

  1. 从根本上改变现状

这一点从目前来看只能是展望了,比如mysql数据库换更为强大的oracle数据库,

或更换InnoDb引擎为其他,或更换SATA硬盘为SSD 。。。。。。

 

 

  1. 从实践效果来看,优化后的效果是很明显的。

相同的查询条件,原来一个页面查询时间由于超过60秒超时了,根据1-6点建议优化之后,查询时间变为2秒至3.5秒之间。


本文旨在介绍一种对数据库中的大数据量表格进行分页查询的实现方法,该方法对应用服务器、数据库服务器、查询客户端的cpu和内存占用都较低,查询速度较快,是一个较为理想的分页查询实现方案。 


1.问题的提出 
在软件开发中,大数据量的查询是一个常见的问题,经常会遇到对大量数据进行查询的场景。

常见的对大数据量查询的解决方案有以下两种:

(1)、将全部数据先查询到内存中,然后在内存中进行分页,这种方式对内存占用较大,必须限制一次查询的数据量。

(2)、采用存储过程在数据库中进行分页,这种方式对数据库的依赖较大,不同的数据库实现机制不通,并且查询效率不够理想。以上两种方式对用户来说都不够友好。

 
2.解决思路 
通过在待查询的数据库表上增加一个用于查询的自增长字段,然后采用该字段进行分页查询,可以很好地解决这个问题。下面举例说明这种分页查询方案。 


(1)、在待查询的表格上增加一个long型的自增长列,取名为“queryId”,mssql、sybase直接支持自增长字段,oracle可以用sequence和trigger来实现。然后在该列上加上一个索引

添加queryId列的语句如下: 
Mssql: [QUERYID] [bigint] IDENTITY (1, 1) 


Sybase: QUERYID   numeric(19)   identity 


Oracle
CREATE SEQUENCE queryId_S 
          INCREMENT BY 1 
          START WITH 1 
             MAXVALUE 999999999999999 MINVALUE 1 
             CYCLE 
             CACHE 20 
             ORDER; 
CREATE OR REPLACE TRIGGER queryId_T BEFORE INSERT 
              ON "test_table" 
              FOR EACH ROW 
BEGIN 
       select queryId_S.nextval into :new.queryId from dual; 
END; 


(2)、在查询第一页时,先按照大小顺序的倒序查出所有的queryId,

语句如下:select queryId from test_table where + 查询条件 +order by queryId desc 。

因为只是查询queryId字段,即使表格中的数据量很大,该查询也会很快得到结果。然后将得到的queryId保存在应用服务器的一个数组中。 


(3)、用户在客户端进行翻页操作时,客户端将待查询的页号作为参数传递给应用服务器,服务器通过页号和queyId数组算出待查询的queyId最大和最小值,然后进行查询。 


算出queyId最大和最小值的算法如下,其中page为待查询的页号,pageSize为每页的大小,queryIds为第二步生成的queryId数组: 
         int startRow = (page - 1) * pageSize
        int endRow = page * pageSize - 1; 
              if (endRow >=queryIds.length) 
                { 
                    endRow = this.queryIds.length - 1; 
                } 
         long startId =queryIds[startRow]; 
         long endId =queryIds[endRow]; 


查询语句如下: 
String sql = "select * from test_table" + 查询条件 + "(queryId <= " + startId + " and queryId >= " + endId + ")"; 


3.效果评价 
该分页查询方法对所有数据库都适用,对应用服务器、数据库服务器、查询客户端的cpu和内存占用都较低,查询速度较快,是一个较为理想的分页查询实现方案。经过测试,查询4百万条数据,可以在3分钟内显示出首页数据,以后每一次翻页操作基本在2秒以内。内存和cpu占用无明显增长。

 

补充:

不久前我也开发过这样的一个数据库,解决的办法是: 1 硬件方面,提高内存容量(这是最重要的),将更多的内存给予ORACLE固定使用。 2 数据库方面,拆分大型表单,使用分时间段数据库,我单位有一个巨型数据也是采用了这种方法!(这非常重要,速度可以提高几十倍左右)。 3 编程方面,尽量不要使用ODBC,采用ORACLE驱动编程,用ODBC太慢。加入每日的统计,加入到你的日报表中去,月底可以加入每月的报表等。 4 看书,查看ORACLE的书籍,对这方面问题应该会有很好的回答(看的书,应该在700页以上,以清华的书为佳)。

 

我认为数据分区、分成多个表、增加内存、换更好的机器都是物理上的,当然她带来的速度的改善是有的。但是性能的改善一般比较少做多10倍到100倍之间。 对Oracle我不熟悉,但在SQL Server中最有效和可行的办法是优化数据库结构和索引。 对于优化数据库有根据事务型和数据仓库型分为两个方面。 偏重事务需要插入、更新速度快,所以一般这样的表索引比较少,字段数目也少 数据仓库需要查询速度快,他一般会根据查询可能出现的条件建立所有的索引,形成所谓的索引覆盖。在大数据量的数据库中,一旦某个查询不能完全利用索引,就会形成表扫描。这是最坏的情况,查询速度同数据量成正比。而如果能完全利用索引,查询速度只有在数据量变化几个等级才会有一些变化。我曾经测试过一个库存表150条记录,索引建立不好一个查询需要4分钟,对索引优化以后1秒不到。如果数据单纯作为查询可以取消对该表的日志功能。 我一般是分成两个库,一个处理事务,一个处理查询,然后建立一个定期事务把事务数据增加到查询库中。 总的来说,只有才所有软的手段不能解决问题的情况下才采用物理的方法。但是物理

正文到此结束
Loading...