How exactly could we provide a seamless automated method of key generation to the application that **operates across all shards**

http://www.dbshards.com/articles/database-sharding-whitepapers/:

Auto-increment key management. Typical auto-increment functionality provided by database management systems generate a sequential key for each new row inserted into the database. This is fine for a single database application, but when using Database Sharding, keys must be managed across all shards in a coordinated fashion. The requirement here is to provide a seamless, automated method of key generation to the application, one that operates across all shards, ensuring that keys are unique across the entire system.

I cannot understand how exactly coul开发者_开发问答d we provide a seamless automated method of key generation to the application that operates across all shards.

I have heard of using GUID, but since GUID isn't guaranteed to be unique across different machines, I do not see how that is going to work reliably

---

For MySQL you can use the server variables auto_increment_increment and auto_increment_offset to have different servers generate different Ids to avoid collisions. It is entirely up to you to configure your properly servers so that you don't end up with duplicate values.

---

I struggled with this for a distributed database system I developed, all problems went away when I switch to using GUIDs as primary unique identifiers. I highly recommend them. Not only do you fine identifiers are unique within a single key definition data domain, but you will fine that you can have universal tables that reference multiple domains.

I have also learn not to rely on business data for a true unique key in the database, customers think these keys are unique but there are always incidents when they want to change them and many times have an undefined situation.