写在后面
相熟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 -P3306drop 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 -P8066Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners. 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，后续我都会继续更新和保护，感激大家长期以来对冰河的反对！！
写在最初
如果你感觉冰河写的还不错，请微信搜寻并关注「 冰河技术 」微信公众号，跟冰河学习高并发、分布式、微服务、大数据、互联网和云原生技术，「 冰河技术 」微信公众号更新了大量技术专题，每一篇技术文章干货满满！不少读者曾经通过浏览「 冰河技术 」微信公众号文章，吊打面试官，胜利跳槽到大厂；也有不少读者实现了技术上的飞跃，成为公司的技术骨干！如果你也想像他们一样晋升本人的能力，实现技术能力的飞跃，进大厂，升职加薪，那就关注「 冰河技术 」微信公众号吧，每天更新超硬核技术干货，让你对如何晋升技术能力不再迷茫！