SQL Server 大数据量数据存储设计思路分享
论坛上总看到有人说某某数据库几百万的数据量怎么提高查询速度等等，最近正好做了一个关于这方面的表结构优化，分享给大家，希望对大家有帮助。本人也不是什么大牛，只希望互相交流学习。仅为分享，不喜勿喷，谢谢。言归正传，下面说一下具体的实现及效果。
应用场景：
一张日志表，记录每天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的专栏
页:
[1]