腾讯云分布式数据库TDSQL 内部的自增例介绍

TDSQL 内部的自增列‍介绍

这种设计方式实现了分布式环境下的自增属性全局唯一。每个Proxy缓存一定数量的值，并且增加单独线程负责向ZK申请值，使得性能影响降到最低，同时具有容灾特性，即使Proxy挂了或者重启，都能保证全局唯一。但是缺点是：多个Proxy一起使用的时候，只能保证全局唯一，不能保证单调递增。

下面我们来验证自增例：

#1.创建一张带有自增例的表MySQL [–c]> mysql -uhuyi -phuyi -h10.85.10.51 -P15002 –cMySQL [–c]> create database huyidb ; MySQL [–c]> create table huyidb.test4 ( a int auto_increment , b int , c char(20),primary key (a),unique key u_2(a,c) ) shardkey=a;Query OK, 0 rows affected (0.75 sec)#2.在Porxy1 插入5条数据mysql -uhuyi -phuyi -h10.85.10.51 -P15002 –cinsert into huyidb.test4 ( a,b, c) values (0,10, 'huyi01') ;insert into huyidb.test4 ( a,b, c) values (0,20, 'huyi02') ;insert into huyidb.test4 ( a,b, c) values (0,30, 'huyi03') ;insert into huyidb.test4 ( a,b, c) values (0,40, 'huyi04') ;insert into huyidb.test4 ( a,b, c) values (0,50, 'huyi05') ;select last_insert_id()  ;MySQL [–c]> insert into huyidb.test4 ( a,b, c) values (0,10, 'huyi01') ;Query OK, 1 row affected (0.01 sec)MySQL [–c]> insert into huyidb.test4 ( a,b, c) values (0,20, 'huyi02') ;Query OK, 1 row affected (0.01 sec)MySQL [–c]> insert into huyidb.test4 ( a,b, c) values (0,30, 'huyi03') ;Query OK, 1 row affected (0.01 sec)MySQL [–c]> insert into huyidb.test4 ( a,b, c) values (0,40, 'huyi04') ;Query OK, 1 row affected (0.00 sec)MySQL [–c]> insert into huyidb.test4 ( a,b, c) values (0,50, 'huyi05') ;Query OK, 1 row affected (0.00 sec)#3.当前最大自增例的值是5MySQL [–c]> select last_insert_id()  ;+------------------+| last_insert_id() |+------------------+| 5 |+------------------+1 row in set (0.00 sec)#4.在Porxy2 插入5条数据mysql -uhuyi -phuyi -h10.85.10.52 -P15002 –cinsert into huyidb.test4 ( a,b, c) values (0,12, 'huyi21') ;insert into huyidb.test4 ( a,b, c) values (0,22, 'huyi22') ;insert into huyidb.test4 ( a,b, c) values (0,32, 'huyi23') ;insert into huyidb.test4 ( a,b, c) values (0,42, 'huyi24') ;insert into huyidb.test4 ( a,b, c) values (0,55, 'huyi25') ;select last_insert_id()  ;MySQL [–c]> insert into huyidb.test4 ( a,b, c) values (0,12, 'huyi21') ;insert into huyidb.test4 ( a,b, c) values (0,22, 'huyi22') ;Query OK, 1 row affected (0.01 sec)MySQL [–c]> insert into huyidb.test4 ( a,b, c) values (0,22, 'huyi22') ;Query OK, 1 row affected (0.00 sec)MySQL [–c]> insert into huyidb.test4 ( a,b, c) values (0,32, 'huyi23') ;Query OK, 1 row affected (0.01 sec)MySQL [–c]> insert into huyidb.test4 ( a,b, c) values (0,42, 'huyi24') ;Query OK, 1 row affected (0.01 sec)MySQL [–c]> insert into huyidb.test4 ( a,b, c) values (0,55, 'huyi25') ;Query OK, 1 row affected (0.00 sec)#5.当前最大自增例是2005,说明自增例是不保证连续的MySQL [–c]> select last_insert_id()  ; +------------------+| last_insert_id() |+------------------+| 2005 |+------------------+1 row in set (0.00 sec)#6.模拟Proxy1挂掉ps -ef |grep gateway |grep 15002ps -ef |grep gateway |grep 15002 | grep -v 'grep'| awk '{print $2}' | xargs kill -9 ps -ef |grep gateway |grep 15002 #7.待Proxy1重新挂起后，再次插入5条数据mysql -uhuyi  -phuyi -h10.85.10.51 -P15002 -cinsert into huyidb.test4 ( a,b, c) values (0,13, 'huyi31') ;insert into huyidb.test4 ( a,b, c) values (0,23, 'huyi32') ;insert into huyidb.test4 ( a,b, c) values (0,33, 'huyi33') ;insert into huyidb.test4 ( a,b, c) values (0,43, 'huyi34') ;insert into huyidb.test4 ( a,b, c) values (0,53, 'huyi35') ;MySQL [(none)]> insert into huyidb.test4 ( a,b, c) values (0,13, 'huyi31') ;Query OK, 1 row affected (0.00 sec)MySQL [(none)]> insert into huyidb.test4 ( a,b, c) values (0,23, 'huyi32') ;Query OK, 1 row affected (0.01 sec)MySQL [(none)]> insert into huyidb.test4 ( a,b, c) values (0,33, 'huyi33') ;Query OK, 1 row affected (0.01 sec)MySQL [(none)]> insert into huyidb.test4 ( a,b, c) values (0,43, 'huyi34') ;Query OK, 1 row affected (0.01 sec)MySQL [(none)]> insert into huyidb.test4 ( a,b, c) values (0,53, 'huyi35') ;Query OK, 1 row affected (0.00 sec)#8.当前的自增例跳到5005， 也就是说在proxy网关故障恢复后，会重新从zk中获取一段唯一MySQL [(none)]> select last_insert_id()  ; +------------------+| last_insert_id() |+------------------+| 5005 |+------------------+1 row in set (0.00 sec)MySQL [(none)]> select * from    huyidb.test4 ;+------+------+--------+| a    | b    | c      |+------+------+--------+| 1 | 10 | huyi01 || 2005 | 55 | huyi25 || 5002 | 23 | huyi32 || 2004 | 42 | huyi24 || 2002 | 22 | huyi22 || 2001 | 12 | huyi21 || 2 | 20 | huyi02 || 5004 | 43 | huyi34 || 5 | 50 | huyi05 || 3 | 30 | huyi03 || 2003 | 32 | huyi23 || 5005 | 53 | huyi35 || 5003 | 33 | huyi33 || 5001 | 13 | huyi31 || 4 | 40 | huyi04 |+------+------+--------+15 rows in set (0.00 sec); 

那自增例里面的值，多个表可以混用么？我们创建第二个自增例的表来确认一下。

create table huyidb.test5 ( a int auto_increment , b int , c char(20),primary key (a),unique key u_2(a,c) ) shardkey=a;mysql -uhuyi  -phuyi -h10.85.10.51 -P15002 -cMySQL [(none)]>  insert into huyidb.test5 ( a,b, c) values (0,55, 'huyi55') ;Query OK, 1 row affected (0.01 sec)#从结果可以看出，每个表的自增例都是独立的， 表与表之间的自增例不能混用MySQL [(none)]> select last_insert_id() ; | last_insert_id() |+------------------+| 1 |+------------------+1 row in set (0.00 sec) #同时不能在一个表上创建2个自增例MySQL [(none)]> create table huyidb.test6 ( a int auto_increment , b int auto_increment, c char(20),primary key (a),unique key u_2(a,c) ) shardkey=a;ERROR 688 (HY000): Proxy ERROR:Complex sql can not used to create shard tables

我们从ZK中查看是如何分配的,我们以test5表为例;

1.首先我们查看zk中当前最大的自增例的值是5000

 [zk: localhost:2181(CONNECTED) 8] get /tdsqlzk/group_1609208018_42/sequenceids/sequenceid@huyidb.test5/current_id5000cZxid = 0x359bfctime = Tue Dec 29 10:37:27 CST 2020mZxid = 0x35dfbmtime = Tue Dec 29 10:38:57 CST 2020pZxid = 0x359bfcversion = 0dataVersion = 5aclVersion = 0ephemeralOwner = 0x0dataLength = 4numChildren = 0

2.我们在次killproxy进程，模拟故障， 待网关恢复后，我发现新插入的值为5001

[root@tdsql1 bin]# mysql -uhuyi  -phuyi -h10.85.10.51 -P15002 -c Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MySQL connection id is 3347Server version: 5.7.17-11-V2.0R540D002-20191226-1152-log Source distributionCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MySQL [(none)]>  insert into huyidb.test5 ( a,b, c) values (0,55, 'huyi55') ;Query OK, 1 row affected (0.00 sec)MySQL [(none)]> select last_insert_id()  ;+------------------+| last_insert_id() |+------------------+| 5001             |+------------------+1 row in set (0.00 sec)

3.同时我要在zk中查看当前最大的值为6000， 即验证了我们最初图中所说，proxy是从zk中取一段自增例缓存到proxy中。

[zk: localhost:2181(CONNECTED) 9] get /tdsqlzk/group_1609208018_42/sequenceids/sequenceid@huyidb.test5/current_id6000cZxid = 0x359bfctime = Tue Dec 29 10:37:27 CST 2020mZxid = 0x371d9mtime = Tue Dec 29 10:45:55 CST 2020pZxid = 0x359bfcversion = 0dataVersion = 6aclVersion = 0ephemeralOwner = 0x0dataLength = 4numChildren = 0

目前select last_insert_id() 只能跟 shard 表和广播表的自增字段一起使用，不支持 noshard 表。

自增例的缺陷：

1.  每个表只能有一个自增列，并且这个自增列必须是一个索引的第一列。

2.  同时，一个自增序列无法给多个表使用。

3.无法独立引用(refer to)一个表的自增列的当前值（last_insert_id无法指定某个表）

*禁止转载，可转发（转发原创文章请注明出处）