22.3.1 Management of RANGE and LIST Partitions — MySQL 5.7

22.3.1 Управление разделами RANGE и LIST

Добавление и удаление разделов range и list обрабатываются аналогично, поэтому в этом разделе мы обсудим управление обоими типами разбиения. Сведения о работе с таблицами, разбитыми по хешу или ключу, см. в разделе 22.3.2 «Управление разделами HASH и KEY».

Удаление раздела из таблицы, разбитой по RANGE или LIST, можно выполнить с помощью инструкции ALTER TABLE с опцией DROP PARTITION. Предположим, что вы создали таблицу, разбитую по диапазону, и заполнили её 10 записями с помощью следующих инструкций CREATE TABLE и INSERT:

mysql> CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
    ->     PARTITION BY RANGE( YEAR(purchased) ) (
    ->         PARTITION p0 VALUES LESS THAN (1990),
    ->         PARTITION p1 VALUES LESS THAN (1995),
    ->         PARTITION p2 VALUES LESS THAN (2000),
    ->         PARTITION p3 VALUES LESS THAN (2005),
    ->         PARTITION p4 VALUES LESS THAN (2010),
    ->         PARTITION p5 VALUES LESS THAN (2015)
    ->     );
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO tr VALUES
    ->     (1, 'desk organiser', '2003-10-15'),
    ->     (2, 'alarm clock', '1997-11-05'),
    ->     (3, 'chair', '2009-03-10'),
    ->     (4, 'bookcase', '1989-01-10'),
    ->     (5, 'exercise bike', '2014-05-09'),
    ->     (6, 'sofa', '1987-06-05'),
    ->     (7, 'espresso maker', '2011-11-22'),
    ->     (8, 'aquarium', '1992-08-04'),
    ->     (9, 'study desk', '2006-09-16'),
    ->     (10, 'lava lamp', '1998-12-25');
Query OK, 10 rows affected (0.05 sec)
Records: 10  Duplicates: 0  Warnings: 0

Вы можете увидеть, какие элементы должны были быть добавлены в раздел p2, как показано здесь:

mysql> SELECT * FROM tr
    ->     WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';
+------+-------------+------------+
| id   | name        | purchased  |
+------+-------------+------------+
|    2 | alarm clock | 1997-11-05 |
|   10 | lava lamp   | 1998-12-25 |
+------+-------------+------------+
2 rows in set (0.00 sec)

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

mysql> SELECT * FROM tr PARTITION (p2);
+------+-------------+------------+
| id   | name        | purchased  |
+------+-------------+------------+
|    2 | alarm clock | 1997-11-05 |
|   10 | lava lamp   | 1998-12-25 |
+------+-------------+------------+
2 rows in set (0.00 sec)

Дополнительные сведения см. в разделе 22.5 «Выбор раздела».

Чтобы удалить раздел с именем p2, выполните следующую команду:

mysql> ALTER TABLE tr DROP PARTITION p2;
Query OK, 0 rows affected (0.03 sec)

Примечание

Двигатель хранения NDBCLUSTER не поддерживает ALTER TABLE ... DROP PARTITION. Однако он поддерживает другие расширения, связанные с разбиением, для ALTER TABLE, которые описаны в этой главе.

Очень важно помнить, что при удалении раздела также удаляются все данные, хранящиеся в этом разделе. Вы можете убедиться в этом, повторно выполнив предыдущий запрос SELECT:

mysql> SELECT * FROM tr WHERE purchased
    -> BETWEEN '1995-01-01' AND '1999-12-31';
Empty set (0.00 sec)

По этой причине у вас должна быть привилегия DROP для таблицы, прежде чем вы сможете выполнить ALTER TABLE ... DROP PARTITION для этой таблицы.

Если вы хотите удалить все данные из всех разделов, сохранив определение таблицы и схему разбиения, используйте инструкцию TRUNCATE TABLE. (См. раздел 13.1.34 «Оператор TRUNCATE TABLE».)

Если вы хотите изменить разбиение таблицы без потери данных, используйте ALTER TABLE ... REORGANIZE PARTITION вместо этого. Сведения о REORGANIZE PARTITION см. ниже или в разделе 13.1.8 «Оператор ALTER TABLE».

Если вы теперь выполните оператор SHOW CREATE TABLE, вы можете увидеть, как изменилось разбиение таблицы:

mysql> SHOW CREATE TABLE tr\G
*************************** 1. row ***************************
       Table: tr
Create Table: CREATE TABLE `tr` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( YEAR(purchased))
(PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB) */
1 row in set (0.00 sec)

При добавлении новых строк в изменённую таблицу со значениями столбца purchased между '1995-01-01' и '2004-12-31' (включительно), эти строки хранятся в разделе p3. Вы можете проверить это следующим образом:

mysql> INSERT INTO tr VALUES (11, 'pencil holder', '1995-07-12');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tr WHERE purchased
    -> BETWEEN '1995-01-01' AND '2004-12-31';
+------+----------------+------------+
| id   | name           | purchased  |
+------+----------------+------------+
|    1 | desk organiser | 2003-10-15 |
|   11 | pencil holder  | 1995-07-12 |
+------+----------------+------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE tr DROP PARTITION p3;
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * FROM tr WHERE purchased
    -> BETWEEN '1995-01-01' AND '2004-12-31';
Empty set (0.00 sec)

Количество строк, удалённых из таблицы в результате ALTER TABLE ... DROP PARTITION, не сообщается сервером, в отличие от эквивалентного запроса DELETE.

Удаление LIST разделов использует ту же самую синтаксическую конструкцию, что и при удалении RANGE разделов. Однако есть одно важное различие в эффекте этого на последующее использование таблицы: вы больше не можете вставлять в таблицу строки, содержащие какие-либо значения, включённые в список значений, определяющий удалённый раздел. (См. раздел 22.2.2 «Разбиение LIST», для примера.)

Чтобы добавить новый диапазон или список разделов в уже разбитую таблицу, используйте оператор ALTER TABLE ... ADD PARTITION. Для таблиц, разбитых по RANGE, это можно использовать для добавления нового диапазона в конец списка существующих разделов. Предположим, что у вас есть таблица с данными членства в вашей организации, которая определена следующим образом:

CREATE TABLE members (
    id INT,
    fname VARCHAR(25),
    lname VARCHAR(25),
    dob DATE
)
PARTITION BY RANGE( YEAR(dob) ) (
    PARTITION p0 VALUES LESS THAN (1980),
    PARTITION p1 VALUES LESS THAN (1990),
    PARTITION p2 VALUES LESS THAN (2000)
);

Предположим далее, что минимальный возраст для членов составляет 16 лет. По мере приближения конца 2015 года вы понимаете, что скоро будете принимать членов, родившихся в 2000 году (и позже). Вы можете изменить таблицу members, чтобы вместить новых членов, родившихся в период с 2000 по 2010 год, как показано здесь:

ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));

Для таблиц, разбитых по диапазону, вы можете использовать ADD PARTITION, чтобы добавить новые разделы только в конец списка разделов. Попытка добавить новый раздел таким образом между существующими разделами или перед ними приводит к ошибке, как показано здесь:

mysql> ALTER TABLE members
     >     ADD PARTITION (
     >     PARTITION n VALUES LESS THAN (1970));
ERROR 1463 (HY000): VALUES LESS THAN value must be strictly »
   increasing for each partition

Вы можете обойти эту проблему, переорганизовав первый раздел на два новых раздела, разделив диапазон между ними, как показано здесь:

ALTER TABLE members
    REORGANIZE PARTITION p0 INTO (
        PARTITION n0 VALUES LESS THAN (1970),
        PARTITION n1 VALUES LESS THAN (1980)
);

Используя SHOW CREATE TABLE, вы можете увидеть, что оператор ALTER TABLE имел желаемый эффект:

mysql> SHOW CREATE TABLE members\G
*************************** 1. row ***************************
       Table: members
Create Table: CREATE TABLE `members` (
  `id` int(11) DEFAULT NULL,
  `fname` varchar(25) DEFAULT NULL,
  `lname` varchar(25) DEFAULT NULL,
  `dob` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( YEAR(dob))
(PARTITION n0 VALUES LESS THAN (1970) ENGINE = InnoDB,
 PARTITION n1 VALUES LESS THAN (1980) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */
1 row in set (0.00 sec)

См. также раздел 13.1.8.1 «Операции с разделами ALTER TABLE».

Вы также можете использовать ALTER TABLE ... ADD PARTITION для добавления новых разделов в таблицу, разбитую по LIST. Предположим, что таблица tt определена с помощью следующего оператора CREATE TABLE:

CREATE TABLE tt (
    id INT,
    data INT
)
PARTITION BY LIST(data) (
    PARTITION p0 VALUES IN (5, 10, 15),
    PARTITION p1 VALUES IN (6, 12, 18)
);

Вы можете добавить новый раздел, в котором будут храниться строки со значениями столбца data 7, 14 и 21, как показано:

ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));

Помните, что вы не можете добавить новый LIST раздел, охватывающий значения, уже включённые в список значений существующего раздела. Если вы попытаетесь это сделать, произойдёт ошибка:

mysql> ALTER TABLE tt ADD PARTITION 
     >     (PARTITION np VALUES IN (4, 8, 12));
ERROR 1465 (HY000): Multiple definition of same constant »
                    in list partitioning

Поскольку все строки со значением столбца data равным 12 уже назначены разделу p1, вы не можете создать новый раздел в таблице tt, включающий 12 в свой список значений. Чтобы этого добиться, можно удалить p1, добавить np, а затем новый p1 с изменённым определением. Однако, как уже обсуждалось ранее, это приведёт к потере всех данных, хранящихся в p1, и часто этого не хотят делать. Другим решением может быть создание копии таблицы с новым разбиением и копирование данных в неё с помощью CREATE TABLE ... SELECT ..., а затем удаление старой таблицы и переименование новой, но это может занять очень много времени при работе с большими объёмами данных. Это также может быть невозможным в ситуациях, когда требуется высокая доступность.

Вы можете добавить несколько разделов в одном операторе ALTER TABLE ... ADD PARTITION, как показано здесь:

CREATE TABLE employees (
  id INT NOT NULL,
  fname VARCHAR(50) NOT NULL,
  lname VARCHAR(50) NOT NULL,
  hired DATE NOT NULL
)
PARTITION BY RANGE( YEAR(hired) ) (
  PARTITION p1 VALUES LESS THAN (1991),
  PARTITION p2 VALUES LESS THAN (1996),
  PARTITION p3 VALUES LESS THAN (2001),
  PARTITION p4 VALUES LESS THAN (2005)
);

ALTER TABLE employees ADD PARTITION (
    PARTITION p5 VALUES LESS THAN (2010),
    PARTITION p6 VALUES LESS THAN MAXVALUE
);

К счастью, реализация разбиения в MySQL предоставляет способы переопределения разделов без потери данных. Давайте сначала рассмотрим несколько простых примеров, связанных с разбиением RANGE. Вспомните таблицу members, которая теперь определена следующим образом:

mysql> SHOW CREATE TABLE members\G
*************************** 1. row ***************************
       Table: members
Create Table: CREATE TABLE `members` (
  `id` int(11) DEFAULT NULL,
  `fname` varchar(25) DEFAULT NULL,
  `lname` varchar(25) DEFAULT NULL,
  `dob` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( YEAR(dob))
(PARTITION n0 VALUES LESS THAN (1970) ENGINE = InnoDB,
 PARTITION n1 VALUES LESS THAN (1980) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */
1 row in set (0.00 sec)

Предположим, что вы хотите перенести все строки, представляющие членов, родившихся до 1960 года, в отдельный раздел. Как мы уже видели, это нельзя сделать с помощью ALTER TABLE ... ADD PARTITION. Однако вы можете использовать другое расширение, связанное с разбиением, для ALTER TABLE, чтобы этого добиться:

ALTER TABLE members REORGANIZE PARTITION n0 INTO (
    PARTITION s0 VALUES LESS THAN (1960),
    PARTITION s1 VALUES LESS THAN (1970)
);

По сути, эта команда разделяет раздел n0 на два новых раздела s0 и s1. Она также переносит данные, хранящиеся в n0, в новые разделы в соответствии с правилами, заложенными в двух PARTITION ... VALUES ...-строках, так что s0 содержит только те записи, для которых YEAR(dob) меньше 1960, а s1 содержит те строки, в которых YEAR(dob) больше или равно 1960, но меньше 1970.

Оператор REORGANIZE PARTITION также может использоваться для объединения смежных разделов. Вы можете отменить эффект предыдущей инструкции для таблицы members, как показано здесь:

ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (
    PARTITION p0 VALUES LESS THAN (1970)
);

При разбиении или объединении разделов с помощью REORGANIZE PARTITION данные не теряются. При выполнении вышеприведенной инструкции MySQL переносит все записи, хранящиеся в разделах s0 и s1, в раздел p0.

Общая синтаксическая конструкция для REORGANIZE PARTITION показана здесь:

ALTER TABLE tbl_name
    REORGANIZE PARTITION partition_list
    INTO (partition_definitions);

Здесь tbl_name — имя разделяемой таблицы, а partition_list — список имён одной или нескольких существующих разделяемых таблиц, подлежащих изменению, разделённых запятыми. partition_definitions — список новых определений разделов, которые подчиняются тем же правилам, что и список partition_definitions, используемый в CREATE TABLE. При использовании REORGANIZE PARTITION вы не ограничены слиянием нескольких разделов в один или разделением одного раздела на несколько. Например, вы можете переорганизовать все четыре раздела таблицы members в два, как показано ниже:

ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (
    PARTITION m0 VALUES LESS THAN (1980),
    PARTITION m1 VALUES LESS THAN (2000)
);

Вы также можете использовать REORGANIZE PARTITION с таблицами, которые разделены по LIST. Вернёмся к проблеме добавления нового раздела в таблицу со списком разделов tt и ошибке, возникающей из-за того, что новый раздел имел значение, уже присутствующее в списке значений одного из существующих разделов. Мы можем справиться с этим, добавив раздел, содержащий только несовпадающие значения, а затем переорганизовав новый раздел и существующий раздел таким образом, чтобы значение, хранящееся в существующем разделе, было перемещено в новый раздел:

ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));
ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (
    PARTITION p1 VALUES IN (6, 18),
    PARTITION np VALUES in (4, 8, 12)
);

Вот несколько ключевых моментов, которые следует учитывать при использовании ALTER TABLE ... REORGANIZE PARTITION для переразделения таблиц, разделенных по RANGE или LIST:

Опции PARTITION, используемые для определения новой схемы разделения, подчиняются тем же правилам, что и те, которые используются с оператором CREATE TABLE.

Новая схема разделения по диапазонам не может иметь перекрывающихся диапазонов; новая схема разделения по списку значений не может иметь перекрывающихся наборов значений.
Сочетание разделов в списке partition_definitions должно охватывать тот же диапазон или набор значений в целом, что и объединённые разделы, указанные в списке partition_list.

Например, разделы p1 и p2 вместе покрывают годы с 1980 по 1999 в таблице members, используемой в качестве примера в данном разделе. Любая переорганизация этих двух разделов должна охватывать тот же диапазон лет в целом.
Для таблиц, разделенных по RANGE, можно переорганизовать только смежные разделы; нельзя пропускать разделы диапазонов.

Например, вы не могли бы переорганизовать пример таблицы members, используя оператор, начинающийся с ALTER TABLE members REORGANIZE PARTITION p0,p2 INTO ..., так как p0 охватывает годы до 1970, а p2 — годы с 1990 по 1999 включительно, поэтому эти разделы не являются смежными. (В этом случае нельзя пропустить раздел p1.)
Вы не можете использовать REORGANIZE PARTITION для изменения типа разделения, используемого таблицей (например, вы не можете изменить разделы RANGE на разделы HASH или наоборот). Вы также не можете использовать этот оператор для изменения выражения разделения или столбца. Чтобы выполнить любой из этих задач без удаления и повторного создания таблицы, вы можете использовать ALTER TABLE ... PARTITION BY ..., как показано здесь:
```
ALTER TABLE members
    PARTITION BY HASH( YEAR(dob) )
    PARTITIONS 8;
```