MySQL и партицирование

Прочитав статью вспомнил об одном очень специфичном заказчике и системе по сбору статистики по событиям. На дворе 21 век и я знаю о наличии ClickHouse, но вот заказчик не хочет менять БД (причина мне непонятна и не известна, религия, наверное, не позволяет), да и пусть будет так, я его несколько раз предупреждал о последствиях. Когда станет медленно совсем, осознает проблему.

Суть проблемы

Но речь не об этом. В общем, прочитав статью я вспомнил об этом проекте и решил попробовать интегрировать партицирование в таблицу с 7 000 000 записями. На prod стэнде там уже намного больше записей.

Также в проекте использовался шардинг, который, по большому счету там лишний. Нет смысла в такого рода системе делать шардинг да еще и по времени (на каждый месяц своя таблица).

В общем вариантов, на самом деле было немного, как делить данные, и был выбран самый очевидный: добавить в таблицу колонку dYm (date Year month), так как в таблицу и так уже пишется время, то сделать этого не составило труда. Правда с определенной оговоркой, так как на сервере мало памяти, то пришлось пересоздать таблицу и импортировать данные в новую таблицу, предварительно добавив нужное поле.

Создание таблицы с партициями (часть полей убрал):

CREATE TABLE `event_list_test` (
  `dYd` int(6) unsigned NOT NULL COMMENT 'год и месяц',
  `hash` varchar(13) NOT NULL COMMENT 'hash',
  `time` int(10) unsigned NOT NULL COMMENT 'timestamp',
  PRIMARY KEY (`time`,`dYd`,`hash`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY LIST (dYd)
(PARTITION p201703 VALUES IN (201703) ENGINE = InnoDB,
 PARTITION p201704 VALUES IN (201704) ENGINE = InnoDB,
 PARTITION p201705 VALUES IN (201705) ENGINE = InnoDB,
 PARTITION p201706 VALUES IN (201706) ENGINE = InnoDB,
 PARTITION p201707 VALUES IN (201707) ENGINE = InnoDB,
 PARTITION p201708 VALUES IN (201708) ENGINE = InnoDB,
 PARTITION p201709 VALUES IN (201709) ENGINE = InnoDB,
 PARTITION p201710 VALUES IN (201710) ENGINE = InnoDB,
 PARTITION p201711 VALUES IN (201711) ENGINE = InnoDB,
 PARTITION p201712 VALUES IN (201712) ENGINE = InnoDB)

Как было описано в статье, что я привел изначально, плюсы такого деления очевидны:

1. простота администрирования, так как в таблицу с 7 000 000 строками при 1Гб памяти колонку уже не добавить, а индекс и подавно
2. изначально подобного рода таблицы шардировались, но очевидный минус — это написание sql запросов. Часто приходилось делать запросы на несколько месяцев, а если нужна агрегация — то тут уж совсем беда.
3. добавить партицию в таблицу проще простого (особенно если поставить в крон задачу с отсылкой письма в телеграм или на почту, тут кому как удобнее)

Далее нужно оптимизировать запросы, ведь при неграмотно составленном запросе, MySQL будет проходить по всем партициям, что добавляет дополнительные расходы, а это не очень хорошо.

Почитав статью решение по оптимизации также напрашивается само собой: нам в запросе нужно использовать поиск через between по уникальному ключу. В итоге, если в приложении все запросы заменить на такие:

SELECT `time` FROM `event_list_test` WHERE (`time` BETWEEN 1505385901 AND 1506934784) AND (`dYd` BETWEEN 201709 AND 201710) LIMIT 10

то мы получим очень хороший explain:

SIMPLE event_list_test p201709,p201710 range PRIMARY,time PRIMARY 8 NULL 145875 11.11 Using where


Чего же мы добились?

А добились мы следующего:

1. исчез ненужный шардинг данных
2. очень легко можно строить запросы на получение данных (с шардингом было много проблем)
3. очень просто администрировать таблицы (вставка партиции, удаление партиции, вставка данные, выборка данных, изменение таблицы, работа с индексами)
4. и как следствие — упрощение приложения в разы.

Автор: Виталий

Источник