我如何在SQLServer上每天处理四亿三千万记录

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

逐步测试验证系统瓶颈

修改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的编译器一样，应该会自动优化吧。

具体怎样，还是要用事实来说话：

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

难道真的有这么大的影响？等等，是不是忘记清空缓存，造成了假象？

于是让同事执行下述语句以便得出更多的信息：

--优化之前
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SET STATISTICS IO ON
select Dtime,Value from dbo.his20140825 WHERE  Dtime>='' AND Dtime<='' AND MgrObjId='' AND Id=''
SET STATISTICS IO OFF

--优化之后
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SET STATISTICS IO ON
select Dtime,Value from dbo.his20140825 WHERE MgrObjId='' AND Id='' AND Dtime>='' AND Dtime<=''
SET STATISTICS IO OFF

结果如下：

（编辑：应用网_丽江站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!