26.3.1 Management of RANGE and LIST Partitions — MySQL 8.4

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.37 «Оператор TRUNCATE TABLE».)

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

Если вы теперь выполните оператор 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» для примера.)

Для добавления нового раздела range или 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 с разделами».

Вы также можете использовать 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)
);

При разделении или объединении разделов данные не теряются, используя 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;
```