mycat+mysql 使用取模分片分库分表

由于遇到单个数据库读写瓶颈问题，我们决定使用多个数据库来分担压力，最后使用了mycat技术
实现方式：切分规则根据配置中输入的数值n。此种分片规则将数据分成n份（通常dn节点也为n），从而将数据均匀的分布于各节点上。
上一篇文章已经说了下载mycat以及配置mycat环境变量
下面就直接说如何配置mycat的配置文件
我们只需要改两个配置文件，rule.xml和schema.xml文件
rule.xml

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
    - you may not use this file except in compliance with the License. - You 
    may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
    - - Unless required by applicable law or agreed to in writing, software - 
    distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
    License for the specific language governing permissions and - limitations 
    under the License. -->
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">

    <tableRule name="sharding-by-mod"> 
        <rule> 
			 <!--属性指定对应表中用于分片的列名-->
            <columns>entpr_id</columns> 
			 <!--属性对应function 中指定的算法的名称-->
            <algorithm>sharding-by-mod</algorithm> 
        </rule> 

    </tableRule> 
    
    <function name="sharding-by-mod" class="io.mycat.route.function.PartitionByMod">
           <!-- 共有多少个节点 -->
             <property name="count">3</property>
    </function>

</mycat:rule>

schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <!-- name="TESTDB"是在server.xml中配置<property name="schemas">TESTDB</property>-->
    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100"  >
    <table name="sensor_data"  primaryKey="id" autoIncrement="true"   dataNode="dn$1-3" rule="sharding-by-mod"> </table>
    </schema>
    <!--配置mysql数据库testdb2,cespimp_hazard  mycat逻辑库名为 dn1,dn2,dn3-->
    <dataNode name="dn1" dataHost="localhost1" database="cespimp_hazard" />
    <dataNode name="dn2" dataHost="localhost1" database="testdb2" />
    <dataNode name="dn3" dataHost="localhost1" database="testdb3" />
    <!--配置数据类型 balance="0"读写不分离 -->
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1" slaveThreshold="100">
            <!--心跳包 -->
            <heartbeat>select user()</heartbeat>
            <!--配置mysql数据 账户密码 -->
            <writeHost host="hostM1" url="localhost:3306" user="root" password="root" />
    </dataHost>
</mycat:schema>

接下来我们创建在schema.xml配置的三个数据库，这三个库中都有一个相同的表sensor_data

启动mycat，连接mycat，执行插入语句：

INSERT INTO `sensor_data`(id,entpr_id,sensor_id,sensor_no,sensor_value,sensor_type,report_time) VALUES (82345, 1, 1, 'LT3621', 3.65075, 1, '2019-12-24 00:00:00');

INSERT INTO `sensor_data`(id,entpr_id,sensor_id,sensor_no,sensor_value,sensor_type,report_time) VALUES (82346, 2, 2, 'TI3601', 16.84030, 2, '2019-12-24 00:00:00');
INSERT INTO `sensor_data` (id,entpr_id,sensor_id,sensor_no,sensor_value,sensor_type,report_time)VALUES (82347, 2, 3, 'LT3622', 5.26713, 1, '2019-12-24 00:00:00');
INSERT INTO `sensor_data`(id,entpr_id,sensor_id,sensor_no,sensor_value,sensor_type,report_time) VALUES (82348, 3, 4, 'TI3602', 17.96880, 2, '2019-12-24 00:00:00');
INSERT INTO `sensor_data`(id,entpr_id,sensor_id,sensor_no,sensor_value,sensor_type,report_time) VALUES (82349, 3, 1, 'LT3621', 3.65075, 1, '2019-12-24 00:00:00');
INSERT INTO `sensor_data`(id,entpr_id,sensor_id,sensor_no,sensor_value,sensor_type,report_time) VALUES (82350, 3, 2, 'TI3601', 16.84030, 2, '2019-12-24 00:00:00');
INSERT INTO `sensor_data`(id,entpr_id,sensor_id,sensor_no,sensor_value,sensor_type,report_time) VALUES (82351, 2, 3, 'LT3622', 5.26713, 1, '2019-12-24 00:00:00');
INSERT INTO `sensor_data` (id,entpr_id,sensor_id,sensor_no,sensor_value,sensor_type,report_time)VALUES (82352, 2, 4, 'TI3602', 17.96880, 2, '2019-12-24 00:00:00');
INSERT INTO `sensor_data` (id,entpr_id,sensor_id,sensor_no,sensor_value,sensor_type,report_time)VALUES (82353, 2, 1, 'LT3621', 3.65255, 1, '2019-12-24 00:00:00');
INSERT INTO `sensor_data`(id,entpr_id,sensor_id,sensor_no,sensor_value,sensor_type,report_time) VALUES (82354, 1, 2, 'TI3601', 16.84030, 2, '2019-12-24 00:00:00');
INSERT INTO `sensor_data`(id,entpr_id,sensor_id,sensor_no,sensor_value,sensor_type,report_time) VALUES (82355, 1, 3, 'LT3622', 5.26713, 1, '2019-12-24 00:00:00');
INSERT INTO `sensor_data` (id,entpr_id,sensor_id,sensor_no,sensor_value,sensor_type,report_time)VALUES (82356, 1, 4, 'TI3602', 17.96880, 2, '2019-12-24 00:00:00');
INSERT INTO `sensor_data` (id,entpr_id,sensor_id,sensor_no,sensor_value,sensor_type,report_time)VALUES (82357, 1, 1, 'LT3621', 3.65255, 1, '2019-12-24 00:00:00');

你会发现：数据被均匀的插入到了三个库中的sensor_data表中
执行查询语句

SELECT * from sensor_data;

查询出来三个库中sensor_data的所有数据