База данных — краеугольный камень любого программного продукта. Ее сложнее всего масштабировать, она представляет наибольшую ценность и потребляет больше всего ресурсов: как вычислительных, так и ресурсов в виде внимания инженеров на ее оптимизацию, поддержку и мониторинг.
Базу данных можно назвать душой проекта — она живет и развивается вместе с ним, увеличивается в объеме и из-за этого изменяет свои свойства. Но даже небольшие ошибки при проектировании баз данных, которые неизбежно возникают при хаотичном начале любого проекта (детские травмы), в последующем часто оборачиваются сильной головной болью и издержками.
Меня зовут Максим Таисов, я разработчик в компании СберЗдоровье. В этой статье я собрал несколько примеров организации баз данных, которые позволяют немного сэкономить на дисковом пространстве и скорости исполнения запросов. Все описанные кейсы хорошо известны в узких кругах — я не претендую на новаторство, но считаю важным, чтобы инженеры больше задумывались над структурой баз данных до того, как она становится неповоротливой и неэффективной.
Справка: Все измерения были проведены в MySQL 8.0 из коробки (если немного подкрутить настройки, можно ускорить некоторые показатели значительно). Среда — виртуальная машина с двумя 2 ГГц ядрами, 4 Гб оперативной памяти и дешевыми HDD. Для проведения запросов и оценки времени использовалась утилита mysqlslap и немного скриптов
Пример №1
Для хранения небольших строк нерегламентированной длины лучше использовать VARCHAR, а не CHAR.
Обусловлено это тем, что CHAR занимает на диске всю указанную ему длину, тогда как VARCHAR займет ровно столько, сколько занимает сама строка, записанная в колонку.
Например, возьмем две таблицы с одинаковой структурой.
Для полей `first_name`, `last_name` и `email` в одной используем VARCHAR, а в другой — CHAR. Средняя длина полей first_name и last_name — 6 символов, а email — 30 символов.
Размер таблицы с VARCHAR |
Размер таблицы с CHAR |
|
100к строк |
13.5 MiB |
28.6 MiB |
1 млн строк |
92.6 MiB |
213.7 MiB |
10 млн строк |
783.0 MiB |
1.7 GiB |
100 млн строк |
7.6 GiB |
17.0 GiB |
По результатам замеров видно, что разница существенная — использование VARCHAR экономит место.
Пример №2
Для некоторых таблиц выгодно использовать TINYINT(1) вместо INT(11).
Например, есть таблица entity_dictionary, в которой заведомо будет небольшое количество строк. Идентификаторы из entity_dictionary используются в таблице users, у которых очень большое количество строк. Какое количество дискового пространства можно сэкономить, если использовать TINYINT(1) вместо INT(11)? Давайте посмотрим.
Размер таблицы users с entity_dictionary.id INT(11) |
Размер таблицы users с entity_dictionary.id TINYINT(1) |
|
2 млн строк |
357 MiB |
349 MiB |
20 млн строк |
2573 MiB |
2513 MiB |
200 млн строк |
26705 MiB |
26081 MiB |
Разница всего 2.6% или примерно 700 мегабайт. На первый взгляд показатель незначительный, но такое мнение ошибочно, особенно если на entity_dictionary.id ссылается множество таблиц — сэкономленное дисковое пространство может быть кратно больше.
Так же не нужно забывать, что бэкапы выполняются ежедневно и хранятся минимум в течение месяца. У нас в компании есть похожий кейс — в нашем случае использование INT(11) вместо TINYINT(1) всего для одной системной сущности требовало дополнительных 500 Гб дискового пространства в месяц, за которые пришлось бы регулярно платить. Переход на TINYINT(1) позволил нам cэкономить — да, на спичках, но про это и речь.
Пример №3
Не стоит забывать, что в MySQL, помимо стандартного InnoDB, есть много других движков хранения данных. Для специфичных случаев, могут хорошо подойти движки Memory и Archive.
Если вам не нужна персистентность, а нужна скорость — используйте Memory. Обработка запросов будет быстрой, но размер такой таблицы ограничен, и все данные будут утеряны при рестарте сервера.
Если хочется занимать меньше места на диске, используйте Archive. Движок занимает, как минимум в 2 раза меньше места, чем InnoDB, но не поддерживает транзакции и индексы.
Рассмотрим интересный пример использования Memory таблицы для ускорения JOIN запроса.
Запрос A в обычную InnoDB таблицу:
CREATE TABLE `address` (
`address_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`address` varchar(50) NOT NULL,
`district` varchar(20) NOT NULL,
`city_id` smallint(5) unsigned NOT NULL,
`postal_code` varchar(10) DEFAULT NULL,
`phone` varchar(20) NOT NULL,
`location` geometry NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`address_id`),
KEY `idx_fk_city_id` (`city_id`),
SPATIAL KEY `idx_location` (`location`),
CONSTRAINT `address_ibfk_1` FOREIGN KEY (`city_id`) REFERENCES `city` (`city_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;/** Запрос A */
SELECT c.last_name, c.first_name, a.district
FROM `customer` AS c
LEFT JOIN `address` as a ON a.address_id = c.address_id
WHERE c.customer_id=FLOOR(RAND()*30000));Запрос B в Memory таблицу:
CREATE TABLE `address_district_memory` (
`address_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`district` varchar(20) NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`address_id`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;/** Запрос B */
SELECT c.last_name, c.first_name, a.district
FROM `customer` AS c
LEFT JOIN `address_district_memory` as a ON a.address_id = c.address_id
WHERE c.customer_id=FLOOR(RAND()*30000));Среднее время выполнения Запроса А (1000 запросов в 20 потоков) |
Среднее время выполнения Запроса Б (1000 запросов в 20 потоков) |
|
В таблице с адресами 1000 строк |
67.148 seconds |
22.387 seconds |
В таблице с адресами 10 млн строк |
85.583 seconds |
24.430 seconds |
Снова разница очевидна. Но стоит отметить, что при таком сценарии могут появляться и дополнительные сложности, особенно с учетом ограниченного размера Memory-таблиц.
Пример №4
Как эффективно хранить UUID-ов для идентификаторов сущностей?
Об этом говорят давно и для этого есть две основные причины: привычные INT с AUTO_INCREMENT занимают заметно меньше места в памяти и на диске, а также немного быстрее строятся.
В статье Storing UUID Values in MySQL давно проводилось сравнение различных вариантов хранения. С выводами статьи относительно разницы в размере занимаемых данных на диске и в памяти не поспорить — действительно можно сэкономить много места, особенно если идентификатор сущности прокидывается в много других таблиц. Похожую историю я уже рассматривал в примере №2.
А вот что касается скорости вставки, давайте посмотрим — у меня не получилось воспроизвести результаты этой статьи.
Замерим скорость вставки для трех таблиц:
Таблица A: первичный ключ BIGINT() с AUTO_INCREMENT
Таблица B: первичный ключ CHAR(36)
Таблица С: первичный ключ BINARY(16) - при вставке и выборке используем функции UUID_TO_BIN()/BIN_TO_UUID() (эти функции доступны только начиная с mysql 8.0)
Как видно по графикам, заметной разницы между таблицами B и С нет. Да и таблица A со старым-добрым AUTO_INCREMENT выигрывает не сильно и только после 400 вставок по 25000 (когда таблица перерастает 10 млн записей). По моему мнению мучатся с BINARY(16) только для ускорения времени вставки — действительно «экономия на спичках».
Больше о работе с BINARY(16) можно почитать тут.
Пример №5
Известно, что запрос с сортировкой и OFFSET начинает работать медленнее с увеличением OFFSET. Если offset стоит на миллион, то базе данных придется вычитать из диска миллион строк плюс лимит, а потом отдать только лимит. Поэтому такие запросы начинают выполнятся по несколько секунд.
Для следующей структуры:
CREATE TABLE `customer` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
`store_id` int NOT NULL,
. . .
CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `first_name` (`first_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;Если делать запрос с большим OFFSET, например, таким образом, то запрос будет выполняться, как минимум 10 секунд, а то и все 20:
SELECT * FROM `customer` ORDER BY `first_name` LIMIT 1000000, 10;Но если сделать вместо одного запроса два, то можно уложиться в несколько миллисекунд:
SELECT `id` FROM `customer` ORDER BY `first_name` LIMIT 1000000, 10;
SELECT * FROM `customer` WHERE `id` IN (...);Здесь не стоит забывать, что WHERE IN изменит порядок записей, поэтому его придется запоминать из первого запроса.
Структуры запросов с сортировкой очень похожи, отличается только то, что во втором случае мы вытаскиваем только id, которые уже содержатся в отсортированном индексе. Поэтому это работает гораздо быстрее — mysql не нужно обращаться к диску для первого запроса. Так же стоит помнить, что mysql очень хорошо работает с запросами WHERE field IN (...) за счет наличия кластерного индекса.
На эту тему есть очень хорошая старая статья, которую можно почитать здесь.
Пример №6
Использование NULL для обозначения отсутствия значения может немного сохранить место, особенно для числовых типов полей, но и увеличить время выборки, если поле необходимо индексировать.
Для наглядности рассмотрим на примере.
Таблица customer из Примера №5 c 10 млн записей будет весить 973 MiB, если половина записей одного store_id будет 0. Если же сделать поле store_id IS NULL и выставить половину строк store_id=NULL вместо 0, то размер таблицы будет 953 MiB. Крошечная разница, но давайте посмотрим на разницу в производительности по выборке.
Напомню, что в наших таблицах есть индекс на поле store_id.
В некоторые источниках утверждается (например, здесь и здесь), что наличие NULL в индексированном поле увеличивает время выборки по такому индексу.
Посмотрим разницу выполнения запроса:
SELECT id FROM customer WHERE store_id=FLOOR(RAND()*50000) LIMIT 1;В случае, когда мы использовали NOT NULL для store_id выполнение 500 таких запросов (в 10 потоков) в среднем занимает 5 секунд.
В случае же, когда store_id было NULLable и половина строк была помечена как NULL — те же запросы выполняются в среднем 10 секунд.
При этом explain для двух таблиц будет выглядеть почти одинаково:
Единственная разница в размере индекса — для NULLable полей он на один байт больше.
Получается разница в скорости выборки в 2 раза. Но здесь надо понимать, что NULLable поля редко имеет смысл индексировать из-за низкой селективности индекса. Но, если это ваш случай, имейте ввиду — лучше немного больше занять места на диске, но ускорить выборку за счет значения по умолчанию вместо NULL.
Заключение
Проектирование базы данных — это вечная борьба за оптимизацию и, соответственно, вечный компромисс между скоростью работы, занимаемым местом. И чем больше базы данных, тем тяжелее их оптимизировать.
Приведенные примеры — ни в коем случае не панацея, а лишь малая часть практик, следование которым поможет «сэкономить как можно больше спичек» в самом начале пути и, как результат, позволит повысить стабильность баз данных, упростить их масштабирование и сократить расходы бизнеса на организацию хранилищ.
А вы придерживаетесь этих рекомендаций? Что можете предложить еще?
Комментарии (18)
Akina
00.00.0000 00:00+4Примеры №1 и №2 следовало объединить под единым лозунгом "Выбирайте правильные типы данных". Или уж тогда добавляйте советы типа "Используйте DATE вместо DATETIME, если Вам не нужна компонента времени", "храните JSON не в TEXT, а в JSON" и т.п. Ну и очень хочется спросить - а что, кто-то всё ещё использует строковые поля фиксированного размера для данных динамической длины?
Пример №3 совершенно не упоминает о действительно важных свойствах, недоступных Memory, а именно о внешних ключах и полнотексте. И да, транзакции и индексы Memory тоже не поддерживает, Archive в этом не одинок. Кроме того, насколько велика разница, если InnoDB таблица и индексы полностью прогреты (кэшированы)? Если разница по-прежнему троекратна (а она и будет приблизительно таковой - впрочем, её легко уменьшить до где-то двукратной), то причина этой разницы связана с чем угодно, но только не с тем, что Memory-таблица лежит в ОЗУ.
Пример №4 очень странен. Почему Вы решили сравнивать именно запись? Записывается-то значение один раз, а вот читать его будут неоднократно, может, искать и даже сортировать по нему. Вы сравните связывание и поиск по описываемым типам данных - сразу станет видно, что никакой "экономии на спичках" нет и в помине, и разница вполне себе ощущаема.
Пример №5 - не понимаю, зачем ДВА запроса, когда всё прекрасно объединяется в один. Пометка "что WHERE IN изменит порядок записей, поэтому его придется запоминать из первого запроса" - просто изумляет.. проблема с порядком не оттого, что "WHERE IN изменит порядок записей", а потому что Вы в процессе оптимизации тупо забыли добавить ORDER BY во второй запрос. Также следовало до конца пояснить, почему "mysql не нужно обращаться к диску для первого запроса" - для неспециалиста информация, откуда этот запрос выуживает значение id, совершенно неочевидна.
Пример №6 "Использование NULL для обозначения отсутствия значения может немного сохранить место" ... Ась? А Вы знаете, что NULLable поле требует больше места, чем NOT NULL? Какая же тут экономия - эффект будет с точностью до наоборот. Впрочем, разница в один байт как правило вообще не сказывается на потребности в дисковом пространстве, а для именно использованной (из примера №5) таблицы - не сказывается вообще. Как и откуда Вы умудрились получить 20 мегабайт экономии - ну очень большая загадка. Вероятнее всего, Вы просто неточно воспроизвели порядок заполнения таблицы.
Уж извините, но прочитав, как Вы представились в начале статьи, я не ожидал такого уровня верхоглядства.
John_Carter Автор
00.00.0000 00:00Спасибо за столь детальный анализ! Со многим согласен.
Примеры 1-4: Вижу только как вы сами написали бы их и что упомянули бы, кажется, это окей - спасибо за дополнение ????
Пример 5: Расскажите, пожалуйста, как возможно это сделать ОДНИМ запросом?
Пример 6: Тут, похоже, вы меня не так поняли - место в таблице с NULLable полями экономится только в сравнении с указанием DEFAULT значения (например 0 или '') вместо NULL
Akina
00.00.0000 00:00+2как возможно это сделать ОДНИМ запросом?
SELECT *FROM `customer`WHERE `id` IN (SELECT `id`FROM `customer`ORDER BY `first_name` LIMIT 1000000, 10)ORDER BY `first_name`;место в таблице с NULLable полями экономится только в сравнении с указанием DEFAULT значения (например 0 или '') вместо NULL
Теперь вообще не понял. DEFAULT-значение поля - это вообще свойство поля, присутствующее исключительно в определении структуры таблицы и в принципе не влияющее на файл данных. Можно взять таблицу с DEFAULT NULL и без проблем скопировать её TABLESPACE в таблицу той же структуры, но с DEFAULT 0 или вообще без DEFAULT...
Насчёт того, что "NULLable поля редко имеет смысл индексировать из-за низкой селективности индекса" - это всего лишь частный случай... Давно и прочно эмпирически установлено, что при селективности более 5-7% использование индекса менее эффективно, чем fullscan. Что же до похожести планов - так он похож просто потому, что индекс используется не как сортированный набор, а как покрывающий. То есть на самом деле мы имеем всё тот же fullscan, но не по данным, а по индексу. Ну и, понятно, имеем два сравнения вместо одного (точнее, статистически полтора).
Кстати, попробуйте тот же запрос, но в версии
SELECT idFROM customerWHERE store_id IS NOT NULLAND store_id=FLOOR(RAND()*50000) LIMIT 1;Интересно, как сервер построит план выполнения на тех же структуре и данных, и какое получится время выполнения...
edo1h
00.00.0000 00:00`id` IN (SELECT… LIMIT 1000000, 10)
А вы пробовали? Мне mariadb написала, что не умеет так. Впрочем, через join всё получилось.
P.S. Конечно, простота планировщика запросов mysql, не сумевшего построить приличный план выполнения для
select * … limit 100000, 10, вызывает некоторое недоумение.Akina
00.00.0000 00:00+1Мне mariadb написала, что не умеет так.
Я в шоке...MySQL и MariaDB - оно, конечно, родственники, но сейчас родственники настолько далёкие, что переносить сведения по одной СУБД на другую можно не всегда, а когда можно, то делать это надо ну с крайней осторожностью.
К тому же, если речь не идёт об ну очень древней версии СУБД, вместо подзапроса можно использовать и CTE:
WITH cte AS (SELECT `id`FROM `customer`ORDER BY `first_name` LIMIT 1000000, 10)SELECT customer.*FROM `customer`JOIN cte USING (id)ORDER BY `first_name`;edo1h
00.00.0000 00:00+2а на какой версии mysql вы проверяете?
в текущей документации не вижу, чтобы к MySQL does not support LIMIT in subqueries for certain subquery operators были приписки, что начиная с какой-то версии это ограничение не действует.что же до вашего запроса с cte, там join вместо in, к нему это ограничение неприменимо.
вдогонку: вы будет смеяться, но запрос в виде
select * from users u where id in (select id from (select id from users order by email LIMIT 1000000, 10) a);mariadb приняла.
в виде
with cte as (select id from users order by email LIMIT 1000000, 10) select * from users u where id in (select id from cte);тоже приняла, но после предыдущего запроса это уже не удивляет.
Akina
00.00.0000 00:00+1MySQL 8.0, LIMIT в CTE: https://dbfiddle.uk/wvnZaUIm
Этот код после соотв. корректировки работает и в MariaDB 10.3: https://dbfiddle.uk/7LuqxcqN
А вот напрямую LIMIT в подзапросе WHERE IN - да, не поддерживается ни тут, ни там. И вряд ли будет поддерживаться - из-за особенностей обработки LIMIT. Кстати, изрядные тормоза при большом offset связаны совершенно с той же причиной.
там join вместо in, к нему это ограничение неприменимо
id уникально и не содержит NULL (первичный ключ как-никак), так что не понимаю, почему Вы считаете, что неприменимо.
edo1h
00.00.0000 00:00почему Вы считаете, что неприменимо
вы, видимо, меня не так поняли. я говорил, что ограничение «limit не может быть использован в подзапросах» не касается join.
NickyX3
00.00.0000 00:00кто-то всё ещё использует строковые поля фиксированного размера для данных динамической длины?
Почему бы и нет? Если у таблицы FIXED ROW FORMAT они и так будут фиксированной длины, что может несколько ускорить выборки в некоторых случаях. Есть достаточно много кейсов, когда хранятся не числовые данные ограниченной длины, да взять какой-нить СНИЛС к примеру
John_Carter Автор
00.00.0000 00:00+
а еще поле фиксированной длинны не покинет кластерный индекс, а вот VARCHAR будет заменено ссылкой на диск, в случае если размер строки начнет превышать выделенный лимит
я пытался это воспроизвести - но не получилось продемонстрировать разницу
подробнее тут: https://dev.mysql.com/blog-archive/externally-stored-fields-in-innodb/NickyX3
00.00.0000 00:00Ну я в основном про Aria Engine в MariaDB или окончательно забытый MyISAM. В FIXED конечно не сунуть динамические поля типа VARCHAR или TEXT, но часто это и не надо. В плюсах, лично у нас, меньшая фрагментация (вернее даже отсутствие таковой, мы не удаляем записи, только помечаем).
как-то так и живем Текст самих сообщений в отдельной таблице в данном случае, тут только всякая фиксированная мета-инфа типа sender/receiver/timestamp/flags etc
FanatPHP
00.00.0000 00:00Это какие-то высосанные из пальца примеры.
Ускорение: за чет чего?
FIXED ROW FORMAT: за счет чего? СНИЛС как бы не один в таблице хранится, там еще какой-нибудь емейл будет.NickyX3
00.00.0000 00:00Выше пример таблицы в 34 миллиона записей, там вообще нет CHAR.
СНИЛС приведен как пример строковых данных фиксированной длины. По поводу Fixed мы уже обсуждали с Вами вроде, каждая запись имеет фикс размер в таблице, у нее четкое смещение внутри. Причем во-первых одно, во-вторых легко вычисляемое. Отсюда и скорость, ибо не надо из разных мест файла в случае фрагментации искать где там куски этих полей VARCHAR/TEXT по файлу раскиданы. Причем у каждого такого поля может быть лютая фрагментация
FanatPHP
00.00.0000 00:00"Искать" в любом случае ничего не надо. Все смещения есть в первичном индексе. Не менее "четкие". Если у вас выборка идет по индексу, то разницы вы не заметите. Если без индекса, то никакой фикс вас не спасет.
Вы заметили, что ваши примеры носят чисто теоретический характер? Не "у нас вот фиксированный формат дал прирост", а "ну вот снилс к примеру".
Плюс вы как будто не слышите собеседника. Я вам пишу, что снилс отдельно в воздухе не висит, он будет в таблице с данными переменной длины и никакой погоды не сделает. А вы мне просто повторяете по второму разу, "это пример". Это выдуманный из пальца пример. О чем и был мой исходный комментарий.
NickyX3
00.00.0000 00:00Блин, Вы так любите теории какие-то придумывать, выше глазками взгляните. 34 ляма, Aria, fixed format (ну да, там нет char полей). И у нас это дает то, что все это работает в виртуалке на 4 гигах оперативы. И индексы там конечно есть. Но! У вас либо в индексе одно смещение, по которому вы можете достать запись целиком всегда (да его даже хранить не надо - оно вычисляется, утрировано, строка*размер ), либо у вас в индексе или где-там еще 20 смещений (тут строка, тут кусок этого поля, тут еще кусок, тут начало еще одного, а вооон там еще его кусок). Что по-вашему быстрее - сразу достать всю строку или побегать по смещениям, подергать кусочки, склеить их и выдать?
Я вам пишу, что снилс отдельно в воздухе не висит, он будет в таблице с данными переменной длины и никакой погоды не сделает
Да что вы к СНИЛС привязались? Наличие в таблицах полей переменной длины это вопрос нормализации ваших конкретных данных. Если уж взять какую-то гипотетическую задачу хранения персональных данных гражданина, то поля переменной длины, не связанные с государственными идентификаторами можно и нужно хранить отдельно. А те самые идентификаторы (sic!) имеют фиксированные длины (СНИЛС, ИНН, номер паспорта, прав - все фиксированые)
FanatPHP
00.00.0000 00:00Я вспомнил да. Вы тот мальчик, который нашел на улице функцию хэширования, и с тех пор всё хэширует — пароли, логины, емейлы, телефоны и дни рождения.
NickyX3
00.00.0000 00:00Если вы меня словом "мальчик" оскорбить пытались - ну я явно не девочка, мне 48 и борода :) Ничего плохого не видим с коллегами в хешировании или шифровании ни паролей, ни е-мейлов, ни телефонов. Есть плюсы, есть минусы. Все зависит от задачи.
Судя по Вашим статьям на Хабре - Вы сильно любите тестировать разные гипотезы - можете протестировать в том числе и разницу между FIXED/DYNAMIC/PAGE. Только давайте возьмем несколько "движков" InnoDB, Aria и что Вам еще захочется.
FanatPHP
Если честно, то эта статья вообще не похожа на написанную разработчиком.
Куда больше похоже что она написана либо контент-менеджером, либо интерном.
Потому что разработчики в статьях пишут не оторванный от реальности список советов из интернета, а делятся примерами из собственного опыта.
У вас в компании СберЗдоровье реально были такие гении, которые использовали char в таблицах, и вы, на основании приведенных исследований, переделывали эти поля на varchar?
У вас реально есть запросы с миллионным оффсетом?
У вас реально используются memory таблицы?
Единственную отсылку к реальному опыту я вижу только в одном пункте.
Вы точно разработчик, а не студент, нанятый компанией СберМедицина продвигать ее блог на Хабре? Давайте вы тогда напишете что-нибудь интересное для других разработчиков, а не отписку для рекламы блога, в стиле "10 способов ускорить ваше похапе"?