phpsqlserver记载数技巧_我是若安在SQLServer中处理天天四亿三切切记录的

以下这篇文章是从一个问题的办理过程去写的，而不是一开始就给大家一个精确的结果，如果文中有不对的地方，请各位数据库大牛给予示正，以便我能够更好的处理这次业务。

这是给某数据中央做的一个项目，项目难度之大令人发指，这个项目真正的让我觉得到了，阛阓如沙场，而我只是个中的一个小兵，太多的战术，太多的高层之间的较劲，太多的底细了。
详细这个项目的情形，我有空再写干系的博文出来。

这个项目是哀求做环境监控，我们暂且把受监控的设备称为采集设备，采集设备的属性称为监控指标。
项目哀求：系统支持不少于10w个监控指标，每个监控指标的数据更新不大于20秒，存储延迟不超过120秒。
那么，我们可以通过大略的打算得出较空想的状态——要存储的数据为：每分钟30w，每个小时1800w，也便是每天4亿3千两百万。
而实际，数据量会比这个大5%旁边。
（实际上大部分是信息垃圾，可以通过数据压缩进行处理的，但是别人便是要搞你，能咋办）

上面是项目哀求的指标，我想很多有不少大数据处理履历的同学都会呲之以鼻，就这么点？嗯，我也看了很多大数据处理的东西，但是之前没处理过，看别人是条理分明，什么分布式，什么读写分离，看起来确实很随意马虎办理。
但是，问题没这么大略，上面我说了，这是一个非常恶劣的项目，是一个行业恶性竞争范例的项目。

首先碰着的第一个拦路虎便是，我们创造现有的程序下，SQLServer根本处理不了这么多的数据量，详细情形是若何的呢?

我们的存储构造

一样平常为了存储大量的历史数据，我们都会进行一个物理的分表，否则每天上百万条的记录，一年下来便是几亿条。
因此，原来我们的表构造是这样的：

No作为唯一的标识、采集设备Id(Guid)、监控指标Id(varchar(50))、记录韶光、记录值。
并以采集设备Id和监控指标Id作为索引，以便快速查找。

批量写入

写入当时是用BulKCopy，没错，便是它，号称写入百万条记录都是秒级的。

存在什么问题？

上面的架构，在每天4千万的数据都是OK的。
但是，调度为上述背景下的配置时，集中监控程序就内存溢出了，剖析得知，吸收的太多数据，放在了内存中，但是没有来得及写入到数据库中，终极导致了天生的数据大于消费的数据，导致内存溢出，程序无法事情。

瓶颈到底在哪里？

是由于RAID磁盘的问题？是数据构造的问题？是硬件的问题？是SQLServer版本的问题？是没有分区表的问题？还是程序的问题？

当时韶光只有一个星期，一个星期搞不好，项目监管就要我们滚蛋了，于是，有了连续事情48小时的壮举，有了到处打电话求人的抓鸡……

但是，这个时候须要的是镇静，再镇静……SQLServer版本？硬件？目前都不大可能换的。
RAID磁盘阵列，该当不是。
那么到底是什么，真TM的镇静不下来。

大家可能体会不到现场那种紧张的气氛，实在过了这么久，我自己也都很难再回到那种情境。
但是可以这么说，或许我们现在有了各种方法，或者处于局外人我们有更多思考，但是当一个项目压迫你快到放弃的时候，你那时的想法、考虑在现场环境成分的制约下，都可能涌现重大的偏差。
有可能让你快速的思考，也有可能思维结束。
有些同事在这种高压的环境下，乃至涌现了更多的低级缺点，思维已经完备乱了，效率更低了……36小时没有合眼，或者只在工地上（下雨天到处都是泥巴，干了的话到时都是泥灰）眯两三个小时，然后连续干，连续这么一个星期！
或者还要连续！

很多人给了很多想法，但是彷佛有用，又彷佛没用。
等等，为什么是“彷佛有用，又彷佛没用”？我模糊约约中，彷佛捉住了一丝方向，到底是什么？对了，验证，我们现在是跑在现场环境下，之前没有问题，不代表现在的压力下没有问题，要在一个大型系统等分析这么个小功能，影响太大了，我们该当分解它。
是的，是“单元测试”，便是单个方法的测试，我们须要验证每个函数，每个独立的步骤到底耗时在哪里？

逐步测试验证系统瓶颈

修正BulkCopy的参数首先，我想到的是，修噶BulkCopy的各项参数，BulkCopyTimeout、BatchSize，不断的测试调度，结果总是在某个范围颠簸，实际并没有影响。
或许会影响一些CPU计数，但是远远没有达到我的期望，写入的速率还是在5秒1w~2w颠簸，远远达不到哀求20秒内要写20w的记录。

按采集设备存储是的，上述构造按每个指标每个值为一条记录，是不是太多的摧残浪费蹂躏？那么按采集设备+采集韶光作为一条记录是否可行?问题是，怎么办理不同采集设备属性不一样的问题？这时，一个同事发挥才能了，监控指标+监控值可以按XML格式存储。
哇，还能这样？查询呢，可以用for XML这种形式。

于是有了这种构造：No、MgrObjId、Dtime、XMLData

结果验证，比上面的轻微好点，但是不是太明显。

数据表分区???那个时候还没有学会这个技能，看了下网上的文章，彷佛挺繁芜的，韶光不多了，不敢考试测验。

停滞其他程序我知道这个肯定是弗成的，由于软件、硬件的架构暂时没法修正。
但是我希望验证是不是这些成分影响的。
结果创造，提示确实明显，但是还是没有达到哀求。

难道是SQLServer的瓶颈?没辙了，难道这便是SQLServer的瓶颈？上网查了下干系的资料，可能是IO的瓶颈，尼玛，还能怎么办，要升级做事器，要改换数据库了吗，但是，项目方给吗？

等等，彷佛还有个东西，索引，对索引！
索引的存在会影响插入、更新

去掉索引

是的，去掉索引之后查询肯定慢，但是我必须先验证去掉索引是否会加快写入。
如果果断把MgrObjId和Id两个字段的索引去掉。

运行，奇迹涌现了，每次写入10w条记录，在7~9秒内完备可以写入，这样就达到了系统的哀求。

查询怎么办理?

一个表一天要4亿多的记录，这是不可能查询的，在没有索引的情形下。
怎么办！
？我又想到了我们的老办法，物理分表。
是的，原来我们按天分表，那么我们现在按小时分表。
那么24个表，每个表只需存储1800w条记录旁边。

然后查询，一个属性在一个小时或者几个小时的历史记录。
结果是：慢！
慢！
！
慢！
！
！
去掉索引的情形下查询1000多万的记录根本是不可想象的。
还能怎么办？

连续分表，我想到了，我们还可以按底层的采集器连续分表，由于采集设备在不同的采集器中是不同的，那么我们查询历史曲线时，只有查单个指标的历史曲线，那么这样就可以分散在不同的表中了。

说干就干，结果，通过按10个采集嵌入式并按24小时分表，每天天生240张表(历史表名类似这样：His_001_2014112615)，终于把一天写入4亿多条记录并支持大略的查询这个问题给办理掉了！
！
！

查询优化

在上述问题办理之后，这个项目的难点已经办理了一半，项目监管也不好意思过来找茬，不知道是出于什么样的战术安排吧。

过了很长一段韶光，到现在快年底了，问题又来了，便是要拖去世你让你在年底不能验收其他项目。

这次哀求是这样的：由于上述是仿照10w个监控指标，而现在实际上线了，却只有5w个旁边的设备。
那么这个明显是不能达到标书哀求的，不能验收。
那么怎么办呢？这些聪明的人就想，既然监控指标减半，那么我们把韶光也减半，不就达到了吗：便是说按现在5w的设备，那你要10s之内入库存储。
我勒个去啊，按你这个逻辑，我们如果只有500个监控指标，岂不是要在0.1秒内入库？你不考虑下那些受监控设备的感想吗？

但是别人要玩你，你能怎么办？接招呗。
结果把韶光降到10秒之后，问题来了，大家仔细剖析上面逻辑可以知道，分表是按采集器分的，现在采集器减少，但是数量增加了，发生什么事情呢，写入可以支持，但是，每张表的记录靠近了400w，有些采集设备监控指标多的，要靠近600w，怎么破？

于是技能干系职员开会谈论干系的举措。

在不加索引的情形下怎么优化查询？

有同事提出了，where子句的顺序，会影响查询的结果，由于按你刷选之后的结果再处理，可以先刷选出一部分数据，然后连续进行下一个条件的过滤。
听起来彷佛很有道理，但是SQLServer查询剖析器不会自动优化吗？体谅我是个小白，我也是觉得而已，觉得该当跟VS的编译器一样，该当会自动优化吧。

详细若何，还是要用事实来说话：

结果同事修正了客户端之后，测试反馈，有较大的改进。
我查看了代码：

难道真的有这么大的影响？等等，是不是忘却清空缓存，造成了假象？于是让同事实行下述语句以便得出更多的信息：

结果如下：

优化之前反而更好了？

仔细查看IO数据，创造，预读是一样的，便是说我们要查询的数据记录都是同等的，物理读、表扫描也是一贯的。
而逻辑读取稍有差异，该当是缓存命中数导致的。
也便是说，在不建立索引的情形下，where子句的条件顺序，对查询结果优化浸染不明显。

那么，就只能通过索引的办法了。

建立索引的考试测验

建立索引不是大略的事情，是须要理解一些基本的知识的，在这个过程中，我走了不少弯路，终极才把索引建立起来。

下面的实验基于以下记录总数做的验证：

按单个字段建立索引这个想法，紧张是受我建立数据构造影响的，我内存中的数据构造为：Dictionary<MgrObjId,Dictionary<Id,Property>>。
我以为先建立MgrObjId的索引，再建立Id的索引，SQLServer查询时，就会更快。

先按MgrObjId建立索引，索引大小为550M，耗时5分25秒。
结果，如上图的预估计划一样，根本没有起浸染，反而更慢了。

按多个条件建立索引OK，既然上面的弗成，那么我们按多个条件建立索引又如何？CREATE NONCLUSTERED INDEX Idx_His20141008 ON dbo.his20141008(MgrObjId,Id,Dtime)

结果，查询速率确实提高了一倍：

等等，难道这便是索引的好处？花费7分25秒，用1.1G的空间换取来的便是这些？肯定是有什么地方不对了，于是开始翻查资料，查看一些干系书本，终极，有了加大的进展。

首先，我们须要明白几个索引的要点：

跟上述几点原则，我们建立以下的索引：CREATE NONCLUSTERED INDEX Idx_His20141008 ON dbo.his20141008(MgrObjId,Id) INCLUDE(Value,Dtime)

耗费韶光为：6分多钟，索引大小为903M。

我们看看预估计划：

可以看到，这里完备利用了索引，没有额外的花费。
而实际实行的结果，1秒都不到，竟然不用一秒就在1100w的记录中把结果筛选了出来！
！
帅呆了！
！

怎么运用索引?

既然写入完成了、读取完成了，怎么结合呢？我们可以把一个小时之前的数据建立索引，当前一个小时的数据就不建立索引。
也便是，不要再创建表的时候建立索引！
！

还能怎么优化

可以考试测验读写分离，写两个库，一个是实时库，一个是只读库。
一个小时内的数据查询实时库，一个小时之前的数据查询只读库；只读库定时存储，然后建立索引；超过一个星期的数据，进行剖析处理再存储。
这样，无论查询什么韶光段的数据，都能够精确处理了——一个小时之内的查询实时库，一个小时到一个星期内的查询只读库，一个星期之前的查询报表库。

如果不须要物理分表，则在只读库中，定时重修索引即可。

如何在SQLServer中处理亿万级别的数据(历史数据)，可以按以下方面进行：

如侵权烦请联系删除。