如何在SQLServer中处理每天4.3亿条记录
这是一个数据中心的项目。这个项目的难度令人吃惊。这个项目真的让我觉得商场如战场，我只是其中之一。战术太多，高层之间的较量太多，内幕太多。关于这个项目的具体情况，有时间我会再写一篇博文。
这个项目需要环境监测。我们暂且将被监控的设备称为采集设备，采集设备的属性称为监控指标。项目要求:系统支持不低于10w的监控指标，每个监控指标的数据更新不超过20秒，存储延迟不超过120秒。那么，通过简单的计算，我们就可以得到需要存储的理想数据:每分钟30w，每小时1800w，也就是每天4.32亿。现实中数据量会比这个大5%左右。(其实大部分都是信息垃圾，可以通过数据压缩来处理，但别人就是想干你。他们能做什么？)
以上是项目要求的指标。我想很多对大数据处理很有经验的同学都会对此嗤之以鼻。就这些吗？嗯，我也看过很多大数据处理的东西，但是我之前没有处理过。看别人的思路，分配，读写分离，好像真的很容易解决。然而，问题并没有这么简单。就像我上面说的，这是一个非常不好的项目，是一个典型的行业恶性竞争的项目。
没有更多的服务器，但是这个服务器除了数据库和集中采集器(即数据分析、报警和存储的程序)之外，还支持30w北向接口(SNMP)，在程序优化之前，CPU常年占用80%以上。因为项目需要使用双机热备，为了省事，减少不必要的麻烦，我们把相关服务放在一起，以便充分利用HA(从外部购买的HA系统)的特点。
系统数据的准确性极其变态，要求从底层采集系统到顶层监控系统的每一个数据都不能差于我们的系统架构。可以看到，数据库压力非常大，尤其是在LevelA节点:
硬件配置如下:CPU: Intel？至强？处理器E5-2609 (4核，2.40GHz，10MB，6.4 GT/s)内存:4GB (2x2GB) DDR 3 rdimm内存，1333 MHz，ECC硬盘:500GB 7200 rpm 3.5'' SATA 3硬盘，Raid5。
数据库版本采用SQLServer2012标准版，惠普提供的正版软件缺少很多企业版的NB功能。
写入瓶颈
我们遇到的第一个障碍是，我们发现在现有的程序下，SQLServer根本无法处理这么多数据。具体是什么情况？
我们的存储结构
一般为了存储大量的历史数据，我们会做一个物理表，不然每天几百万条记录一年就是几亿条。因此，我们最初的表结构是这样的:
创建表[dbo]。[his 20140822]([No][bigint]IDENTITY(1，1)非空，[Dtime] [datetime]非空，[MgrObjId] [varchar](36)非空，[Id] [varchar](50)非空，[Value] [varchar](50)非空，约束[PK_His20140822]主键用(PAD_INDEX = OFF)聚集([No] ASC)
No作为唯一标识，采集设备Id(Guid)，监控索引Id(varchar(50))，记录时间和记录值。采集设备Id和监控索引Id用作快速搜索的索引。
批量写入
当时用的是BulKCopy，对，就是它。据说写几百万条记录都是秒的。
public static int BatchInert(string connectionString，string desTable，DataTable dt，int batch size = 500){ using(var SBC = new SqlBulkCopy(connectionString，SqlBulkCopyOptions。UseInternalTransaction){ BulkCopyTimeout = 300，NotifyAfter = dt。Rows.Count，BatchSize = batchSize，destination tablename = desTable }){ foreach(dt中的DataColumn列。列)sbc。ColumnMappings.Add(column。ColumnName，列。column name)；sbc。write to server(dt)；}返回dt。行数。计数；}
有什么问题？
以上架构，每天4000万数据，还可以。但是，当配置调整到上述后台时，集中监控程序的内存溢出。分析认为，接收到的数据太多放入内存，却没有及时写入数据库，最终导致生成的数据大于消耗的数据，导致内存溢出，程序无法运行。
瓶颈在哪里？
是因为RAID磁盘吗？是数据结构吗？是硬件的问题吗？是SQLServer版本吗？没有分区表吗？还是程序问题？
当时时间只有一周。也许有一周，工程监理会要求我们把它处理掉。于是，有了连续工作48小时的壮举，我们只好到处叫人来抓鸡。
但是，这个时候需要的是冷静，然后再冷静SQLServer版本？硬件？目前不太可能改变。RAID磁盘阵列，我不这么认为。那是什么呢？真他妈的冷。
你可能没有意识到现场的紧张气氛。其实过了这么久，我自己也很难再回到那种境地了。但是可以这么说，也许我们现在有各种各样的方法，或者我们是外人的时候有更多的想法，但是当一个项目逼迫你很快放弃的时候，你当时的想法和考虑可能在现场环境因素的制约下会有很大的偏差。它可能让你快速思考，也可能让你停止思考。在这种高压环境下，有些同事甚至犯了更多的低级错误，思维完全混乱，效率更低。他们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左右的记录。
然后查询一个属性在一个小时或几个小时内的历史。结果就是:慢！慢点！！慢点！！！在没有索引的情况下查询超过10万条记录简直是不可想象的。我还能做什么？
继续分表，我想，可以继续按照底层采集器分表，因为不同采集器采集设备不一样，所以我们查询历史曲线的时候，只需要查看单个指标的历史曲线，就可以分散到不同的表中。
结果，通过收集10个嵌入表，分成24小时，每天生成240个表(历史表名类似于这样:HIS _ 001 _ 2014112615)，当天最终写成4亿。！！
查询优化
以上问题解决后，这个项目的难点就解决了一半，项目监理都不好意思找茬了。不知道是什么样的战术安排。
时间久了，现在快年底了，问题又来了，就是把你拖死，让你年底不能接其他项目。
这次的要求是这样的:因为上面的监控指标都是在10w模拟的，但是现在实际上线了，但是只有5w左右的设备。那么这个显然不能满足标书的要求，不能接受。那么我们该怎么办呢？这些聪明人心想，既然监控指标减半，那么我们就减半时间，不就实现了吗？也就是说，按照现在的5w设备，你要在10s之内入库。我靠，按照你的逻辑，如果我们只有500个监控指标，不是要在0.1秒内入库吗？你不考虑一下那些被监控设备的感受吗？
但是别人想和你玩你能怎么办？拿着。结果时间缩减到10秒后，问题就来了。仔细分析上面的逻辑，就可以知道表是按收藏者分的。现在收藏的人少了，但是数量增加了。发生什么可以用文字支持，但是每个表的记录接近400w，采集设备的一些监控指标接近600w。怎么破？
于是技术人员开会讨论相关措施。
如何在没有索引的情况下优化查询？
有同事提出，where子句的顺序会影响查询的结果，因为按照你选择的结果处理后，可以先选择一些数据，然后继续过滤下一个条件。听起来很有道理，但是SQLServer查询分析器不是会自动优化吗？原谅我有点白，但我就是感觉到了。应该和VS的编译器一样，应该是自动优化的。
具体如何，还是要用事实来说话:
结果同事修改客户端后，测试反馈有了很大改善。我检查了代码:
真的有这么大的影响吗？等等，你是不是忘了清空缓存，造成了幻觉？因此，让同事们执行以下语句来获取更多信息:
-优化前，dbcc freeproccache dbcc dropcleanbuffers在select dtime上设置statistics io，值来自dbo.his20140825，其中dtime > = ' '和Dtime & lt= ' ' AND mgr objid = ' ' AND Id = ' ' set statistics io off-optimized DBCC freeproccache DBCC drop buffers set statistics io on select Dtime，Value from dbo.his20140825其中MgrObjId = ' ' AND Id = ' ' AND Dtime & gt；= ' '和Dtime & lt= ' '关闭统计IO
结果如下:
优化前更好？
仔细看IO数据，发现预读是一样的，也就是说我们要查询的数据记录是一致的，物理读和表扫描都是一样的。逻辑读取略有不同，这应该是由缓存命中次数造成的。也就是说，在没有建立索引的情况下，where子句的条件顺序对查询结果的优化没有明显的影响。
那么，就只能用指数法了。
创建索引的尝试
建立指数不是一件简单的事情，需要一些基础知识。在这个过程中，我走了很多弯路，最终建立了指数。
以下实验基于对以下记录总数的验证:
单个字段构建索引的思路主要是受我构建的数据结构的影响。我记忆中的数据结构是字典
首先，按MgrObjId创建一个索引，索引大小为550M，耗时5分25秒。结果就像上面显示的预测方案一样，根本不起作用，反而更慢了。
按多个条件索引是可以的。既然上面不行，那我们按多个条件索引呢？在dbo . his 20141008(MgrObjId，Id，Dtime)上创建非聚集索引Idx_His201465438
因此，查询速度确实提高了一倍:
等等，这就是索引的妙处吗？用了7分25秒，1.1G的空间换来的就是这个？肯定有问题，于是我开始查资料，看了一些相关的书。最后，我取得了很大的进步。
正确索引
首先，我们需要了解几个指标的关键点:
索引后，按重复最少的索引字段排序会达到最好的效果。以我们的桌子为例。如果建立了No的聚集索引，最好将No放在where子句的第一个位置，然后是Id，接着是MgrObjId，最后是time。如果表是一个小时，最好不要用。
where子句的顺序决定了查询分析器是否使用索引进行查询。例如，如果建立了MgrObjId和Id的索引，则where mgrobjid = ' '和id = ' '和dtime = ' '将使用索引搜索，而where dtime = ' '和mgrobjid = ' '不一定使用索引搜索。
将非索引列的结果列放在包含列中。因为我们的条件是MgrObjId和Id以及Dtime，所以返回的结果只需要包含Dtime和值，所以把Dtime和值放在包含列中，会使返回的索引结果有这个值，不用查物理表就可以达到最优的速度。
根据上述原则，我们建立以下索引:创建非聚集索引idx _ his 20141008 ondbo . his 20141008(mgrobjid，id) include (value，dtime)。
耗时6分多钟，索引大小为903M。
让我们看看预测计划:
可以看到，这里完全使用了索引，没有额外的消耗。实际执行结果不到1秒，结果在1100w的记录中没有一秒被筛选出来！！厉害！！
如何应用指数？
现在写完了，读完了，怎么结合呢？我们可以索引一小时前的数据，但不能索引当前一小时的数据。也就是说，创建表的时候不要创建索引！！
如何优化？
你可以试着把读和写分开，写两个库，一个是实时库，一个是只读库。实时数据库用于一小时内的数据查询，只读数据库用于一小时前的数据查询；只读库定期存储，然后建立索引；一个多星期的数据，存储前的分析和处理。这样无论查询什么时间段的数据，一小时内的实时数据库，一小时到一周内的只读数据库，一周前的报表数据库都能正确处理。
如果不需要物理表，可以在只读库中定期重建索引。
摘要
如何处理SQLServer中数十亿的数据(历史数据)可以按如下方式进行:
从表中删除所有索引。
使用SqlBulkCopy插入
划分表或分区，以减少每个表的总数据量。
在表完全写完之前，不要建立索引。
正确指定索引字段
将您需要的字段放入包含索引中(所有内容都包含在返回的索引中)
查询时只返回必填字段。
如何在SQLServer中处理每天4.3亿条记录
标签: