大数据量实时统计排序分页查询优化总结
大数据量实时统计排序分页查询 (并发数较小时) 的瓶颈不是函数(count,sum等)执行,
不是having, 也不是order by,甚至不是表join, 导致慢的原因就在于"数据量太大本身"化整为零
就是将表划分为M份相互独立的部分,可以是分表,也可以是不分表但冗余一个取模结果字段
实际结果是不分表比分表更加灵活,只需稍加配置,就可以动态切分大表,随意更改M的大小。
将1条慢sql(大于30秒)拆分成为N条查询速度巨快的sql(单条sql执行时间控制在20毫秒以内)
然后再web应用中以适当的线程数去并发查询这些执行时间快的N条小sql再汇总结果两步查询
第一步查询中去并发执行这N条小sql, 只取排序字段和标识字段,其他字段一律丢弃
汇总结果后定位出当前页面要显示的pageNum条数据,再进行第二步查询,取出页面上需要展示的所有字段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]页。
切分成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.
优先加载页面上的主要元素,然后再去异步加载次要元素,
把数据库的连接,扫表,计算等资源优先让给用户关注的主要元素,次要元素可等主要元素加载完成之后再加载。
反应在卖家监控页面中,查数据和查页页码的sql语句基本相同,是在竞争同一资源,
所以,需要做一个策略,优先把资源让给查数,数据查完之后再去查页码。
限流
由于多线程取数据并没有从本质上提高数据库性能,所以必须针对大数据量实时统计排序分页查询做限流
我这里打个比方:食堂有6个窗口,物流团队吃饭要买6个菜,平均每买1个菜需要1分钟的时间,
如果派我一个人去一个窗口买的话需要6分钟的时间
假如派6个人分别去6个窗口买这6个菜,只需要1分钟的时间
但是,如果除了物流团队,再来其他5个团队呢,也就是说6个团队每个团队买6个菜共买36个菜,
这样子有的团队先买完,有的团队后买完,但平均时间还是6分钟。本质上没有变化。
所以,对于特定的查询条件,必须进行限流。让每分钟至多有6个团队买菜,这样子能使得情况变得不至于太糟糕。
从根本上改变现状
这一点从目前来看只能是展望了,比如mysql数据库换更为强大的oracle数据库,
或更换InnoDb引擎为其他,或更换SATA硬盘为SSD …
从实践效果来看,优化后的效果是很明显的。
相同的查询条件,原来一个页面查询时间由于超过60秒超时了,根据1-6点建议优化之后,查询时间变为2秒至3.5秒之间。
北上广深人民喜欢哪些纯电动车?能源人都在看,点击右上角加关注中汽协数据显示,2021年新能源汽车产销分别达到354。5万辆和352。1万辆,同比增长均为1。6倍,全年市场占有率提升至13。4。中汽协数据显示,2
恐龙特辑发现号近期科技趣评沧海遗珠,信手拈来01hr恐龙的呼吸道也难免感染梁龙想象图图源Woodruff,etal。根据对一件编号为MOR7029的梁龙骨骼化石的研究,科学家认为这种大型长颈龙可能感染了呼吸
阿里巴巴犀牛智造入选工信部工业互联网平台创新领航应用案例近日,工业和信息化部对外公示2021年工业互联网平台创新领航应用案例名单,来自阿里巴巴迅犀数字科技有限公司(即犀牛智造)的云端智造平台创新应用入选。记者注意到,在今年入选的全部14
中国泰尔实验室颁发移动智能终端续航性能泰尔测评证书近年来,智能手机市场由成长期向成熟期发展,产品续航能力一直是全行业共同关注的问题。中国泰尔实验室针对目前智能终端的续航性能现状及用户使用习惯推出了泰尔测评证书移动智能终端续航性能。
电视声音调不了如何是好?电视声音无法调节的原因在晚上8点之后电视的声音就得调小否则会影响到周围的邻居,如果在白天电视则可以调得大一些,如此哪怕沙发距离电视很远也能听得清楚。那么,电视声音调不了如何是好?下面就跟大家具体分析一下
华为SoundX2021音响再度升级,顶级HiFi的入场券,好声音看得见华为Sound系列音响已经发布了三款,从初代华为SoundX开始,就得到不少音频爱好者的肯定,华为Sound音响也是和法国顶级HiFi音响制作商帝瓦雷联合打造的产品。大锤也是体验了
耳机振膜的材料多种多样哪种最好?对音质的影响大吗?你在购买真无线耳机时最关注的因素是什么呢?根据高通音频产品使用现状调研报告2021显示,有51的人在购买真无线耳机时第一考虑的购买因素是音质。并且,音质已经连续6年成为人们购买真无
8个非常实用的Vue自定义指令在Vue,除了核心功能默认内置的指令(vmodel和vshow),Vue也允许注册自定义指令。它的作用价值在于当开发人员在某些场景下需要对普通DOM元素进行操作。Vue自定义指令有
字节跳动28岁员工猝死,哪些保险产品能为其买单?2月22日深夜,脉脉上字节跳动同事圈板块,一则字节(字节跳动)又猝死一位,妻子怀孕两个月,惨的消息被广泛传开。据说他们家还有30年的房贷,每月2万1,而妻子是个全职太太。事情发生后
经济日报携手京东发布数据爆款产品是怎样炼成的数据来源京东消费及产业发展研究院神器走俏的背后近年来,以懒人神器为起点,各个领域的神器商品层出不穷,销量日益增长。而在神器走俏的背后,反映了消费升级的明显趋势,以及商家围绕消费需求
金观平实打实兑现科技成果转化政策新春伊始,科技成果转移转化迎来政策红利。2015年以来,国家科技成果转化引导基金下设创投子基金近40只,规模将近700亿元。科技成果转化是高度复杂的系统工程,界面多跨度大难度高。建