对这几年mysql的使用进行一个整理

        Mysql 是互联网中最常见，应用最广泛的的一个数据库。免费开源让它受到广大开发者的喜欢。本文总结了这几年使用mysql下来的经验以及自己对mysql的一些了解。

 

 1. 近几年个人参与的项目架构中mysql的变化

    14年底刚实习时，由于实习公司的业务限制，mysql是单服务单库单表的使用方式。如下图所示

随着业务的不断发展，单服务器单库单表已经无法支撑用户的请求。根据28理论，在数据库的请求中，80%的请求都是读请求，因此引入了mysql的主从架构。如下图所示：

从此时起，读写分离的设计，让系统的性能得到了很大的提升。后期，随着业务量的变化以及微服务的兴起，分库分表的架构设计也被引用了我们的系统架构中，如下图所示：

可见，随着业务的发展，用户的增多，并发数的递增，数据量的变化，mysql的架构从单库单节点，单库主从复制读写分离，到单库分表读写分离发展到了现在的分库分表读写分离的设计。

 

2. 主从复制的实现原理

Mysql Master节点与Slave节点之间的数据拷贝主要是依赖于mysql的主从赋值。主从复制的实现原理如图所示：

如图所示，mysql的主从复制主要由三个线程完成。分别是 binlog dump thread， I/O thread 和 SQL thread。

主从复制的具体流程如下：

1. 主库db的更新事件(update、insert、delete)被写到binlog
2. 主库创建一个binlog dump thread，把binlog的内容发送到从库
3. 从库启动并发起连接，连接到主库。从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
4. 从库启动之后，创建一个SQL线程，从relay log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db。

3. 主从复制的方式

异步方式

主数据库进行数据的CUD之后，将数据写入到二进制文件binlog中，完成commit。同时，binlog dump thread 将二进制文件发送给slave服务器，slave通过I/O thread 和 SQL thread 完成数据的重写。

这个方式性能最好，但是一旦master奔溃，发生主从切换后，将会产生数据不一致的风险。

为了解决上面的问题，mysql提供了传统的半同步复制

半同步复制与异步复制的不同点在于，当主数据服务器发送二进制给从服务器时，主服务器必须等待至少一个从服务器返回一个ack消息。

半同步复制的优点在于，可以在一定程度上解决异步复制存在的数据一致性问题，让数据更加安全。但与此同时，半同步复制也存在下面的缺点：

    1. 一旦Ack超时，将退化为异步复制模式，那么异步复制的问题也将发送

    2. 性能下降，增多至少一个ACK等待时间

    3. 数据不一致性问题仍然会存在，因为等待ACK返回的时间点是Commit之后，此时Master已经完成数据变更，用户已经可以看到最新数据。当Binlog还未同步到Slave时，发生主从切换，那么此时从库是没有这个最新数据的，用户又看到老数据。

 

增强半同步复制

增强半同步复制与传统的半同步方式的差别在于，接收slave返回的ack的时间点不同。传统的半同步方式是 master直接commit之后再等待ack，而增强半同步方式需要等到salve服务器返回ack之后，再执行commit操作。

增强半同步复制比传统的半同步复制更加能确保数据的一致性。但相对的，增强半同步方式也存在如下的这几个问题

    1. 一旦Ack超时，将退化为异步复制模式，那么异步复制的问题也将发送

    2. 性能下降，增多至少一个ACK等待时间

    3. 如果超时时间设置很大，然后因为网络原来长时间收不到ACK，用户提交是被挂起的，可用性收到打击（半同步一样存在）

 

对上述的内容做一个总结，引入主从复制存在如下的这些优缺点以及这些一些解决方案。

优点：

    1. 用于备份，避免影响业务

    2. 实时灾备，用于切换故障

    3. 读写分离，提供查询服务

缺点：

    1. 当主DB压力过大时，复制会延迟

    2. 当主DB宕机后，数据可能会丢失

延迟的解决方案：

    1. 写操作后的读操作指定发给数据库主服务器

    2. 读从机失败后再读一次主机

    3. 关键业务读写操作全部指向主机，非关键业务采用读写分离

数据丢失的解决方案：

采用增强半同步方式以尽量减低数据丢失率.

 

3. 分库分表

在引入了上述的主从复制+读写分离的架构之后，系统可以承担较大的并发量。但随着业务量的增长，单库单表的数据无限制的增加，单库单表的查询性能也会受到影响。与此同时，随着springcloud 和 dubbo 等微服务框架的逐渐流行，后期继续引入了分库分表的架构设计。

单库单表数据库服务器的瓶颈：

    1. 数据量太大，读写的性能会下降，即使有索引，索引也会变得很大，性能同样会下降。

    2. 数据文件会变得很大，数据库备份和恢复需要耗费很长时间。

    3. 数据文件越大，极端情况下丢失数据的风险越高（例如，机房火灾导致数据库主备机都发生故障）。

 

分库分表的方式

    1. 垂直分库分表

    垂直分表适合将表中某些不常用且占了大量空间的列拆分出去

    2. 水平分库分表

    水平分表适合表行数特别大的表

比如用户信息，常用的信息有名字，手机号等。但工作地址，生活地址这些信息，其实不经常被用到。如果将这几个信息存在同一张表里，那么每次数据库查询都会遍历这些无效的信息，无形的增加了性能的损耗。因此，我们可以采用垂直分库的方式，把名字，手机号存成一张表，把工作地址，生活地址存成另一张表。在获取用户的基础信息之后，需要获取工作地址或者生活地址时，在查询一次数据库就可以。

而随着我们的系统的注册用户越来越多，单表的数据量也会越来越大，也就会遇到上面描述的单表单库的性能问题。因此我们可以采用水平分库的方式，将用户表分成10张子表，根据用户手机号取模来决定这行数据具体存到哪张子表里面。

 

采用了分库分表之后，也就引入了一个路由的问题。即某一行数据应该存到哪个库哪个表里面去。常见的路由方法有以下几个。

    1. 范围路由:

        选取有序的数据列（例如，整形、时间戳等）作为路由的条件，不同分段分散到不同的数据库表中。以最常见的用户 ID 为例，路由算法可以按照 1000000 的范围大小进行分段，1 ~ 999999 放到数据库 1 的表中，1000000 ~ 1999999 放到数据库 2 的表中，以此类推。

        范围路由的优点是扩展方便，随着数据的增加平滑地扩充新的表。

        与此对应的，他的缺点是分布不均。假如按照 1000 万来进行分表，有可能某个分段实际存储的数据量只有 1000 条，而另外一个分段实际存储的数据量有 900 万条。

    2. Hash路由:

        选取某个列（或者某几个列组合也可以）的值进行 Hash 运算，然后根据 Hash 结果分散到不同的数据库表中。与范围路由相比，他的缺点在于扩展不便，每次修改路由都需要对数据进行重新分布，数据迁移。但他的有点在于分布均匀。

    3. 配置路由:

        配置路由就是路由表，用一张独立的表来记录路由信息。以用户 ID 为例，我们新增一张 user_router 表，这个表包含 user_id 和 table_id 两列，根据 user_id 就可以查询对应的 table_id。          配置路由的有点在于使用灵活，尤其在扩表的时候，只需要迁移部分的数据，修改配置表的路由就行。在对应的，他的缺点就是需多查一遍路由表，影响性能。而一旦为路由表分库分表，则陷入了一个路由选择的死循环

 

4. 分库分表的优缺点以及带来的问题

优点：

1.  不存在单库数据量过大、高并发的性能瓶颈，提升系统稳定性和负载能力

2.  应用端改造较小，不需要拆分业务模块

缺点：

1.    跨分片的事务一致性较难保障
2.   跨库的join关联查询性能较差

引入的问题：

(1) 事务一致性问题

    当更新内容同时分布在不同库中，不可避免会带来跨库事务问题。

    解决方案: XA，二阶段，柔性事务，TCC，最大努力(分布式事务不在本文的范围，也就不展开了)

(2) 跨节点关联查询 join 问题

    切分之后，数据可能分布在不同的节点上，此时join带来的问题就麻烦，为了性能，尽量避免使用join查询。

    解决这个问题的一些方法 a. 全局表 (将分库的数据综合成一张宽表，或同步到ES，查询走ES来提高性能)， b. 字段冗余(虽然违反了数据库范式，但可以减少一定的join，可是无法完全能避免join查询)，c. 数据组装(代码组织数据，比方先查用户名字，和用户id，在根据用户id去领一张表查用户具体信息)

(3) 跨节点分页、排序、函数问题

    跨节点多库进行查询时，会出现limit分页、order by排序等问题。分库分表之后，例如要获取前十行数据，需要在每个库的每个表中都获取10条数据，分库分表中间件会对这些数据进行汇总计算，再排序获取前10条数据，这会对数据查询造成一定的性能影响。

(4) 全局主键重复问题

    在分库分表环境中，由于表中数据同时存在不同数据库中，主键平时使用的自增长无法确保id唯一。

    解决这个问题的常用方法：Twitter公布的snowflake算法

snowflake算法的结果是一个long型的ID。其核心思想是：使用41bit作为毫秒数，10bit作为机器的ID（5个bit是数据中心，5个bit的机器ID），12bit作为毫秒内的流水号（意味着每个节点在每毫秒可以产生 4096 个 ID），最后还有一个符号位，永远是0。

 

5. 分库分表的中间件

分库分表的中间件可以分为两大类。一类是 client模式的中间件，另一类是proxy模式的中间件。

Client模式如下：

一般以一个jar包的形式，嵌入到我们的application中，并且直连数据库。业界常用的有当当开源的Sharding-Jdbc，以及我们公司内部的TDDL。

 

Proxy模式

代理模式需要独立部署proxy应用。而我们的application也不直连数据库，而是通过代理应用去连接。常见的有Mycat，Cobar，Atlas。

无论是Client模式，还是Proxy模式，几个核心的步骤都是一样的：SQL解析，重写，路由，执行，结果归并。

以Sharding-Jdbc的实现做一个例子：

当Sharding-JDBC接受到一条SQL语句时，会陆续执行 SQL解析 => 查询优化 => SQL路由 => SQL改写 => SQL执行 =>结果归并 ，最终返回执行结果。

1. SQL解析过程分为词法解析和语法解析。 词法解析器用于将SQL拆解为不可再分的原子符号，称为Token。并根据不同数据库方言所提供的字典，将其归类为关键字，表达式，字面量和操作符。 再使用语法解析器将SQL转换为抽象语法树。

2. SQL路由就是把针对逻辑表的数据操作映射到对数据结点操作的过程。

3. 工程师面向逻辑表书写的SQL，并不能够直接在真实的数据库中执行，SQL改写用于将逻辑SQL改写为在真实数据库中可以正确执行的SQL

4.cSharding-JDBC采用一套自动化的执行引擎，负责将路由和改写完成之后的真实SQL安全且高效发送到底层数据源执行

5. 将从各个数据节点获取的多数据结果集，组合成为一个结果集并正确的返回至请求客户端，称为结果归并。

结束语：

近期对mysql的一些知识点进行了总结，对mysql的主从，分库分表也有了新的认识。但同时也发现自己对这方面还存在很多的盲点。比如主从复制的组复制，并没有在本文中体现。比方说各分库分表中间件的对比以及实际的使用(除sharding-jdbc外)，也没有具体的实操过(TDDL暂时还没有实际的分库分表的操作)。今后需要继续完善这个文章。