Mycat（3）——Mycat水平拆分（分表）

一、水平切分

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

拆分数据就需要定义分片规则。关系型数据库是行列的二维模型，拆分的第一原则是找到拆分维度。比如：

• 从会员的角度来分析，商户订单交易类系统中查询会员某天某月某个订单，那么就需要按照会员结合日期来拆分，不同的数据按照会员 ID 做分组，这样所有的数据查询 join 都会在单库内解决；
• 如果从商户的角度来讲，要查询某个商家某天所有的订单数，就需要按照商户 ID 做拆分；
• 但是如果系统既想按会员拆分，又想按商家数据，则会有一定的困难。如何找到合适的分片规则需要综合考虑衡量。

几种典型的分片规则包括：

• 按照用户 ID 求模，将数据分散到不同的数据库，具有相同数据用户的数据都被分散到一个库中；
• 按照日期，将不同月甚至日的数据分散到不同的库中；
• 按照某个特定的字段求摸，或者根据特定范围段分散到不同的库中。

如图，切分原则都是根据业务找到适合的切分规则分散到不同的库，下面用用户 ID 求模举例：

优点：
• 拆分规则抽象好，join 操作基本可以数据库做；
• 不存在单库大数据，高并发的性能瓶颈；
• 应用端改造较少；
• 提高了系统的稳定性跟负载能力。
缺点：
• 拆分规则难以抽象；
• 分片事务一致性难以解决；
• 数据多次扩展难度跟维护量极大；
• 跨库 join 性能较差。

前面讲了垂直切分跟水平切分的不同跟优缺点，会发现每种切分方式都有缺点，但共同的特点缺点有：
• 引入分布式事务的问题；
• 跨节点 Join 的问题；
• 跨节点合并排序分页问题；
• 多数据源管理问题

——上述内容摘自《Mycat权威指南》

二、实现分表

以orders表为例：

CREATE TABLE orders(
     id INT AUTO_INCREMENT,
     order_type INT,
     customer_id INT,
     amount DECIMAL(10,2),
     PRIMARY KEY(id) 
     );

在dn1和dn2中分别创建orders表。对于orders可以根据不同的字段进行分表：

分表字段	结果
id	查询订单注重实效，历史订单查询较少，会造成节点访问不平均。
coustomer_id	根据客户id去分表，一个客户的所有订单在一个节点，访问较为平均

（一）修改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="customer" dataNode="dn2" />
           <table name="orders" dataNode="dn1,dn2" rule="mod_rule">
           </table>
        </schema>
        <dataNode name="dn1" dataHost="localhost1" database="orders" />
        <dataNode name="dn2" dataHost="localhost2" database="orders" />
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="192.168.30.130:3306" user="root" password="root">
                </writeHost>
        </dataHost>
          <dataHost name="localhost2" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM2" url="192.168.30.131:3306" user="root" password="root">
                </writeHost>
        </dataHost>
</mycat:schema>

在schema节点下新增table节点：

 <table name="orders" dataNode="dn1,dn2" rule="mod_rule">

（二）修改rule.xml

增加：

  <tableRule name="mod_rule">
               <rule>
                       <columns>customer_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">2</property>
        </function>

(三）测试分表

启动mycat:

./mycat console

登录mycat：

mysql -uroot -p123456 -P 8066 -h 192.168.30.130

切换到TESTDB，插入数据：

INSERT INTO orders(id,order_type,customer_id,amount) VALUES(1,101,100,100100);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(2,101,100,100300);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(3,101,101,120000);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(4,101,101,103000);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(5,102,101,100400);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(6,102,100,100020);

分别在mycat、dn1、dn2中查询orders，可以看到customer_id为100的在dn1中，customer_id为101的在dn2中。

三、分片的JOIN

（一）理论

• 尽量避免使用 Left join 或 Right join,而用 Inner join
• 在使用 Left join 或 Right join 时，ON 会优先执行，where 条件在最后执行，所以在使用过程中，条件尽可能的在 ON 语句中判断，减少 where 的执行
• 少用子查询，而用 join。

MyCAT 借鉴了 NewSQL 领域的新秀 Foundation DB 的设计思路，Foundation DB 创新性的提出了 TableGroup 的概念，其将子表的存储位置依赖于主表，并且物理上紧邻存放，因此彻底解决了 JION 的效率和性能问题，根据这一思路，提出了基于 E-R 关系的数据分片策略，子表的记录与所关联的父表记录存放在同一个数据分片上。

（二）测试

在dn1和dn2中新建orders_detail：

CREATE TABLE orders_detail(
     id INT AUTO_INCREMENT,
     detail VARCHAR(2000),
     order_id INT,
     PRIMARY KEY(id)
     );

1. 修改schema.xml

<table name="orders" dataNode="dn1,dn2" rule="mod_rule">
   <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id"/>
</table>

2.使用mycat插入数据

INSERT INTO orders_detail(id,detail,order_id) VALUES(1,'detail1',1);
INSERT INTO orders_detail(id,detail,order_id) VALUES(2,'detail1',2); 
INSERT INTO orders_detail(id,detail,order_id) VALUES(3,'detail1',3); 
INSERT INTO orders_detail(id,detail,order_id) VALUES(4,'detail1',4); 
INSERT INTO orders_detail(id,detail,order_id) VALUES(5,'detail1',5); 
INSERT INTO orders_detail(id,detail,order_id) VALUES(6,'detail1',6);

3. 验证

在mycat、dn1、dn2中分别执行：

select o.*,od.detail from orders o inner join orders_detail od on o.id=od.order_id;

四、全局表

（一）理论

一个真实的业务系统中，往往存在大量的类似字典表的表格，它们与业务表之间可能有关系，这种关系，可以理解为“标签”，而不应理解为通常的“主从关系”，这些表基本上很少变动，可以根据主键 ID 进行缓存。

在分片的情况下，当业务表因为规模而进行分片以后，业务表与这些附属的字典表之间的关联，就成了比较棘手的问题，考虑到字典表具有以下几个特性：

• 变动不频繁
• 数据量总体变化不大
• 数据规模不大，很少有超过数十万条记录。

鉴于此，MyCAT 定义了一种特殊的表，称之为“全局表”，全局表具有以下特性：

• 全局表的插入、更新操作会实时在所有节点上执行，保持各个分片的数据一致性
• 全局表的查询操作，只从一个节点获取
• 全局表可以跟任何一个表进行 JOIN 操作

将字典表或者符合字典表特性的一些表定义为全局表，则从另外一个方面，很好的解决了数据 JOIN 的难题。通过全局表+基于 E-R 关系的分片策略，MyCAT 可以满足 80%以上的企业应用开发。

（二）测试全局表

在dn1和dn2中新建订单字典表：

CREATE TABLE dict_order_type(
     id INT AUTO_INCREMENT,
     order_type VARCHAR(200),
     PRIMARY KEY(id)
     );

修改schema.xml，新增：

<table name="dict_order_type" dataNode="dn1,dn2" type="global"/>

使用mycat插入数据：

INSERT INTO dict_order_type (id,order_type) VALUES (101,'type1');
INSERT INTO dict_order_type (id,order_type) VALUES (102,'type2');

可以看到在dn1和dn2中都插入了相应数据。

五、常用分片规则

（一）取模

参见实现分表中的案例。

（二）分片枚举

修改schema.xml:

<table name="orders_ware_info" dataNode="dn1,dn2" rule="sharding-by-intfile"/>

修改rule.xml

    <tableRule name="sharding-by-intfile">
                <rule>
                        <columns>areacode</columns>
                        <algorithm>hash-int</algorithm>
                </rule>
        </tableRule>
        ...
     <function name="hash-int"
                          class="io.mycat.route.function.PartitionByFileMap">
                <property name="mapFile">partition-hash-int.txt</property>
                <property name="type">1</property>
                <property name="defaultNode">0</property>
        </function>

partition-hash-int.txt:

110=0
120=1

在function节点中：

• mapFile：标识配置文件名称
• type : 0为int型、非0为String
• defaultNode: 默认节点:小于 0 表示不设置默认节点，大于等于 0 表示设置默认节点，设置默认节点如果碰到不识别的枚举值，就让它路由到默认节点，如不设置不识别就报错。

启动mycat，建表并写入数据：

mysql在linux环境下，表名默认区分大小写。修改/etc/my.cnf,配置：lower-case-table-names=1

CREATE TABLE orders_ware_info
(
 `id` INT AUTO_INCREMENT comment '编号',
 `order_id` INT comment '订单编号',
 `address` VARCHAR(200) comment '地址',
`areacode` VARCHAR(20) comment '区域编号',
PRIMARY KEY(id)
);
INSERT INTO ORDERS_WARE_INFO (id, order_id,address,areacode) VALUES (1,1,'北京','110');
INSERT INTO ORDERS_WARE_INFO (id, order_id,address,areacode) VALUES (2,2,'天津','120');
INSERT INTO ORDERS_WARE_INFO (id, order_id,address,areacode) VALUES (3,3,'河南','130');

其中110和130对应的数据写入了dn1,120的数据写入了dn2.

（三）范围约定

此分片适用于，提前规划好分片字段某个范围属于哪个分片。
修改schema.xml：

<table name="payment_info" dataNode="dn1,dn2" rule="sharding-by-long"/>

修改rule.xml ：

       <tableRule name="sharding-by-long">
                <rule>
                        <columns>order_id</columns>
                        <algorithm>rang-long</algorithm>
                </rule>
        </tableRule>
...
  <function name="rang-long"
                          class="io.mycat.route.function.AutoPartitionByLong">
                <property name="mapFile">autopartition-long.txt</property>
                <property name="defaultNode">0</property>
        </function>

修改autopartition-long.txt:

0-101=0
102-200=1

重启并登陆mycat，切换到TESTDB：

CREATE TABLE payment_info
(
 `id` INT AUTO_INCREMENT comment '编号',
 `order_id` INT comment '订单编号',
 `payment_status` INT comment '支付状态',
 PRIMARY KEY(id)
);

INSERT INTO payment_info (id,order_id,payment_status) VALUES (1,100,0);
INSERT INTO payment_info (id,order_id,payment_status) VALUES (2,101,1);
INSERT INTO payment_info (id,order_id ,payment_status) VALUES (3,102,0);
INSERT INTO payment_info (id,order_id,payment_status) VALUES (4,103,1);

100和101插入到dn1中，102和103插入到dn2中。

（四）按日期（天）分片

修改schema.xml:

<table name="login_info" dataNode="dn1,dn2" rule="sharding-by-date"/>

修改rule.xml:

  <tableRule name="sharding-by-date">
                <rule>
                        <columns>login_date</columns>
                        <algorithm>partbyday</algorithm>
                </rule>
        </tableRule>
        ...
          <function name="partbyday"
                          class="io.mycat.route.function.PartitionByDate">
                <property name="dateFormat">yyyy-MM-dd</property>
                <property name="sNaturalDay">0</property>
                <property name="sBeginDate">2021-01-01</property>
                <property name="sEndDate">2021-01-31</property>
                <property name="sPartionDay">10</property>
        </function>

• columns ：标识将要分片的表字段
• algorithm ：分片函数
• dateFormat ：日期格式
• sBeginDate ：开始日期
• sEndDate：结束日期
• sPartionDay ：分区天数，即默认从开始日期算起，分隔 10 天一个分区
• 如果配置了 sEndDate 则代表数据达到了这个日期的分片后循环从开始分片插入。

重启mycat，登陆后切换至TESTDB：

CREATE TABLE login_info
(
 `id` INT AUTO_INCREMENT comment '编号',
 `user_id` INT comment '用户编号',
 `login_date` date comment '登录日期',
 PRIMARY KEY(id)
);
INSERT INTO login_info(id,user_id,login_date) VALUES (1,101,'2021-01-01');
INSERT INTO login_info(id,user_id,login_date) VALUES (2,102,'2021-01-11');
INSERT INTO login_info(id,user_id,login_date) VALUES (3,103,'2021-01-21');
INSERT INTO login_info(id,user_id,login_date) VALUES (4,104,'2021-01-31');

2021-01-01 和2021-01-21插入dn1,01-11和01-31插入dn2.