MySQL分库分表
1，背景
我们在工作当中，随着用户和业务增长，业务越来越复杂，数据量越来越多，对数据库的性能和效率就有了更高的要求，就需要开始考虑分库分表了。数据库的性能会受到以下几个因素影响：
- 数据量：MySQL单库数据量在5000W以内性能比较好，超过此阈值后性能会随着数据量的增大而变弱。MySQL单表的数据量是500W-1000W之间性能比较好，超过1000W性能也会下降，一般控制在700W-800W。
- 数据库连接：数据库连接是非常稀少的资源，如果一个库里既有用户、商品、订单相关的数据，最终都会导致数据库的活跃连接数增加，当海量用户同时操作时，数据库连接就很可能成为瓶颈，甚至近达到数据库可承载活跃连接数的阈值。在业务Service来看就是，可用数据库连接少甚至无连接可用。
- IO瓶颈：
第一种：磁盘读IO瓶颈，热点数据太多，数据库缓存放不下，每次查询时会产生大量的IO，降低查询速度，可 分库和垂直分表。
第二种：网络IO瓶颈，请求的数据太多，网络带宽不够，可 分库。 CPU瓶颈：
第一种：SQL语句问题，如SQL中包含JOIN，GROUP BY，ORDER BY，非索引字段条件查询等，增加CPU运算的操作，可 优化SQL，建立合适的索引，在业务Service层进行业务计算。
第二种：单表数据量太大，查询时扫描的行太多，SQL效率低，CPU率先出现瓶颈，可 水平分表。
分库分表，都是对数据库进行分块(Sharding)的方式。对数据库分区，被称作Partition。
2，切分的时机
2.1 能不切分尽量不要切分
并不是所有表都需要进行切分，主要还是看数据的增长速度。切分后会在某种程度上提升业务的复杂度，数据库除了承载数据的存储和查询外，协助业务更好的实现需求也是其重要工作之一。不到万不得已不用轻易使用分库分表这个大招，避免"过度设计"和"过早优化"。分库分表之前，不要为分而分，先尽力去做力所能及的事情，例如：升级硬件、升级网络、读写分离、索引优化等等。当数据量达到单表的瓶颈时候，再考虑分库分表。
2.2 数据量过大，正常运维影响业务访问
这里说的运维，指：
2.2.1 对数据库备份，如果单表太大，备份时需要大量的磁盘IO和网络IO。例如1T的数据，网络传输占50MB时候，需要20000秒才能传输完毕，整个过程的风险都是比较高的；
2.2.2 对一个很大的表进行DDL修改时，MySQL会锁住全表，这个时间会很长，这段时间业务不能访问此表，影响很大。如果使用pt-online-schema-change，使用过程中会创建触发器和影子表，也需要很长的时间。在此操作过程中，都算为风险时间。将数据表拆分，总量减少，有助于降低这个风险；
2.2.3 大表会经常访问与更新，就更有可能出现锁等待。将数据切分，用空间换时间，变相降低访问压力；
2.3 随着业务发展，需要对某些字段垂直拆分
举个例子，假如项目一开始设计的用户表如下：
id bigint #用户的IDname
varchar #用户的名字last_login_time
datetime #最近登录时间personal_info
text #私人信息..... #其他信息字段
在项目初始阶段，这种设计是满足简单的业务需求的，也方便快速迭代开发。而当业务快速发展时，用户量从10W激增到10亿，用户非常的活跃，每次登录会更新 last_login_name 字段，使得 user 表被不断update，压力很大。而其他字段：id, name, personal_info 是不变的或很少更新的，此时在业务角度，就要将 last_login_time 拆分出去，新建一个 user_time 表。
personal_info 属性是更新和查询频率较低的，并且text字段占据了太多的空间。这时候，就要对此垂直拆分出 user_ext 表了。
2.4 数据量快速增长
随着业务的快速发展，单表中的数据量会持续增长，当性能接近瓶颈时，就需要考虑水平切分，做分库分表了。此时一定要选择合适的切分规则，提前预估好数据容量。一般数据的日增长量达到2%就可以考虑做切分了。
2.5 安全性和可用性
鸡蛋不要放在一个篮子里。在业务层面上垂直切分，将不相关的业务的数据库分隔，因为每个业务的数据量、访问量都不同，不能因为一个业务把数据库搞挂而牵连到其他业务。利用水平切分，当一个数据库出现问题时，不会影响到100%的用户，每个库只承担业务的一部分数据，这样整体的可用性就能提高。
3，分库
3.1 水平分库
3.1.1 概念：以 字段为依据 ，按照一定策略（hash、range等），将一个 库中的数据拆分到多个 库中。
3.1.2 结果：
- 每个库的结构都一样；
- 每个库的数据都不一样，没有交集；
- 所有库的并集是全量数据；
3.1.3 场景：系统绝对并发量上来了，分表难以根本上解决问题，并且还没有明显的业务归属来垂直分库。
3.1.4 分析：库多了，IO和CPU的压力自然可以成倍缓解。
3.2 垂直分库
3.2.1 概念：以 表为依据，按照业务归属不同，将不同的 表拆分到不同的 库中 。
3.2.2 结果：
- 每个库的结构都不一样；
- 每个库的数据也不一样，没有交集；
- 所有库的并集是全量数据；
3.2.3 场景：系统绝对并发量上来了，并且可以抽象出单独的业务模块。
3.2.4 分析：到这一步，基本上就可以服务化了。例如，随着业务的发展一些公用的配置表、字典表等越来越多，这时可以将这些表拆到单独的库中，甚至可以服务化。再有，随着业务的发展孵化出了一套业务模式，这时可以将相关的表拆到单独的库中，甚至可以服务化。
4，分表
4.1 水平分表
4.1.1 概念：以 字段 为依据 ，按照一定策略（hash、range等），将一个 表中的数据拆分到多个 表中。
4.1.2 结果：
- 每个表的结构都一样；
- 每个表的数据都不一样，没有交集；
- 所有表的并集是全量数据；
4.1.3 场景：系统绝对并发量并没有上来，只是单表的数据量太多，影响了SQL效率，加重了CPU负担，以至于成为瓶颈。
4.1.4 分析：表的数据量少了，单次SQL执行效率高，自然减轻了CPU的负担。
4.2 垂直分表
4.2.1 概念：以 字段为依据，按照字段的活跃性，将 表中字段拆到不同的 表（主表和扩展表）中。
4.2.2 结果：
- 每个表的结构都不一样；
- 每个表的数据也不一样，一般来说，每个表的 字段至少有一列交集，一般是主键，用于关联数据；
- 所有表的并集是全量数据；
4.2.3 场景：系统绝对并发量并没有上来，表的记录并不多，但是字段多，并且热点数据和非热点数据在一起，单行数据所需的存储空间较大。以至于数据库缓存的数据行减少，查询时会去读磁盘数据产生大量的随机读IO，产生IO瓶颈。
4.2.4 分析：可以用列表页和详情页来帮助理解。垂直分表的拆分原则是将热点数据（可能会冗余经常一起查询的数据）放在一起作为主表，非热点数据放在一起作为扩展表。这样更多的热点数据就能被缓存下来，进而减少了随机读IO。拆了之后，要想获得全部数据就需要关联两个表来取数据。但记住，千万别用join，因为join不仅会增加CPU负担并且会讲两个表耦合在一起（必须在一个数据库实例上）。关联数据，应该在业务Service层做文章，分别获取主表和扩展表数据然后用关联字段关联得到全部数据。
5，分库分表的策略
5.1 哈希取模 (Hash)
根据字段值，对表的数量进程取模，获取到表的前缀或者后缀，例如要分3个表，当user_id=1时，对应到表user_1。
5.2 范围分片 (Range)
预先估计一个表存10W条数据，则从1-100000为表1，100001-20000为表2。
5.3 时间、空间分片
如按年份，季度，月份来分表；或者按地域来分，亚洲，澳洲，欧洲，非洲 ，南美洲，北美洲，南极洲这样分。
5.4 Composite (复合模式)
其实是以上模式的组合使用而已，就不解释了。举例：在初始化已经进行了Range范围分区的表上，我们可以对其中一个分区再进行hash哈希分区。
6，分库分表步骤
一般分以下几个步骤：
6.1 根据容量（当前容量和增长量）评估分库或分表个数；
6.2 选则合适的key，确保数据均匀分布；
6.3 选择分表策略或规则（hash或range等）；
6.4 执行（一般双写）；
6.5 扩容问题（尽量减少数据的移动）；
注意：在分表过程中，在分表规则之外，还会使用一个表去存储key与分表前缀/后缀之间的映射关系，以避免在扩容或者缩容时，引起大部分数据的移动。比如，一开始有100张表，按user_id % 100 取模的，此时要扩容到 128 张表，则user_id 为 101的，原本在 表1 中，按新规则，就要移动到 表101中去。user_id 129 要从表29移动到表1中去。缩表也是同理。如果有一张映射表，记录下user_id 和表名的关系，则可以只对新数据适用新规则，老数据仍然保持不变。每次都先查映射表，没有，再采用新规则去做映射和保存。
7，分库分表后引入的问题
7.1 事务一致性问题
如果我们做了垂直分库或者水平分库以后，就必然会涉及到跨库执行SQL的问题。那要如何解决这个问题呢？
7.1.1 分布式事务
分布式事务能最大限度保证了数据库操作的原子性。但在提交事务时需要协调多个节点，推后了提交事务的时间点，延长了事务的执行时间。导致事务在访问共享资源时发生冲突或死锁的概率增高。随着数据库节点的增多，这种趋势会越来越严重，从而成为系统在数据库层面上水平扩展的枷锁。
- 分布式事务，使用分布式事务中间件；
- 使用MySQL自带的针对跨库的事务一致性方案(XA)，不过性能要比单库的慢10倍左右；
- 两阶段提交，三阶段提交，PAXOS算法等；
- 能否避免掉跨库操作(比如将用户和商品放在同一个库中)；
7.1.2 最终一致性
对于那些性能要求很高，但对一致性要求不高的系统， 往往不苛求系统的实时一致性，只要在允许的时间段内达到最终一致性即可，可采用事务补偿的方式。与事务在执行中发生错误后立即回滚的方式不同，事务补偿是一种事后检查补救的措施，一些常见的实现方法有：对数据进行对账检查，基于日志进行对比，定期同标准数据来源进行同步等等。事务补偿还要结合业务系统来考虑。
7.2 跨库join的问题
分库分表后表之间的关联操作将受到限制，我们无法join位于不同分库的表，也无法join分表粒度不同的表，原本一次查询能够完成的业务，可能需要多次查询才能完成。
粗略的解决方法：
- 全局表：基础数据，所有库都拷贝一份。
- 字段冗余：这样有些字段就不用join去查询了。
- 系统层组装：分别查询出所有，然后组装起来，较复杂。
7.3 横向扩容的问题
当我们使用HASH取模做分表的时候，针对数据量的递增，可能需要动态的增加表，此时就需要考虑因为reHash导致数据迁移的问题。
7.4 结果集合并、排序的问题
因为我们是将数据分散存储到不同的库、表里的，当我们查询指定数据列表时，数据来源于不同的子库或者子表，就必然会引发结果集合并、排序的问题。如果每次查询都需要排序、合并等操作，性能肯定会受非常大的影响。走缓存可能一条路！
7.5 非partition key的查询问题
（水平分库分表，拆分策略为常用的hash法）
7.5.1 端上除了partition key只有一个非partition key作为条件查询
7.5.1.1 映射法
7.5.1.2 基因法
注：写入时，基因法生成user_id，如图。
- 获取x位数，如要分8张表，2^3=8，故x取3，即3bit基因(000, 001, 010, 011, 100, 101, 110, 111)；
- 根据user_name查询时，先通过user_name_code生成函数生成user_name_code，再取user_name_code的后3位；
- 通过分布式ID服务生成64位的原始user_id，常用snowflake算法生成；
- 将原始user_id的后3位用user_name_code的后3位进行替换，生成最终的user_id；
- 根据user_id查询，直接取模，路由到对应的分库或分表；
7.5.2 端上除了partition key不止一个非partition key作为条件查询
7.5.2.1 映射法
7.5.2.2 冗余法
注：按照order_id或buyer_id查询时路由到db_o_buyer库中，按照seller_id查询时路由到db_o_seller库中。感觉有点本末倒置！有其他好的办法吗？改变技术栈呢？
7.5.3 后台除了partition key还有各种非partition key组合条件查询
7.5.3.1 NoSQL法
7.5.3.2 冗余法
7.6 非partition key跨库跨表分页查询问题
（水平分库分表，拆分策略为常用的hash法）
注：可用 NoSQL法（ES等）解决 。
7.7 扩容问题
（水平分库分表，拆分策略为常用的hash法）
7.7.1 水平扩容库（升级从库法）
注：扩容是成倍的。
7.7.2 水平扩容表（双写迁移法）
步骤：
- （同步双写）应用配置双写，部署；
- （同步双写）将老库中的老数据复制到新库中；
- （同步双写）以老库为准校对新库中的老数据；
- （同步双写）应用去掉双写，部署；
注： 双写是通用方案。
参考文章：https://www.jianshu.com/p/7aec260ca1a2
以上是 MySQL分库分表 的全部内容， 来源链接： utcz.com/z/517072.html
