MySQL分表案例

1. 案例实战 - 用户表水平拆分

1) 背景

一般面对这么一个几千万级的数据，刚开始可能都是把数据放在MySQL的一个单库单表里的，但是往往这么大量级的数据到了后期，会搞的数据库查询速度很慢，因为结合之前的知识大家知道，数据量级太大了，会导致表的索引很大，树的层级很高，进而导致搜索性能下降，而且能放内存缓存的数据页是比较少的。

所以说，往往我们都建议MySQL单表数据量不要超过1000万，最好是在500万以内，如果能控制在100万以内，那是最佳的选择了，基本单表100万以内的数据，性能上不会有太大的问题，前提是，只要你建好索引就行，其实保证MySQL高性能通常没什么特别高深的技巧，就是控制数据量不要太大，另外就是保证你的查询用上了索引，一般就没问题。

好，所以针对这个问题，我们就可以进行分库分表了，可以选择把这个用户大表拆分为比如100张表，那么此时几千万数据瞬间分散到100个表里去，类似user_001、user_002、user_100这样的100个表，每个表也就几十万数据而已。

其次，可以把这100个表分散到多台数据库服务器上去，此时要分散到几台服务器呢？你要考虑两个点，一个是数据量有多少个GB/TB，一个是针对用户中心的并发压力有多高。实际上一般互联网公司对用户中心的压力不会高的太离谱，因为一般不会有很多人同时注册/登录，或者是同时修改自己的个人信息，所以并发这块不是太大问题。

至于数据量层面的话，我可以给大家一个经验值，一般1亿行数据，大致在1GB到几个GB之间的范围，这个跟具体你一行数据有多少字段也有关系，大致大致就是这么个范围，所以说你几千万的用户数据，往多了说也就几个GB而已。

这点数据量，对于服务器的存储空间来说，完全没压力，不是问题。

2) 解决方案

所以综上所述，此时你完全可以给他分配两台数据库服务器，放两个库，然后100张表均匀分散在2台服务器上就可以了，分的时候需要指定一个字段来分，一般来说会指定userid，根据用户id进行hash后，对表进行取模，路由到一个表里去，这样可以让数据均匀分散。

到此就搞定了用户表的分库分表，你只要给系统加上数据库中间件技术，设置好路由规则，就可以轻松的对2个分库上的100张表进行增删改查的操作了。平时针对某个用户增删改查，直接对他的userid进行hash，然后对表取模，做一个路由，就知道到哪个表里去找这个用户的数据了。

但是这里可能会出现一些问题，一个是说，用户在登录的时候，可能不是根据userid登陆的，可能是根据username之类的用户名，手机号之类的来登录的，此时你又没有userid，怎么知道去哪个表里找这个用户的数据判断是否能登录呢？

关于这个问题，一般来说常规方案是建立一个索引映射表，就是说搞一个表结构为（username, userid）的索引映射表，把username和userid一一映射，然后针对username再做一次分库分表，把这个索引映射表可以拆分为比如100个表分散在两台服务器里。

然后用户登录的时候，就可以根据username先去索引映射表里查找对应的userid，比如对username进行hash然后取模路由到一个表里去，找到username对应的userid，接着根据userid进行hash再取模，然后路由到按照userid分库分表的一个表里去，找到用户的完整数据即可。

但是这种方式会把一次查询转化为两个表的两次查询，先查索引映射表，再根据userid去查具体的数据，性能上是有一定的损耗的，不过有时候为了解决分库分表的问题，也只能用这种类似的办法。

另外就是如果在公司运营团队里，有一个用户管理模块，需要对公司的用户按照手机号、住址、年龄、性别、职业等各种条件进行极为复杂的搜索，这怎么办呢？其实没太多的好办法，基本上就是要对你的用户数据表进行binlog监听，把你要搜索的所有字段同步到Elasticsearch里去，建立好搜索的索引。

然后你的运营系统就可以通过Elasticsearch去进行复杂的多条件搜索，ES是适合干这个事儿的，然后定位到一批userid，通过userid回到分库分表环境里去找出具体的用户数据，在页面上展示出来即可。
这就是一套比较常规和完整的分库分表的方案。

2. 案例实战 - 订单数据库设计

1) 背景

一般互联网公司的订单系统是如何做分库分表的，既然要聊订单系统的分库分表，那么就得先说说为什么订单需要分库分表，其实最关键的一点就是要分析一下订单系统的数据量，那么订单系统的数据量有多大？这个就得看具体公司的情况了。

比如说一个小型互联网公司，如果是涉及到电商交易的，那么肯定每天都会有一些订单进来的，那么比如小型互联网公司假设有500万的注册用户，每天日活的用户会有多少人？意思就是说，你500万的注册用户，并不是每个人每天都来光顾你这里的！

我们往多了说，即使按照28法则，你500万的注册用户，每天最多是20%的用户会过来光顾你这里，也就是会来访问你的APP/小程序/网站，也就是100万的日活用户，但是这个日活比例恐怕很多公司都达不到，所以一般靠谱点就算他是10%的用户每天会来光顾你，算下来就是平均每个注册用户10天会来光顾你一次，这就是50万的日活用户。

但是这50万的日活用户仅仅是来看看而已，那么有多少人会来买你的东西呢？这个购买比例可就更低了，基本上很可能这种小型互联网公司每天就做个1w订单，或者几万订单，这就已经相当的不错了，咱们就以保守点按1w订单来算吧。

那么也就是说，这个互联网公司的订单表每天新增数据大概是1w左右，每个月是新增30w数据，每年是新增360w数据。大家对这个数据量感觉如何？看着不大是吧，但是按照我们上次说的，一般建议单表控制在千万以内，尽量是100w到500w之间，如果控制在几十万是最好了！

所以说，分析下来，大家会发现，哪怕是个小互联网公司，居然订单数据量也不少！因为订单这种数据和用户数据是不同的，你用户数据一般不会增长过快，而且很快会达到一个天花板，就不会怎么再涨了，但是订单数据是每天都有增量的，他们的特点是不同的。

所以说这个订单表，即使你按一年360w数据增长来计算，最多3年就到千万级大表了，这个就绝对会导致你涉及订单的操作，速度挺慢的。我这里可以给大家分享两个我亲身体验过的订单这块的案例。

一个是我使用过的某社保类的APP，这个APP可以让你在上面下单自助缴纳五险一金，你每次自助缴纳，说白了就是下一个订单，把钱给他，他帮你缴纳五险一金，这个东西对于很多自由职业者是很有用的。

这个APP，很明显就是订单日积月累很多，而且一定是没有做任何的分表，导致每次对自己的订单进行查询的时候，基本都是秒级，每次打开订单页面都很慢，有时候甚至会达到两三秒的样子，这个体验就很差。

另外一个是我使用过的一个企业银行的APP，大家都知道，企业银行是可以允许财务提交打款申请，然后有人可以去审批的，但是有一个银行APP，很明显也是对这类申请和审批的数据表，没有做分库分表的处理，导致数据日积月累的增加，每次在申请和审批的查询界面都很慢，起码要卡1s以上的时间，这个体验也很不好。

2) 解决方案

所以说，基本上个这类订单表，哪怕是个小互联网公司，按分库分表几乎是必须得做的，那么怎么做呢？订单表，一般在拆分的时候，往往要考虑到三个维度，一个是必然要按照订单id为粒度去分库分表，也就是把订单id进行hash后，对表数量进行取模然后把订单数据均匀分散到100~1000个表里去，再把这些表分散在多台服务器上。

但是这里有个问题，另外两个维度是用户端和运营端，用户端，就是用户可能要查自己的订单，运营端就是公司可能要查所有订单，那么怎么解决这类问题呢？其实就跟上次的差不多，基本上针对用户端，你就需要按照（userid, orderid）这个表结构，去做一个索引映射表。

userid和orderid的一一对应映射关系要放在这个表里，然后针对userid为粒度去进行分库分表，也就是对userid进行hash后取模，然后把数据均匀分散在很多索引映射表里，再把表放在很多数据库里。

然后每次用户端拿出APP查询自己的订单，直接根据userid去hash然后取模路由到一个索引映射表，找到这个用户的orderid，这里当然可以做一个分页了，因为一般订单都是支持分页的，此时可以允许用于用户分页查询orderid，然后拿到一堆orderid了，再根据orderid去按照orderid粒度分库分表的表里提取订单完整数据。

至于运营端，一般都是要根据N多条件对订单进行搜索的，此时跟上次讲的一样，可以把订单数据的搜索条件都同步到ES里，然后用ES来进行复杂搜索，找出来一波orderid，再根据orderid去分库分表里找订单完整数据。

其实大家到最后会发现，分库分表的玩法基本都是这套思路，按业务id分库分表，建立索引映射表同时进行分库分表，数据同步到ES做复杂搜索，基本这套玩法就可以保证你的分库分表场景下，各种业务功能都可以支撑了。

3. 案例实战 - 跨库分页

关于分库分表后的跨库/跨表的分页问题，首先我们先来聊聊这个所谓的分页是个什么场景。那比如说还是说之前的那个订单的场景，假设用户现在要查询自己的订单，同时订单要求要支持分页，该怎么做？

其实按我们之前所说的，基本上你只要按照userid先去分库分表的（userid, orderid）索引映射表里查找到你的那些orderid，然后搞一个分页就可以了，对分页内的orderid，每个orderid都得去按orderid分库分表的数据里查找完整的订单数据，这就可以搞定分库分表环境的下分页问题了。

这仅仅是一个例子，告诉你的是，如果要在分库分表环境下搞分页，最好是保证你的一个主数据粒度（比如userid）是你的分库分表的粒度，你可以根据一个业务id路由到一个表找到他的全部数据，这就可以做分页了。

但是此时可能有人会提出一个疑问了，那如果说现在我想要对用户下的订单做分页，但是同时还能支持指定一些查询条件呢？对了，这其实也是很多APP里都支持的，就是对自己的订单查询，有的APP是支持指定一些条件的，甚至是排序规则，比如订单名称模糊搜索，或者是别的条件，比如说订单状态。

举个例子吧，比如说最经典的某个电商APP，大家平时都玩儿的一个，在我的订单界面，可以按照订单状态来搜索，分别是全部、待付款、待收货、已完成、已取消几个状态，同时就是对订单购买的商品标题进行模糊搜索。

那么此时你怎么玩儿分页呢？因为毕竟你的索引映射表里，只有（userid, orderid）啊！可是这又如何呢？你完全可以在这个索引映射表里加入更多的数据，比如（userid, orderid, order_status, product_description），加上订单所处的状态，以及商品的标题、副标题等文本。

然后你在对我的订单进行分页的时候，直接就可以根据userid去索引映射表里找到用户的所有订单，然后按照订单状态、商品描述文本模糊匹配去搜索，完了再分页，分页拿到的orderid，再去获取订单需要展示的数据，比如说订单里包含的商品列表，每个商品的缩略图、名称、价格以及所属店铺。

那如果是针对运营端的分页查询需求呢？这还用说？上次都提过了，数据直接进入ES里，通过ES就可以对多条件进行搜索同时再进行分页了，这很好搞定！

当然，网上是有人说过一些所谓的跨库的分页方案，比如说一定要针对跨多个库和多个表的数据搞查询和分页，那这种如果你一定要做，基本上只能是自己从各个库表拉数据到内存，自己内存里做筛选和分页了，或者是基于数据库中间件去做，那数据库中间件本质也是干这个，把各个库表的数据拉到内存做筛选和分页。

实际上我是绝对反对这种方案的，因为效率和性能极差，基本都是几秒级别的速度。

所以当你觉得似乎必须要跨库和表查询和分页的时候，我建议你，第一，你考虑一下是不是可以把你查询里按照某个主要的业务id进行分库分表建立一个索引映射表，第二是不是可以可以把这个查询里要的条件都放到索引映射表里去，第三，是不是可以通过ES来搞定这个需求。

尽可能还是按照上述思路去做分库分表下的分页，而不要去搞跨库/表的分页查询。

4. 案例实战 - 再次扩容

看看在自己的业务场景下，如果业务表搞成上千万数据的大表，此时各种查询性能如何，完了如何分表，按什么字段分，是否要建立索引映射表，跨库跨表的查询应该怎么做，选用什么数据库中间件，然后如何进行数据迁移，最后就是如何进行扩容。

这些细节最好大家是能够在自己负责的项目里去实践一下，那是效果最好的。

因此最后我们给大家谈一个话题，就是如果你分库分表了，比如搞了几个数据库服务器，每个服务器上部署了一个数据库实例，然后你的业务库拆分在各个服务器上，你的业务表拆分为几百上千个，每个服务器上都有一部分。

此时如果过了几年后，你每个表的数据量都增长到了一定水准，比如刚拆分的时候每个表才100w数据，结果过了几年，每个表都增长到了几百万数据，此时应该怎么办？还能怎么办！当然是把表进一步拆分，增加更多的表了！

完了增加更多的表之后还得把数据做迁移，更改系统的路由规则，极为的麻烦。

那大家觉得真的应该出现这种情况吗？其实完全不是，咱们应该从一开始，就对上述情况say no，也就是说，刚开始就完全可以多分一些表，比如你数据量有10亿级，那么你可以分为10000个表，每个表才10w数据，而且后续你计算好增量，可能10年，20年过后，单表数据才百万级。

那么此时是不是就不会出现上述情况了？

所以说，从一开始，你的表数量宁愿多一些，也别太少了，最好是计算一下数据增量，让自己永远不用增加更多的表。

其次，万一是过了几年后，你的每一台服务器上的存储空间要耗尽了呢？或者是写并发压力太大，每个服务器的并发压力都到瓶颈了呢？此时还用说么，当然要增加更多的数据库服务器了！但是增加服务器之后，那么你的表怎么办呢？

简单，此时你就得把你的表均匀分散迁移到新增加的数据库服务器上去，然后再修改一下系统里的路由规则就可以了，用新的路由规则保证你能正确的把数据路由到指定表以及指定库上去就没问题了。

因此关于数据库扩容这块，虽然网上有很多方案，但是我们建议的就是，刚开始拆分，表数量可以多一些，避免后续要增加表。然后数据库服务器要扩容是没问题的，直接把表做一下迁移就行了，然后修改路由规则。