Храните UUID оптимизированным способом

Для этого поста я переставил часть метки времени UUID (универсальный уникальный идентификатор) и сделал несколько тестов.

Многие люди хранят UUID как char (36) и используют в качестве значения идентификатора строки (PRIMARY KEY), потому что он уникален для каждой таблицы, каждой базы данных и каждого сервера и позволяет легко объединять записи из разных баз данных. Но здесь возникает проблема, использование его в качестве ПЕРВИЧНОГО КЛЮЧА вызывает проблемы, описанные ниже.

Проблемы с UUID

• UUID имеет 36 символов, что делает его громоздким.
• InnoDB хранит данные в порядке PRIMARY KEY, и все вторичные ключи также содержат PRIMARY KEY. Таким образом, наличие UUID в качестве PRIMARY KEY увеличивает индекс, который не помещается в память
• Вставки случайные, а данные разбросаны.

Несмотря на проблемы с UUID, люди все еще предпочитают его, потому что он уникален для каждой таблицы и может быть сгенерирован где угодно. В этом блоге я объясню, как эффективно хранить UUID, переставляя часть метки времени UUID.

Структура UUID

MySQL использует UUID версии 1, который представляет собой 128-битное число, представленное строкой utf8 из пяти шестнадцатеричных чисел

• Первые три числа генерируются из отметки времени.
• Четвертое число сохраняет временную уникальность в случае, если значение временной метки теряет монотонность (например, из-за перехода на летнее время).
• Пятое число — это номер узла IEEE 802, который обеспечивает пространственную уникальность. Случайное число заменяется, если последнее недоступно (например, потому что на хост-компьютере нет карты Ethernet или мы не знаем, как найти аппаратный адрес интерфейса в вашей операционной системе). В этом случае пространственная уникальность не может быть гарантирована. Тем не менее, столкновение должно иметь очень низкую вероятность.

Отметка времени отображается следующим образом:
Когда отметка времени имеет шестнадцатеричное значение (60 бит): 1d8eebc58e0a7d7. Устанавливаются следующие части UUID: 58e0a7d7-eebc-11d8 -9669-0800200c9a66. 1 перед старшими значащими цифрами (в 11d8) метки времени указывает версию UUID, для основанных на времени UUID это 1.

Четвертая и пятая части будут в основном постоянными, если они генерируются с одного сервера. Первые три числа основаны на отметке времени, поэтому они будут монотонно увеличиваться. Позволяет переставить общую последовательность, делая UUID ближе к последовательному. Это заставляет вставки и последние данные искать быстрее. Тире (‘-‘) не имеют смысла, поэтому давайте удалим их.
58e0a7d7-eebc-11d8-9669-0800200c9a66 => 11d8eebc58e0a7d796690800200c9a66

Бенчмаркинг

Я создал три таблицы:

• events_uuid — UUID двоичный (16) ПЕРВИЧНЫЙ КЛЮЧ
• events_int — Дополнительный столбец автоинкремента BIGINT и сделан его в качестве первичного ключа и индекса для столбца UUID
• events_uuid_ordered — Переупорядоченный двоичный код UUID (16) как ПЕРВИЧНЫЙ КЛЮЧ

Я создал три хранимые процедуры, которые вставляют 25K случайных строк за раз в соответствующие таблицы. Есть еще три хранимые процедуры, которые вызывают случайные хранимые процедуры вставки в цикле, а также вычисляют время, необходимое для вставки 25К строк и данных и размера индекса после каждого цикла. Всего я вставил 25 миллионов записей.

• Размер данных
  Горизонтальная ось — Количество вставок x 25 000
  Вертикальная ось — Размер данных в МБ Размер данных для таблицы UUID больше, чем в двух других таблицах.
  
  
• Размер индекса
  Горизонтальная ось — Количество вставок x 25 000
  Вертикальная ось — Размер индекса в МБ
  
• 
  Горизонтальная ось общего размера — количество вставок x 25 000
  вертикальной оси — общий размер в МБ
  
• Время, взятое
  Горизонтальная ось — Количество вставок х 25 000
  Вертикальная ось — Время, взятое в секундах
  

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.ibd

#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;

DELIMITER //
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 ;

INSERT INTO events_uuid_ordered VALUES (ordered_uuid(uuid()),'1','M',....);

SELECT HEX(uuid),is_active,... FROM events_uuid_ordered ;