乐趣区

关于mycat:MycatMycat核心开发者带你轻松掌握Mycat路由转发

jiezi

写在后面

相熟 Mycat 的小伙伴都晓得，Mycat 一个很重要的性能就是路由转发，那么，这篇文章就带着大家一起来看看 Mycat 是如何进行路由转发的，好了，不多说了，咱们间接进入主题。

环境筹备

软件版本

操作系统：CentOS-6.8

JDK 版本：jdk1.8

Mycat 版本：Mycat-server-1.6

MySQL：5.7

留神：这里，我将 Mycat 和 MySQL 装置在同一台虚拟机(IP：192.168.209.140 主机名为:binghe140)，大家也能够将 Mycat 和 MySQL 装置到不同的主机上，测试成果是一样的。

创立物理库

mysql -uroot -proot -h192.168.209.140 -P3306

drop database if exists db1;
create database db1;
drop database if exists db2;
create database db2;
drop database if exists db3;
create database db3;

配置 Mycat

schema.xml 配置

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/" >
 
    <schema name="binghe" checkSQLschema="false" sqlMaxLimit="100">
        <table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long"></table>
    </schema>
    <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743" 
        /> -->
    <dataNode name="dn1" dataHost="localhost1" database="db1" />
    <dataNode name="dn2" dataHost="localhost1" database="db2" />
    <dataNode name="dn3" dataHost="localhost1" database="db3" />
    <!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
     <dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" /> 
    <dataNode    name="jdbc_dn2" dataHost="jdbchost" database="db2" /> 
    <dataNode name="jdbc_dn3"     dataHost="jdbchost" database="db3" /> -->
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
        writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM1" url="127.0.0.1:3306" user="root" password="root"></writeHost>
        
        <writeHost host="hostM2" url="127.0.0.1:3306" user="root" password="root"></writeHost>
        <!--<writeHost host="hostS1" url="localhost:3316" user="root"-->
            <!--password="123456" />-->
        <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
    </dataHost>
</mycat:schema>

server.xml 配置

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://org.opencloudb/">
    <system>
    <property name="defaultSqlParser">druidparser</property>
    </system>
    <user name="binghe">
        <property name="password">binghe.123</property>
        <property name="schemas">binghe</property>
    </user>
    <user name="test">
        <property name="password">test</property>
        <property name="schemas">binghe</property>
        <property name="readOnly">true</property>
    </user>
</mycat:server>

rule.xml 配置

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://org.opencloudb/">
    <tableRule name="rule1">
        <rule>
            <columns>id</columns>
            <algorithm>func1</algorithm>
        </rule>
    </tableRule>
 
    <tableRule name="rule2">
        <rule>
            <columns>user_id</columns>
            <algorithm>func1</algorithm>
        </rule>
    </tableRule>
 
    <tableRule name="sharding-by-intfile">
        <rule>
            <columns>sharding_id</columns>
            <algorithm>hash-int</algorithm>
        </rule>
    </tableRule>
    <tableRule name="auto-sharding-long">
        <rule>
            <columns>id</columns>
            <algorithm>rang-long</algorithm>
        </rule>
    </tableRule>
    <tableRule name="mod-long">
        <rule>
            <columns>id</columns>
            <algorithm>mod-long</algorithm>
        </rule>
    </tableRule>
    <tableRule name="sharding-by-murmur">
        <rule>
            <columns>id</columns>
            <algorithm>murmur</algorithm>
        </rule>
    </tableRule>
    <tableRule name="sharding-by-month">
        <rule>
            <columns>create_date</columns>
            <algorithm>partbymonth</algorithm>
        </rule>
    </tableRule>
    <tableRule name="latest-month-calldate">
        <rule>
            <columns>calldate</columns>
            <algorithm>latestMonth</algorithm>
        </rule>
    </tableRule>
    
    <tableRule name="auto-sharding-rang-mod">
        <rule>
            <columns>id</columns>
            <algorithm>rang-mod</algorithm>
        </rule>
    </tableRule>
    
    <tableRule name="jch">
        <rule>
            <columns>id</columns>
            <algorithm>jump-consistent-hash</algorithm>
        </rule>
    </tableRule>
 
    <function name="murmur"
        class="org.opencloudb.route.function.PartitionByMurmurHash">
        <property name="seed">0</property>
        <property name="count">2</property>
        <property name="virtualBucketTimes">160</property>
    </function>
    <function name="hash-int"
        class="org.opencloudb.route.function.PartitionByFileMap">
        <property name="mapFile">partition-hash-int.txt</property>
    </function>
    <function name="rang-long"
        class="org.opencloudb.route.function.AutoPartitionByLong">
        <property name="mapFile">autopartition-long.txt</property>
    </function>
    <function name="mod-long" class="org.opencloudb.route.function.PartitionByMod">
        <!-- how many data nodes -->
        <property name="count">3</property>
    </function>
 
    <function name="func1" class="org.opencloudb.route.function.PartitionByLong">
        <property name="partitionCount">8</property>
        <property name="partitionLength">128</property>
    </function>
    <function name="latestMonth"
        class="org.opencloudb.route.function.LatestMonthPartion">
        <property name="splitOneDay">24</property>
    </function>
    <function name="partbymonth"
        class="org.opencloudb.route.function.PartitionByMonth">
        <property name="dateFormat">yyyy-MM-dd</property>
        <property name="sBeginDate">2020-01-01</property>
    </function>
    
    <function name="rang-mod" class="org.opencloudb.route.function.PartitionByRangeMod">
            <property name="mapFile">partition-range-mod.txt</property>
    </function>
    
    <function name="jump-consistent-hash" class="org.opencloudb.route.function.PartitionByJumpConsistentHash">
        <property name="totalBuckets">3</property>
    </function>
</mycat:rule>

登录 Mycat

登录 Mycat

命令行输出以下命令登录 Mycat

D:\>mysql -ubinghe -pbinghe.123 -h192.168.209.140 -P8066
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.8-mycat-1.6.1-RELEASE-20170807215126 MyCat Server (OpenCloundDB)
 
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql>

创立表测试

输出以下命令查看创立表的路由

create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int);

后果如下：

mysql> explain create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int);
+-----------+-----------------------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL                                                                                                                   |
+-----------+-----------------------------------------------------------------------------------------------------------------------+
| dn1       | create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int) |
| dn2       | create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int) |
| dn3       | create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int) |
+-----------+-----------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
 
mysql>

阐明创立表的 SQL 语句被 Mycat 路由到 dn1,dn2,dn3 三个节点上，也就是说在 3 个节点上都执行了创立表的 SQL。

咱们输出建表语句：

mysql> create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int);
Query OK, 0 rows affected (0.18 sec)

此时，将会在 dn1,dn2,dn3 三个节点上创立 travelrecord 表。

录入数据测试

录入到 dn1 节点

咱们在命令行输出如下 SQL 语句

explain insert into travelrecord (id,user_id,traveldate,fee,days) values(100001,'binghe','2020-11-10',510.5,3);

后果如下：

mysql> explain insert into travelrecord (id,user_id,traveldate,fee,days) values(100001,'binghe','2020-11-10',510.5,3);
+-----------+-------------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL                                                                                                         |
+-----------+-------------------------------------------------------------------------------------------------------------+
| dn1       | insert into travelrecord (id,user_id,traveldate,fee,days) values(100001,'binghe','2020-11-10',510.5,3) |
+-----------+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

阐明 Mycat 将 SQL 路由到了 dn1 节点。

咱们执行插入语句：

mysql> insert into travelrecord (id,user_id,traveldate,fee,days) values(100001,'binghe','2020-11-10',510.5,3);
Query OK, 1 row affected, 1 warning (0.01 sec)
 
mysql>

录入到 dn2 节点

咱们在命令行输出如下语句：

explain insert into travelrecord (id,user_id,traveldate,fee,days) values(8000004,'binghe','2020-11-10',510.5,3);

后果如下：

mysql> explain insert into travelrecord (id,user_id,traveldate,fee,days) values(8000004,'binghe','2020-11-10',510.5,3);
+-----------+--------------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL                                                                                                          |
+-----------+--------------------------------------------------------------------------------------------------------------+
| dn2       | insert into travelrecord (id,user_id,traveldate,fee,days) values(8000004,'binghe','2020-11-10',510.5,3) |
+-----------+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

阐明 Mycat 将 SQL 路由到了 dn2 节点，咱们执行插入语句：

mysql> insert into travelrecord (id,user_id,traveldate,fee,days) values(8000004,'binghe','2020-11-10',510.5,3);
Query OK, 1 row affected, 1 warning (0.06 sec)

路由到 dn3 节点

咱们在命令行输出如下语句

explain insert into travelrecord (id,user_id,traveldate,fee,days) values(10000004,'binghe','2020-11-10',510.5,3);

后果为：

mysql> explain insert into travelrecord (id,user_id,traveldate,fee,days) values(10000004,'binghe','2020-11-10',510.5,3);
+-----------+---------------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL                                                                                                           |
+-----------+---------------------------------------------------------------------------------------------------------------+
| dn3       | insert into travelrecord (id,user_id,traveldate,fee,days) values(10000004,'binghe','2020-11-10',510.5,3) |
+-----------+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

阐明 Mycat 将 SQL 路由到了 dn3 节点，咱们同样执行插入语句的操作

mysql>  insert into travelrecord (id,user_id,traveldate,fee,days) values(10000004,'binghe','2020-11-10',510.5,3);
Query OK, 1 row affected, 1 warning (0.01 sec)

查问测试

查问所有数据

在命令行执行如下语句：

explain select * from travelrecord;

后果为：

mysql> explain select * from travelrecord;
+-----------+--------------------------------------+
| DATA_NODE | SQL                                  |
+-----------+--------------------------------------+
| dn1       | SELECT * FROM travelrecord LIMIT 100 |
| dn2       | SELECT * FROM travelrecord LIMIT 100 |
| dn3       | SELECT * FROM travelrecord LIMIT 100 |
+-----------+--------------------------------------+
3 rows in set (0.01 sec)

阐明查问所有的数据，Mycat 是将 SQL 语句路由到了所有的数据分片，即 dn1,dn2,dn3 节点上。

依据 id 查问指定数据

咱们别离在命令行中执行如下 SQL:

explain select * from travelrecord where id = 1000004;
explain select * from travelrecord where id = 8000004;
explain select * from travelrecord where id = 10000004;

失去的后果顺次如下：

mysql> explain select * from travelrecord where id = 1000004;
+-----------+---------------------------------------------------------+
| DATA_NODE | SQL                                                     |
+-----------+---------------------------------------------------------+
| dn1       | SELECT * FROM travelrecord WHERE id = 1000004 LIMIT 100 |
+-----------+---------------------------------------------------------+
1 row in set (0.06 sec)
 
mysql> explain select * from travelrecord where id = 8000004;
+-----------+---------------------------------------------------------+
| DATA_NODE | SQL                                                     |
+-----------+---------------------------------------------------------+
| dn2       | SELECT * FROM travelrecord WHERE id = 8000004 LIMIT 100 |
+-----------+---------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql> explain select * from travelrecord where id = 10000004;
+-----------+----------------------------------------------------------+
| DATA_NODE | SQL                                                      |
+-----------+----------------------------------------------------------+
| dn3       | SELECT * FROM travelrecord WHERE id = 10000004 LIMIT 100 |
+-----------+----------------------------------------------------------+
1 row in set (0.00 sec)

阐明：依照分片字段查问，Mycat 只会将 SQL 路由到指定的数据分片。

删表测试

在命令行输出如下 SQL：

explain drop table travelrecord;

后果如下

mysql> explain drop table travelrecord;
+-----------+-------------------------+
| DATA_NODE | SQL                     |
+-----------+-------------------------+
| dn1       | drop table travelrecord |
| dn2       | drop table travelrecord |
| dn3       | drop table travelrecord |
+-----------+-------------------------+
3 rows in set (0.00 sec)

有后果可知，删表操作和创立表操作一样，Mycat 在本实例中都会将 SQL 路由到所有的数据分片。

留神：本文的 Mycat 路由后果针对本文的配置实例，其余配置下，Mycat 的路由后果可能会有不同。

好了，咱们明天就到这儿吧，我是冰河，咱们下期见~~

重磅福利

微信搜一搜【冰河技术】微信公众号，关注这个有深度的程序员，每天浏览超硬核技术干货，公众号内回复【PDF】有我筹备的一线大厂面试材料和我原创的超硬核 PDF 技术文档，以及我为大家精心筹备的多套简历模板（不断更新中），心愿大家都能找到心仪的工作，学习是一条时而郁郁寡欢，时而开怀大笑的路，加油。如果你通过致力胜利进入到了心仪的公司，肯定不要懈怠放松，职场成长和新技术学习一样，逆水行舟。如果有幸咱们江湖再见！

另外，我开源的各个 PDF，后续我都会继续更新和保护，感激大家长期以来对冰河的反对！！

写在最初

如果你感觉冰河写的还不错，请微信搜寻并关注「冰河技术 」微信公众号，跟冰河学习高并发、分布式、微服务、大数据、互联网和云原生技术，「 冰河技术 」微信公众号更新了大量技术专题，每一篇技术文章干货满满！不少读者曾经通过浏览「 冰河技术 」微信公众号文章，吊打面试官，胜利跳槽到大厂；也有不少读者实现了技术上的飞跃，成为公司的技术骨干！如果你也想像他们一样晋升本人的能力，实现技术能力的飞跃，进大厂，升职加薪，那就关注「 冰河技术」微信公众号吧，每天更新超硬核技术干货，让你对如何晋升技术能力不再迷茫！

退出移动版