Mycat学习实战-Mycat全局主键

@(学习)[mycat, mysql]

Mycat学习实战-Mycat全局主键

1. Mycat全局主键介绍

2. Mycat全局主键方式

2.1 本地文件方式

2.2 本地时间戳方式

2.3 数据库方式

2.4 zookeeper方式

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提供的全局主键方式如下：

本地文件方式：使用服务器本地磁盘文件的方式

数据库方式：使用数据库的方式

本地时间戳方式：使用时间戳方式

分布式zookeeper生成ID

2.1 本地文件方式

vim conf/server.xml

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

vim conf/sequence_conf.properties

#defaultglobalsequenceGLOBAL.HISIDS=GLOBAL.MINID=10001GLOBAL.MAXID=20000GLOBAL.CURID=10000#selfdefinesequenceID_LOCAL_FILE.HISIDS=ID_LOCAL_FILE.MINID=1001ID_LOCAL_FILE.MAXID=2000ID_LOCAL_FILE.CURID=1000

以上配置文件中，自定义表名必须大写书写

HISIDS：表示使用过的历史分段(一般

无特殊需要可不配置)

MINID ：最小ID 值

MAXID ：表示最大ID 值

CURID 表示当前ID 值。

当 sequence_conf.properties的配置名字与 表名一致的时候sql可以不包含ID字段（此处表名为id_local_file）

vim conf/schema.xml

<?xmlversion="1.0"?><!DOCTYPEmycat:schemaSYSTEM"schema.dtd"><mycat:schemaxmlns:mycat="http://io.mycat/"><schemaname="test"checkSQLschema="false"sqlMaxLimit="100"><tablename="id_local_file"dataNode="test1"autoIncrement="true"primaryKey="id"></table></schema><dataNodename="test1"dataHost="testA"database="test"/><dataHostname="testA"maxCon="1000"minCon="10"balance="1"writeType="0"dbType="mysql"dbDriver="native"switchType="1"slaveThreshold="100"><heartbeat>select1</heartbeat><writeHosthost="hostM1"url="192.168.33.11:3306"user="root"password="123456"/></dataHost></mycat:schema>

实验验证：

[root@testAconf]#mysql-uroot-p123456-P8066-h127.0.0.1testmysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.WelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis3Serverversion:5.6.29-mycat-1.6-RELEASE-20161028204710MyCatServer(OpenCloundDB)Copyright(c)2000,2017,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>CREATETABLE`id_local_file`(`id`varchar(20)NOTNULL,`nm`varchar(60)NULL,PRIMARYKEY(`id`));QueryOK,0rowsaffected(0.06sec)mysql>insertintoid_local_file(id,nm)values(nextvalueforMYCATSEQ_GLOBAL,'id_local_file');QueryOK,1rowaffected(0.03sec)mysql>insertintoid_local_file(nm)values('id_local_file');/*插入的sql语句里没有了自增ID字段*/QueryOK,1rowaffected(0.01sec)mysql>select*fromid_local_file;+-------+---------------+|id|nm|+-------+---------------+|10001|id_local_file||1001|id_local_file|+-------+---------------+2rowsinset(0.08sec)mysql>selectnextvalueforMYCATSEQ_GLOBAL;+-------+|10002|+-------+|10002|+-------+1rowinset(0.00sec)mysql>

优点：本地加载，读取速度较快，配置简单
缺点：mycat重新发布时，seq文件需要替换，集群部署无法用此方式，路由到不同的mycat上无法保证id唯一，使mycat变成了有状态的中间件。

2.2 本地时间戳方式

vim conf/server.xml

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

vim conf/sequence_time_conf.properties

#sequencedependonTIMEWORKID=01DATAACENTERID=01

两个属性值为：0-31 任意整数

vim conf/schema.xml

<?xmlversion="1.0"?><!DOCTYPEmycat:schemaSYSTEM"schema.dtd"><mycat:schemaxmlns:mycat="http://io.mycat/"><schemaname="test"checkSQLschema="false"sqlMaxLimit="100"><tablename="id_local_time"dataNode="test1"autoIncrement="true"primaryKey="id"></table></schema><dataNodename="test1"dataHost="testA"database="test"/><dataHostname="testA"maxCon="1000"minCon="10"balance="1"writeType="0"dbType="mysql"dbDriver="native"switchType="1"slaveThreshold="100"><heartbeat>select1</heartbeat><writeHosthost="hostM1"url="192.168.33.11:3306"user="root"password="123456"/></dataHost></mycat:schema>

实验验证：

[root@testAconf]#mysql-uroot-p123456-P8066-h127.0.0.1testmysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.WelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis2Serverversion:5.6.29-mycat-1.6-RELEASE-20161028204710MyCatServer(OpenCloundDB)Copyright(c)2000,2017,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>CREATETABLE`id_local_time`(`id`varchar(20)NOTNULL,`nm`varchar(60)NULL,PRIMARYKEY(`id`));QueryOK,0rowsaffected(0.02sec)mysql>insertintoid_local_time(id,nm)values(nextvalueforMYCATSEQ_GLOBAL,'id_local_time');QueryOK,1rowaffected(0.06sec)mysql>insertintoid_local_time(nm)values('id_local_time');/*插入的sql语句里没有了自增ID字段*/QueryOK,1rowaffected(0.01sec)mysql>select*fromid_local_time;+--------------------+---------------+|id|nm|+--------------------+---------------+|922641363168792576|id_local_time||922641424359493632|id_local_time|+--------------------+---------------+2rowsinset(0.06sec)mysql>selectnextvalueforMYCATSEQ_GLOBAL;+--------------------+|922641542101995520|+--------------------+|922641542101995520|+--------------------+1rowinset(0.00sec)

本地时间戳计算方式
ID= 64 位二进制 (42(毫秒)+5(机器 ID)+5(业务编码)+12(重复累加)
长度18位，因此下面提示非常重要。

注意
表字段长度必须大于等于18位

优点：不存在mycat重新发布影响seq的问题，
缺点：字段长度是18位。

2.3 数据库方式

vim conf/server.xml

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

vim conf/sequence_db_conf.properties

#sequencestoredindatanodeGLOBAL=test1ID_DB=test1

在test1节点本地数据库添加函数和表，以下为sql内容：

DROPTABLEIFEXISTSmycat_sequence;CREATETABLEmycat_sequence(NAMEVARCHAR(50)NOTNULL,current_valueINTNOTNULL,incrementINTNOTNULLDEFAULT100,PRIMARYKEY(NAME))ENGINE=INNODB;INSERTINTOmycat_sequence(name,current_value,increment)VALUES('GLOBAL',100000,100);DROPFUNCTIONIFEXISTS`mycat_seq_currval`;DELIMITER;;CREATEFUNCTION`mycat_seq_currval`(seq_nameVARCHAR(50))RETURNSvarchar(64)CHARSETutf8DETERMINISTICBEGINDECLAREretvalVARCHAR(64);SETretval="-999999999,null";SELECTconcat(CAST(current_valueASCHAR),",",CAST(incrementASCHAR))INTOretvalFROMmycat_sequenceWHEREname=seq_name;RETURNretval;END;;DELIMITER;DROPFUNCTIONIFEXISTS`mycat_seq_nextval`;DELIMITER;;CREATEFUNCTION`mycat_seq_nextval`(seq_nameVARCHAR(50))RETURNSvarchar(64)CHARSETutf8DETERMINISTICBEGINUPDATEmycat_sequenceSETcurrent_value=current_value+incrementWHEREname=seq_name;RETURNmycat_seq_currval(seq_name);END;;DELIMITER;DROPFUNCTIONIFEXISTS`mycat_seq_setval`;DELIMITER;;CREATEFUNCTION`mycat_seq_setval`(seq_nameVARCHAR(50),valueINTEGER)RETURNSvarchar(64)CHARSETutf8DETERMINISTICBEGINUPDATEmycat_sequenceSETcurrent_value=valueWHEREname=seq_name;RETURNmycat_seq_currval(seq_name);END;;DELIMITER;

添加过程：

[root@testAmycat]#mysql-uroot-p123456testmysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.WelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis102Serverversion:5.7.19-logSourcedistributionCopyright(c)2000,2017,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>DROPTABLEIFEXISTSmycat_sequence;QueryOK,0rowsaffected,1warning(0.00sec)mysql>CREATETABLEmycat_sequence(->NAMEVARCHAR(50)NOTNULL,->current_valueINTNOTNULL,->incrementINTNOTNULLDEFAULT100,->PRIMARYKEY(NAME)->)ENGINE=INNODB;INSERTINTOmycat_sequence(name,current_value,increment)VALUES('GLOBAL',100000,100);QueryOK,0rowsaffected(0.11sec)mysql>mysql>mysql>mysql>mysql>INSERTINTOmycat_sequence(name,current_value,increment)VALUES('GLOBAL',100000,100);QueryOK,1rowaffected(0.00sec)mysql>mysql>mysql>DROPFUNCTIONIFEXISTS`mycat_seq_currval`;QueryOK,0rowsaffected(0.00sec)mysql>DELIMITER;;mysql>CREATEFUNCTION`mycat_seq_currval`(seq_nameVARCHAR(50))->RETURNSvarchar(64)CHARSETutf8->DETERMINISTIC->BEGIN->DECLAREretvalVARCHAR(64);->SETretval="-999999999,null";->SELECTconcat(CAST(current_valueASCHAR),",",CAST(incrementASCHAR))INTOretval->FROMmycat_sequenceWHEREname=seq_name;->RETURNretval;->END->;;QueryOK,0rowsaffected(0.00sec)mysql>DELIMITER;mysql>mysql>mysql>DROPFUNCTIONIFEXISTS`mycat_seq_nextval`;DELIMITER;;QueryOK,0rowsaffected(0.00sec)mysql>DELIMITER;;mysql>CREATEFUNCTION`mycat_seq_nextval`(seq_nameVARCHAR(50))RETURNSvarchar(64)->CHARSETutf8->DETERMINISTIC->BEGIN->UPDATEmycat_sequence->SETcurrent_value=current_value+increment->WHEREname=seq_name;->RETURNmycat_seq_currval(seq_name);->END->;;QueryOK,0rowsaffected(0.00sec)mysql>DELIMITER;mysql>mysql>mysql>mysql>mysql>DROPFUNCTIONIFEXISTS`mycat_seq_setval`;QueryOK,0rowsaffected(0.00sec)mysql>DELIMITER;;mysql>CREATEFUNCTION`mycat_seq_setval`(seq_nameVARCHAR(50),valueINTEGER)->RETURNSvarchar(64)CHARSETutf8->DETERMINISTIC->BEGIN->UPDATEmycat_sequence->SETcurrent_value=value->WHEREname=seq_name;->RETURNmycat_seq_currval(seq_name);->END->;;QueryOK,0rowsaffected(0.00sec)mysql>DELIMITER;mysql>

以下步骤非常关键，让id_db表也支持数据库序列号。

mysql>INSERTINTOmycat_sequence('ID_DB',1,100);mysql>select*frommycat_sequence;+--------+---------------+-----------+|NAME|current_value|increment|+--------+---------------+-----------+|GLOBAL|100200|100||ID_DB|301|100|+--------+---------------+-----------+2rowsinset(0.00sec)

vim conf/schema.xml

<?xmlversion="1.0"?><!DOCTYPEmycat:schemaSYSTEM"schema.dtd"><mycat:schemaxmlns:mycat="http://io.mycat/"><schemaname="test"checkSQLschema="false"sqlMaxLimit="100"><tablename="id_db"dataNode="test1"autoIncrement="true"primaryKey="id"></table><tablename="mycat_sequence"dataNode="test1"autoIncrement="true"primaryKey="id"></table></schema><dataNodename="test1"dataHost="testA"database="test"/><dataHostname="testA"maxCon="1000"minCon="10"balance="1"writeType="0"dbType="mysql"dbDriver="native"switchType="1"slaveThreshold="100"><heartbeat>select1</heartbeat><writeHosthost="hostM1"url="192.168.33.11:3306"user="root"password="123456"/></dataHost></mycat:schema>

注意
将mycat_sequence表也放出来，且注意大小写（数据库默认区分大小写）

实验验证：

[root@testAmycat]#mysql-uroot-p123456-P8066-h227.0.0.1testmysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.WelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis2Serverversion:5.6.29-mycat-1.6-RELEASE-20161028204710MyCatServer(OpenCloundDB)Copyright(c)2000,2017,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>showdatabases;+----------+|DATABASE|+----------+|test|+----------+1rowinset(0.00sec)mysql>showtables;+----------------+|Tablesintest|+----------------+|id_db||mycat_sequence|+----------------+2rowsinset(0.00sec)mysql>dropid_db;ERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'id_db'atline1mysql>droptableid_db;QueryOK,0rowsaffected(0.08sec)mysql>CREATETABLE`id_db`(`id`intNOTNULL,`nm`varchar(60)NULL,PRIMARYKEY(`id`));QueryOK,0rowsaffected(0.02sec)mysql>insertintoid_db(id,nm)values(nextvalueforMYCATSEQ_GLOBAL,'id_db');QueryOK,1rowaffected(0.10sec)mysql>insertintoid_db(nm)values('db');QueryOK,1rowaffected(0.00sec)mysql>selectnextvalueforMYCATSEQ_GLOBAL;+--------+|100201|+--------+|100201|+--------+1rowinset(0.00sec)mysql>select*fromid_db;+--------+-------+|id|nm|+--------+-------+|303|db||100200|id_db|+--------+-------+2rowsinset(0.00sec)mysql>insertintoid_db(nm)values('db');QueryOK,1rowaffected(0.01sec)mysql>select*fromid_db;+--------+-------+|id|nm|+--------+-------+|303|db||304|db||100200|id_db|+--------+-------+3rowsinset(0.00sec)mysql>

优点：重新部署mycat不受影响
缺点：当配置节点的部署是主从复制，当主挂了切从后会有重复。

注意
节点如果是主从切换后，数据id可能会有异常（重复）

2.4 zookeeper方式

vim conf/server.xml

<propertyname="sequnceHandlerType">3</property>

vim conf/sequence_distributed_conf.properties

INSTANCEID=01CLUSTERID=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] 龙哥官方课程课件、博客