MYSQL Indexes and Keys
Create unique index
A unique index prevents the insertion of duplicated data in a table. NULL values can be inserted in the columns that form part of the unique index (since, by definition, a NULL value is different from any other value, including another NULL value)
.Starts with 1 and increments by 1 automatically when you fail to specify it on INSERT, or specify it as NULL.
.The ids are always distinct from each other, but...
.Do not make any assumptions (no gaps, consecutively generated, not reused, etc) about the values of the id other than being unique at any given instant.
.On restart of server, the 'next' value is 'computed' as MAX(id)+1.
.If the last operation before shutdown or crash was to delete the highest id, that id may be reused (this is engine-dependent). So, do not trust auto_increments to be permanently unique; they are only unique at any moment.
.For multi-master or clustered solutions, see auto_increment_offset and auto_increment_increment.
.It is OK to have something else as the PRIMARY KEY and simply do INDEX(id). (This is an optimization in some situations.)
.Using the AUTO_INCREMENT as the "PARTITION key" is rarely beneficial; do something different.
.Various operations may "burn" values. This happens when they pre-allocate value(s), then don't use them: INSERT IGNORE (with dup key), REPLACE (which is DELETE plus INSERT) and others. ROLLBACK is another cause for gaps in ids.
.In Replication, you cannot trust ids to arrive at the slave(s) in ascending order. Although ids are assigned in consecutive order, InnoDB statements are sent to slaves in COMMIT order.