大容量数据读写分离方案讨论
数据库是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的功能，数据库的快照能满足我的需求吗？
------解决方案--------------------
快照不适合实时查询哦。
------解决方案--------------------
1)对于子表是否可以根据ID或者日期字段进行分区
2)分析下来应该是因为频繁更新导致索引有碎片或者分布不均匀 是否可以适当调整下填充因子不要100%填充
可以设置为70%~80%
3)业务相对不繁忙的时候 是否可以整理下索引碎片
4)分析查询的执行计划 是否还有优化的空间
5）HA应该可以分担部分压力
------解决方案--------------------
有没想过IO效率低呢？美国的SD高速硬盘很不错的。
------解决方案--------------------

引用:

Quote: 引用:

Quote: 引用:

Quote: 引用:

一亿多是多久的数据
建议做分表处理。

一亿数据，多个服务器在做处理，平均一个礼拜会全部更新一趟。

为什么要一周都有更新一遍呢，你的更新好频繁啊。

就是因为更新很频繁哈。。估计是硬盘瓶颈。准备换几个SSD硬盘做个raid10试试。

更新频繁鄙视问题，但是你的更新是整表的，这业务没遇到过
数据增量如何？如果从性能来看，可以考虑分表

当然升级硬件更省事

还有你的索引维护频率太高了。会适得其反。

------解决方案--------------------
1、检查下聚集索引字段是否太长，因为聚集索引查找好像有点慢
2、既然索引重建这么频繁，填充因子40%没有必要，80%左右合适，填充因子太小可能引起索引层次增加，得不偿失。
3、分区可以降低索引层次，提高更新和查询速度
4、多服务器更新数据库，需要注意是否存在更新事务太大，或者资源锁定时间太长的程序问题
5、如果读写分离，需要采用事务发布，数据量小很多
6、大表如果分区效果不够，可以考虑分表，如果数据与时间相关，可考虑按时间分表，否则可考虑按关键的id段分表，关键是需要分表能提高inner join速度，分表后查询逻辑需要重新设计。
7、查询有时快有时很慢，需要重点关注4和1

------解决方案--------------------
第一，你的查询连接字段在大表中是不是索引
第二，你的索引分布是否均匀，比如，你的表一个有150W数据，一个1亿多数据，平均下来是1:100的样子，实际上根本达不到这个标准，这就导致索引的分布不均匀，你尝试建立索引包含列试试。
事实上，你这样还用不到复制，
其实，从你的查询计划中，可以看出，排序问题开销大，还有，你既然是查询满足条件的数据应该不是聚集索引查找啊，范围查询应该会选择聚集索引扫描。你看看统计信息是否过期？
------解决方案--------------------
有个明显的地方，大家都没说到点子上哦 
第一，你的查询连接字段在大表中是不是索引
第二，你的索引分布是否均匀，比如，你的表一个有150W数据，一个1亿多数据，平均下来是1:100的样子，实际上根本达不到这个标准，这就导致索引的分布不均匀，你尝试建立索引包含列试试。
事实上，你这样还用不到复制，
其实，从你的查询计划中，可以看出，排序问题开销大，还有，你既然是查询满足条件的数据应该不是聚集索引查找啊，范围查询应该会选择聚集索引扫描。你看看统计信息是否过期
------解决方案--------------------
其实像楼主这样的应用，最合适的是NOSQL，例如MongoDB

传统关系型数据库很难满足又要大数据量，又要高性能读写。
------解决方案--------------------
针对楼主，及各位版主提出的问题我说一下个人意见：
1.sqlserver所有读写分离基本上都无法实现时时数据同步。
2.发布订阅方式读写分离方式[快照、事务]也要具体看数据的操作频率以及更新上线率来决定。
3.日志传送读写分离方式虽然传送速度一般要优越与发布订阅方式，但是每次复制还原过来的日志
时候有一种独占数据模式会导致暂时无法问的问题。
4.读写分离也要考虑服务器的物理配置，比如磁盘raid、cpu、内存、网络等基本配置。

如果你对你现在你对以上都有了解，并对所掌握数据已经有明确认识，ok 接下来讨论一下具体
执行方案：
发布订阅读写分离方式
1.物理服务器配置。根据主从[发布与订阅]服务器分工分别做好磁盘raid。
2.数据分区。如果数据量过大可以考虑将大数据拆分也就是所谓的数据表分区。
3.数据订阅。
   a.快照订阅。一般针对考虑数据时效性不强的时候可采用也即是对数据更新周期