怎样建立一个网站步骤,设计网站能否用素材来制作广告,网站建设书生商友,微官网建设自增序列广泛使用于数据库的开发和设计中#xff0c;用于生产唯一主键、日志流水号等唯一ID的场景。传统数据库中使用Sequence和自增列的方式实现自增序列的功能#xff0c;在分布式数据库中兼容Oracle和MySQL等传统数据库语法#xff0c;也是基于Sequence和自增列的方式实现…自增序列广泛使用于数据库的开发和设计中用于生产唯一主键、日志流水号等唯一ID的场景。传统数据库中使用Sequence和自增列的方式实现自增序列的功能在分布式数据库中兼容Oracle和MySQL等传统数据库语法也是基于Sequence和自增列的方式实现分布式架构下的全局唯一ID。本文简要介绍了传统数据库和分布式数据库的自增序列实现方式并且突出NOCYCLE非循环使用的自增序列在监控上的策略。 1、传统数据库的自增序列实现
在关系型数据库的开发过程中自增序列或自增列使用场景非常广泛可以生成唯一ID作为主键、生成唯一标识符也可以生成顺序编号如交易流水号、订单编号等。因此在传统的Oracle、DB2和MySQL等数据库中通过Sequence这一特殊的数据库对象或者自增列的方式实现自增序列满足应用开发中唯一ID的需求和性能要求。
1.1 Sequence对象实现
1.1.1 Oracle数据库
Sequence是Oracle数据库中的特殊的数据库对象用于生成唯一的序列。Oracle数据库中创建Sequence语法如下
CREATE SEQUENCE sequence_name START WITH start_value INCREMENT BY increment_value [NO MAXVALUE] | [MAXVALUE max_value] [NO MINVALUE] | [MINVALUE min_value] [CACHE cache_value] [NO ORDER] | [ORDER] [CYCLE] | [NO CYCLE]
[RESET ON START] | [NO RESET ON START];相关参数如下
sequence_nameSequence的名称。START WITH start_value指定Sequence的起始值。INCREMENT BY increment_value指定每次调用Sequence时数值的增量。MAXVALUE max_value和MINVALUE min_value指定Sequence的最大值和最小值。CACHE cache_value指定预先生成的数值数量并存储在内存中以提高性能。NO ORDER和ORDER指定Sequence值是否按请求顺序生成。CYCLE和NO CYCLE指定当Sequence达到最大值或最小值后是否重新开始。RESET ON START 和 NO RESET ON START指定在数据库重启后是否重置Sequence值。
注意CYCLE和NO CYCLE定义指定自增列是否定义为循环使用。
2Sequence的使用
Oracle中使用CURRVAL和NEXTVAL获取序列的当前值和下一个值如下所示
##获取当前值
select 序列名称.currval from dual
##获取下一个值
select 序列名称.nextval from dualSequence在使用上主要包括以下对于一些子查询、关联查询或者GROUP BY/ORDER BY语句中是不可以使用sequence的
INSERT的VALUES子句、子查询不包含子查询、snapshot、视图的SELECT语句中的列表UPDATE的SET子句
需要注意的是同个Sequence有多个用户并发使用时每个用户只能获取一次Sequence的下一个值。如果需要确保并发访问时的唯一性需要使用数据库锁或其他机制来控制并发访问。
1.1.2 DB2数据库
DB2数据库中sequence对象出现之前最早是Identify列类似自增列的概念后来为解决自增列的一些问题及合入其它功能引入Sequence。创建Sequence语法与Oracle类似
CREATE SEQUENCE sequence_name START WITH start_value INCREMENT BY increment_value [NO MAXVALUE] | [MAXVALUE max_value] [NO MINVALUE] | [MINVALUE min_value] [CACHE cache_value] [NO ORDER] | [ORDER] [CYCLE] | [NO CYCLE]
[RESET ON START] | [NO RESET ON START];相关参数如下
sequence_nameSequence的名称。START WITH start_value指定Sequence的起始值。INCREMENT BY increment_value指定每次调用Sequence时数值的增量。MAXVALUE max_value和MINVALUE min_value指定Sequence的最大值和最小值。CACHE cache_value指定预先生成的数值数量并存储在内存中以提高性能。NO ORDER和ORDER指定Sequence值是否按请求顺序生成。CYCLE和NO CYCLE指定当Sequence达到最大值或最小值后是否重新开始。RESET ON START 和 NO RESET ON START指定在数据库重启后是否重置Sequence值。
创建好Sequence后可以在SQL语句中使用sequence如INSERT语句中插入自增序列值、select语句中查询自增值。
1在INSERT语句中使用NEXTVAL FOR来获取Sequence的下一个值并将其插入到表的相应列中
INSERT INTO USER(USER_ID, USER_NAME) VALUES(NEXTVAL FOR SEQ_USER_ID, username1);2通过查询Sequence来获取其当前值或下一个值
SELECT NEXTVAL FOR SEQ_USER_ID FROM SYSIBM.SYSDUMMY1;1.2 自增列实现
1.2.1 Oracle数据库
Oracle数据库本身并不直接支持自增列类似MySQL中的AUTO_INCREMENT的创建其提供了触发器结合Sequence来实现类似的功能。在创建了Sequence以后创建一个BEFORE INSERT触发器该触发器会在每次插入新行之前从序列中获取下一个值并将其设置为自增列的值。
CREATE OR REPLACE TRIGGER trg_my_table_before_insert
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN SELECT seq_name.NEXTVAL INTO :new.id FROM dual;
END;
/当插入数据时不需要指定自增列的值触发器会自动创建自增的序列值。
1.2.2 DB2数据库
DB2数据库在引入Sequence之前是使用identify列实现自增列语法如下
CREATE TABLE table_name ( column1 datatype [constraint], column2 datatype [constraint], identity_column datatype GENERATED ALWAYS AS IDENTITY ( START WITH start_value INCREMENT BY increment_value [NO MAXVALUE] | [MAXVALUE max_value] [NO MINVALUE] | [MINVALUE min_value] [CACHE cache_value] [CYCLE] | [NO CYCLE] ), ...
);其中GENERATED ALWAYS AS IDENTITY指定该列为自增列。可以看到DB2中的自增列是在创建表时直接定义的而不是在表创建后单独添加。此外自增列只能用于表的主键且每个表只能有一个自增列。和Sequence相比自增列在使用上有限制只能定义在单表上另外对自增列的更改将导致表空间被置为Reorg-pending状态。
1.2.3 MySQL数据库
在MySQL数据库中没有Sequence这个数据库对象但是通过为表的某一列设置AUTO_INCREMENT属性来创建一个自增列并且通常作为主键。如下所示在创建表时在id列指定AUTO_INCREMENT属性每次插入新行时这一列的值就会自动递增。
CREATE TABLE t1 ( id INT NOT NULL AUTO_INCREMENT, c1 VARCHAR(50) NOT NULL, PRIMARY KEY (id)
);另外在MySQL中有一些系统变量定义了自增列的属性
mysql show vaiables like AUTO_INCREMENT%
-----------------------------------------
| Variable_name | Value |
-----------------------------------------
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| auto_increment_wrap | 0 |
| auto_increment_for_each_replica | OFF |
-----------------------------------------相关参数解释如下
auto_increment_increment控制自增列每次递增的量。默认值为1意味着每次插入新行时自增列的值增加1。auto_increment_offset在复制环境中用于设置自增列的起始偏移量以避免在多个服务器上生成相同的自增ID。默认值为1。auto_increment_wrap当设置为非零值时自增列的值会在达到最大值后回绕到最小值。这通常用于循环使用自增ID的场景。默认值为0表示不启用回绕。相当于Sequence定义中的Cycle和Nocycle。auto_increment_for_each_replica: 在MySQL 8.0及更高版本中当设置为ON时每个复制从服务器将独立生成自增ID。这有助于避免在多个从服务器上生成相同的自增ID。默认值为OFF。
另外在mysql中多并发申请自增列时通过innodb_autoinc_lock_mode参数控制在插入新记录时如何锁定自增计数器这个参数影响在并发插入操作中的性能和锁争用。
0 (TRADITIONAL)这是传统的锁定模式。在每次插入操作中InnoDB 会对自增计数器进行锁定以确保生成的自增 ID 是唯一的。这种模式下并发插入可能会受到严重的限制因为每次插入都需要等待前一个插入操作完成并释放锁。1 (CONSECUTIVE)在这种模式下InnoDB 会尝试在单个事务中预先分配多个自增 ID然后逐个使用它们。这样可以减少锁定的次数从而提高并发插入的性能。但是如果事务被回滚那么预先分配但未使用的 ID 会被浪费。2 (INTERLEAVED)这是MySQL 5.6引入的新模式。在这种模式下InnoDB会根据实际需要动态地选择是否预先分配多个自增 ID。这结合了前两种模式的优点试图在并发性和ID浪费之间找到一个平衡点。
默认情况下是1innodb_autoinc_lock_mode参数的修改要重启mysql实例才能生效。
1.3 Sequence和自增列对比
从上文中可以看到在传统数据库中自增序列的实现有两种方式sequence和自增列那么二者在使用上有哪些不同之处如下表所示 最主要的区别是Sequence是数据库中的对象可以创建、修改和删除、授权等操作并且在使用上更为灵活。
2、分布式数据库中自增序列实现
上文看到在传统数据库Oracle、DB2和MySQL中对自增序列的支持当然在国产分布式数据库中对传统数据库语法的兼容也支持通过Sequence对象和自增列的方式实现自增序列。不过与传统集中数据库不同之处是分布式数据库中如何保证自增序列在分布式架构下的全局自增。
2.1 OceanBase数据库
OceanBase数据库中支持创建sequence对象和自增列两种方式实现自增序列。
1Sequence实现
使用CREATE SEQUENCE语句可以创建sequence对象语法如下
CREATE SEQUENCE [ IF NOT EXISTS ] [ schema. ] sequence_name
{ START WITH int_value
|[ INCREMENT BY int_value ]
|[ MINVALUE int_value | NOMINVALUE ]
|[ MAXVALUE int_value | NOMAXVALUE ]
|[ CACHE int_value | NOCACHE ]
|[ ORDER | NOORDER ]
|[ CYCLE | NOCYCLE ]
}
;语法上和传统数据库没有什么区别。创建序列后可以在SQL语句中使用CURRVAL伪列返回序列当前值或使用NEXTVAL伪列返回递增的新值。
Cache指在内存中预分配的自增值个数默认为20CYCLE/NOCYCLECYCLE为默认值指定序列在达到其最大值或最小值后无法生成更多值。
2自增列AUTO_INCRREMENT
OceanBase作为分布式数据库兼容MySQL的自增列特性不同的是在分布式多实例的场景下自增列生成会出现跳变。
CREATE TABLE t1(id bigint not null auto_increment primary key,
c1 varchar(50));
Query OK, 0 rows affected在OceanBase数据库V4.x版本开始新增了ORDER模式的自增列以避免NOORDER模式中多机多分区生成自增值和通过INSERT语句插入指定的最大值等比较常用的使用场景下发生自增值跳变的问题。 ORDER模式的自增列会在所有OBServer节点中选取当前集群的Leader作为自增列服务的Leader其它作为Follower的OBServer节点需要通过发送RPC请求来从作为Leader的OBServer节点处申请自增值而作为Leader的OBServer节点会从内部表申请自增区间来作为自增缓存。
因此在大部分场景下和单机MySQL一样可以生成连续的全局自增序列但是在大并发场景下ORDER模式的性能会较差。并且在Leader节点重启或宕机、切主等极端情况下仍然会出现自增值的跳变情况。为了避免切主场景下出现自增值跳变的问题发生切主的时候会将将原Leader的OBServer节点上的缓存区间清理掉。
2.2 TiDB数据库
1Sequence使用
TiDB数据库v4.0版本开始支持序列功能具体语法如下
CREATE [TEMPORARY] SEQUENCE [IF NOT EXISTS] sequence_name[ INCREMENT [ BY | ] increment ][ MINVALUE [] minvalue | NO MINVALUE | NOMINVALUE ][ MAXVALUE [] maxvalue | NO MAXVALUE | NOMAXVALUE ][ START [ WITH | ] start ][ CACHE [] cache | NOCACHE | NO CACHE][ CYCLE | NOCYCLE | NO CYCLE][table_options]Cache指定每个TIDB实例本地缓存的大小默认是1000CYCLE/COCYCLE指定序列用完之后是否循环使用
定义完Sequence后可以通过NEXTVAL、LASTVAL获取下一个序列值和上一次使用的序列值SETVAL可以设置序列的增长返回一个满足增长值的序列值。
SELECT nextval(seq);
SELECT lastval(seq);
SELECT setval(seq, 10);2自增列auto_increment
TiDB中兼容MySQL数据库的auto_increment特性在v6.4.0版本之前TiDB数据库和OceanBase一样存在自增值跳变的问题v6.4.0版本之后支持在所有TiDB实例上全局单调自增。建表时候创建自增列语法如下
CREATE TABLE t(id int PRIMARY KEY AUTO_INCREMENT, c int);TiDB 在v6.4.0版本实现了中心化分配自增ID的服务当前中心化分配服务内置在TiDB进程类似于DDL Owner的工作模式。其中有一个TiDB实例将充当“主”的角色提供ID分配服务而其它的TiDB实例将充当“备”角色。当“主”节点发生故障时会自动进行“主备切换”从而保证中心化服务的高可用。
在建表时将AUTO_ID_CACHE设置为1表示使用MySQL兼容模式该模式下能保证ID唯一、单调递增行为几乎跟MySQL完全一致。只有当中心化服务的“主”TiDB实例异常崩溃时才有可能造成少量ID不连续。这是因为主备切换时“备”节点需要丢弃一部分之前的“主”节点可能已经分配的ID以保证ID不出现重复。
2.3 GoldenDB数据库
GoldenDB分布式数据库中的自增序列有sequence和auto_increment两种方式自增序列值由全局事务节点GTM统一分配和维护管理。
1Sequence创建
Sequence创建的语法
CREATE SEQUENCE schema.sequence
INCREMENT BY num
START WITH num
MAXVALUE num | NOMAXVALUE
MINVALUE num | NOMINVALUE
CYCLE | NOCYCLE
CACHE | NOCACHESEQUENCE默认CACHE为100修改步长时会导致当前CACHE丢失。由于SEQUENCE的值是在SQL语句执行之前向GTM申请语句执行失败的情形下申请的SEQUENCE值不能够回收所以表中的数据会有不连续的情形。
2自增列auto_increment
GoldenDB数据库兼容MySQL的自增列特性在建表时候指定auto_increment即可
CREATE TABLE t1 ( id INT NOT NULL AUTO_INCREMENT, c1 VARCHAR(50) NOT NULL, PRIMARY KEY (id)
);GoldenDB数据库中的Sequence和自增列由GTM统一管理定义后通过dbtool -gtm -show-seq命令可以查看自增列的信息
dbtool -gtm -show-seq -clusterid2
Send message to other module successfully!
The response message: RSP Code[0].{0:success; 1:provisional response; other: fail.}
Successful response:
clusterid|database|name,start,step,minval,maxval,cache,cycle,curvalue
1|testdb1|t_seq,10,10,1,9223372036854775807,5,0,160610
2|testdb2 |seq_test1,1,1,1,9223372036854775807,1,0,16016
~success~在多并发申请自增列的场景中GTM会先将请求放到一个队列中再统一分配自增值这样可以减少自增序列请求交互的开销、保证自增列的连续性不过在极少数高并发场景下可能会存在一定的性能瓶颈。
2.4 TDSQL for MySQL数据库
TDSQL for MySQL分布式数据库版本中支持创建sequence和自增列。
1Sequence支持
TDSQL支持使用sequence需要注意的是Sequence为保证分布式全局数值唯一导致性能较差主要适用于并发不高的场景。语法如下
CREATE TDSQL_SEQUENCE {DATABASE | SCHEMA} [IF NOT EXISTS] sequence_name
[ TDSQL_INCREMENT BY increment ]
[ START WITH startvalue ]
[ TDSQL_MINVALUE minvalue] | [ TDSQL_NOMINVALUE ]
[ TDSQL_MAXVALUE maxvalue] | [ TDSQL_NOMAXVALUE ]
[ TDSQL_CACHE cachevalue ] | [ TDSQL_NOCACHE ]
[ TDSQL_CYCLE ] | [ TDSQL_NOCYCLE ]
[ TDSQL_ORDER ] | [ TDSQL_NOORDER ]
-- 查看sequence建表语句
SHOW CREATE TDSQL_SEQUENCE sequence_name;创建sequence后可以通过nextval和lastval获取下一个序列值和上一次使用的值
-- 使用Sequence获取下一个数值
select tdsql_nextval(test.seq2);
select next value for test.seq2;
-- 获取上一次的值
select tdsql_lastval(test.seq2);
select tdsql_previous value for test.seq2;2全局唯一序列auto_increment
TDSQL支持全局唯一数字序列auto_increment的使用当前10.3.22版本暂时仅保证自增字段全局唯一和递增性但是不保证单调递增也就是OceanBase和TiDB之前版本出现的跳变问题。全局唯一数字序列auto_increment长8字节最大为18446744073709551616建表时候创建自增列
create table auto_inc
(a int,
b int auto_increment,
key auto(b),
primary key p(a,b))
shardkeya;通过select last_insert_id()命令获取最新自增值暂不支持直接从Insert返回包获取。目前select last_insert_id()只能跟shard表和广播表的自增字段一起使用不支持noshard表。
MySQL [test] select last_insert_id();
------------------
| last_insert_id() |
------------------
| 1009 |
------------------
1 row in set (0.00 sec)由于auto_increment仅保证自增字段全局唯一和递增性如果在节点调度切换、重启等过程中自增长字段中间会出现空洞。
2.5 GaussDB for OpenGauss数据库
GaussDB分布式数据库for opengauss版本中也支持sequence对象和自增列方式实现自增序列并由GTM全局事务管理器统一维护管理保证序列号全局唯一性。
建表时声明字段的类型为序列整型由数据库在后台自动创建一个对应的Sequence。使用CREATE SEQUENCE自定义一个新的Sequence然后将nextval(‘sequence_name’)函数读取的序列值指定为某一字段的默认值这样该字段就可以作为唯一标识符。
1Sequence对象
GaussDB中Sequence是一个存放等差数列的特殊表该表受DBMS控制。这个表没有实际意义通常用于为行或者表生成唯一的标识符。创建语法如下
CREATE SEQUENCE name [ INCREMENT [ BY ] increment ][ MINVALUE minvalue | NO MINVALUE | NOMINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE | NOMAXVALUE] [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE | NOCYCLE ] [ OWNED BY { table_name.column_name | NONE } ];Cache为了快速访问而在内存中预先存储序列号的个数默认为1。CYCLE/NOCYCLE用于使序列达到maxvalue或者minvalue后可循环并继续下去。若定义序列为CYCLE则不能保证序列的唯一性。
在GaussDB中不建议同时定义cache和maxvalue或minvalue。因为定义cache后不能保证序列的连续性可能会产生空洞造成序列号段浪费。如对并发性能有要求指定参数session_sequence_cache。cache指定了单CN/DN一次向GTM中申请的值session_sequence_cache指定的是单个会话一次向CN/DN申请缓存的值会话结束后会自动丢弃。
2声明字段类型为serial
gaussdb# CREATE TABLE T1
(id serial,name text
);在后台会自动创建一个sequence序列。另外将sequence和一个表的指定字段关联后当删除那个字段或其所在表的时候会自动删除已关联的sequence。
2.6 自增序列的维护和监控
从上文可以看到各个分布式数据库产品中已经实现了自增序列的功能保证全局唯一性在实际使用过程中开发关心的是全局序列的唯一性和并发访问时候的性能从运维视角来看主要是自增序列的使用值监控。Sequence在定义的时候有CYCLE和NOCYCLE两种模式业务使用场景中不是所有的都能使用CYCLE循环模式毕竟无法保证它的唯一性。如果是NOCYCLE模式肯定有个上限申请的序列值达到上限后业务访问会报错如何做好监控和维护
1Sequence序列对象的监控
Sequence对象在定义的时候指定了MAXVALUE或者使用系统默认的最大value通过SQL语句查询当前的VALUE值CURRVAL和最大值比较获得使用率并以定时任务或脚本方式部署到监控中。有些数据库如GoldenDB和GaussDB中本身有管理组件GTM对sequence维护管理由数据库本身实现sequence使用率的监控。
2自增列的监控
自增列auto_increment随着业务的插入操作自增值不断增长。在MySQL系列的数据库中通过自定义视图来监控自增列的使用情况以下是一个例子
##1、从information_schema.full_columns中查出table_name,table_schema,column_name,data_type,column_type等信息##2、根据自增列的data_type得到最大值
case information_schema.full_columns.data_type when ‘tinyint’ then 255 when ‘smallint’ then 65535 when ‘mediumint’ then 16777215 when ‘int’ then 4294967295 when ‘bigint’ then 18446744073709551615)##3、从表information_schema.full_tables中获取auto_increment使用值以上是自增序列的监控手段实际操作过程中需要增加自增序列的使用这一监控指标并定制监控策略。 参考资料 https://www.oceanbase.com/docshttps://docs.pingcap.com/zh/tidb/stable/https://cloud.tencent.com/document/product/557/47526GoldenDB分布式数据库SQL指南GaussDB云数据库操作指南
