- PVSM.RU - https://www.pvsm.ru -
Прочитав статью [1] вспомнил об одном очень специфичном заказчике и системе по сбору статистики по событиям. На дворе 21 век и я знаю о наличии ClickHouse [2], но вот заказчик не хочет менять БД (причина мне непонятна и не известна, религия, наверное, не позволяет), да и пусть будет так, я его несколько раз предупреждал о последствиях. Когда станет медленно совсем, осознает проблему.
Но речь не об этом. В общем, прочитав статью я вспомнил об этом проекте и решил попробовать интегрировать партицирование в таблицу с 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)
Как было описано в статье, что я привел изначально, плюсы такого деления очевидны:
Далее нужно оптимизировать запросы, ведь при неграмотно составленном запросе, MySQL будет проходить по всем партициям, что добавляет дополнительные расходы, а это не очень хорошо.
Почитав статью [3] решение по оптимизации также напрашивается само собой: нам в запросе нужно использовать поиск через 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
А добились мы следующего:
Автор: Виталий
Источник [4]
Сайт-источник PVSM.RU: https://www.pvsm.ru
Путь до страницы источника: https://www.pvsm.ru/mysql/269785
Ссылки в тексте:
[1] статью: https://habrahabr.ru/post/159131/
[2] ClickHouse: https://clickhouse.yandex/docs/ru/single/
[3] статью: https://dev.mysql.com/doc/refman/5.7/en/partitioning-pruning.html
[4] Источник: https://habrahabr.ru/post/343824/?utm_source=habrahabr&utm_medium=rss&utm_campaign=sandbox
Нажмите здесь для печати.