大容量数据读写分离方案讨论

[caoshangfei]

数据库是sql server 2008 r2,数据库有两张表，一个主表，大概有150万数据，对应的字表有1亿多数据。
因为两个表的更新操作都很频繁，建了索引，查询需要用到两个表的inner join,每次查询是根据某个条件某个字段排序，只需要查询前1000条数据即可。语句都用的with(nolock)，但是查询速度速度还是比较慢，有的时候瞬间出来结果，有的时候几十秒甚至1分钟以上。服务器CPU使用率都很低，内存也够大，96G的内存。
以前有在论坛发过贴，http://bbs.csdn.net/topics/390381835
分析下来应该是因为频繁更新导致索引有碎片或者分布不均匀。
大家有好的建议吗？我的数据允许脏读，脏读允许几个小时甚至更长。
我现在想到的是，通过数据库发布订阅功能，专门建立一个只读数据库来专门给查询使用。
但是我几个有个疑问：
1.通过发布订阅同步到只读数据库的时候，不同样有数据写入吗？会不会也会对查询性能有影响。
2.数据量比较大，我是应该用快照发布的方式呢？还是事务发布的方式呢？
3.我昨天有测试过用快照发布的方式，发现发布初始化的时候，会锁表，我的数据库更新操作就会很卡，有办法在发布的时候不锁表吗？
4.我是不是需要先通过还原数据库的方式在订阅服务器上把数据库先还原好，再来做发布订阅，这样会节省时间？

希望大家给我出出主意，谢谢。
另外，我看sql server 2008有Snapshot的功能，数据库的快照能满足我的需求吗？

[u010141386]

不错，谢谢分享！

[hz_gis]

受教了！学习。

[弘恩]

很好，学习了。

[xjlqlqlq]

升级到 SQL 2012, 有高可用方案，主库用于事务处理, 备份库[据称实时性可达妙级]提供查询。 参看下sql 2012的介绍。

[caoshangfei]

字表数据量太大，因为排序需要根据主表的某个字段排序。发现升级内存，升级硬盘为raid10的固态硬盘，数据通过复制到另外一台服务器查询，速度提升都不大。 去掉主表关联，只用子表的排序后，速度非常快。看来数据量太大，主子表关联再用主表的字段排序，效率太低。还是采取上面一位朋友说的，在字表冗余下这个排序的字段。

[caoshangfei]

同时前台web端采取缓存机制，每隔几个小时才从数据库中查询一次数据。这样的方案应该比较好了。 给有相同需求的朋友参考参考。

[test2002]

才150W的记录，应该是小case呀，是不是数据库并发量大？ 个人认为从连接上考虑更好，客户端可考虑长连接，共享连接，省去不必要开销。

[test2002]

其实排队写库，比多线程并发写库快多了。

[test2002]

只是100多万的记录也叫海量？没有亿级记录都不好意思叫大容量

[xzliwei]

楼主的问题，和我的问题一样。 我的客户是医院客户，每天院办统计、财务统计、门诊统计都会有大量的统计产生，每天早晨从8点开始到10点，数据库锁表情况比较严重，原本的方式是把要统计的数据汇总到生产库的一张专用统计的表中，然后再统计。但是，如果汇总比较慢，在早晨8点多开始就会对门诊、住院的生产数据产生影响。 我在没有对服务器做优化之前做过一次快照复制，悲剧的很，服务器的磁盘I/O几乎满载，本地登录服务器卡死。查阅了国外的SQL DBA的书籍后，发现一些问题，主要如下，1、生产服务器的数据磁盘几乎用的都是RAID 5，这种方式读取时候效率很低；2、在做复制时，没有给复制的数据单独分配磁盘；3、虚拟内存分配不足；4、32位操作系统做复制真心不行。 针对以上问题，只能用事务复制，快照复制是不行的，这个我查过MS DBA的书籍，写的很清楚，小型数据库可以做快照复制，因为数据更新比较低，一次性就结束了。但是面对每天数据库变更比较大的时候，快照就不行了，你的服务器顶不住，我是这样理解的。 医院的客户没法升级系统，也没法更新存储的RAID方式，NND，很郁闷，只能继续在32位系统里做这个事务复制。分配了3个磁盘，给发布服务器，一个做复制的数据存放，一个做备份的数据存放，一个做虚拟内存。在订阅服务器上也是如此配置的。 业务不是很多的情况下，做复制效果还算是不错的，至少应用软件可以登录，办理业务，不至于被门诊、急诊、住院的MM骂！服务器也是不卡的。 其实，做好硬件的优化，效果可能会更明显，RAID 0+1的配置性能最高，如果可以的话，一定要这么配置。 昨晚做好的，效果还是不错的，实际上订阅服务器生成的数据是可以做写的操作的，这个MS DBA的书上也是这样叙述的，但是我没有做测试，理论上是可以的。 复制的过程中，发现一些问题，primary key约束某个表，不能插入重复键，我这里是一些登陆日志或者修改设置的日志的表，我把这些表清除在复制内容之外，就暂时不会有什么问题了。 我的复制情况就是如此，希望能给大家一些借鉴。

[Killua幽阳]

test2002
test2002 等级：
结帖率：100% #95 得分：0 回复于： 2013-04-03 16:01:57
其实排队写库，比多线程并发写库快多了。

[haitao]

引用 16 楼 caoshangfei 的回复:

引用 12 楼 annatrov 的回复:有没想过IO效率低呢？美国的SD高速硬盘很不错的。 有可能。SD告诉硬盘？只听说过SSD固态硬盘哈。

最近在2台机上使用了PCI-E的内置ssd阵列 但是有一台写入数据略大就重启，而且找不到这个卡了 必须重新插拔这个卡，才正常。。。。 感觉服务器用这些，还是不靠谱，太超前了一点

[caoshangfei]

引用 96 楼 test2002 的回复:

只是100多万的记录也叫海量？没有亿级记录都不好意思叫大容量

子表几亿数据

[caoshangfei]

谢谢大家的建议。 已经找到问题所在，并且将最后的优化方案写出来，供有需求的朋友们参考，文章地址：http://blog.csdn.net/caoshangfei/article/details/8761301 结贴。

[无聊找乐]

如果是读写分离架构的话有个超级变态的方案你可以试试，你内存不是很大吗？你可以用内存建个虚拟硬盘，把查询服务器的数据库装到虚拟硬盘上，也就是内存里。然后，，然后你就好好体验下这恐怖的响应速度吧。

[caoshangfei]

数据冗余到一个表.优化索引.现在查询结果都是瞬间出来了.最终优化索引后的执行计划如下,优化索引后,执行计划都不需要排序了.速度非常快.

[jvhmr]

1、表分区 2、硬盘做raid10 （但有点些贵） 3、是在不行将表做分布式处理，估计需要自己写个分布存储和查询的中间件来 期待楼主的解决方案

[AlexLee8810]

精彩。。。。。

[qxyywy]

引用 楼主 caoshangfei 的回复:

数据库是sql server 2008 r2,数据库有两张表，一个主表，大概有150万数据，对应的字表有1亿多数据。 因为两个表的更新操作都很频繁，建了索引，查询需要用到两个表的inner join,每次查询是根据某个条件某个字段排序，只需要查询前1000条数据即可。语句都用的with(nolock)，但是查询速度速度还是比较慢，有的时候瞬间出来结果，有的时候几十秒甚至……

先标记下 大数据处理也想看看他人是如何操作 对数据的处理一般有： 缓存 索引 主从库 分库（水平分） 以前看过NOSQL 不过一般不容易去实现 现在还是关系型数据库占主导

[xml大仙]

路过，谢谢分享！