寻求外部传入大集合的 sql in 查询优化方案
-2
悬赏园豆：80
[已解决问题]
解决于 2017-04-26 14:20
福利公司搞一系列活动给会员发福利，注册会员（User）十万，每天举办N多个活动（Activity），一个活动可能包含数个礼品（Gif），一个会员能领取每一样礼品，但数量最多1个，所以要记录每个会员对于每个活动的礼品领取记录。
方案一：创建活动-用户的礼品领取记录
GifLog { User, Activity, "Gif1,Gif2,Gif3,Gif4,Gif5" }
用户与领取记录为 1对多，活动与领取记录为 1对多，一天举办200个活动，有5万用户参与，那么一天产生的记录量就有200 * 50000 = 1千万条记录，数据爆炸
方案一弃用。
方案二：在活动上创建一个以用户为键，该用户领取的礼品集合为值的字典，以Json形式存储
Activity { User1:[Gif1,Gif2,Gif3,Gif4,Gif5], User2:[Gif1,Gif2,Gif3,Gif4], User3:[Gif1,Gif3,Gif4] }
这样，数据只在活动记录上扩展，不产生新的数据记录，大大减少了数据的爆炸
方案二入选
新需求：公司准备针对活动，随机选取200个不参与活动的用户，颁发特别奖励，这个随机用户，怎么选取？
1.先从方案二的字典中查询出参与活动的5万个用户
2.把这5万个用户通过in查询传入sql中查询来排除这5万个用户，由于传入的数量量巨大，性能极低下。。。
寻求解决方案，或者好的设计思路
最佳答案
0
1千万数据如何？本来就应该有这么多数据，再说你行信息少，这点根本消耗不了什么东西。你那方案2根本就不靠谱，信息丢失的太多而且根本没办法查询。
针对你每天50k的用户，200个活动（秒杀？），先排除秒杀场景而言，这块你可以在每场前将50k的用户单独写到某个地方，包含活动的标识（活动id）（秒杀写cache，普通写db），当一旦用户参与则对这个地方的数据做标识（db做状态更新，cache可以做删除），然后进行最普通的数据落地（包含用户，时间，礼品，ip等信息），如果没有特殊需求的话，这里可以每个活动就一张表。其次可根据你后续可能出现的查询场景做其他冗余表（比如查询用户一段时间内拿了哪些活动的哪些礼品）。
选择200个未参与的用户这个需求实现也很简单，这个需要看业务是否允许，如果允许是在普通活动结束后一段时间才做这个玩意的话，直接对已有数据做清洗后得出；如果需要较高的实时性的话可以用户信息上打个标识即可。（此处依赖前面根据活动id做分表设计而言，因此无法直接通过查询每个活动的表确认用户是否参加过至少一次活动）
整个东西的难点不在于怎么存放，性能可以通过针对查询的冗余表解决（普通秒杀也如此，因为用户只会查询自己的数据，而这块有个普通的索引1千万的数据对db而言很轻松）。主要的问题在如何保证数据的一致性，强事务是一种选择，但选择强事务就代表牺牲性能，如果压力不高的话这种方案简单易信。如果性能上有考量的话，这块只能牺牲强一致性选择最终一致性，通过补偿对数据做修正。
最后给你点建议，数据库就是做数据存放的，不要考虑数据量太大（你这个量级离所谓的爆炸这个词差远了）。其次原始数据肯定要落地的，这个是最后的凭证（保不准出现争执的时候这个数据就是证据，你那样缩减信息全丢了），一般场景下不要想任何歪点子去给数据库减肥
收获园豆：40
其他回答(6)
0
1，建议先查询出5万用户，和参与活动的用户，通过程序筛选。
2，在用户表中添加一个字段（默认0没有参加活动），当用户参与了活动，就改成1.
收获园豆：2
0
取不参与活动的用户，给User加个字段就行，比如：IsJoinActive，到时候根据这个字段来筛选，然后取UserId，不就可以找随机了。
收获园豆：2
0
使用in肯定会带来查询效率低下的问题，你可以使用left join
select * from user u left join activity a on u.id = a.userid where a.gif is null
收获园豆：5
0
你难道不知道sql的XMLPATH的用法么？
还是用in
string strData = string.Empty;
foreach (int id in ids)
{
strData += "<row><id>" + id + "</id></row>";
}
sql:
in (select d.x.value('./id[1]','int') from @Ids.nodes('/*') as d(x))
SqlParameter("@Ids",SqlDbType.Xml){Value = strData}
5万条数据的话，性能提高几百倍。
收获园豆：6
0
每天发一千万礼品？？？既然发了，怎么能不记录在数据库呢？不要说一千万，一千亿也要咬牙记下来啊。业务数据是必须持久化的，这个没什么可考虑的。
随机选没有参加活动的200用户很简单啊，既然前面已经记录了参与的活动和领取的礼品，那么只要用left join礼品记录表，并且where 礼品记录表ID is null ，就能查到没有领取礼品的用户的记录，读出来随机取200个发礼品就行了。
收获园豆：10
0
现有：Activity{Index} User{Id}
额外引入：UserActivity{UserId, ActivityJson, ActivityPath}
这样可以把领取记录数和用户记录数持平（线性）。ActivityPath是个字符串，用来摘要某个Activity是否领取礼品。用位标识，和Activity的Index属性配合。例如，对于User{"001"}和Activity{1},Activity{2},Activity{3}，如果用户领取过前两个，那么就有UserActivity{"001",<json>,"011"}。这样，通过判断UserActivity就能确定用户是否已经领取过任一礼品。通过ActivityPath和指定Activity的Index就能判断用户是否领取过指定的活动的礼品。当然，这里要求Activity的Index需要合理的维护。
同时这里还保留了一点扩展性，如果你希望判断用户是否领取了在指定的时间区域内发生的任意活动的礼品，可以分别在Activity和UserActivity引入一个ActivityPeriod（活动期数）的属性。
---
话说回来，我觉得对于问题提及的业务，发生的频次并不高。让应用程序将数据装载到内存之后再进行处理也不是很大的问题。
收获园豆：15
