MyCat实现分库分表

迷南。 2024-03-24 13:25 47阅读 0赞

#### 目录 ####

*  一、垂直拆分——分库
 *   *  1.如何划分
     *  2.实现分库
     *   *  2.1 停止mycat，停止主从，修改schema配置文件
         *  2.2 新增两个空白库
         *  2.3 启动Mycat
         *  2.4 访问Mycat进行分库
 *  二、水平拆分——分表
 *   *  1.实现分表
     *   *  1.1 选择要拆分的表
         *  1.2 分表字段
         *  1.3 停止mycat修改配置文件schema.xml
         *  1.4 停止mycat修改配置文件rule.xml
         *  1.5 在数据节点dn2上建orders表
         *  1.6 重启Mycat配置生效，将配置文件重新导入
         *  1.7 访问Mycat灌数据实现分片
     *  2.Mycat 的分片 join
     *   *  2.1 E-R表
         *  2.2 全局表
     *  3.全局序列
     *   *  3.1 本地文件
         *  3.2 时间戳方式
         *  3.3 自主生成全局序列
         *  3.4 数据库方式

## 一、垂直拆分——分库 ##

一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面，如下图：  
![在这里插入图片描述][d2d2daf448204a74918eb391b656ad55.png]  
系统被切分成了，用户，订单交易，支付几个模块。

### 1.如何划分 ###

一个问题：在两台主机上的两个数据库中的表，能否关联查询？  
答案：不可以关联查询。  
分库的原则：有紧密关联关系的表应该在一个库里，相互没有关联关系的表可以分到不同的库里。

#客户表  rows:20万 
    CREATE TABLE customer(
        id INT AUTO_INCREMENT,
        NAME VARCHAR(200),
        PRIMARY KEY(id)
    );
    #订单表   rows:600万
    CREATE TABLE orders(
        id INT AUTO_INCREMENT,
        order_type INT,
        customer_id INT,
        amount DECIMAL(10,2),
        PRIMARY KEY(id)  
    ); 
    #订单详细表  rows:600万
    CREATE TABLE orders_detail(
        id INT AUTO_INCREMENT,
        detail VARCHAR(2000),
        order_id INT,
        PRIMARY KEY(id)
    );
    #订单状态字典表   rows:20条
    CREATE TABLE dict_order_type(
        id INT AUTO_INCREMENT,
        order_type VARCHAR(200),
        PRIMARY KEY(id)
    );

以上四个表如何分库？客户表分在一个数据库，另外三张都需要关联查询，分在另外一个数据库。

### 2.实现分库 ###

#### 2.1 停止mycat，停止主从，修改schema配置文件 ####

在从机上执行 mysql> stop slave;

<?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>
            </schema>
          
            <dataNode name="dn1" dataHost="host1" database="orders" />
            <dataNode name="dn2" dataHost="host2" database="orders" />
    
    
            
            <dataHost name="host1" 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.121.140:3306" user="mycat" password="123456">
                    </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>
                    
                    <writeHost host="hostM1" url="192.168.121.141:3306" user="mycat" password="123456">
                    </writeHost>
            </dataHost>
    </mycat:schema>

#### 2.2 新增两个空白库 ####

分库操作不是在原来的老数据库上进行操作，需要准备两台机器分别安装新的数据库

在数据节点dn1、dn2上分别创建数据库orders
    CREATE DATABASE orders;

#### 2.3 启动Mycat ####

./mycat console

#### 2.4 访问Mycat进行分库 ####

#访问Mycat
    mysql -umycat -p123456 -h 192.168.121.140 -P 8066
    #切换到TESTDB
    #创建4张表 执行上述创建表的sql 语句！
    #查看表信息，可以看到成功分库

![在这里插入图片描述][71890dd892e045c09d68e1b5c0df7dcb.png]

## 二、水平拆分——分表 ##

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

### 1.实现分表 ###

#### 1.1 选择要拆分的表 ####

MySQL单表存储数据条数是有瓶颈的，单表达到1000万条数据就达到了瓶颈，会影响查询效率，需要进行水平拆分（分表）进行优化。  
例如：例子中的orders、orders\_detail都已经达到600万行数据，需要进行分表优

#### 1.2 分表字段 ####

以orders表为例，可以根据不同自字段进行分表

<table> 
 <tbody> 
  <tr> 
   <td><p><font>编号</font></p></td> 
   <td><p><font>分表字段</font></p></td> 
   <td><p><font>效果</font></p></td> 
  </tr> 
  <tr> 
   <td><p><font>1</font></p></td> 
   <td><p><font>id,createtime</font></p><p><font>（主键、或创建时间）</font></p></td> 
   <td><p><font>查询订单注重时效，历史订单被查询的次数少，如此分片会造成一个节点访问多，一个访问少，不平均。</font></p></td> 
  </tr> 
  <tr> 
   <td><p><font>2</font></p></td> 
   <td><p><b><font>customer_id</font><font>（客户</font><font>id</font><font>）</font></b></p></td> 
   <td><p><font>根据客户</font><font>id</font><font>去分，两个节点访问平均，</font><font>一个客户的所有订单都在同一个节点</font></p></td> 
  </tr> 
 </tbody> 
</table>

#### 1.3 停止mycat修改配置文件schema.xml ####

为orders表设置数据节点为dn1、dn2，并指定分片规则为mod\_rule（自定义的名字）

![在这里插入图片描述][ab6ce256b36e47a09003d59ba28b7acb.png]

<?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>
                  
                  <table name="orders" dataNode="dn1,dn2"  rule="mod_rule" ></table>
            </schema>
          
            <dataNode name="dn1" dataHost="host1" database="orders" />
            <dataNode name="dn2" dataHost="host2" database="orders" />
    
    
            
            <dataHost name="host1" 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.121.140:3306" user="mycat" password="123456">
                    </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>
                    
                    <writeHost host="hostM1" url="192.168.121.141:3306" user="mycat" password="123456">
                    </writeHost>
            </dataHost>
    </mycat:schema>

#### 1.4 停止mycat修改配置文件rule.xml ####

在rule配置文件里新增分片规则mod\_rule，并指定规则适用字段为customer\_id，  
还有选择分片算法mod-long（对字段求模运算），customer\_id对两个节点求模，根据结果分片  
新增配置算法mod-long参数count为2，两个节点

<tableRule name="mod_rule">
            <rule>
                 <columns>customer_id</columns>
                 <algorithm>mod-long</algorithm>
             </rule>
     </tableRule>
    …
    ##在原有的配置中将 3--->2即可！
    <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
             
             <property name="count">2</property>
    </function>

#### 1.5 在数据节点dn2上建orders表 ####

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

#### 1.6 重启Mycat配置生效，将配置文件重新导入 ####

#### 1.7 访问Mycat灌数据实现分片 ####

#在mycat里向orders表插入数据，INSERT时字段不能省略
    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表数据，分表成功  
![在这里插入图片描述][49402447c6414e829e31b188c6a3c755.png]

### 2.Mycat 的分片 join ###

Orders订单表已经进行分表操作了，和它关联的orders\_detail订单详情表如何进行join查询。  
我们要对orders\_detail也要进行分片操作。Join的原理如下图：  
![在这里插入图片描述][c58812a2cfb04c3da7e1dabbdaf2e59d.png]

#### 2.1 E-R表 ####

子表的记录与所关联的父表记录存放在同一个数据分片上

![在这里插入图片描述][aed2c5a16aff4f91b74a8fbebb081ee2.png]

<?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>
                  
                  <table name="orders" dataNode="dn1,dn2"  rule="mod_rule">
                         <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
                 </table>
                 
            </schema>
          
            <dataNode name="dn1" dataHost="host1" database="orders" />
            <dataNode name="dn2" dataHost="host2" database="orders" />
    
    
            
            <dataHost name="host1" 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.121.140:3306" user="mycat" password="123456">
                    </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>
                    
                    <writeHost host="hostM1" url="192.168.121.141:3306" user="mycat" password="123456">
                    </writeHost>
            </dataHost>
    </mycat:schema>

导入配置文件，重启Mycat  
访问Mycat向orders\_detail表插入数据

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);

在mycat、dn1、dn2中运行两个表join语句

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

![在这里插入图片描述][e85ac7b38c0d452980e9d48f29db0f63.png]

#### 2.2 全局表 ####

在分片的情况下，当业务表因为规模而进行分片以后，业务表与这些附属的字典表之间的关联，就成了比较棘手的问题，考虑到字典表具有以下几个特性：  
1.变动不频繁  
2.数据量总体变化不大  
3.数据规模不大，很少有超过数十万条记录  
鉴于此，Mycat 定义了一种特殊的表，称之为“全局表”，全局表具有以下特性：  
4.全局表的插入、更新操作会实时在所有节点上执行，保持各个分片的数据一致性  
5.全局表的查询操作，只从一个节点获取  
6.全局表可以跟任何一个表进行 JOIN 操作  
将字典表或者符合字典表特性的一些表定义为全局表，则从另外一个方面，很好的解决了数据 JOIN 的难题。通过全局表+基于 E-R 关系的分片策略，Mycat 可以满足 80%以上的企业应用开发!

**修改schema.xml配置文件**

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

在dn2创建dict\_order\_type表

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

导入配置文件，重启Mycat  
访问Mycat向dict\_order\_type表插入数据

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

在Mycat、dn1、dn2中查询表数据  
![在这里插入图片描述][4d3e054f54464869a2daf8cc426418c0.png]

### 3.全局序列 ###

在实现分库分表的情况下，数据库自增主键已无法保证自增主键的全局唯一。  
为此，Mycat 提供了全局 sequence，并且提供了包含本地配置和数据库配置等多种实现方式

#### 3.1 本地文件 ####

此方式 Mycat 将 sequence 配置到文件中，当使用到 sequence 中的配置后，Mycat 会更下  
classpath 中的 sequence\_conf.properties 文件中 sequence 当前的值。  
/usr/local/mycat/conf/sequence\_conf.properties  
优点：本地加载，读取速度较快  
缺点：抗风险能力差，Mycat所在主机宕机后，无法读取本地文件。

#### 3.2 时间戳方式 ####

全局序列ID= 64 位二进制 (42(毫秒)+5(机器 ID)+5(业务编码)+12(重复累加) 换算成十进制为 18 位数的 long 类型，每毫秒可以并发 12 位二进制的累加。  
![在这里插入图片描述][ce8cdefaeb6843f08ad1cfc094914690.png]  
优点：配置简单  
缺点：18位ID过长

#### 3.3 自主生成全局序列 ####

可在java项目里自己生成全局序列，如下：  
根据业务逻辑组合  
可以利用 redis的单线程原子性 incr来生成序列  
但，自主生成需要单独在工程中用java代码实现，增加了分布式项目的复杂性

#### 3.4 数据库方式 ####

**1.建库序列脚本**  
在dn1主机上创建全局序列表

CREATE TABLE MYCAT_SEQUENCE (
    NAME VARCHAR(50) NOT NULL,
    current_value INT NOT NULL,
    increment INT NOT NULL DEFAULT 100, 
    PRIMARY KEY(NAME)) 
    ENGINE=INNODB;

查询数据

SELECT * FROM MYCAT_SEQUENCE;

清空数据

TRUNCATE TABLE MYCAT_SEQUENCE;

truncate : 表示直接情况所有数据，不留任何痕迹！使用回滚无效！

**创建全局序列所需函数：官方提供**

DELIMITER $$ 
    CREATE FUNCTION mycat_seq_currval(seq_name VARCHAR(50)) RETURNS VARCHAR(64)
    DETERMINISTIC  
    BEGIN
    DECLARE retval VARCHAR(64);
    SET retval="-999999999,null";
    SELECT CONCAT(CAST(current_value AS CHAR),",",CAST(increment AS CHAR)) INTO retval FROM
    MYCAT_SEQUENCE WHERE NAME = seq_name;
    RETURN retval;
    END $$

DELIMITER $$
    CREATE FUNCTION mycat_seq_setval(seq_name VARCHAR(50),VALUE INTEGER) RETURNS VARCHAR(64)
    DETERMINISTIC
    BEGIN
    UPDATE MYCAT_SEQUENCE
    SET current_value = VALUE
    WHERE NAME = seq_name;
    RETURN mycat_seq_currval(seq_name);
    END $$

DELIMITER $$
    CREATE FUNCTION mycat_seq_nextval(seq_name VARCHAR(50)) RETURNS VARCHAR(64) 
    DETERMINISTIC
    BEGIN
    UPDATE MYCAT_SEQUENCE
    SET current_value = current_value + increment WHERE NAME = seq_name;
    RETURN mycat_seq_currval(seq_name);
    END $$

初始化序列表记录

INSERT INTO MYCAT_SEQUENCE(NAME,current_value,increment) VALUES ('ORDERS', 400000,100);

**2.修改Mycat配置**  
修改sequence\_db\_conf.properties  
vim sequence\_db\_conf.properties  
意思是 ORDERS这个序列在dn1这个节点上，具体dn1节点是哪台机子，请参考schema.xml  
![在这里插入图片描述][c6345b5755f24064977cc45d1d394141.png]  
修改server.xml  
vim server.xml  
16行\#全局序列类型：0-本地文件，1-数据库方式，2-时间戳方式。此处应该修改成1。  
![在这里插入图片描述][1ecfa3d14fa2455f91d18439e9cbbd54.png]

去掉autoIncrement  
重启Mycat

**3.验证全局序列**  
登录Mycat，插入数据

insert into orders(id,amount,customer_id,order_type) 
    values(next value for MYCATSEQ_ORDERS,1000,101,102);

查询数据  
![在这里插入图片描述][3bd5689af1fc4cf782ee23aef2f1a884.png]

重启Mycat后，再次插入数据，再查询(模拟Mycat备机上线)  
![在这里插入图片描述][dcf206b207f840df8a89e8ac89ba8b9c.png]

**总结：**

*  利用数据库一个表来进行计数累加。但是并不是每次生成序列都读写数据库，这样效率太低。
 *  Mycat会预加载一部分号段到Mycat的内存中，这样大部分读写序列都是在内存中完成的。
 *  如果内存中的号段用完了 Mycat会再向数据库要一次。
 *  问：如果Mycat崩溃了 ，内存中的序列岂不是都没了？
 *  是的。如果是这样，那么Mycat启动后会向数据库申请新的号段，原有号段会弃用。
 *  也就是说如果Mycat重启，那么损失是当前的号段没用完的号码，但是不会因此出现主键重复。

[d2d2daf448204a74918eb391b656ad55.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/f7264d9961be4dffb61312cc152b16b6.png
[71890dd892e045c09d68e1b5c0df7dcb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/20b983df11934025be0bd954dca074ad.png
[643d2dbb2fcf4dbf8f8beda60a9a2ed4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/fae19602fbe443368d2e23695fa433a5.png
[ab6ce256b36e47a09003d59ba28b7acb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/1d54d7fa790349658ec36ed154313dc6.png
[49402447c6414e829e31b188c6a3c755.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/5ee9f7f452e34aafb2c4277fa5175c6e.png
[c58812a2cfb04c3da7e1dabbdaf2e59d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/3cb00c11c70f43f18c85c31c2d5af129.png
[aed2c5a16aff4f91b74a8fbebb081ee2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/0c984db5a15a451a8e03a947e1220f51.png
[e85ac7b38c0d452980e9d48f29db0f63.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/273ec258f8de4051a48a39011530cf18.png
[4d3e054f54464869a2daf8cc426418c0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/d3d4dbc88e7044da89958662bb690b72.png
[ce8cdefaeb6843f08ad1cfc094914690.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/526a0b44806a4ae28dda06d46dd1fa7a.png
[c6345b5755f24064977cc45d1d394141.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/d0f62b306fc74b149e349dcce820c67f.png
[1ecfa3d14fa2455f91d18439e9cbbd54.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/1e6dd87b04724e1f885ee86b63f220cd.png
[3bd5689af1fc4cf782ee23aef2f1a884.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/a3686e7fafcb4ff2ba40130bd106883c.png
[dcf206b207f840df8a89e8ac89ba8b9c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/e0af521772a34d4fa06a7b81da8e2d67.png