一、概述
根据表中的数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多台数据库（主机）上面，这种切分称之为数据的水平（横向）切分。
二、案例场景
在业务系统中, 有一张表(日志表), 业务系统每天都会产生大量的日志数据 , 单台服务器的数据存储及处理能力是有限的, 可以对数据库表进行拆分, 原有的数据库表拆分成以下表 :
三、准备工作
1.准备三台数据库实例
192.168.192.157 192.168.192.158 192.168.192.159
2.在三台数据库实例中创建数据库
create database log_db DEFAULT CHARACTER SET utf8mb4;
四、schema.xml的配置
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="LOG_DB" checkSQLschema="false" sqlMaxLimit="100">
<table name="tb_log" dataNode="dn1,dn2,dn3" primaryKey="id" rule="mod- long" />
</schema>
<dataNode name="dn1" dataHost="host1" database="log_db" />
<dataNode name="dn2" dataHost="host2" database="log_db" />
<dataNode name="dn3" dataHost="host3" database="log_db" />
<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.192.157:3306" user="root" password="itcast"></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="hostM2" url="192.168.192.158:3306" user="root" password="itcast"></writeHost>
</dataHost>
<dataHost name="host3" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM3" url="192.168.192.159:3306" user="root" password="itcast"></writeHost>
</dataHost> </mycat:schema>
五、server.xml的配置
123456 LOG_DB 123456 LOG_DB 123456 LOG_DB true六、测试
1.在MyCat数据库中执行建表语句
CREATE TABLE `tb_log` (
`id` bigint(20) NOT NULL COMMENT 'ID',
`model_name` varchar(200) DEFAULT NULL COMMENT '模块名',
`model_value` varchar(200) DEFAULT NULL COMMENT '模块值',
`return_value` varchar(200) DEFAULT NULL COMMENT '返回值',
`return_class` varchar(200) DEFAULT NULL COMMENT '返回值类型',
`operate_user` varchar(20) DEFAULT NULL COMMENT '操作用户',
`operate_time` varchar(20) DEFAULT NULL COMMENT '操作时间',
`param_and_value` varchar(500) DEFAULT NULL COMMENT '请求参数名及参数值',
`operate_class` varchar(200) DEFAULT NULL COMMENT '操作类',
`operate_method` varchar(200) DEFAULT NULL COMMENT '操作方法',
`cost_time` bigint(20) DEFAULT NULL COMMENT '执行方法耗时, 单位 ms',
`source` int(1) DEFAULT NULL COMMENT '来源 : 1 PC , 2 Android , 3 IOS',
PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
2.插入数据
INSERT INTO `tb_log` (`id`, `model_name`, `model_value`, `return_value`, `return_class`,
`operate_user`, `operate_time`, `param_and_value`, `operate_class`, `operate_method`, `cost_time`，`source`)
VALUES('1','user','insert','success','java.lang.String','10001','2020-02-26 18:12:28','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.controller.UserController','insert','10',1);
INSERT INTO `tb_log` (`id`, `model_name`, `model_value`, `return_value`,`return_class`,
`operate_user`, `operate_time`, `param_and_value`, `operate_class`, `operate_method`, `cost_time`，`source`)
VALUES('2','user','insert','success','java.lang.String','10001','2020-02-26 18:12:27','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.controller.UserController','insert','23',1);
INSERT INTO `tb_log` (`id`, `model_name`, `model_value`, `return_value`, `return_class`,
`operate_user`, `operate_time`, `param_and_value`, `operate_class`, `operate_method`, `cost_time`，`source`)
VALUES('3','user','update','success','java.lang.String','10001','2020-02-26 18:16:45','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.controller.UserController','update','34',1);
INSERT INTO `tb_log` (`id`, `model_name`, `model_value`, `return_value`, `return_class`,
`operate_user`, `operate_time`, `param_and_value`, `operate_class`, `operate_method`, `cost_time`，`source`)
VALUES('4','user','update','success','java.lang.String','10001','2020-02-26 18:16:45','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.controller.UserController','update','13',2);
INSERT INTO `tb_log` (`id`, `model_name`, `model_value`, `return_value`, `return_class`,
`operate_user`, `operate_time`, `param_and_value`, `operate_class`, `operate_method`, `cost_time`，`source`)
VALUES('5','user','insert','success','java.lang.String','10001','2020-02-26 18:30:31','{\"age\":\"200\",\"name\":\"TomCat\",\"gender\":\"0\"}','cn.itcast.cont roller.UserController','insert','29',3);
INSERT INTO `tb_log` (`id`, `model_name`, `model_value`, `return_value`,`return_class`,
`operate_user`, `operate_time`, `param_and_value`, `operate_class`,`operate_method`, `cost_time`，`source`)
VALUES('6','user','find','success','java.lang.String','10001','2020-02 2618:30:31','{\"age\":\"200\",\"name\":\"TomCat\",\"gender\":\"0\"}','cn.itcast.cont roller.UserController','find','29',2);
