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

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 選項會緊跟在表格名稱之後，在所有其他選項之前，包括任何表格別名)。例如，下列查詢會取得在銷售或運送部門工作的所有員工的姓名、員工 ID、部門和城市 (部門表格的分割區 p1)，這些員工在楠巴卡和貝林根 (商店表格的分割區 p0) 的其中一個城市中的商店工作

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 8.4 版本資訊
下載本手冊
PDF (US Ltr) - 39.9Mb
PDF (A4) - 40.0Mb
Man Pages (TGZ) - 258.5Kb
Man Pages (Zip) - 365.5Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb