- 浏览: 202160 次
- 性别:
- 来自: 南京
最新评论
-
18335864773:
用国内自主研发的 pageoffice 插件在线操作offic ...
开源组件DocX导出Word -
zhudoney:
用了这种验证的WEBAPI怎么调用啊? 我按你的方法写了,调用 ...
WebApi接口安全认证——HTTP之摘要认证 -
songshijia88888:
正在找这方面的资料,很有用!
android访问远程数据库 -
sqcjy111:
运行之后结果还没没有出来怎么回事啊?
将sqlserver数据生成XML文件 -
zhonglushu:
没看到什么实际意义
android访问远程数据库
论坛上总看到有人说某某数据库几百万的数据量怎么提高查询速度等等,最近正好做了一个关于这方面的表结构优化,分享给大家,希望对大家有帮助。本人也不是什么大牛,只希望互相交流学习。仅为分享,不喜勿喷,谢谢。
言归正传,下面说一下具体的实现及效果。
应用场景:
一张日志表,记录每天150w左右的数据量,应用要求存储6个月以上,则共计27000w左右的数据规模,表从设计初期就考虑到数据增长会很快,所以采用的是日志表的形似记录的内容,前端应用不需和任何表关联,只需从这张表中读取数据即可。应用主要是根据不同的条件进行日志数据的检索和统计功能(时间条件,条件1,条件2),数据检索分页显示。
原始处理方式,单表双TOP语句的方式。但是随着数据量的增长,检索速度越来越慢,老是出现查询超时的问题,同时由于双TOP方式分页的限制,导致页数越接近尾页越缓慢的现象。
为了解决以上的问题,对原始数据表的结构进行了如下的调整。
数据库对于百万级别的数据量,响应速度还是很快的,但是对于上亿级别的数据量而言,由于存在着大量的磁盘IO,所以速度的降低不是线性的,而是呈现指数级的下降,所以,优化方法的中心思想就是减少结果集的数量,提高数据库本身的响应时间。
针对以上的想法,分4个步骤对数据库的存储结构及数据的选取方式进行以下的优化。
1.分而治之
对于几千万甚至亿级的数据,想在如此庞大的基础数据中做到快速响应的读取数据,比较可行的方法就是大而化小的思想。
大而化小的思想对于算法来说,怎样选取“小”这一概念的界定就很关键了,对于如此庞大的表,发现每天产生的数据量相对较稳定,基本维持在150w左右每天,这个数据量对于数据库来说是个可以快速进行响应的数据规模。而选择的太小,比如说按小时计算的方式,虽然单位的数据量减少了24倍,但是相对于数据量从150w到6w的时间提升远比1张表到24张表的维护性提高付出的代价多,所以最后决定采用按日分表的方式进行分表,这样以3个月的数据来说,从13500w的一张大表转化为150w的90张小表。这样可以改善最终结果的选取速度大大提升。
2.合而击之
经过以上的步骤,将大表划分为多个小表,虽然对于最终选取来说,只是从某一个小表中选取最终的数据,但是还是无法避免第一次进行所有涉及表的轮循计算行数的过程,这样与以前的方式相比数据量没有减少反而增加了算法的复杂度,所以单单分而治之的方法无法解决计算行数及页数的轮循产生的时间开销。
针对选取首页时的行数计算,可以反其道而行之,采用合而击之的方式,在数据初始化的时候进行以日为单位的选取条件统计,将统计结果分类存放于月表中,这样在选取时对于整日的区间只要直接从月表中读取响应日期的数据条目就可以了。
目前日志的查询条件有3组,分别是日期,条件1(固定),条件2(固定)。这样以日期为划分依据,就将检索条件由3组缩小为2组,减少了排列组合的数量,同时条件1和条件2的月表存储采用了“鸡兔同笼问题”的逆运算,通过选取条件确定某一类别的方式计算某一条件组合的信息条数。这样,实际的实验数据表明,可以讲150w的数据量缩小的4千左右的数据行数。对于表的数目来说每个月产生一张,代价很小。
CREATE TABLE monthtable
(
条件1,
条件2,
数量,
日期
)
这样就有效地避免了在计算总行数时轮循所有日表的大量读取工作,节省了时间。
3.折半计算
以上的2部,可以解决大表,及轮循时耗费大量时间的问题,但是对于起始日期来说,如果是完整的一天可以直接从月表中读取数据,若不是完整的一天,则还是需要读取日表,虽然最多只需要读取两张日表,但是还是会浪费一些时间。
对于表的顺序选取来说,由于数据库本身的机制限制,所以无法避免的需要进行正逆两次数据的选取,所以现在可以做的就是减少首次选取的行数及减少排序操作的次数。
出于以上的目的,现将日表的数据选取方式修改为折半计算的方式。即选取行之前,先计算所选取的行是否超过表选取数据行数的一半,若未超过仍采用原始的数据选取方式,若超过,则通过倒序选取的方式从数据尾部提取数据,这样当选去的尾行<总行数一半时,选取时间与原是相同,当选取的尾行>总行数的一半时,由于少了一次排序,并且选取的行数也减少了,所以速度优于原始的选取方式,而且越接近数据尾部选取的速度越快,尾页的速度和首页相同,这要比原始的方式速度提升很多倍。
这样,扫描的结果集最大值仅为原始的一半,而且少了一次大结果集的排序过程,时间大大提高。
原始方式流程图:
折中计算流程图:
4.创建统计表
在数据库的数据选取过程中创中间统计表存储结果,用于存取在每个日表中选取的数据行数,只要传入的参数不是选择首页,那么数据库都直接从统计表中读取行数,这样就不用每一页都计算总行数,节省大量时间。
信息查询流程图:
优化前:
优化后:
以上数据选取过程封装在存储过程中统一进行。
统计优化测试数据:
数据量 14141687
单位(ms)
测试计算机配置:
CPU:Inter(R) Celeron(R) CPU 430 @ 1.80GHz 1.79GHz
Memory:2G (计算机存在其他服务,SQL Server 可利用的内存实际在1G左右)
每进行一次数据检索操作,均重启SQL Server服务。
优化前后数据库均已建立相关索引,索引在这里就不列出了。
由于数据库存在数据预热的阶段,所以平均值的计算方式为去掉最大值后计算的平均值,表中红色为去掉的测试时间。
统计属性 |
方案 |
统计列 |
数据1 |
数据2 |
数据3 |
数据4 |
数据5 |
平均 |
条件1 |
优化前 |
14141687 |
51724 |
33373 |
31803 |
30563 |
37223 |
33241 |
条件1 |
优化后 |
14141687 |
347 |
17 |
16 |
17 |
20 |
18 |
比例 |
|
1 |
150 |
1964 |
1988 |
1798 |
1862 |
1847 |
条件2 |
优化前 |
14141687 |
52340 |
35465 |
35760 |
37856 |
34970 |
36013 |
条件2 |
优化后 |
14141687 |
383 |
23 |
17 |
16 |
20 |
19 |
比例 |
|
1 |
137 |
1542 |
2104 |
2366 |
1749 |
1896 |
按日统计 |
优化前 |
14141687 |
57796 |
45356 |
45710 |
44520 |
45586 |
45293 |
按日统计 |
优化后 |
14141687 |
727 |
14 |
17 |
16 |
13 |
15 |
比例 |
|
1 |
80 |
3240 |
2689 |
2783 |
3507 |
3020 |
按月统计 |
优化前 |
14141687 |
55180 |
42130 |
42063 |
42780 |
42493 |
42367 |
按月统计 |
优化后 |
14141687 |
380 |
10 |
7 |
13 |
7 |
9 |
比例 |
|
1 |
145 |
4213 |
6009 |
3291 |
6070 |
4708 |
存储空间占用统计:
数据量14141687
单位(KB)
数据日期区间2011-11-25 ~ 2011-12-29
统计属性 |
占用空间 |
数据空间 |
索引空间 |
表数量 |
优化前 |
2150480 |
1593688 |
557792 |
1 |
优化后 |
2057688 |
1521752 |
535936 |
19 |
比例 |
0.9569 |
0.9549 |
0.9608 |
19 |
信息查询优化测试数据:
数据量 14141687
单位(ms)
每页行数:50
由于数据库存在数据预热的阶段,所以平均值的计算方式为去掉最大值后计算的平均值,表中红色为去掉的测试时间。
条件属性为:
1.统计时间:2011-11-16 0:00:00 ~ 2011-12-30 0:00:00
2.条件1:xxxx
3.条件2:xxxx
测试1:以时间为筛选条件
方案 |
选取页 |
统计列 |
数据1 |
数据2 |
数据3 |
数据4 |
数据5 |
平均 |
优化前 |
首页 |
14141687 |
89426 |
64220 |
61466 |
62850 |
54046 |
60646 |
优化后 |
首页 |
14141687 |
1253 |
113 |
234 |
167 |
200 |
179 |
比例 |
|
1 |
71 |
568 |
263 |
376 |
270 |
339 |
优化前 |
第2页 |
14141687 |
34420 |
6423 |
6480 |
6350 |
6433 |
6422 |
优化后 |
第2页 |
14141687 |
416 |
16 |
14 |
20 |
13 |
16 |
比例 |
|
1 |
83 |
401 |
483 |
318 |
495 |
401 |
优化前 |
第3页 |
14141687 |
34020 |
5546 |
6446 |
6543 |
6486 |
6255 |
优化后 |
第3页 |
14141687 |
537 |
13 |
17 |
20 |
14 |
16 |
比例 |
|
1 |
63 |
427 |
379 |
327 |
463 |
391 |
优化前 |
第141417页 |
14141687 |
36640 |
9853 |
9833 |
10833 |
11160 |
10420 |
优化后 |
第141417页 |
14141687 |
2213 |
504 |
653 |
720 |
517 |
599 |
比例 |
|
1 |
17 |
20 |
15 |
15 |
22 |
18 |
优化前 |
尾页 |
14141687 |
37546 |
11443 |
11456 |
11290 |
11366 |
11389 |
优化后 |
尾页 |
14141687 |
590 |
23 |
17 |
17 |
17 |
19 |
比例 |
|
1 |
64 |
498 |
674 |
664 |
669 |
599 |
测试2:以时间和条件1为筛选条件
方案 |
选取页 |
统计列 |
数据1 |
数据2 |
数据3 |
数据4 |
数据5 |
平均 |
优化前 |
首页 |
66355 |
79003 |
42870 |
40183 |
49413 |
42683 |
43787 |
优化后 |
首页 |
66355 |
1136 |
226 |
163 |
203 |
104 |
174 |
比例 |
|
1 |
70 |
190 |
247 |
243 |
411 |
252 |
优化前 |
第2页 |
66355 |
34773 |
12690 |
12686 |
13026 |
12726 |
12782 |
优化后 |
第2页 |
66355 |
916 |
40 |
30 |
30 |
30 |
33 |
比例 |
|
1 |
38 |
317 |
423 |
434 |
424 |
387 |
优化前 |
第3页 |
66355 |
35746 |
12986 |
14296 |
12816 |
13880 |
13495 |
优化后 |
第3页 |
66355 |
820 |
33 |
37 |
40 |
40 |
38 |
比例 |
|
1 |
44 |
394 |
386 |
320 |
347 |
355 |
优化前 |
第664页 |
66355 |
41143 |
17736 |
17763 |
18763 |
17743 |
18001 |
优化后 |
第664页 |
66355 |
997 |
37 |
40 |
38 |
35 |
37 |
比例 |
|
1 |
41 |
479 |
444 |
494 |
507 |
487 |
优化前 |
尾页 |
66355 |
42463 |
18916 |
20100 |
20156 |
20093 |
19816 |
优化后 |
尾页 |
66355 |
1117 |
96 |
97 |
100 |
99 |
98 |
比例 |
|
1 |
38 |
197 |
207 |
202 |
203 |
202 |
测试3:以时间和条件2为筛选条件
方案 |
选取页 |
统计列 |
数据1 |
数据2 |
数据3 |
数据4 |
数据5 |
平均 |
优化前 |
首页 |
4829945 |
84673 |
60546 |
60303 |
58516 |
58556 |
59480 |
优化后 |
首页 |
4829945 |
2033 |
193 |
184 |
220 |
160 |
189 |
比例 |
|
1 |
42 |
314 |
328 |
266 |
366 |
315 |
优化前 |
第2页 |
4829945 |
39156 |
15083 |
15526 |
15193 |
15210 |
15253 |
优化后 |
第2页 |
4829945 |
713 |
23 |
27 |
30 |
23 |
26 |
比例 |
|
1 |
55 |
656 |
575 |
506 |
662 |
587 |
优化前 |
第3页 |
4829945 |
36806 |
15053 |
15353 |
16230 |
15333 |
15493 |
优化后 |
第3页 |
4829945 |
727 |
20 |
20 |
24 |
17 |
20 |
比例 |
|
1 |
51 |
753 |
768 |
676 |
902 |
775 |
优化前 |
第48283页 |
4829945 |
44203 |
23950 |
24126 |
23386 |
23293 |
23689 |
优化后 |
第48283页 |
4829945 |
1314 |
507 |
500 |
510 |
503 |
505 |
比例 |
|
1 |
34 |
47 |
48 |
46 |
46 |
47 |
优化前 |
尾页 |
4829945 |
46203 |
24390 |
25596 |
25400 |
24333 |
24930 |
优化后 |
尾页 |
4829945 |
2012 |
1087 |
987 |
963 |
1000 |
1009 |
比例 |
|
1 |
23 |
22 |
26 |
27 |
24 |
25 |
测试4:以时间和条件1、条件2为筛选条件
方案 |
选取页 |
统计列 |
数据1 |
数据2 |
数据3 |
数据4 |
数据5 |
平均 |
优化前 |
首页 |
66353 |
78130 |
30340 |
28080 |
28210 |
27090 |
28430 |
优化后 |
首页 |
66353 |
1330 |
120 |
130 |
90 |
107 |
112 |
比例 |
|
1 |
59 |
253 |
216 |
314 |
254 |
254 |
优化前 |
第2页 |
66353 |
35150 |
12660 |
12553 |
12736 |
13250 |
12780 |
优化后 |
第2页 |
66353 |
848 |
20 |
23 |
20 |
20 |
21 |
比例 |
|
1 |
42 |
633 |
546 |
637 |
663 |
609 |
优化前 |
第3页 |
66353 |
34933 |
12996 |
12916 |
14226 |
12843 |
13246 |
优化后 |
第3页 |
66353 |
833 |
27 |
25 |
22 |
29 |
26 |
比例 |
|
1 |
42 |
482 |
517 |
647 |
443 |
510 |
优化前 |
第664页 |
66353 |
73570 |
27630 |
27096 |
26276 |
26053 |
26734 |
优化后 |
第664页 |
66353 |
1305 |
190 |
189 |
210 |
209 |
200 |
比例 |
|
1 |
57 |
146 |
144 |
126 |
125 |
134 |
优化前 |
尾页 |
66353 |
70176 |
32090 |
31000 |
32010 |
32110 |
31803 |
优化后 |
尾页 |
66353 |
1076 |
183 |
187 |
182 |
181 |
184 |
比例 |
|
1 |
66 |
176 |
166 |
176 |
178 |
173 |
测试结果分析:
从统计功能来看,数据库的响应速度提升很大,从30秒左右提升到20毫秒左右,速度最大提升了4700多倍,效果明显。
从查询功能来看,数据库的响应速度提升没有统计功能明显,但是也从30多秒提高到200毫秒左右,提升了约400-500倍左右。
原因分析:统计功能利用本算法可以完全避免对于原始数据表的读取操作,所以对于该功能来说,只是操作几张4-5千条数据的表,所以响应时间快,但是对于查询功能来说,最终返回给用户的信息是具体的消息,所以无法避免的要对数据表进行查找,所以时间上消耗的多了些。但是基本上也都控制在200毫秒以下就可以做出响应。
代价:
本机制采用的是复杂度换时间的方法,即数据库结构和选取方式的复杂度增加了,从而减少了在选取时消耗的时间,并且有一部分统计工作提到了选取操作以外进行,将时间分块,从而在感觉上降低了响应时间。
对于,日表及月表的数据填充工作可以使用数据库的作业,在业务不繁忙的时间进行原始表的划分工作。
对于测试数据来说,1千4百万的数据初始化需要十分钟左右的时间进行,可以选择凌晨1点开始,这样最长1-2个小时就可以把数据处理完毕。并且大量表的初始化只在第一次更换数据库结构时产生,以后每天进行的数据统计工作基本上只有200w左右,这个数量级只需要几分钟就可以完成,完全不影响软件每天的正常使用。
--转至:magician547的专栏
发表评论
-
WebApi接口安全认证——HTTP之摘要认证
2014-12-05 14:07 66858摘要访问认证是一种协议规定的Web服务器用来 ... -
开源组件DocX导出Word
2014-08-18 11:01 71191、使用Docx替换Word模板里书签里内容的一个方法 ... -
.net里使用MSComm控件发生短信
2014-04-13 23:00 2782一.MSComm32.ocx控件(以下简称MSComm控件 ... -
IIS部署的网站性能优化几例
2014-03-09 20:51 0IIS部署的网站,第一访问的时候特别的慢,主要原因是用 ... -
正则表达式语法规则归总【转】
2013-10-30 09:01 1050一、正则表达式概念: 在计算机科学中,是指一个用来描述或者匹配 ... -
jQuery EasyUI之datagrid
2012-08-19 10:12 525981. <table id="mTab&quo ... -
SQL Server 2008 Reporting Services实现匿名访问报表
2011-10-06 15:26 8505实现SQL Server 2008 Reporting Ser ... -
SQL Server 2008 Reporting Services匿名访问报表
2011-10-06 15:07 2实现SQL Server 2008 Reporting Ser ... -
使用C#对Active Directory(活动目录)的操作
2011-06-12 12:03 7366Active Directory(活动目录)是Windows ...
相关推荐
应用场景:一张日志表,记录每天150w左右的数据量,应用要求存储6个月以上,则共计27000w左右的数据规模,表从设计初期就考虑到数据增长会很快,所以采用的是日志表的形似记录的内容,前端应用不需和任何表关联,只...
SQLSERVER,全称Microsoft SQL Server,是微软公司的一款关系型数据库管理系统,广泛应用于数据存储、查询和管理。在教学日历管理系统中,SQLSERVER作为后台数据库,承载着大量的教学数据,如课程信息、教师信息、...
根据提供的文件内容,以下是从...通过上述内容,可以了解到SQL Server在处理大量遥感数据方面的优势,以及如何通过数据库设计提高数据存储和读取的效率。同时,这也突显了在地震学研究中使用高级数据库技术的重要性。
- ROLAP(关系型在线分析处理)依赖于关系数据库进行数据存储,支持复杂的SQL查询,提供高度的灵活性,但可能在大数据量下性能较低。 - MOLAP(多维在线分析处理)使用多维数据结构,如立方体,优化了查询性能,尤其...
这个系统的设计思路和实现技术为处理大量历史数据提供了一个有效的工具,它不仅解决了SQL Server 6.5备份功能的不足,还增加了数据的管理和查询功能,提高了整个系统的性能。对于依赖SQL Server的网络系统来说,这样...
SQL Server是由微软开发的一款关系型数据库管理系统,它支持多种数据库管理和应用程序开发功能,包括数据存储、查询处理、事务管理、备份恢复、安全性控制等。SQL Server以其高性能、高可用性和易于管理的特点,在...
本书讲解了如何使用SQL Server的特性进行表结构设计,包括字段类型选择、主键和外键定义等,以及如何创建存储过程和触发器来实现复杂的数据逻辑处理。此外,还涉及了查询优化技巧,如索引使用策略、查询计划分析等,...
在这个项目中,SQL Server作为数据存储的核心,负责存储仓库的各项数据,如货物信息、库存量、进出库记录等。通过SQL语句,Java程序可以与数据库进行交互,执行CRUD(Create, Read, Update, Delete)操作,确保数据...
- **规模考量**:考虑到系统的用户量约为15000人,图书量约为10万册,因此主数据文件初始大小设定为110MB(包括读者表一条记录大约512字节、图书表一条记录约1024字节及其他表的大小),自动增长设置为20MB;...
本文将深入探讨一款使用C#编程语言结合SQL Server 2000数据库开发的进销存管理系统,包括系统设计思路、关键技术及其实现过程。 一、系统概述 进销存管理系统是企业日常运营的核心部分,它涵盖了采购(进)、库存...
在SQL Server 2000中,BLOB数据类型可存储的数据量最大为2GB。 接下来,在VB6.0中存取图像数据,主要利用了ActiveX Data Objects(简称ADO)技术中的Field对象提供的AppendChunk方法和GetChunk方法。这些方法专门为...
文章中描述了实现批量数据导入的具体步骤,包括使用FileUpload控件上传Excel数据文件,将数据导入SQL Server临时表中进行预览,并确保信息正确性。接着使用OleDb读取Excel数据文件,并将其导入到SQL Server中。在...
DataShow是一款基于.net Framework2.0的Microsoft SQL Server数据库建模工具,工具全部采用C#代码完成。该工具旨在利用自定义表字典的功能来设计强大的数据库控件或者页面,减少开发周期和代码量。 本工具具有如下...
1. 毕业论文JSP+SQL SERVER记者站考勤管理系统毕业设计.doc:详述了系统的背景、设计思路、功能实现和技术细节。 2. 专业计算机毕业设计.jpg:可能包含系统界面截图或设计图,帮助理解系统外观和布局。 3. 演示视频...
SQL Server是微软公司的一款关系型数据库管理系统,广泛应用于企业级数据存储和管理。它提供了强大的数据处理能力,包括事务处理、数据查询、报表生成、数据仓库等。SQL Server支持T-SQL语言,这是一种扩展了标准SQL...
在SQL Server中,确保数据的一致性和完整性是数据库管理的核心任务。当面临高并发环境时,防止重复数据的插入显得尤为重要。以下将详细介绍几种有效的方法来防止并发插入重复数据。 1. 使用Primary Key和Unique Key...
在博客系统中,SQL Server用于存储用户信息、文章内容、评论等数据,确保数据的安全性和一致性。 n层架构是软件设计中常用的一种模式,通常包括表示层(UI)、业务逻辑层(BLL)、数据访问层(DAL)以及可能的数据...
综上所述,`SqlServer 分页存储过程`涉及到如何在SQL Server中有效地执行分页查询,这通常通过存储过程来实现,并可能利用`get_no.sql`来获取序列号或特定条件下的最大值。通过理解这些概念和技术,可以构建高效且...
8. "数据库sqlserver":可能是一个文件夹,存储了与SQL Server数据库相关的文件。 9. "sale_vs2017":可能是使用Visual Studio 2017开发的“Sale”项目的源代码文件。 综合来看,这个项目是一个全面的学习实践,...