История одной оптимизации MySQL

Речь пойдет об оптимизации в MySQL базе данных.

Это случилось, когда мы делали систему для email рассылок. Наша система должна была высылать десятки млн. писем в день. Отправка письма — задача не из простых, хотя выглядит все довольно примитивно:

1. Собрать письмо из html креатива, подставить персонализированные данные.
2. Добавить пиксель просмотра письма, заменить все ссылки в письме на свои — чтобы отслеживать клики.
3. Проверить перед отправкой, что email не находится в black list-е.
4. Отправить письмо на определенный пул.

Расскажу подробнее о втором пункте:
Микросервис mail-builder занимается подготовкой письма к отправке:

• находит все ссылки в письме;
• для каждой ссылки генерируется уникальный 32-ух символьный uuid;
• заменяет исходную ссылку на новую и сохраняет данные в базу.

Таким образом, все исходные ссылки будут заменены на uuid-ы, а домен поменяется на наш. При GET запросе по этой ссылке мы проксируем оригинальную картинку или редиректим на исходную ссылку. Сохранение происходит в базу данных MySQL, мы сохраняем сгенерированный uuid вместе с оригинальной ссылкой и с некоторой мета информацией (email пользователя, id рассылки и другими данными). Денормализация помогает нам в 1 запрос получить все необходимые данные для сохранения статистики, или старта какой-то триггерной цепочки.

Проблема №1

Генерация uuid-a у нас зависела от timestamp.

Так как рассылки обычно происходят в определенный период времени и запущено много инстансов микросервиса по сборке письма, получалось, что часть uuid-ов была очень схожими. Это давало низкую селективность. UPD: т.к данные получались схожие, работа с би-деревом была не очень эффективная.

Решили эту проблему использованием модуля uuid в питоне, где нет зависимости от времени.
Такая неявная вещь уменьшала скорость работы индексов.

Как происходит хранение?

Структура таблицы была следующая:

CREATE TABLE IF NOT EXISTS `Messages` (
`UUID` varchar(32) NOT NULL,
`Message` json NOT NULL,
`Inserted` DATE NOT NULL,
PRIMARY KEY (`UUID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

На момент создания все выглядело логично:
UUID — primary key, а также является кластерным индексом. Когда мы делаем выборку по этому полю, то мы просто выбираем запись, ведь все значения хранятся тут же. Это было обдуманным решением. Подробнее о кластерном индексе.

Все было замечательно, пока таблица не разрослась.

Проблема №2

Если прочитать подробнее про кластерный индекс, можно узнать о таком нюансе:

При добавлении новой строки в таблицу, она дописывается не в конец файла, не в конец плоского списка, а в нужную ветку древовидной структуры, соответствующую ей по сортировке.

Таким образом, при росте нагрузки время вставки росло.

Решением было использовать другую структуру таблицы.

CREATE TABLE IF NOT EXISTS `Messages` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`UUID` varchar(32) NOT NULL,
`Message` json NOT NULL,
`Inserted` DATE NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `UUID` (`UUID`, `Inserted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Так как теперь primary key автоинкрементный, а в mysql хранится кеш последнего места вставки, теперь вставка всегда происходит в конец, т.е Innodb оптимизирован под запись последовательно-возрастающих значений.

Подробности этой оптимизации я находил в исходниках у postgres. В mysql реализована очень похожая оптимизация.
Пришлось, конечно, добавить уникальный ключ, чтобы не было коллизий, но скорость вставки мы увеличили.

С еще большим ростом базы, мы задумались над тем, чтобы удалять старые данные. Использовать DELETE по полю Inserted абсолютно не оптимально — это очень долго, а также место не будет освобождено пока мы не выполним команду optimize table. Кстати, эта операция полностью блокирует таблицу — это нам совсем не подходило.

Поэтому мы решили разбить нашу таблицу на партиции.
1 день — 1 партиция, старые дропаются автоматически, когда приходит время.

Проблема №3

Мы получили возможность удалять старые данные, но мы не получили возможности выбирать из нужной партиции, т.к при select`e мы указываем только uuid, mysql не знает в какой партиции нам это искать и ищет во всех.

Решение родилось из Проблемы №1 — добавить timestamp в сгенерированный uuid. Только в этот раз мы поступили немного по-другому: вставили timestamp в случайное место строки, не в начало и не в конец; перед ним и после добавили символ тире, чтобы его можно было получить регулярным выражением.

С помощью этой оптимизации мы смогли получать дату, когда uuid был сгенерирован и уже сделать select с указанием конкретного значения поля Inserted. Теперь мы читаем данные сразу из нужной нам партиции.

Так же, благодаря таким вещам, как ROW_FORMAT=COMPRESSED и сменой кодировки на latin1, мы сэкономили еще больше места на жестком диске.