mycat sequence
数据库方式
原理
在数据库中建立一张表，存放sequence名称(name)，sequence当前值(current_value)，步长(increment int类型每次读取多少个sequence，假设为K)等信息；
Sequence获取步骤：
1).当初次使用该sequence时，根据传入的sequence名称，从数据库这张表中读取current_value，
和increment到MyCat中，并将数据库中的current_value设置为原current_value值+increment值；
.MyCat将读取到current_value+increment作为本次要使用的sequence值，下次使用时，自动加1，
当使用increment次后，执行步骤1)相同的操作.
MyCat负责维护这张表，用到哪些sequence，只需要在这张表中插入一条记录即可。若某次读取的
sequence没有用完，系统就停掉了，则这次读取的sequence剩余值不会再使用。
创建一个 person表，主键为Id，hash方式分片，主键自增（采用数据库方式），当自增的step分别为10,100,1万的三种情况下，对此表做性能测试。
person表结构如下
Id，主键，Mycat自增主键
name,字符串，16字节最长
school,毕业学校，数字，1-1000范围，是学校编号
age，年龄，18-60
addr,地址，32字节，建议为 gz-tianhe（城市-地区两级 枚举的仿真数据）
zcode,邮编，
birth，生日，为日期类型， 1980到2010年之间随机的日期
score，得分，0-100分
1 自增主键（数据库方式）
[mysql@hongquan ~]$ cd /usr/local/mycat/
[mysql@hongquan mycat]$ ll
total 8
drwxr-xr-x 8 mycat mycat 4096 Jun 22 03:13 jdk1.8.0_101
drwxr-xr-x 7 mycat mycat 4096 Oct 11 03:05 mycat
server.xml配置：
<system><property name="sequnceHandlerType">1</property></system>
注：sequnceHandlerType 需要配置为1，表示使用数据库方式生成sequence.
数据库配置:
创建表，函数
use db1;
创建表
DROP TABLE IF EXISTS MYCAT_SEQUENCE;
CREATE TABLE MYCAT_SEQUENCE ( name VARCHAR(50) NOT NULL, current_value INT NOT NULL, increment INT NOT NULL DEFAULT 100, PRIMARY KEY (name) ) ENGINE=InnoDB;
创建函数
DROP FUNCTION IF EXISTS `mycat_seq_currval`;
DELIMITER ;;
CREATE DEFINER=`system`@`%` FUNCTION `mycat_seq_currval`(seq_name VARCHAR(50)) RETURNS varchar(64) CHARSET latin1
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 ;
DROP FUNCTION IF EXISTS `mycat_seq_nextval`;
DELIMITER ;;
CREATE DEFINER=`system`@`%` FUNCTION `mycat_seq_nextval`(seq_name VARCHAR(50)) RETURNS varchar(64) CHARSET latin1
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = current_value + increment WHERE name = seq_name;
RETURN mycat_seq_currval(seq_name);
END
;;
DELIMITER ;
DROP FUNCTION IF EXISTS `mycat_seq_setval`;
DELIMITER ;;
CREATE DEFINER=`system`@`%` FUNCTION `mycat_seq_setval`(seq_name VARCHAR(50), value INTEGER) RETURNS varchar(64) CHARSET latin1
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = value
WHERE name = seq_name;
RETURN mycat_seq_currval(seq_name);
END
;;
DELIMITER ;
INSERT INTO db1.MYCAT_SEQUENCE(name,current_value,increment) VALUES ('GLOBAL', 1, 1);
[root@hqmysql1 conf]# vim sequence_db_conf.properties
sequence_db_conf.properties相关配置，指定sequence相关配置在哪个节点上：
USER_SEQ=dn1
TABLE1=dn1
INSERT INTO db1.MYCAT_SEQUENCE(name,current_value,increment) VALUES ('GLOBAL', 1, 1);
INSERT INTO db1.MYCAT_SEQUENCE(name,current_value,increment) VALUES ('COMPANY', 2, 10);
INSERT INTO db1.MYCAT_SEQUENCE(name,current_value,increment) VALUES ('TABLE1', 2, 10);
--重启mycat
[root@hqmysql1 bin]# ./mycat stop
Stopping Mycat-server...
Stopped Mycat-server.
[root@hqmysql1 bin]# ./mycat start
Starting Mycat-server...
select * from MYCAT_SEQUENCE;
select next value for MYCATSEQ_GLOBAL,next value for MYCATSEQ_GLOBAL;
select next value for MYCATSEQ_TABLE1,next value for MYCATSEQ_COMPANY;
caught err: com.alibaba.druid.sql.parser.ParserException: syntax error, error in :' for MYCATSEQ_GLOBAL,next value for',expect UPDATE, actual IDENTIFIER MYCATSEQ_GLOBAL
错误：
mysql> insert into table2(name_) values(1);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> select next value for MYCATSEQ_TABLE2,next value for MYCATSEQ_TABLE2;
+--------------------+--------------------+
| 804585874594598912 | 804585874594598912 |
+--------------------+--------------------+
| 804585874594598912 | 804585874594598912 |
--重启mycat，正常，难道是reload @@config_all;对server.xml的配置不生效？？
mysql> select next value for MYCATSEQ_TABLE2,next value for MYCATSEQ_TABLE2;
+---+---+
| 2 | 2 |
+---+---+
| 2 | 2 |
mysql> insert into table2(id,name_) values(next value for MYCATSEQ_TABLE2,2);
Query OK, 1 row affected (0.14 sec)
mysql> insert into table2(id,name_) values(next value for MYCATSEQ_TABLE2,2);
Query OK, 1 row affected (0.15 sec)
mysql> insert into table2(id,name_) values(next value for MYCATSEQ_TABLE2,2);
Query OK, 1 row affected (0.11 sec)
mysql> select * from table2;
+----+-------+
| id | name_ |
+----+-------+
| 4 | 2 |
| 3 | 2 |
| 5 | 2 |
+----+-------+
3 rows in set (0.10 sec)
<table name="table1" primaryKey="id_" autoIncrement="true" dataNode="dn1,dn2,dn3" rule="mod-long" />
CREATE TABLE table1(
‘id_’ INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
‘name_’ INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (‘id_’)
) ENGINE=MYISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
---
<table name="person1" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2,dn3" rule="mod-long" />
<table name="person2" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2,dn3" rule="mod-long" />
<table name="person3" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2,dn3" rule="mod-long" />
INSERT INTO db1.MYCAT_SEQUENCE(name,current_value,increment) VALUES ('PERSON1', 1, 10);
INSERT INTO db1.MYCAT_SEQUENCE(name,current_value,increment) VALUES ('PERSON2', 1, 100);
INSERT INTO db1.MYCAT_SEQUENCE(name,current_value,increment) VALUES ('PERSON3', 1, 1000);
PERSON1=dn1
PERSON2=dn1
PERSON3=dn1
create table person1(
id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
name varchar(16) not null,
school int(4) not null,
age int(3) not null,
addr varchar(32),
zcode int(10),
birth datetime,
score int(3),PRIMARY KEY (id))ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
insert into person1(id,name,school,age,addr,zcode,birth,score)
values(next value for MYCATSEQ_PERSON1,'xiaoming',100,19,'gz-tianhe',1254565,now(),90);
2016-12-02 00:59:22.081 DEBUG [Thread-1] (io.mycat.server.NonBlockingSession.execute(NonBlockingSession.java:119)) - ServerConnection [id=5, schema=TESTDB, host=10.0.1.134, user=system,txIsolation=3, autocommit=true, schema=TESTDB]insert into person3(id,name,school,age,addr,zcode,birth,score)
values( 1001,'xiaoming',100,19,'gz-tianhe',1254565,now(),90), route={
1 -> dn3{insert into person3(id,name,school,age,addr,zcode,birth,score)
values( 1001,'xiaoming',100,19,'gz-tianhe',1254565,now(),90)}
} rrs
2016-12-02 00:59:22.081 DEBUG [Thread-1] (io.mycat.backend.mysql.nio.handler.SingleNodeHandler.execute(SingleNodeHandler.java:173)) - rrs.getRunOnSlave() null
2016-12-02 00:59:22.081 DEBUG [Thread-1] (io.mycat.backend.mysql.nio.handler.SingleNodeHandler.execute(SingleNodeHandler.java:175)) - node.getRunOnSlave() null
2016-12-02 00:59:22.082 DEBUG [Thread-1] (io.mycat.backend.mysql.nio.handler.SingleNodeHandler.execute(SingleNodeHandler.java:184)) - node.getRunOnSlave() null
2016-12-02 00:59:22.082 DEBUG [Thread-1] (io.mycat.backend.mysql.nio.handler.SingleNodeHandler.execute(SingleNodeHandler.java:186)) - node.getRunOnSlave() null
2016-12-02 00:59:22.082 DEBUG [Thread-1] (io.mycat.backend.datasource.PhysicalDBNode.getConnection(PhysicalDBNode.java:96)) - rrs.getRunOnSlave() null
2016-12-02 00:59:22.082 DEBUG [Thread-1] (io.mycat.backend.datasource.PhysicalDBNode.getConnection(PhysicalDBNode.java:127)) - rrs.getRunOnSlave() null
2016-12-02 00:59:22.105 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.server.NonBlockingSession.releaseConnection(NonBlockingSession.java:354)) - release connection MySQLConnection [id=34, lastTime=1480669162082, user=system, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=1208094, charset=utf8, txIsolation=3, autocommit=true, attachment=dn3{insert into person3(id,name,school,age,addr,zcode,birth,score)
values( 1001,'xiaoming',100,19,'gz-tianhe',1254565,now(),90)}, respHandler=SingleNodeHandler [node=dn3{insert into person3(id,name,school,age,addr,zcode,birth,score)
values( 1001,'xiaoming',100,19,'gz-tianhe',1254565,now(),90)}, packetId=1], host=10.0.1.134, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
