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. 針對 p 的任何分割區層級 MAX_ROWS 設定，必須與為 nt 設定的表格層級 MAX_ROWS 值相同。針對 p 的任何分割區層級 MIN_ROWS 設定也必須與為 nt 設定的任何表格層級 MIN_ROWS 值相同。

    無論 pt 是否有效使用明確的表格層級 MAX_ROWS 或 MIN_ROWS 選項，這在兩種情況下都成立。

  9. 兩個表格 pt 和 nt 之間的 AVG_ROW_LENGTH 不能不同。

  10. INDEX DIRECTORY 在表格與要與之交換的分割區之間不能不同。

  11. 表格或分割區 TABLESPACE 選項都不能在任何表格中使用。

除了執行 ALTER TABLE 陳述式通常需要的 ALTER、INSERT 和 CREATE 權限之外，您還必須擁有 DROP 權限才能執行 ALTER TABLE ... EXCHANGE PARTITION。

您還應該注意 ALTER TABLE ... EXCHANGE PARTITION 的下列效果

  • 執行 ALTER TABLE ... EXCHANGE PARTITION 不會叫用分割表格或要交換的表格上的任何觸發程序。

  • 已交換表格中的任何 AUTO_INCREMENT 資料行都會重設。

  • 與 ALTER TABLE ... EXCHANGE PARTITION 搭配使用時，IGNORE 關鍵字沒有任何作用。

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 中支援的任何類型或多種類型。

將分割區與未分割表格交換

假設已使用下列 SQL 陳述式建立和填入分割表格 e

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");

現在我們建立名為 e2 的 e 未分割副本。這可以使用 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

您可以透過查詢 Information Schema PARTITIONS 表格來查看表格 e 中哪些分割區包含資料列，如下所示

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 表格，Information Schema PARTITIONS 表格的 TABLE_ROWS 資料行中提供的資料列計數僅為 SQL 最佳化中使用的估計值，而且並不總是完全準確。

若要將表格 e 中的分割區 p0 與表格 e2 交換，您可以使用 ALTER TABLE，如下所示

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

更精確地說，剛發出的陳述式會導致在分割區中找到的任何資料列與在表格中找到的資料列交換。您可以如先前所述，透過查詢 Information Schema 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 中，方法是選擇一個小於 50 的 id 欄位值，然後透過查詢 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)

現在，我們再次使用與先前相同的 ALTER TABLE 陳述式，將分割區 p0 與表格 e2 交換。

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

以下查詢的輸出顯示，在發出 ALTER TABLE 陳述式之前，儲存在分割區 p0 中的表格列，以及儲存在表格 e2 中的表格列，現在已經交換了位置。

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 中插入一個超出表格 e 的分割區 p0 分割區定義邊界的列。例如，插入一個 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 來執行。

在不逐列驗證的情況下交換分割區

為了避免在將分割區與具有許多列的表格交換時耗時的驗證，可以透過在 ALTER TABLE ... EXCHANGE PARTITION 陳述式中附加 WITHOUT VALIDATION 來跳過逐列驗證步驟。

以下範例比較了在有驗證和沒有驗證的情況下，將分割區與未分割表格交換時的執行時間差異。分割表格（表格 e）包含兩個各有一百萬列的分割區。表格 e 的 p0 中的列被移除，並且 p0 與一個具有一百萬列的未分割表格交換。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 來執行。

將子分割區與未分割表格交換

您也可以使用 ALTER TABLE ... EXCHANGE PARTITION 陳述式，將子分割表格的子分割區（請參閱第 26.2.6 節「子分割區」）與未分割表格交換。在以下範例中，我們首先建立一個表格 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 時沒有明確命名任何子分割區，但我們可以透過在從 INFORMATION_SCHEMA 中選取時，包含 PARTITIONS 表格的 SUBPARTITION_NAME 欄位，來取得這些產生出的名稱，如下所示：

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 陳述式將表格 es 中的子分割區 p3sp0 與未分割表格 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 中移除了分割區。


上一頁   首頁   上層   下一頁
相關文件
MySQL 8.4 發行說明
下載本手冊
PDF (美式信紙) - 39.9Mb
PDF (A4) - 40.0Mb
Man Pages (TGZ) - 258.5Kb
Man Pages (Zip) - 365.5Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb