This sections describes in detail how document IDs are generated
and how to interpret them. 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.
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 is 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.
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 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.
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 ofserial
is set to theauto_increment_offset
system variable, and the increment of the value is set by theauto_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 ofserial
overflows, thestart_timestamp
is incremented by 1 and theserial
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 instances from the same cluster. The range ofunique_prefix
is from 0 to 2**16-1, which is hex encoded, and defaults to 0 if not set by InnoDB Cluster or themysqlx_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.