Mycat讀寫分離以及拆庫拆表綜合實驗3：通過日志分析mycat路由過程

#在客戶端查詢t1表中id=1的值

我們在之前的schma.cnf中定義了t1在是一張全局表，且位于名稱為mysql的分片節(jié)點(diǎn)（datanode)上，因此當(dāng)mycat攔截到客戶端SQL語句之后，分析t1位于mysql分片節(jié)點(diǎn)上，直接發(fā)放該分片節(jié)點(diǎn)的readhost上

mysql > select * from t1 where id=1;

2018-05-25 10:16:16.906 DEBUG [$_NIOREACTOR-1-RW] (io.mycat.backend.mysql.nio.MySQLConnection.synAndDoExecute(MySQLConnection.java:451)) - con need syn ,total syn cmd 1 commands SET names utf8;schema change:false con:MySQLConnection [id=29, lastTime=1527214576906, user=mycat, schema=yuyang, old shema=yuyang, borrowed=true, fromSlaveDB=true, threadId=20, charset=utf8, txIsolation=3, autocommit=true, attachment=MySQL{SELECT *
FROM t1
WHERE id = 1
LIMIT 100}, respHandler=SingleNodeHandler [node=MySQL{SELECT *
FROM t1
WHERE id = 1
LIMIT 100}, packetId=0], host=192.168.31.117, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2018-05-25 10:16:16.907 DEBUG [$_NIOREACTOR-1-RW] (io.mycat.server.NonBlockingSession.releaseConnection(NonBlockingSession.java:378)) - release connection MySQLConnection [id=29, lastTime=1527214576902, user=mycat, schema=yuyang, old shema=yuyang, borrowed=true, fromSlaveDB=true, threadId=20, charset=utf8, txIsolation=3, autocommit=true, attachment=MySQL{SELECT *
FROM t1
WHERE id = 1
LIMIT 100}, respHandler=SingleNodeHandler [node=MySQL{SELECT *
FROM t1
WHERE id = 1
LIMIT 100}, packetId=8], host=192.168.31.117, port=3306, statusSync=io.mycat.backend.mysql.nio.MySQLConnection$StatusSync@40563af9, writeQueue=0, modifiedSQLExecuted=false]
2018-05-25 10:16:16.907 DEBUG [$_NIOREACTOR-1-RW] (io.mycat.backend.datasource.PhysicalDatasource.releaseChannel(PhysicalDatasource.java:595)) - release channel MySQLConnection [id=29, lastTime=1527214576902, user=mycat, schema=yuyang, old shema=yuyang, borrowed=true, fromSlaveDB=true, threadId=20, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.31.117, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

t2同理，只不過發(fā)往名為mysql_galera的分片節(jié)點(diǎn)上

mysql > select * from t2 where id=3;
2018-05-25 10:44:00.504 DEBUG [$_NIOREACTOR-1-RW] (io.mycat.backend.mysql.nio.MySQLConnection.synAndDoExecute(MySQLConnection.java:451)) - con need syn ,total syn cmd 1 commands SET names utf8;schema change:false con:MySQLConnection [id=44, lastTime=1527216240504, user=mycat, schema=yuyang, old shema=yuyang, borrowed=true, fromSlaveDB=true, threadId=42, charset=utf8, txIsolation=3, autocommit=true, attachment=MySQL_galera{SELECT *
FROM t2
WHERE id = 3
LIMIT 100}, respHandler=SingleNodeHandler [node=MySQL_galera{SELECT *
FROM t2
WHERE id = 3
LIMIT 100}, packetId=0], host=192.168.31.123, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2018-05-25 10:44:00.506 DEBUG [$_NIOREACTOR-1-RW] (io.mycat.server.NonBlockingSession.releaseConnection(NonBlockingSession.java:378)) - release connection MySQLConnection [id=44, lastTime=1527216240502, user=mycat, schema=yuyang, old shema=yuyang, borrowed=true, fromSlaveDB=true, threadId=42, charset=utf8, txIsolation=3, autocommit=true, attachment=MySQL_galera{SELECT *
FROM t2
WHERE id = 3
LIMIT 100}, respHandler=SingleNodeHandler [node=MySQL_galera{SELECT *
FROM t2
WHERE id = 3
LIMIT 100}, packetId=8], host=192.168.31.123, port=3306, statusSync=io.mycat.backend.mysql.nio.MySQLConnection$StatusSync@786ee29, writeQueue=0, modifiedSQLExecuted=false]
2018-05-25 10:44:00.506 DEBUG [$_NIOREACTOR-1-RW] (io.mycat.backend.datasource.PhysicalDatasource.releaseChannel(PhysicalDatasource.java:595)) - release channel MySQLConnection [id=44, lastTime=1527216240502, user=mycat, schema=yuyang, old shema=yuyang, borrowed=true, fromSlaveDB=true, threadId=42, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.31.123, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

接下來我們分析一下t3的轉(zhuǎn)發(fā)邏輯，在schma.cnf中我們定義t3分別位于mysql與mysql_galera兩個分片節(jié)點(diǎn)上，且分片規(guī)則為id%1024后值為0-512則在mysql_galera上，513-1024則在mysql上，搞清楚了定義的分片位置與規(guī)則，下面來分析一下轉(zhuǎn)發(fā)過程
首先，當(dāng)mtcat攔截到SQL語句之后，發(fā)現(xiàn)t3存在分片規(guī)則，提取分片字段，匹配分片函數(shù)（也就是規(guī)則），得到了分片列表，把SQL語句發(fā)送到對應(yīng)的分片節(jié)點(diǎn)上去執(zhí)行

以select id from t3 where id=512 這里的查找id=512,按照分片函數(shù)，512放在名為mysql的分片節(jié)點(diǎn)上，然后把SQL發(fā)往該節(jié)點(diǎn)執(zhí)行。

mysql > select id from t3 where id=512;
2018-05-25 10:46:01.027 DEBUG [$_NIOREACTOR-1-RW] (io.mycat.server.NonBlockingSession.execute(NonBlockingSession.java:126)) - ServerConnection [id=10, schema=mycat01, host=192.168.31.124, user=root,txIsolation=3, autocommit=true, schema=mycat01]select * from t3 where id=512, route={
   1 -> MySQL{select * from t3 where id=512}
} rrs 

mysql > select id from t3 where id=1088;
2018-05-25 10:50:20.308 DEBUG [$_NIOREACTOR-1-RW] (io.mycat.server.NonBlockingSession.execute(NonBlockingSession.java:126)) - ServerConnection [id=10, schema=mycat01, host=192.168.31.124, user=root,txIsolation=3, autocommit=true, schema=mycat01]select * from t3 where id=1088, route={
   1 -> MySQL_galera{select * from t3 where id=1088}
} rrs