26.3.1 Management of RANGE and LIST Partitions — MySQL 9.2

26.3.1 Управление разбиениями RANGE и LIST

Добавление и удаление разбиений range и list обрабатываются аналогично, поэтому в этом разделе мы обсудим управление обоими типами разбиений. Сведения о работе с таблицами, разбитыми по хэш или ключу, см. в разделе 26.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)

Дополнительную информацию см. в разделе 26.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 PARTITION поддерживается встроенными API для разбиений и может использоваться с ALGORITHM={COPY|INPLACE}. DROP PARTITION с ALGORITHM=INPLACE удаляет данные, хранящиеся в разбиении, и удаляет само разбиение. Однако DROP PARTITION с ALGORITHM=COPY или old_alter_table=ON перестраивает таблицу с разбиением и пытается переместить данные из удаленного разбиения в другое разбиение с совместимым определением PARTITION ... VALUES. Данные, которые нельзя переместить в другое разбиение, удаляются.

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

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

Если вы хотите изменить разбиение таблицы без потери данных, используйте ALTER TABLE ... REORGANIZE PARTITION вместо этого. Подробности о REORGANIZE PARTITION см. ниже или в разделе 15.1.9 «Инструкция 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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!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 использует точно такой же синтаксис ALTER TABLE ... DROP PARTITION, как и для удаления разбиений RANGE. Однако есть одно важное различие в последствиях для последующего использования таблицы: вы больше не сможете вставлять в таблицу строки, содержащие значения, включенные в список значений, определяющих удалённое разбиение. (См. раздел 26.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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!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)

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

Вы также можете использовать 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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!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)
);

При разделении или объединении разделов данных не теряется. При выполнении приведенного выше оператора 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;
```