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

Сразу скажу, что делал это не первый раз, до этого успешно делал партицирование у сайта на битрикс примерно вот таким образом:

Шаг 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. На timestamp MySQL ругался
ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

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 за принесенную боль.

Комментарии (9)


  1. 4knowledge
    27.10.2015 17:54

    То есть в итоге все зря и откатить назад — снова боль и страдание?


    1. stagnantice
      27.10.2015 18:39

      Просто придется пересобирать партиции раз в год. Можно задачу в крон поставить)


  1. summerwind
    27.10.2015 19:58

    Шаг 2. Пришлось поменять поле timestamp на datetime.

    А по какой причине?


    1. stagnantice
      27.10.2015 20:16

      Из-за следующей ошибки:

      ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed


  1. mickvav
    27.10.2015 21:52

    Ну, у меня заббикс на похожих партициях (только по дате) вполне себе живёт, так что должно работать.


  1. aspirineilia
    28.10.2015 09:27

    У нас в проекте для партиций и поиска год и месяц вынесены в отдельные поля и партиции собственно по годам с субпартициями по месяцам:

    PARTITION BY RANGE (Year)
    SUBPARTITION BY LINEAR HASH (Month)
    SUBPARTITIONS 12
    (PARTITION p0 VALUES LESS THAN (2010) ENGINE = InnoDB,
     PARTITION p1 VALUES LESS THAN (2011) ENGINE = InnoDB,
     PARTITION p2 VALUES LESS THAN (2012) ENGINE = InnoDB,
     PARTITION p3 VALUES LESS THAN (2013) ENGINE = InnoDB,
     PARTITION p4 VALUES LESS THAN (2014) ENGINE = InnoDB,
     PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB,
     PARTITION p6 VALUES LESS THAN (2016) ENGINE = InnoDB,
     PARTITION p7 VALUES LESS THAN (2017) ENGINE = InnoDB,
     PARTITION p8 VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
    

    Думаю мускулу явно проще разбивать и выбирать данные по ключу нежели делать расчёт по дате или я ошибаюсь?


    1. stagnantice
      28.10.2015 09:46

      Думаю легче, но выборка все равно идет в пределах одной таблицы, если будет плохо работать, то наверное так и придется создавать отдельные поля. В этом случае получится дублирование данных в таблице, и придется заносить туда YEAR, и MONTH от даты, и следить чтобы они друг другу соответствовали. В вашем случае можно было использовать функцию YEAR, а вынести только месяц, но наверное уже были не критично.


      1. aspirineilia
        28.10.2015 09:55

        В нашем случае это ежемесячная отчётность и поля с месяцем и годом не избыточные, а основные. Дата внесения есть, но данные заносят в конце месяца-начале следующего и на неё нельзя опираться.


        1. stagnantice
          28.10.2015 10:26

          А, ну в моем случае выборка идет по датам, а не по месяцу и году.