Mycat学习实战-Mycat全局主键
Mycat学习实战-Mycat全局主键
[TOC]
1. Mycat全局主键介绍
在分库分表的情况下,数据库自增主键无法保证自增主键的全局唯一。
全局序列号的语法符合标准SQL规范,其格式为:
next value for MYCATSEQ_XXX
MYCATSEQ_XXX
是序列号的名字,MyCAT自动创建新的序列号,免去了开发的复杂度,另外,MyCAT也提供了一个全局的序列号,名称为:MYCATSEQ_GLOBAL
注意,MYCATSEQ_
必须大写才能正确识别。
MyCAT温馨提示:实践中,建议每个表用自己的序列号,序列号的命名建议为MYCATSEQ _tableName_ID_SEQ
。
SQL中使用说明
自定义序列号的标识为:MYCATSEQ_XXX
,其中XXX
为具体定义的sequence
的名称,应用举例如下:
使用默认的全局sequence
:
insert into tb1(id,name) values(next value for MYCATSEQ_GLOBAL,'tb1');
使用自定义的 sequence
:
insert into tb2(id,name) values(next value for MYCATSEQ_MY1,'tb2');
获取最新的值
select next value for MYCATSEQ_xxx
2. Mycat全局主键方式
Mycat提供的全局主键方式如下:
1. 本地文件方式:使用服务器本地磁盘文件的方式
2. 数据库方式:使用数据库的方式
3. 本地时间戳方式:使用时间戳方式
4. 分布式zookeeper生成ID
2.1 本地文件方式
vim conf/server.xml
1 2 |
<property name="sequnceHandlerType">0</property> |
vim conf/sequence_conf.properties
1 2 3 4 5 6 7 8 9 10 11 12 |
#default global sequence GLOBAL.HISIDS= GLOBAL.MINID=10001 GLOBAL.MAXID=20000 GLOBAL.CURID=10000 # self define sequence ID_LOCAL_FILE.HISIDS= ID_LOCAL_FILE.MINID=1001 ID_LOCAL_FILE.MAXID=2000 ID_LOCAL_FILE.CURID=1000 |
- 以上配置文件中,自定义表名必须大写书写
- HISIDS:表示使用过的历史分段(一般
- 无特殊需要可不配置)
- MINID :最小ID 值
- MAXID :表示最大ID 值
- CURID 表示当前ID 值。
- 当 sequence_conf.properties的配置名字与 表名一致的时候sql可以不包含ID字段(此处表名为
id_local_file
)
vim conf/schema.xml
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="test" checkSQLschema="false" sqlMaxLimit="100"> <table name="id_local_file" dataNode="test1" autoIncrement="true" primaryKey="id"></table> </schema> <dataNode name="test1" dataHost="testA" database="test" /> <dataHost name="testA" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select 1</heartbeat> <writeHost host="hostM1" url="192.168.33.11:3306" user="root" password="123456" /> </dataHost> </mycat:schema> |
实验验证:
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 |
[root@testA conf]# mysql -uroot -p123456 -P8066 -h 127.0.0.1 test mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE TABLE `id_local_file` (`id` varchar(20) NOT NULL ,`nm` varchar(60) NULL ,PRIMARY KEY (`id`)); Query OK, 0 rows affected (0.06 sec) mysql> insert into id_local_file(id,nm) values(next value for MYCATSEQ_GLOBAL,'id_local_file'); Query OK, 1 row affected (0.03 sec) mysql> insert into id_local_file(nm) values('id_local_file'); /* 插入的sql语句里没有了自增ID字段 */ Query OK, 1 row affected (0.01 sec) mysql> select * from id_local_file; +-------+---------------+ | id | nm | +-------+---------------+ | 10001 | id_local_file | | 1001 | id_local_file | +-------+---------------+ 2 rows in set (0.08 sec) mysql> select next value for MYCATSEQ_GLOBAL; +-------+ | 10002 | +-------+ | 10002 | +-------+ 1 row in set (0.00 sec) mysql> |
优点:本地加载,读取速度较快,配置简单
缺点:mycat重新发布时,seq文件需要替换,集群部署无法用此方式,路由到不同的mycat上无法保证id唯一,使mycat变成了有状态的中间件。
2.2 本地时间戳方式
vim conf/server.xml
1 2 |
<property name="sequnceHandlerType">2</property> |
vim conf/sequence_time_conf.properties
1 2 3 4 |
#sequence depend on TIME WORKID=01 DATAACENTERID=01 |
两个属性值为:0-31 任意整数
vim conf/schema.xml
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="test" checkSQLschema="false" sqlMaxLimit="100"> <table name="id_local_time" dataNode="test1" autoIncrement="true" primaryKey="id"></table> </schema> <dataNode name="test1" dataHost="testA" database="test" /> <dataHost name="testA" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select 1</heartbeat> <writeHost host="hostM1" url="192.168.33.11:3306" user="root" password="123456" /> </dataHost> </mycat:schema> |
实验验证:
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 |
[root@testA conf]# mysql -uroot -p123456 -P8066 -h 127.0.0.1 test mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE TABLE `id_local_time` (`id` varchar(20) NOT NULL ,`nm` varchar(60) NULL ,PRIMARY KEY (`id`)); Query OK, 0 rows affected (0.02 sec) mysql> insert into id_local_time(id,nm) values(next value for MYCATSEQ_GLOBAL,'id_local_time'); Query OK, 1 row affected (0.06 sec) mysql> insert into id_local_time(nm) values('id_local_time'); /* 插入的sql语句里没有了自增ID字段 */ Query OK, 1 row affected (0.01 sec) mysql> select * from id_local_time; +--------------------+---------------+ | id | nm | +--------------------+---------------+ | 922641363168792576 | id_local_time | | 922641424359493632 | id_local_time | +--------------------+---------------+ 2 rows in set (0.06 sec) mysql> select next value for MYCATSEQ_GLOBAL; +--------------------+ | 922641542101995520 | +--------------------+ | 922641542101995520 | +--------------------+ 1 row in set (0.00 sec) |
本地时间戳计算方式
ID= 64 位二进制 (42(毫秒)+5(机器 ID)+5(业务编码)+12(重复累加)
长度18位,因此下面提示非常重要。
注意
表字段长度必须大于等于18位
优点:不存在mycat重新发布影响seq的问题,
缺点:字段长度是18位。
2.3 数据库方式
vim conf/server.xml
1 2 |
<property name="sequnceHandlerType">1</property> |
vim conf/sequence_db_conf.properties
1 2 3 4 |
#sequence stored in datanode GLOBAL=test1 ID_DB=test1 |
在test1节点本地数据库添加函数和表,以下为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 |
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 ; INSERT INTO mycat_sequence(name,current_value,increment) VALUES ('GLOBAL', 100000, 100); DROP FUNCTION IF EXISTS `mycat_seq_currval`; DELIMITER ;; CREATE FUNCTION `mycat_seq_currval`(seq_name VARCHAR(50)) RETURNS varchar(64) CHARSET utf8 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 FUNCTION `mycat_seq_nextval`(seq_name VARCHAR(50)) RETURNS varchar(64) CHARSET utf8 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 FUNCTION `mycat_seq_setval`(seq_name VARCHAR(50), value INTEGER) RETURNS varchar(64) CHARSET utf8 DETERMINISTIC BEGIN UPDATE mycat_sequence SET current_value = value WHERE name = seq_name; RETURN mycat_seq_currval(seq_name); END ;; DELIMITER ; |
添加过程:
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 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 |
[root@testA mycat]# mysql -uroot -p123456 test mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 102 Server version: 5.7.19-log Source distribution Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> DROP TABLE IF EXISTS mycat_sequence; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> 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 ; INSERT INTO mycat_sequence(name,current_value,increment) VALUES ('GLOBAL', 100000, 100); Query OK, 0 rows affected (0.11 sec) mysql> mysql> mysql> mysql> mysql> INSERT INTO mycat_sequence(name,current_value,increment) VALUES ('GLOBAL', 100000, 100); Query OK, 1 row affected (0.00 sec) mysql> mysql> mysql> DROP FUNCTION IF EXISTS `mycat_seq_currval`; Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ;; mysql> CREATE FUNCTION `mycat_seq_currval`(seq_name VARCHAR(50)) -> RETURNS varchar(64) CHARSET utf8 -> 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 -> ;; Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> mysql> mysql> DROP FUNCTION IF EXISTS `mycat_seq_nextval`; DELIMITER ;; Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ;; mysql> CREATE FUNCTION `mycat_seq_nextval`(seq_name VARCHAR(50)) RETURNS varchar(64) -> CHARSET utf8 -> DETERMINISTIC -> BEGIN -> UPDATE mycat_sequence -> SET current_value = current_value + increment -> WHERE name = seq_name; -> RETURN mycat_seq_currval(seq_name); -> END -> ;; Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> mysql> mysql> mysql> mysql> DROP FUNCTION IF EXISTS `mycat_seq_setval`; Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ;; mysql> CREATE FUNCTION `mycat_seq_setval`(seq_name VARCHAR(50), value INTEGER) -> RETURNS varchar(64) CHARSET utf8 -> DETERMINISTIC -> BEGIN -> UPDATE mycat_sequence -> SET current_value = value -> WHERE name = seq_name; -> RETURN mycat_seq_currval(seq_name); -> END -> ;; Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> |
以下步骤非常关键,让id_db
表也支持数据库序列号。
1 2 3 4 5 6 7 8 9 10 |
mysql> INSERT INTO mycat_sequence ('ID_DB', 1, 100); mysql> select * from mycat_sequence; +--------+---------------+-----------+ | NAME | current_value | increment | +--------+---------------+-----------+ | GLOBAL | 100200 | 100 | | ID_DB | 301 | 100 | +--------+---------------+-----------+ 2 rows in set (0.00 sec) |
vim conf/schema.xml
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="test" checkSQLschema="false" sqlMaxLimit="100"> <table name="id_db" dataNode="test1" autoIncrement="true" primaryKey="id"></table> <table name="mycat_sequence" dataNode="test1" autoIncrement="true" primaryKey="id"></table> </schema> <dataNode name="test1" dataHost="testA" database="test" /> <dataHost name="testA" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select 1</heartbeat> <writeHost host="hostM1" url="192.168.33.11:3306" user="root" password="123456" /> </dataHost> </mycat:schema> |
注意
将mycat_sequence表也放出来,且注意大小写(数据库默认区分大小写)
实验验证:
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 68 69 70 71 72 73 74 75 76 77 |
[root@testA mycat]# mysql -uroot -p123456 -P8066 -h127.0.0.1 test mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +----------+ | DATABASE | +----------+ | test | +----------+ 1 row in set (0.00 sec) mysql> show tables; +----------------+ | Tables in test | +----------------+ | id_db | | mycat_sequence | +----------------+ 2 rows in set (0.00 sec) mysql> drop id_db; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'id_db' at line 1 mysql> drop table id_db; Query OK, 0 rows affected (0.08 sec) mysql> CREATE TABLE `id_db` (`id` int NOT NULL ,`nm` varchar(60) NULL ,PRIMARY KEY (`id`)); Query OK, 0 rows affected (0.02 sec) mysql> insert into id_db(id,nm) values(next value for MYCATSEQ_GLOBAL,'id_db'); Query OK, 1 row affected (0.10 sec) mysql> insert into id_db(nm) values('db'); Query OK, 1 row affected (0.00 sec) mysql> select next value for MYCATSEQ_GLOBAL; +--------+ | 100201 | +--------+ | 100201 | +--------+ 1 row in set (0.00 sec) mysql> select * from id_db; +--------+-------+ | id | nm | +--------+-------+ | 303 | db | | 100200 | id_db | +--------+-------+ 2 rows in set (0.00 sec) mysql> insert into id_db(nm) values('db'); Query OK, 1 row affected (0.01 sec) mysql> select * from id_db; +--------+-------+ | id | nm | +--------+-------+ | 303 | db | | 304 | db | | 100200 | id_db | +--------+-------+ 3 rows in set (0.00 sec) mysql> |
优点:重新部署mycat不受影响
缺点:当配置节点的部署是主从复制,当主挂了切从后会有重复。
注意
节点如果是主从切换后,数据id可能会有异常(重复)
2.4 zookeeper方式
vim conf/server.xml
1 2 |
<property name="sequnceHandlerType">3</property> |
vim conf/sequence_distributed_conf.properties
1 2 3 |
INSTANCEID=01 CLUSTERID=01 |
schema的table 增加属性 autoIncrement="true"
和 primaryKey="id"
基于ZK 与本地配置的分布式ID 生成器(可以通过ZK 获取集群(机房)唯一InstanceID,也可以通过配置文件配置InstanceID)ID 结构:long 64 位,ID 最大可占63 位
current time millis(微秒时间戳38 位,可以使用17 年)
instanceId(实例ID,可以通过ZK 或者配置文件获取,5 位,也就是十进制0-31)
threadId(线程ID,9 位)
increment(自增,6 位)
一共63 位,可以承受单机房单机器单线程1000*(2^6)=640000 的并发。
优点:无悲观锁,无强竞争,吞吐量更高
缺点:对zookeeper集群的要求增加。
参考资料:
[1] http://mycat.io/
[2] 《分布式数据库架构及企业实践——基于Mycat中间件》
[3] 龙哥官方课程课件、博客
微信扫描下方的二维码阅读本文
reid
2017年11月22日 18:17
时间戳方式生成的 id 全是偶数的,如果datanode 为两个,分片方式为mod-long,那么全部数据都只分到其中一个库里,不知道有没有解决办法?
ygqygq2
2017年12月5日 17:03
时间戳方式生成的 id 全是偶数,这个我不清楚,可以具体查看mycat源码。但你说的这种情况为什么不多加些节点呢,比如3个节点。工具是为了让我们更加方便,应该避免其缺陷,使用其优点。