Партицирование и боль MySQL
На днях пришлось делать партицирование. В таблицу каждый день будет добавляться миллион записей и ожидается, что эти записи будут браться для предоставления различных отчетов и графиков.
Сразу скажу, что делал это не первый раз, до этого успешно делал партицирование у сайта на битрикс примерно вот таким образом:
Шаг 1. Убираем AUTO INCREMENT из таблицы b_iblock_element.
ALTER TABLE b_iblock_element MODIFY ID INT(11) NOT NULL
Шаг 2. Удаляем PRIMARY key из таблицы.
ALTER TABLE b_iblock_element DROP PRIMARY KEY
Шаг 3. Создаем новый PRIMARY KEY, который будет содержать прошлый ключ и IBLOCK_ID, по которому идет разбиение на partition`ы.
ALTER TABLE b_iblock_element ADD CONSTRAINT id_iblock_id PRIMARY KEY (ID,IBLOCK_ID)
Шаг 4. Возвращаем AUTO INCREMENT.
ALTER TABLE b_iblock_element MODIFY ID INT(11) NOT NULL AUTO_INCREMENT
Шаг 5. Наконец то делаем разбиением на 10 частей.
ALTER TABLE b_iblock_element PARTITION BY HASH(IBLOCK_ID) PARTITIONS 10;
Все довольно просто. Функция по которой идет разбиение может содержать ключи, но все эти ключи должны быть в PRIMARY KEY.
Теперь же мне предстояло разбить другую таблицу, и хотелось бы ее разбить сразу по 2 полям: по типу и дате. Причем дату хотелось разбить по месяцам и данные хранить не больше года.
Шаг 1. Первое с чем я столкнулся, это то, что пришлось удалить все foreign keys. Mysql с ними при партицировании не работает.
ALTER TABLE table_name DROP CONSTRAINT fk_key_name
Шаг 2. Пришлось поменять поле timestamp на datetime. Функций для datetime было в избытке.
ALTER TABLE table_name CHANGE `date` `date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;
Шаг 3. Пришлось добавить в PRIMARY KEY поля даты и типа.
Шаг 4. Я решил использовать subpartitions для разбиения по двум полям
ALTER TABLE table_data PARTITION BY LIST( MONTH(`date`) )
SUBPARTITION BY HASH(`type_id`) SUBPARTITIONS 10
(
PARTITION p1 VALUES IN (1),
PARTITION p2 VALUES IN (2),
PARTITION p3 VALUES IN (3),
PARTITION p4 VALUES IN (4),
PARTITION p5 VALUES IN (5),
PARTITION p6 VALUES IN (6),
PARTITION p7 VALUES IN (7),
PARTITION p8 VALUES IN (8),
PARTITION p9 VALUES IN (9),
PARTITION p10 VALUES IN (10),
PARTITION p11 VALUES IN (11),
PARTITION p12 VALUES IN (12)
);
Но хоть и MySQL поддерживает функцию MONTH для партицирования и я использую LIST/RANGE вместе с HASH/KEY, то есть MySQL subpartitions поддерживает только на этом уровне, у меня ничего не получилось.
Команда explain partitions запроса показала, что когда я выбираю интервал по дате, используются все партишены. Гугл сказал мне, что я не могу использовать функцию MONTH, а только функцию TO_DAYS, YEAR и TO_SECONDS. Пришлось все таки делать партиции статичными:
ALTER TABLE table_data PARTITION BY RANGE(to_days(`date`))
SUBPARTITION BY HASH(`type_id`) SUBPARTITIONS 10
(
PARTITION p01 VALUES LESS THAN (to_days('2015-10-01')),
PARTITION p02 VALUES LESS THAN (to_days('2015-11-01')),
PARTITION p03 VALUES LESS THAN (to_days('2015-12-01')),
PARTITION p04 VALUES LESS THAN (to_days('2016-01-01')),
PARTITION p05 VALUES LESS THAN (to_days('2016-02-01')),
PARTITION p06 VALUES LESS THAN (to_days('2016-03-01')),
PARTITION p07 VALUES LESS THAN (to_days('2016-04-01')),
PARTITION p08 VALUES LESS THAN (to_days('2016-05-01')),
PARTITION p09 VALUES LESS THAN (to_days('2016-06-01')),
PARTITION p10 VALUES LESS THAN (to_days('2016-07-01')),
PARTITION p11 VALUES LESS THAN (to_days('2016-08-01')),
PARTITION p12 VALUES LESS THAN (to_days('2016-09-01')),
PARTITION p13 VALUES LESS THAN (to_days('2016-10-01')),
PARTITION p14 VALUES LESS THAN (to_days('2016-11-01')),
PARTITION p15 VALUES LESS THAN (to_days('2016-12-01')),
PARTITION p16 VALUES LESS THAN (to_days('2017-01-01')),
PARTITION p18 VALUES LESS THAN (to_days('2017-02-01')),
PARTITION p19 VALUES LESS THAN (to_days('2017-03-01')),
PARTITION p20 VALUES LESS THAN (to_days('2017-04-01')),
PARTITION pmaxval VALUES LESS THAN MAXVALUE
);
Команда explain partitions наконец-то показала, что не станет использовать кучу таблиц. Хочется надеяться, что это будет работать, и спасибо MySQL за принесенную боль.
