MyCat分库分表

分库

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

首先配置schema.xml

<?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 DATABASE order;

创建完成后，连接MyCat

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

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

选择数据库,创建customer

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

分库成功！！

水平分表

单表

首先修改schema.xml

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

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

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

接着启动mycat。

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

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)，直接插入

查询一下select * from orders

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

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

跨库join

ER表

我们插入6条，跟orders表有关联的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);

插入后我们运行查询语句

SELECT o.*,od.detail
FROM orders o
INNER JOIN orders_detail od ON o.id = od.order_id;

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

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

CREATE TABLE orders_detail(
	id INT auto_increment,
	detail VARCHAR(200),
	order_id INT,
	PRIMARY key(id)
);

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

运行查询语句

SELECT o.*,od.detail
FROM orders o
INNER JOIN orders_detail od ON o.id = od.order_id;

成功查询到。

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

全局表

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

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

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

这里的全局表是dict_order_type

<?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表

CREATE TABLE dict_order_type(
	id int auto_increment,
	order_type VARCHAR(200),
	PRIMARY key(id)
);

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

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

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

全局序列

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

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

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

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

建库序列脚本

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

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;

接着创建三个函数。

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 ;

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 ;

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

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

修改配置文件

sequence_db_conf.properties

#sequence stored in datanode
GLOBAL=dn1
COMPANY=dn1
CUSTOMER=dn1
ORDERS=dn1        

编辑server.xml修改为1

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

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

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

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

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

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