Mysql集群和一主多从之后如何分库分表的方案实现（三）

深碍√TFBOYSˉ_ 2022-06-03 00:37 335阅读 0赞

## 4-3、使用MyCat配置横向拆分 ##

之前文章中我们介绍了如何使用MyCat进行读写分离，类似的关系型数据库的读写分离存储方案可以在保持上层业务系统透明度的基础上满足70%业务系统的数据承载规模要求和性能要求。比起单纯使用LVS + Replicaion的读写分离方案而言最大的优势在于更能增加对上层业务系统的透明性。当然如果   
您觉得单个MyCat节点在高可用范畴或者性能范畴上还需要增强，还可以使用Keepalived、LVS等组件在多个MyCat节点上组成高可用集群或者负载集群。

但是这个方案也有一个明显的问题，那就是它没有解决数据存储规模的瓶颈。如果单个节点上某个单表的数据规模超过了千万级，那么这个节点的读操作也会产生性能瓶颈。所以我们还需要进一步使用MyCat的分片技术对业务数据表进行横向拆分。

![这里写图片描述][20161123094622322]

要说清楚MyCat对横向拆分的支持，就首先要说清楚关系型数据库横向拆分所面临的主要问题，以及MyCat为了解决这些问题所作的努力。总的来说横向拆分的所面临的问题主要分为两大类，一类是数据读的问题一类是数据写的问题。本节我们首先分析讨论一下数据读的问题，后文中介绍MyCat对分布式事务的支持时我们再来讨论横向拆分时的数据写问题。

### 4-3-1、数据分片中的数据读操作问题 ###

select TableA.*,TableB.xname,TableC.xcode from TableA left join TableB on TableB.id = TableA.b_codeid left join TableC on TableC.a_id = TableA.id where TableA.groupname = 'XXXX'
       
        
         1
         2
         3
         4

以上查询语句是我们在业务系统数据查询的过程中经常使用的一种查询类型，是一种多个数据表进行左外连接的查询语句。其中TableA业务表拥有大量的数据且变化频率非常高，是需要进行拆分的主要数据表；TableB业务表可能是一张字典表，虽然它有比较大的数据，但远远没有达到千万级别并且变化频率很低（每天最多有10000次数据写操作）；TableC业务表中的数据量也很大，从技术角度上说该业务表可以做拆分也可以不做拆分，其中的TableC.a\_id字段和TableA.id字段也是一种弱关联，也就是说TableC中的业务数据就算没有关联TableA中的业务数据也可以相对独立的工作。当然以上说的是一种可能的业务数据状态，实际情况还可能更复杂。

如果这些业务表同在一个数据库中，甚至是存在于同一个MySQL实例的不同数据库中，那么执行以上查询语句基本上没有什么难度，技术人员使用MySQL的执行计划也可以很清晰的看到查询语句的执行过程：

![这里写图片描述][20161122093816174]

但是如果在分库状态下，那么查询过程就没有这么简单了。首先来说，主要需要进行数据拆分处理的TableA中的数据分布在不同的数据库中，这些数据库工作在不同的MySQL实例上。另外业务表TableC中的数据也进行了拆分，但是拆分时并没有参考和其可能有关联的TableA中的业务数据存储分片情况，也就是说原来已有的关联在拆分存储后可能就消失了，而且即使拆分后的数据关联还存在，但拆分前和拆分后执行数据排序操作的结构也可能是不同的。至于字典表TableB，由于可预见的时间内数据总规模不大，所以可以不进行拆分——所有拆分后的数据库中TableB数据表的数据内容完全一样。下图展示了一种数据表中数据进行随机拆分后可能的存储结构和产生的问题：

![这里写图片描述][20161122100013100]

这样来看，数据表横向拆分过程中至少需要考虑以下读操作问题：

*  横向拆分后数据表之间的逻辑关联问题：数据表间存在各种关联，有的关联甚至还存在外键约束。数据拆分后的关联关系应该和拆分前的关联关系保持一致，至少应该保证通过数据库中间件查询得到的数据关联结果和拆分前的关联结果保持一致。
 *  横向拆分后数据的排序和分页问题：由于数据拆分后，排序动作会分别在各个拆分后的数据库中单独执行，这可能就会导致拆分后的排序和分页结果和拆分前的结果不一致。那么数据库中间件需要保证能够将这些结果集合进行整合并还原成拆分前的排序和分页结果。
 *  横向拆分后数据的分组操作问题：分组和统计操作同样存在和以上描述类似的问题，各个拆分后的数据库将单独执行分组和统计，这就可能导致用一个分区条件在各个拆分数据库中都有分组和统计结果。数据库中间件还是需要保证能够合并这些分组统计结果，并保证它们和拆分前的数据库操作结果一致。

### 4-3-2、全局表 ###

在数据库的横向拆分过程中，各种数据字典表基本上不需要进行拆分。这是因为这些数据表的数据规模都不会太大且变化频率较低，另外一个原因是减少横向拆分后表关联操作的难度。类似省市县信息、手机区号信息、功能菜单信息等数据都应算作字典数据。根据实际工作经验，并不会出现所有业务表的数据规模都达到或超过千万级规模，只有部分关键业务表的数据规模会出现这样的情况，基于这样的情况只有这些业务表和与它们直接关联的部分数据表需要进行数据拆分设计。

MyCat数据库中间件中为除了以上情况外，各个不需要进行数据拆分的数据表提供了一种冗余复制方案：全局表。如果一张业务表在schema.xml配置文件配设置成了全局表，那么MyCat将在涉及这张业务表的所有分片节点上保持这张数居表中数据完全一致。Mycat在Join操作中，业务表与全局表进行Join聚合会优先选择相同分片内的全局表join，避免跨库 Join；在进行数据插入操作时，MyCat将把数据分发到全局表对应的所有分片执行，在进行数据读取时候将会随机获取一个节点读取数据。schema.xml配置文件中的全局表配置类似如下：

...... <schema ......> # 请注意这里的type属性，属性值为“global”，代表全局表 # 这样，在dn1和dn2两个分片节点中的t_area业务表中，其数据将保持完全一致。 <table name="t_area" primaryKey="id" type="global" dataNode="dn1,dn2" /> </schema> ......
       
        
         1
         2
         3
         4
         5
         6
         7

### 4-3-3、分片表 ###

为了在表关联查询性能和表关联处理难易程度之间取得平衡，MyCat提供了两种分片表类型和多种分片规则。对于业务关联较为独立的需要进行数据分片的业务表可以采用普通分片。然而有一类情况是，需要进行数据分片的业务表有一些非常重要的关联数据也同时需要进行分片，例如订单（order）数据表和订单明细（order\_detail）数据表。很明显订单数据和订单明细数据是经常需要进行关联查询的，并且既然订单数据达到了一定的规模需要进行数据分片，那么只会比它数据量更大的订单明细表也同时需要进行分片。在这样的关联分片情况下，MyCat需要保证订单明细A1、A2、A3、A4数据能够正确的写入到他们关联的订单信息A所在的分片上。这样才能保证订单A在join查询订单明细时，向请求者返回正确的查询结果。MyCat提供的这种分片模式称为ER分片/智能分片。

在后续4-4、4-5和4-6节中，本文将和读者一起来讨论MyCat中支持数据分片的两种关键分片表类型，普通分片和智能分片。我们还会一起讨论MyCat中主要支持的数据分片规则，包括mod-long、partbymonth、rang-mod、rang-long、hash-int等分片规则。MyCat还支持开发人员自定义分片规则，这个自定义方式也会进行介绍。

### 4-3-4、Share join和catlet（人工智能） ###

MyCat还向技术人员提供了两种不同分片的查询汇总功能，其中Share join是一个简单的跨分片Join方式，目前支持 2 个表的 join,原理就是解析 SQL 语句，拆分成单表的 SQL 语句执行，然后把各个节点的数据汇   
集；另外一种catlet人工智能分片查询功能，是将Join查询语句分析后，从指定分片提取查询结果的前半部分，然后将查询结果送入其它分片以便可以结合到这个结果所关联的其他数据。Share join查询的做法和人工智能分片查询的做法往往无法实现高性能处理，所以这两种不同分片的数据关联查询方式只适合开发人员使用，不建议在生产环境中使用。

## 4-4、普通分片场景示例 ##

数据表普通分片是比较好理解的概念，即是说一个拥有相对独立业务的数据表，按照一定的拆分规则将数据分别存储在若干个独立的数据库中的操作。能够进行这种分片操作的数据表的特点是，业务耦合度一般较低或者属于基础性功能模块；这种数据表也可能存在和其它数据的关联，但是关联的是一个或者多个字典数据表；即使这种数据表存在直接关联的其它业务数据，那么后者的数据规模和变化频率也不会在可预见的时间内进行数据分片操作。这种场景在实际业务中是比较常见的，典型的就是用户基础信息：

![这里写图片描述][20161122194050002]

在产品第N次迭代时，考虑了后续几个月内注册用户量将突破1000万大关，且半年内将继续成几何级增长。这时架构师就必须考虑对“用户中心子系统”中用户基本信息进行分库处理。用户基本信息快速迁移/割接的问题很好解决，由于目前用户基本信息只有百万左右，所以可以考虑在每个分片库先做整体冗余，然后再后续运维工作中再进行数据清扫。也可以在最初阶段就考虑合适的分片规则，保证这几百万数据在后续存储方案升级中将可以作为整个MySQL分库分表集群的第一个分片节点组（后文在讲解分片规则时会详细讲到）“用户中心子系统”中我们为可能的5000万用户数据规模规划了5个分片，每个分片中做两组读写分离，每组读写分离包含一个写节点和二至三个读节点。并且这两个组的写节点互为主从。

![这里写图片描述][20161123104339722]

以下是schema.xml主配置文件中重要的设置内容：

<mycat:schema xmlns:mycat="http://io.mycat/">
        
        <schema name="usercenterSchema" checkSQLschema="false" sqlMaxLimit="200">
            
            <table name="dictionaryA" primaryKey="Id" type="global" dataNode="dn1,dn2,dn3,dn4,dn5" />
            <table name="dictionaryB" primaryKey="Id" type="global" dataNode="dn1,dn2,dn3,dn4,dn5" />
            <table name="dictionaryC" primaryKey="Id" type="global" dataNode="dn1,dn2,dn3,dn4,dn5" />
            <table name="dictionaryD" primaryKey="Id" type="global" dataNode="dn1,dn2,dn3,dn4,dn5" />
            
            <table name="usertable" primaryKey="Id" dataNode="dn1,dn2,dn3,dn4,dn5" rule="mod-long"/>
        </schema>
    
        
        <dataNode name="dn1" dataHost="dataHost1" database="stacks" />
        <dataNode name="dn2" dataHost="dataHost2" database="stacks" />
        <dataNode name="dn3" dataHost="dataHost3" database="stacks" />
        <dataNode name="dn4" dataHost="dataHost4" database="stacks" />
        <dataNode name="dn5" dataHost="dataHost5" database="stacks" />
    
        
        <dataHost name="dataHost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="2">
            <heartbeat>select user()</heartbeat>
            <writeHost host="dataHost1_hostM1" url="192.168.61.140:3306" user="root" password="123456">
                <readHost host="dataHost1_hostS11" url="192.168.61.141:3306" user="root" password="123456"/>
                <readHost host="dataHost1_hostS12" url="192.168.61.142:3306" user="root" password="123456"/>
            </writeHost>
            <writeHost host="dataHost1_hostM2" url="192.168.61.150:3306" user="root" password="123456">
                <readHost host="dataHost1_hostS21" url="192.168.61.151:3306" user="root" password="123456"/>
                <readHost host="dataHost1_hostS22" url="192.168.61.152:3306" user="root" password="123456"/>
            </writeHost>
        </dataHost>
        .......
    </mycat:schema>
       
        
         1
         2
         3
         4
         5
         6
         7
         8
         9
         10
         11
         12
         13
         14
         15
         16
         17
         18
         19
         20
         21
         22
         23
         24
         25
         26
         27
         28
         29
         30
         31
         32
         33
         34
         35
         36
         37
         38
         39
         40
         41
         42
         43
         44
         45
         46
         47

以上配置示例中关于分片规则的部分（table标签的rule属性），我们将在后文中专门进行介绍。读者在这里只需要知道“mod-long”是一种长整形取余的分片方式就可以了，另外全局表和分片表唯一的设置差别就是全局表需要明确指定type属性，而分片表不需要。很明显至少从现在的情况看，用户基本信息虽然有直接关联的数据信息，但是关联的都是字典性质的数据表，这些数据表都被设置为全局表，所以在任何分片中用户基本信息都可以找到与它正确关联的信息。最后需要再次注意的MyCat并不负责数据同步过程，所以所有节点的数据同步还需要技术人员根据顶层设计自行配置。

## 4-5、ER分片及使用限制 ##

### 4-5-1、ER分片基本使用 ###

经过上一节示例的技术迭代过程，为不久的将来线上业务系统达到5000万级别用户基本信息的数据存储规模的准备工作就完成了，但是新的要求又来了：我们需要记录最近一年时间内用户对基本信息的修改情况。这部分修改情况可能来源于另一套日志采集系统（例如一套基于Apache Flume + Apache Kafka + Apache Storm的日志数据实时采集分析平台）也可能直接来源于业务系统对数据变化的判断，这里我们并不讨论数据的来源问题，而只讨论这部分用户基本信息变化数据的存储情况——假设技术团队已经决定使用关系型数据库存储这些变化数据。

很显然用户基本信息的修改明细和用户基本信息存在很强的关联关系，且用户基本信息的修改明细也需要进行分片。当用户基本信息A进入分片数据库X时，需要和这个用户基本信息进行关联的修改明细信息也必须正确进入数据库X，这样才能保持数据关联的正确性。这是因为：

*  如果数据表存在外键约束设定，那么用户信息修改明细错误写入分片时就会导致写操作直接报错——分片数据库无法找到关联的用户信息。而使用外键约束又是明确被建议的数据库设计方式。
 *  即使数据表不存在外键约束设定，虽然用户信息修改明细可以写入和用户基本信息不一致的分片，但是在基于用户基本信息进行关联查询时就无法查询到正确的关联信息。

看来要在保持性能的前提下解决这个问题，就必须保证父级表和子级表在同时需要分片时，相关联数据能够正确写入相同的分片中，MyCat称这样的分片表为ER分片表。

![这里写图片描述][20161123152058198]

MyCat的主配置文件中使用table标签的子标签childTable对ER分片表的关系进行标识。如下示例：

......
    <table name="usertable" primaryKey="Id" dataNode="dn1,dn2" rule="mod-long">
        <childTable name="booktable" primaryKey="Id" joinKey="authorid" parentKey="Id"/>
    </table>
    ......
       
        
         1
         2
         3
         4
         5

关于table标签已经在上文中介绍过了，这里的使用方式相似。需要注意的是childTable标签的几个关键属性：

*  primaryKey属性：该属性和table标签中的primaryKey属性意义相同，表示该逻辑表对应真实表的主键。
 *  joinKey属性和parentKey属性：在进行childTable表数据插入时，MyCat会首先依据joinKey属性设置的字段拿到本次数据插入时该字段的值，然后再根据parentKey属性指定的父级Table的列信息生成查询语句，以便确定将要插入的这条数据，其父级数据在哪个分片上。

有的读者可能就要提问了：为什么不采用已设置的分片规则重新计算出数据存放的分片呢？这是因为分片规则可能会产生变化，即使分片规则没有产生变化，很多规则下作为计算基准的“可用分片数量”也可能产生了变化。了解了ER分片表的基本工作方式，我们就可以对上一节用户中心使用的普通分片场景进行调整，在其为用户基本信息修改明细配置ER分片关系，调整后的配置文件如下所示（只列出了关键的变化位置，其他全局表的设置没有变化）：

......
    
    <table name="dictionaryD" primaryKey="Id" type="global" dataNode="dn1,dn2,dn3,dn4,dn5" />
    
    <table name="usertable" primaryKey="Id" dataNode="dn1,dn2,dn3,dn4,dn5" rule="mod-long">
        
        <childTable name="usermodifyDetails" primaryKey="Id" joinKey="userid" parentKey="Id"/>
    </table>
    ......
       
        
         1
         2
         3
         4
         5
         6
         7
         8
         9
         10
         11
         12
         13

请注意，我们并没有为childTable设置分片规则和可以使用的分片节点，这是因为childTable每一条数据存储的位置是由它父级Table表中每一条数据的实际存储位置决定。通过ER分片我们可以保证类似如下的join关联语句能够在每个分片中正确执行，并被汇总到MyCat服务上。这是MyCat服务对这些分片结果进行正确的二次整合的前提条件。

# 无论是这两张数据表做怎样的join关联，都可以保证没个分片中的查询结果是正确的。
    # 如以下这种查询方法
    select usertable.*,usermodifyDetails.fieldname,usermodifyDetails.fieldnewValue from usertable
    left join usermodifyDetails on usertable.Id = usermodifyDetails.userid
    
    # 或者这种查询方法，又或者其它的只涉及这两个数据表的一对多、多对一关联查询
    select usermodifyDetails.*,usertable.Id,usertable.username from usermodifyDetails
    left join usertable on usertable.Id = usermodifyDetails.userid
       
        
         1
         2
         3
         4
         5
         6
         7
         8

===========================================================

来源：http://blog.csdn.net/yinwenjie（未经允许严禁用于商业用途！）

[20161123094622322]: /images/20220603/e01fa488db6c43b29e1ff3a11f669304.png
[20161122093816174]: /images/20220603/1f74592c137c4ce4bbf98e673dac64a0.png
[20161122100013100]: /images/20220603/6d7d37603d414ac58ea0bdd313146081.png
[20161122194050002]: /images/20220603/a8b76778999643eda4b7b137d6c1a784.png
[20161123104339722]: /images/20220603/3c6261950e0a47488dfb83af52d1e706.png
[20161123152058198]: /images/20220603/91809761b8e74118be4e4a677dea791b.png