Mycat的使用 - 03.全局序列號

02配置篇說到tb1表按照主鍵id進行了分片, 實際工作中還會經常按業務字段分片, 這次有tb3表, 按user_id分片, 依上文思路, 先調整下涉及到的配置文件.

1. 在schema.xml中, 添加tb3表的配置.

<table name="tb3" dataNode='dnTest1,dnTest2' rule="mod-long-user_id"/>

2. 在rule.xml中, 添加tb3表分片配置, 其中分片算法還是簡單取摸.

<tableRule name="mod-long-user_id">

    <rule>

        <columns>user_id</columns>

        <algorithm>mod-long</algorithm>

    </rule>

</tableRule>

連接Mycat, 創建tb3的表結構.

mysql> create table tb3(id int auto_increment primary key, user_id int not null default 0, user_name varchar(30) not null default '');

下面插入2條數據.

mysql> insert into tb3(id, user_id, user_name) values(7, 1, 'abcd');

mysql> insert into tb3(id, user_id, user_name) values(7, 2, 'efgh');

查看發現, 雖然id字段為主鍵, 但在分片情況下, 其已失去了原有的唯一性約束. 原因很簡單, 多個MySQL實例上唯一主鍵可以很自然的出現相同值.

mysql> select * from tb3;

+----+---------+-----------+

| id | user_id | user_name |

+----+---------+-----------+

|  7 |       2 | efgh      |

|  7 |       1 | abcd      |

+----+---------+-----------+

針對上述情況, Mycat中使用全局序列號(簡稱sequence), 來重塑主鍵的全局唯一性, 提供了包含本地配置和數據庫配置等多種實現方式.

下面使用配置數據庫的方式來獲取sequence, 先了解下其原理.

在數據庫中創建一張表, 存放sequence名稱(name列), 當前值(current_value), 和步長(increment, 表每次讀取多少個sequence).

sequence的獲取, 和維護:

1). 當初次使用該sequence時, 根據傳入的sequence名稱, 從數據庫該表中讀取current_value, 和increment到Mycat中, 并將數據庫中的current_value設置為原current_value值+increment值.

2). Mycat將讀取到current_value值+increment值作為本次要使用的sequence值, 下次再使用時, 自動加1, 當使用increment次后, 執行步驟1)相同的操作.

3). Mycat負責維護這張表, 用到哪些sequence, 只需在該表中插入一條記錄即可. 若某次讀取的sequence沒有用完, Mycat就停掉了, 則這次讀取的sequence剩余值不會再使用.

配置方式:

1. 在server.xml中, 開啟使用數據庫方式生成sequence的開關.

<property name="sequnceHandlerType">1</property>

2. 調整schema.xml, 并在Mycat后端某個MySQL實例上創建mycatseq數據庫(該庫名隨意), MYCAT_SEQUENCE表(表名要大寫), 和3個函數.

2.1 于schema.xml中添加如下配置.

<dataNode name="gseq" dataHost="Rep1_3306" database="mycatseq"/>

2.2 登陸節點主機Rep1_3306創建相應的數據庫和表.

mysql> create database mycatseq;

mysql> use mycatseq;

mysql> create table MYCAT_SEQUENCE(name varchar(50) not null, current_value int not null, increment int not null default 100, primary key(name));

2.3 并創建3個函數.

# mycat_seq_currval

DELIMITER //

CREATE DEFINER=`zzzz`@`192.168.4.%` FUNCTION `mycat_seq_currval`(seq_name VARCHAR(50)) RETURNS varchar(64) CHARSET utf8 COLLATE utf8_bin

    DETERMINISTIC

BEGIN

        DECLARE retval VARCHAR(64);

        SET retval="-999999999,null";

        SELECT concat(CAST(current_value AS CHAR),",",CAST(increment AS CHAR) ) INTO retval   FROM MYCAT_SEQUENCE  WHERE name = seq_name;

        RETURN retval;

END; //

DELIMITER ;

# mycat_seq_nextval;

DELIMITER //

CREATE DEFINER=`zzzz`@`192.168.4.%` FUNCTION `mycat_seq_nextval`(seq_name VARCHAR(50)) RETURNS varchar(64) CHARSET utf8 COLLATE utf8_bin

    DETERMINISTIC

BEGIN

         UPDATE MYCAT_SEQUENCE

                 SET current_value = current_value + increment  WHERE name = seq_name;

         RETURN mycat_seq_currval(seq_name);

END; //

DELIMITER ;

# mycat_seq_setval;

DELIMITER //

CREATE DEFINER=`zzzz`@`192.168.4.%` FUNCTION `mycat_seq_setval`(seq_name VARCHAR(50), value INTEGER) RETURNS varchar(64) CHARSET utf8 COLLATE utf8_bin

    DETERMINISTIC

BEGIN

         UPDATE MYCAT_SEQUENCE

                   SET current_value = value

                   WHERE name = seq_name;

         RETURN mycat_seq_currval(seq_name);

END; //

DELIMITER ;

至此, 使用Mycat sequence的準備工作就緒了, 誰來使用呢, 就是tb3, 怎么使用呢, 看如下步驟.

1. 要在schema.xml中對tb3的配置稍微改造下, 添加autoIncrement="true", 告訴Mycat tb3使用sequence; 添加primaryKey="id", 告訴Mycat主鍵字段是什么.

<table name="tb3" dataNode='dnTest1,dnTest2' rule="mod-long-user_id" primaryKey="id" autoIncrement="true"/>

2. 那Mycat如何知道去哪個節點主機獲取sequence呢, 需要在一個新的配置文件sequence_db_conf.properties中標明表名(要大寫)和分片節點的對應關系.

#testdb

TB3=gseq

3. 登陸節點主機Rep1_3306, 初始化tb3表的sequence.

mysql> insert into MYCAT_SEQUENCE(name, current_value, increment) values('TB3', 400, 100);

mysql> select * from MYCAT_SEQUENCE;

+------+---------------+-----------+

| name | current_value | increment |

+------+---------------+-----------+

| TB3  |           400 |       100 |

+------+---------------+-----------+

登陸Mycat, 驗證sequence可以正常使用.

mysql> select next value for MYCATSEQ_TB3;

+-----+

| 500 |

+-----+

| 500 |

+-----+

此時, tb3表使用sequence的工作準備好了, 下面插入數據看看.

mysql> insert into tb3(user_name) values('igkl');

ERROR 1064 (HY000): bad insert sql (sharding column:USER_ID not provided,INSERT INTO tb3 (ID, user_name)

VALUES (501, 'igkl')

mysql> insert into tb3(id, user_id, user_name) values(9, 4, 'igkl');

由于Mycat負責主鍵值id的生成, SQL語句中可省去id字段不寫(若像上面, 指定id為某值, 也沒問題).

mysql> insert into tb3(user_id, user_name) values(59, 'mnop');

mysql> select * from tb3 where user_id = 59;

+-----+---------+-----------+

| id  | user_id | user_name |

+-----+---------+-----------+

| 502 |      59 | mnop      |

+-----+---------+-----------+

查看日志, 發現Mycat對原始SQL語句進行了改寫, 添加了id字段.

03/18 20:46:36.798  DEBUG [$_NIOREACTOR-1-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=testdb, host=192.168.4.184, user=test_user,txIsolation=3, autocommit=true, schema=testdb]insert into tb3(user_id, user_name) values(59, 'mnop')

03/18 20:46:36.800  DEBUG [Thread-1] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=testdb, host=192.168.4.184, user=test_user,txIsolation=3, autocommit=true, schema=testdb]insert into tb3(ID,user_id, user_name) values( 502,59, 'mnop'), route={

   1 -> dnTest2{insert into tb3(ID,user_id, user_name) values( 502,59, 'mnop')}

03/18 20:46:36.800  DEBUG [Thread-1] (MySQLConnection.java:459) -con need syn ,total syn cmd 2 commands SET names utf8;SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;schema change:false con:MySQLConnection [id=2, lastTime=1521377196800, user=zzzz, schema=test2, old shema=test2, borrowed=true, fromSlaveDB=false, threadId=81, charset=utf8, txIsolation=0, autocommit=true, attachment=dnTest2{insert into tb3(ID,user_id, user_name) values( 502,59, 'mnop')}, respHandler=SingleNodeHandler [node=dnTest2{insert into tb3(ID,user_id, user_name) values( 502,59, 'mnop')}, packetId=0], host=192.168.4.151, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]

03/18 20:46:36.802  DEBUG [$_NIOREACTOR-2-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=2, lastTime=1521377196792, user=zzzz, schema=test2, old shema=test2, borrowed=true, fromSlaveDB=false, threadId=81, charset=utf8, txIsolation=3, autocommit=true, attachment=dnTest2{insert into tb3(ID,user_id, user_name) values( 502,59, 'mnop')}, respHandler=SingleNodeHandler [node=dnTest2{insert into tb3(ID,user_id, user_name) values( 502,59, 'mnop')}, packetId=1], host=192.168.4.151, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]

若感興趣可關注訂閱號”數據庫最佳實踐”(DBBestPractice).