请教一个sql server优化的问题

Question

最近遇到一个需求，要求在会员卡表增加几个冗余字段，记录该会员卡的最后消费时间，总消费金额（总消费金额分为现金，会员卡卡金，其中现金包括支付宝，微信和其他用户自定义支付方式），总消费次数，总充值金额，最后充值时间，总充值次数这么几个冗余字段。目前分离出来的测试数据库里面正常会员卡的总数是700万左右，消费数据上亿了（暂且就考虑为一亿）； 涉及到的数据表： MemberShipCards：会员卡表（700多万条正常数据） MemberConsums：消费记录表 MemberConsumePayments:消费记录支付方式 MemberPayment:支付方式列表 MemberRecharges:充值数据 各个表之间的关联关系是:MemberConsumes关联了MemberShipCards(一张会员卡有多条消费记录) MemberConsumePayments：这里面记录了该会员卡结算的使用的结算方式，关联在每条消费记录上面的（MemberConsums），但是每一种结算方式又是对应在MemberPayment支付方式列表里面的，也就是说要查询每张卡的现金支付总金额就要MemberShipCards，MemberConsums，MemberConsumePayments,MemberPayment联合查询，比如： select sum(money) from MemberConsumePayments p inner join MemberConsums c  on p.ConsumeId=c.Id  inner join MemberPayment m on p.PaymentId=m.Id where c.status=1 and m.paymenttype in(1,3,4,6,8) and c.memberCardid=xxxx 以上的sql语句就是查询该会员卡总现金消费金额（支付金额是存储在MemberConsumePayments 这里面的，而区别现金这个支付方式的在MemberPayment这里面，因为每张卡有很多的消费记录，要筛选出正常状态的，所以才有c.status=1 ） 然后查询出这个金额之后再根据会员卡id去修改会员表里面的冗余字段值 以上就是其中的一个查询，目前我的实现逻辑是先把正常的会员卡id查询出来放在临时表里面，然后循环这个临时表 select id=identity(int,1,1),Id as cardId into #cardsIds from MemberShipCards where [Status] = 1 CREATE INDEX IX_Id ON #cardsIds(Id) declare @count bigint declare @index bigint select @count = COUNT(id) from #cardsIds while(@count>=@index) begin 　　--具体逻辑，查询和修改冗余字段 　　set @index+=1 end 注：各个查询总和的字段和where条件都已建立索引，由于这里我用的是存储过程循环600多万条数据，整个过程执行完毕粗略估算大约耗时好几个小时，这样对于其他的查询操作肯定受影响，由于本人sql功底有限，在此跪求各位大神为小弟提供一下解决方案，大恩不言谢

Answer 1

这个需求仅仅是基于数据库中现有的记录来计算这几个冗余字段并更新到会员卡表？不用管以后发生新的消费时同步更新这些冗余字段？

 

如果是这样，那简单啊。性能差也不是问题，找数据库压力小的时候，一批批慢慢跑嘛，总能跑完的。

 

其实更大的问题是，这些中途增加的冗余字段的数据初始化搞定以后，怎样才能伴随这新的消费发生而同步更新的问题。这才是要命的大问题。

Answer 2

会员卡700万的这种软件，确实是大恩了... 自己不行就找个行的呗，反正不差钱。

1、上策，找个行的。

2、中策，清理过期数据，升级硬件

3、下策，自己慢慢玩，慢慢优化。

Answer 3

这些都不能段时间内解决问题，找个专业的数据库大神也不是那么容易的

Answer 4

@Mr.落叶: 技术债务都能短时间解决的话，那就不叫技术债务了。

谁不希望1小时解决问题啊，问题是问题就是需要几个月或者几年才能解决的咋办？

先升级硬件，拖一拖，然后就拖成技术债务了，几乎99%的人不会想着如何花时间做优化，而不是增加功能的。

如果给你两个选择

一个月解决问题费用10万，

一天解决问题费用100万。

你会选择哪个？

Answer 5

洗数据肯定会对db造成一定压力的，但不建议你在一个存储过程中搞完，你这样搞可能会把正常业务给完全拖跪掉

正常的搞法是你前面通过分页方式抽取卡id信息，通过程序一个个的对卡的数据进行查询和更新，这种虽然看起来耗时更长，但对db压力是最小的了

Answer 6

你说得对，我现在是备份的一个测试数据库，就是想看有没有办法把这些数据控制在两个小时内搞完，正式的可以等凌晨的时候批量更新了，那时候基本上没人使用，影响不是特别大

Answer 7

@Mr.落叶: 我们这边有些类似的数据变化就是采用我说的这种方式，mysql数据库，洗完几千万数据也就2个多小时（而且还是在线）。

这块你可以估一下，按照我的做法只要每秒能够处理掉1k行数据2小时足够了，这块你只要确保填充几个字段的查询语句速度快，问题不大的。同时你可以在部署一个这样的程序后看下db压力，如果不大的话可以再部署一个加快处理速度（需要区分不同程序处理的数据，比如简单的卡号是否是偶数）

Answer 8

给一个歪招。找一个不使用的冗余字段是能存json/xml的，把那几个冗余字段当一个对象进行反序列化存在刚刚的字段里。这个改动应该比较小。

Answer 9

兄弟，你有点跑题了

Answer 10

update MemberShipCards A set A.总消费金额=sum(money) from MemberConsumePayments p inner join MemberConsums c

 on p.ConsumeId=c.Id 

inner join MemberPayment m on p.PaymentId=m.Id where c.status=1 and m.paymenttype in(1,3,4,6,8) and c.memberCardid=A.xxxx

语法可能有误，大体这样，供参考