MySQL 9.0 參考手冊  /  分割區  /  選擇分割區

26.5 選擇分割區

支援針對符合給定 WHERE 條件的資料列，明確選擇分割區和子分割區。選擇分割區與分割區修剪類似，因為只會檢查特定分割區是否符合，但在兩個關鍵方面有所不同：

  1. 要檢查的分割區由陳述式發出者指定，這與自動進行的分割區修剪不同。

  2. 分割區修剪僅適用於查詢，而明確選擇分割區則支援查詢和許多 DML 陳述式。

此處列出支援明確分割區選擇的 SQL 陳述式：

本節的其餘部分將討論明確分割區選擇如何普遍適用於剛才列出的陳述式，並提供一些範例。

明確分割區選擇是使用 PARTITION 選項實作的。對於所有支援的陳述式，此選項使用此處顯示的語法：

      PARTITION (partition_names)

      partition_names:
          partition_name, ...

此選項始終位於分割區所屬表格的名稱之後。partition_names 是要使用的分割區或子分割區的逗號分隔清單。此清單中的每個名稱都必須是指定表格的現有分割區或子分割區的名稱；如果找不到任何分割區或子分割區，陳述式將失敗並出現錯誤 (分割區 'partition_name' 不存在)。partition_names 中命名的分割區和子分割區可以任何順序排列，而且可能會重疊。

使用 PARTITION 選項時，只會檢查列出的分割區和子分割區是否有符合的資料列。此選項可用於 SELECT 陳述式，以判斷哪些資料列屬於給定的分割區。考量一個名為 employees 的已分割區表格，該表格是使用此處顯示的陳述式建立並填入的：

SET @@SQL_MODE = '';

CREATE TABLE employees  (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    fname VARCHAR(25) NOT NULL,
    lname VARCHAR(25) NOT NULL,
    store_id INT NOT NULL,
    department_id INT NOT NULL
)
    PARTITION BY RANGE(id)  (
        PARTITION p0 VALUES LESS THAN (5),
        PARTITION p1 VALUES LESS THAN (10),
        PARTITION p2 VALUES LESS THAN (15),
        PARTITION p3 VALUES LESS THAN MAXVALUE
);

INSERT INTO employees VALUES
    ('', 'Bob', 'Taylor', 3, 2), ('', 'Frank', 'Williams', 1, 2),
    ('', 'Ellen', 'Johnson', 3, 4), ('', 'Jim', 'Smith', 2, 4),
    ('', 'Mary', 'Jones', 1, 1), ('', 'Linda', 'Black', 2, 3),
    ('', 'Ed', 'Jones', 2, 1), ('', 'June', 'Wilson', 3, 1),
    ('', 'Andy', 'Smith', 1, 3), ('', 'Lou', 'Waters', 2, 4),
    ('', 'Jill', 'Stone', 1, 4), ('', 'Roger', 'White', 3, 2),
    ('', 'Howard', 'Andrews', 1, 2), ('', 'Fred', 'Goldberg', 3, 3),
    ('', 'Barbara', 'Brown', 2, 3), ('', 'Alice', 'Rogers', 2, 2),
    ('', 'Mark', 'Morgan', 3, 3), ('', 'Karen', 'Cole', 3, 2);

您可以像這樣查看哪些資料列儲存在分割區 p1 中：

mysql> SELECT * FROM employees PARTITION (p1);
+----+-------+--------+----------+---------------+
| id | fname | lname  | store_id | department_id |
+----+-------+--------+----------+---------------+
|  5 | Mary  | Jones  |        1 |             1 |
|  6 | Linda | Black  |        2 |             3 |
|  7 | Ed    | Jones  |        2 |             1 |
|  8 | June  | Wilson |        3 |             1 |
|  9 | Andy  | Smith  |        1 |             3 |
+----+-------+--------+----------+---------------+
5 rows in set (0.00 sec)

結果與查詢 SELECT * FROM employees WHERE id BETWEEN 5 AND 9 取得的結果相同。

若要從多個分割區取得資料列，請以逗號分隔清單的形式提供其名稱。例如，SELECT * FROM employees PARTITION (p1, p2) 會傳回分割區 p1 和 p2 中的所有資料列，同時排除其餘分割區中的資料列。

可以透過使用 PARTITION 選項來改寫任何針對已分割區表格的有效查詢，以將結果限制為一個或多個想要的分割區。您可以使用 WHERE 條件、ORDER BY 和 LIMIT 選項等等。您也可以搭配 HAVING 和 GROUP BY 選項使用彙總函式。當在先前定義的 employees 表格上執行時，下列每個查詢都會產生有效結果：

mysql> SELECT * FROM employees PARTITION (p0, p2)
    ->     WHERE lname LIKE 'S%';
+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
|  4 | Jim   | Smith |        2 |             4 |
| 11 | Jill  | Stone |        1 |             4 |
+----+-------+-------+----------+---------------+
2 rows in set (0.00 sec)

mysql> SELECT id, CONCAT(fname, ' ', lname) AS name
    ->     FROM employees PARTITION (p0) ORDER BY lname;
+----+----------------+
| id | name           |
+----+----------------+
|  3 | Ellen Johnson  |
|  4 | Jim Smith      |
|  1 | Bob Taylor     |
|  2 | Frank Williams |
+----+----------------+
4 rows in set (0.06 sec)

mysql> SELECT store_id, COUNT(department_id) AS c
    ->     FROM employees PARTITION (p1,p2,p3)
    ->     GROUP BY store_id HAVING c > 4;
+---+----------+
| c | store_id |
+---+----------+
| 5 |        2 |
| 5 |        3 |
+---+----------+
2 rows in set (0.00 sec)

使用分割區選擇的陳述式可以用於任何受支援的分割區類型表格。當使用 [LINEAR] HASH 或 [LINEAR] KEY 分割區建立表格，而且未指定分割區的名稱時，MySQL 會自動將分割區命名為 p0、p1、p2、...、pN-1，其中 N 是分割區的數量。對於未明確命名的子分割區，MySQL 會自動將每個分割區 pX 中的子分割區命名為 pXsp0、pXsp1、pXsp2、...、pXspM-1，其中 M 是子分割區的數量。當針對此表格執行 SELECT (或其他允許明確分割區選擇的 SQL 陳述式) 時，您可以在 PARTITION 選項中使用這些產生的名稱，如下所示：

mysql> CREATE TABLE employees_sub  (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     fname VARCHAR(25) NOT NULL,
    ->     lname VARCHAR(25) NOT NULL,
    ->     store_id INT NOT NULL,
    ->     department_id INT NOT NULL,
    ->     PRIMARY KEY pk (id, lname)
    -> )
    ->     PARTITION BY RANGE(id)
    ->     SUBPARTITION BY KEY (lname)
    ->     SUBPARTITIONS 2 (
    ->         PARTITION p0 VALUES LESS THAN (5),
    ->         PARTITION p1 VALUES LESS THAN (10),
    ->         PARTITION p2 VALUES LESS THAN (15),
    ->         PARTITION p3 VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (1.14 sec)

mysql> INSERT INTO employees_sub   # reuse data in employees table
    ->     SELECT * FROM employees;
Query OK, 18 rows affected (0.09 sec)
Records: 18  Duplicates: 0  Warnings: 0

mysql> SELECT id, CONCAT(fname, ' ', lname) AS name
    ->     FROM employees_sub PARTITION (p2sp1);
+----+---------------+
| id | name          |
+----+---------------+
| 10 | Lou Waters    |
| 14 | Fred Goldberg |
+----+---------------+
2 rows in set (0.00 sec)

您也可以在 INSERT ... SELECT 陳述式的 SELECT 部分中使用 PARTITION 選項，如下所示：

mysql> CREATE TABLE employees_copy LIKE employees;
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO employees_copy
    ->     SELECT * FROM employees PARTITION (p2);
Query OK, 5 rows affected (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM employees_copy;
+----+--------+----------+----------+---------------+
| id | fname  | lname    | store_id | department_id |
+----+--------+----------+----------+---------------+
| 10 | Lou    | Waters   |        2 |             4 |
| 11 | Jill   | Stone    |        1 |             4 |
| 12 | Roger  | White    |        3 |             2 |
| 13 | Howard | Andrews  |        1 |             2 |
| 14 | Fred   | Goldberg |        3 |             3 |
+----+--------+----------+----------+---------------+
5 rows in set (0.00 sec)

分割區選擇也可以與聯結搭配使用。假設我們使用此處顯示的陳述式建立並填入兩個表格：

CREATE TABLE stores (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    city VARCHAR(30) NOT NULL
)
    PARTITION BY HASH(id)
    PARTITIONS 2;

INSERT INTO stores VALUES
    ('', 'Nambucca'), ('', 'Uranga'),
    ('', 'Bellingen'), ('', 'Grafton');

CREATE TABLE departments  (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL
)
    PARTITION BY KEY(id)
    PARTITIONS 2;

INSERT INTO departments VALUES
    ('', 'Sales'), ('', 'Customer Service'),
    ('', 'Delivery'), ('', 'Accounting');

您可以從聯結中的任何或所有表格明確選擇分割區 (或子分割區，或兩者)。(用於從給定表格中選擇分割區的 PARTITION 選項緊接在表格的名稱之後，在所有其他選項之前，包括任何表格別名。)例如，下列查詢會取得在銷售或運送部門 (departments 表格的分割區 p1) 工作，而且在 Nambucca 和 Bellingen 這兩個城市的商店 (stores 表格的分割區 p0) 中工作的所有員工的姓名、員工 ID、部門和城市：

mysql> SELECT
    ->     e.id AS 'Employee ID', CONCAT(e.fname, ' ', e.lname) AS Name,
    ->     s.city AS City, d.name AS department
    -> FROM employees AS e
    ->     JOIN stores PARTITION (p1) AS s ON e.store_id=s.id
    ->     JOIN departments PARTITION (p0) AS d ON e.department_id=d.id
    -> ORDER BY e.lname;
+-------------+---------------+-----------+------------+
| Employee ID | Name          | City      | department |
+-------------+---------------+-----------+------------+
|          14 | Fred Goldberg | Bellingen | Delivery   |
|           5 | Mary Jones    | Nambucca  | Sales      |
|          17 | Mark Morgan   | Bellingen | Delivery   |
|           9 | Andy Smith    | Nambucca  | Delivery   |
|           8 | June Wilson   | Bellingen | Sales      |
+-------------+---------------+-----------+------------+
5 rows in set (0.00 sec)

如需 MySQL 中聯結的一般資訊，請參閱 第 15.2.13.2 節，「JOIN 子句」。

當 PARTITION 選項與 DELETE 陳述式搭配使用時，只會檢查選項中列出的那些分割區 (和子分割區，如果有的話) 是否有要刪除的資料列。任何其他分割區都會被忽略，如下所示：

mysql> SELECT * FROM employees WHERE fname LIKE 'j%';
+----+-------+--------+----------+---------------+
| id | fname | lname  | store_id | department_id |
+----+-------+--------+----------+---------------+
|  4 | Jim   | Smith  |        2 |             4 |
|  8 | June  | Wilson |        3 |             1 |
| 11 | Jill  | Stone  |        1 |             4 |
+----+-------+--------+----------+---------------+
3 rows in set (0.00 sec)

mysql> DELETE FROM employees PARTITION (p0, p1)
    ->     WHERE fname LIKE 'j%';
Query OK, 2 rows affected (0.09 sec)

mysql> SELECT * FROM employees WHERE fname LIKE 'j%';
+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
| 11 | Jill  | Stone |        1 |             4 |
+----+-------+-------+----------+---------------+
1 row in set (0.00 sec)

只會刪除分割區 p0 和 p1 中符合 WHERE 條件的兩個資料列。正如您在第二次執行 SELECT 時的結果所見，表格中仍然存在符合 WHERE 條件的資料列，但該資料列位於不同的分割區 (p2) 中。

使用明確分割區選擇的 UPDATE 陳述式也以相同方式運作；在判斷要更新的資料列時，只會考量 PARTITION 選項所參考的分割區中的資料列，如下列執行陳述式所示：

mysql> UPDATE employees PARTITION (p0) 
    ->     SET store_id = 2 WHERE fname = 'Jill';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> SELECT * FROM employees WHERE fname = 'Jill';
+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
| 11 | Jill  | Stone |        1 |             4 |
+----+-------+-------+----------+---------------+
1 row in set (0.00 sec)

mysql> UPDATE employees PARTITION (p2)
    ->     SET store_id = 2 WHERE fname = 'Jill';
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM employees WHERE fname = 'Jill';
+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
| 11 | Jill  | Stone |        2 |             4 |
+----+-------+-------+----------+---------------+
1 row in set (0.00 sec)

同樣地，當 PARTITION 與 DELETE 搭配使用時，只會檢查分割區清單中命名的分割區中的資料列是否要刪除。

對於插入列的陳述式，其行為有所不同，如果找不到合適的分割區，將導致陳述式失敗。對於 INSERT 和 REPLACE 陳述式都是如此，如下所示

mysql> INSERT INTO employees PARTITION (p2) VALUES (20, 'Jan', 'Jones', 1, 3);
ERROR 1729 (HY000): Found a row not matching the given partition set
mysql> INSERT INTO employees PARTITION (p3) VALUES (20, 'Jan', 'Jones', 1, 3);
Query OK, 1 row affected (0.07 sec)

mysql> REPLACE INTO employees PARTITION (p0) VALUES (20, 'Jan', 'Jones', 3, 2);
ERROR 1729 (HY000): Found a row not matching the given partition set

mysql> REPLACE INTO employees PARTITION (p3) VALUES (20, 'Jan', 'Jones', 3, 2);
Query OK, 2 rows affected (0.09 sec)

對於使用 InnoDB 儲存引擎將多列寫入分割資料表的陳述式：如果 VALUES 後面清單中的任何列無法寫入 partition_names 清單中指定的其中一個分割區，則整個陳述式將失敗且不會寫入任何列。以下範例針對 INSERT 陳述式顯示，重複使用先前建立的 employees 資料表

mysql> ALTER TABLE employees
    ->     REORGANIZE PARTITION p3 INTO (
    ->         PARTITION p3 VALUES LESS THAN (20),
    ->         PARTITION p4 VALUES LESS THAN (25),
    ->         PARTITION p5 VALUES LESS THAN MAXVALUE
    ->     );
Query OK, 6 rows affected (2.09 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE employees\G
*************************** 1. row ***************************
       Table: employees
Create Table: CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fname` varchar(25) NOT NULL,
  `lname` varchar(25) NOT NULL,
  `store_id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (15) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (20) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (25) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql> INSERT INTO employees PARTITION (p3, p4) VALUES
    ->     (24, 'Tim', 'Greene', 3, 1),  (26, 'Linda', 'Mills', 2, 1);
ERROR 1729 (HY000): Found a row not matching the given partition set

mysql> INSERT INTO employees PARTITION (p3, p4, p5) VALUES
    ->     (24, 'Tim', 'Greene', 3, 1),  (26, 'Linda', 'Mills', 2, 1);
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

上述情況對於寫入多列的 INSERT 陳述式和 REPLACE 陳述式都適用。

對於採用提供自動分割的儲存引擎（例如 NDB）的資料表，分割區選擇會停用。


上一步   首頁   上一層   下一步
相關文件
MySQL 9.0 版本注意事項
下載本手冊
PDF (US Ltr) - 40.0Mb
PDF (A4) - 40.1Mb
Man Pages (TGZ) - 258.2Kb
Man Pages (Zip) - 365.3Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb