• 一、应用层关联的优势
• 二、应用层关联的使用场景
• 三、不推荐使用join的原因
• 四、不使用join的解决方案
• 五、join查询的优势

1.对于mysql，不推荐使用子查询和join是因为本身join的效率就是硬伤，一旦数据量很大效率就很难保证，强烈推荐分别根据索引单表取数据，然后在程序里面做join，merge数据。

2.子查询就更别用了，效率太差，执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响，这里多了一个创建和销毁临时表的过程。

3.如果是JOIN的话，它是走嵌套查询的。小表驱动大表，且通过索引字段进行关联。如果表记录比较少的话，还是OK的。大的话业务逻辑中可以控制处理。

4.数据库是最底层的，瓶颈往往是数据库。建议数据库只是作为数据store的工具，而不要添加业务上去。

一、应用层关联的优势

让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。如果关联中的某个表发生了变化，那么就无法使用查询缓存了，而拆分后，如果某个表很少改变，那么基于该表的查询就可以重复利用查询缓存结果了。

• 将查询分解后，执行单个查询可以减少锁的竞争。
• 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
• 查询本身效率也可能会有所提升。查询id集的时候，使用IN（）代替关联查询，可以让MySQL按照ID顺序进行查询，这可能比随机的关联要更高效。
• 可以减少冗余记录的查询。在应用层做关联查询，意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需
• 要重复地访问一部分数据。从这点看，这样的重构还可能会减少网络和内存的消艳。
• 更进一步，这样做相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。某些场景哈希关联的效率要高很多。

二、应用层关联的使用场景

• 当应用能够方便地缓存单个查询的结果的时候
• 当可以将数据分布到不同的MySQL服务器上的时候
• 当能够使用IN（）的方式代替关联查询的时候
• 并发场景多，DB查询频繁，需要分库分表

三、不推荐使用join的原因

1.DB承担的业务压力大，能减少负担就减少。当表处于百万级别后，join导致性能下降；

2.分布式的分库分表。这种时候是不建议跨库join的。目前mysql的分布式中间件，跨库join表现不良。

3.修改表的schema，单表查询的修改比较容易，join写的sql语句要修改，不容易发现，成本比较大，当系统比较大时，不好维护。

四、不使用join的解决方案

在业务层，单表查询出数据后，作为条件给下一个单表查询。也就是子查询。会担心子查询出来的结果集太多。mysql对in的数量没有限制，但是mysql限制整条sql语句的大小。

通过调整参数max_allowed_packet ，可以修改一条sql的最大值。建议在业务上做好处理，限制一次查询出来的结果集是能接受的。

五、join查询的优势

关联查询的好处是可以做分页，可以用副表的字段做查询条件，在查询的时候，将副表匹配到的字段作为结果集，用主表去in它。

但是问题来了，如果匹配到的数据量太大就不行了，也会导致返回的分页记录跟实际的不一样，解决的方法可以交给前端，一次性查询，让前端分批显示就可以了，这种解决方案的前提是数据量不太，因为sql本身长度有限。

• 一、背景
• 二、ES高可用方案
• 三、会员Redis缓存方案
• 四、高可用会员主库方案
• 五、异常会员关系治理
• 六、展望：更精细化的流控和降级策略

会员系统是一种基础系统，跟公司所有业务线的下单主流程密切相关。如果会员系统出故障，会导致用户无法下单，影响范围是全公司所有业务线。所以，会员系统必须保证高性能、高可用，提供稳定、高效的基础服务。

随着同程和艺龙两家公司的合并，越来越多的系统需要打通同程APP、艺龙APP、同程微信小程序、艺龙微信小程序等多平台会员体系。例如微信小程序的交叉营销，用户买了一张火车票，此时想给他发酒店红包，这就需要查询该用户的统一会员关系。因为火车票用的是同程会员体系，酒店用的是艺龙会员体系，只有查到对应的艺龙会员卡号后，才能将红包挂载到该会员账号。除了上述讲的交叉营销，还有许多场景需要查询统一会员关系，例如订单中心、会员等级、里程、红包、常旅、实名，以及各类营销活动等等。所以，会员系统的请求量越来越大，并发量越来越高，今年五一小长假的秒并发tps甚至超过2万多。在如此大流量的冲击下，会员系统是如何做到高性能和高可用的呢？这就是本文着重要讲述的内容。

同程和艺龙两家公司融合后，全平台所有体系的会员总量是十多亿。在这么大的数据体量下，业务线的查询维度也比较复杂。有的业务线基于手机号，有的基于微信unionid，也有的基于艺龙卡号等查询会员信息。这么大的数据量，又有这么多的查询维度，基于此，我们选择ES用来存储统一会员关系。ES集群在整个会员系统架构中非常重要，那么如何保证ES的高可用呢？

首先我们知道，ES集群本身就是保证高可用的，如下图所示：

当ES集群有一个节点宕机了，会将其他节点对应的Replica Shard升级为Primary Shard，继续提供服务。但即使是这样，还远远不够。例如ES集群都部署在机房A，现在机房A突然断电了，怎么办？例如服务器硬件故障，ES集群大部分机器宕机了，怎么办？或者突然有个非常热门的抢购秒杀活动，带来了一波非常大的流量，直接把ES集群打死了，怎么办？面对这些情况，让运维兄弟冲到机房去解决？这个非常不现实，因为会员系统直接影响全公司所有业务线的下单主流程，故障恢复的时间必须非常短，如果需要运维兄弟人工介入，那这个时间就太长了，是绝对不能容忍的。那ES的高可用如何做呢？我们的方案是ES双中心主备集群架构。

我们有两个机房，分别是机房A和机房B。我们把ES主集群部署在机房A，把ES备集群部署在机房B。会员系统的读写都在ES主集群，通过MQ将数据同步到ES备集群。此时，如果ES主集群崩了，通过统一配置，将会员系统的读写切到机房B的ES备集群上，这样即使ES主集群挂了，也能在很短的时间内实现故障转移，确保会员系统的稳定运行。最后，等ES主集群故障恢复后，打开开关，将故障期间的数据同步到ES主集群，等数据同步一致后，再将会员系统的读写切到ES主集群。

双中心ES主备集群做到这一步，感觉应该没啥大问题了，但去年的一次恐怖流量冲击让我们改变了想法。那是一个节假日，某个业务上线了一个营销活动，在用户的一次请求中，循环10多次调用了会员系统，导致会员系统的tps暴涨，差点把ES集群打爆。这件事让我们后怕不已，它让我们意识到，一定要对调用方进行优先级分类，实施更精细的隔离、熔断、降级、限流策略。首先，我们梳理了所有调用方，分出两大类请求类型。第一类是跟用户的下单主流程密切相关的请求，这类请求非常重要，应该高优先级保障。第二类是营销活动相关的，这类请求有个特点，他们的请求量很大，tps很高，但不影响下单主流程。基于此，我们又构建了一个ES集群，专门用来应对高tps的营销秒杀类请求，这样就跟ES主集群隔离开来，不会因为某个营销活动的流量冲击而影响用户的下单主流程。如下图所示：

讲完了ES的双中心主备集群高可用架构，接下来我们深入讲解一下ES主集群的优化工作。有一段时间，我们特别痛苦，就是每到饭点，ES集群就开始报警，搞得每次吃饭都心慌慌的，生怕ES集群一个扛不住，就全公司炸锅了。那为什么一到饭点就报警呢？因为流量比较大， 导致ES线程数飙高，cpu直往上窜，查询耗时增加，并传导给所有调用方，导致更大范围的延时。那么如何解决这个问题呢？通过深入ES集群，我们发现了以下几个问题： 

• ES负载不合理，热点问题严重。ES主集群一共有几十个节点，有的节点上部署的shard数偏多，有的节点部署的shard数很少，导致某些服务器的负载很高，每到流量高峰期，就经常预警。

  
  

• ES线程池的大小设置得太高，导致cpu飙高。我们知道，设置ES的threadpool，一般将线程数设置为服务器的cpu核数，即使ES的查询压力很大，需要增加线程数，那最好也不要超过“cpu core * 3 / 2 + 1”。如果设置的线程数过多，会导致cpu在多个线程上下文之间频繁来回切换，浪费大量cpu资源。

  
  

• shard分配的内存太大，100g，导致查询变慢。我们知道，ES的索引要合理分配shard数，要控制一个shard的内存大小在50g以内。如果一个shard分配的内存过大，会导致查询变慢，耗时增加，严重拖累性能。

  
  

• string类型的字段设置了双字段，既是text，又是keyword，导致存储容量增大了一倍。会员信息的查询不需要关联度打分，直接根据keyword查询就行，所以完全可以将text字段去掉，这样就能节省很大一部分存储空间，提升性能。

  
  

• ES查询，使用filter，不使用query。因为query会对搜索结果进行相关度算分，比较耗cpu，而会员信息的查询是不需要算分的，这部分的性能损耗完全可以避免。

  
  

• 节约ES算力，将ES的搜索结果排序放在会员系统的jvm内存中进行。

  
  

• 增加routing key。我们知道，一次ES查询，会将请求分发给所有shard，等所有shard返回结果后再聚合数据，最后将结果返回给调用方。如果我们事先已经知道数据分布在哪些shard上，那么就可以减少大量不必要的请求，提升查询性能。

经过以上优化，成果非常显著，ES集群的cpu大幅下降，查询性能大幅提升。ES集群的cpu使用率： 

会员系统的接口耗时：

一直以来，会员系统是不做缓存的，原因主要有两个：第一个，前面讲的ES集群性能很好，秒并发3万多，99线耗时5毫秒左右，已经足够应付各种棘手的场景。第二个，有的业务对会员的绑定关系要求实时一致，而会员是一个发展了10多年的老系统，是一个由好多接口、好多系统组成的分布式系统。所以，只要有一个接口没有考虑到位，没有及时去更新缓存，就会导致脏数据，进而引发一系列的问题，例如：用户在APP上看不到微信订单、APP和微信的会员等级、里程等没合并、微信和APP无法交叉营销等等。那后来为什么又要做缓存呢？是因为今年机票的盲盒活动，它带来的瞬时并发太高了。虽然会员系统安然无恙，但还是有点心有余悸，稳妥起见，最终还是决定实施缓存方案。

在做会员缓存方案的过程中，遇到一个ES引发的问题，该问题会导致缓存数据的不一致。我们知道，ES操作数据是近实时的，往ES新增一个Document，此时立即去查，是查不到的，需要等待1秒后才能查询到。如下图所示：

ES的近实时机制为什么会导致redis缓存数据不一致呢？具体来讲，假设一个用户注销了自己的APP账号，此时需要更新ES，删除APP账号和微信账号的绑定关系。而ES的数据更新是近实时的，也就是说，1秒后你才能查询到更新后的数据。而就在这1秒内，有个请求来查询该用户的会员绑定关系，它先到redis缓存中查，发现没有，然后到ES查，查到了，但查到的是更新前的旧数据。最后，该请求把查询到的旧数据更新到redis缓存并返回。就这样，1秒后，ES中该用户的会员数据更新了，但redis缓存的数据还是旧数据，导致了redis缓存跟ES的数据不一致。如下图所示：

面对该问题，如何解决呢？我们的思路是，在更新ES数据时，加一个2秒的redis分布式并发锁，为了保证缓存数据的一致性，接着再删除redis中该会员的缓存数据。如果此时有请求来查询数据，先获取分布式锁，发现该会员ID已经上锁了，说明ES刚刚更新的数据尚未生效，那么此时查询完数据后就不更新redis缓存了，直接返回，这样就避免了缓存数据的不一致问题。如下图所示：

上述方案，乍一看似乎没什么问题了，但仔细分析，还是有可能导致缓存数据的不一致。例如，在更新请求加分布式锁之前，恰好有一个查询请求获取分布式锁，而此时是没有锁的，所以它可以继续更新缓存。但就在他更新缓存之前，线程block了，此时更新请求来了，加了分布式锁，并删除了缓存。当更新请求完成操作后，查询请求的线程活过来了，此时它再执行更新缓存，就把脏数据写到缓存中了。发现没有？主要的问题症结就在于“删除缓存”和“更新缓存”发生了并发冲突，只要将它们互斥，就能解决问题。如下图所示：

实施了缓存方案后，经统计，缓存命中率90%+，极大缓解了ES的压力，会员系统整体性能得到了很大提升。

接下来，我们看一下如何保障Redis集群的高可用。如下图所示： 

关于Redis集群的高可用，我们采用了双中心多集群的模式。在机房A和机房B各部署一套Redis集群。更新缓存数据时，双写，只有两个机房的redis集群都写成功了，才返回成功。查询缓存数据时，机房内就近查询，降低延时。这样，即使机房A整体故障，机房B还能提供完整的会员服务。

上述讲到，全平台会员的绑定关系数据存在ES，而会员的注册明细数据存在关系型数据库。最早，会员使用的数据库是SqlServer，直到有一天，DBA找到我们说，单台SqlServer数据库已经存储了十多亿的会员数据，服务器已达到物理极限，不能再扩展了。按照现在的增长趋势，过不了多久，整个SqlServer数据库就崩了。你想想，那是一种什么样的灾难场景：会员数据库崩了，会员系统就崩了；会员系统崩了，全公司所有业务线就崩了。想想就不寒而栗，酸爽无比，为此我们立刻开启了迁移DB的工作。

经过调研，我们选择了双中心分库分表的MySql集群方案，如下图所示：

会员一共有十多亿的数据，我们把会员主库分了1000多个分片，平分到每个分片大概百万的量级，足够使用了。MySql集群采用1主3从的架构，主库放在机房A，从库放在机房B，两个机房之间通过专线同步数据，延迟在1毫秒内。会员系统通过DBRoute读写数据，写数据都路由到master节点所在的机房A，读数据都路由到本地机房，就近访问，减少网络延迟。这样，采用双中心的MySql集群架构，极大提高了可用性，即使机房A整体都崩了，还可以将机房B的Slave升级为Master，继续提供服务。

双中心MySql集群搭建好后，我们进行了压测，测试下来，秒并发能达到2万多，平均耗时在10毫秒内，性能达标。

接下来的工作，就是把会员系统的底层存储从SqlServer切到MySql上，这是个风险极高的工作，主要有以下几个难点：

• 会员系统是一刻都不能停机的，要在不停机的情况下完成SqlServer到MySql的切换，就像是在给高速行驶的汽车换轮子。

  
  

• 会员系统是由很多个系统和接口组成的，毕竟发展了10多年，由于历史原因，遗留了大量老接口，逻辑错综复杂。这么多系统，必须一个不落的全部梳理清楚，DAL层代码必须重写，而且不能出任何问题，否则将是灾难性的。

  
  

• 数据的迁移要做到无缝迁移，不仅是存量10多亿数据的迁移，实时产生的数据也要无缝同步到mysql。另外，除了要保障数据同步的实时性，还要保证数据的正确性，以及SqlServer和MySql数据的一致性。

基于以上痛点，我们设计了“全量同步、增量同步、实时流量灰度切换”的技术方案。

首先，为了保证数据的无缝切换，采用实时双写的方案。因为业务逻辑的复杂，以及SqlServer和MySql的技术差异性，在双写mysql的过程中，不一定会写成功，而一旦写失败，就会导致SqlServer和MySql的数据不一致，这是绝不允许的。所以，我们采取的策略是，在试运行期间，主写SqlServer，然后通过线程池异步写MySql，如果写失败了，重试三次，如果依然失败，则记日志，然后人工排查原因，解决后，继续双写，直到运行一段时间，没有双写失败的情况。通过上述策略，可以确保在绝大部分情况下，双写操作的正确性和稳定性，即使在试运行期间出现了SqlServer和MySql的数据不一致的情况，也可以基于SqlServer再次全量构建出MySql的数据，因为我们在设计双写策略时，会确保SqlServer一定能写成功，也就是说，SqlServer中的数据是全量最完整、最正确的。如下图所示：

 讲完了双写，接下来我们看一下“读数据”如何灰度。整体思路是，通过A/B平台逐步灰度流量，刚开始100%的流量读取SqlServer数据库，然后逐步切流量读取MySql数据库，先1%，如果没有问题，再逐步放流量，最终100%的流量都走MySql数据库。在逐步灰度流量的过程中，需要有验证机制，只有验证没问题了，才能进一步放大流量。那么这个验证机制如何实施呢？方案是，在一次查询请求里，通过异步线程，比较SqlServer和 MySql的查询结果是否一致，如果不一致，记日志，再人工检查不一致的原因，直到彻底解决不一致的问题后，再逐步灰度流量。如下图所示：

所以，整体的实施流程如下：

首先，在一个夜黑风高的深夜，流量最小的时候，完成SqlServer到MySql数据库的全量数据同步。接着，开启双写，此时，如果有用户注册，就会实时双写到两个数据库。那么，在全量同步和实时双写开启之间，两个数据库还相差这段时间的数据，所以需要再次增量同步，把数据补充完整，以防数据的不一致。剩下的时间，就是各种日志监控，看双写是否有问题，看数据比对是否一致等等。这段时间是耗时最长的，也是最容易发生问题的，如果有的问题比较严重，导致数据不一致了，就需要从头再来，再次基于SqlServer全量构建MySql数据库，然后重新灰度流量，直到最后，100%的流量全部灰度到MySql，此时就大功告成了，下线灰度逻辑，所有读写都切到MySql集群。

做到这一步，感觉会员主库应该没问题了，可dal组件的一次严重故障改变了我们的想法。那次故障很恐怖，公司很多应用连接不上数据库了，创单量直线往下掉，这让我们意识到，即使数据库是好的，但dal组件异常，依然能让会员系统挂掉。所以，我们再次异构了会员主库的数据源，双写数据到ES，如下所示：

如果dal组件故障或MySql数据库挂了，可以把读写切到ES，等MySql恢复了，再把数据同步到MySql，最后把读写再切回到MySql数据库。如下图所示：

会员系统不仅仅要保证系统的稳定和高可用，数据的精准和正确也同样重要。举个例子，一个分布式并发故障，导致一名用户的APP账户绑定了别人的微信小程序账户，这将会带来非常恶劣的影响。首先，一旦这两个账号绑定了，那么这两个用户下的酒店、机票、火车票订单是互相可以看到的。你想想，别人能看到你订的酒店订单，你火不火，会不会投诉？除了能看到别人的订单，你还能操作订单。例如，一个用户在APP的订单中心，看到了别人订的机票订单，他觉得不是自己的订单，就把订单取消了。这将会带来非常严重的客诉，大家知道，机票退订费用是挺高的，这不仅影响了该用户的正常出行，还导致了比较大的经济损失，非常糟糕。

针对这些异常会员账号，我们进行了详细的梳理，通过非常复杂烧脑的逻辑识别出这些账号，并对会员接口进行了深度优化治理，在代码逻辑层堵住了相关漏洞，完成了异常会员的治理工作。如下图所示：

任何一个系统，都不能保证百分之一百不出问题，所以我们要有面向失败的设计，那就是更精细化的流控和降级策略。

热点控制。针对黑产刷单的场景，同一个会员id会有大量重复的请求，形成热点账号，当这些账号的访问超过设定阈值时，实施限流策略。

基于调用账号的流控规则。这个策略主要是防止调用方的代码bug导致的大流量。例如，调用方在一次用户请求中，循环很多次来调用会员接口，导致会员系统流量暴增很多倍。所以，要针对每个调用账号设置流控规则，当超过阈值时，实施限流策略。

全局流控规则。我们会员系统能抗下tps 3万多的秒并发请求量，如果此时，有个很恐怖的流量打过来，tps高达10万，与其让这波流量把会员数据库、es全部打死，还不如把超过会员系统承受范围之外的流量快速失败，至少tps 3万内的会员请求能正常响应，不会让整个会员系统全部崩溃。

基于平均响应时间的降级。会员接口也有依赖其他接口，当调用其他接口的平均响应时间超过阈值，进入准降级状态。如果接下来 1s 内进入的请求，它们的平均响应时间都持续超过阈值，那么在接下的时间窗口内，自动地熔断。

基于异常数和异常比例的降级。当会员接口依赖的其他接口发生异常，如果1分钟内的异常数超过阈值，或者每秒异常总数占通过量的比值超过阈值，进入降级状态，在接下的时间窗口之内，自动熔断。

目前，我们最大的痛点是会员调用账号的治理。公司内，想要调用会员接口，必须申请一个调用账号，我们会记录该账号的使用场景，并设置流控、降级策略的规则。但在实际使用的过程中，申请了该账号的同事，可能异动到其他部门了，此时他可能也会调用会员系统，为了省事，他不会再次申请会员账号，而是直接沿用以前的账号过来调用，这导致我们无法判断一个会员账号的具体使用场景是什么，也就无法实施更精细的流控和降级策略。所以，接下来，我们将会对所有调用账号进行一个个的梳理，这是个非常庞大且繁琐的工作，但无路如何，硬着头皮也要做好。

• 一、背景
• 二、ES高可用方案
• 三、会员Redis缓存方案
• 四、高可用会员主库方案
• 五、异常会员关系治理
• 六、展望：更精细化的流控和降级策略

一、背景

会员系统是一种基础系统，跟公司所有业务线的下单主流程密切相关。如果会员系统出故障，会导致用户无法下单，影响范围是全公司所有业务线。所以，会员系统必须保证高性能、高可用，提供稳定、高效的基础服务。

随着同程和艺龙两家公司的合并，越来越多的系统需要打通同程APP、艺龙APP、同程微信小程序、艺龙微信小程序等多平台会员体系。例如微信小程序的交叉营销，用户买了一张火车票，此时想给他发酒店红包，这就需要查询该用户的统一会员关系。因为火车票用的是同程会员体系，酒店用的是艺龙会员体系，只有查到对应的艺龙会员卡号后，才能将红包挂载到该会员账号。除了上述讲的交叉营销，还有许多场景需要查询统一会员关系，例如订单中心、会员等级、里程、红包、常旅、实名，以及各类营销活动等等。所以，会员系统的请求量越来越大，并发量越来越高，今年五一小长假的秒并发tps甚至超过2万多。在如此大流量的冲击下，会员系统是如何做到高性能和高可用的呢？这就是本文着重要讲述的内容。

二、ES高可用方案

1. ES双中心主备集群架构

同程和艺龙两家公司融合后，全平台所有体系的会员总量是十多亿。在这么大的数据体量下，业务线的查询维度也比较复杂。有的业务线基于手机号，有的基于微信unionid，也有的基于艺龙卡号等查询会员信息。这么大的数据量，又有这么多的查询维度，基于此，我们选择ES用来存储统一会员关系。ES集群在整个会员系统架构中非常重要，那么如何保证ES的高可用呢？

首先我们知道，ES集群本身就是保证高可用的，如下图所示：

当ES集群有一个节点宕机了，会将其他节点对应的Replica Shard升级为Primary Shard，继续提供服务。但即使是这样，还远远不够。例如ES集群都部署在机房A，现在机房A突然断电了，怎么办？例如服务器硬件故障，ES集群大部分机器宕机了，怎么办？或者突然有个非常热门的抢购秒杀活动，带来了一波非常大的流量，直接把ES集群打死了，怎么办？面对这些情况，让运维兄弟冲到机房去解决？这个非常不现实，因为会员系统直接影响全公司所有业务线的下单主流程，故障恢复的时间必须非常短，如果需要运维兄弟人工介入，那这个时间就太长了，是绝对不能容忍的。那ES的高可用如何做呢？我们的方案是ES双中心主备集群架构。

我们有两个机房，分别是机房A和机房B。我们把ES主集群部署在机房A，把ES备集群部署在机房B。会员系统的读写都在ES主集群，通过MQ将数据同步到ES备集群。此时，如果ES主集群崩了，通过统一配置，将会员系统的读写切到机房B的ES备集群上，这样即使ES主集群挂了，也能在很短的时间内实现故障转移，确保会员系统的稳定运行。最后，等ES主集群故障恢复后，打开开关，将故障期间的数据同步到ES主集群，等数据同步一致后，再将会员系统的读写切到ES主集群。

2. ES流量隔离三集群架构

双中心ES主备集群做到这一步，感觉应该没啥大问题了，但去年的一次恐怖流量冲击让我们改变了想法。那是一个节假日，某个业务上线了一个营销活动，在用户的一次请求中，循环10多次调用了会员系统，导致会员系统的tps暴涨，差点把ES集群打爆。这件事让我们后怕不已，它让我们意识到，一定要对调用方进行优先级分类，实施更精细的隔离、熔断、降级、限流策略。首先，我们梳理了所有调用方，分出两大类请求类型。第一类是跟用户的下单主流程密切相关的请求，这类请求非常重要，应该高优先级保障。第二类是营销活动相关的，这类请求有个特点，他们的请求量很大，tps很高，但不影响下单主流程。基于此，我们又构建了一个ES集群，专门用来应对高tps的营销秒杀类请求，这样就跟ES主集群隔离开来，不会因为某个营销活动的流量冲击而影响用户的下单主流程。如下图所示：

3. ES集群深度优化提升

讲完了ES的双中心主备集群高可用架构，接下来我们深入讲解一下ES主集群的优化工作。有一段时间，我们特别痛苦，就是每到饭点，ES集群就开始报警，搞得每次吃饭都心慌慌的，生怕ES集群一个扛不住，就全公司炸锅了。那为什么一到饭点就报警呢？因为流量比较大， 导致ES线程数飙高，cpu直往上窜，查询耗时增加，并传导给所有调用方，导致更大范围的延时。那么如何解决这个问题呢？通过深入ES集群，我们发现了以下几个问题：

• ES负载不合理，热点问题严重。ES主集群一共有几十个节点，有的节点上部署的shard数偏多，有的节点部署的shard数很少，导致某些服务器的负载很高，每到流量高峰期，就经常预警。
• ES线程池的大小设置得太高，导致cpu飙高。我们知道，设置ES的threadpool，一般将线程数设置为服务器的cpu核数，即使ES的查询压力很大，需要增加线程数，那最好也不要超过“cpu core * 3 / 2 + 1”。如果设置的线程数过多，会导致cpu在多个线程上下文之间频繁来回切换，浪费大量cpu资源。
• shard分配的内存太大，100g，导致查询变慢。我们知道，ES的索引要合理分配shard数，要控制一个shard的内存大小在50g以内。如果一个shard分配的内存过大，会导致查询变慢，耗时增加，严重拖累性能。
• string类型的字段设置了双字段，既是text，又是keyword，导致存储容量增大了一倍。会员信息的查询不需要关联度打分，直接根据keyword查询就行，所以完全可以将text字段去掉，这样就能节省很大一部分存储空间，提升性能。
• ES查询，使用filter，不使用query。因为query会对搜索结果进行相关度算分，比较耗cpu，而会员信息的查询是不需要算分的，这部分的性能损耗完全可以避免。
• 节约ES算力，将ES的搜索结果排序放在会员系统的jvm内存中进行。
• 增加routing key。我们知道，一次ES查询，会将请求分发给所有shard，等所有shard返回结果后再聚合数据，最后将结果返回给调用方。如果我们事先已经知道数据分布在哪些shard上，那么就可以减少大量不必要的请求，提升查询性能。

经过以上优化，成果非常显著，ES集群的cpu大幅下降，查询性能大幅提升。ES集群的cpu使用率：

会员系统的接口耗时：

三、会员Redis缓存方案

一直以来，会员系统是不做缓存的，原因主要有两个：第一个，前面讲的ES集群性能很好，秒并发3万多，99线耗时5毫秒左右，已经足够应付各种棘手的场景。第二个，有的业务对会员的绑定关系要求实时一致，而会员是一个发展了10多年的老系统，是一个由好多接口、好多系统组成的分布式系统。所以，只要有一个接口没有考虑到位，没有及时去更新缓存，就会导致脏数据，进而引发一系列的问题，例如：用户在APP上看不到微信订单、APP和微信的会员等级、里程等没合并、微信和APP无法交叉营销等等。那后来为什么又要做缓存呢？是因为今年机票的盲盒活动，它带来的瞬时并发太高了。虽然会员系统安然无恙，但还是有点心有余悸，稳妥起见，最终还是决定实施缓存方案。

1. ES近一秒延时导致的Redis缓存数据不一致问题的解决方案

在做会员缓存方案的过程中，遇到一个ES引发的问题，该问题会导致缓存数据的不一致。我们知道，ES操作数据是近实时的，往ES新增一个Document，此时立即去查，是查不到的，需要等待1秒后才能查询到。如下图所示：

ES的近实时机制为什么会导致redis缓存数据不一致呢？具体来讲，假设一个用户注销了自己的APP账号，此时需要更新ES，删除APP账号和微信账号的绑定关系。而ES的数据更新是近实时的，也就是说，1秒后你才能查询到更新后的数据。而就在这1秒内，有个请求来查询该用户的会员绑定关系，它先到redis缓存中查，发现没有，然后到ES查，查到了，但查到的是更新前的旧数据。最后，该请求把查询到的旧数据更新到redis缓存并返回。就这样，1秒后，ES中该用户的会员数据更新了，但redis缓存的数据还是旧数据，导致了redis缓存跟ES的数据不一致。如下图所示：

面对该问题，如何解决呢？我们的思路是，在更新ES数据时，加一个2秒的redis分布式并发锁，为了保证缓存数据的一致性，接着再删除redis中该会员的缓存数据。如果此时有请求来查询数据，先获取分布式锁，发现该会员ID已经上锁了，说明ES刚刚更新的数据尚未生效，那么此时查询完数据后就不更新redis缓存了，直接返回，这样就避免了缓存数据的不一致问题。如下图所示：

上述方案，乍一看似乎没什么问题了，但仔细分析，还是有可能导致缓存数据的不一致。例如，在更新请求加分布式锁之前，恰好有一个查询请求获取分布式锁，而此时是没有锁的，所以它可以继续更新缓存。但就在他更新缓存之前，线程block了，此时更新请求来了，加了分布式锁，并删除了缓存。当更新请求完成操作后，查询请求的线程活过来了，此时它再执行更新缓存，就把脏数据写到缓存中了。发现没有？主要的问题症结就在于“删除缓存”和“更新缓存”发生了并发冲突，只要将它们互斥，就能解决问题。如下图所示：

实施了缓存方案后，经统计，缓存命中率90%+，极大缓解了ES的压力，会员系统整体性能得到了很大提升。

2. Redis双中心多集群架构

接下来，我们看一下如何保障Redis集群的高可用。如下图所示：

关于Redis集群的高可用，我们采用了双中心多集群的模式。在机房A和机房B各部署一套Redis集群。更新缓存数据时，双写，只有两个机房的redis集群都写成功了，才返回成功。查询缓存数据时，机房内就近查询，降低延时。这样，即使机房A整体故障，机房B还能提供完整的会员服务。

四、高可用会员主库方案

上述讲到，全平台会员的绑定关系数据存在ES，而会员的注册明细数据存在关系型数据库。最早，会员使用的数据库是SqlServer，直到有一天，DBA找到我们说，单台SqlServer数据库已经存储了十多亿的会员数据，服务器已达到物理极限，不能再扩展了。按照现在的增长趋势，过不了多久，整个SqlServer数据库就崩了。你想想，那是一种什么样的灾难场景：会员数据库崩了，会员系统就崩了；会员系统崩了，全公司所有业务线就崩了。想想就不寒而栗，酸爽无比，为此我们立刻开启了迁移DB的工作。

1. MySql双中心Partition集群方案

经过调研，我们选择了双中心分库分表的MySql集群方案，如下图所示：

会员一共有十多亿的数据，我们把会员主库分了1000多个分片，平分到每个分片大概百万的量级，足够使用了。MySql集群采用1主3从的架构，主库放在机房A，从库放在机房B，两个机房之间通过专线同步数据，延迟在1毫秒内。会员系统通过DBRoute读写数据，写数据都路由到master节点所在的机房A，读数据都路由到本地机房，就近访问，减少网络延迟。这样，采用双中心的MySql集群架构，极大提高了可用性，即使机房A整体都崩了，还可以将机房B的Slave升级为Master，继续提供服务。

双中心MySql集群搭建好后，我们进行了压测，测试下来，秒并发能达到2万多，平均耗时在10毫秒内，性能达标。

2. 会员主库平滑迁移方案

接下来的工作，就是把会员系统的底层存储从SqlServer切到MySql上，这是个风险极高的工作，主要有以下几个难点：

• 会员系统是一刻都不能停机的，要在不停机的情况下完成SqlServer到MySql的切换，就像是在给高速行驶的汽车换轮子。
• 会员系统是由很多个系统和接口组成的，毕竟发展了10多年，由于历史原因，遗留了大量老接口，逻辑错综复杂。这么多系统，必须一个不落的全部梳理清楚，DAL层代码必须重写，而且不能出任何问题，否则将是灾难性的。
• 数据的迁移要做到无缝迁移，不仅是存量10多亿数据的迁移，实时产生的数据也要无缝同步到mysql。另外，除了要保障数据同步的实时性，还要保证数据的正确性，以及SqlServer和MySql数据的一致性。

基于以上痛点，我们设计了“全量同步、增量同步、实时流量灰度切换”的技术方案。

首先，为了保证数据的无缝切换，采用实时双写的方案。因为业务逻辑的复杂，以及SqlServer和MySql的技术差异性，在双写mysql的过程中，不一定会写成功，而一旦写失败，就会导致SqlServer和MySql的数据不一致，这是绝不允许的。所以，我们采取的策略是，在试运行期间，主写SqlServer，然后通过线程池异步写MySql，如果写失败了，重试三次，如果依然失败，则记日志，然后人工排查原因，解决后，继续双写，直到运行一段时间，没有双写失败的情况。通过上述策略，可以确保在绝大部分情况下，双写操作的正确性和稳定性，即使在试运行期间出现了SqlServer和MySql的数据不一致的情况，也可以基于SqlServer再次全量构建出MySql的数据，因为我们在设计双写策略时，会确保SqlServer一定能写成功，也就是说，SqlServer中的数据是全量最完整、最正确的。如下图所示：

讲完了双写，接下来我们看一下“读数据”如何灰度。整体思路是，通过A/B平台逐步灰度流量，刚开始100%的流量读取SqlServer数据库，然后逐步切流量读取MySql数据库，先1%，如果没有问题，再逐步放流量，最终100%的流量都走MySql数据库。在逐步灰度流量的过程中，需要有验证机制，只有验证没问题了，才能进一步放大流量。那么这个验证机制如何实施呢？方案是，在一次查询请求里，通过异步线程，比较SqlServer和 MySql的查询结果是否一致，如果不一致，记日志，再人工检查不一致的原因，直到彻底解决不一致的问题后，再逐步灰度流量。如下图所示：

所以，整体的实施流程如下：

首先，在一个夜黑风高的深夜，流量最小的时候，完成SqlServer到MySql数据库的全量数据同步。接着，开启双写，此时，如果有用户注册，就会实时双写到两个数据库。那么，在全量同步和实时双写开启之间，两个数据库还相差这段时间的数据，所以需要再次增量同步，把数据补充完整，以防数据的不一致。剩下的时间，就是各种日志监控，看双写是否有问题，看数据比对是否一致等等。这段时间是耗时最长的，也是最容易发生问题的，如果有的问题比较严重，导致数据不一致了，就需要从头再来，再次基于SqlServer全量构建MySql数据库，然后重新灰度流量，直到最后，100%的流量全部灰度到MySql，此时就大功告成了，下线灰度逻辑，所有读写都切到MySql集群。

3. MySql和ES主备集群方案

做到这一步，感觉会员主库应该没问题了，可dal组件的一次严重故障改变了我们的想法。那次故障很恐怖，公司很多应用连接不上数据库了，创单量直线往下掉，这让我们意识到，即使数据库是好的，但dal组件异常，依然能让会员系统挂掉。所以，我们再次异构了会员主库的数据源，双写数据到ES，如下所示：

如果dal组件故障或MySql数据库挂了，可以把读写切到ES，等MySql恢复了，再把数据同步到MySql，最后把读写再切回到MySql数据库。如下图所示：

五、异常会员关系治理

会员系统不仅仅要保证系统的稳定和高可用，数据的精准和正确也同样重要。举个例子，一个分布式并发故障，导致一名用户的APP账户绑定了别人的微信小程序账户，这将会带来非常恶劣的影响。首先，一旦这两个账号绑定了，那么这两个用户下的酒店、机票、火车票订单是互相可以看到的。你想想，别人能看到你订的酒店订单，你火不火，会不会投诉？除了能看到别人的订单，你还能操作订单。例如，一个用户在APP的订单中心，看到了别人订的机票订单，他觉得不是自己的订单，就把订单取消了。这将会带来非常严重的客诉，大家知道，机票退订费用是挺高的，这不仅影响了该用户的正常出行，还导致了比较大的经济损失，非常糟糕。

针对这些异常会员账号，我们进行了详细的梳理，通过非常复杂烧脑的逻辑识别出这些账号，并对会员接口进行了深度优化治理，在代码逻辑层堵住了相关漏洞，完成了异常会员的治理工作。如下图所示：

六、展望：更精细化的流控和降级策略

任何一个系统，都不能保证百分之一百不出问题，所以我们要有面向失败的设计，那就是更精细化的流控和降级策略。

1. 更精细化的流控策略

热点控制。针对黑产刷单的场景，同一个会员id会有大量重复的请求，形成热点账号，当这些账号的访问超过设定阈值时，实施限流策略。

基于调用账号的流控规则。这个策略主要是防止调用方的代码bug导致的大流量。例如，调用方在一次用户请求中，循环很多次来调用会员接口，导致会员系统流量暴增很多倍。所以，要针对每个调用账号设置流控规则，当超过阈值时，实施限流策略。

全局流控规则。我们会员系统能抗下tps 3万多的秒并发请求量，如果此时，有个很恐怖的流量打过来，tps高达10万，与其让这波流量把会员数据库、es全部打死，还不如把超过会员系统承受范围之外的流量快速失败，至少tps 3万内的会员请求能正常响应，不会让整个会员系统全部崩溃。

2. 更精细化的降级策略

基于平均响应时间的降级。会员接口也有依赖其他接口，当调用其他接口的平均响应时间超过阈值，进入准降级状态。如果接下来 1s 内进入的请求，它们的平均响应时间都持续超过阈值，那么在接下的时间窗口内，自动地熔断。

基于异常数和异常比例的降级。当会员接口依赖的其他接口发生异常，如果1分钟内的异常数超过阈值，或者每秒异常总数占通过量的比值超过阈值，进入降级状态，在接下的时间窗口之内，自动熔断。

目前，我们最大的痛点是会员调用账号的治理。公司内，想要调用会员接口，必须申请一个调用账号，我们会记录该账号的使用场景，并设置流控、降级策略的规则。但在实际使用的过程中，申请了该账号的同事，可能异动到其他部门了，此时他可能也会调用会员系统，为了省事，他不会再次申请会员账号，而是直接沿用以前的账号过来调用，这导致我们无法判断一个会员账号的具体使用场景是什么，也就无法实施更精细的流控和降级策略。所以，接下来，我们将会对所有调用账号进行一个个的梳理，这是个非常庞大且繁琐的工作，但无路如何，硬着头皮也要做好。

• 前言

• 准备数据

• 创建表

• 创建数据脚本

• 开始测试

• 普通分页查询

• 如何优化

• 优化偏移量大问题

• 优化数据量大问题

• SELECT * 它不香吗？

• 结束

前言

• 面试官：来说说，一千万的数据，你是怎么查询的？
• B哥：直接分页查询，使用limit分页。
• 面试官：有实操过吗？
• B哥：肯定有呀

此刻献上一首《凉凉》

也许有些人没遇过上千万数据量的表，也不清楚查询上千万数据量的时候会发生什么。

今天就来带大家实操一下，这次是基于MySQL 5.7.26做测试

准备数据

没有一千万的数据怎么办？

创建呗

代码创建一千万？那是不可能的，太慢了，可能真的要跑一天。可以采用数据库脚本执行速度快很多。

创建表

CREATE TABLE `user_operation_log`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `ip` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `op_data` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr3` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr4` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr5` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr6` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr7` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr8` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr9` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr10` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr11` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr12` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

创建数据脚本

采用批量插入，效率会快很多，而且每1000条数就commit，数据量太大，也会导致批量插入效率慢

DELIMITER ;;
CREATE PROCEDURE batch_insert_log()
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE userId INT DEFAULT 10000000;
 set @execSql = 'INSERT INTO `test`.`user_operation_log`(`user_id`, `ip`, `op_data`, `attr1`, `attr2`, `attr3`, `attr4`, `attr5`, `attr6`, `attr7`, `attr8`, `attr9`, `attr10`, `attr11`, `attr12`) VALUES';
 set @execData = '';
  WHILE i   set @attr = "'测试很长很长很长很长很长很长很长很长很长很长很长很长很长很长很长很长很长的属性'";
  set @execData = concat(@execData, "(", userId + i, ", '10.0.69.175', '用户登录操作'", ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ")");
  if i % 1000 = 0
  then
     set @stmtSql = concat(@execSql, @execData,";");
    prepare stmt from @stmtSql;
    execute stmt;
    DEALLOCATE prepare stmt;
    commit;
    set @execData = "";
   else
     set @execData = concat(@execData, ",");
   end if;
  SET i=i+1;
  END WHILE;

END;;
DELIMITER ;

开始测试

哥的电脑配置比较低：win10 标压渣渣i5 读写约500MB的SSD

由于配置低，本次测试只准备了3148000条数据，占用了磁盘5G(还没建索引的情况下)，跑了38min，电脑配置好的同学，可以插入多点数据测试

SELECT count(1) FROM `user_operation_log`

返回结果：3148000

三次查询时间分别为：

• 14060 ms
• 13755 ms
• 13447 ms

普通分页查询

MySQL 支持 LIMIT 语句来选取指定的条数数据， Oracle 可以使用 ROWNUM 来选取。

MySQL分页查询语法如下：

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

• 第一个参数指定第一个返回记录行的偏移量
• 第二个参数指定返回记录行的最大数目

下面我们开始测试查询结果：

SELECT * FROM `user_operation_log` LIMIT 10000, 10

查询3次时间分别为：

• 59 ms
• 49 ms
• 50 ms

这样看起来速度还行，不过是本地数据库，速度自然快点。

换个角度来测试

相同偏移量，不同数据量

SELECT * FROM `user_operation_log` LIMIT 10000, 10
SELECT * FROM `user_operation_log` LIMIT 10000, 100
SELECT * FROM `user_operation_log` LIMIT 10000, 1000
SELECT * FROM `user_operation_log` LIMIT 10000, 10000
SELECT * FROM `user_operation_log` LIMIT 10000, 100000
SELECT * FROM `user_operation_log` LIMIT 10000, 1000000

查询时间如下：

从上面结果可以得出结束：数据量越大，花费时间越长

相同数据量，不同偏移量

SELECT * FROM `user_operation_log` LIMIT 100, 100
SELECT * FROM `user_operation_log` LIMIT 1000, 100
SELECT * FROM `user_operation_log` LIMIT 10000, 100
SELECT * FROM `user_operation_log` LIMIT 100000, 100
SELECT * FROM `user_operation_log` LIMIT 1000000, 100

从上面结果可以得出结束：偏移量越大，花费时间越长

SELECT * FROM `user_operation_log` LIMIT 100, 100
SELECT id, attr FROM `user_operation_log` LIMIT 100, 100

如何优化

既然我们经过上面一番的折腾，也得出了结论，针对上面两个问题：偏移大、数据量大，我们分别着手优化

优化偏移量大问题

采用子查询方式

我们可以先定位偏移位置的 id，然后再查询数据

SELECT * FROM `user_operation_log` LIMIT 1000000, 10SELECT id FROM `user_operation_log` LIMIT 1000000, 1SELECT * FROM `user_operation_log` WHERE id >= (SELECT id FROM `user_operation_log` LIMIT 1000000, 1) LIMIT 10

查询结果如下：

从上面结果得出结论：

• 第一条花费的时间最大，第三条比第一条稍微好点
• 子查询使用索引速度更快

缺点：只适用于id递增的情况

id非递增的情况可以使用以下写法，但这种缺点是分页查询只能放在子查询里面

注意：某些 mysql 版本不支持在 in 子句中使用 limit，所以采用了多个嵌套select

SELECT * FROM `user_operation_log` WHERE id IN (SELECT t.id FROM (SELECT id FROM `user_operation_log` LIMIT 1000000, 10) AS t)

采用 id 限定方式

这种方法要求更高些，id必须是连续递增，而且还得计算id的范围，然后使用 between，sql如下

SELECT * FROM `user_operation_log` WHERE id between 1000000 AND 1000100 LIMIT 100

SELECT * FROM `user_operation_log` WHERE id >= 1000000 LIMIT 100

查询结果如下：

从结果可以看出这种方式非常快

注意：这里的 LIMIT 是限制了条数，没有采用偏移量

优化数据量大问题

返回结果的数据量也会直接影响速度

SELECT * FROM `user_operation_log` LIMIT 1, 1000000

SELECT id FROM `user_operation_log` LIMIT 1, 1000000

SELECT id, user_id, ip, op_data, attr1, attr2, attr3, attr4, attr5, attr6, attr7, attr8, attr9, attr10, attr11, attr12 FROM `user_operation_log` LIMIT 1, 1000000

查询结果如下：

从结果可以看出减少不需要的列，查询效率也可以得到明显提升

第一条和第三条查询速度差不多，这时候你肯定会吐槽，那我还写那么多字段干啥呢，直接 * 不就完事了

注意本人的 MySQL 服务器和客户端是在_同一台机器_上，所以查询数据相差不多，有条件的同学可以测测客户端与MySQL分开

SELECT * 它不香吗？

在这里顺便补充一下为什么要禁止 SELECT *。难道简单无脑，它不香吗？

主要两点：

• 用 “SELECT * ” 数据库需要解析更多的对象、字段、权限、属性等相关内容，在 SQL 语句复杂，硬解析较多的情况下，会对数据库造成沉重的负担。
• 增大网络开销，* 有时会误带上如log、IconMD5之类的无用且大文本字段，数据传输size会几何增涨。特别是MySQL和应用程序不在同一台机器，这种开销非常明显。

结束

最后还是希望大家自己去实操一下，肯定还可以收获更多，欢迎留言！！

创建脚本我给你正好了，你还在等什么！！！

快乐分享，Java干货及时送达👇

一、概述

1.什么是多租户架构？

多租户架构是指在一个应用中支持多个租户（Tenant）同时访问，每个租户拥有独立的资源和数据，并且彼此之间完全隔离。通俗来说，多租户就是把一个应用按照客户的需求“分割”成多个独立的实例，每个实例互不干扰。

2. 多租户架构的优势

• 更好地满足不同租户的个性化需求。
• 可以降低运维成本，减少硬件、网络等基础设施的投入。
• 节约开发成本，通过复用代码，快速上线新的租户实例。
• 增强了系统的可扩展性和可伸缩性，支持水平扩展，每个租户的数据和资源均可管理和控制。

3. 实现多租户架构的技术选择

对于实现多租户架构技术不是最重要的最重要的是正确的架构思路。但是选择正确的技术可以更快地实现多租户架构。

二、设计思路

1. 架构选型

基于Java开发多租户应用推荐使用Spring Boot和Spring Cloud。Spring Boot能快速搭建应用并提供许多成熟的插件。Spring Cloud则提供了许多实现微服务架构的工具和组件。

1.1 Spring Boot

使用Spring Boot可以简化项目的搭建过程自动配置许多常见的第三方库和组件，减少了开发人员的工作量。

@RestController
public class TenantController {

    @GetMapping("/hello")
    public String hello(@RequestHeader("tenant-id") String tenantId) {
        return "Hello, " + tenantId;
    }
}

1.2 Spring Cloud

在架构多租户的系统时Spring Cloud会更加有用。Spring Cloud提供了一些成熟的解决方案，如Eureka、Zookeeper、Consul等，以实现服务发现、负载均衡等微服务功能。

2. 数据库设计

在多租户环境中数据库必须为每个租户分别存储数据并确保数据隔离。我们通常使用以下两种方式实现：

• 多个租户共享相同的数据库，每个表中都包含tenant_id这一列，用于区分不同租户的数据。
• 为每个租户创建单独的数据库，每个数据库内的表结构相同，但数据相互隔离。

3. 应用多租户部署

为了实现多租户在应用部署时我们需要考虑以下两个问题。

3.1 应用隔离

在多租户环境中不同租户需要访问不同的资源，因此需要进行应用隔离。可以通过构建独立的容器或虚拟机、使用命名空间等方式实现。Docker就是一种非常流行的隔离容器技术。

3.2 应用配置

由于每个租户都有自己的配置需求因此需要为每个租户分别设置应用配置信息，例如端口号、SSL证书等等。这些配置可以存储在数据库中，也可以存储在云配置中心中。

4. 租户管理

在多租户系统中需要能够管理不同租户的数据和资源，同时需要为每个租户分配相应的权限。解决方案通常包括以下两部分。

4.1 租户信息维护

租户信息的维护包括添加、修改、删除、查询等操作，要求能够根据租户名称或租户ID快速查找对应的租户信息。

CREATE TABLE tenant (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(255),
    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

4.2 租户权限控制

在多租户应用中必须为每个租户分别设置对系统资源的访问权限。例如，A租户和B租户不能访问彼此的数据。

@EnableGlobalMethodSecurity(prePostEnabled = true)
@Configuration
public class SecurityConfig extends WebSecurityConfigurerAdapter {

    @Override
    protected void configure(HttpSecurity http) throws Exception {
        http.authorizeRequests()
                .antMatchers("/api/tenant/**").hasRole("ADMIN")
                .anyRequest().authenticated()
                .and()
                .formLogin();
    }

    @Autowired
    public void configureGlobal(AuthenticationManagerBuilder auth) throws Exception {
        auth.userDetailsService(userDetailsService())
                .passwordEncoder(new BCryptPasswordEncoder())
                .and()
                .inMemoryAuthentication()
                .withUser("admin")
                .password(new BCryptPasswordEncoder().encode("123456"))
                .roles("ADMIN");
    }
}

三、技术实现

1. Spring Boot中的多租户实现

在Spring Boot中可以通过多数据源和动态路由来实现多租户机制。

1.1 多数据源实现

多数据源是指为不同的租户配置不同的数据源，使得每个租户都可以访问自己的独立数据。具体实现方法如下：

@Configuration
public class DataSourceConfig {
    @Bean(name = "dataSourceA")
    @ConfigurationProperties(prefix = "spring.datasource.a")
    public DataSource dataSourceA() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "dataSourceB")
    @ConfigurationProperties(prefix = "spring.datasource.b")
    public DataSource dataSourceB() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "dataSourceC")
    @ConfigurationProperties(prefix = "spring.datasource.c")
    public DataSource dataSourceC() {
        return DataSourceBuilder.create().build();
    }
}

以上代码是配置了三个数据源分别对应三个租户。然后在使用时，可以使用注解标记需要连接的数据源。

@Service
public class ProductService {
    @Autowired
    @Qualifier("dataSourceA")
    private DataSource dataSource;

    // ...
}

1.2 动态路由实现

动态路由是指根据请求的URL或参数动态地切换到对应租户的数据源。具体实现如下：

public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return TenantContextHolder.getTenantId();
    }
}

@Configuration
public class DataSourceConfig {
    @Bean(name = "dataSource")
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource dataSource() {
        return DataSourceBuilder.create().type(DynamicDataSource.class).build();
    }
}

以上是动态路由的核心代码DynamicDataSource继承自AbstractRoutingDataSource，通过determineCurrentLookupKey()方法动态获得租户ID，然后切换到对应的数据源。关注公众号：码猿技术专栏，回复关键词：1111 获取阿里内部Java性能调优手册！

2. Spring Cloud中的多租户实现

在Spring Cloud中可以通过服务注册与发现、配置中心、负载均衡等方式实现多租户机制。

2.1 服务注册与发现

使用Spring Cloud中的Eureka实现服务注册与发现。每个租户的服务都在注册中心以不同的应用名称进行注册，客户端可以通过服务名称来访问对应租户的服务。

2.2 配置中心

使用Spring Cloud Config作为配置中心。配置文件以租户ID进行区分，客户端通过读取对应租户的配置文件来获取配置信息。

2.3 负载均衡

使用Spring Cloud Ribbon作为负载均衡器。根据请求的URL或参数选择对应租户的服务实例进行请求转发。

2.4 API

在API网关层面实现多租户机制根据请求的URL或参数判断所属租户，并转发到对应租户的服务实例。

四、 应用场景

1. 私有云环境

私有云环境指的是由企业自行搭建的云环境，不对外提供服务，主要应用于企业内部的数据存储、管理、共享和安全控制。相较于公有云，私有云的优点在于可以更好地保护企业核心数据，同时也能够满足企业对于数据安全性和可控性的要求。

2. 公有云环境

公有云环境指的是由云服务商搭建并对外提供服务的云环境，用户可以根据需要购买相应的云服务，如云存储、云计算、云数据库等。相较于私有云，公有云的优点在于具有成本低廉、弹性伸缩、全球化部署等特点，能够更好地满足企业快速发展的需求。

3. 企业级应用

企业级应用是指面向企业客户的应用程序，主要包括ERP、CRM、OA等一系列应用系统。这类应用的特点在于功能强大、流程复杂、数据量大，需要满足企业的高效率、高可靠性、高安全性和易维护性等要求。在云计算环境下，企业可以将这些应用部署在私有云或公有云上，减少了硬件设备的投入和维护成本，提高了管理效率。

五、实现步骤

1. 搭建Spring Boot和Spring Cloud环境

首先需要在Maven项目中引入以下依赖：

dependency>
    groupId>org.springframework.bootgroupId>
    artifactId>spring-boot-starter-webartifactId>
dependency>


dependency>
    groupId>org.springframework.cloudgroupId>
    artifactId>spring-cloud-dependenciesartifactId>
    version>2020.0.3version>
    type>pomtype>
    scope>importscope>
dependency>

然后需要在application.yml中配置相应的参数，如下所示：

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/appdb?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
    username: root
    password: 123456

mybatis:
  type-aliases-package: com.example.demo.model
  mapper-locations: classpath:mapper/*.xml

server:
  port: 8080

eureka:
  client:
    serviceUrl:
      defaultZone: http://localhost:8761/eureka/

management:
  endpoints:
    web:
      exposure:
        include: "*"

其中datasource.url为数据库连接的URL，username和password为数据库连接的账号和密码；server.port为Spring Boot应用启动的端口；eureka.client.serviceUrl.defaultZone为Eureka服务注册中心的URL。

Java指南：java-family.cn

2. 修改数据库设计

接下来需要对数据库进行相应的修改，以支持多租户部署。具体来说，我们需要在数据库中添加一个与租户相关的字段，以便在应用中区分不同的租户。

3. 实现应用多租户部署

接着需要在代码中实现应用的多租户部署功能。具体来说，我们需要为每个租户实例化对应的Spring Bean，并根据租户ID将请求路由到相应的Bean中去处理。

以下是一个简单的实现示例：

@Configuration
public class MultiTenantConfig {
 
    // 提供对应租户的数据源
    @Bean
    public DataSource dataSource(TenantRegistry tenantRegistry) {
        return new TenantAwareDataSource(tenantRegistry);
    }
 
    // 多租户Session工厂
    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(DataSource dataSource)
            throws Exception {
        SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        return sessionFactory.getObject();
    }
 
    // 动态切换租户
    @Bean
    public MultiTenantInterceptor multiTenantInterceptor(TenantResolver tenantResolver) {
        MultiTenantInterceptor interceptor = new MultiTenantInterceptor();
        interceptor.setTenantResolver(tenantResolver);
        return interceptor;
    }
 
    // 注册拦截器
    @Override
    public void addInterceptors(InterceptorRegistry registry) {
        registry.addInterceptor(multiTenantInterceptor());
    }
 
    // 注册租户信息
    @Bean
    public TenantRegistry tenantRegistry() {
        return new TenantRegistryImpl();
    }
     
    // 解析租户ID
    @Bean
    public TenantResolver tenantResolver() {
        return new HeaderTenantResolver();
    }
 
}

其中MultiTenantConfig是多租户部署的核心配置类，它提供了对应租户数据源、多租户Session工厂、动态切换租户等功能。

4. 实现租户管理

最后需要实现一个租户管理的功能，以便在系统中管理不同的租户。具体来说，我们可以使用Spring Cloud的服务注册与发现组件Eureka来注册每个租户的实例，并在管理界面中进行相应的操作。另外，我们还需要为每个租户提供一个独立的数据库，以保证数据隔离性。

六、小结回顾

本文详细介绍了如何使用Spring Boot和Spring Cloud实现一个支持多租户部署的应用。主要包括搭建Spring Boot和Spring Cloud环境、修改数据库设计、实现应用多租户部署、实现租户管理等方面。

应用场景主要包括SaaS应用、多租户云服务等。优劣势主要体现在提升了应用的可扩展性和可维护性，但也增加了部署和管理的复杂度。未来的改进方向可以考虑进一步提升多租户管理的自动化程度，减少人工干预和错误率。

来源：blog.csdn.net/u010349629/article

点击关注公众号，Java干货及时送达👇

来源：https://my.oschina.net/u/4090830/blog/5559454

1 背景

作为在后端圈开车的多年老司机，是不是经常听到过，“mysql 单表最好不要超过 2000w”,“单表超过 2000w 就要考虑数据迁移了”，“你这个表数据都马上要到 2000w 了，难怪查询速度慢”

这些名言民语就和 “群里只讨论技术，不开车，开车速度不要超过 120 码，否则自动踢群”，只听过，没试过，哈哈。

下面我们就把车速踩到底，干到 180 码试试…….

2 实验

某一个值去控制增加的数据量即可。insert into person(id, person_id, person_name, gmt_create, gmt_modified)select @i:=@i+1,  left(rand()*10,10) as person_id,  concat(‘user_’,@i%2048),  date_add(gmt_create,interval + @i*cast(rand()*100 as signed) SECOND), date_add(date_add(gmt_modified,interval +@i*cast(rand()*100 as signed) SECOND), interval + cast(rand()*1000000 as signed) SECOND)from person;此处需要注意的是，也许你在执行到近 800w 或者 1000w 数据的时候，会报错：The total number of locks exceeds the lock table size，这是由于你的临时表内存设置的不够大，只需要扩大一下设置参数即可。SET GLOBAL tmp_table_size =512*1024*1024; （512M）SET global innodb_buffer_pool_size= 1*1024*1024*1024 (1G);先来看一组测试数据，这组数据是在 mysql8.0 的版本，并且是在我本机上，由于本机还跑着 idea , 浏览器等各种工具，所以并不是机器配置就是用于数据库配置，所以测试数据只限于参考。” linktype=”text” imgurl=”” imgdata=”null” data-itemshowtype=”0″ tab=”innerlink” style=”color: rgb(58, 58, 58);” data-linktype=”2″>实验一把看看…

某一个值去控制增加的数据量即可。insert into person(id, person_id, person_name, gmt_create, gmt_modified)select @i:=@i+1,  left(rand()*10,10) as person_id,  concat(‘user_’,@i%2048),  date_add(gmt_create,interval + @i*cast(rand()*100 as signed) SECOND), date_add(date_add(gmt_modified,interval +@i*cast(rand()*100 as signed) SECOND), interval + cast(rand()*1000000 as signed) SECOND)from person;此处需要注意的是，也许你在执行到近 800w 或者 1000w 数据的时候，会报错：The total number of locks exceeds the lock table size，这是由于你的临时表内存设置的不够大，只需要扩大一下设置参数即可。SET GLOBAL tmp_table_size =512*1024*1024; （512M）SET global innodb_buffer_pool_size= 1*1024*1024*1024 (1G);先来看一组测试数据，这组数据是在 mysql8.0 的版本，并且是在我本机上，由于本机还跑着 idea , 浏览器等各种工具，所以并不是机器配置就是用于数据库配置，所以测试数据只限于参考。” linktype=”text” imgurl=”” imgdata=”null” data-itemshowtype=”0″ tab=”innerlink” data-linktype=”2″>建一张表：

 某一个值去控制增加的数据量即可。insert into person(id, person_id, person_name, gmt_create, gmt_modified)select @i:=@i+1,  left(rand()*10,10) as person_id,  concat('user_',@i%2048),  date_add(gmt_create,interval + @i*cast(rand()*100 as signed) SECOND), date_add(date_add(gmt_modified,interval +@i*cast(rand()*100 as signed) SECOND), interval + cast(rand()*1000000 as signed) SECOND)from person;此处需要注意的是，也许你在执行到近 800w 或者 1000w 数据的时候，会报错：The total number of locks exceeds the lock table size，这是由于你的临时表内存设置的不够大，只需要扩大一下设置参数即可。SET GLOBAL tmp_table_size =512*1024*1024; （512M）SET global innodb_buffer_pool_size= 1*1024*1024*1024 (1G);先来看一组测试数据，这组数据是在 mysql8.0 的版本，并且是在我本机上，由于本机还跑着 idea , 浏览器等各种工具，所以并不是机器配置就是用于数据库配置，所以测试数据只限于参考。" linktype="text" imgurl="" imgdata="null" data-itemshowtype="0" tab="innerlink" data-linktype="2">CREATE TABLE person(
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY comment '主键',
  person_id tinyint not null comment '用户id',
  person_name VARCHAR(200) comment '用户名称',
  gmt_create datetime comment '创建时间',
  gmt_modified datetime comment '修改时间'
) comment '人员信息表';

插入一条数据：

 某一个值去控制增加的数据量即可。insert into person(id, person_id, person_name, gmt_create, gmt_modified)select @i:=@i+1,  left(rand()*10,10) as person_id,  concat('user_',@i%2048),  date_add(gmt_create,interval + @i*cast(rand()*100 as signed) SECOND), date_add(date_add(gmt_modified,interval +@i*cast(rand()*100 as signed) SECOND), interval + cast(rand()*1000000 as signed) SECOND)from person;此处需要注意的是，也许你在执行到近 800w 或者 1000w 数据的时候，会报错：The total number of locks exceeds the lock table size，这是由于你的临时表内存设置的不够大，只需要扩大一下设置参数即可。SET GLOBAL tmp_table_size =512*1024*1024; （512M）SET global innodb_buffer_pool_size= 1*1024*1024*1024 (1G);先来看一组测试数据，这组数据是在 mysql8.0 的版本，并且是在我本机上，由于本机还跑着 idea , 浏览器等各种工具，所以并不是机器配置就是用于数据库配置，所以测试数据只限于参考。" linktype="text" imgurl="" imgdata="null" data-itemshowtype="0" tab="innerlink" data-linktype="2">insert into person values(1,1,'user_1', NOW(), now());

利用 mysql 伪列 rownum 设置伪列起始点为 1

 某一个值去控制增加的数据量即可。insert into person(id, person_id, person_name, gmt_create, gmt_modified)select @i:=@i+1,  left(rand()*10,10) as person_id,  concat('user_',@i%2048),  date_add(gmt_create,interval + @i*cast(rand()*100 as signed) SECOND), date_add(date_add(gmt_modified,interval +@i*cast(rand()*100 as signed) SECOND), interval + cast(rand()*1000000 as signed) SECOND)from person;此处需要注意的是，也许你在执行到近 800w 或者 1000w 数据的时候，会报错：The total number of locks exceeds the lock table size，这是由于你的临时表内存设置的不够大，只需要扩大一下设置参数即可。SET GLOBAL tmp_table_size =512*1024*1024; （512M）SET global innodb_buffer_pool_size= 1*1024*1024*1024 (1G);先来看一组测试数据，这组数据是在 mysql8.0 的版本，并且是在我本机上，由于本机还跑着 idea , 浏览器等各种工具，所以并不是机器配置就是用于数据库配置，所以测试数据只限于参考。" linktype="text" imgurl="" imgdata="null" data-itemshowtype="0" tab="innerlink" data-linktype="2">select (@i:=@i+1) as rownum, person_name 
from person, (select @i:=100) as init;

set @i=1;

某一个值去控制增加的数据量即可。insert into person(id, person_id, person_name, gmt_create, gmt_modified)select @i:=@i+1,  left(rand()*10,10) as person_id,  concat(‘user_’,@i%2048),  date_add(gmt_create,interval + @i*cast(rand()*100 as signed) SECOND), date_add(date_add(gmt_modified,interval +@i*cast(rand()*100 as signed) SECOND), interval + cast(rand()*1000000 as signed) SECOND)from person;此处需要注意的是，也许你在执行到近 800w 或者 1000w 数据的时候，会报错：The total number of locks exceeds the lock table size，这是由于你的临时表内存设置的不够大，只需要扩大一下设置参数即可。SET GLOBAL tmp_table_size =512*1024*1024; （512M）SET global innodb_buffer_pool_size= 1*1024*1024*1024 (1G);先来看一组测试数据，这组数据是在 mysql8.0 的版本，并且是在我本机上，由于本机还跑着 idea , 浏览器等各种工具，所以并不是机器配置就是用于数据库配置，所以测试数据只限于参考。” linktype=”text” imgurl=”” imgdata=”null” data-itemshowtype=”0″ tab=”innerlink” style=”color: rgb(58, 58, 58);” data-linktype=”2″>运行下面的 sql，连续执行 20 次，就是 2 的 20 次方约等于 100w 的数据；执行 23 次就是 2 的 23 次方约等于 800w , 如此下去即可实现千万测试数据的插入，如果不想翻倍翻倍的增加数据，而是想少量，少量的增加，有个技巧，就是在 SQL 的后面增加 where 条件，如 id > 某一个值去控制增加的数据量即可。

 某一个值去控制增加的数据量即可。insert into person(id, person_id, person_name, gmt_create, gmt_modified)select @i:=@i+1,  left(rand()*10,10) as person_id,  concat('user_',@i%2048),  date_add(gmt_create,interval + @i*cast(rand()*100 as signed) SECOND), date_add(date_add(gmt_modified,interval +@i*cast(rand()*100 as signed) SECOND), interval + cast(rand()*1000000 as signed) SECOND)from person;此处需要注意的是，也许你在执行到近 800w 或者 1000w 数据的时候，会报错：The total number of locks exceeds the lock table size，这是由于你的临时表内存设置的不够大，只需要扩大一下设置参数即可。SET GLOBAL tmp_table_size =512*1024*1024; （512M）SET global innodb_buffer_pool_size= 1*1024*1024*1024 (1G);先来看一组测试数据，这组数据是在 mysql8.0 的版本，并且是在我本机上，由于本机还跑着 idea , 浏览器等各种工具，所以并不是机器配置就是用于数据库配置，所以测试数据只限于参考。" linktype="text" imgurl="" imgdata="null" data-itemshowtype="0" tab="innerlink" data-linktype="2">insert into person(id, person_id, person_name, gmt_create, gmt_modified)
select @i:=@i+1,
  left(rand()*10,10) as person_id,
  concat('user_',@i%2048),
  date_add(gmt_create,interval + @i*cast(rand()*100 as signed) SECOND),
 date_add(date_add(gmt_modified,interval +@i*cast(rand()*100 as signed) SECOND), interval + cast(rand()*1000000 as signed) SECOND)
from person;

某一个值去控制增加的数据量即可。insert into person(id, person_id, person_name, gmt_create, gmt_modified)select @i:=@i+1,  left(rand()*10,10) as person_id,  concat(‘user_’,@i%2048),  date_add(gmt_create,interval + @i*cast(rand()*100 as signed) SECOND), date_add(date_add(gmt_modified,interval +@i*cast(rand()*100 as signed) SECOND), interval + cast(rand()*1000000 as signed) SECOND)from person;此处需要注意的是，也许你在执行到近 800w 或者 1000w 数据的时候，会报错：The total number of locks exceeds the lock table size，这是由于你的临时表内存设置的不够大，只需要扩大一下设置参数即可。SET GLOBAL tmp_table_size =512*1024*1024; （512M）SET global innodb_buffer_pool_size= 1*1024*1024*1024 (1G);先来看一组测试数据，这组数据是在 mysql8.0 的版本，并且是在我本机上，由于本机还跑着 idea , 浏览器等各种工具，所以并不是机器配置就是用于数据库配置，所以测试数据只限于参考。” linktype=”text” imgurl=”” imgdata=”null” data-itemshowtype=”0″ tab=”innerlink” style=”color: rgb(58, 58, 58);” data-linktype=”2″>此处需要注意的是，也许你在执行到近 800w 或者 1000w 数据的时候，会报错：The total number of locks exceeds the lock table size，这是由于你的临时表内存设置的不够大，只需要扩大一下设置参数即可。

 某一个值去控制增加的数据量即可。insert into person(id, person_id, person_name, gmt_create, gmt_modified)select @i:=@i+1,  left(rand()*10,10) as person_id,  concat('user_',@i%2048),  date_add(gmt_create,interval + @i*cast(rand()*100 as signed) SECOND), date_add(date_add(gmt_modified,interval +@i*cast(rand()*100 as signed) SECOND), interval + cast(rand()*1000000 as signed) SECOND)from person;此处需要注意的是，也许你在执行到近 800w 或者 1000w 数据的时候，会报错：The total number of locks exceeds the lock table size，这是由于你的临时表内存设置的不够大，只需要扩大一下设置参数即可。SET GLOBAL tmp_table_size =512*1024*1024; （512M）SET global innodb_buffer_pool_size= 1*1024*1024*1024 (1G);先来看一组测试数据，这组数据是在 mysql8.0 的版本，并且是在我本机上，由于本机还跑着 idea , 浏览器等各种工具，所以并不是机器配置就是用于数据库配置，所以测试数据只限于参考。" linktype="text" imgurl="" imgdata="null" data-itemshowtype="0" tab="innerlink" data-linktype="2">SET GLOBAL tmp_table_size =512*1024*1024; （512M）
SET global innodb_buffer_pool_size= 1*1024*1024*1024 (1G);

某一个值去控制增加的数据量即可。insert into person(id, person_id, person_name, gmt_create, gmt_modified)select @i:=@i+1,  left(rand()*10,10) as person_id,  concat(‘user_’,@i%2048),  date_add(gmt_create,interval + @i*cast(rand()*100 as signed) SECOND), date_add(date_add(gmt_modified,interval +@i*cast(rand()*100 as signed) SECOND), interval + cast(rand()*1000000 as signed) SECOND)from person;此处需要注意的是，也许你在执行到近 800w 或者 1000w 数据的时候，会报错：The total number of locks exceeds the lock table size，这是由于你的临时表内存设置的不够大，只需要扩大一下设置参数即可。SET GLOBAL tmp_table_size =512*1024*1024; （512M）SET global innodb_buffer_pool_size= 1*1024*1024*1024 (1G);先来看一组测试数据，这组数据是在 mysql8.0 的版本，并且是在我本机上，由于本机还跑着 idea , 浏览器等各种工具，所以并不是机器配置就是用于数据库配置，所以测试数据只限于参考。” linktype=”text” imgurl=”” imgdata=”null” data-itemshowtype=”0″ tab=”innerlink” style=”color: rgb(58, 58, 58);” data-linktype=”2″>先来看一组测试数据，这组数据是在 mysql8.0 的版本，并且是在我本机上，由于本机还跑着 idea , 浏览器等各种工具，所以并不是机器配置就是用于数据库配置，所以测试数据只限于参考。

看到这组数据似乎好像真的和标题对应，当数据达到 2000w 以后，查询时长急剧上升；难道这就是铁律吗？那下面我们就来看看这个建议值 2kw 是怎么来的？

3 单表数量限制

首先我们先想想数据库单表行数最大多大？

CREATE TABLE person(
  id int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY comment '主键',
  person_id tinyint not null comment '用户id',
  person_name VARCHAR(200) comment '用户名称',
  gmt_create datetime comment '创建时间',
  gmt_modified datetime comment '修改时间'
) comment '人员信息表';

看看上面的建表 sql，id 是主键，本身就是唯一的，也就是说主键的大小可以限制表的上限，如果主键声明 int 大小，也就是 32 位，那么支持 2^32-1 ~~21 亿；如果是 bigint，那就是 2^62-1 ？（36893488147419103232），难以想象这个的多大了，一般还没有到这个限制之前，可能数据库已经爆满了！！

有人统计过，如果建表的时候，自增字段选择无符号的 bigint , 那么自增长最大值是 18446744073709551615，按照一秒新增一条记录的速度，大约什么时候能用完？

4 表空间

下面我们再来看看索引的结构，对了，我们下面讲内容都是基于 Innodb 引擎的，大家都知道 Innodb 的索引内部用的是 B+ 树

这张表数据，在硬盘上存储也是类似如此的，它实际是放在一个叫 person.ibd （innodb data）的文件中，也叫做表空间；虽然数据表中，他们看起来是一条连着一条，但是实际上在文件中它被分成很多小份的数据页，而且每一份都是 16K。大概就像下面这样，当然这只是我们抽象出来的，在表空间中还有段、区、组等很多概念，但是我们需要跳出来看。

5 页的数据结构

因为每个页只有 16K 的大小，但是如果数据很多，那一页肯定就放不下这些数据，那数据肯定就会被分到其他的页中，所以为了把这些页关联起来，肯定就会有记录前后页地址，方便找到对应页；同时每页都是唯一的，那就会需要有一个唯一标志来标记页，就是页号；页中会记录数据所以会存在读写操作，读写操作会存在中断或者其他异常导致数据不全等，那就会需要有校验机制，所以里面还有会校验码，而读操作最重要的就是效率问题，如果按照记录一个个进行遍历，那肯定是很费劲的，所以这里面还会为数据生成对应的页目录（Page Directory）; 所以实际页的内部结构像是下面这样的。

从图中可以看出，一个 InnoDB 数据页的存储空间大致被划分成了 7 个部分，有的部分占用的字节数是确定的，有的部分占用的字节数是不确定的。

在页的 7 个组成部分中，我们自己存储的记录会按照我们指定的行格式存储到 User Records 部分。

但是在一开始生成页的时候，其实并没有 User Records 这个部分，每当我们插入一条记录，都会从 Free Space 部分，也就是尚未使用的存储空间中申请一个记录大小的空间划分到 User Records 部分，当 Free Space 部分的空间全部被 User Records 部分替代掉之后，也就意味着这个页使用完了，如果还有新的记录插入的话，就需要去申请新的页了。这个过程的图示如下。

那下面就来说说，数据的查找过程，假如我们需要查找一条记录，我们可以把表空间中的每一页都加载到内存中，然后对记录挨个判断是不是我们想要的，在数据量小的时候，没啥问题，内存也可以撑；但是现实就是这么残酷，不会给你这个局面；为了解决这问题，mysql 中就有了索引的概念；大家都知道索引能够加快数据的查询，那到底是怎么个回事呢？下面我就来看看。

6 索引的数据结构

在 mysql 中索引的数据结构和刚刚描述的页几乎是一模一样的，而且大小也是 16K, 但是在索引页中记录的是页 (数据页，索引页) 的最小主键 id 和页号，以及在索引页中增加了层级的信息，从 0 开始往上算，所以页与页之间就有了上下层级的概念。

看到这个图之后，是不是有点似曾相似的感觉，是不是像一棵二叉树啊，对，没错！它就是一棵树，只不过我们在这里只是简单画了三个节点，2 层结构的而已，如果数据多了，可能就会扩展到 3 层的树，这个就是我们常说的 B+ 树，最下面那一层的 page level =0, 也就是叶子节点，其余都是非叶子节点。

看上图中，我们是单拿一个节点来看，首先它是一个非叶子节点（索引页），在它的内容区中有 id 和 页号地址两部分，这个 id 是对应页中记录的最小记录 id 值，页号地址是指向对应页的指针；而数据页与此几乎大同小异，区别在于数据页记录的是真实的行数据而不是页地址，而且 id 的也是顺序的。

7 单表建议值

下面我们就以 3 层，2 分叉（实际中是 M 分叉）的图例来说明一下查找一个行数据的过程。

比如说我们需要查找一个 id=6 的行数据，因为在非叶子节点中存放的是页号和该页最小的 id，所以我们从顶层开始对比，首先看页号 10 中的目录，有 [id=1, 页号 = 20],[id=5, 页号 = 30], 说明左侧节点最小 id 为 1，右侧节点最小 id 是 5；6>5, 那按照二分法查找的规则，肯定就往右侧节点继续查找，找到页号 30 的节点后，发现这个节点还有子节点（非叶子节点），那就继续比对，同理，6>5&&6

从上述的过程中发现，我们为了查找 id=6 的数据，总共查询了三个页，如果三个页都在磁盘中（未提前加载至内存），那么最多需要经历三次的磁盘 IO。需要注意的是，图中的页号只是个示例，实际情况下并不是连续的，在磁盘中存储也不一定是顺序的。

至此，我们大概已经了解了表的数据是怎么个结构了，也大概知道查询数据是个怎么的过程了，这样我们也就能大概估算这样的结构能存放多少数据了。

从上面的图解我们知道 B+ 数的叶子节点才是存在数据的，而非叶子节点是用来存放索引数据的。

所以，同样一个 16K 的页，非叶子节点里的每条数据都指向新的页，而新的页有两种可能

• 如果是叶子节点，那么里面就是一行行的数据
• 如果是非叶子节点的话，那么就会继续指向新的页

假设

• 非叶子节点内指向其他页的数量为 x
• 叶子节点内能容纳的数据行数为 y
• B+ 数的层数为 z

如下图中所示
Total =x^(z-1) *y 也就是说总数会等于 x 的 z-1 次方 与 Y 的乘积。

X =？

在文章的开头已经介绍了页的结构，索引也也不例外，都会有 File Header (38 byte)、Page Header (56 Byte)、Infimum + Supermum（26 byte）、File Trailer（8byte）, 再加上页目录，大概 1k 左右，我们就当做它就是 1K, 那整个页的大小是 16K, 剩下 15k 用于存数据，在索引页中主要记录的是主键与页号，主键我们假设是 Bigint (8 byte), 而页号也是固定的（4Byte）, 那么索引页中的一条数据也就是 12byte; 所以 x=15*1024/12≈1280 行。

Y=？

叶子节点和非叶子节点的结构是一样的，同理，能放数据的空间也是 15k；但是叶子节点中存放的是真正的行数据，这个影响的因素就会多很多，比如，字段的类型，字段的数量；每行数据占用空间越大，页中所放的行数量就会越少；这边我们暂时按一条行数据 1k 来算，那一页就能存下 15 条，Y≈15。

算到这边了，是不是心里已经有谱了啊
根据上述的公式，Total =x^(z-1) y，已知 x=1280,y=15
假设 B+ 树是两层，那就是 Z =2， Total = （1280 ^1 ）15 = 19200
假设 B+ 树是三层，那就是 Z =3， Total = （1280 ^2） *15 = 24576000 （约 2.45kw）

哎呀，妈呀！这不是正好就是文章开头说的最大行数建议值 2000w 嘛！对的，一般 B+ 数的层级最多也就是 3 层，你试想一下，如果是 4 层，除了查询的时候磁盘 IO 次数会增加，而且这个 Total 值会是多少，大概应该是 3 百多亿吧，也不太合理，所以，3 层应该是比较合理的一个值。

到这里难道就完了？

不
我们刚刚在说 Y 的值时候假设的是 1K ，那比如我实际当行的数据占用空间不是 1K , 而是 5K, 那么单个数据页最多只能放下 3 条数据
同样，还是按照 Z=3 的值来计算，那 Total = （1280 ^2） *3 = 4915200 （近 500w）

所以，在保持相同的层级（相似查询性能）的情况下，在行数据大小不同的情况下，其实这个最大建议值也是不同的，而且影响查询性能的还有很多其他因素，比如，数据库版本，服务器配置，sql 的编写等等，MySQL 为了提高性能，会将表的索引装载到内存中。在 InnoDB buffer size 足够的情况下，其能完成全加载进内存，查询不会有问题。但是，当单表数据库到达某个量级的上限时，导致内存无法存储其索引，使得之后的 SQL 查询会产生磁盘 IO，从而导致性能下降，所以增加硬件配置（比如把内存当磁盘使），可能会带来立竿见影的性能提升哈。

8 总结

• Mysql 的表数据是以页的形式存放的，页在磁盘中不一定是连续的。
• 页的空间是 16K, 并不是所有的空间都是用来存放数据的，会有一些固定的信息，如，页头，页尾，页码，校验码等等。
• 在 B+ 树中，叶子节点和非叶子节点的数据结构是一样的，区别在于，叶子节点存放的是实际的行数据，而非叶子节点存放的是主键和页号。
• 索引结构不会影响单表最大行数，2kw 也只是推荐值，超过了这个值可能会导致 B + 树层级更高，影响查询性能。

参考资料：

• https://www.jianshu.com/p/cf5d381ef637
• https://www.modb.pro/db/139052
• 《MYSQL 内核：INNODB 存储引擎 卷 1》

测试环境：

MySQL版本：8.0

数据库表：T （主键id，唯一索引c，普通字段d）

如果你的业务设计依赖于自增主键的连续性，这个设计假设自增主键是连续的。但实际上，这样的假设是错的，因为自增主键不能保证连续递增。

一、自增值的属性特征：

1. 自增主键值是存储在哪的？

MySQL5.7版本

在 MySQL 5.7 及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为这个表当前的自增值。

MySQL8.0之后版本

在 MySQL 8.0 版本，将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值。

可以通过看表详情查看当前自增值，以及查看表参数详情AUTO_INCREMENT值(AUTO_INCREMENT就是当前数据表的自增值)

2. 自增主键值的修改机制？

在表t中，我定义了主键id为自增值，在插入一行数据的时候，自增值的行为如下：

1. 如果插入数据时 id 字段指定为 0、null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段；
2. 如果插入数据时 id 字段指定了具体的值，就直接使用语句里指定的值。

根据要插入的值和当前自增值的大小关系，自增值的变更结果也会有所不同。假设，某次要插入的值是 X，当前的自增值是 Y。

1. 如果 X
2. 如果 X≥Y，就需要把当前自增值修改为新的自增值。

二、新增语句自增主键是如何变化的：

我们执行以下SQL语句，来观察自增主键是如何进行变化的

insert into t values(null, 1, 1); 

流程图如下所示

流程步骤：

• AUTO_INCREMENT=1 （表示下一次插入数据时，如果需要自动生成自增值，会生成 id=1。）
• insert into t values(null, 1, 1) （执行器调用 InnoDB 引擎接口写入一行，传入的这一行的值是 (0,1,1)）
• get AUTO_INCREMENT=1 （InnoDB 发现用户没有指定自增 id 的值，获取表 t 当前的自增值 1 ）
• AUTO_INCREMENT=2 insert into t values(1, 1, 1) （将传入的行的值改成 (1,1,1)，并把自增值改为2）
• insert （1，1，1) 执行插入操作，至此流程结束

大家可以发现，在这个流程当中是先进行自增值的+1，在进行新增语句的执行的。大家可以发现这个操作并没有进行原子操作，如果SQL语句执行失败，那么自增是不是就不会连续了呢？

三、自增主键值不连续情况：（唯一主键冲突）

当我执行以下SQL语句时

insert into t values(null, 1, 1); 

第一次我们可以进行新增成功，根据自增值的修改机制。如果插入数据时 id 字段指定为 0、null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段；

当我们第二次在执行以下SQL语句时，就会出现错误。因为我们表中c字段是唯一索引，会出现Duplicate key error错误导致新增失败。

例如：

• AUTO_INCREMENT=2 （表示下一次插入数据时，如果需要自动生成自增值，会生成 id=2。）
• insert into t values(null, 1, 1) （执行器调用 InnoDB 引擎接口写入一行，传入的这一行的值是 (0,1,1)）
• get AUTO_INCREMENT=2 （InnoDB 发现用户没有指定自增 id 的值，获取表 t 当前的自增值 2 ）
• AUTO_INCREMENT=3 insert into t values(2, 1, 1) （将传入的行的值改成 (2,1,1)，并把自增值改为3）
• insert （2，1，1)  执行插入操作，由于已经存在 c=1 的记录，所以报 Duplicate key error，语句返回。

可以看到，这个表的自增值改成 3，是在真正执行插入数据的操作之前。这个语句真正执行的时候，因为碰到唯一键 c 冲突，所以 id=2 这一行并没有插入成功，但也没有将自增值再改回去。所以，在这之后，再插入新的数据行时，拿到的自增 id 就是 3。也就是说，出现了自增主键不连续的情况。

四、自增主键值不连续情况：（事务回滚）

其实事务回滚原理也和上面一样，都是因为异常导致新增失败，但是自增值没有进行回退。

五、自增主键值不连续情况：（批量插入）

批量插入数据的语句，MySQL 有一个批量申请自增 id 的策略：

1. 语句执行过程中，第一次申请自增 id，会分配 1 个；
2. 1 个用完以后，这个语句第二次申请自增 id，会分配 2 个；
3. 2 个用完以后，还是这个语句， 第三次申请自增 id，会分配 4 个；
4. 依此类推，同一个语句去申请自增 id，每次申请到的自增 id 个数都是上一次的两倍。

执行以下SQL语句（在表t中先新增了4条数据，在创建表tt把表t数据进行批量新增）

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);
create table tt like t;
insert into tt(c,d) select c,d from t;

insert into tt values(null, 5,5);

第一次申请到了 id=1，第二次被分配了 id=2 和 id=3， 第三次被分配到 id=4 到 id=7。当我们再执行 insert into t2 values(null, 5,5)，实际上插入的数据就是（8,5,5)，出现了自增主键不连续的情况。

六、自增主键值的优化

1.什么是自增锁

自增锁是一种比拟非凡的表级锁。并且在事务向蕴含了 AUTO_INCREMENT 列的表中新增数据时就会去持有自增锁，假如事务 A 正在做这个操作，如果另一个事务 B 尝试执行 INSERT语句，事务 B 会被阻塞住，直到事务 A 开释自增锁。

2.自增锁有哪些优化

在 MySQL 5.0 版本的时候，自增锁的范围是语句级别。也就是说，如果一个语句申请了一个表自增锁，这个锁会等语句执行结束以后才释放。显然，这样设计会影响并发度。在MySQL 5.1.22 版本引入了一个新策略，新增参数 innodb_autoinc_lock_mode，默认值是 1。

传统模式（Traditional）

这个参数的值被设置为 0 时，表示采用之前 MySQL 5.0 版本的策略，即语句执行结束后才释放锁；

传统模式他可以保证数据一致性，但是如果有多个事务并发的执行 INSERT 操作，AUTO-INC的存在会使得 MySQL 的性能略有降落，因为同时只能执行一条 INSERT 语句。

间断模式（Consecutive）

这个参数的值被设置为 1 时：普通 insert 语句，自增锁在申请之后就马上释放；类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；

间断模式他可以保证数据一致性，但是如果有多个事务并发的执行 INSERT 批量操作时，就会进行锁等待状态。如果我们业务插入数据量很大时，这个时候MySQL的性能就会大大下降。

穿插模式（Interleaved）

这个参数的值被设置为 2 时，所有的申请自增主键的动作都是申请后就释放锁。

穿插模式他没有进行任何的上锁设置。在一定情况下是保证了MySQL的性能，但是他无法保证数据的一致性。如果我们在穿插模式下进行主从复制时，如果你的binlog格式不是row格式，主从复制就会出现不一致。

七、MySQL8.0做了哪些优化

在MySQL8.0之后版本，已经默认设置为 innodb_autoinc_lock_mode=2 ， binlog_format=row.。这样更有利与我们在 insert … select 这种批量插入数据的场景时，既能提升并发性，又不会出现数据一致性问题。