MyCat实现分库分表_mycat分库分表

一、垂直拆分——分库

一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面，如下图：

系统被切分成了，用户，订单交易，支付几个模块。

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

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

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>
                <!-- can have multi write hosts -->
                <!-- 可写的服务器 -->
                <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>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.121.141:3306" user="mycat" 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

2.2 新增两个空白库

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

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

2.3 启动Mycat

./mycat console

2.4 访问Mycat进行分库

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

二、水平拆分——分表

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

1.实现分表

1.1 选择要拆分的表

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

1.2 分表字段

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

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

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

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

<?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>
              <!--  把orders表拆分开 mod_rule 里定义了拆分规则-->
              <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>
                <!-- can have multi write hosts -->
                <!-- 可写的服务器 -->
                <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>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.121.141:3306" user="mycat" 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

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">
         <!-- how many data nodes -->
         <property name="count">2</property>
</function>
1
2
3
4
5
6
7
8
9
10
11
12

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
2
3
4
5
6
7

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);
1
2
3
4
5
6
7

在mycat、dn1、dn2中查看orders表数据，分表成功

2.Mycat 的分片 join

Orders订单表已经进行分表操作了，和它关联的orders_detail订单详情表如何进行join查询。
我们要对orders_detail也要进行分片操作。Join的原理如下图：

2.1 E-R表

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

<?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>
              <!--  把orders表拆分开 mod_rule 里定义了拆分规则-->
              <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>
                <!-- can have multi write hosts -->
                <!-- 可写的服务器 -->
                <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>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.121.141:3306" user="mycat" 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

导入配置文件，重启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);
1
2
3
4
5
6

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

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

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>
…
1
2
3
4
5
6

在dn2创建dict_order_type表

CREATE TABLE dict_order_type(
    id INT AUTO_INCREMENT,
    order_type VARCHAR(200),
    PRIMARY KEY(id)
);
1
2
3
4
5

导入配置文件，重启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');
1
2

在Mycat、dn1、dn2中查询表数据

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 位二进制的累加。

优点：配置简单
缺点：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;
1
2
3
4
5
6

查询数据

SELECT * FROM MYCAT_SEQUENCE;
1

清空数据

TRUNCATE TABLE MYCAT_SEQUENCE;
1

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 $$

1
2
3
4
5
6
7
8
9
10
11

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 $$
1
2
3
4
5
6
7
8
9

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 $$
1
2
3
4
5
6
7
8

初始化序列表记录

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

2.修改Mycat配置
修改sequence_db_conf.properties
vim sequence_db_conf.properties
意思是 ORDERS这个序列在dn1这个节点上，具体dn1节点是哪台机子，请参考schema.xml

修改server.xml
vim server.xml
16行#全局序列类型：0-本地文件，1-数据库方式，2-时间戳方式。此处应该修改成1。

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

去掉autoIncrement
重启Mycat

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

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

查询数据

重启Mycat后，再次插入数据，再查询(模拟Mycat备机上线)

总结：

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