Spec-Zone.ru › MySQL 8.4

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

В MySQL 8.4 можно обменять раздел или подраздел таблицы с таблицей, используя 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.

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

  7. Для таблиц InnoDB обе таблицы должны использовать один и тот же формат строк. Чтобы определить формат строк таблицы InnoDB, запросите INFORMATION_SCHEMA.INNODB_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. INDEX DIRECTORY не должен отличаться между таблицей и разделом, которые нужно обменять.

  11. Никакие опции таблицы или раздела 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 8.4.

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

Предположим, что таблица с разделением 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.

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

Spec-Zone.ru

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