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。
主从复制的具体流程如下：
- 主库db的更新事件(update、insert、delete)被写到binlog
- 主库创建一个binlog dump thread，把binlog的内容发送到从库
- 从库启动并发起连接，连接到主库。从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
- 从库启动之后，创建一个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暂时还没有实际的分库分表的操作)。今后需要继续完善这个文章。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
