mycat 分库分表

分库分表

分库：从单个数据库拆分成多个数据库的过程，将数据散落在多个数据库中。
分表：从单张表拆分成多张表的过程，将数据散落在多张表内。

为什么要分库分表？

关键字：提升性能、增加可用性。

从性能上看
随着单库中的数据量越来越大、数据库的查询QPS越来越高，相应的，对数据库的读写所需要的时间也越来越多。数据库的读写性能可能会成为业务发展的瓶颈。对应的，就需要做数据库性能方面的优化。本文中我们只讨论数据库层面的优化，不讨论缓存等应用层优化的手段。

如果数据库的查询QPS过高，就需要考虑拆库，通过分库来分担单个数据库的连接压力。比如，如果查询QPS为3500，假设单库可以支撑1000个连接数的话，那么就可以考虑拆分成多个个库，来分散查询连接压力。

如果单表数据量过大，当数据量超过一定量级后，无论是对于数据查询还是数据更新，在经过索引优化等纯数据库层面的传统优化手段之后，还是可能存在性能问题。这是量变产生了质变，这时候就需要去换个思路来解决问题，比如：从数据生产源头、数据处理源头来解决问题，既然数据量很大，那我们就来个分而治之，化整为零。这就产生了分表，把数据按照一定的规则拆分成多张表，来解决单表环境下无法解决的存取性能问题。

从可用性上看
单个数据库如果发生意外，很可能会丢失所有数据。尤其是云时代，很多数据库都跑在虚拟机上，如果虚拟机/宿主机发生意外，则可能造成无法挽回的损失。因此，除了传统的 Master-Slave、Master-Master 等部署层面解决可靠性问题外，我们也可以考虑从数据拆分层面解决此问题。

此处我们以数据库宕机为例：

单库部署情况下，如果数据库宕机，那么故障影响就是100%，而且恢复可能耗时很长。
如果我们拆分成2个库，分别部署在不同的机器上，此时其中1个库宕机，那么故障影响就是50%，还有50%的数据可以继续服务。
如果我们拆分成4个库，分别部署在不同的机器上，此时其中1个库宕机，那么故障影响就是25%，还有75%的数据可以继续服务，恢复耗时也会很短。
当然，我们也不能无限制的拆库，这也是牺牲存储资源来提升性能、可用性的方式，毕竟资源总是有限的。

如何分库分表
分库？分表？还是既分库又分表？
从第一部分了解到的信息来看，分库分表方案可以分为下面3种：

• 只分库部分表 数据库读/写QPS过高，数据库连接不足
• 只分表部分库 单表数据量过大，存储性能遇到瓶颈
• 即分库又分表 连接数不足+数据量过大引起的存储性能瓶颈
  如何选择我们自己的切分方案？
  如果需要分表，那么分多少张表合适？

由于所有的技术都是为业务服务的，那么，我们就先从数据方面回顾下业务背景。

比如，我们这个业务系统是为了解决会员的咨询诉求，通过我们的XSpace客服平台系统来服务会员，目前主要以同步的离线工单数据作为我们的数据源来构建自己的数据。

假设，每一笔离线工单都会产生对应一笔会员的咨询问题（我们简称：问题单），如果：

在线渠道：每天产生 3w 笔聊天会话，假设，其中50%的会话会生成一笔离线工单，那么每天可生成 3w * 50% = 1.5w 笔工单；
热线渠道：每天产生 2.5w 通电话，假设，其中80%的电话都会产生一笔工单，那么每天可生成 2.5w * 80% = 2w 笔/天；
离线渠道：假设离线渠道每天直接生成 3w 笔；
合计共 1.5w + 2w + 3w = 6.5w 笔/天

考虑到以后可能要继续覆盖的新的业务场景，需要提前预留部分扩展空间，这里我们假设为每天产生 8w 笔问题单。

除问题单外，还有另外2张常用的业务表：用户操作日志表、用户提交的表单数据表。

其中，每笔问题单都会产生多条用户操作日志，根据历史统计数据来可以看到，平均每个问题单大约会产生8条操作日志，我们预留一部分空间，假设每个问题单平均产生约10条用户操作日志。

如果系统设计使用年限5年，那么问题单数据量大约 = 5年 365天/年 8w/天 = 1.46亿，那么估算出的表数量如下：

问题单需要：1.46亿/500w = 29.2 张表，我们就按 32 张表来切分；
操作日志需要 ：32 10 = 320 张表，我们就按 32 16 = 512 张表来切分。

如果需要分库，那么分多少库合适？
分库的时候除了要考虑平时的业务峰值读写QPS外，还要考虑到诸如双11大促期间可能达到的峰值，需要提前做好预估。

根据我们的实际业务场景，问题单的数据查询来源主要来自于阿里客服小蜜首页。因此，可以根据历史QPS、RT等数据评估，假设我们只需要3500数据库连接数，如果单库可以承担最高1000个数据库连接，那么我们就可以拆分成4个库。

如何对数据进行切分？
根据行业惯例，通常按照 水平切分、垂直切分 两种方式进行切分，当然，有些复杂业务场景也可能选择两者结合的方式。

（1）水平切分

这是一种横向按业务维度切分的方式，比如常见的按会员维度切分，根据一定的规则把不同的会员相关的数据散落在不同的库表中。由于我们的业务场景决定都是从会员视角进行数据读写，所以，我们就选择按照水平方式进行数据库切分。

（2）垂直切分

垂直切分可以简单理解为，把一张表的不同字段拆分到不同的表中。

比如：假设有个小型电商业务，把一个订单相关的商品信息、买卖家信息、支付信息都放在一张大表里。可以考虑通过垂直切分的方式，把商品信息、买家信息、卖家信息、支付信息都单独拆分成独立的表，并通过订单号跟订单基本信息关联起来。

也有一种情况，如果一张表有10个字段，其中只有3个字段需要频繁修改，那么就可以考虑把这3个字段拆分到子表。避免在修改这3个数据时，影响到其余7个字段的查询行锁定。

分库

数据库准备

此处模拟云商品管理系统当数据库连接压力过大时进行数据库的拆分，计划拆分成两个数据库
商品库 用户广告库
根据上述业务表关联关系商品表，类别表表之间是有关联关系的所以应该放到一个数据节点上，另外两张表user ad表为了测试放到另外一个数据节点上
创建数据表到两个数据库

配置mycat

分库规则：

dn1：department部门表、user用户表、register患者挂号表
dn2：drugs 药品表、disease 疾病表
修改mycat的schema.xml重新配置分库规则：

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        <table name="drugs" dataNode="dn2"></table>
        <table name="disease" dataNode="dn2"></table>
    </schema>

    <dataNode name="dn1" dataHost="host1" database="his_mycat"/>
    <dataNode name="dn2" dataHost="host2" database="his_mycat"/>

    <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"
              slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM1" url="172.17.0.2:3306" user="root" password="root">
        </writeHost>
    </dataHost>
    <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"
              slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM2" url="172.17.0.3:3306" user="root"  password="root">
        </writeHost>
    </dataHost>

</mycat:schema>

启动mycat
在mycat/bin目录中执行

./mycat console

登录mycat创建表结构

验证
创建完成后分别在mycat客户端、dn1节点、dn2节点查看表存储情况

分表

相对于垂直拆分，水平拆分不是将表做分类，而是按照某个字段的某种规则来分散到多个库之中，每个表中 包含一部分数据。简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中的某些行切分 到一个数据库，而另外的某些行又切分到其他的数据库中。

MySQL 单表存储数据条数是有瓶颈的，单表达到 1000 万条数据就达到了瓶颈，会影响查询效率，
需要进行水平拆分（分表）进行优化。系统预测5年内表5000万条以上。解决方案是将id进行水平拆分。

分表实现(取模)

此规则为对分片字段求摸运算

<tableRule name="mod-long">
    <rule>
        <columns>user_id</columns>
        <algorithm>mod-long</algorithm>
    </rule>
</tableRule>

<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
    <!-- how many data nodes -->
    <property name="count">3</property>
</function>

上面 columns 标识将要分片的表字段，algorithm 分片函数，
此种配置非常明确即根据 id 进行十进制求模预算，相比固定分片 hash，此种在批量插入时可能存在批量插入单
事务插入多数据分片，增大事务一致性难度（因此种方式实现最简单所以优先说明）。
配置完的完整的rule.xml如下

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
    - you may not use this file except in compliance with the License. - You 
    may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
    - - Unless required by applicable law or agreed to in writing, software - 
    distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
    License for the specific language governing permissions and - limitations 
    under the License. -->
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
    <tableRule name="customer_rule">
                <rule>
                        <columns>cid</columns>
                        <algorithm>mod-long</algorithm>
                </rule>
        </tableRule>
    <tableRule name="rule1">
        <rule>
            <columns>id</columns>
            <algorithm>func1</algorithm>
        </rule>
    </tableRule>

    <tableRule name="rule2">
        <rule>
            <columns>user_id</columns>
            <algorithm>func1</algorithm>
        </rule>
    </tableRule>

    <tableRule name="sharding-by-intfile">
        <rule>
            <columns>sharding_id</columns>
            <algorithm>hash-int</algorithm>
        </rule>
    </tableRule>
    <tableRule name="auto-sharding-long">
        <rule>
            <columns>id</columns>
            <algorithm>rang-long</algorithm>
        </rule>
    </tableRule>
    <tableRule name="mod-long">
        <rule>
            <columns>id</columns>
            <algorithm>mod-long</algorithm>
        </rule>
    </tableRule>
    <tableRule name="sharding-by-murmur">
        <rule>
            <columns>id</columns>
            <algorithm>murmur</algorithm>
        </rule>
    </tableRule>
    <tableRule name="crc32slot">
        <rule>
            <columns>id</columns>
            <algorithm>crc32slot</algorithm>
        </rule>
    </tableRule>
    <tableRule name="sharding-by-month">
        <rule>
            <columns>create_time</columns>
            <algorithm>partbymonth</algorithm>
        </rule>
    </tableRule>
    <tableRule name="latest-month-calldate">
        <rule>
            <columns>calldate</columns>
            <algorithm>latestMonth</algorithm>
        </rule>
    </tableRule>

    <tableRule name="auto-sharding-rang-mod">
        <rule>
            <columns>id</columns>
            <algorithm>rang-mod</algorithm>
        </rule>
    </tableRule>

    <tableRule name="jch">
        <rule>
            <columns>id</columns>
            <algorithm>jump-consistent-hash</algorithm>
        </rule>
    </tableRule>

    <function name="murmur"
        class="io.mycat.route.function.PartitionByMurmurHash">
        <property name="seed">0</property><!-- 默认是0 -->
        <property name="count">2</property><!-- 要分片的数据库节点数量，必须指定，否则没法分片 -->
        <property name="virtualBucketTimes">160</property><!-- 一个实际的数据库节点被映射为这么多虚拟节点，默认是160倍，也就是虚拟节点数是物理节点数的160倍 -->
        <!-- <property name="weightMapFile">weightMapFile</property> 节点的权重，没有指定权重的节点默认是1。以properties文件的格式填写，以从0开始到count-1的整数值也就是节点索引为key，以节点权重值为值。所有权重值必须是正整数，否则以1代替 -->
        <!-- <property name="bucketMapPath">/etc/mycat/bucketMapPath</property> 
            用于测试时观察各物理节点与虚拟节点的分布情况，如果指定了这个属性，会把虚拟节点的murmur hash值与物理节点的映射按行输出到这个文件，没有默认值，如果不指定，就不会输出任何东西 -->
    </function>

    <function name="crc32slot"
              class="io.mycat.route.function.PartitionByCRC32PreSlot">
        <property name="count">2</property><!-- 要分片的数据库节点数量，必须指定，否则没法分片 -->
    </function>
    <function name="hash-int"
        class="io.mycat.route.function.PartitionByFileMap">
        <property name="mapFile">partition-hash-int.txt</property>
    </function>
    <function name="rang-long"
        class="io.mycat.route.function.AutoPartitionByLong">
        <property name="mapFile">autopartition-long.txt</property>
    </function>
    <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
        <!-- how many data nodes -->
        <property name="count">2</property>
    </function>

    <function name="func1" class="io.mycat.route.function.PartitionByLong">
        <property name="partitionCount">8</property>
        <property name="partitionLength">128</property>
    </function>
    <function name="latestMonth"
        class="io.mycat.route.function.LatestMonthPartion">
        <property name="splitOneDay">24</property>
    </function>
    <function name="partbymonth"
        class="io.mycat.route.function.PartitionByMonth">
        <property name="dateFormat">yyyy-MM-dd</property>
        <property name="sBeginDate">2015-01-01</property>
    </function>

    <function name="rang-mod" class="io.mycat.route.function.PartitionByRangeMod">
            <property name="mapFile">partition-range-mod.txt</property>
    </function>

    <function name="jump-consistent-hash" class="io.mycat.route.function.PartitionByJumpConsistentHash">
        <property name="totalBuckets">3</property>
    </function>
</mycat:rule>

Mycat 的分片 join

Join 绝对是关系型数据库中最常用一个特性，然而在分布式环境中,跨分片的 join 确是最复杂的，最难解决一个问题。

Mycat性能建议
尽量避免使用 Left join 或 Right join,而用 Inner join
在使用 Left join 或 Right join 时，ON 会优先执行，where 条件在最后执行，所以在使用过程中，条件尽
可能的在 ON 语句中判断，减少 where 的执行
少用子查询，而用 join。
Mycat 目前版本支持跨分片的 join,主要实现的方式有四种。
全局表，ER 分片，catletT(人工智能)和 ShareJoin，ShareJoin 在开发版中支持，前面三种方式 1.3.0.1 支持。

ER分片
MyCAT 借鉴了 NewSQL 领域的新秀 Foundation DB 的设计思路，Foundation DB 创新性的提出了 TableGroup 的概念，其将子表的存储位置依赖于主表，并且物理上紧邻存放，因此彻底解决了 JION 的效率和性能问题，根据这一思路，提出了基于 E-R 关系的数据分片策略，子表的记录与所关联的父表记录存放在同一个数据分片上。
customer(客户手机账户表) 采用 按照客户id取模 这个分片策略，分片在 spt1,spt2 上，calllog（呼叫记录表） 依赖父表进行分片，两个表的关联关系为 customer.id =calllog.phone_id。

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="spt1">
        <!-- 
                         rule="customer_rule"  自定义的 分片规则，需要在rule.xml中配置
        -->
        <table name="customer" dataNode="spt1,spt2" rule="customer_rule" >
            <!-- 
                name="calllog"          子表（从表）名称
                primaryKey="id"         子表（从表）主键
                joinKey="id"            主表主键（customer.id）
                parentKey="phone_id"    从表中记录的外键（calllog.phone_id）
            -->
            <childTable name="calllog" primaryKey="id" joinKey="id" parentKey="phone_id" />
        </table>
    </schema>

    <dataNode name="spt1" dataHost="host1" database="boss"/>
    <dataNode name="spt2" dataHost="host2" database="boss"/>

    <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"
              slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM1" url="172.17.0.2:3306" user="root" password="root">
        </writeHost>
    </dataHost>

    <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"
              slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM2" url="172.17.0.3:3306" user="root"  password="root">
        </writeHost>
    </dataHost>

</mycat:schema>

分别在mycat、数据节点spt1、spt2使用join查询

SELECT
cus.id,
cus.name ‘客户名称’,
log.type ‘通话类型’,
log.othernum ‘对方电话号’
FROM
customer cus
INNER JOIN calllog log
ON cus.id = log.phone_id
ORDER BY cus.id ;