An overview,

According to the logical relationship of the data in the table, the data in the same table is split to multiple databases (hosts) in accordance with certain conditions, which is called horizontal (horizontal) data segmentation.

Ii. Case scenario

In a service system, there is one table (log table). The service system generates a large amount of log data every day. The data storage and processing capacity of a single server is limited.

Three, preparation

1. Prepare three database instances

192.168.192.157 192.168.192.158 192.168.192.159

2. Create databases on the three database instances

create database log_db DEFAULT CHARACTER SET utf8mb4;

Configuration of schema. XML

<? The 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 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.155:3306" user="root" password="itcast"></writeHost> </dataHost> </mycat:schema>Copy the code

Configuration of server.xml

Six, test,

1. Execute the construction sentence in MyCat database

CREATE TABLE 'tb_log' (' id 'bigint(20) NOT NULL COMMENT' id ', 'model_name' varchar(200) DEFAULT NULL COMMENT 'module ', '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 'Param_and_value ', 'operate_class' varchar(200) DEFAULT NULL COMMENT '表 名 ',' operate_method 'varchar(200) DEFAULT NULL COMMENT' 表 名 ', 'cost_time' bigint(20) DEFAULT NULL COMMENT 'Execution time, in ms',' source 'int(1) DEFAULT NULL COMMENT' 1 PC , 2 Android , 3 IOS', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;Copy the code

2. Insert data

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);Copy the code