A few years ago Peter Zaitsev, in a post titled “To UUID or not to UUID,” wrote: “There is timestamp based part in UUID which has similar properties to auto_increment and which could be used to have values generated at same point in time physically local in BTREE index.”For this post I’ve rearranged the timestamp part of UUID (Universal Unique Identifier) and did some benchmarks.Many people store UUID as char (36) and use as row identity value (PRIMARY KEY) because it is unique across every table, every database and every server and allow easy merging of records from different databases. But here comes the problem, using it as PRIMARY KEY causes the problems described below.Problems with UUIDUUID has 36 characters which makes it bulky.InnoDB stores data in the PRIMARY KEY order and all the secondary keys also contain PRIMARY KEY. So having UUID as PRIMARY KEY makes the index bigger which can not be fit into the memoryInserts are random and the data is scattered.Despite the problems with UUID, people still prefer it because it is UNIQUE across every table, can be generated anywhere. In this blog, I will explain how to store UUID in an efficient way by re-arranging timestamp part of UUID.Structure of UUIDMySQL uses UUID version 1 which is a 128-bit number represented by a utf8 string of five hexadecimal numbersThe first three numbers are generated from a timestamp.The fourth number preserves temporal uniqueness in case the timestamp value loses monotonicity (for example, due to daylight saving time).The fifth number is an IEEE 802 node number that provides spatial uniqueness. A random number is substituted if the latter is not available (for example, because the host computer has no Ethernet card, or we do not know how to find the hardware address of an interface on your operating system). In this case, spatial uniqueness cannot be guaranteed. Nevertheless, a collision should have very low probability.The timestamp is mapped as follows: When the timestamp has the (60 bit) hexadecimal value: 1d8eebc58e0a7d7. The following parts of the UUID are set:: 58e0a7d7-eebc-11d8-9669-0800200c9a66. The 1 before the most significant digits (in 11d8) of the timestamp indicates the UUID version, for time-based UUIDs this is 1.Fourth and Fifth parts would be mostly constant if it is generated from a single server. First three numbers are based on timestamp, so they will be monotonically increasing. Lets rearrange the total sequence making the UUID closer to sequential. This makes the inserts and recent data look up faster. Dashes (‘-‘) make no sense, so lets remove them. 58e0a7d7-eebc-11d8-9669-0800200c9a66 => 11d8eebc58e0a7d796690800200c9a66BenchmarkingI created created three tablesevents_uuid – UUID binary(16) PRIMARY KEYevents_int – Additional BIGINT auto increment column and made it as primary key and index on UUID columnevents_uuid_ordered – Rearranged UUID binary(16) as PRIMARY KEYI created three stored procedures which insert 25K random rows at a time into the respective tables. There are three more stored procedures which call the random insert-stored procedures in a loop and also calculate the time taken to insert 25K rows and data and index size after each loop. Totally I have inserted 25M records.Data Size Horizontal Axis – Number of inserts x 25,000 Vertical Axis – Data Size in MB The data size for UUID table is more than other two tables.Index Size Horizontal axis – Number of inserts x 25,000 Vertical axis – Index Size in MB Total Size Horizontal Axis – Number of inserts x 25,000 Vertical Axis – Total Size in MB Time taken Horizontal axis – Number of inserts x 25,000 Vertical axis – Time Taken in seconds For the table with UUID as PRIMARY KEY, you can notice that as the table grows big, the time taken to insert rows is increasing almost linearly. Whereas for other tables, the time taken is almost constant.The size of UUID table is almost 50% bigger than Ordered UUID table and 30% bigger than table with BIGINT as PRIMARY KEY. Comparing the Ordered UUID table BIGINT table, the time taken to insert rows and the size are almost same. But they may vary slightly based on the index structure.root@localhost:~# ls -lhtr /media/data/test/ | grep ibd
-rw-rw—- 1 mysql mysql 13G Jul 24 15:53 events_uuid_ordered.ibd
-rw-rw—- 1 mysql mysql 20G Jul 25 02:27 events_uuid.ibd
-rw-rw—- 1 mysql mysql 15G Jul 25 07:59 events_int.ibdTable Structure#1 events_int
CREATE TABLE `events_int` (
`count` bigint(20) NOT NULL AUTO_INCREMENT,
`id` binary(16) NOT NULL,
`unit_id` binary(16) DEFAULT NULL,
`event` int(11) DEFAULT NULL,
`ref_url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`campaign_id` binary(16) COLLATE utf8_unicode_ci DEFAULT ”,
`unique_id` binary(16) COLLATE utf8_unicode_ci DEFAULT NULL,
`user_agent` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`city` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL,
`country` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL,
`demand_partner_id` binary(16) DEFAULT NULL,
`publisher_id` binary(16) DEFAULT NULL,
`site_id` binary(16) DEFAULT NULL,
`page_id` binary(16) DEFAULT NULL,
`action_at` datetime DEFAULT NULL,
`impression` smallint(6) DEFAULT NULL,
`click` smallint(6) DEFAULT NULL,
`sold_impression` smallint(6) DEFAULT NULL,
`price` decimal(15,7) DEFAULT ‘0.0000000’,
`actioned_at` timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00′,
`unique_ads` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`notification_url` text COLLATE utf8_unicode_ci,
PRIMARY KEY (`count`),
KEY `id` (`id`),
KEY `index_events_on_actioned_at` (`actioned_at`),
KEY `index_events_unit_demand_partner` (`unit_id`,`demand_partner_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
#2 events_uuid
CREATE TABLE `events_uuid` (
`id` binary(16) NOT NULL,
`unit_id` binary(16) DEFAULT NULL,
~
~
PRIMARY KEY (`id`),
KEY `index_events_on_actioned_at` (`actioned_at`),
KEY `index_events_unit_demand_partner` (`unit_id`,`demand_partner_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
#3 events_uuid_ordered
CREATE TABLE `events_uuid_ordered` (
`id` binary(16) NOT NULL,
`unit_id` binary(16) DEFAULT NULL,
~
~
PRIMARY KEY (`id`),
KEY `index_events_on_actioned_at` (`actioned_at`),
KEY `index_events_unit_demand_partner` (`unit_id`,`demand_partner_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;Conclusions Create function to rearrange UUID fields and use itDELIMITER //
CREATE DEFINER=`root`@`localhost` FUNCTION `ordered_uuid`(uuid BINARY(36))
RETURNS binary(16) DETERMINISTIC
RETURN UNHEX(CONCAT(SUBSTR(uuid, 15, 4),SUBSTR(uuid, 10, 4),SUBSTR(uuid, 1, 8),SUBSTR(uuid, 20, 4),SUBSTR(uuid, 25)));
//
DELIMITER ;InsertsINSERT INTO events_uuid_ordered VALUES (ordered_uuid(uuid()),’1′,’M’,….);SelectsSELECT HEX(uuid),is_active,… FROM events_uuid_ordered ;Define UUID as binary(16) as binary does not have any character set Referenceshttp://dev.mysql.com/doc/refman/5.1/en/miscellaneous-functions.html#function_uuidhttp://www.famkruithof.net/guid-uuid-timebased.htmlhttp://www.percona.com/blog/2007/03/13/to-uuid-or-not-to-uuid/http://blog.codinghorror.com/primary-keys-ids-versus-guids/ The post Store UUID in an optimized way appeared first on MySQL Performance Blog.
via Planet MySQL
Store UUID in an optimized way