26.3.3 Exchanging Partitions and Subpartitions with Tables — MySQL 9.2

26.3.3 Обмен разделами и подразделами с таблицами

В MySQL 9.2 возможно обменять раздел или подраздел таблицы на таблицу, используя ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt, где pt — это разделяемая таблица, а p — это раздел или подраздел pt, который необходимо обменять на неразделяемую таблицу nt, при условии, что выполнены следующие условия:

Таблица nt сама по себе не разделена.
Таблица nt не является временной таблицей.
Структуры таблиц pt и nt в остальном идентичны.
Таблица nt не содержит ссылок внешних ключей, и ни одна другая таблица не имеет внешних ключей, ссылающихся на nt.
В nt нет строк, которые выходят за пределы определения раздела для p. Это условие не применяется, если используется WITHOUT VALIDATION.
Обе таблицы должны использовать один и тот же набор символов и сортировку.
Для таблиц InnoDB обе таблицы должны использовать один и тот же формат строк. Чтобы определить формат строки таблицы InnoDB, выполните запрос к INFORMATION_SCHEMA.INNODB_TABLES.
Любая настройка MAX_ROWS уровня раздела для p должна быть такой же, как значение MAX_ROWS уровня таблицы, заданное для nt. Настройка любой настройки MIN_ROWS уровня раздела для p также должна быть такой же, как любое значение MIN_ROWS уровня таблицы, заданное для nt.

Это верно в обоих случаях, независимо от того, есть ли у pt явное значение MAX_ROWS или MIN_ROWS уровня таблицы.
AVG_ROW_LENGTH не может отличаться между двумя таблицами pt и nt.
INDEX DIRECTORY не может отличаться между таблицей и разделом, которые будут обменены.
Никакие параметры таблицы или раздела TABLESPACE не могут быть использованы ни в одной из таблиц.

В дополнение к ALTER, INSERT и CREATE привилегиям, обычно необходимым для инструкций ALTER TABLE, вы должны иметь привилегию DROP для выполнения ALTER TABLE ... EXCHANGE PARTITION.

Вы также должны быть осведомлены о следующих эффектах инструкции ALTER TABLE ... EXCHANGE PARTITION:

Выполнение ALTER TABLE ... EXCHANGE PARTITION не вызывает никаких триггеров ни в разделяемой таблице, ни в таблице, которая будет обменены.
Любые столбцы AUTO_INCREMENT в таблице, которая будет обменены, сбрасываются.
Ключевое слово IGNORE не имеет эффекта при использовании с ALTER TABLE ... EXCHANGE PARTITION.

Синтаксис инструкции ALTER TABLE ... EXCHANGE PARTITION показан здесь, где pt — это разделяемая таблица, p — это раздел (или подраздел), который необходимо обменять, а nt — это неразделяемая таблица, которая должна быть обменена с p:

ALTER TABLE pt
    EXCHANGE PARTITION p
    WITH TABLE nt;

Дополнительно можно добавить WITH VALIDATION или WITHOUT VALIDATION. Когда указано WITHOUT VALIDATION, операция ALTER TABLE ... EXCHANGE PARTITION не выполняет проверки строк по строкам при обмене раздела неразделяемой таблицей, позволяя администраторам базы данных взять на себя ответственность за обеспечение того, что строки находятся в пределах определения раздела. WITH VALIDATION — это значение по умолчанию.

Один и только один раздел или подраздел может быть обменен с одной и только одной неразделяемой таблицей в одном операторе ALTER TABLE EXCHANGE PARTITION. Чтобы обменять несколько разделов или подразделов, используйте несколько операторов ALTER TABLE EXCHANGE PARTITION. EXCHANGE PARTITION нельзя сочетать с другими параметрами оператора ALTER TABLE. Разбиение и (если применимо) подразделение, используемые разделяемой таблицей, могут быть любого типа или типов, поддерживаемых в MySQL 9.2.

Обмен раздела с неразделяемой таблицей

Предположим, что разделяемая таблица e была создана и заполнена с помощью следующих операторов SQL:

CREATE TABLE e (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
)
    PARTITION BY RANGE (id) (
        PARTITION p0 VALUES LESS THAN (50),
        PARTITION p1 VALUES LESS THAN (100),
        PARTITION p2 VALUES LESS THAN (150),
        PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

INSERT INTO e VALUES
    (1669, "Jim", "Smith"),
    (337, "Mary", "Jones"),
    (16, "Frank", "White"),
    (2005, "Linda", "Black");

Теперь мы создаем неразделяемую копию e под названием e2. Это можно сделать с помощью клиента mysql, как показано здесь:

mysql> CREATE TABLE e2 LIKE e;
Query OK, 0 rows affected (0.04 sec)

mysql> ALTER TABLE e2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

Можно увидеть, какие разделы в таблице e содержат строки, выполнив запрос к таблице схемы информации PARTITIONS, например:

mysql> SELECT PARTITION_NAME, TABLE_ROWS
           FROM INFORMATION_SCHEMA.PARTITIONS
           WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          1 |
| p1             |          0 |
| p2             |          0 |
| p3             |          3 |
+----------------+------------+
2 rows in set (0.00 sec)

Примечание

Для разделяемых InnoDB таблиц количество строк, указанное в столбце TABLE_ROWS таблицы схемы информации PARTITIONS, является только оценочным значением, используемым в оптимизации SQL, и не всегда точным.

Для обмена разделом p0 в таблице e с таблицей e2 можно использовать ALTER TABLE, как показано здесь:

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
Query OK, 0 rows affected (0.04 sec)

Более точно, данный оператор вызывает обмен строками, найденными в разделе, с теми, что находятся в таблице. Вы можете наблюдать, как это произошло, запросив таблицу схемы информации PARTITIONS, как и раньше. Строка таблицы, которая ранее находилась в разделе p0, больше не присутствует:

mysql> SELECT PARTITION_NAME, TABLE_ROWS
           FROM INFORMATION_SCHEMA.PARTITIONS
           WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |          0 |
| p2             |          0 |
| p3             |          3 |
+----------------+------------+
4 rows in set (0.00 sec)

Если вы запросите таблицу e2, вы увидите, что «пропущенная» строка теперь находится там:

mysql> SELECT * FROM e2;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 16 | Frank | White |
+----+-------+-------+
1 row in set (0.00 sec)

Таблица, которая будет обменена с разделом, не обязательно должна быть пустой. Чтобы продемонстрировать это, сначала вставим новую строку в таблицу e, убедившись, что эта строка хранится в разделе p0, выбрав значение столбца id, которое меньше 50, и проверив это позже, запросив таблицу PARTITIONS:

mysql> INSERT INTO e VALUES (41, "Michael", "Green");
Query OK, 1 row affected (0.05 sec)

mysql> SELECT PARTITION_NAME, TABLE_ROWS
           FROM INFORMATION_SCHEMA.PARTITIONS
           WHERE TABLE_NAME = 'e';            
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          1 |
| p1             |          0 |
| p2             |          0 |
| p3             |          3 |
+----------------+------------+
4 rows in set (0.00 sec)

Теперь мы снова обменяем раздел p0 с таблицей e2, используя тот же оператор ALTER TABLE, что и ранее:

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
Query OK, 0 rows affected (0.28 sec)

Вывод следующих запросов показывает, что строка таблицы, которая хранилась в разделе p0, и строка таблицы, которая хранилась в таблице e2 до выполнения оператора ALTER TABLE, поменялись местами:

mysql> SELECT * FROM e;
+------+-------+-------+
| id   | fname | lname |
+------+-------+-------+
|   16 | Frank | White |
| 1669 | Jim   | Smith |
|  337 | Mary  | Jones |
| 2005 | Linda | Black |
+------+-------+-------+
4 rows in set (0.00 sec)

mysql> SELECT PARTITION_NAME, TABLE_ROWS
           FROM INFORMATION_SCHEMA.PARTITIONS
           WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          1 |
| p1             |          0 |
| p2             |          0 |
| p3             |          3 |
+----------------+------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM e2;
+----+---------+-------+
| id | fname   | lname |
+----+---------+-------+
| 41 | Michael | Green |
+----+---------+-------+
1 row in set (0.00 sec)

Строки, не соответствующие требованиям

Следует иметь в виду, что все строки, найденные в неразделяемой таблице до выполнения оператора ALTER TABLE ... EXCHANGE PARTITION, должны соответствовать условиям для хранения в целевом разделе; в противном случае оператор завершится ошибкой. Чтобы увидеть, как это происходит, сначала вставьте строку в e2, которая выходит за пределы определения раздела для раздела p0 таблицы e. Например, вставьте строку со значением столбца id, которое слишком велико; затем попробуйте снова обменять таблицу с разделом:

mysql> INSERT INTO e2 VALUES (51, "Ellen", "McDonald");
Query OK, 1 row affected (0.08 sec)

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
ERROR 1707 (HY000): Found row that does not match the partition

Только параметр WITHOUT VALIDATION позволит выполнить эту операцию:

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITHOUT VALIDATION;
Query OK, 0 rows affected (0.02 sec)

Когда раздел обменивается с таблицей, содержащей строки, которые не соответствуют определению раздела, администратор базы данных должен исправить несовпадающие строки, что можно сделать с помощью REPAIR TABLE или ALTER TABLE ... REPAIR PARTITION.

Обмен разделами без проверки строк по строкам

Чтобы избежать длительной валидации при обмене раздела с таблицей, содержащей много строк, можно пропустить этап проверки строк по строкам, добавив WITHOUT VALIDATION к оператору ALTER TABLE ... EXCHANGE PARTITION.

В следующем примере сравнивается разница во времени выполнения при обмене раздела с неразделенной таблицей с валидацией и без нее. Разделенная таблица (таблица e) содержит два раздела по 1 миллиону строк каждый. Строки в p0 таблицы e удаляются, а p0 обменивается с неразделенной таблицей из 1 миллиона строк. Операция WITH VALIDATION занимает 0,74 секунды. В сравнении, операция WITHOUT VALIDATION занимает 0,01 секунды.

# Create a partitioned table with 1 million rows in each partition

CREATE TABLE e (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
)
    PARTITION BY RANGE (id) (
        PARTITION p0 VALUES LESS THAN (1000001),
        PARTITION p1 VALUES LESS THAN (2000001),
);

mysql> SELECT COUNT(*) FROM e;
| COUNT(*) |
+----------+
|  2000000 |
+----------+
1 row in set (0.27 sec)

# View the rows in each partition

SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+-------------+
| PARTITION_NAME | TABLE_ROWS  |
+----------------+-------------+
| p0             |     1000000 |
| p1             |     1000000 |
+----------------+-------------+
2 rows in set (0.00 sec)

# Create a nonpartitioned table of the same structure and populate it with 1 million rows

CREATE TABLE e2 (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
);

mysql> SELECT COUNT(*) FROM e2;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.24 sec)

# Create another nonpartitioned table of the same structure and populate it with 1 million rows

CREATE TABLE e3 (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
);

mysql> SELECT COUNT(*) FROM e3;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.25 sec)

# Drop the rows from p0 of table e

mysql> DELETE FROM e WHERE id < 1000001;
Query OK, 1000000 rows affected (5.55 sec)

# Confirm that there are no rows in partition p0

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |    1000000 |
+----------------+------------+
2 rows in set (0.00 sec)

# Exchange partition p0 of table e with the table e2 'WITH VALIDATION'

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITH VALIDATION;
Query OK, 0 rows affected (0.74 sec)

# Confirm that the partition was exchanged with table e2

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |    1000000 |
| p1             |    1000000 |
+----------------+------------+
2 rows in set (0.00 sec)

# Once again, drop the rows from p0 of table e

mysql> DELETE FROM e WHERE id < 1000001;
Query OK, 1000000 rows affected (5.55 sec)

# Confirm that there are no rows in partition p0

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |    1000000 |
+----------------+------------+
2 rows in set (0.00 sec)

# Exchange partition p0 of table e with the table e3 'WITHOUT VALIDATION'

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e3 WITHOUT VALIDATION;
Query OK, 0 rows affected (0.01 sec)

# Confirm that the partition was exchanged with table e3

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |    1000000 |
| p1             |    1000000 |
+----------------+------------+
2 rows in set (0.00 sec)

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

Обмен подразделом с неразделенной таблицей

Вы также можете обменять подраздел подразделенной таблицы (см. Раздел 26.2.6, «Подразделение») с неразделенной таблицей с помощью оператора ALTER TABLE ... EXCHANGE PARTITION. В следующем примере сначала создается таблица es, разделенная по RANGE и подразделяемая по KEY, заполняется так же, как и таблица e, а затем создается пустая неразделенная копия es2 таблицы, как показано здесь:

mysql> CREATE TABLE es (
    ->     id INT NOT NULL,
    ->     fname VARCHAR(30),
    ->     lname VARCHAR(30)
    -> )
    ->     PARTITION BY RANGE (id)
    ->     SUBPARTITION BY KEY (lname)
    ->     SUBPARTITIONS 2 (
    ->         PARTITION p0 VALUES LESS THAN (50),
    ->         PARTITION p1 VALUES LESS THAN (100),
    ->         PARTITION p2 VALUES LESS THAN (150),
    ->         PARTITION p3 VALUES LESS THAN (MAXVALUE)
    ->     );
Query OK, 0 rows affected (2.76 sec)

mysql> INSERT INTO es VALUES
    ->     (1669, "Jim", "Smith"),
    ->     (337, "Mary", "Jones"),
    ->     (16, "Frank", "White"),
    ->     (2005, "Linda", "Black");
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE es2 LIKE es;
Query OK, 0 rows affected (1.27 sec)

mysql> ALTER TABLE es2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.70 sec)
Records: 0  Duplicates: 0  Warnings: 0

Хотя при создании таблицы es мы не указывали имена подразделов явно, мы можем получить сгенерированные имена, включив столбец SUBPARTITION_NAME таблицы PARTITIONS из INFORMATION_SCHEMA при выборке из этой таблицы, как показано здесь:

mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS
    ->     FROM INFORMATION_SCHEMA.PARTITIONS
    ->     WHERE TABLE_NAME = 'es';
+----------------+-------------------+------------+
| PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS |
+----------------+-------------------+------------+
| p0             | p0sp0             |          1 |
| p0             | p0sp1             |          0 |
| p1             | p1sp0             |          0 |
| p1             | p1sp1             |          0 |
| p2             | p2sp0             |          0 |
| p2             | p2sp1             |          0 |
| p3             | p3sp0             |          3 |
| p3             | p3sp1             |          0 |
+----------------+-------------------+------------+
8 rows in set (0.00 sec)

Следующий оператор ALTER TABLE обменивает подраздел p3sp0 в таблице es с неразделенной таблицей es2:

mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2;
Query OK, 0 rows affected (0.29 sec)

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

mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS
    ->     FROM INFORMATION_SCHEMA.PARTITIONS
    ->     WHERE TABLE_NAME = 'es';
+----------------+-------------------+------------+
| PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS |
+----------------+-------------------+------------+
| p0             | p0sp0             |          1 |
| p0             | p0sp1             |          0 |
| p1             | p1sp0             |          0 |
| p1             | p1sp1             |          0 |
| p2             | p2sp0             |          0 |
| p2             | p2sp1             |          0 |
| p3             | p3sp0             |          0 |
| p3             | p3sp1             |          0 |
+----------------+-------------------+------------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM es2;
+------+-------+-------+
| id   | fname | lname |
+------+-------+-------+
| 1669 | Jim   | Smith |
|  337 | Mary  | Jones |
| 2005 | Linda | Black |
+------+-------+-------+
3 rows in set (0.00 sec)

Если таблица подразделяема, вы можете обменять только подраздел таблицы, а не весь раздел, с неразделенной таблицей, как показано здесь:

mysql> ALTER TABLE es EXCHANGE PARTITION p3 WITH TABLE es2;
ERROR 1704 (HY000): Subpartitioned table, use subpartition instead of partition

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

mysql> CREATE TABLE es3 LIKE e;
Query OK, 0 rows affected (1.31 sec)

mysql> ALTER TABLE es3 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.53 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE es3\G
*************************** 1. row ***************************
       Table: es3
Create Table: CREATE TABLE `es3` (
  `id` int(11) NOT NULL,
  `fname` varchar(30) DEFAULT NULL,
  `lname` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> ALTER TABLE es3 ENGINE = MyISAM;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es3;
ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL

Оператор ALTER TABLE ... ENGINE ... в данном примере работает, потому что предыдущий оператор ALTER TABLE удалил разделение из таблицы es3.