MyCat global primary key generation strategy

In the case of sub databases and sub tables, the global uniqueness of the auto increasing primary key cannot be guaranteed for multiple databases. Therefore, the global primary key generation strategy of MyCat was born.

Mycat global primary key mode

1. Local file mode: use the server's local disk file mode

2. Local time stamp method: use time stamp method

3. Database mode: the mode of using database

4. Distributed zookeeper generation ID

 

1. Local file mode

vim conf/server.xml

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

vim conf/sequence_conf.properties

#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

matters needing attention

1. In the above configuration file, the user-defined table name must be written in uppercase
 2.HISIDS: indicates the used historical segment (it can not be configured generally without special needs)
3.MINID: minimum ID value
 4. Maximum: indicates the maximum ID value
 5.CURID represents the current ID value.
6. When sequence_conf.properties When the configuration name of is consistent with the table name, sql can not contain the ID field (here the table name is id_local_file)

vim conf/schema.xml

<?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>

Advantages: local loading, fast reading speed and simple configuration

Disadvantages: when mycat is republished, the seq file needs to be replaced. Cluster deployment cannot use this method. Routing to different mycat cannot guarantee the unique id, making mycat a stateful middleware

 

2. Local time stamp method

vim conf/server.xml

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

vim conf/sequence_time_conf.properties

#sequence depend on TIME
WORKID=01
DATAACENTERID=01

*The above two property values are: 0-31 any integer

vim conf/schema.xml

<?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>

*Local timestamp calculation method:

ID= 64 bit binary (42 (MS) + 5 (machine ID) + 5 (business code) + 12 (repeated accumulation) length 18 bits, so the length of table primary key field must be greater than or equal to 18 bits

Advantages: there is no problem of mycat re publishing affecting seq,

Disadvantage: the field length is 18 bits, which takes up more space

 

3. Database mode

vim conf/server.xml

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

vim conf/sequence_db_conf.properties

#sequence stored in datanode
GLOBAL=test1
ID_DB=test1

Add functions and tables to the local database of test1 node. The following is the sql content:

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 ;

Add process:

[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>

The following steps are very important to make id_db tables also support database serial numbers.

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

<?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>

*Note: MYCAT_ The sequence table is also put out, and pay attention to case (the database is case sensitive by default)

Advantage: the redeployment of mycat will not be affected. If the node is a master-slave switch, the data id may be abnormal (duplicate)

Disadvantage: when the deployment of the configuration node is master-slave replication, there will be duplication when the master has a slave.

 

4. Distributed zookeeper generation ID

vim conf/server.xml

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

vim conf/sequence_distributed_conf.properties

INSTANCEID=01
CLUSTERID=01

vim conf/schema.xml

Add autoIncrement="true" and primaryKey="id" to table of schema

The ID structure of the distributed ID generator based on ZK and local configuration (you can get the unique InstanceID of the cluster (machine room) through ZK, or you can configure the InstanceID through the configuration file): long 64 bits, the maximum ID can occupy 63 bits

current time millis(38 bits of microsecond time stamp, can be used for 17 years)

instanceId (instance ID, which can be obtained through ZK or configuration file, 5-bit, that is, decimal 0-31)

threadId (thread ID, 9 bits)

increment(6-bit)

There are 63 bits in total, which can bear the concurrency of single machine and single thread 1000 * (2 ^ 6) = 640000. Because of the integration of hardware and software, there is no implementation here, just to understand.

Advantages: no pessimistic lock, no strong competition, higher throughput

Disadvantages: increased requirements for zookeeper clusters.


Tags: MySQL mycat vim xml

Posted on Sun, 24 May 2020 06:58:14 -0400 by rajivgonsalves