[MySQL]从业务层面对MySQL高可用方案进行分解

         相对于传统行业的相对服务时间9x9x6或者9x12x5，因为互联网电子商务以及互联网游戏的实时性，所以服务要求7*24小时，业务架构不管是应用还是数据库，都需要容灾互备，在MySQL的体系中，最好通过在最开始阶段的数据库架构阶段来实现容灾系统。所以这里从业务宏观角度阐述下mysql架构的方方面面。

创新互联建站专业为企业提供佛坪网站建设、佛坪做网站、佛坪网站设计、佛坪网站制作等企业网站建设、网页设计与制作、佛坪企业网站模板建站服务，10年佛坪做网站经验，不只是建网站，更提供有价值的思路和整体网络服务。

一，MySQL架构设计—业务分析

（1）读多写少

虚线表示跨机房部署，比如电子商务系统，一个Master既有读也有些写，对读数据一致性需要比较重要的，读要放在Master上面。

M(R)仅仅是一个备库，只有M(WR)挂了之后，才会切换到M(R)上，这个时候M(R)就变成了读写库。比如游戏系统，有很多Salve会挂载后面一个M(R)上面。

（2）读多写少MMS-电商

如果是电子商务类型的，这种读多写少的，一般是1个master拖上4到6个slave，所有slave挂载在一个master也足够了。

切换的时候，把M1的读写业务切换到M2上面，然后把所有M1上的slave挂到M2上面去，如下所示：

（3）读多写少MMSS-游戏

如果是游戏行业的话，读非常多蛮明显的，会出现一般1个Master都会挂上10个以上的Slave的情况，所以这个时候，可以把一部分Slave挂载新的M(R)上面。至少会减少一些压力，这样至少服务器挂掉的时候，不会对所有的slave有影响，还有一部分在M(R)上的slave在继续，不会对所有的slave受到影响，见图3，

图3

（4）读少写多

        意味着读并不会影响写的效率，所以读写都可以放在一个M1(WR)，而另外一个不提供读也不提供写，只提供standby冗余异地容灾。

    这个异地容灾是非常重要的，否则如果是单机的，单边的业务，万一idc机房故障了，一般就会影响在线业务的，这种 造成业务2小时无法应用，对于在线电子商务交易来说，影响是蛮大的，所以为了最大限度的保证7*24小时，必须要做到异地容灾，MM要跨idc机房。虽然对资源有一些要求，但是对HA来说是不可缺少的，一定要有这个MM机制。

        做切换的时候，把所有的读写从M1直接切换到M2上就可以了。

（5）读写平分秋色

读和写差不多，但是读不能影响写的能力，把读写放在M1(WR)上，然后把一部分读也放在M2(R)上面，当然M1和M2也是跨机房部署的。

        切换的时候，把一部分读和全部写从M1切换到M2上就可以了。

二：MySQL架构设计—常见架构

（1）强一致性

 对读一致性的权衡，如果是对读写实时性要求非常高的话，就将读写都放在M1上面，M2只是作为standby，就是采取和上面的一（4）的读少写多的一样的架构模式。

        比如，订单处理流程，那么对读需要强一致性，实时写实时读，类似这种涉及交易的或者动态实时报表统计的都要采用这种架构模式

（2）弱一致性

如果是弱一致性的话，可以通过在M2上面分担一些读压力和流量，比如一些报表的读取以及静态配置数据的读取模块都可以放到M2上面。比如月统计报表，比如首页推荐商品业务实时性要求不是很高，完全可以采用这种弱一致性的设计架构模式。

（3）中间一致性

如果既不是很强的一致性又不是很弱的一致性，那么我们就采取中间的策略，就是在同机房再部署一个S1(R)，作为备库，提供读取服务，减少M1(WR)的压力，而另外一个idc机房的M2只做standby容灾方式的用途。

当然这里会用到3台数据库服务器，也许会增加采购压力，但是我们可以提供更好的对外数据服务的能力和途径，实际中尽可能两者兼顾。

（4）统计业务

比如PV、UV操作、页数的统计、流量的统计、数据的汇总等等，都可以划归为统计类型的业务。

数据库上做大查询的统计是非常消耗资源的。统计分为实时的统计和非实时的统计，由于mysql主从是逻辑sql的模式，所以不能达到100%的实时，如果是online要严格的非常实时的统计比如像火车票以及金融异地结算等的统计，mysql这块不是它的强项，就只有查询M1主库来实现了。

A，但是对于不是严格的实时性的统计，mysql有个很好的机制是binlog，我们可以通过binlog进行解析Parser，解析出来写入统计表进行统计或者发消息给应用端程序来进行统计。这种是准实时的统计操作，有一定的短暂的可接受的统计延迟现象，如果要100%实时性统计只有查询M1主库了。

通过binlog的方式实现统计，在互联网行业，尤其是电商和游戏这块，差不多可以解决90%以上的统计业务。有时候如果用户或者客户提出要实时read-time了，大家可以沟通一下为什么需要实时，了解具体的业务场景，有些可能真的不需要实时统计，需要有所权衡，需要跟用户和客户多次有效沟通，做出比较适合业务的统计架构模型。

B，还有一种offline统计业务，比如月份报表年报表统计等，这种完全可以把数据放到数据仓库里面或者第三方NOSQL里面进行统计。

（5）历史数据迁移

历史数据迁移，需要尽量不影响现在线上的业务，尽量不影响现在线上的查询写入操作，为什么要做历史数据迁移？因为有些业务的数据是有时效性的，比如电商中的已经完成的历史订单等，不会再有更新操作了，只有很简单的查询操作，而且查询也不会很频繁，甚至可能一天都不会查询一次。

        如果这时候历史数据还在online库里面或者online表里面，那么就会影响online的性能，所以对于这种，可以把数据迁移到新的历史数据库上，这个历史数据库可以是mysql也可以是nosql，也可以是数据仓库甚至hbase大数据等。

        实现途径是通过slave库查询出所有的数据，然后根据业务规则比如时间、某一个纬度等过滤筛选出数据，放入历史数据库(History Databases)里面。迁移完了，再回到主库M1上，删除掉这些历史数据。这样在业务层面，查询就要兼顾现在实时数据和历史数据，可以在filter上面根据迁移规则把online查询和history查询对接起来。比如说一个月之内的在online库查询一个月之前的在history库查询，可以把这个规则放在DB的迁移filter层和应用查询业务模块层。如果可以的话，还可以配置更细化，通过应用查询业务模块层来影响DB的迁移filter层，比如以前查询分为一个月为基准，现在查询业务变化了，以15天为基准，那么应用查询业务模块层变化会自动让DB的filter层也变化，实现半个自动化，更加智能一些。

（6）MySQL Sharding

像oracle这种基于rac基于共享存储的方式，不需要sharding只需要扩从rac存储就能实现了。但是这种代价相对会比较高一些，共享存储一般都比较贵，随着业务的扩展数据的爆炸式增长，你会不停累计你的成本，甚至达到一个天文数字。

目前这种share disk的方式，除了oracle的业务逻辑层做的非常完善之外其他的解决方案都还不是很完美。

Mysql的sharding也有其局限性，sharding之后的数据查询访问以及统计都会有很大的问题，mysql的sharding是解决share nothing的存储的一种分布式的方法，大体上分为垂直拆分和水平拆分。

（6.1）垂直拆分

可以横向拆分，可以纵向拆分，可以横向纵向拆分，还可以按照业务拆分。

Mysql库里面的横向拆分是指，每一个数据库实例里面都有很多个db库，每一个db库里面都有A表B表，比如db1库有A表B表，db2库里也有A表和B表，那么我们把db1、db2库的A表B表拆分出来，把一个库分成2个，就拆分成db1、db2、db3、db4，其中db1库和db2库放A表数据，db3库和db4库放B表的数据，db1、db2库里面只有A表数据，db3、db4库里面只有B表的数据。

打个比方，作为电商来说，每个库里面都有日志表和订单表，假如A表是日志表log表，B表是订单表Order表，一般说来写日志和写订单没有强关联性，我们可以讲A表日志表和B表订单表拆分出来。那么这个时候就做了一次横向的拆分工作，将A表日志表和B表订单表拆分开来放在不同的库，当然A表和B表所在的数据库名也可以保持一致(PS：在不同的实例里面)，如下图所示：

PS：这种拆分主要针对于不同的业务对表的影响不大，表之间的业务关联很弱或者基本上没有业务关联。拆分的好处是不相关的数据表拆分到不同的实例里面，对数据库的容量扩展和性能提高的均衡来说，都是蛮有好处的。

        把同一个实例上的不同的db库拆分出来，放入单独的不同实例中。这种拆分的适应场景和要求是db1和db2是没有多少业务联系的，类似6.1.2里面的A表和B表那样。如果你用到了跨库业务同时使用db1和db2的话，个人建议要重新考虑下业务，重新梳理下尽量把一个模块的表放在一个库里面，不要垮库操作。

        这种库纵向拆分里面，单独的库db1，表A和表B是强关联的。如下图所示：

PS：看到很多使用mysql的人，总是把很多没有业务关联性的表放在一个库里面，或者总是把很多个的db库放在同一个实例里面，就像使用oracle那样就一个instance的概念而已。Mysql的使用一大原则就是简单，尽量单一，简单的去使用mysql，库要严格的分开；表没有关系的，要严格拆分成库。这样的话扩展我们的业务就非常方便简单了，只需要把业务模块所在的db拆分出来，放入新的数据库服务器上即可。

有些刚起步的，开始为了快速出产品，就把所以的库所有的表都放在一个实例上，等业务发展后，就面临着数据拆分，这里就会把横向纵向拆分结合起来，一起实现，如下图所示：

        跟水平拆分有点类似，但是有不同的地方。比如一个供应商，可能整个网站上有10个供应商，一个网站上面每一个供应商都有一定的量，而且供应商之间的数据量规模都差不多的规模，那么这个时候就可以使用供应商的纬度来做拆分。

        比如usern库中，a、b、c表都是强关联的，都有完整的业务逻辑存在，这里只有用户（供应商）纬度是没有关联的，那这个时候就可以把数据以用户的纬度来进行拆分。

        就是用户1和用户2各自都有一套完整的业务逻辑，而且彼此之间不关联，所以就可以把用户1和用户2数据拆分到不同的数据库实例上面。目前很多互联网公司或者游戏公司有很多业务都是以用户纬度进行拆分的，比如qunaer、sohu game、sina等。

（6.2） 水平拆分

        水平拆分相对要简单一些，但是难度偏大，会导致分布式的情况、跨数据的情况、跨事务的情况可以分为大概三类，1是历史数据和实时数据拆分，2是单库多表拆分，3是多库多表拆分。

和历史数据迁移是一样的逻辑，就是要将online库的数据迁移到listory的数据库里面，对于实时的读写来说，数据是放在online db库里面，对于时间较远的数据来说，是放在历史History DB记录库里面的，这里的历史库可以是mysql也可以是别的nosql库等。

        主要不是解决容量问题，而是解决性能问题而扩展的，加入当前实例只有一个DB，有一个大表，一个大表就把整个实例占满了，这个时候就不能拆分db了，因为只有一个单表，这个时候我们就只能拆表了，拆表的方式主要是解决性能问题，因为单个表越大，对于mysql来说遍历表的树形结构遍历数据会消耗更多的资源，有时候一个简单的查询就可能会引起整个db的很多叶子节点都要变动。表的insert、update、delete操作都会引起几乎所有节点的变更，此时操作量会非常大，操作的时候读写性能都会很低，这个时候我们就可以考虑把大表拆分成多个小表，工作经历中是按照hash取模打散成16个小表，也有按照id主键/50取模打散到50个小表当中，下图实例是打散成2个小表。

        在单库多表的基础上，如果单库空间资源已经不足以提供业务支撑的话，可以考虑多库多表的方式来做，解决了空间问题和性能问题，不过会有一个问题就是跨库查询操作，查询就会有另外的策略，比如说加一个logic db层来实现跨库跨实例自动查询，简单如下图所示：

水平拆分原则：

-- a.尽量均匀的拆分维度。

-- b.尽量避免跨库事务。

-- c.尽量避免跨库查询。

设计：

--a根据拆分维度，做mod进行数据表拆分，大部分都是取模的拆分机制，比如hash的16模原则等。

--b根据数据容量，划分数据库拆分

数据操作

--a跨事务操作：分布式事务，通过预写日志的方式来间接地实现。

--b跨库查询：数据汇总or消息服务

u  案例：

–      按照用户维度进行拆分成64个分库，1024个分表

?      user_id%1024拆分到1024张分表中

?       每个分库中存放1024/64张分表

?       取模的时候，可以用id的最后4位数据或者3位数字来取模就可以了。

u  操作1：采用Configure DB

–      拆分之后的查询操作，做一个Configure DB，这个DB存放的是所有实例的库表的映射关系，当我APP发来有一个请求查询user1的数据，那么这个user1的数据是存放在上千个实例中的哪一个库表呢？这个关联信息就在Configure DB里面，APP先去Configure DB里面取得user1的关联系信息(比如是存放在d_01库上的t_0016表里面)，然后APP根据关联信息直接去查询对应的d_01实例的t_0016表里面取得数据。

u  操作2：采用Proxy

–      拆分之后的查询操作，做一个Proxy，APP访问Proxy，Proxy根据访问规则就可以直接路由到具体的db实例，生成新的sql去操作对应的db实例，然后通过Proxy协议进行操作把操作结果返回给APP。

–      优势是Proxy和db实例是在一个网段，这样Proxy与db实例的操作的时间是非常短的。

u  操作3：采用Data Engine

–      拆分之后的查询操作，有一个Data Engine Service，这个DES里面配置了所有数据库实例的映射关系，需要在APP应用端安装一个Agent，是同步逻辑，在JDBC层实现，DES可以实现读写分离，原理可以参考TDDL的实现。

6.3集群管理

纵向扩容：一个实例拆分成多个实例，纵向拆分比较简单，修改的东西比较少，拆分的时候要通知到Configure DB或者DES，以免拆分之后查询不到数据或者数据录入不到新的db上面，如下图所示：

横向扩容：比较复杂，在纵向扩容成2个库的基础之上，再一次对库的表进行扩容，所以需要及时通知Configure DB和DES更细库和表的路由连接信息。