1. 分库分表

1.1 为什么要分库分表

我们看一下 sql 的执行过程。mysql 执行一条sql的过程如下：

1. 收到sql
2. 把sql放到排队队列中
3. 执行sql
4. 返回结果

在这个执行过程中最花时间的地方在于：排队等待的时间和sql的执行时间。

如果有 2 个 sql 都要同时修改同一张表的同一条数据，mysql 对这种情况的处理是：

一种是表锁定（MyISAM存储引擎）
表锁定表示其他操作都不能对这张表进行操作，必须等当前对表的操作完才行。
一个是行锁定（InnoDB存储引擎）
行锁定也一样，别的sql必须等这条数据操作完了，其他人才能对这条数据进行操作。

如果数据太多，一次执行的时间太长，等待的时间就越长，这也是我们为什么要分表的原因。

1.2 分库分表术语

读写分离： 不同的数据库，同步相同的数据，分别只负责数据的读和写。

分区：指定分区列表达式，把记录拆分到不同的区域中（必须是同一服务器，可以是不同硬盘），应用看来还是同一张表，没有变化。

分库：一个系统的多张数据表，存储到多个数据库实例中。

分表： 对于一张多行（记录）多列（字段）的二维数据表，又分两种情形：

垂直分表
竖向切分，不同分表存储不同的字段，可以把不常用或者大容量、或者不同业务的字段拆分出去
水平分表(最复杂)
横向切分，按照特定分片算法，不同分表存储不同的记录。

在实际生产中，通常的进化过程是：

单库单表 -> 单库多表 -> 多库多表
分区 -> 分表 -> 分库（垂直分库 - 水平分库 - 读写分离）

单库单表

单库单表是最常见的数据库设计，如有一张订单表（order）放在数据库中，所有的订单都可以在 order 表中查到。

单库多表

随着需求的迭代，如果增加添加一列的时候，mysql会锁表，期间所有的读写操作只能等待，别无他法。

这时候，可以将 order 进行水平的切分，产生多个表结构完全一样的 order 表。比如：order_01，order_02， …，order_n，那么order_01 + order_02 + … + order_n 的数据是一份完整的订单数据。

水平切分，简单的做法如：

按ID区间来切分，将orderId为 1~~9999 的记录分到第一个库，10000~~20000 的分到第二个库，以此类推。

好处在于扩容很方便，因为只要每个月预备好合适大小的新库，到了一个新的月份的时候，自然而然就会写新的库了。缺点在于如果大部分请求都是访问最新的数据，那么局部表的负荷很大，旧数据的数据库几乎没有负荷。

按时间切分，按日期将不同月甚至是日的数据分散到不同的库中，比如：2019年1月份存在第一张表，2019年2月份存在第二张表。依次类推。还可以按照id的哈希值进行切分等。

好处在于可以平均分配没给库的数据量和请求压力。坏处在于扩容起来比较麻烦，会有一个数据迁移的这么一个过程。

2. 分表中间件

用过哪些分库分表中间件？不同的分库分表中间件都有什么优点和缺点？

2.1 Cobar

Cobar 是提供关系型数据库（MySQL）分布式服务的中间件，它可以让传统的数据库得到良好的线性扩展，并看上去还是一个数据库，对应用保持透明。

Cobar以Proxy的形式位于前台应用和实际数据库之间，对前台的开放的接口是 MySQL 通信协议，将前台 SQL 语句变更并按照数据分布规则发到合适的后台数据分库，再合并返回结果，模拟单库下的数据库行为。

Cobar 属于中间层方案，在应用程序和 MySQL 之间搭建一层 Proxy。中间层介于应用程序与数据库间，需要做一次转发，而基于 JDBC 协议并无额外转发，直接由应用程序连接数据库。

性能上有些许优势。这里并非说明中间层一定不如客户端直连，除了性能，需要考虑的因素还有很多，中间层更便于实现监控、数据迁移、连接管理等功能。

Cobar 属于阿里 B2B 事业群，始于 2008 年，在阿里服役 3 年多，接管 3000+ 个 MySQL 数据库的 schema，集群日处理在线 SQL 请求 50 亿次以上。

由于Cobar发起人的离职，Cobar 停止维护。后续的类似中间件，比如 MyCAT 建立于 Cobar 之上，包括现在阿里服役的 RDRS 其中也复用了 Cobar-Proxy 的相关代码。

2.2 MyCAT

MyCAT 是社区爱好者在阿里 cobar 基础上进行二次开发，解决了 cobar 当时存在的一些问题，并且加入了许多新的功能在其中。目前 MyCAT 社区活跃度很高，目前已经有一些公司在使用MyCAT。总体来说支持度比较高，也会一直维护下去，发展到目前的版本，已经不是一个单纯的 MySQL 代理了。

它的后端可以支持 MySQL，SQL Server，Oracle，DB2，PostgreSQL 等主流数据库，也支持 MongoDB 这种新型NoSQL 方式的存储，未来还会支持更多类型的存储。

MyCAT是一个强大的数据库中间件，不仅仅可以用作读写分离，以及分表分库、容灾管理，而且可以用于多租户应用开发、云平台基础设施，让你的架构具备很强的适应性和灵活性。

借助于即将发布的 MyCAT 性能优化模块，系统的数据访问瓶颈和热点一目了然，根据这些统计分析数据，你可以自动或手工调整后端存储，将不同的表隐射到不同存储引擎上，而整个应用的代码一行也不用改变。

MyCAT是在 Cobar 基础上发展的版本，两个显著提高：后端由 BIO 改为 NIO，并发量有大幅提高；增加了对Order By，Group By，Limit等聚合功能。（虽然 Cobar 也可以支持 Order By，Group By，Limit语法，但是结果没有进行聚合，只是简单返回给前端，聚合功能还是需要业务系统自己完成）

2.3 TDDL

TDDL是 Tabao 根据自己的业务特点开发了（Tabao Distributed Data Layer，外号：头都大了）。主要解决了分库分表对应用的透明化以及异构数据库之间的数据复制，它是一个基于集中式配置的jdbc datasourcce实现，具有主备，读写分离，动态数据库配置等功能。

TDDL并非独立的中间件，只能算作中间层，处于业务层和JDBC层中间，是以Jar包方式提供给应用调用，属于JDBC Shard的思想。

TDDL源码：https://github.com/alibaba/tb_tddl

TDDL复杂度相对较高。当前公布的文档较少，只开源动态数据源，分表分库部分还未开源，还需要依赖diamond，不推荐使用。

2.4 DRDS

DRDS 是阿里巴巴自主研发的分布式数据库服务（此项目不开源），DRDS脱胎于阿里巴巴开源的 Cobar 分布式数据库引擎，吸收了 Cobar 核心的Cobar-Proxy 源码，实现了一套独立的类似MySQL-Proxy协议的解析端，能够对传入的SQL进行解析和处理，对应用程序屏蔽各种复杂的底层DB拓扑结构，获得单机数据库一样的使用体验。

同时借鉴了淘宝TDDL丰富的分布式数据库实践经验，实现了对分布式Join支持，SUM/MAX/COUNT/AVG等聚合函数支持以及排序等函数支持，通过异构索引、小表广播等解决分布式数据库使用场景下衍生出的一系列问题，最终形成了完整的分布式数据库方案。

2.5 Atlas

Atlas 是一个位于应用程序与MySQL之间的基于MySQL协议的数据中间层项目，它是在mysql-proxy 0.8.2版本上对其进行优化，360团队基于mysql proxy 把 lua 用 C 改写，它实现了MySQL的客户端和服务端协议，作为服务端与应用程序通讯，同时作为客户端与MySQL通讯。它对应用程序屏蔽了DB的细节。

这个产品最新一次小幅度更新也是一年前，活跃度很低，不简推荐使用。

2.5 Sharding-JDBC/Sharding-Sphere

sharding-JDBC ，从 3.0 开始，Sharding-JDBC 将更名为 Sharding-Sphere。是当当应用框架ddframe中，从关系型数据库模块dd-rdb中分离出来的数据库水平分片框架，实现透明化数据库分库分表访问。

Sharding-JDBC 是继dubbox和elastic-job之后，ddframe系列开源的第 3 个项目。

Sharding-JDBC直接封装JDBC API，可以理解为增强版的JDBC驱动，旧代码迁移成本几乎为零：

可适用于任何基于Java的ORM框架，如JPA、Hibernate、Mybatis、Spring JDBC Template或直接使用JDBC。
可基于任何第三方的数据库连接池，如DBCP、C3P0、 BoneCP、Druid等。
理论上可支持任意实现JDBC规范的数据库。虽然目前仅支持MySQL，但已有支持Oracle、SQLServer等数据库的计划。

Sharding-JDBC定位为轻量Java框架，使用客户端直连数据库，以 jar 包形式提供服务，无proxy代理层，无需额外部署，无其他依赖，DBA也无需改变原有的运维方式。

Sharding-JDBC分片策略灵活，可支持等号、between、in等多维度分片，也可支持多分片键。

SQL解析功能完善，支持聚合、分组、排序、limit、or等查询，并支持Binding Table以及笛卡尔积表查询。

2. 数据库迁移方案

现在有一个未分库分表的系统，未来要分库分表，如何设计才可以让系统从未分库分表动态切换到分库分表上？

2.1 停机迁移方案

系统停掉，然后将老库的数据迁移到分库分表的数据中

2.2 双写迁移方案

双写迁移方案就是同时写两个库，一个是老库，一个是新库。也就是在线上系统里面，除了对所有老库的增删改地方，同时对新库同样执行增删改，这就是所谓的双写。

系统部署之后，新库数据差太远，用导数工具读老库数据并写到新库中，写的时候要根据gmt_modified这类字段判断这条数据最后修改的时间，除非是读出来的数据在新库里没有，或者是比新库的数据新才会写。简单来说，就是不允许用老数据覆盖新数据。

导完一轮之后，有可能数据还是存在不一致，那么就程序自动做一轮校验，比对新老库每个表的每条数据，接着如果有不一样的，就针对那些不一样的，从老库读数据再次写。反复循环，直到两个库每个表的数据都完全一致为止。

当数据完全一致时，基于仅使用分库分表的最新代码，重新部署一次。

4. 动态扩容

如何设计可以动态扩容缩容的分库分表方案？

动态扩容做到很优雅就是要保证扩容缩容的代价要小，迁移数据要快。可以采用逻辑分库分表的方式来代替物理分库分表的方式：要扩容缩容时，只需要将逻辑上的数据库和表改为物理上的数据库和表。

快速平稳的扩容缩容是建立在合理的分库分表的基础之上，利用对2的倍数取余具有向前兼容的特性（如对4取余得1的数对2取余也是1）来分配数据，可以避免行级别数据的迁移，但是依然需要进行表级别数据的迁移。所以第一次进行分库分表时多分几个库，以保证未来很长时间内都不需要再分库分表。

这里有个实践参考：利用 32 * 32 来分库分表，即分为 32 个库，每个库 32 张表，一共就是 1024 张表，一条数据先根据先根据数据库数量取模路由到哪个库，再根据库的表数量取模路由到哪张表。

一般路由到表的时候，先将数据的 id 对表的总数整除，再对表的总数取模，这样数据会更均匀分布在一个库的表中。

为什么是若表的数量为 1024，库的数量为 33，则无法均分。

刚开始的时候，这个库可能就是逻辑库，等到需要扩容的时候改成物理扩容即可。

5. 分布式全局唯一ID

5.2 结合数据库维护一个Sequence表

此方案的思路也很简单，在数据库中建立一个Sequence表，表的结构类似于：

CREATE TABLE `SEQUENCE` (  
    `table_name` varchar(18) NOT NULL,  
    `next_id` bigint(20) NOT NULL,  
    PRIMARY KEY (`table_name`)  
) ENGINE=InnoDB

每当需要为某个表的新纪录生成 ID 时就从Sequence表中取出对应表的next_id，并将next_id的值加1后更新到数据库中以备下次使用。

此方案也较简单，但缺点同样明显：由于所有插入任何都需要访问该表，该表很容易成为系统性能瓶颈，同时它也存在单点问题，一旦该表数据库失效，整个应用程序将无法工作。

有人提出使用Master-Slave进行主从同步，但这也只能解决单点问题，并不能解决读写比为1:1的访问压力问题。

5.2 UUID

UUID 生成的是length=32的16进制格式的字符串，算法的核心思想是结合机器的网卡、当地时间、一个随即数来生成 UUID，使用 UUID 作主键是最简单的方案。

但是缺点也是非常明显的，由于UUID非常的长，除占用大量存储空间外，最主要的问题是在索引上。

5.3 获取系统当前时间

纯获取系统当前时间作为唯一id在高并发情况下是有风险的，会出现重复主键的情况，因此可以再增加一些唯一标识，如业务字段值跟当前时间拼接起来，组成一个全局唯一的编号：订单编号 + 时间戳 + 用户id + 业务含义编码。

5.4 Snowflake 算法生成全局ID

Twitter的snowflake算法解决了分布式系统生成全局ID的需求，生成64位的Long型数字，组成部分：

1bit：第一位不使用
永远是0，保证是正数。
41bit：表示的是时间戳，单位是毫秒
41 bit 的长度可以表示 69 年的时间
10bit：节点 ID
5位数据中心（机房）+ 5位节点ID（服务器），支持 32 * 32 = 1024 个节点
12bit：流水号，用来记录同一个毫秒内产生的不同 ID
12 bit 的计数顺序号，支持每个节点每毫秒产生4096个ID序列，每个节点每毫秒内支持 4096 个 ID，相当于 409万的 QPS，相同时间内如 ID 遇翻转，则等待至下一毫秒。

6. MySQL 读写分离

读写分离，基本的原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。

因为数据库的”写”（写 10000 条数据到 oracle 可能要3分钟）操作是比较耗时的。但是数据库的”读”（从 oracle 读10000 条数据可能只要 5 秒钟）。 所以读写分离，解决的是，数据库的写入，影响了查询的效率。

数据库不一定要读写分离，如果程序使用数据库较多时，而更新少，查询多的情况下会考虑使用，利用数据库主从同步。可以减少数据库压力，提高性能。当然，数据库也有其它优化方案。memcache 或是表折分，或是搜索引擎。都是解决方法。

7. MySQL 主从复制

在实际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实际需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。

因此，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。有点类似于 ES 搜索引擎的rsync，但是不同的是rsync是对磁盘文件做备份，而 MySQL 主从复制是对数据库中的数据、语句做备份。

7.1 MySQL支持的复制类型

1. 基于语句的复制

在服务器上执行sql语句，在从服务器上执行同样的语句，MySQL 默认采用基于语句的复制，执行效率高。

2. 基于行的复制

把改变的内容复制过去，而不是把命令在从服务器上执行一遍。

3. 混合类型的复制

默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。

7.2 复制的工作过程

mysql主从复制需要三个线程，master（binlog dump thread）、slave（I/O thread 、SQL thread）。

master

（1）binlog dump线程：当主库中有数据更新时，那么主库就会根据按照设置的binlog格式，将此次更新的事件类型写入到主库的binlog文件中，此时主库会创建log dump线程通知slave有数据更新，当I/O线程请求日志内容时，会将此时的binlog名称和当前更新的位置同时传给slave的I/O线程。

slave

（2）I/O线程：该线程会连接到master，向log dump线程请求一份指定binlog文件位置的副本，并将请求回来的binlog存到本地的relay log中，relay log和binlog日志一样也是记录了数据更新的事件，它也是按照递增后缀名的方式，产生多个relay log（ host_name-relay-bin.000001）文件，slave会使用一个 index 文件（ host_name-relay-bin.index）来追踪当前正在使用的relay log文件。

（3）SQL线程：该线程检测到relay log有更新后，会读取并在本地做redo操作，将发生在主库的事件在本地重新执行一遍，来保证主从数据同步。

如果一个relay log文件中的全部事件都执行完毕，那么SQL线程会自动将该relay log文件删除掉。

MySQL 5.7 之后增加了并行复制功能，并行复制指的是从库开启多个线程，并行读取relay log中不同库的日志，然后并行重放不同库的日志，这是库级别的并行。

建议是一般在读远远多于写，而且读的时候一般对数据时效性要求没那么高的时候，用 MySQL 主从同步。

7.3 主从同步的延时问题

当写数据的并发很高的时候，可能会出现少许数据不能及时查询到，很有可能就是数据库主从同步延时问题。

slave 同步延迟的可能原因

slave的I/O线程推迟读取日志中的事件信息；最常见原因是slave是在单线程中执行所有事务，而master有很多线程可以并行执行事务。
带来低效连接的长查询、磁盘读取的I/O限制、锁竞争和innodb线程同步启动等。
Master负载、Slave负载
网络延迟

如何查看同步延迟

可以通过比对master、slave上的日志位置
通过show slave status查看Seconds_Behind_Master的值，这个值代表主从同步延迟的时间，值越大说明延迟越严重。值为0为正常情况，正值表示已经出现延迟，数字越大从库落后主库越多。
使用percona-toolkit的pt-hearbeat工具进行查看。

减少同步延迟的操作方案

拆分主库
将主库拆分，降低每个主库写的并发量，这样主从同步的延时时长就能有效缩短。
优化代码逻辑
插入数据的时候立即查数据是主从复制架构上开发的一个大忌。因此，代码修改逻辑可以借鉴如下：
- 如果第二步获取的数据不需要第一步更新的status字段，那就先读，然后再更新
- 如果第二步获取的数据需要依赖第一步的status字段，那就在读出来的时候先判断是否为空，如果是空的，报错，下一次重试。
负载均衡
搭建多少slave，并且使用lvs或nginx进行查询负载均衡，可以减少每个 slave 执行查询的次数和时间，从而将更多的时间用于去处理主从同步。
Slave调整参数
为了保障较高的数据安全性，配置sync_binlog=1，innodb_flush_log_at_trx_commit=1等设置。而Slave可以关闭binlog，innodb_flush_log_at_trx_commit也可以设置为 0 来提高sql的执行效率（这两个参数很管用）
并行复制
即有单线程的复制改成多线程复制，从库有两个线程与复制相关：io_thread负责从主库拿binlog并写到relaylog，sql_thread负责读relaylog并执行。
多线程的思路就是把sql_thread变成分发线程，然后由一组worker_thread来负责执行。几乎所有的并行复制都是这个思路，有不同的，便是sql_thread 的分发策略。

MySQL 5.7 的真正并行复制enhanced multi-threaded slave（MTS）很好的解决了主从同步复制的延迟问题。