A Distributed Relational Database Service (DRDS) sequence corresponds to a 64-digit number of the signed BIGINT type in MySQL. It is used to create a globally unique and sequentially incremental numeric sequence, such as values of a primary key column or a unique index column.
DRDS sequences are used in the following two ways:
- Explicit sequences are created and maintained by using sequence-specific data definition language (DDL) syntax and can be independently used. The sequence value can be retrieved by using
select seq.nextval, where
seqindicates the sequence name.
- Implicit sequences are used to automatically fill in primary keys with AUTO_INCREMENT defined and are automatically maintained by DRDS.
- DRDS creates an implicit sequence only when AUTO_INCREMENT is specified for a partitioned or a broadcast table. DRDS does not create a sequence for a non-partition table. The value of the AUTO_INCREMENT field in a non-partition table is automatically generated by the underlying ApsaraDB RDS for MySQL instance.
- However, in DRDS V5.3, if BY GROUP is explicitly specified for the AUTO_INCREMENT field for a non-partition table (that is, a single-database table), DRDS also automatically creates a group sequence, which is used in distributed transaction scenarios.
This topic includes the following parts:
- Types and features of DRDS sequences
- Use cases and selection of different sequence types
Types and features of DRDS sequences
Four types of DRDS sequences are supported.
|Type (abbreviation)||Globally unique||Consecutive||Monotonically increasing||Monotonically increasing within the same connection||Non-single point||Data type||Readability||Unitization capability|
|Group sequence (GROUP)||Yes||No||No||Yes||Yes||All integer types||Excellent||No|
|Unit group sequence (GROUP)||Yes||No||No||Yes||Yes||All integer types||Excellent||Yes|
|Time-based sequence (TIME)||Yes||No||Monotonically increasing at the macro level and non-monotonically increasing at the micro level||Yes||Yes||Only BIGINT||Poor||No|
|Simple sequence (SIMPLE)||Yes||Yes||Yes||Yes||No||All integer types||Excellent||No|
- Consecutive: If the current value is n, the next value must be n + 1. If the next value is not n + 1, it is nonconsecutive.
- Monotonically increasing: If the current value is n, the next value must be a number greater than n.
- Single point: The risk of a single point of failure (SPOF) exists.
- Monotonically increasing at the macro level and non-monotonically increasing at the micro level: An example of this is
1, 3, 2, 4, 5, 7, 6, 8, ...Such a sequence is monotonically increasing at the macro level and non-monotonically increasing at the micro level.
- Unitization capability: It indicates the capability of allocating DRDS sequences to different instances or databases.
Group sequence (GROUP, used by default)
A group sequence is a globally unique sequence with natural numeric values, which are not necessarily consecutive or monotonically increasing. If the sequence type is not specified, DRDS uses group sequence by default.
- Advantages: A group sequence is globally unique and provides excellent performance, preventing SPOFs.
- Disadvantages: A group sequence may contain nonconsecutive values, which may not necessarily start from the start value and do not cycle.
The values of a group sequence are created by multiple nodes to ensure high availability. The values in a segment are nonconsecutive if not all the values are used, such as in the case of disconnection.
Unit group sequence (GROUP)
A unit group sequence extends the units of a group sequence and is globally unique in different instances or database. Its values are not necessarily consecutive or monotonically increasing A unit group sequence with only one unit is equivalent to a common group sequence.
- Advantages: A unit group sequence has all the advantages of a group sequence and has more units.
- Disadvantages: A unit group sequence has the same disadvantages as a group sequence.
The basic principle is the same as that of a group sequence. You can customize the number of units and the unit index by adding related parameters.
- The number of units determines the DRDS sequence space assigned to the unit group sequence.
- Each unit (specified by the unit index) occupies a subset of the assigned space in the DRDS sequence.
- Subsets occupied by different units (specified by different unit indexes) do not overlap with each other, that is, each unit is assigned with a different sequence value.
- Unit group sequences that belong to the same DRDS sequence must have the same number of units but different unit indexes.
Note: DRDS starts to support unit group sequences from the following versions:
- DRDS V5.2: V5.2.7-1606682 (2018.4.27)
- DRDS V5.3: V5.3.3-1670435 (2018.8.15)
Time-based sequence (TIME)
A time-based sequence combines the timestamp, node number, and sequence number. It is globally unique and increases monotonically at the macro level. The update of values in this sequence is not based on databases, or stored as persistent data in databases. Only the name and type information is stored in databases. The time-based sequence has excellent performance. An example of time-based sequence is
776668092129345536, 776668098018148352, 776668111578333184, 776668114812141568...
- Advantages: A time-based sequence is globally unique with excellent performance.
- Disadvantages: The values of a time-based sequence are nonconsecutive. The START WITH, INCREMENT BY, MAXVALUE, and CYCLE or NOCYCLE parameters are invalid for time-based sequences.
- Sequence values must be of the BIGINT type when used in the auto-increment columns of tables.
- DRDS starts to support time-based sequences from the following versions:
- DRDS V5.2: V5.2.8-15432885 (2018.11.27)
- DRDS V5.3: V5.3.6-15439241 (2018.11.29)
Simple sequence (SIMPLE)
Only simple sequences support the INCREMENT BY, MAXVALUE, and CYCLE or NOCYCLE parameters.
- Advantages: A simple sequence is globally unique and supports MAXVALUE, and CYCLE or NOCYCLE parameters. Its values are consecutive and monotonically increasing.
- Disadvantages: A simple sequence is prone to SPOFs, poor performance, and bottlenecks. Use a simple sequence with caution.
Each sequence value must be persistently stored.
These sequences are globally unique and can be applied to both primary key columns and unique index columns.
- We recommend that you use group sequences in most cases.
- If you need to assign a DRDS sequence across instances or databases, use unit group sequences.
- If you accept monotonically increasing at the macro level and non-monotonically increasing at the micro level, and do not want to rely on the allocation mechanism of the database, you can use time-based sequences.
- Use only simple sequences for services that strongly depend on consecutive sequence values. Pay attention to sequence performance.
The following example shows how to create a sequence with a start value of 100000 and a step of 1.
- A simple sequence creates globally unique, consecutive, and monotonically increasing values, such as
100000, 100001, 100002, 100003, 100004, ..., 200000, 200001, 200002, 200003...Simple sequences are persistently stored. Even after services are restarted upon an SPOF, values are still consecutively created from the breakpoint. However, simple sequences have poor performance because each value is persistently stored once it is created.
- A group sequence or a unit group sequence may create values like
200001, 200002, 200003, 200004, 100001, 100002, 100003...
- The start value of a group sequence is not necessarily the same as the value of the START WITH parameter (which is 100000 in this example) but is invariably greater than this value. In this example, the group sequence starts from 200001.
- A group sequence is globally unique but may contain nonconsecutive values, for example, when a node is faulty or the connection that only uses partial values is closed. In this example, a skip segment occurs between 200004 and 100001.
- To ensure the global uniqueness of the unit group sequence across instances or databases, you must specify the same number of units but different unit indexes.