Mysql PARTITION BY YEAR(date) / MONTH(date) / DAYOFWEEK(date)

Зачастую мне приходится иметь дело с таблицами которые содержат редко или даже никогда ни обновляемые данные. Хорошим примером таких данных являются различные логи. Некоторые таблицы регулярно очищаются от устаревших данных, а в некоторых приходится хранить записи «вечно». Поэтому такие таблицы «пухнут» и работа с ними становится тяжелой операцией для всей системы.

Чтобы уменьшить нагрузку на диск и ФС, придумали partitioning, по простому — секционирование. Файл с данными таблицы разрезается по какому-то условию на несколько не больших файлов — партиций. Для случая с логами разумно партиционировать таблицы по полю, содержащему даты события. Часто бывает разумно резать таблицу на partition по году по месяцу или по дням месяца/недели.

Что-то подсказывает что резать придется по полю timestamp.


Сделаем табличку:

CREATE TABLE `foo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `name` varchar(30) DEFAULT NULL,
  `email` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Но как быть если надо например таблицу с логами разложить по дням месяца? То есть в таблицу пишется что-то, что хранится месяц или два, а потом удаляется. Как быть если мы хотим порезать так:

 ALTER TABLE foo PARTITION BY RANGE (YEAR(date_added))
(
 PARTITION p2011 VALUES LESS THAN (2012) ,
 PARTITION p2012 VALUES LESS THAN (2013) ,
 PARTITION p2013 VALUES LESS THAN (2014) 
);

Получаем:

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

объяснения этому вот какое: «TIMESTAMP is internally converted to the local sessions timezone.»

Ладно:

SELECT UNIX_TIMESTAMP('2012-01-01 00:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2012-01-01 00:00:00') |
+---------------------------------------+
|                            1325361600 |
+---------------------------------------+


SELECT UNIX_TIMESTAMP('2013-01-01 00:00:00'); 
+---------------------------------------+
| UNIX_TIMESTAMP('2013-01-01 00:00:00') |
+---------------------------------------+
|                            1356984000 |
+---------------------------------------+


SELECT UNIX_TIMESTAMP('2014-01-01 00:00:00'); 
+---------------------------------------+
| UNIX_TIMESTAMP('2014-01-01 00:00:00') |
+---------------------------------------+
|                            1388520000 |
+---------------------------------------+

Теперь:

 ALTER TABLE foo PARTITION BY RANGE (UNIX_TIMESTAMP(date_added))
(
 PARTITION p2011 VALUES LESS THAN (1325361600) ,
 PARTITION p2012 VALUES LESS THAN (1356984000) ,
 PARTITION p2013 VALUES LESS THAN (1388520000) ,
 PARTITION pMAXVALUE VALUES LESS THAN (MAXVALUE)
);

Вот, теперь получаем:

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

Это лечится:

ALTER table foo  DROP PRIMARY KEY, add PRIMARY KEY (`id`,`date_added`);

И еще раз:

 ALTER TABLE foo PARTITION BY RANGE (UNIX_TIMESTAMP(date_added))
(
 PARTITION p2011 VALUES LESS THAN (1325361600) ,
 PARTITION p2012 VALUES LESS THAN (1356984000) ,
 PARTITION p2013 VALUES LESS THAN (1388520000) ,
 PARTITION pMAXVALUE VALUES LESS THAN (MAXVALUE)
);

Все ок.

Получаем:

CREATE TABLE `foo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `name` varchar(30) DEFAULT NULL,
  `email` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`,`date_added`)
) ENGINE=InnoDB 

PARTITION BY RANGE (UNIX_TIMESTAMP(date_added))
(PARTITION p2011 VALUES LESS THAN (1325361600) ENGINE = InnoDB,
 PARTITION p2012 VALUES LESS THAN (1356984000) ENGINE = InnoDB,
 PARTITION p2013 VALUES LESS THAN (1388520000) ENGINE = InnoDB,
 PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE ENGINE = InnoDB
);

Отлично!

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

Тоесть как быть если мы хотим порезать так:

		PARTITION BY RANGE (MONTH(date))

Или так:

		PARTITION BY RANGE (DAY(date_add))

Поле типа timestamp не подходит.

Гугление говорит что надо юзать datetime и точка.

ок, создадим таблицу:

CREATE TABLE `foo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_added` datetime  DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  `email` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`,`date_added`)
) ENGINE=InnoDB;

Обратите внимание:

		`date_added` datetime  DEFAULT NULL

Дело в том что, CURRENT_TIMESTAMP в качестве дефаултного значения для поля типа datetime не катит, NOW() как дефаултное значение указывать нельзя т.к. функция.

А надо чтоб date_added выставлялось автоматом.

Выхода два:
1. Либо во всех запросах в INSERT добавлять NOW().
2. Либо вешать триггер который при каждом инсерте будет date_added=NOW();

Когда кода INSERT делаются во многих местах и везде поправить на INSERT… NOW() невозможно будем использовать триггер.

Что-то вроде:

DELIMITER $$

USE `test_db`$$

CREATE

    TRIGGER `foo_add` BEFORE INSERT ON `foo` 
    FOR EACH ROW BEGIN

      SET NEW.date_added = IFNULL(NEW.date_added, NOW());

    END;
$$

Теперь у нас таблица с нужными типами, ключами и триггером.

И мы с легкостью можем разрезать таблицу по месяцам:

 ALTER TABLE foo PARTITION BY RANGE (MONTH(date_added))
(
PARTITION p01 VALUES LESS THAN (02) ,
PARTITION p02 VALUES LESS THAN (03) ,
PARTITION p03 VALUES LESS THAN (04) ,
PARTITION p04 VALUES LESS THAN (05) ,
PARTITION p05 VALUES LESS THAN (06) ,
PARTITION p06 VALUES LESS THAN (07) ,
PARTITION p07 VALUES LESS THAN (08) ,
PARTITION p08 VALUES LESS THAN (09) ,
PARTITION p09 VALUES LESS THAN (10) ,
PARTITION p10 VALUES LESS THAN (11) ,
PARTITION p11 VALUES LESS THAN (12) ,
PARTITION p12 VALUES LESS THAN (13) ,
PARTITION pmaxval VALUES LESS THAN MAXVALUE 
);

Или даже по дням недели:

 ALTER TABLE foo PARTITION BY RANGE (DAYOFWEEK(date_added))
(
PARTITION p01 VALUES LESS THAN (2) ,
PARTITION p02 VALUES LESS THAN (3) ,
PARTITION p03 VALUES LESS THAN (4) ,
PARTITION p04 VALUES LESS THAN (5) ,
PARTITION p05 VALUES LESS THAN (6) ,
PARTITION p06 VALUES LESS THAN (7) ,
PARTITION p07 VALUES LESS THAN (8) ,
PARTITION pmaxval VALUES LESS THAN MAXVALUE 
);

Или даже 2 дня на partition:

ALTER TABLE foo PARTITION BY LIST (DAY(date_added))
(
PARTITION p00 VALUES IN  (0,1) ,
PARTITION p02 VALUES IN  (2,3) ,
PARTITION p04 VALUES IN  (4,5) ,
PARTITION p06 VALUES IN  (6,7) ,
PARTITION p08 VALUES IN  (8,9) ,
PARTITION p10 VALUES IN  (10,11),
PARTITION p12 VALUES IN  (12,13),
PARTITION p14 VALUES IN  (14,15),
PARTITION p16 VALUES IN  (16,17),
PARTITION p18 VALUES IN  (18,19),
PARTITION p20 VALUES IN  (20,21),
PARTITION p22 VALUES IN  (22,23),
PARTITION p24 VALUES IN  (24,25),
PARTITION p26 VALUES IN  (26,27),
PARTITION p28 VALUES IN  (28,29),
PARTITION p30 VALUES IN  (30,31)
);

В общем теперь все в ваших руках.