By Valleylord

更新日期:

文章目录
  1. 1. 多分片排序查询
  2. 2. 分布式事务
  3. 3. mysql存储过程和函数的调用

MyCAT在多节点（MultiNode）的查询和事务上提供了非常不错的支持，但也有一些限制，本文将部分特性予以讨论。

多分片排序查询

在执行类似这样的sql，select * from travelrecord order by fee limit 100000,100，的时候，MyCAT的处理比较简单，将sql改写为：select * from travelrecord order by fee limit 0,100000+100发给每个分片去执行，然合排序，取出100条记录。如果每个分片都有200万数据，总共10个分片，那么就要处理200*10=2000万数据。当然这样的做法比较简单有效，却并不优化。

实验如下，travelrecord表的建表语句如下，

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

先使用python做出大约100万条测试数据的travelrecord表，

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb;
import random;
import string;
conn= MySQLdb.connect(
        host='workstation',
        port = 8066,
        user='test',
        passwd='test',
        db ='TESTDB',
        )
cur = conn.cursor()
for i in range(4500000,5500000):
    id_pk = i;
    # random user id
    user_id = string.join(random.sample('zyxwvutsrqponmlkjihgfedcba',5)).replace(' ','');
    traveldate = '2016-02-13';
    fee = 100 + random.random()*10;
    days = 3;
    sqli="insert into travelrecord (id,user_id,traveldate,fee,days) values (%s,%s,%s,%s,%s)"
    cur.execute(sqli,(id_pk, user_id, traveldate, fee, days))
cur.close()
conn.commit()
conn.close()
print "Insert Done."

然后分别执行如下sql，

1
2
3
4
select * from travelrecord order by fee limit 1000,100
select * from travelrecord order by fee limit 10000,100
select * from travelrecord order by fee limit 100000,100
select * from travelrecord order by fee limit 1000000,100

执行结果如下（为了便于显示结果，稍微修改了一下sql，不影响结论），

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
mysql> select * from travelrecord order by fee limit 1000,10;
+---------+---------+------------+------+------+
| id      | user_id | traveldate | fee  | days |
+---------+---------+------------+------+------+
| 5007747 | kxypj   | 2016-02-13 |  100 |    3 |
| 5007707 | qzkuf   | 2016-02-13 |  100 |    3 |
| 5007966 | agcpd   | 2016-02-13 |  100 |    3 |
| 5001525 | uxwhz   | 2016-02-13 |  100 |    3 |
| 5003049 | vnpkg   | 2016-02-13 |  100 |    3 |
| 5008317 | ijvrz   | 2016-02-13 |  100 |    3 |
| 5008381 | zuwcr   | 2016-02-13 |  100 |    3 |
| 5007470 | xicgh   | 2016-02-13 |  100 |    3 |
| 5004417 | zheyd   | 2016-02-13 |  100 |    3 |
| 5003625 | tbdhx   | 2016-02-13 |  100 |    3 |
+---------+---------+------------+------+------+
10 rows in set (0.64 sec)
mysql> select * from travelrecord order by fee limit 10000,10;
+---------+---------+------------+------+------+
| id      | user_id | traveldate | fee  | days |
+---------+---------+------------+------+------+
| 5157892 | uzwyd   | 2016-02-13 |  100 |    3 |
| 5157894 | wpqvi   | 2016-02-13 |  100 |    3 |
| 5157912 | qpmia   | 2016-02-13 |  100 |    3 |
| 5157935 | kotqb   | 2016-02-13 |  100 |    3 |
| 5157941 | axpqr   | 2016-02-13 |  100 |    3 |
| 5157950 | gxjvl   | 2016-02-13 |  100 |    3 |
| 5157956 | psgcn   | 2016-02-13 |  100 |    3 |
| 5157980 | xcnwe   | 2016-02-13 |  100 |    3 |
| 5158013 | mrosy   | 2016-02-13 |  100 |    3 |
| 5499548 | cswpa   | 2016-02-13 |  100 |    3 |
+---------+---------+------------+------+------+
10 rows in set (1.42 sec)
mysql> select * from travelrecord order by fee limit 100000,10;
+---------+---------+------------+------+------+
| id      | user_id | traveldate | fee  | days |
+---------+---------+------------+------+------+
| 5294771 | hzdqa   | 2016-02-13 |  101 |    3 |
| 5294769 | xzndi   | 2016-02-13 |  101 |    3 |
| 5294761 | edmsj   | 2016-02-13 |  101 |    3 |
| 5294752 | nrcmx   | 2016-02-13 |  101 |    3 |
| 5294744 | syanq   | 2016-02-13 |  101 |    3 |
| 4529145 | jqkcf   | 2016-02-13 |  101 |    3 |
| 5294735 | auekc   | 2016-02-13 |  101 |    3 |
| 5294734 | anhtb   | 2016-02-13 |  101 |    3 |
| 5294732 | isflj   | 2016-02-13 |  101 |    3 |
| 5294711 | undpo   | 2016-02-13 |  101 |    3 |
+---------+---------+------------+------+------+
10 rows in set (2.12 sec)
mysql> select * from travelrecord order by fee limit 900000,10;
+---------+---------+------------+------+------+
| id      | user_id | traveldate | fee  | days |
+---------+---------+------------+------+------+
| 4530570 | lqzda   | 2016-02-13 |  109 |    3 |
| 4530567 | yaesg   | 2016-02-13 |  109 |    3 |
| 4530545 | oiyrq   | 2016-02-13 |  109 |    3 |
| 4530537 | vibfu   | 2016-02-13 |  109 |    3 |
| 4530534 | lwzfd   | 2016-02-13 |  109 |    3 |
| 4530498 | dtvcu   | 2016-02-13 |  109 |    3 |
| 4530495 | vfqli   | 2016-02-13 |  109 |    3 |
| 4530481 | ycfon   | 2016-02-13 |  109 |    3 |
| 5031809 | janwy   | 2016-02-13 |  109 |    3 |
| 5031831 | utaco   | 2016-02-13 |  109 |    3 |
+---------+---------+------------+------+------+
10 rows in set (4.29 sec)

可见，对于limit M, N类型的sql查询，MyCAT至少需要在每个分片上进行M+N量级的排序，因此当M变大，N不不变的时候，虽然返回结果的数量没有变化，但是总的耗时却增加了。对应的log如下，

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
02/13 17:24:06.548  DEBUG [$_NIOREACTOR-0-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=192.168.2.201, user=test,txIsolation=3, autocommit=true, schema=TESTDB]select * from travelrecord order by fee limit 100000,10
02/13 17:24:06.548  DEBUG [$_NIOREACTOR-0-RW] (EnchachePool.java:70) -SQLRouteCache hit cache ,key:TESTDBselect * from travelrecord order by fee limit 100000,10
02/13 17:24:06.549  DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=TESTDB, host=192.168.2.201, user=test,txIsolation=3, autocommit=true, schema=TESTDB]select * from travelrecord order by fee limit 100000,10, route={
   1 -> dn1{SELECT *
FROM travelrecord
ORDER BY fee
LIMIT 0, 100010}
   2 -> dn2{SELECT *
FROM travelrecord
ORDER BY fee
LIMIT 0, 100010}
   3 -> dn3{SELECT *
FROM travelrecord
ORDER BY fee
LIMIT 0, 100010}
} rrs 
02/13 17:24:06.549  DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:82) -execute mutinode query select * from travelrecord order by fee limit 100000,10
02/13 17:24:06.549  DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:97) -has data merge logic 
02/13 17:24:06.549  DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -select read source hostM1 for dataHost:localhost1
02/13 17:24:06.549  DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -select read source hostM1 for dataHost:localhost1
02/13 17:24:06.558  DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -select read source hostM1 for dataHost:localhost1
02/13 17:24:06.559  DEBUG [$_NIOREACTOR-2-RW] (DataMergeService.java:94) -field metadata inf:[DAYS=ColMeta [colIndex=4, colType=3], USER_ID=ColMeta [colIndex=1, colType=253], ID=ColMeta [colIndex=0, colType=8], TRAVELDATE=ColMeta [colIndex=2, colType=10], FEE=ColMeta [colIndex=3, colType=246]]
02/13 17:24:06.559  DEBUG [$_NIOREACTOR-2-RW] (MultiNodeQueryHandler.java:241) -on row end reseponse MySQLConnection [id=10, lastTime=1455355446538, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=14, charset=latin1, txIsolation=3, autocommit=true, attachment=dn3{SELECT *
FROM travelrecord
ORDER BY fee
LIMIT 0, 100010}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@4fa37362, host=workstation, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/13 17:24:06.559  DEBUG [$_NIOREACTOR-2-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=10, lastTime=1455355446538, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=14, charset=latin1, txIsolation=3, autocommit=true, attachment=dn3{SELECT *
FROM travelrecord
ORDER BY fee
LIMIT 0, 100010}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@4fa37362, host=workstation, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/13 17:24:06.559  DEBUG [$_NIOREACTOR-2-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=10, lastTime=1455355446538, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=14, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=workstation, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/13 17:24:08.228  DEBUG [$_NIOREACTOR-1-RW] (MultiNodeQueryHandler.java:241) -on row end reseponse MySQLConnection [id=5, lastTime=1455355446538, user=root, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=9, charset=latin1, txIsolation=3, autocommit=true, attachment=dn2{SELECT *
FROM travelrecord
ORDER BY fee
LIMIT 0, 100010}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@4fa37362, host=workstation, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/13 17:24:08.228  DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=5, lastTime=1455355446538, user=root, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=9, charset=latin1, txIsolation=3, autocommit=true, attachment=dn2{SELECT *
FROM travelrecord
ORDER BY fee
LIMIT 0, 100010}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@4fa37362, host=workstation, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/13 17:24:08.228  DEBUG [$_NIOREACTOR-1-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=5, lastTime=1455355446538, user=root, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=9, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=workstation, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/13 17:24:09.454  DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:241) -on row end reseponse MySQLConnection [id=8, lastTime=1455355446538, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=6, charset=latin1, txIsolation=3, autocommit=true, attachment=dn1{SELECT *
FROM travelrecord
ORDER BY fee
LIMIT 0, 100010}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@4fa37362, host=workstation, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/13 17:24:09.455  DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=8, lastTime=1455355446538, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=6, charset=latin1, txIsolation=3, autocommit=true, attachment=dn1{SELECT *
FROM travelrecord
ORDER BY fee
LIMIT 0, 100010}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@4fa37362, host=workstation, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/13 17:24:09.455  DEBUG [$_NIOREACTOR-0-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=8, lastTime=1455355446538, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=6, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=workstation, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/13 17:24:09.468  DEBUG [BusinessExecutor7] (DataMergeService.java:296) -prepare mpp merge result for select * from travelrecord order by fee limit 100000,10
02/13 17:24:09.468  DEBUG [BusinessExecutor7] (MultiNodeQueryHandler.java:324) -last packet id:18

注意，log中显示，sql已经被改写为

1
2
3
4
SELECT *
FROM travelrecord
ORDER BY fee
LIMIT 0, 100010

，并在每个分片上执行。

分布式事务

分布式事务的模型比较复杂，主要也是两阶段提交的思路，先预先写数据，然后提交。

MyCAT的事务支持是弱XA的，事务内的SQL在各自分片上执行并且返回状态码，如果某个分片上的返回码为error，MyCAT就认为本次事务失败了，此时将会一次回滚事务所涉及到的所有分片。反之，如果所有的分片都返回成功的返回码，则当AP（应用程序）提交事务的时候，MyCAT会同时向事务涉及的所有分片发送提交事务的命令。

之所以说是弱XA，是因为在二阶段提交的过程中，若commit时某个节点出错了，只能回滚，而不会等其恢复后再次提交。

下面分析一下MyCAT分布式事务的日志，使用测试表customer1，使用主键id列取模3的余数来分片，建表和初始数据语句如下，

1
2
3
4
5
create table customer1 (id bigint not null primary key, name varchar(100), register_date DATE);
insert into customer1(id, name, register_date) values (10000, 'aaa', '20160131');
insert into customer1(id, name, register_date) values (20000, 'bbb', '20160131');
insert into customer1(id, name, register_date) values (30000, 'ccc', '20160131');

先执行一条insert语句，

1
insert into customer1(id, name, register_date) values (40000, 'ddd', '20160213'), (40001, 'eee', '20160213') ;

该语句会在customer1的两个分片上分别插入一条数据（即分布式事务），对应的MyCAT的log如下，

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
02/13 17:45:34.979  DEBUG [$_NIOREACTOR-0-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=192.168.2.201, user=test,txIsolation=3, autocommit=true, schema=TESTDB]insert into customer1(id, name, register_date) values (40000, 'ddd', '20160213'), (40001, 'eee', '20160213')
02/13 17:45:34.980  DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=TESTDB, host=192.168.2.201, user=test,txIsolation=3, autocommit=true, schema=TESTDB]insert into customer1(id, name, register_date) values (40000, 'ddd', '20160213'), (40001, 'eee', '20160213'), route={
   1 -> dn2{INSERT INTO customer1 (id, name, register_date)
VALUES (40000, 'ddd', '20160213')}
   2 -> dn3{INSERT INTO customer1 (id, name, register_date)
VALUES (40001, 'eee', '20160213')}
} rrs 
02/13 17:45:34.980  DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:82) -execute mutinode query insert into customer1(id, name, register_date) values (40000, 'ddd', '20160213'), (40001, 'eee', '20160213')
02/13 17:45:35.016  DEBUG [$_NIOREACTOR-2-RW] (MultiNodeQueryHandler.java:171) -received ok response ,executeResponse:true from MySQLConnection [id=10, lastTime=1455356734980, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=14, charset=latin1, txIsolation=3, autocommit=true, attachment=dn3{INSERT INTO customer1 (id, name, register_date)
VALUES (40001, 'eee', '20160213')}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@3f0911ed, host=workstation, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
02/13 17:45:35.016  DEBUG [$_NIOREACTOR-3-RW] (MultiNodeQueryHandler.java:171) -received ok response ,executeResponse:true from MySQLConnection [id=3, lastTime=1455356734980, user=root, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=7, charset=latin1, txIsolation=3, autocommit=true, attachment=dn2{INSERT INTO customer1 (id, name, register_date)
VALUES (40000, 'ddd', '20160213')}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@3f0911ed, host=workstation, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
02/13 17:45:35.016  DEBUG [$_NIOREACTOR-2-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=10, lastTime=1455356734980, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=14, charset=latin1, txIsolation=3, autocommit=true, attachment=dn3{INSERT INTO customer1 (id, name, register_date)
VALUES (40001, 'eee', '20160213')}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@3f0911ed, host=workstation, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
02/13 17:45:35.016  DEBUG [$_NIOREACTOR-2-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=10, lastTime=1455356734980, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=14, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=workstation, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/13 17:45:35.016  DEBUG [$_NIOREACTOR-3-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=3, lastTime=1455356734980, user=root, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=7, charset=latin1, txIsolation=3, autocommit=true, attachment=dn2{INSERT INTO customer1 (id, name, register_date)
VALUES (40000, 'ddd', '20160213')}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@3f0911ed, host=workstation, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]

此时，如果再执行另一个sql，

1
insert into customer1(id, name, register_date) values (40002, 'fff', '20160213'), (40001, 'eee', '20160213') ;

当然会因为主键40001冲突而发生错误，对应的log如下，

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
02/13 17:50:16.047  DEBUG [$_NIOREACTOR-0-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=192.168.2.201, user=test,txIsolation=3, autocommit=true, schema=TESTDB]insert into customer1(id, name, register_date) values (40002, 'fff', '20160213'), (40001, 'eee', '20160213')
02/13 17:50:16.048  DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=TESTDB, host=192.168.2.201, user=test,txIsolation=3, autocommit=true, schema=TESTDB]insert into customer1(id, name, register_date) values (40002, 'fff', '20160213'), (40001, 'eee', '20160213'), route={
   1 -> dn1{INSERT INTO customer1 (id, name, register_date)
VALUES (40002, 'fff', '20160213')}
   2 -> dn3{INSERT INTO customer1 (id, name, register_date)
VALUES (40001, 'eee', '20160213')}
} rrs 
02/13 17:50:16.048  DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:82) -execute mutinode query insert into customer1(id, name, register_date) values (40002, 'fff', '20160213'), (40001, 'eee', '20160213')
02/13 17:50:16.049  DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=4, lastTime=1455357016040, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=8, charset=latin1, txIsolation=3, autocommit=true, attachment=dn3{INSERT INTO customer1 (id, name, register_date)
VALUES (40001, 'eee', '20160213')}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@7de649b5, host=workstation, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
02/13 17:50:16.049  DEBUG [$_NIOREACTOR-0-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=4, lastTime=1455357016040, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=8, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=workstation, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/13 17:50:16.049   WARN [$_NIOREACTOR-0-RW] (MultiNodeHandler.java:127) -error response from MySQLConnection [id=4, lastTime=1455357016040, user=root, schema=db3, old shema=db3, borrowed=false, fromSlaveDB=false, threadId=8, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=workstation, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] err Duplicate entry '40001' for key 'PRIMARY' code:1062
02/13 17:50:16.085  DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:171) -received ok response ,executeResponse:true from MySQLConnection [id=8, lastTime=1455357016040, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=6, charset=latin1, txIsolation=3, autocommit=true, attachment=dn1{INSERT INTO customer1 (id, name, register_date)
VALUES (40002, 'fff', '20160213')}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@7de649b5, host=workstation, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
02/13 17:50:16.085  DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=8, lastTime=1455357016040, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=6, charset=latin1, txIsolation=3, autocommit=true, attachment=dn1{INSERT INTO customer1 (id, name, register_date)
VALUES (40002, 'fff', '20160213')}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@7de649b5, host=workstation, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
02/13 17:50:16.085  DEBUG [$_NIOREACTOR-0-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=8, lastTime=1455357016040, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=6, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=workstation, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/13 17:50:16.085  DEBUG [$_NIOREACTOR-0-RW] (MultiNodeHandler.java:194) -error all end ,clear session resource

以上两个sql，MyCAT的返回如下，

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> insert into customer1(id, name, register_date) values (40000, 'ddd', '20160213'), (40001, 'eee', '20160213') ;
Query OK, 2 rows affected (0.04 sec)
mysql> insert into customer1(id, name, register_date) values (40002, 'fff', '20160213'), (40001, 'eee', '20160213') ;
ERROR 1105 (HY000): Duplicate entry '40001' for key 'PRIMARY'
mysql> select * from customer1;
+-------+------+---------------+
| id    | name | register_date |
+-------+------+---------------+
| 10000 | aaa  | 2016-01-31    |
| 40000 | ddd  | 2016-02-13    |
| 20000 | bbb  | 2016-01-31    |
| 40001 | eee  | 2016-02-13    |
| 30000 | ccc  | 2016-01-31    |
| 40002 | fff  | 2016-02-13    |
+-------+------+---------------+
6 rows in set (0.01 sec)

对于后一条sql，MyCAT的返回结果与单节点的mysql不一致，这可能是有问题的。对于mysql，后一条sql已经报错，那么整条语句都应当被rollback，即没有一笔数据可以插入；但对于MyCAT，即使其遵守弱XA模型，该错误是发生在sql的执行时期，而非commit时期，因此，应当报错并整条语句rollback。这可能是MyCAT的一个bug，该错误发生在启用autocommitautocommit=1的时候。如果关闭autocommitautocommit=0，并手动rollback，会得到不同的结果，如下，

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into customer1(id, name, register_date) values (40002, 'fff', '20160213'), (40001, 'eee', '20160213') ;
ERROR 1105 (HY000): Duplicate entry '40001' for key 'PRIMARY'
mysql> select * from customer1;
ERROR 1003 (HY000): Transaction error, need to rollback.Duplicate entry '40001' for key 'PRIMARY'
mysql> commit;
ERROR 1003 (HY000): Transaction error, need to rollback.
mysql> rollback;
Query OK, 0 rows affected (0.05 sec)
mysql> select * from customer1;
+-------+------+---------------+
| id    | name | register_date |
+-------+------+---------------+
| 10000 | aaa  | 2016-01-31    |
| 40000 | ddd  | 2016-02-13    |
| 20000 | bbb  | 2016-01-31    |
| 40001 | eee  | 2016-02-13    |
| 30000 | ccc  | 2016-01-31    |
+-------+------+---------------+
5 rows in set (0.01 sec)

insert语句对应的log如下，

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
02/13 18:15:07.385  DEBUG [$_NIOREACTOR-0-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=192.168.2.201, user=test,txIsolation=3, autocommit=false, schema=TESTDB]insert into customer1(id, name, register_date) values (40002, 'fff', '20160213'), (40001, 'eee', '20160213')
02/13 18:15:07.386  DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=TESTDB, host=192.168.2.201, user=test,txIsolation=3, autocommit=false, schema=TESTDB]insert into customer1(id, name, register_date) values (40002, 'fff', '20160213'), (40001, 'eee', '20160213'), route={
   1 -> dn1{INSERT INTO customer1 (id, name, register_date)
VALUES (40002, 'fff', '20160213')}
   2 -> dn3{INSERT INTO customer1 (id, name, register_date)
VALUES (40001, 'eee', '20160213')}
} rrs 
02/13 18:15:07.386  DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:82) -execute mutinode query insert into customer1(id, name, register_date) values (40002, 'fff', '20160213'), (40001, 'eee', '20160213')
02/13 18:15:07.386  DEBUG [$_NIOREACTOR-0-RW] (MySQLConnection.java:445) -con need syn ,total syn cmd 1 commands SET autocommit=0;schema change:false con:MySQLConnection [id=8, lastTime=1455358507386, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=6, charset=latin1, txIsolation=3, autocommit=true, attachment=dn1{INSERT INTO customer1 (id, name, register_date)
VALUES (40002, 'fff', '20160213')}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@6258e05a, host=workstation, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
02/13 18:15:07.387  DEBUG [$_NIOREACTOR-0-RW] (MySQLConnection.java:445) -con need syn ,total syn cmd 1 commands SET autocommit=0;schema change:false con:MySQLConnection [id=7, lastTime=1455358507387, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=10, charset=latin1, txIsolation=3, autocommit=true, attachment=dn3{INSERT INTO customer1 (id, name, register_date)
VALUES (40001, 'eee', '20160213')}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@6258e05a, host=workstation, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
02/13 18:15:07.387  DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:171) -received ok response ,executeResponse:false from MySQLConnection [id=8, lastTime=1455358507385, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=6, charset=latin1, txIsolation=3, autocommit=false, attachment=dn1{INSERT INTO customer1 (id, name, register_date)
VALUES (40002, 'fff', '20160213')}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@6258e05a, host=workstation, port=3306, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@286a4c10, writeQueue=0, modifiedSQLExecuted=true]
02/13 18:15:07.388  DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:171) -received ok response ,executeResponse:true from MySQLConnection [id=8, lastTime=1455358507385, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=6, charset=latin1, txIsolation=3, autocommit=false, attachment=dn1{INSERT INTO customer1 (id, name, register_date)
VALUES (40002, 'fff', '20160213')}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@6258e05a, host=workstation, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
02/13 18:15:07.387  DEBUG [$_NIOREACTOR-3-RW] (MultiNodeQueryHandler.java:171) -received ok response ,executeResponse:false from MySQLConnection [id=7, lastTime=1455358507385, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=10, charset=latin1, txIsolation=3, autocommit=false, attachment=dn3{INSERT INTO customer1 (id, name, register_date)
VALUES (40001, 'eee', '20160213')}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@6258e05a, host=workstation, port=3306, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@35b287d8, writeQueue=0, modifiedSQLExecuted=true]
02/13 18:15:07.388   WARN [$_NIOREACTOR-3-RW] (MultiNodeHandler.java:127) -error response from MySQLConnection [id=7, lastTime=1455358507385, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=10, charset=latin1, txIsolation=3, autocommit=false, attachment=dn3{INSERT INTO customer1 (id, name, register_date)
VALUES (40001, 'eee', '20160213')}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@6258e05a, host=workstation, port=3306, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@35b287d8, writeQueue=0, modifiedSQLExecuted=true] err Duplicate entry '40001' for key 'PRIMARY' code:1062
02/13 18:15:07.388  DEBUG [$_NIOREACTOR-3-RW] (MultiNodeHandler.java:194) -error all end ,clear session resource

rollback语句对应的log如下，

1
2
3
4
5
6
7
8
9
10
11
12
13
14
02/13 18:18:09.243  DEBUG [$_NIOREACTOR-0-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=192.168.2.201, user=test,txIsolation=3, autocommit=false, schema=TESTDB]rollback
02/13 18:18:09.243  DEBUG [$_NIOREACTOR-0-RW] (RollbackNodeHandler.java:71) -rollback job run for MySQLConnection [id=7, lastTime=1455358507385, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=10, charset=latin1, txIsolation=3, autocommit=false, attachment=dn3{INSERT INTO customer1 (id, name, register_date)
VALUES (40001, 'eee', '20160213')}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@6258e05a, host=workstation, port=3306, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@35b287d8, writeQueue=0, modifiedSQLExecuted=true]
02/13 18:18:09.243  DEBUG [$_NIOREACTOR-0-RW] (RollbackNodeHandler.java:71) -rollback job run for MySQLConnection [id=8, lastTime=1455358507385, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=6, charset=latin1, txIsolation=3, autocommit=false, attachment=dn1{INSERT INTO customer1 (id, name, register_date)
VALUES (40002, 'fff', '20160213')}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@6258e05a, host=workstation, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
02/13 18:18:09.281  DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:361) -clear session resources org.opencloudb.server.NonBlockingSession@535dc7dc
02/13 18:18:09.281  DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=7, lastTime=1455358507385, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=10, charset=latin1, txIsolation=3, autocommit=false, attachment=dn3{INSERT INTO customer1 (id, name, register_date)
VALUES (40001, 'eee', '20160213')}, respHandler=org.opencloudb.mysql.nio.handler.RollbackNodeHandler@50237363, host=workstation, port=3306, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@35b287d8, writeQueue=0, modifiedSQLExecuted=true]
02/13 18:18:09.281  DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=8, lastTime=1455358507385, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=6, charset=latin1, txIsolation=3, autocommit=false, attachment=dn1{INSERT INTO customer1 (id, name, register_date)
VALUES (40002, 'fff', '20160213')}, respHandler=org.opencloudb.mysql.nio.handler.RollbackNodeHandler@50237363, host=workstation, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
02/13 18:18:09.281  DEBUG [$_NIOREACTOR-3-RW] (RollbackReleaseHandler.java:58) -autocomit is false,but no commit or rollback ,so mycat rollbacked backend conn MySQLConnection [id=7, lastTime=1455358507385, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=10, charset=latin1, txIsolation=3, autocommit=false, attachment=null, respHandler=org.opencloudb.mysql.nio.handler.RollbackReleaseHandler@76e05d9e, host=workstation, port=3306, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@35b287d8, writeQueue=0, modifiedSQLExecuted=true]
02/13 18:18:09.281  DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=7, lastTime=1455358507385, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=10, charset=latin1, txIsolation=3, autocommit=false, attachment=null, respHandler=null, host=workstation, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/13 18:18:09.281  DEBUG [$_NIOREACTOR-0-RW] (RollbackReleaseHandler.java:58) -autocomit is false,but no commit or rollback ,so mycat rollbacked backend conn MySQLConnection [id=8, lastTime=1455358507385, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=6, charset=latin1, txIsolation=3, autocommit=false, attachment=null, respHandler=org.opencloudb.mysql.nio.handler.RollbackReleaseHandler@36784733, host=workstation, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
02/13 18:18:09.282  DEBUG [$_NIOREACTOR-0-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=8, lastTime=1455358507385, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=6, charset=latin1, txIsolation=3, autocommit=false, attachment=null, respHandler=null, host=workstation, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

而在发生sql错误和rollback语句之间的其他语句，几乎没有log，只有连接的log，类似这样，

1
02/13 18:16:52.337  DEBUG [$_NIOREACTOR-0-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=192.168.2.201, user=test,txIsolation=3, autocommit=false, schema=TESTDB]select * from customer1

综上，MyCAT的分布式事务在关闭autocommit的时候才算是比较完整。

mysql存储过程和函数的调用

MyCAT本身是不支持存储过程和函数的，但可以通过使用MyCAT注解的方式来变相的实现多分片上存储过程的功能。MyCAT的注解主要有两种，一种是sql解析的注解，以sql=开头，表示不使用实际sql 的执行计划，而使用注解中的sql的执行计划；另一种是执行计划的注解，以catlet=开头，这个目前主要针对于表Join的情况，修改Join的执行计划，目前的ShareJoin只能完成两个表的join。调用存储过程和函数的注解主要是使用前一种。

在分片dn1上创建存储过程count_customer，输出customer1表中的数据总行数，存储过程代码如下，

1
2
3
4
5
CREATE  PROCEDURE `count_customer`(OUT s int)
BEGIN 
SELECT COUNT(*) INTO s FROM customer1; 
END

在MyCAT中，可以使用注解的方式来执行，如下，

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> /*!mycat: sql=select * from customer1 where id = 30000 */ set @p_out=0;
Query OK, 0 rows affected (0.00 sec)
mysql> /*!mycat: sql=select * from customer1 where id = 30000 */ select @p_out;
+--------+
| @p_out |
+--------+
| NULL   |
+--------+
1 row in set (0.00 sec)
mysql> /*!mycat: sql=select * from customer1 where id = 30000 */ CALL count_customer(@p_out);
Query OK, 1 row affected (0.01 sec)
mysql> /*!mycat: sql=select * from customer1 where id = 30000 */ select @p_out;
+--------+
| @p_out |
+--------+
| NULL   |
+--------+
1 row in set (0.00 sec)

可见，已经执行成功，但是因为MyCAT客户端不支持set变量，所以存储过程的返回值p_out没有传回来。甚至，MyCAT客户端连基本的变量设置值也没有支持，这样，连传入参数也无法完成。log如下，可见变量被忽略，

1
2
02/14 12:54:25.774  DEBUG [$_NIOREACTOR-1-RW] (ServerQueryHandler.java:56) -ServerConnection [id=2, schema=TESTDB, host=192.168.2.201, user=test,txIsolation=3, autocommit=true, schema=TESTDB]/*!mycat: sql=select * from customer1 where id = 30000 */ set @p_out=0
02/14 12:54:25.774   WARN [$_NIOREACTOR-1-RW] (SetHandler.java:127) -ServerConnection [id=2, schema=TESTDB, host=192.168.2.201, user=test,txIsolation=3, autocommit=true, schema=TESTDB]/*!mycat: sql=select * from customer1 where id = 30000 */ set @p_out=0 is not recoginized and ignored

如果要返回数值的话，可以使用函数，函数的代码如下，

1
2
3
4
5
6
7
CREATE FUNCTION `count_customer`() RETURNS int(11)
BEGIN
declare s int;
SELECT COUNT(*) INTO s FROM customer1;
RETURN s;
END

MyCAT中的执行结果如下，

1
2
3
4
5
6
7
8
9
mysql> /*!mycat: sql=select id from customer1 */ select count_customer();
+------------------+
| count_customer() |
+------------------+
|                1 |
|                2 |
|                2 |
+------------------+
3 rows in set (0.01 sec)

但是无法分清楚具体哪个分片上有几行数据，也不是很好用。

综上，MyCAT对mysql存储过程和函数的支持还是比较弱的，没有变量，对每个分片返回值也缺乏标识，毕竟，存储过程并不是分布式数据库面对的常见场景。

文章目录
  1. 1. 多分片排序查询
  2. 2. 分布式事务
  3. 3. mysql存储过程和函数的调用

欢迎来到Valleylord的博客！

本博的文章尽量原创。