Search code examples
mysqlsharding

MySQL Cluster sharding a table with an auto incrementing BIGINT unique primary key

I have a hypothetical table with an auto incrementing BIGINT unique primary key. If I choose to shard the table what happens to the primary key BIGINT? Does sharding allow to exceed the capacity of the BIGINT? Does sharding do anything to prevent duplicate keys from showing up?

Thanks in advance...

Solution

  • As I understand it, the NDB storage engine shards based on the modulus of the first primary key column over the number of shards. It does this so it's deterministic which node to store the row on, and it's also deterministic which node to read from when your query requests a row.

    This means you can't have the same primary key value exist on more than one shard. In other words, the range of values in a BIGINT is divided among the shards.

    So no, you don't get to exceed the range of values in a BIGINT. Though I question why you even need to ask, because BIGINT is really, really big and you are not likely to exhaust a BIGINT, unless you skip a few billion id values per row.

    Yes, mapping values to nodes means that unique key checks can be done on each node. However, there are caveats to this when using replication. Read https://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-replication-issues.html