MyCat自增主键

  1. 云栖社区>
  2. 博客>
  3. 正文

MyCat自增主键

技术小甜 2017-11-25 15:16:00 浏览1517
展开阅读全文


全局序列号是MyCAT提供的一个新功能,为了实现分库分表情况下,表的主键是全局唯一,而默认的MySQL的自增长主键无法满足这个要求。全局序列号的语法符合标准SQL规范,其格式为:

next value for MYCATSEQ_XXX

MYCATSEQ_XXX 是序列号的名字,MyCAT自动创建新的序列号,免去了开发的复杂度。

另外,MyCAT也提供了一个全局的序列号,名称为:MYCATSEQ_GLOBAL

注意,MYCATSEQ_必须大写才能正确识别。

MyCAT温馨提示:实践中,建议每个表用自己的序列号,序列号的命名建议为MYCATSEQ _tableName_ID_SEQ。

实现方式主要有三种:本地文件方式、数据库方式、本地时间戳算法。

、本地文件方式:

1、原理:此方式MyCAT将sequence配置到文件中,当使用到sequence中的配置后,MyCAT会更新conf中的sequence_conf.properties文件中sequence当前的值。

2、使用方式:

(1)、配置MyCat的Server.xml

其中0,表示使用本地文件方式。

<system>

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

</system>

(2)、配置sequence_conf.properties  

$ vim mycat/conf/sequence_conf.properties

#default global sequence 全局

GLOBAL.HISIDS=

GLOBAL.MINID=10001

GLOBAL.MAXID=20000

GLOBAL.CURID=10000

# self define sequence 自定义

COMPANY.HISIDS=

COMPANY.MINID=1001

COMPANY.MAXID=2000

COMPANY.CURID=1000

 

#其中HISIDS表示使用过的历史分段(一般无特殊需要可不配置),MINID表示最小ID值,MAXID表示最大ID值,CURID表示当前ID值。

# GLOBAL在这里也可以使用其他的名字,但必须是大写的;定义以后可以在全局使用。

#可以使用 mysql>select next value for MYCATSEQ_xxx(自定义的名字,这里就是MYCATSEQ_ GLOBAL); 来查看下一个自增ID。

设置完成以后重启MyCat。

3、测试

mysql>create table test(id int,name varchar(20));

mysql>insert into test(id,name) values(next value for MYCATSEQ_GLOBAL,@@hostname);

mysql> select * from test;

4、优缺点

优点:本地加载,读取速度较快。

缺点:当 MyCAT 重新发布后,配置文件中的 sequence 会恢复到初始值。

、数据库方式:

1、原理:

在数据库中建立一张表,存放sequence名称(name),sequence当前值(current_value),步长(increment int类型每次读取多少个sequence,当读取的increment用完后,

再次读取increment提高效率,显然mycat的increment和mysql的increment意义不同)等信息.

2、Sequence获取步骤:

 (1)、第一次使用该sequence时,根据传入的sequence名称,从数据库这张表中读取current_value,和increment到MyCat中,并将数据库中的current_value设置为原current_value值+increment值(实现方式是基于后续的存储函数)。

(2)、MyCat将读取到current_value+increment作为本次要使用的sequence值,下次使用时,自动加1,当使用increment次后,执行步骤1)相同的操作. MyCat负责维护这张表,用到哪些sequence,只需要在这张表中插入一条记录即可。若某次读取的sequence没有用完,系统就停掉了,则这次读取的sequence剩余值不会再使用。

3、使用方式:

(1)、配置Server.xml  , 其中1,表示使用数据库方式。

<system>

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

</system>

(2)、设置 sequence_db_conf.properties

 在mycat conf目录下的sequence_db_conf.properties文件中添加如下内容:

#sequence stored in datanode

GLOBAL=dn2

DICT=dn2

dn2:表示把表和函数都建在了dn2节点上。

注意:GLOBAL 和DICT必须为大写。

重启MyCat 

 

(3)、在其中一个分片点对应的数据库中创建表和存储函数

因我在schema.xml 中配置的是: <dataNode name="dn$1-4" dataHost="localhost1" database="db$1-4" />

譬如我在dn2中创建,对应的数据库名为db2(为什么这里会涉及到datanode,因为后续的sequence_db_conf.properties文件会使用到)。

注意,登录到数据库中创建表和存储函数,而不是在mycat中创建。   

(3.1)、创建表--  创建MYCAT_SEQUENCE表

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 1,

remark varchar(100),  -- remark 并不是必须的,在这里是为了让每一个表都对应一个全局的自增,在Remark中配置自增项对应的表名。方便后期维护

PRIMARY KEY(name)) ENGINE=InnoDB;

 

(3.2)、创建存储函数1--– 获取当前sequence的值(返回当前值,增量)

DROP FUNCTION IF EXISTS `mycat_seq_currval`;

DELIMITER ;;

CREATE DEFINER=`root`@`%` 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 ;

(3.3)、创建存储函数2-- 获取下一个sequence值

DROP FUNCTION IF EXISTS `mycat_seq_nextval`;

DELIMITER ;;

CREATE DEFINER=`root`@`%` 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 ;

(3.4)、创建存储函数3--设置sequence值

DROP FUNCTION IF EXISTS `mycat_seq_setval`;

DELIMITER ;;

CREATE DEFINER=`root`@`%` 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 ; 

 

在表MYCAT_SEQUENCE中,其中:

–name sequence名称

–current_value 当前value

–increment增长步长! 可理解为mycat在数据库中一次读取多少个sequence. 当这些用完后, 下次再从数据库中读取.

注意:MYCAT_SEQUENCE必须大写。

创建存储函数:

注意:必须在同一个数据库中创建,在本例中,是db2。一共要创建三个存储函数。

–获取当前sequence的值(返回当前值,增量)。

4、插入sequence记录:

-- 插入sequence记录

INSERT INTO MYCAT_SEQUENCE(name,current_value,increment,remark) VALUES ('DICT', 1, 100,'match:tb_dic');

INSERT INTO MYCAT_SEQUENCE(name,current_value,increment,remark) VALUES ('GLOBAL', 1, 100,'GLOBAL');

-- 代表插入了一个名为mycat的sequence,当前值为1,步长为100。

mysql> select * from mycat_sequence;

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

| name           | current_value | increment | remark                         |

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

| DICT           |             1 |       100 | match:tb_dic                   |

| GLOBAL         |           200 |       100 | GLOBAL                         |

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

至此,数据库方面的准备工作已结束完毕。

5、开始测试

$ mysql -h127.0.0.1 -utest -ptest -P8066 -DTESTDB

mysql>

create table tb_dic

(

   id                   int not null auto_increment,

   dic_name             varchar(100) not null comment '字典名称',

   dic_value            varchar(20) not null comment '字典值',

   dic_type             int not null comment '字典类型:如支付方式等',

   primary key (id)

);

然后插入值

mysql>  INSERT into tb_dic(id,dic_name,dic_value,dic_type) VALUES(next value for MYCATSEQ_DICT,'支付方式','1',0);

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

| id  | dic_name     | dic_value | dic_type |

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

| 101 | 活动形式      | 2         |        0 |

| 102 | 表单类型      | 2         |        0 |

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

错误处理: 

ERROR 1003 (HY000): mycat sequnce err.org.opencloudb.config.util.ConfigException: can't find definition for sequence :DICT

因为对于sequence_db_conf.properties的修改当前的mycat并不知晓,这时候,可重启mycat或者登录9066管理端口进行 reload @@config ;

至此,测试完毕,关键还是两点:MYCAT_SEQUENCE必须大写,sequence_db_conf.properties文件中DICT=dn2必须大写。

、本地时间戳算法

ID= 64位二进制 (42(毫秒)+5(机器ID)+5(业务编码)+12(重复累加) 。

换算成十进制为18位数的long类型,每毫秒可以并发12位二进制的累加。

1、配置server.xml

<system>

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

</system>

2、在mycat下配置:sequence_time_conf.properties

WORKID=0-31 任意整数

DATAACENTERID=0-31 任意整数

每个mycat配置的 WORKID,DATAACENTERID不同,组成唯一标识,总共支持32*32=1024种组合。

ID示例:56763083475511

 

、总结:

1、从MyCAT 1.3开始,支持自增长主键,依赖于全局序列号机制,建议采用数据库方式的全局序列号,并正确设置步长,以免影响实际性能。

 

首先要开启数据库方式的全局序列号,对于需要定义自增长主键的表,建立对应的全局序列号,与table名称同名大写,

如customer序列名为CUSTOMER,然后再 schema.xml 中对customer表的table元素增加属性autoIncrement值为true.

<table name=”CUSTOMER” autoIncrement=”true”>

2、应用如何获得自增主键:

 

MyCAT自增字段和返回生成的主键ID的经验分享

(1)、mysql本身对非自增长主键,使用last_insert_id()不会返回结果,只会返回0.

(2)、mysql只会对定义自增长主键,可以用last_insert_id()返回主键值。

(3)、mycat目前提供了自增长主键功能,但是如果对应的mysql节点上数据表,没有定义auto_increment,那么在mycat层调用last_insert_id()也是不会返回结果的。

(4)、数据库方式正确的使用方式如下:

(4.1)、mysql定义自增主键
CREATE TABLE `tt2` (
`id` bigINT(10) UNSIGNED NOT NULL AUTO_INCREMENT, //必须是自增的

`nm` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

(4.2)mycat定义自增

[root@test conf]# vim schema.xml

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://org.opencloudb/">

<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">

<!-- random sharding using mod sharind rule -->

<!-- autoIncrement="true" 属性-->

<table name="tt2" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2,dn3,dn4" rule="mod-long" />

<table name="mycat_sequence" primaryKey="name" dataNode="dn1"/>

</schema>

<dataNode name="dn1" dataHost="localhost1" database="db1" />

<dataNode name="dn2" dataHost="localhost1" database="db2" />

<dataNode name="dn3" dataHost="localhost1" database="db3" />

<dataNode name="dn4" dataHost="localhost1" database="db4" />

<dataHost name="localhost1" maxCon="1000" minCon="20" balance="1" writeType="1" dbType="mysql" dbDriver="native">

<heartbeat>select user()</heartbeat>

<writeHost host="hostM1" url="127.0.0.1:3306" user="root" password="123456">

</writeHost>

</dataHost>

</mycat:schema>

(4.3)mycat对应sequence_db_conf.properties增加相应设置;并在mycat的对应mycat_sequence增加对应记录。

(4.4)连接mycat,测试结果如下:

127.0.0.1/root:[TESTDB> insert into tt2(nm) values (99);

Query OK, 1 row affected (0.14 sec)

 

127.0.0.1/root:[TESTDB> select last_insert_id();

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

| LAST_INSERT_ID() |

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

| 101 |

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

1 row in set (0.01 sec)

 

(4.5)、关于批量插入使用:

A、使用普通的序列号批量插入 :

insert(a,b,c) values(x,x,x),(x,x,x);

b、使用全局序列号批量插入,必须加注解:

/*!mycat:catlet=demo.catlets.BatchInsertSequence */

insert(a,b,c) values(x,x,x),(x,x,x);

c、是sharding key 必须包含在列枚举中,特别是主键是自增的时候必须显示调用:

/*!mycat:catlet=demo.catlets.BatchInsertSequence */

insert(id,a,b,c) values(next value for MYCATSEQ_ID,x,x,x),(next value for MYCATSEQ_ID,x,x,x);
















本文转自lzf0530377451CTO博客,原文链接:http://blog.51cto.com/8757576/2054713 ,如需转载请自行联系原作者





网友评论

登录后评论
0/500
评论
技术小甜
+ 关注