We recently saw how the new Invisible Column feature works in MySQL since 8.0.23 and how we can use it as a Primary Key for InnoDB tables when no Primary Key was defined.
As I wrote earlier, a good Primary Key is important for InnoDB (storage, IOPS, secondary indexes, memory…) but there is another important domain where a Primary Key is important in MySQL: replication !
Asynchronous Replication
When using “traditional” replication, if you modify a record
(UPDATE
and DELETE
), the record(s) to
modify on the replica are identified using indexes, and of course
the Primary Key if any. The hidden global 6-bytes auto generated
by InnoDB primary key is never used as never exposed and as it’s
global, …