mycat 路由转发过程原理深度解析

1 安装后进入

Schema.xml配置信息如下，数据节点名称dn1、dn2、dn3： 

安装完后登录测试： 
MySQL -utest -ptest -h127.0.0.1 -P8066 -DTESTDB 

---

2 , 创建表

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

---

3，录入数据

3.1插入在分片0上：

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">explain <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">insert</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">into</span> travelrecord (id,user_id,traveldate,fee,days) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">values</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">100001</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'xiaohong'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'2014-01-06'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">510.5</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">3</span>);</span>
<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">insert</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">into</span> travelrecord (id,user_id,traveldate,fee,days) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">values</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">100001</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'xiaohong'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'2014-01-06'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">510.5</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">3</span>);</span>
</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li></ul>

3.2录入在分片1上：

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">explain <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">insert</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">into</span> travelrecord (id,user_id,traveldate,fee,days) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">values</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">8000004</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'mazi'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'2014-01-06'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">510.5</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">3</span>);</span>
<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">insert</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">into</span> travelrecord (id,user_id,traveldate,fee,days) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">values</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">8000004</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'mazi'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'2014-01-06'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">510.5</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">3</span>);</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li></ul>

3.3录入在分片2上：

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">explain <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">insert</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">into</span> travelrecord (id,user_id,traveldate,fee,days) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">values</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">10000004</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'suqi'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'2014-01-06'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">510.5</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">3</span>);</span>
<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">insert</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">into</span> travelrecord (id,user_id,traveldate,fee,days) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">values</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">10000004</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'suqi'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'2014-01-06'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">510.5</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">3</span>);</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li></ul>

---

4，查询数据

4.1在mycat后台的db上面，路由看到是分在3个片上面，

explain select * from Travelrecord; 

4.2单个数据路由分析，都在不同的datanode上：

4.3去实际的mysql上面测试，查询数据，已经分到3个分片 db1、db2、db3上面了，如下所示：