Spec-Zone.ru › MySQL 5.7

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

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

  1. Таблица nt не является самой по себе разделительной.

  2. Таблица nt не является временной таблицей.

  3. Структуры таблиц pt и nt в остальном идентичны.

  4. Таблица nt не содержит ссылок на внешние ключи, и никакая другая таблица не имеет внешних ключей, ссылающихся на nt.

  5. В nt нет строк, которые выходят за пределы определения раздела для p. Это условие не применяется, если используется параметр WITHOUT VALIDATION. Параметр [{WITH|WITHOUT} VALIDATION] был добавлен в MySQL 5.7.5.

  6. Обе таблицы должны использовать один и тот же набор символов и сортировку.

  7. Для таблиц InnoDB обе таблицы должны использовать один и тот же формат строк. Чтобы определить формат строк таблицы InnoDB, выполните запрос к таблице схемы информации INNODB_SYS_TABLES.

  8. Любое значение настроек раздела MAX_ROWS для p должно быть таким же, как значение настроек таблицы MAX_ROWS для nt. Значение любого параметра раздела MIN_ROWS для p также должно быть таким же, как любое значение настроек таблицы MIN_ROWS для nt.

    Это верно в любом случае, независимо от того, есть ли у pt явное значение настроек таблицы MAX_ROWS или MIN_ROWS.

  9. AVG_ROW_LENGTH не может отличаться между двумя таблицами pt и nt.

  10. pt не имеет разделов, использующих параметр DATA DIRECTORY. Это ограничение снято для таблиц InnoDB в MySQL 5.7.25 и более поздних версиях.

  11. INDEX DIRECTORY не может отличаться между таблицей и разделом, который с ней обменивается.

  12. Параметры таблицы или раздела 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 — это поведение по умолчанию и его не нужно явно указывать. Параметр [{WITH|WITHOUT} VALIDATION] был добавлен в MySQL 5.7.5.

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

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

Предположим, что разделяемая таблица 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 (1.34 sec)

mysql> ALTER TABLE e2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.90 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 |
+----------------+------------+
4 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.28 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)
END_OF_DOCUMENT_MARKER

При обмене раздела с таблицей, содержащей строки, которые не соответствуют определению раздела, администратор базы данных должен исправить несовпадающие строки, что можно сделать с помощью 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),
);

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.

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

Вы также можете обменять подраздел разделённой таблицы (см. Раздел 22.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, очень строгое. Количество, порядок, имена и типы столбцов и индексов разделённой таблицы и неразделённой таблицы должны точно совпадать. Кроме того, обе таблицы должны использовать один и тот же движок хранения: 

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=latin1
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

© 2025 Oracle
Licensed under the GPLv2 License.
https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/partitioning-management-exchange.html

Spec-Zone.ru

Настройки Оффлайн Что нового Помощь О нас
Spec-Zone .ru
спецификации, руководства, описания, API