This topic introduces the terms that are related to sequences and the types of sequences that PolarDB-X supports.
PolarDB-X provides sequences to generate globally unique and sequentially incremental numeric values, such as values of primary key columns and values of unique index key columns. Values that are generated by PolarDB-X sequences are of the MySQL BIGINT data type that stores signed 64-bit integers.
Terms
This section introduces the terms that are related to sequences to help you select a sequence type based on your business requirements.
Consecutive: If the current value in a consecutive sequence is n, the next value must be n + 1. If the next value is not n + 1, the sequence is a nonconsecutive sequence.
Monotonically increasing: If the current value in a monotonically increasing sequence is n, the next value must be a number greater than n.
Monotonically increasing at the macro level and non-monotonically increasing at the micro level: For example, the sequence that contains 1, 3, 2, 4, 5, 7, 6, 8, ... is monotonically increasing at the macro level and non-monotonically increasing at the micro level.
Unitization capability: The unitization capability allows you to create a GROUP sequence that contains multiple units across multiple instances or databases. Globally unique sequence values are generated across multiple instances or databases.
Use sequences
PolarDB-X sequences are divided into the following two categories:
Explicit sequence: You can use DDL statements to create and maintain explicit sequences. An explicit sequence can be used in an independent manner. You can execute the
SELECT seq.nextvalstatement to obtain values in an explicit sequence. seq specifies the name of the sequence.Implicit sequence: If you specify the AUTO_INCREMENT attribute for a primary key column, an implicit sequence can be used to automatically generate primary key values. PolarDB-X automatically maintains implicit sequences. For more information see AUTO_INCREMENT.
Supported types and features of sequences
PolarDB-X supports the following types of sequences.
Type (abbreviation) | Globally unique | Consecutive | Monotonically increasing | Monotonically increasing in the same session | Data type | Readability | Unitization capability |
New sequences | Yes | Yes | Yes | Yes | All integer data types are supported. | High | No |
Group sequences | Yes | No | No | Yes | All integer data types are supported. | High | Yes |
Time sequences | Yes | No | Monotonically increasing at the macro level and non-monotonically increasing at the micro level | Yes | Only BIGINT is supported. | Low | No |
New sequences
A new sequence generates values that are globally unique, consecutive, and monotonically increasing. By default, values of a new sequence are natural numeric values, and the start value of a new sequence is 1.
If the kernel version of your PolarDB-X instance is V5.4.14 or later and you do not specify the type of a sequence when you create the sequence in a database in AUTO mode database, a new sequence is created.
Only instances of PolarDB-X 5.4.17 and later allow you to specify the INCREMENT BY, MAXVALUE, and CYCLE or NOCYCLE parameters.
For limits on new sequences, see New sequences.
Advantages: Global uniqueness, continuity, monotonically increasing, high performance, and support for the INCREMENT BY, MAXVALUE, and CYCLE or NOCYCLE parameters.
Disadvantages: Dependence on GMS features and occupation of more GMS resources than other types of sequences.
Example:
You can create a new sequence and specify 100000 as the start value of the sequence. The sequence generates globally unique, consecutive, and monotonically increasing values. Sample sequence values:
100000, 100001, 100002, 100003, 100004, ..., 199999, 200000, 200001, 200002, 200003,...Group sequences
A group sequence generates natural numeric values that are globally unique and start from 100001. Values in a group sequence do not need to be consecutive or monotonically increasing. By default, if you do not specify the UNIT COUNT parameter or INDEX parameter when you create a group sequence, a group sequence of only one unit is created. In most scenarios, only one unit is required.
If you specify a value that is greater than 1 as the value of the UNIT COUNT parameter, the group sequence contains multiple units. A group sequence that contains multiple units has the following features:
The number of units determines the sequence space that is assigned to the group sequence.
Each unit is specified by the INDEX parameter. Each unit occupies a subset of the sequence space.
Unit indexes cannot be duplicate. You must specify different indexes for different units. The subsets of sequence space for different units do not overlap. This indicates that the sequence generates different values for different units.
PolarDB-X uses multiple nodes to generate values for a group sequence. This multi-node model ensures high availability of group sequences. The system retrieves a segment of values from a database at a time. In scenarios such as network disconnections, not all values in a segment are used. As a result, the sequence values are nonconsecutive.
Advantages: group sequences generate globally unique values, provide high performance, and do not cause single points of failures. group sequences also can provide unique values across instances or databases.
Disadvantages: group sequences may contain nonconsecutive values and may not start from the specified start value.
For limits on group sequences, see Group sequences.
Example:
You can create a group sequence and specify 100000 as the start value of the sequence. The sequence generates globally unique values. Sample sequence values:
200001, 200002, 200003, 200004, 100001, 100002, 100003, 200005, 200006, ...The actual start value of a group sequence may not be the start value that is specified. The actual start value can be a value that is greater than the start value that is specified. In this example, the specified start value is 100000. However, the actual start value of the sequence is 200001.
A group sequence generates globally unique values and can contain nonconsecutive values. For example, if a node fails or only some values in a segment of sequence values are used when the connection is closed, the sequence values are nonconsecutive values. In the preceding example, values between 200004 and 100001, and between 100003 and 200005 are not used.
Time sequences
A value of a time sequence consists of a timestamp, node ID, and serial number. A time sequence generates values that are globally unique and auto-increasing at the macro level. Updates of values in time sequences do not rely on databases. You only need to store sequence names and types in databases instead of storing sequence values in a persistent manner. This way, a time sequence can deliver excellent performance. Sample sequence values: 776668092129345536, 776668098018148352, 776668111578333184, 776668114812141568, ....
Advantages: time sequences generate globally unique values and deliver excellent performance.
Disadvantages: Values in a time sequence are nonconsecutive. The START WITH, INCREMENT BY, MAXVALUE, CYCLE, and NOCYCLE parameters do not take effect on time sequences.
If a time sequence is associated with the auto-increment column in a table, the values of the auto-increment column must be of the BIGINT data type.
Common scenarios
The three types of sequences generate globally unique values, and can be used in primary key columns and unique index key columns.
If the kernel version of your PolarDB-X instance is V5.4.14 or later, we recommend that you use only NEW sequences in databases in AUTO mode.
By default, group sequences are used in databases in DRDS mode.
Group sequences can be used only for applications that consist of multiple units or that are migrated from PolarDB-X 1.0.
Time sequences can be used only for applications that are migrated from PolarDB-X 1.0.