GUIDs as fast primary keys under multiple databases. Introduction This article outlines an approach for using GUID values as primary keys/clustered indexes that avoids most of the normal disadvantages, adapting the COMB model for sequential GUIDs developed by Jimmy Nilsson in his article The Cost of GUIDs as Primary Keys.
While that basic model has been used by a variety of libraries and frameworks (including NHibernate), most implementations seem to be specific to Microsoft SQL Server. This article attempts to adapt the approach into a flexible system that's can be used with other common database systems such as Oracle, PostgreSQL, and MySQL, and also addresses some of the eccentricities of the .NET Framework in particular. Background Historically, a very common model for database design has used sequential integers to identify a row of data, usually generated by the server itself when the new row is inserted. However, there are also some situations where it's not ideal. One tempting alternative is to use GUIDs as key values. The Algorithm. To UUID or not to UUID ?
March 13, 2007 by Peter Zaitsev28 Comments Brian recently posted an article comparing UUID and auto_increment primary keys, basically advertising to use UUID instead of primary keys.
I wanted to clarify this a bit as I’ve seen it being problems in so many cases. First lets look at the benchmark – we do not have full schema specified in the article itself so it results are not absolutely clear but we already can have certain conclusions. Data size is very small. What is the biggest problem with UUID ? What is about Secondary Keys ? Parallel Inserts UUID are often advertised as allowing to spread the load from single buffer page which auto_increment is constantly hitting, this is true but at large date size it is way overturned by BTREE buffer. Data Clustering This again applies to Innodb tables aspect of primary key selection – you often can gain a lot by selecting primary key which provides data clustering which matches your application needs.
Can UUID be handled efficiently ? Developed: auto-generate IDs (GUIDs) within MySQL. Brian "Krow" Aker's Idle Thoughts - Myths, GUID vs Autoincrement. I am a fan of using GUIDs as primary keys.
They are database independent, object independent and I can either create them in my application or in the database (MySQL since 5.0 has had a UUID() function but since it does not replicate I don't use it normally... this has been fixed in 5.1 though). What I have heard for years was that Innodb didn't handle them very well. I have had my doubts, but never taken the time to see if this was true or not. This week I finally got around to putting this to a test.
/mysqlslap --concurrency=1,50,100,150,200,250,300 --iterations=10 -- number-int-cols=2 --number-char-cols=3 --auto-generate-sql --auto- generate-sql-add-autoincrement --auto-generate-sql-load-type=update -- auto-generate-sql-execute-number=10000 --auto-generate-sql-unique- write-number=10 --auto-generate-sql-write-number=100000 --csv=/tmp/ innodb-auto.csv --engine=blackhole,innodb So 10K of rows, with increasing numbers of threads making modifications.
12.14 Miscellaneous Functions. Primary Keys: IDs versus GUIDs. Long-time readers of this blog know that I have an inordinate fondness for GUIDs.
Each globally unique ID is like a beautiful snowflake: every one a unique item waiting to be born. Perhaps that's why I read with great interest recent accounts of people switching their database tables from traditional integer primary keys ... ID Value -- ----- 1 Apple 2 Orange 3 Pear 4 Mango .. to GUID keys. ID Value ------------------------------------ ----- C87FC84A-EE47-47EE-842C-29E969AC5131 Apple 2A734AE4-E0EF-4D77-9F84-51A8365AC5A0 Orange 70E2E8DE-500E-4630-B3CB-166131D35C21 Pear 15ED815C-921C-4011-8667-7158982951EA Mango I know what you're thinking. Using a GUID as a row identity value feels more natural-- and certainly more truly unique-- than a 32-bit integer.
I'm not proposing that every database switch to GUID primary keys, but I do think it's important to know the option is out there.