TDSQL自增列:
Mysql 的innodb中常用自增列来存储某表的唯一ID例,每次mysqld启动后首次打开这个表时,把这个索引的最大值取出来作为自增例的起始值,并且存储在内存中,然后mysqld运行期间妈可维护和使用这个自增例值。
TDSQL 作为高度兼容MYSQL语法的数据库,自然也是有全局唯一自增列这一功能。 关键字auto_increment,即支持一个全局的自增字段,auto_increment 可以保证该表某个字段全局唯一,但不保证单调递增 。TDSQL自增例的值是保存在Zk组件中的,当Proxy启动后,会从ZK中取一个号段,每个Proxy 都是从ZK中取的唯一号段,所以它能保证唯一;但是由于它取的是号段,当SQL选择不同的Proxy时对SQL操作时,它不能保证连续,如果Proxy 挂掉,在恢复的时候会重新向ZK去请求一个号段。这样的好处,不用频繁的去ZK中取,避免了资源竞争,有利于性能的提长。
#创建自增例的表,
这种设计方式实现了分布式环境下的自增属性全局唯一。每个Proxy缓存一定数量的值,并且增加单独线程负责向ZK申请值,使得性能影响降到最低,同时具有容灾特性,即使Proxy挂了或者重启,都能保证全局唯一。但是缺点是:多个Proxy一起使用的时候,只能保证全局唯一,不能保证单调递增。
下面我们来验证自增例:
#1.创建一张带有自增例的表
MySQL [–c]> mysql -uhuyi -phuyi -h10.85.10.51 -P15002 –c
MySQL [–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 –c
insert 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.当前最大自增例的值是5
MySQL [–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 –c
insert 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 15002
ps -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 -c
insert 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);
从网关日志中可以看出, 在ZK中获取了相关的路由信息,shardkey , 以及自己增例等信息。
more /data/tdsql_run/15002/gateway/log/sys_instance_15002.2020-12-29.0/data/tdsql_run/15002/gateway/log/
main/proxy-zookeeper.c:275:get_new_id,tid:0x7f3c9d3f5700,con:(nil),get_new_id current:0,old_value:7000,step:1000,db_table:huyidb.test4
main/proxy-zookeeper.c:139:deal_one_path,tid:0x7f3c9f3f7700,con:(nil),deal_one_path :/tdsqlzk/group_1609208018_42/sequenceids/sequenceid@huyidb.test4/alter_seqid,event:3
main/proxy-zookeeper.c:287:get_new_id,tid:0x7f3c9d3f5700,con:(nil),get_new_id,set_path node:/tdsqlzk/group_1609208018_42/sequenceids/sequenceid@huyidb.test4/current_id,new_value:8000,return :7000,
main/auto_inc.h:109:get_new_id,tid:0x7f3c9d3f5700,con:(nil),get_new_id:7000 from zk,can use (7000,8000] for db_table:huyidb.test4
main/proxy-zookeeper.c:170:deal_one_path,tid:0x7f3c9f3f7700,con:(nil),in deal_one_path:/tdsqlzk/group_1609208018_42/sequenceids/sequenceid@huyidb.test4/alter_seqid,got a new value:0 for db_table:h
main/proxy-zookeeper.c:139:deal_one_path,tid:0x7f3c9f3f7700,con:(nil),deal_one_path :/tdsqlzk/group_1609208018_42/sequenceids/sequenceid@huyidb.test4/current_id,event:3
main/proxy-zookeeper.c:275:get_new_id,tid:0x7f3c9d3f5700,con:(nil),get_new_id current:0,old_value:5000,step:1000,db_table:huyidb.test5
main/proxy-zookeeper.c:139:deal_one_path,tid:0x7f3c9f3f7700,con:(nil),deal_one_path :/tdsqlzk/group_1609208018_42/sequenceids/sequenceid@huyidb.test5/alter_seqid,event:3
main/proxy-zookeeper.c:287:get_new_id,tid:0x7f3c9d3f5700,con:(nil),get_new_id,set_path node:/tdsqlzk/group_1609208018_42/sequenceids/sequenceid@huyidb.test5/current_id,new_value:6000,return :5000,
main/auto_inc.h:109:get_new_id,tid:0x7f3c9d3f5700,con:(nil),get_new_id:5000 from zk,can use (5000,6000] for db_table:huyidb.test5
main/proxy-zookeeper.c:170:deal_one_path,tid:0x7f3c9f3f7700,con:(nil),in deal_one_path:/tdsqlzk/group_1609208018_42/sequenceids/sequenceid@huyidb.test5/alter_seqid,got a new value:0 for db_table:h
main/proxy-zookeeper.c:139:deal_one_path,tid:0x7f3c9f3f7700,con:(nil),deal_one_path :/tdsqlzk/group_1609208018_42/sequenceids/sequenceid@huyidb.test5/current_id,event:3
main/proxy-zookeeper.c:139:deal_one_path,tid:0x7f3c9f3f7700,con:(nil),deal_one_path :/tdsqlzk/group_1609208018_42/groupdcn,event:3
main/proxy-extend.cpp:457:print_backend_info,tid:0x7f3ce6fff700,con:(nil),thread version is not equal to gloabl,so update version to:6,after update:
main/proxy-extend.cpp:460:print_backend_info,tid:0x7f3ce6fff700,con:(nil),thread update groupstatus to :0
main/proxy-extend.cpp:463:print_backend_info,tid:0x7f3ce6fff700,con:(nil),thread route set_range set_name:set_1609208277_1,range:0-31
main/proxy-extend.cpp:463:print_backend_info,tid:0x7f3ce6fff700,con:(nil),thread route set_range set_name:set_1609208653_3,range:32-63
main/proxy-extend.cpp:465:print_backend_info,tid:0x7f3ce6fff700,con:(nil),thread route set_slice set_name:set_1609208277_1,slice:0
main/proxy-extend.cpp:465:print_backend_info,tid:0x7f3ce6fff700,con:(nil),thread route set_slice set_name:set_1609208653_3,slice:0
main/proxy-extend.cpp:468:print_backend_info,tid:0x7f3ce6fff700,con:(nil),thread route dbtable_shardkey dbtable:huyidb.test4,shardkey:a
main/proxy-extend.cpp:468:print_backend_info,tid:0x7f3ce6fff700,con:(nil),thread route dbtable_shardkey dbtable:huyidb.test5,shardkey:a
main/proxy-extend.cpp:491:print_backend_info,tid:0x7f3ce6fff700,con:(nil),thread route dbtable_auto_inc dbtable:huyidb.test4,auto_inc:a
main/proxy-extend.cpp:491:print_backend_info,tid:0x7f3ce6fff700,con:(nil),thread route dbtable_auto_inc dbtable:huyidb.test5,auto_inc:a
main/proxy-extend.cpp:494:print_backend_info,tid:0x7f3ce6fff700,con:(nil),thread route dbtable_auto_inc_type dbtable:huyidb.test4,auto_inc_type:0
main/proxy-extend.cpp:494:print_backend_info,tid:0x7f3ce6fff700,con:(nil),thread route dbtable_auto_inc_type dbtable:huyidb.test5,auto_inc_type:0
main/proxy-extend.cpp:496:print_backend_info,tid:0x7f3ce6fff700,con:(nil),thread route dbtable_auto_inc_step dbtable:huyidb.test4,auto_inc_step:1000
main/proxy-extend.cpp:496:print_backend_info,tid:0x7f3ce6fff700,con:(nil),thread route dbtable_auto_inc_step dbtable:huyidb.test5,auto_inc_step:1000
main/proxy-extend.cpp:512:print_backend_info,tid:0x7f3ce6fff700,con:(nil),thread sets master set_name:set_1609208277_1,ip:10.85.10.51:4005
main/proxy-extend.cpp:512:print_backend_info,tid:0x7f3ce6fff700,con:(nil),thread sets master set_name:set_1609208653_3,ip:10.85.10.51:4006
main/proxy-extend.cpp:514:print_backend_info,tid:0x7f3ce6fff700,con:(nil),thread sets master set_name:set_1609208277_1,status:0
main/proxy-extend.cpp:514:print_backend_info,tid:0x7f3ce6fff700,con:(nil),thread sets master set_name:set_1609208653_3,status:0
main/proxy-extend.cpp:516:print_backend_info,tid:0x7f3ce6fff700,con:(nil),thread sets slave set_name:set_1609208277_1,ip:10.85.10.52:4005@1@IDC_CQ_YB_9527_02@0,10.85.10.53:4005@1@IDC_CQ_YB_9527_03
main/proxy-extend.cpp:516:print_backend_info,tid:0x7f3ce6fff700,con:(nil),thread sets slave set_name:set_1609208653_3,ip:10.85.10.52:4006@1@IDC_CQ_YB_9527_02@0,10.85.10.53:4006@1@IDC_CQ_YB_9527_03
那自增例里面的值,多个表可以混用么?我们创建第二个自增例的表来确认一下。
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 -c
MySQL [(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 表为例;
首先我们查看zk中当前最大的自增例的值是5000
[zk: localhost:2181(CONNECTED) 8] get /tdsqlzk/group_1609208018_42/sequenceids/sequenceid@huyidb.test5/current_id
5000
cZxid = 0x359bf
ctime = Tue Dec 29 10:37:27 CST 2020
mZxid = 0x35dfb
mtime = Tue Dec 29 10:38:57 CST 2020
pZxid = 0x359bf
cversion = 0
dataVersion = 5
aclVersion = 0
ephemeralOwner = 0x0
dataLength = 4
numChildren = 0
2.我们在次kill proxy进程,模拟故障, 待网关恢复后,我发现新插入的值为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 3347
Server version: 5.7.17-11-V2.0R540D002-20191226-1152-log Source distribution
Copyright (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_id
6000
cZxid = 0x359bf
ctime = Tue Dec 29 10:37:27 CST 2020
mZxid = 0x371d9
mtime = Tue Dec 29 10:45:55 CST 2020
pZxid = 0x359bf
cversion = 0
dataVersion = 6
aclVersion = 0
ephemeralOwner = 0x0
dataLength = 4
numChildren = 0
目前 select last_insert_id() 只能跟 shard 表和广播表的自增字段一起使用,不支持 noshard 表。
自增例的缺陷:
每个表只能有一个自增列,并且这个自增列必须是一个索引的第一列。
同时,一个自增序列无法给多个表使用。
3.无法独立引用(refer to)一个表的自增列的当前值(last_insert_id无法指定某个表)