高并发系统设计：MySQL存储海量数据的最后一招---分库分表_多并发数据库设计

为什么要分库分表

但是随着流量的提升，数据量的会海量增加，这时数据库的查询和写入性能都会下降。

• 随着系统的运行，数据库中存储的数据页越来越多，单个表的数据量超过了千万甚至到了亿级别。这时即使你使用了索引也会影响到查询的性能了。那么这时应该如何提升查询性能呢？ 
  • 数据量太大，会导致表的索引很大 
    • 表的索引很大，数据库就可能无法缓存全部的索引信息，那么就需要从磁盘上读取索引数据
    • 表的索引很大，那么B+树的层级会很高，这会导致搜索性能下架，而且能放内存缓存的数据页是比较少的。这样就导致查询比较慢了
  • 数据量越大数据库就越慢呢？ 
    • 我们知道，无论是“增删查改”哪个操作，其实都是查找问题，因为必须先找到数据才能对数据做操作。也就是说存储系统性能问题，本质就是查找快慢问题。
    • 无论是什么样的存储系统，一次查询所消耗的时间，都取决于两个因素： 
      • 查找的时间复杂度
      • 数据总量
    • 查找的时间复杂度又取决于两个因素： 
      • 查找算法
      • 存储数据中的数据结构
    • 对于我们大多数业务系统来说，用的都是现成的数据库，数据的存储结构和查找算法都是由数据库来实现的，业务系统基本没有办法改变它。比如，MySQL的InnoDB存储引擎的存储结构是B+树，查找算法就是树的查找，其时间复杂度为O(log n)，这些都是固定的。那我们唯一能够改变的就是数据总量了。
  • 高性能MySQL的本质就是控制数据量不要太大，以及保证你的查询都用上了索引
    • 解决海量数据导致存储系统慢的问题，思想非常简单，就是一个“拆”字，把一大坨数据拆分成N个小块，学名叫做“分片（shard）”。
    • 拆开之后，每个分片里的数据就没有那么多了，然后让查找尽量落在某一个分片上，这样来提升查找性能。
• 不同模块的数据，比如用户数据和用户关系数据，全都存储在一个主库中，一旦主库发生故障，所有的模块都会受到影响。那么如何做到不同模块的故障隔离呢？
• 数据量的增加也占据了磁盘的空间，数据库在备份和恢复的时间变长，那么如何让数据库系统支持如此大的数据量呢？
• 在 4 核 8G 的云服务器上对 MySQL5.7 做 Benchmark，大概可以支撑500TPS 和 10000QPS，可以看到数据库对写入性能要弱于数据查询的能力，那么随着系统写入请求量的增长，*数据库如何处理更高的并发写入请求呢？

问题：解决海量数据的问题，能不能用分布式存储呢？因为MySQL本质上是一个单机数据库，并不适合TB级别以上的数据库呀

• 只有MySQL这类关系型数据库，才能提供金融级别的事务保证（分布式事务是“残血版”）
• 所以，绝大多数电商大厂，它的在线交易这部分的业务，比如说，订单、支付相关的系统，还是舍弃不了MySQL

从上面我们可以看出，海量数据会造成如下两个问题

• 第一个问题是：数据量太多导致查询和更新慢 
  • 怎么办？只要减少每次查询的数据总量就可以了
  • 怎么做？ 分表，一张表可能有很多的数据，根据某种算法将它们分散到不同的数据库中（单表数据库大时分表）
• 第二个问题是：为了高并发读写的问题 
  • 怎么办？一个数据库撑不住，那么就把并发请求分散到多个数据库中
  • 所以，解决高并发的问题是需要分库的
• 也就是说，数据量大，就分表；并发高，就分库。

即MySQL提出的解决方案是：分库分表。也就是对数据进行分片。

• 啥叫做分片？就是拆分数据。1TB的数据，一个库撑不住，把它拆成100个库，每个库就只有100G的数据了，不就可以了吗？这种拆分就是所谓的MySQL分库分表。对数据进行分片，可以很好的分摊数据库的读写压力，也可以突破单机的存储瓶颈。
• 分库分表的基本思路时依照某一种策略将数据尽量平均的分配到多个数据库节点或者多个表中。 
  • 分库分表之后，每个节点只保存部分的数据（主从复制，数据会全量的被拷贝到多个节点）
  • 数据的写入请求和读写请求都由单一的主库请求变成了请求多个数据分配节点。 在一定程度上会提升并发写/读入的性能

数据量大就一定要分库分表吗？

在考虑到底是分库还是分表之前，我们需要先明确一个原则，那就是能不拆就不拆，能少拆就少拆。原因也很简单，你把数据拆分得越散，开发和维护起来就越麻烦，系统出问题的概率就越大。

在分库分表之前，我们要考虑能不能归档。比如说像是历史订单这样句柄时间属性的

• 所谓归档，也是一种拆分数据的策略，简单来说，就是把大量的历史订单移到另外一张历史订单表中。为什么这么做呢？因为像订单这类具有时间属性的数据，都存在热尾效应。大多数情况下访问的都是最近的数据，但是订单表里面大量的数据都是不怎么常用的老数据。
• 我们在开发业务系统的时候，很多数据都是具备时间属性的，并且随着系统运行，累计增长越来越多，数据量达到一定程度就会越来越慢，比如说电商中的订单数据，就是这种情况。这个时候就需要拆分数据了。
• 因为新数据只占数据总量中很少的一部分数据，所以把新老数据分开之后，新数据的数据量就会少很多，查询速度也会快很多。老数据虽然和之前比起来没少多少，查询速度提升不明显，但是，因为老数据很少被访问到，所以慢一点问题不太。
• 这样拆分的另外一个好处是，拆分订单时，需要改动的代码非常少。大部分对订单表的操作都是在订单完成之前，这些业务逻辑都是完全不用修改的。即使像退货退款这类订单完成后的操作，也是有时限的，那这些业务逻辑也不需要修改，原来该怎么操作订单表还怎么操作。
• 基本上只有查询统计类的功能，才会查到历史订单，这些需要稍微做一些调整，按照时间，选择去订单表还是历史订单表查询就可以了。

归档历史订单，大致流程如下：

• 首先我们需要创建一个和订单表结构一模一样的历史订单表
• 然后，把订单表中的历史订单数据分批查出来，插入到历史订单表中去。这个过程你怎么实现多可以，用存储过程、写个脚本或者写个导数据的小程序都可以。如果你的数据已经做了主从分离，那最好是去从库查询，再写到主库的历史订单表中去，这样对主库的压力会小一点
• 现在，订单表和历史订单表都有历史订单数据，先不要着急去删除订单表中的数据，你应该测试和上线支持历史订单表的新版本代码。因为两个表都有历史订单，所以现在这个数据库可以支持新旧两个版本的代码，如果新版本的代码有 Bug，你还可以立刻回滚到旧版本，不至于影响线上业务。
• 等新版本代码上线并验证无误之后，就可以删除订单表中的历史订单数据了。
• 最后，还需要上线一个迁移数据的程序或者脚本，定期把过期的订单从订单表搬到历史订单表中去。

类似于订单商品表这类订单的相关的子表，也是需要按照同样的方式归档到各自的历史表中，由于它们都是用订单 ID 作为外键来关联到订单主表的，随着订单主表中的订单一起归档就可以了。

这个过程中，我们要注意的问题是，要做到对线上业务的影响尽量的小。迁移这么大量的数据，或多或少都会影响数据库的性能，你应该尽量放在闲时去迁移，迁移之前一定做好备份，这样如果不小心误操作了，也能用备份来恢复。

这里面还有一个很重要的细节问题：如何从订单表中删除已经迁走的历史订单数据？我们直接执行一个删除历史订单的 SQL 行不行？像这样删除三个月前的订单：

<span style="color:#000000"><span style="background-color:#fafafa"><code class="language-sql"><span style="color:#0077aa">delete</span> <span style="color:#0077aa">from</span> orders
<span style="color:#0077aa">where</span> <span style="color:#0077aa">timestamp</span> <span style="color:#a67f59"><</span> SUBDATE<span style="color:#999999">(</span>CURDATE<span style="color:#999999">(</span><span style="color:#999999">)</span><span style="color:#999999">,</span><span style="color:#0077aa">INTERVAL</span> <span style="color:#986801">3</span> <span style="color:#0077aa">month</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
</code></span></span>

• 1
• 2

• 大概率会遇到错误，提升删除失败，因为需要删除的数据量太大了，所以需要分批删除。比如说我们每批删除 1000 条记录，那分批删除的 SQL 可以这样写：

<span style="color:#000000"><span style="background-color:#fafafa"><code class="language-sql"><span style="color:#0077aa">delete</span> <span style="color:#0077aa">from</span> orders
<span style="color:#0077aa">where</span> <span style="color:#0077aa">timestamp</span> <span style="color:#a67f59"><</span> SUBDATE<span style="color:#999999">(</span>CURDATE<span style="color:#999999">(</span><span style="color:#999999">)</span><span style="color:#999999">,</span><span style="color:#0077aa">INTERVAL</span> <span style="color:#986801">3</span> <span style="color:#0077aa">month</span><span style="color:#999999">)</span>
<span style="color:#0077aa">order</span> <span style="color:#0077aa">by</span> id <span style="color:#0077aa">limit</span> <span style="color:#986801">1000</span><span style="color:#999999">;</span>
</code></span></span>

• 1
• 2
• 3

• 执行删除语句的时候，最好在每次删除之间停顿一会儿，避免给数据库造成太大压力。上面的这个删除语句已经可以用了，反复执行这个 SQL，直到全部删除历史订单是可以完成删除任务的。
• 但是这个 SQL 还是有优化空间的，这个 SQL 每执行一次，都要先去timestamp 对应的索引上找出符合条件的记录，然后再把这些记录按照订单 ID 排序，之后删除前 1000 条记录。
• 其实每次都排序是没必要的，所以我们可以先通过一次查询，找到符合条件的历史订单中最大的那个订单 ID，然后在删除语句中把删除的条件转换成按主键删除。

<span style="color:#000000"><span style="background-color:#fafafa"><code class="language-sql"><span style="color:#0077aa">select</span> <span style="color:#dd4a68">max</span><span style="color:#999999">(</span>id<span style="color:#999999">)</span> <span style="color:#0077aa">from</span> orders
<span style="color:#0077aa">where</span> <span style="color:#0077aa">timestamp</span> <span style="color:#a67f59"><</span> SUBDATE<span style="color:#999999">(</span>CURDATE<span style="color:#999999">(</span><span style="color:#999999">)</span><span style="color:#999999">,</span><span style="color:#0077aa">INTERVAL</span> <span style="color:#986801">3</span> <span style="color:#0077aa">month</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#0077aa">delete</span> <span style="color:#0077aa">from</span> orders
<span style="color:#0077aa">where</span> id <span style="color:#a67f59"><=</span> ?
<span style="color:#0077aa">order</span> <span style="color:#0077aa">by</span> id <span style="color:#0077aa">limit</span> <span style="color:#986801">1000</span><span style="color:#999999">;</span>
</code></span></span>

• 1
• 2
• 3
• 4
• 5

• 这样每次删除的时候，由于条件变成了主键比较

  • 我们知道MySQL的InnoDB存储引擎中，表数据结构就是按照主键组织的一颗B+树，而B+树本身就是有序的，所以不仅查找非常快，也不需要进行额外的排序操作了。
  • 当前这样做的前提条件是订单ID必须和订单时间正相关才行，大多数订单 ID 的生成规则都可以满足这个条件，所以问题不大。

• 另外，为什么在删除语句中非要加一个排序呢？

  • 因为按ID排序后，我们每批删除的记录，基本都是ID连续的一批记录
  • 由于B+树的有序性，这些ID相近的记录，在磁盘的物理文件上，大致也是放在一起的
  • 这样删除效率会比较高，也便于MySQL回收页。

大量的历史订单数据删除之后，如果你检查一下MySQL占用的磁盘空间，你会发现它占用的磁盘空间并没有变小，这是什么原因呢？

• 这也和InnoDB的物理存储结构有关
• 虽然逻辑上每个表都是一颗B+树，但是物理上，每条记录都是存放在磁盘文件中的，这些记录通过一些位置指针来组织成一颗B+树
• 当MySQL删除一条记录的时候，只能是找到记录所在的文件中位置，然后把文件的这块区域标记为空闲，然后再修改B+树中相关的一些指针，完成删除。其实那条被删除的记录还是躺在那个文件的那个位置，所以并不会释放磁盘空间
• (数据和索引虽然在物理上没有删除，但逻辑上已经删除掉了，执行查询操作的时候，并不会去访问这些已经删除的数据)。
• 这也是没有方法的方法，因为文件就是一段连续的二进制字节，类似于数据，它不支持从文件中间删除一部分数据。如果非要这么删除，只能把这个位置之后的所有数据往前挪，这样等于是要移动大量数据，非常非常慢。所以，删除的时候，只能是标记一下，并不真正删除，后继写入新数据的时候再重用这块空间

不仅是MySQL，很多其他的数据库都会有类似的问题。这个问题没有什么特别好的解决方法，磁盘足够的话，就这样吧，至少数据删除了，查询速度也快了，基本上是达到了目的。

• 如果我们的数据库磁盘空间很紧张，非要把这部分磁盘空间释放出来，可以执行一次OPTIMIZE TABLE释放存储空间。对于InnoDB来说，执行OPTIMIZE TABLE实际上就是把这个表重建一遍，执行过程中会一直锁表，也就是说这个时候下单都会被卡住。另外这么优化有关前提矫健，MySQL的配置必须是每个表独立一个表空间（innodb_file_per_table = ON），如果所有表都是放在一起的，执行OPTIMIZE TABLE也不会释放空间。
• 重建表的时候，索引也会重建，这样表数据和索引数据都会更加紧凑，不仅占用磁盘空间更小，查询效率也会提升。那对于频繁插入删除大量数据的这种表，如果能够接受缩表，定期执行行 OPTIMIZE TABLE是非常有必要的。

如果说，我们的系统可以接受暂时停服，最快的方法是这样的：

• 直接新建一个临时订单表，然后把当前订单复制到临时订单表中，再把旧的订单表改名，最后把临时订单表的表名改成正式订单表。
• 这样，相当于我们手工把订单表重建了一次，但是，不需要漫长的删除历史订单的过程了。执行过程的 SQL如下：

<span style="color:#000000"><span style="background-color:#fafafa"><code class="language-sql"><span style="color:#708090">-- 新建一个临时订单表</span>
<span style="color:#0077aa">create</span> <span style="color:#0077aa">table</span> orders_temp <span style="color:#a67f59">like</span> orders<span style="color:#999999">;</span>
<span style="color:#708090">-- 把当前订单复制到临时订单表中</span>
<span style="color:#0077aa">insert</span> <span style="color:#0077aa">into</span> orders_temp
<span style="color:#0077aa">select</span> <span style="color:#a67f59">*</span> <span style="color:#0077aa">from</span> orders
<span style="color:#0077aa">where</span> <span style="color:#0077aa">timestamp</span> <span style="color:#a67f59">>=</span> SUBDATE<span style="color:#999999">(</span>CURDATE<span style="color:#999999">(</span><span style="color:#999999">)</span><span style="color:#999999">,</span><span style="color:#0077aa">INTERVAL</span> <span style="color:#986801">3</span> <span style="color:#0077aa">month</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
 
 
<span style="color:#708090">-- 修改替换表名</span>
<span style="color:#0077aa">rename</span> <span style="color:#0077aa">table</span> orders <span style="color:#0077aa">to</span> orders_to_be_droppd<span style="color:#999999">,</span> orders_temp <span style="color:#0077aa">to</span> orders<span style="color:#999999">;</span>
<span style="color:#708090">-- 删除旧表</span>
<span style="color:#0077aa">drop</span> <span style="color:#0077aa">table</span> orders_to_be_dropp
</code></span></span>

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12

小结：

• 对于订单这类具有时间属性的数据，会随时间累积，数据量越来越多，为了提升查询性能需求
• 要对数据进行拆分，首选的拆分方法是把旧数据归档到历史表中去。这种拆分方法能起到很效果，更重要的是对系统的改动小，升级成本低。
• 在迁移历史数据过程中，如果可以停服，最快的方式是重建一张新的订单表，然后把三个月内的订单数据复制到新订单表中，再通过修改表名让新的订单表生效。如果只能在线迁移，那需要分批迭代删除历史订单数据，删除的时候注意控制删除节奏，避免给线上数据库造成太大压力。
• 最后，线上数据操作非常危险，在操作之前一定要做好数据备份。

分库分表的原则

• 如果在性能上没有瓶颈点就尽量不做分库分表
• 如果要做，就尽量一次到位。比如说16库64表就基本能够满足未来几年业务的需求
• 很多的NoSQL数据库，比如Hbase、mongoDB都提供auto sharding的特性，可以考虑使用这些NoSQL数据库替代传统的关系型数据库

如何规划分库分表

• 分库就是把数据库中的表拆分到不同的 MySQL 库中去（这个叫做垂直拆分） 
  • 垂直拆分的原则一般是按照业务类型来拆分，核心思想是专库专用，将业务耦合度比较高的表拆分到单独的库中
  • 什么时候需要分库： 
    • 分库是因为单库的性能（读写）无法满足要求才进行的
    • 分多少个库需要用并发量来预估
  • 优缺点： 
    • 把不同的业务的数据拆分到不同的数据库节点上，这样一旦数据库发生故障时只会影响到某一个模块的功能，不会影响到整体性能，从而实现了数据层面的故障隔离。
    • 依然不能解决某一个业务模块的数据大量膨胀的问题，一旦你的系统遭遇某一个业务库的数据量暴增，就需要做水平拆分了
  • 举个例子： 
    • 分库前，主库中：用户表user，订单表feed
    • 分库后，变成了两个数据库：用户库user、订单库feed
• 分表就是单一数据表按照某一种规则拆分到多个数据库和多个数据表中（水平拆分） 
  • 分库关注业务相关性，要做到专表专用；分表要关注数据的特点，按照某中规则将数据拆分到多个表中
  • 什么时候需要分表： 
    • 分表是因为数据量比较大查询较慢才进行的
    • 往往我们都建议MySQL单表数据量不要超过1000万，最好是在500万以内，如果能控制在100万以内，那最好。基本单表100万以内的数据，性能上不会有太大的问题。前提是，只要你建立好索引就行。
    • 分多少表需要用数据量来预估
  • 举个例子： 
    • 分表前：用户表user
    • 分表后： 
      • 用户表user1、用户表user2，用户表user3
      • 然后我们把这些表分散到多个数据库服务器上。
  • 问题是分表之后应该把这些表分散到几台服务器上呢？需要从两个地方考虑： 
    • 一个是并发能不能扛得住
    • 一个是数据量存储空间能不能存的下 
      • 注意数据量，一个经验是一般一亿行数据，大致在1GB到几个GB之间
      • 这个跟具体你一行数据有多行字段也有关系，大致就是这么个范围。

那我们是先分库还是先分表呢？

• 上面我们提到了，数据量大，就分表；并发高，就分库。

• 但是一般情况下，我们的方案都需要同时做分库分表，这时候分多少库，多少张表，分别用预估的并发量和数据量来计算就可以了。

• 另外，不建议在方案中考虑二次扩容的问题，也就是考虑未来的并发量，把这次分库分表设计的容量都填满了之后，数据如何再次分裂的问题。现在技术和业务变化这么快，等真正到了那个时候，业务早就变了，可能新的技术也出来了，之前设计的二次扩容方案大概率是用不上的，所以没必要为了这个而增加方案的复杂程度。还是那句话，越简单的设计可靠性越高。

如何选择sharding key（如何分表）

上面我们提到，分表就是单一数据表按照某一种规则拆分到多个数据库和多个数据表中

那么，是基于什么拆分的呢？

• 我们需要选择一个合适的列或者说是属性，作为分表的依据，这个属性一般叫做sharding key。
• 选择sharding key时，一定要能够兼容业务最常用的查询条件，让查询尽量落在一个分片中
• 分片之后无法兼容的查询，可以把数据同步到其他存储中去，来解决这个问题

选择了sharding key之后，如何拆分呢？也就是如何选择分片算法。

一般来讲，分片算法有如下几种。具体选择哪一种的原则是并发请求和数据能够均匀的分布在每一个分片上，尽量避免出现热点

• 第一种：按照某一字段的哈希值做拆分。 
  • 应用场景： 
    • 这种拆分规则比较适用于实体表，比如说用户表、内容表，我们一般按照这些实体表的ID字段做拆分
    • 优点：哈希分片比较容易把数据和查询均匀分布到所有分片中。 
      • 哈希分配能够分得足够均匀的前提条件是，用户ID后几位数据必须是均匀分布的。
      • 比如说，你在生成用户 ID 的时候，自定义了一个用户 ID 的规则，最后一位 0 是男性，1 是女性，这样的用户 ID 哈希出来可能就没那么均匀，可能会出现热点。
  • 举个例子： 
    • 比如说我们想把用户表拆分成 16 个库，64 张表，那么可以先对用户 ID 做哈希，哈希的目的是将ID尽量打散，然后再对 16取余，这样就得到了分库后的索引值；对 64 取余，就得到了分表后的索引值。
    • 比如说，我们需要对用户的订单表进行分片，要有24个分片。这个时候，可以这么做：拿用户 ID 除以 24，得到的余数就是分片号。这是最简单的取模算法，一般就可以满足大部分要求了。当然也有一些更复杂的哈希算法，像一致性哈希之类的，特殊情况下也可以使用。
• 第二种：按照某一字段的区间来拆分。比较常用的是时间字段。 
  • 使用场景：范围分片容易产生热点问题，但对查询友好，适合并发量不大的场景；
  • 像是归档历史订单的shading key就是订单完成时间，每次查询的时候，查询条件中必须带上这个时间，我们的程序就知道，三个月以前的数据查订单历史表，三个月内的数据查订单表，这就是一个简单的按照时间范围来分片的算法
  • 这种做法有一个很大的问题，比如现在是3月份，那基本上所有的查询都集中在3月份这个分片上，其他11个分片都闲着，这样不仅浪费资源，很可能你3月那个分片根本扛不住几乎全部的并发请求，这就是热点问题
  • 基于范围分配很容易产生热点问题，不适合作为订单的分片方法，但是这种分片方法的优点也很突出，那就是对查询非常友好，基本上只要加上一个时间范围的查询条件，原来该怎么查，分片后可以怎么查。范围分配特别适合于那种数据量特别大，但是并发访问量不高的ToB系统。比如说，电信运营商的监控系统，它可能要采集所有人手机的信号质量，然后做一些分析，这个数据量非常大，但是这个系统的使用者是运营商的工作人员，并发量很少。这种情况下就很适合范围分片。
• 还有一种分片的方法，查表法。 
  • 使用场景：查表法更灵活，但性能稍差。
  • 查表法其实是没有分片算法，决定某个sharding key落在哪个分片上，全靠人为来分配，分配的结果记录在一张表里面。每次查询的时候，先去表里查一下要找的数据在哪个分片中。
  • 查表法的好处就是灵活，怎么分都可以，你用上面两种分片算法都没法分均匀的情况下，就可以用查表法，人为地来把数据分均匀了。查表法还有一个特好的地方是，它的分片是可以随时改变的。比如我发现某个分片已经是热点了，那我可以把这个分片再拆成几个分片，或者把这个分片的数据移到其他分片中去，然后修改一下分片映射表，就可以在线完成数据拆分了。
  • 但你需要注意的是，分片映射表本身的数据不能太多，否则这个表反而成为热点和性能瓶颈了。查表法相对其他两种分片算法来说，缺点是需要二次查询，实现起来更复杂，性能上也稍微慢一些。但是，分片映射表可以通过缓存来加速查询，实际性能并不会慢很多。

分表所带来的问题

必须携带分区键

• 比如我们把订单ID作为sharding key来拆分订单表，那拆分之后，必须携带分区键订单ID： 
  • 如果我们按照订单ID来查订单，就需要先根据订单ID和分片算法计算出应该去哪个哪个库那张表上查，然后再去那个分片执行查询就可以了。
  • 但是问题是，如果我的查询条件不是sharding key而是其他的怎么办呢？ 
    • 当然你要强行查的话，那就只能把所有分片查一遍，再合并查询结果，这个就很麻烦，而且性能很差，还不能分页。
    • 能不能又用这个条件字段作为sharding key呢？不行的，因为你不可能对所有的查询条件都建立一个sharding key吧，这个数据量会爆炸的
  • 怎么解决呢？举个例子当前已经用了用户ID作为sharding key了，现在正在用订单ID来查询。 
    • 方法一：可以建立一个订单ID和用户ID的映射表。在查询的时候需要先通过昵称查询到ID，再通过ID查询到完整的数据，这个表也可以是分库分表的，也需要占用一定的存储空间。但是因为表中只有两个字段，所以相比重新做一次拆分还是会节省不少空间的
    • 方法二：可以在在生成订单ID的时候，把用户ID的后几位作为订单ID的一部分，比如说，可以规定，18位的订单号，第 10-14 位是用户 ID 的后四位，这样按订单 ID 查询的时候，就可以根据订单 ID 中的用户 ID 找到分片
    • 方法三：如果你的查询条件非常复杂： 
      • 可以对你的表的binlog监听，把你要搜索的所有字段同步到Elasticsearch里去，建立好搜索的索引
      • 然后其他系统就可以ES进行多条件搜索，然后定位到对应的sharding key
  • 分库分表之后，要求看报表，怎么搞？ 
    • 比如说电商中电商要求看自己店铺的订单报表。
    • 把订单数据同步到其他的存储系统中去，在其他的存储系统中里面解决问题。举个例子： 
      • 我们可以再构建一个以店铺ID为sharding key作为只读订单库，专门供商家来使用。
      • 把订单数据同步到HDFS中，然后用一些大数据技术来生成订单相关的报表。

一些聚合类的查询(比如count())性能比较差

引入分库分表之后，一些数据库的特性在实现时可能会变得很困难。

• 比如说join多表在单库时一条SQL就可以完成，但是拆分到多个数据库之后就无法跨库执行SQL了。不过好在我们对join的需求不高，即使有也一般是把两个表的数据取出后在业务代码中做筛选即可
• 在比如说在未分库分表之前查询数据总数时只需要在 SQL 中执行 count() 即可，现在数据被分散到多个库表中，我们可能要考虑其他的方案，比方说将计数的数据单独存储在一张表中或者记录在 Redis 里面。

也就是说需要考虑使用计数器等其他解决方案

主键的全局唯一性问题

啥是主键

数据库中的每一条记录都需要有一个唯一的标识，依据数据库的第二范式，数据库中每一个库中都需要有一个唯一的主键，其他数据元素和主键一一对应。

那么关于主键的选择就成为一个关键点了，一般来讲，有两种选择方式：

• 使用业务字段作为主键，比如对应用户表来说，可以使用手机号、email或者身份证号作为主键
• 使用生成的唯一ID作为主键

用第二种，主键一定要与业务无关，而且是自增长、唯一的、一旦生成就不会变更的。

怎么选择主键

• 在单库单表的场景下，我们可以使用数据库的自增字段作为ID，因为这样最简单，对于开发人员来说也是透明的。

• 但是当数据库分库分表之后，自增字段就无法保证ID的全局唯一性了，所以，建议搭建发号器服务来生成全局唯一的ID

基于Snowflake算法搭建分布式发号器

snowflakes原理

其核心思想是将64bit的二进制数字分为若干部分，每一部分都存储具有特定含义的数据，比如时间戳、机器ID、序列号等等，最终生成全局唯一的有序ID。它的标准算法如下

• 从上面这张图中我们可以看到，41 位的时间戳大概可以支撑pow(2,41)/1000/60/60/24/365 年，约等于 69 年，对于一个系统是足够了。
• 如果你的系统部署在多个机房，那么 10 位的机器 ID 可以继续划分为 2～3 位的 IDC 标示（可以支撑 4 个或者 8 个 IDC 机房）和 7～8 位的机器 ID（支持 128-256 台机器）
• 12位的序列号代表着每个节点每毫秒最多可以生成 4096 的 ID。

不同公司也会依据自身业务的特点对 Snowflake 算法做一些改造，比如说减少序列号的位数增加机器 ID 的位数以支持单 IDC 更多的机器，也可以在其中加入业务 ID 字段来区分不同的业务。

那么了解了 Snowflake 算法的原理之后，我们如何把它工程化，来为业务生成全局唯一的ID 呢？

算法实现方式

一般来说我们会有两种算法的实现方式：

• 一种是嵌入到业务代码里面，也就是分布在业务服务器中
  • 这种方案的好处是业务代码在使用的时候不需要跨网络调用，性能会好一点。
  • 缺点需要更多的机器ID位数来支持更多的业务服务器。
  • 但是由于业务服务器的数量很多，我们很难保证机器ID的唯一性，所以就需要引入zookeeper等分布式一致性组件来保证每次机器重启时都能获得唯一的机器ID
• 另一种是作为独立的服务器部署，这也是我们常说的发号器服务
  • 业务在使用发号器的时候需要多一次的网络调用，但是内网的调用对于性能的损耗有限，却可以减少机器ID的位数，如果发号器以主备方式部署，同时运行的只有一个发号器，那么机器ID可以省略，这样可以留更多位数给最后的自增信息位
  • 即使需要机器ID，因为发号器部署实例数有限，那么就可以把机器ID写在发号器的配置文件里，这样既可以保证机器ID唯一性，也无需引入第三方组件了

Snowflake 算法设计的非常简单且巧妙，性能上也足够高效，同时也能够生成具有全局唯一性、单调递增性和有业务含义的 ID，但是它也有一些缺点，其中最大的缺点是它依赖于系统的时间戳，一旦系统时间不准，就有可能生成重复的ID，所以如果我们发现系统时钟不准，就可以让发号器暂时拒绝发号，直到时钟准确为止。

另外，如果请求发号器的QPS不高，如果说发号器每秒只发一个ID，就会造成生成ID的末位永远是1，那么在分库分表的时候如果使用ID作为分区键就会造成库表分配不均匀。解决方法主要有两个：

• 时间戳不记录毫秒而是记录秒，这样在一个时间区间里面可以多发出几个号，避免出现分库分表时数据分配不均
• 生成的序列号的起始号可以做一下随机，这一秒是 21，下一秒是 30，这样就会尽量的均衡了。

使用建议

• Snowflake 的算法并不复杂，你在使用的时候可以不考虑独立部署的问题，先想清楚按照自身的业务场景，需要如何设计 Snowflake 算法中的每一部分占的二进制位数。比如你的业务会部署几个 IDC，应用服务器要部署多少台机器，每秒钟发号个数的要求是多少等等，然后在业务代码中实现一个简单的版本先使用，等到应用服务器数量达到一定规模，再考虑独立部署的问题就可以了。
• 这样可以避免多维护一套发号器服务，减少了运维上的复杂度。

面试题

分库分表之后如何连表查询

1）分库时把需要关联的数据放到同一个库，尽量避免了夸库查询；

2）实在需要关联查询的，在一个库查出一部分数据，然后再查另一部分，自己在代码中组合；

3）用服务组合需要关联的数据，监视数据变化，将数据拿到进行组合放入 nosql 内存数据库，查询时直接从 nosql 内存数据库查询；

4）使用冗余字段，部分字段两个表都存，直接查询；

5）修改业务，不允许业务展示时关联不必要的数据，或者企图展示过多数据；

举个例子：大型电商网站的上亿数据库的用户表如何进行水平拆分

• 可以选择把这个用户大表拆分为比如100张表，那么此时几千万数据瞬间分散到100个表里去，类似于user_001、user_002、user_003这样的100个表，每个表也就是几十万数据。
• 然后我们把这100张表均匀分布到两台数据库服务器上（从并发量和存储空间考虑）。
• 分的时候需要指定一个字段来分，一般来说会指定userid，根据用户id进行hash后，对表进行取模，路由到一个表里去，这样可以让数据均匀分散

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

但是这里可能会出现一个问题，用户在登录的时候，可能不是根据userid登录的，而是根据user_name之类的用户名，这个时候要怎么知道应该去哪个表里找这个用户的数据判断是否能登录呢？

解决方法：

• 可以建立一个索引映射表，就是建立一个表结构为(username, userid)的索引映射表，把username和userid一一映射，然后针对username再做一次分库分表，把这个索引索引表可以拆分为比如100个表分散到两台服务器里
• 然后用户登录的时候，就可以根据username先去索引映射表里查找对应的userid，比如对username进行hash然后取模路由到一个表里去，找到username对应的userid，接着根据userid进行hash取模，然后路由到按照userid分库分表的一个表里去，找到用户的完整数据即可。

另外就是如果在运营系统中有一个用户管理模块，需要对用丝的用户按照手机号、住址、年龄，性别等各种条件进行极为复杂的搜索，怎么办呢？

• 对你的用户数据表进行binlog监听，把你要搜索的所有字段同步到Elasticsearch里去，建立好搜索的索引
• 然后运营系统就可以通过ES去进行复杂的多条件搜索，然后定位到一批userid，通过userid回到分库分表环境里去找到具体的用户数据，展示到页面即可

如果需要进行垮库的分页操作，应该怎么来做

假设用户要查询自己的订单，同时订单要求支持分页，该怎么做？

• 按照userid先去分库分表（userid, orderid）索引映射表去查找你的那些orderid，然后分页
• 对于分页内的orderid，每个orderid都需要按照orderid分库分表的数据里查找完整的订单数据，这就可以搞定分库分表下的分页问题了

也就是说，如果分库分表环境下搞分页，最好是保证你的一个主数据页(比如userid)是你分库分表的粒度，你可以根据一个业务id路由到一个表里找到它的全部数据，这就可以做分页了

但是如果现在用户既要对用户下的订单做分页，又想指定一些查询条件呢？

• 现在的索引映射表里，只有(userid, orderid)。如果想要解决上面问题，可以在这个索引映射表里加入更多的数据，比如(userid，orderid，order_status，product_description)，加上订单所处的状态，以及商品的标题等文本
• 然后对订单进行分页的时候，直接根据userid去索引映射表中找用户的所有订单，然后按照订单状态、商品描述等进行模糊匹配搜索，完了再分页，分页拿到orderid，再去获取订单需要展示的数据

如果是针对运营系统的分页查询呢？

• 数据直接进行ES里，通过ES就可以对多条件进行搜索同时再分页了。

如果一定要针对跨多个库和多个表的数据搞查询和分页呢？

• 如果一定要的话，基本上只能是自己从各个库拿出数据到内存，自己内存里筛选和分页了；或者是基于数据库中间件去做，数据库中间件本质也是干这个，把各个库表里的数据拿到内存然后筛选和分页。
• 但是绝对反对这种方案，因为效率和性能极差。
• 如果你觉得必须要跨库和表查询和分页的时候，建议，第一，考虑一下是不是可以把你查询里按照某个主要的业务进行分库分表建立一个索引映射库，第二，是不是可以把这个查询里的条件都放到索引映射表去，第三，是不是可以通过ES来搞定这个需求

小结

对MySQL这样的单机数据库来说，分库分表是应对海量数据和高并发的最后一招，分库分表之后，将会对数据查询有非常大的限制。

• 数据库在分库分表之后，数据的访问方式也有了极大的改变，原先只需要根据查询条件从库中查询即可，现在则需要先确认数据在哪一个库表中，再到那个库表中查询数据。这种复杂度也可以通过数据库中间件解决

• 一旦做了分库分表，就会极大的限制数据库的查询能力，之前很简单的查询（比如报表），分库分表之后，可能就没法实现了。
• 当然，虽然分库分表会对我们使用数据库带来一些不便，但是相比它所带来的扩展性和性能方面的提升，我们还是需要做的，因为，经历过分库分表后的系统，才能够突破单机的容量和请求量的瓶颈。分库分表一定是，数据量和并发大到所有招数都不好使了，我们才拿出来的最后一招。

redis缓存、读写分离、分库分表 对比

只读的查询可以通过缓存和读写分离解决：

• 当有大量的读请求而且读出的数据相差不大，这时可以引入redis作为前置缓存降低读压力。
• 当有大量的读请求而且读出的数据是千人千面时，这时redis的命中率不高，我们可以对MySQL做读写分离。

当存储的数据量达到瓶颈(外在表示：查询和更新慢)之后 ，要分库分表

提问：秒杀场景中要不要做读写分离或者分库分表呀？

• 在秒杀场景下，短时间内数据库的写流量会很高，那么依照我们以前的思路应该对数据库做分库分表。
• 如果已经做了分库分表，那么就需要扩展更多的数据库来应对更高的写流量。
• 但是无论是分库分表，还是扩充更多的数据库，都会比较复杂，原因是你需要将数据库中的数据迁移，这个时间就要按照天甚至周来计算了。
• 而在秒杀场景下，高并发的写请求不是连续的，也不是经常发生的，而只有在秒杀活动开始后的几秒或者十几秒内才会存在。为了应该这十几秒的瞬间写高峰，就花费几天甚至几周的时间来扩容数据库，再在秒杀之后花费值台的时间来做缩容，这无疑是的不对的。

怎么解决呢？

• 将秒杀请求暂存在消息队列中