现身说法：实际业务出发分析百亿数据量下的多表查询优化

今天给大家带来的讨论主题是通过实战经验来对百亿数据量下的多表数据查询进行优化，俗话说的好，一切脱离业务的架构都是耍流氓，接下来我就整理一下今天早上微信群里石头哥给大家分享的百亿数据量多表查询架构以及优化思路。由于本文内容整理自微信群，爬楼不易，整理更不易，如果有遗漏，欢迎大家在评论区留言。

作者：依乐祝
原文地址：

简单的例子

这里我们先举个简单的例子，来个开胃菜，然后再引出今天的访谈主题。

举例：比如我们的czarcms系统权限系统设计中的两张表：用户表以及角色表，这两张表有关联关系。这时候如果我要取一万个用户的数据，然后用户数据又需要关联角色表来查询对应的角色名称，这时候你会怎么做呢？

按照以往我们的经验我们会对大表进行尽可能的拆分，能分表就分表。我们在取数据的时候使用下join查询即可实现。

可是，当我们的系统变得足够大的时候，假设我们的用户表有一百万的用户了，角色表也有近10万的数据，这个时候我们如果还继续使用join进行查询的时候就会变得非常慢了！

这时候我们可以改变下思路：就是先把一万条用户数据取出来，然后取所有角色id后再去重的组合，然后用一个查询把所有的角色信息取出来，再在内存中进行相应的拼接处理。
这种思路勉强能够支撑。

可是如果数据量变得越来越大，这时候我们应该如何来进行处理呢？且看下面来自百亿数据实操的经典访谈。

实际业务场景分析

第一段介绍

这里，石头哥就以他们公司的实际情况为例来进行了相关的实例阐述：
我们的主要表，都是几亿到几十亿行，一个join不小心就可以弄死数据库,
而且每天1亿包裹在路上，产生3亿多扫描数据
数据存储最少t+1，保存完整的一个月，也就是30到60天
数据量90到180亿
这里面，最常见的就是省,市,区,网点,人员，这5个字段
很久以前，我们只有三五百万业务量的时候，大家都是join五次
后来为了省事，用了10个字段，提前把名称写进去
再后来，发现亏大了
多花了好多空间，并且join不一定是只需要名称字段
于是，进入了新时代，所有数据表都有那基本的5个字段，不许join
查询出来数据后，在内存中再关联省，市，区，网点，人员等信息
地区5万行，网点3万行，人员100万，全部提前加载到内存，加起来不到100m
我们小部门有100台服务器，绝大部分用到这些基础数据
不仅仅上百亿的扫描表，其它业务表，几乎都会带有这些字段，所以，缓存基础数据，不吃亏

互动环节

1. 多大的数据量，才不能用join？
   答：一般来说，从表100万以内，我们都建议内存缓存，10万以内必须用进程内缓存，没得商量，内存中进行关联即可。
2. 我们删数据只能一条条删。不允许批删除，这个怎么办？
   答：你们dba可以辞退了，这么简单的事情都不会。
   大数据分析的时候，每个月几个亿数据，一条条删，删到何年何月啊，当然是整个分区干掉啦

3. 可以分享一下你们的缓存方案吗？
   这个就不用了吧，我觉得很多大佬关于缓存的文章就写得非常好！
   我这里只补充一些量化的数字：内存速度是redis速度100倍，redis缓存速度是数据库至少10倍。
   10万以内数据量必须缓存在进程内，100万~1亿数据缓存在redis，10万~100万可以上下商量，超高查询量（比如每天10亿次）时放内存。
   很多文章真的写得挺好的，就是少了点经验数据支撑，读者搞不清楚什么时候该用这个，什么时候该用那个。

4. 吉吉：以下场景：假设缓存了地区，比如查询人员档案信息列表是1万行 以前是关联查询 现在缓存地区不能关联查询 只能查出一万条然后循环拼接地区显示 ，因为一万行显示本身这场景就不可能，所以增加翻页 一页显示20行 完全不会性能问题 这样做对吗 求教？
   答：是的，查询一页20行，理论上要去匹配20次地区，但是地区数据少，省市区才四五千行，省市区加上乡镇街道也不过5万行，可以全量缓存到内存。

吉吉：明白 谢谢 只是举例 这种思路真的很正确 我们总是从技术考虑全部场景却不考虑产品本身根本不能一劳永逸的搞。

1. 真是太牛逼了！感谢分享
   答：这么low的办法，大家都可以想得到，只是可能缺少一个遇到这个数据量的机会罢了，这是咱们.net的际遇，遇到问题可以见招拆招.

2. 你说的缓存到进程内，那多个进程内数据怎么保持一致？
   答：不保持一致，因为进程级缓存，可以定时更新的，我们方案是默认10秒异步更新缓存，然后也可以按照添删改随时更新。

3. 我的内存数据以哪个为准？如果我机器是负载均衡，那么几个副本内存不一样啊！
   答：以本机为准,没关系，每台服务器上都有一份缓存。缓存10万用户信息，一共也就10m左右内存，你还在意？

4. 数据怎么进行存储呢？
   答：内存字典进行存储，最常用的就是并行字典 concurrentdictionary。

5. 假设你说的人员数据，那就必然存在 某一时刻 a进程10000人，b进程10002人，可能十秒可能八秒，但你们体量这么大，这个问题不需要处理吗
   答：没错，的确存在这样的问题，我们公司有100万人员，但是全公司都知道，新加一个帐号，往往要两三天才能在100多个内部系统全部生效，甚至新签约一家网点，也要两三天以后，各个系统才会认它，等你有那个体量的时候，就可以接受更长的不一致时间，我们在淘宝开店，发布商品，有时候发布成功了，跳转到详情页却是看不到的，等几秒就好了。
   另外，
   我再说一句恶心一点的情况，大家别拍砖啊，在百亿级数据量之下，就算我算错个几百几千，那又怎么样？？？又怎么样？？
   对内，我会严格要求部门人员，务必追求准确；
   对外，我会反复提醒部门人员，我们提供的数据精度，最高99.99%，你要敢浪费大量时间在0.01%上，我就敢给你绩效c
   不用太死板嘛，把整体工作搞定，让系统稳定工作，那才是咱们的主要工作
   用户有万分之一的机会看到不正确的数据，只要再刷新一次能够正确，就一定不是bug
   万分之一看到不正确的数据，并且只是万分之一的偏差而已
   由此换取的好处是，我们再也不用担心各种该死的“数据一致性。

总结

今天非常感谢石头哥的精彩分享，相信只有实际的业务操作经验才会有如此深刻的讲解！一切从实际业务出发，脱离理论，实践出真知。还是印证了那句话，一切脱离实际业务的架构都是耍流氓！感谢大家的阅读。

有想进一步了解石头哥的可以这篇文章《》

最后为石头哥的xcode打个广告：

newlife.xcode是一个有10多年历史的开源数据中间件，支持nfx/netstandard，由新生命团队(2002~2019)开发完成并维护至今，以下简称xcode。
xcode在2018年已经完成对大数据场景的特殊优化改造，2019年的目标是是针对分布式数据场景的优化。
最近石头哥也在为xcode编写系列教程：
整个系列教程会大量结合示例代码和运行日志来进行深入分析，蕴含多年开发经验于其中，代表作有百亿级大数据实时计算项目。

开源地址：https://github.com/newlifex/x（求star, 670+）