Тип разбиения RANGE

Тип разбиения RANGE используется для назначения каждой части диапазона значений, сгенерированных выражением разбиения. Диапазоны должны быть упорядоченными, непрерывными и непересекающимися. Минимальное значение всегда включается в первый диапазон. Максимальное значение может быть или не быть включено в последний диапазон.

Вариант этого метода разбиения, RANGE COLUMNS, позволяет использовать несколько столбцов и больше типов данных.

Синтаксис

Последняя часть оператора CREATE TABLE может быть определением новых разделов таблицы. В случае разбиения RANGE синтаксис следующий:

PARTITION BY RANGE (partitioning_expression)
(
	PARTITION partition_name VALUES LESS THAN (value),
	[ PARTITION partition_name VALUES LESS THAN (value), ... ]
)

PARTITION BY RANGE указывает, что тип разбиения — RANGE.

partitioning_expression — это SQL-выражение, которое возвращает значение из каждой строки. В простейших случаях это имя столбца. Это значение используется для определения, в какой раздел должна быть помещена строка.

partition_name — это имя раздела.

value указывает верхнюю границу для данного раздела. Значения должны быть возрастающими. Для первого раздела нижняя граница — NULL. При попытке вставить строку, если её значение выше верхней границы последнего раздела, строка будет отклонена (с ошибкой, если ключевое слово IGNORE не используется).

Если это проблема, MAXVALUE можно указать как значение для последнего раздела. Однако при этом невозможно разделить разделы существующей таблицы с разбиением RANGE. Можно добавлять новые разделы, но это не будет возможно, если верхняя граница последнего раздела — MAXVALUE.

Примеры использования

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

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

Примеры

В следующем примере мы разделим таблицу журналов по годам.

CREATE TABLE log
(
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	timestamp DATETIME NOT NULL,
	user INT UNSIGNED,
	ip BINARY(16) NOT NULL,
	action VARCHAR(20) NOT NULL,
	PRIMARY KEY (id, timestamp)
)
	ENGINE = InnoDB
PARTITION BY RANGE (YEAR(timestamp))
(
	PARTITION p0 VALUES LESS THAN (2013),
	PARTITION p1 VALUES LESS THAN (2014),
	PARTITION p2 VALUES LESS THAN (2015),
	PARTITION p3 VALUES LESS THAN (2016)
);

В качестве альтернативы мы можем разбить таблицу по году и месяцу:

CREATE TABLE log
(
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	timestamp TIMESTAMP NOT NULL,
	user INT UNSIGNED,
	ip BINARY(16) NOT NULL,
	action VARCHAR(20) NOT NULL,
	PRIMARY KEY (id, timestamp)
)
	ENGINE = InnoDB
PARTITION BY RANGE (UNIX_TIMESTAMP(timestamp))
(
	PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('2014-08-01 00:00:00')),
	PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2014-11-01 00:00:00')),
	PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2015-01-01 00:00:00')),
	PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP('2015-02-01 00:00:00'))
);

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

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

 ALTER TABLE log DROP PARTITION p0;

Пример ошибки при вставке вне заданного диапазона разделов:

INSERT INTO log(id,timestamp) VALUES 
  (1, '2016-01-01 01:01:01'), 
  (2, '2015-01-01 01:01:01');
ERROR 1526 (HY000): Table has no partition for value 2016

Если не используется ключевое слово IGNORE:

INSERT IGNORE INTO log(id,timestamp) VALUES 
  (1, '2016-01-01 01:01:01'), 
  (2, '2015-01-01 01:01:01');

SELECT * FROM log;
+----+---------------------+------+------------------+--------+
| id | timestamp           | user | ip               | action |
+----+---------------------+------+------------------+--------+
|  2 | 2015-01-01 01:01:01 | NULL |                  |        |
+----+---------------------+------+------------------+--------+

Альтернативное определение с MAXVALUE в качестве универсального варианта:

CREATE TABLE log
(
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	timestamp DATETIME NOT NULL,
	user INT UNSIGNED,
	ip BINARY(16) NOT NULL,
	action VARCHAR(20) NOT NULL,
	PRIMARY KEY (id, timestamp)
)
	ENGINE = InnoDB
PARTITION BY RANGE (YEAR(timestamp))
(
	PARTITION p0 VALUES LESS THAN (2013),
	PARTITION p1 VALUES LESS THAN (2014),
	PARTITION p2 VALUES LESS THAN (2015),
	PARTITION p3 VALUES LESS THAN (2016),
	PARTITION p4 VALUES LESS THAN MAXVALUE
);