40

If I set the primary key to be INT type (AUTO_INCREMENT) or set it in UUID, what is the difference between these two in the database performance (SELECT, INSERT etc) and why?

informatik01
  • 16,038
  • 10
  • 74
  • 104
孙为强
  • 427
  • 1
  • 4
  • 6
  • If you can elaborate a bit on what you know so far then people will be able to fill in the gaps - as well as your question hopefully being useful to other users in the future – Bendy May 26 '15 at 14:55
  • a int is a data type, UUID is function/method/procedure (not sure about the terminology) returning an unique string. Asking for difference is difficult because they have pretty much *nothing* in common. I would guess what you are asking for is the `auto increment` instead... – luk2302 May 26 '15 at 15:18
  • I am sorry that I didn't describe this question clearly.I mean if I set the primary id key in int type (auto increment ) or UUID,what is the difference in the database performance(select,insert...) and why – 孙为强 May 27 '15 at 05:26
  • after latest comment update, i've updated my answer, i think it answrs all your concerns – Nikos M. May 30 '15 at 18:43

3 Answers3

63

UUID returns a universal unique identifier (hopefuly also unique if imported to another DB as well).

To quote from MySQL doc (emphasis mine):

A UUID is designed as a number that is globally unique in space and time. Two calls to UUID() are expected to generate two different values, even if these calls are performed on two separate computers that are not connected to each other.

On the other hand a simply INT primary id key (e.g. AUTO_INCREMENT) will return a unique integer for the specific DB and DB table, but which is not universally unique (so if imported to another DB chances are there will be primary key conflicts).

In terms of performance, there shouldn't be any noticeable difference using auto-increment over UUID. Most posts (including some by the authors of this site), state as such. Of course UUID may take a little more time (and space), but this is not a performance bottleneck for most (if not all) cases. Having a column as Primary Key should make both choices equal wrt to performance. See references below:

  1. To UUID or not to UUID?
  2. Myths, GUID vs Autoincrement
  3. Performance: UUID vs auto-increment in cakephp-mysql
  4. UUID performance in MySQL?
  5. Primary Keys: IDs versus GUIDs (coding horror)

(UUID vs auto-increment performance results, adapted from Myths, GUID vs Autoincrement)

enter image description here

UUID pros / cons (adapted from Primary Keys: IDs versus GUIDs)

GUID Pros

  • Unique across every table, every database, every server
  • Allows easy merging of records from different databases
  • Allows easy distribution of databases across multiple servers
  • You can generate IDs anywhere, instead of having to roundtrip to the database
  • Most replication scenarios require GUID columns anyway

GUID Cons

  • It is a whopping 4 times larger than the traditional 4-byte index value; this can have serious performance and storage implications if you're not careful
  • Cumbersome to debug (where userid='{BAE7DF4-DDF-3RG-5TY3E3RF456AS10}')
  • The generated GUIDs should be partially sequential for best performance (eg, newsequentialid() on SQL 2005) and to enable use of clustered indexes.

Note

I would read carefully the mentioned references and decide whether to use UUID or not depending on my use case. That said, in many cases UUIDs would be indeed preferable. For example one can generate UUIDs without using/accessing the database at all, or even use UUIDs which have been pre-computed and/or stored somewhere else. Plus you can easily generalise/update your database schema and/or clustering scheme without having to worry about IDs breaking and causing conflicts.

In terms of possible collisions, for example using v4 UUIDS (random), the probability to find a duplicate within 103 trillion version-4 UUIDs is one in a billion.

Nikos M.
  • 8,033
  • 4
  • 36
  • 43
  • 7
    Con: If the cache is not big enough for the entire UUID index, performance will suck big time. – Rick James Jun 05 '15 at 02:17
  • @RickJames, true and is mentioned in ther references quoted – Nikos M. Aug 10 '15 at 19:42
  • 1
    Here is a good article breaking down performance: https://www.percona.com/blog/2019/11/22/uuids-are-popular-but-bad-for-performance-lets-discuss/ People should consider if UUIDs are necessary for their use case before just implementing them. There are many cases where a UUID key can be very beneficial. In cases where it's not, it's probably not worth it. Also, it should be stated that UUID columns can always be added to tables at a later date, should the need arise. – Jacob Thomason Oct 25 '20 at 21:28
  • @JacobThomason, nice article. Storage requirements for UUIDs have already been mentioned though, – Nikos M. Oct 26 '20 at 06:58
  • There are MAJOR performance benefits and I'm currently rebuilding our entire db because we are using 36 char UUID for all of our PKs. Orders of magnitude faster for mysql INNODB v8. Even if you have several databases, it would be better to figure out a workaround IMO over using a UUID if you care anything about performance. Ours aren't partially sequenced so that could be part of it, but overall, auto increment INT is far and away better IMHO. – Muab Nhoj Nov 04 '22 at 17:11
4

A UUID key cannot be pk until unless persisted in DB so round tripping will happen until then you cannot assume its pk without a successful transaction. Most of the UUID use time based, mac based, name based or some random uuid. Given we are moving heavily towards container based deployments and they have a pattern for starting sequence MAC addresses relying on mac addresses will not work. Time based is not going to guarantee as the assumption is systems are always in exact time sync which is not true sometimes as clocks will not follow the rules. GUID cannot guarantee that collision will never occur just that in given short period of time it will not occur but given enough time and systems running in parallel and proliferations of systems that guarantee will eventually fail.

http://www.ietf.org/rfc/rfc4122.txt

Greg
  • 23,155
  • 11
  • 57
  • 79
Rohitdev
  • 866
  • 6
  • 15
  • A "short period of time" is arguably a matter of definition. However, I assume that most devs would not consider 86 years a "short period of time". 86 years is the time it takes to generate the amount of version-4 UUIDs (~2.71 * 10^18 = 2.71 quintillion) you need to generate in a single system to have a 50 percent probability of at least one collision when constantly generating 1 billion UUIDs per second. https://en.wikipedia.org/wiki/Universally_unique_identifier#Collisions – Blade1336 Jul 24 '23 at 17:43
  • Yes , its subjective and relative. The 86 years is for a single node generating the keys but if its a clustered environment with large number of nodes , in the same network for the same service ,generating the keys than it will get exhausted quickly – Rohitdev Aug 21 '23 at 09:01
2

For MySQL, which uses clustered primary key, version 4 randomly generated UUID will hurt insertion performance if used as the primary key. This is because it requires reordering the rows to place the newly inserted row at the right position inside the clustered index.

FWIW, PostgreSQL uses heap instead of clustered primary key, thus using UUID as the primary key won't impact PostgreSQL's insertion performance.

For more information, this article has a more comprehensive comparison between UUID and Int: Choose Primary Key - UUID or Auto Increment Integer

Tianzhou
  • 978
  • 2
  • 9
  • 15