5.1.1 Understanding Document IDs

X DevAPI relies on server based document ID generation, added in MySQL version 8.0.11, which results in sequentially increasing document IDs across all clients. InnoDB uses the document ID as a primary key, therefore these sequential primary keys for all clients result in efficient page splits and tree reorganizations.

This section describes the properties and format of the automatically generated document IDs.

Document ID Properties

The _id field of a document behaves in the same way as any other field of the document during queries, except that its value cannot change once inserted to the collection. The _id field can be used as the primary key of the collection (using stored generated columns). It is possible to override the automatic generation of document IDs by manually including an ID in an inserted document.

Important

If you are using manual document IDs, you must ensure that IDs from the server's automatically generated document ID sequence are never used. X Plugin is not aware of the data inserted into the collection, including any IDs you use. Thus in future inserts, if the document ID which you assigned manually when inserting a document uses an ID which the server was going to use, the insert operation fails with an error due to primary key duplication.

Whenever an _id field value is not present in an inserted document, the server generates an _id value. The generated _id value used for a document is returned to the client as part of the document insert Result message. If you are using X DevAPI on an InnoDB cluster, the automatically generated _id must be unique within the range of the cluster. Use the mysqlx_document_id_unique_prefix option to ensure that the unique_prefix part of the document ID is unique to the cluster.

The _id field must be sequential (always incrementing) for optimal InnoDB insertion performance (at least within a single server). The sequential nature of _id values is maintained across server restarts.

In a multi-primary Group Replication or InnoDB cluster environment, the generated _id values of a table are unique across instances to avoid primary key conflicts and minimize transaction certification.

Document ID Generation

This section describes how document IDs are formatted. The general structure of the collection table remains unchanged, except for the type of the generated _id column, which changes from VARCHAR(32) to VARBINARY(32).

The format of automatically generated document ID is:

unique_prefix	start_timestamp	serial
4 bytes	8 bytes	16 bytes

Where:

• serial is a per-instance automatically incremented integer serial number value, which is hex encoded and has a range of 0 to 2**64-1. The initial value of serial is set to the auto_increment_offset system variable, and the increment of the value is set by the auto_increment_increment system variable.

• start_timestamp is the time stamp of the startup time of the server instance, which is hex encoded. In the unlikely event that the value of serial overflows, the start_timestamp is incremented by 1 and the serial value then restarts at 0.

• unique_prefix is a value assigned by InnoDB cluster to the instance, which is used to make the document ID unique across all replicasets from the same cluster. The range of unique_prefix is from 0 to 2**16-1, which is hex encoded, and defaults to 0 if not set by InnoDB cluster or the mysqlx_document_id_unique_prefix system variable has not been configured.

This document ID format ensures that:

• The primary key value monotonically increments for inserts originating from a single server instance, although the interval between values is not uniform within a table.

• When using multi-primary Group Replication or InnoDB cluster, inserts to the same table from different instances do not have conflicting primary key values; assuming that the instances have the auto_increment_* system variables configured properly.