分库

image-20201029211517334

把对customer的操作分给dn2数据库，其他表的操作分发给dn1数据库。

首先配置schema.xml

image-20201029211833323

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
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <!-- 逻辑库 绑定dataNode-->
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
                 <table name="customer" dataNode="dn2"></table>
        </schema>
        <!-- dataNode：数据节点  dataHost：数据主机 database：实际数据库-->
        <dataNode name="dn1" dataHost="host1" database="order" />
        <dataNode name="dn2" dataHost="host2" database="order" />
        
        <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <!-- 心跳测试 发送select user检测是否在线 -->
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <!-- 写主机配置 -->
                <writeHost host="hostM1" url="10.0.0.129:3306" user="root"
                                   password="kylin">
                </writeHost>
        </dataHost>

        <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <!-- 心跳测试 发送select user检测是否在线 -->
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <!-- 写主机配置 -->
                <writeHost host="hostM2" url="10.0.0.128:3306" user="root"
                                   password="kylin">
                </writeHost>
        </dataHost>
</mycat:schema>

由于我们配置了数据为order所以分别在主从机上创建order数据库（自动复制数据配置的是mydb_kylin,其他需要自己创建）

CREATE DATABASEorder;

image-20201029201541630

创建完成后，连接MyCat

mysql -u 用户名 -h 主机地址 -P 8066 -p

mysql -u mycat -h 10.0.0.128 -P 8066 -p

选择数据库,创建customer

1
2
3
4
5
6

CREATE TABLE customer(
	id INT auto_increment,
	NAME VARCHAR(200),
	PRIMARY KEY(id)
);

image-20201029212607760

运行成功，查看表所在位置。

成功分到我们所配置的表中。

image-20201029212641286

接着创建其他表

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
#订单表   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(200),
	order_id INT,
	PRIMARY key(id)
);
 
 
#订单状态字典表   rows:20
CREATE TABLE dict_order_type(
	id int auto_increment,
	order_type VARCHAR(200),
	PRIMARY key(id)
);

image-20201029212925477

image-20201029212944403

分库成功！！

水平分表

单表

首先修改schema.xml

image-20201030091522214

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
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <!-- 逻辑库 绑定dataNode-->
        <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：数据节点  dataHost：数据主机 database：实际数据库-->
        <dataNode name="dn1" dataHost="host1" database="order" />
        <dataNode name="dn2" dataHost="host2" database="order" />
        
        <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <!-- 心跳测试 发送select user检测是否在线 -->
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <!-- 写主机配置 -->
                <writeHost host="hostM1" url="10.0.0.129:3306" user="root"
                                   password="kylin">
                </writeHost>
        </dataHost>

        <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <!-- 心跳测试 发送select user检测是否在线 -->
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <!-- 写主机配置 -->
                <writeHost host="hostM2" url="10.0.0.128:3306" user="root"
                                   password="kylin">
                </writeHost>
        </dataHost>
</mycat:schema>

接着配置规则rule.xml

image-20201030091817503

image-20201030091922806

配置修改完成后在我们的从机创建orders表

1
2
3
4
5
6
7
CREATE TABLE orders(
	id INT auto_increment,
	order_type INT,
	customer_id INT,
	amount DECIMAL(10,2),
	PRIMARY KEY(id)
);

接着启动mycat。

image-20201030092337021

在创建一个命令窗口远程连接mycat。

image-20201030092445143

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

运行插入语句。

注意这里insert语句不能省略字段名insert into orders values(xxxx)，直接插入

image-20201030092538522

查询一下select * from orders

image-20201030092713606

这是因为345，126分别在我们配置的两个数据库中。mycat分别查询后拼接返回给我们。

image-20201030092920981

虽然单表成功了，但是跟这个表相关的表怎么办？跨库join怎么办？？

跨库join

ER表

我们插入6条，跟orders表有关联的orders_detail数据。

1
2
3
4
5
6
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
SELECT o.*,od.detail
FROM orders o
INNER JOIN orders_detail od ON o.id = od.order_id;

image-20201030093732958

这是因为当我们运行这条查询语句时，mycat将它拦截分别到我配置的主从机去运行查找与orders_detail符合sql语句的查询结果。由于orders_detail只存在我的主机上，所以只能查出3条（每台机器上只有3条orders记录），而从机上没有这张表直接返回报错（查出的3条数据不管了）

image-20201030094813601

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
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <!-- 逻辑库 绑定dataNode-->
        <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：数据节点  dataHost：数据主机 database：实际数据库-->
        <dataNode name="dn1" dataHost="host1" database="order" />
        <dataNode name="dn2" dataHost="host2" database="order" />
        
        <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <!-- 心跳测试 发送select user检测是否在线 -->
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <!-- 写主机配置 -->
                <writeHost host="hostM1" url="10.0.0.129:3306" user="root"
                                   password="kylin">
                </writeHost>
        </dataHost>

        <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <!-- 心跳测试 发送select user检测是否在线 -->
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <!-- 写主机配置 -->
                <writeHost host="hostM2" url="10.0.0.128:3306" user="root"
                                   password="kylin">
                </writeHost>
        </dataHost>
</mycat:schema>

保存退出。删掉刚才orders_detail添加的记录，在另一台机器从机上也创建该表orders_detail

1
2
3
4
5
6
CREATE TABLE orders_detail(
	id INT auto_increment,
	detail VARCHAR(200),
	order_id INT,
	PRIMARY key(id)
);

image-20201030095102797

启动mycat。

image-20201030095136656

再次向orders_detail表中添加数据

1
2
3
4
5
6
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
SELECT o.*,od.detail
FROM orders o
INNER JOIN orders_detail od ON o.id = od.order_id;

image-20201030095340091

成功查询到。

image-20201030095456121

分别插入并且规则与父表保持一致。

全局表

设定为全局的表，会直接复制给每个数据库一份，所有写操作也会同步给多个库。

所以全局表一般不能是大数据表或者更新频繁的表

一般是字典表或者系统表为宜。

这里的全局表是dict_order_type

image-20201030095919041

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
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <!-- 逻辑库 绑定dataNode-->
        <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>
                <table name="dict_order_type" dataNode="dn1,dn2" type="global"></table>
        </schema>
        <!-- dataNode：数据节点  dataHost：数据主机 database：实际数据库-->
        <dataNode name="dn1" dataHost="host1" database="order" />
        <dataNode name="dn2" dataHost="host2" database="order" />
        
        <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <!-- 心跳测试 发送select user检测是否在线 -->
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <!-- 写主机配置 -->
                <writeHost host="hostM1" url="10.0.0.129:3306" user="root"
                                   password="kylin">
                </writeHost>
        </dataHost>

        <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <!-- 心跳测试 发送select user检测是否在线 -->
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <!-- 写主机配置 -->
                <writeHost host="hostM2" url="10.0.0.128:3306" user="root"
                                   password="kylin">
                </writeHost>
        </dataHost>
</mycat:schema>

接着在每个机器中创建dict_order_type表

1
2
3
4
5
CREATE TABLE dict_order_type(
	id int auto_increment,
	order_type VARCHAR(200),
	PRIMARY key(id)
);

image-20201030100136408

启动mycat。连接mycat。插入数据

1
2
insert into dict_order_type(id,order_type) values(101,'type1');
INSERT INTO dict_order_type(id,order_type) VALUES(102,'type2');

image-20201030100326505

image-20201030100413326

每个数据库中都插入了同样的数据。

全局序列

当我们分库分表之后id又是如何保持唯一呢？？如果使用自增是不适合的会导致重复，所以就需要使用到全局序列来保持唯一。

(0)在mycat创建本地文件，插入时自增。（不推荐）

(2)时间戳方式虽然很好但是太长了18位。（不推荐）

(1)数据库方式 步长默认为100

建库序列脚本

首先在我们的主机上运行MYCAT_SEQUENCE表

1
2
3
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;

接着创建三个函数。

image-20201030101534936

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

image-20201030101913620

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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 ;


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

image-20201030102035388

接着我们往该序列表中添加数据。

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

image-20201030102444154

修改配置文件

sequence_db_conf.properties

image-20201030102747949

1
2
3
4
5
#sequence stored in datanode
GLOBAL=dn1
COMPANY=dn1
CUSTOMER=dn1
ORDERS=dn1

编辑server.xml修改为1

image-20201030103022946

保存后重启mycat。连接mycat，插入语句

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

image-20201030103259149

多次运行后查看表中的数据。

image-20201030103330953

插入成功，全局序列设置成功。接着重启mycat。再次运行插入语句查看效果。

image-20201030103528564

此时的序列号是从200开始的，所以它的步长为100。

当然也可以自主生成根据业务逻辑组合，例如可以利用 redis的单线程原子性 incr来生成序列。不过这样就需要在java代码中实现。