# DWS数据库SEQUENCE优秀实践 sequence，也称作序列，是用来产生唯一整数的数据库对象。序列的值按照一定的规则自增/自减，一般常被用作主键。DWS中创建sequence时，会同时创建一张同名的元数据表，用来记录sequence相关的信息，例如： ``` CREATE SEQUENCE seq_test; CREATE SEQUENCE SELECT * FROM seq_test; sequence_name | last_value | start_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called | uuid ---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------+--------- seq_test | -1 | 1 | 1 | 9223372036854775807 | 1 | 1 | 0 | f | f | 1400050 (1 row) ``` 其中， - sequence_name表示sequence的名称。 - last_value：当前无意义。 - start_value表示sequence的初始值。 - increment_by表示sequence的步长。 - max_value表示sequence的最大值。 - min_value表示sequence最小值。 - cache_value表示为了快速获取下一个序列值而预先存储的sequence值个数（定义cache后不能保证sequence值的连续性，会产生空洞，造成序列号段浪费）。 - log_cnt表示WAL日志记录的sequence值个数，由于在DWS中sequence是从GTM获取和管理，因此log_cnt无实际意义。 - is_cycled表示sequence在达到最小或最大值后是否循环继续。 - is_called表示该sequence是否已被调用（仅表示在当前实例是否被调用，例如在cn1上调用之后，cn1上该元数据表的值变为t，cn2上该字段仍为f）。 - uuid代表该sequence的唯一标识。 #### sequence创建流程 DWS中，GTM（Global Transaction Manager，即全局事务管理器）负责生成和维护全局事务ID、事务快照、sequence等需要全局唯一的信息。sequence在DWS中的创建流程如下图所示： 图1sequence创建流程 ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000001750156636.png "点击放大") 具体过程为： 1. 接收SQL命令的CN从GTM申请UUID。 2. GTM返回一个UUID。 3. CN将获取的UUID与用户创建的sequenceName绑定。 4. CN将绑定关系下发到其他节点上，其他节点同步创建sequence元数据表。 5. CN将UUID和sequence的startID发送到GTM端，在GTM上进行永久保存。 因此，sequence的维护和申请实际是在GTM上完成的。**当申请nextval，每个执行nextval调用的实例会根据该sequence的UUID到GTM上申请序列值，每次申请的序列值范围与cache有关，只有当cache消耗完之后才会继续到GTM上申请。因此，增大sequence的cache有利于减少CN/DN与GTM通信的次数**。 #### 创建sequence的两种方式 方式一：使用CREATE SEQUENCE语句创建序列，在新建的表中通过nextval调用。 ``` CREATE SEQUENCE seq_test increment by 1 minvalue 1 no maxvalue start with 1; CREATE SEQUENCE CREATE TABLE table_1(id int not null default nextval('seq_test'), name text); CREATE TABLE ``` 方式二：建表时使用serial类型，会自动创建一个sequence，并且会将该列的默认值设置为nextval。 ``` CREATE TABLE mytable(a int, b serial) distribute by hash(a); NOTICE: CREATE TABLE will create implicit sequence "mytable_b_seq" for serial column "mytable.b" CREATE TABLE \d+ mytable Table "dbadmin.mytable" Column | Type | Modifiers | Storage | Stats target | Description --------+---------+-----------------------------------------------------+---------+--------------+------------- a | integer | | plain | | b | integer | not null default nextval('mytable_b_seq'::regclass) | plain | | Has OIDs: no Distribute By: HASH(a) Location Nodes: ALL DATANODES Options: orientation=row, compression=no ``` 本示例中会自动创建一个名为mytable_b_seq的sequence。严格来讲serial类型不是真正的类型，只是为在表中设置唯一标识而存在的概念，在创建时会同时创建一个sequence，并与该列相关联。 等同于下列的操作语句： ``` CREATE TABLE mytable01(a int, b int) distribute by hash(a); CREATE TABLE CREATE SEQUENCE mytable01_b_seq owned by mytable.b; CREATE SEQUENCE ALTER SEQUENCE mytable01_b_seq owner to u1; --u1为mytable01表的属主，如果当前用户即为属主，可不执行此语句。 ALTER SEQUENCE ALTER TABLE mytable01 alter b set default nextval('mytable01_b_seq'), alter b set not null; ALTER TABLE \d+ mytable01 Table "dbadmin.mytable01" Column | Type | Modifiers | Storage | Stats target | Description --------+---------+-------------------------------------------------------+---------+--------------+------------- a | integer | | plain | | b | integer | not null default nextval('mytable01_b_seq'::regclass) | plain | | Has OIDs: no Distribute By: HASH(a) Location Nodes: ALL DATANODES Options: orientation=row, compression=no ``` #### sequence在业务中的常见用法 sequence在业务中常被用作在导入时生成主键或唯一列，常见于数据迁移场景。不同的迁移工具或业务导入场景使用的入库方法不同，常见的方法主要可以分为**copy** 和**insert**。对于sequence来讲，这两种场景在处理时略有差别。 - **场景一：insert下推场景** ``` CREATE TABLE test1(a int, b serial) distribute by hash(a); NOTICE: CREATE TABLE will create implicit sequence "test1_b_seq" for serial column "test1.b" CREATE TABLE CREATE TABLE test2(a int) distribute by hash(a); CREATE TABLE EXPLAIN VERBOSE INSERT INTO test1(a) SELECT a FROM test2; QUERY PLAN ------------------------------------------------------------------------------------------------- id | operation | E-rows | E-distinct | E-memory | E-width | E-costs ----+-------------------------------------+--------+------------+----------+---------+--------- 1 | -> Streaming (type: GATHER) | 1 | | | 4 | 16.34 2 | -> Insert on dbadmin.test1 | 30 | | | 4 | 16.22 3 | -> Seq Scan on dbadmin.test2 | 30 | | 1MB | 4 | 14.21 RunTime Analyze Information ---------------------------------------------------- "dbadmin.test2" runtime: 9.586ms, sync stats Targetlist Information (identified by plan id) --------------------------------------------------------- 1 --Streaming (type: GATHER) Node/s: All datanodes 3 --Seq Scan on dbadmin.test2 Output: test2.a, nextval('test1_b_seq'::regclass) Distribute Key: test2.a ====== Query Summary ===== ------------------------------- System available mem: 1351680KB Query Max mem: 1351680KB Query estimated mem: 1024KB Parser runtime: 0.076 ms Planner runtime: 12.666 ms Unique SQL Id: 831364267 (26 rows) ``` 由于nextval在INSERT场景下可以下推到DN执行，因此，不管是使用default值的nextval，还是显式调用nextval，nextval都会被下推到DN执行，在上例的执行计划中也能看出，nextval的调用在sequence层，说明是在DN执行的。此时，DN直接向GTM申请序列值，且各DN并行执行，因此效率相对较高。 ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000001750890736.png "点击放大") - **场景二：copy场景** 在业务开发过程中，入库方式除了INSERT外，还有COPY入库的场景。常用于将文件内容COPY入库、使用CopyManager接口入库等。此外，CDM数据同步工具，其实现方式也是通过COPY的方式批量入库。在COPY入库过程中，如果COPY的目标表使用了默认值，且默认值为nextval，处理过程如下图： ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000001797797557.png "点击放大") COPY场景中，由CN负责向GTM申请序列值，因此，当sequence的cache值较小，CN会频繁与GTM建连并申请nextval，出现性能瓶颈。[sequence相关的典型优化场景]将针对此种场景说明业务上的性能表现并提供优化方法。 #### sequence相关的典型优化场景 **业务场景：** 某业务场景中使用CDM数据同步工具进行数据迁移，从源端入库目标端DWS。导入速率与经验值相差较大，业务将CDM并发从1调整为5，同步速率仍无法提升。查看语句执行情况，除COPY入库外，其余业务均正常执行，无性能瓶颈，且观察无资源瓶颈，因此初步判断为该业务自身存在瓶颈，查看该表COPY相关的作业等待视图情况： ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000001797803897.png "点击放大") 如上图所示，由于CDM作业执行了5个并发，因此在活跃视图中可以看到5个COPY语句，根据这5个COPY语句对应的query_id查看等待视图情况。查看到这5个COPY中，同一时刻，仅有1个COPY在向GTM申请序列值，其余的COPY在等待轻量级锁。因此，即使作业中开启了5并发在运行，实际效果较1并发并没有带来明显提升。 **问题原因：** 目标表在建表时使用了serial类型，默认创建的sequence的cache为1，导致在并发COPY入库时，CN频繁与GTM建立连接，且多个并发之间存在轻量锁争抢，导致数据同步效率低。 **解决方案：** 此种场景下可以调大sequence的cache值，防止频繁GTM建连带来的瓶颈。本业务场景示例中，业务每次同步的数据量在10万左右，根据业务评估，将cache值修改为10000（实际使用时应根据业务设置合理的cache值，既能保证快速访问，又不会造成序列号浪费）。 8.2.1.100及以上集群版本中支持使用ALTER SEQUENCE的方式修改cache值。 8.2.1及之前低版本集群中DWS不支持通过ALTER SEQUENCE的方式修改cache值，可以通过如下方式修改已有sequence的cache值，以mytable表为例： 1. 解除当前sequence与目标表的关联关系 ``` ALTER SEQUENCE mytable_b_seq owned by none; ALTER TABLE mytable alter b drop default; ``` 2. 记录当前sequence值，作为新建sequence的start value。 ``` SELECT nextval('mytable_b_seq'); ``` 删除sequence。 ``` DROP SEQUENCE mytable_b_seq; ``` 3. 新建sequence并绑定目标表，xxx替换为上一步查到的nextval值。 ``` CREATE SEQUENCE mytable_b_seq START with xxx cache 10000 owned by mytable.b; ALTER SEQUENCE mytable_b_seq owner to u1;--u1为mytable表的属主，如果当前用户即为属主，可不执行此语句。 ALTER TABLE mytable alter b set default nextval('mytable_b_seq'); ```