特定の WHERE 条件に一致する行のパーティションおよびサブパーティションの明示的な選択がサポートされています。 パーティション選択は、特定のパーティションのみで一致がチェックされる点でパーティションプルーニングと似ていますが、2 つの重要な点で異なります。

明示的なパーティション選択をサポートする SQL ステートメントを次に一覧します。

このセクションの残りの部分では、上記に一覧したステートメントに一般的に適用される明示的パーティション選択について説明し、いくつかの例を示します。

明示的パーティション選択は、PARTITION オプションを使用して実装されます。 サポートされるすべてのステートメントについて、このオプションは次のような構文を使用します。

      PARTITION (partition_names)

      partition_names:
          partition_name, ...

このオプションは常に、パーティションが属するテーブルの名前の後ろに続けます。partition_names は、使用されるパーティションまたはサブパーティションのカンマ区切りのリストです。 このリスト内の各名前は、指定されたテーブルの既存のパーティションまたはサブパーティションの名前である必要があります。パーティションまたはサブパーティションが見つからない場合、ステートメントは次のエラーで失敗します (partition 'partition_name' doesn't exist)。 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 オプションを使用して、結果を 1 つ以上の目的のパーティションに制限するように書き直すことができます。 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)

次のように PARTITION オプションを INSERT ... SELECT ステートメントの SELECT 部分に使用することもできます。

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)

パーティション選択は結合と一緒に使用することもできます。 次のステートメントを使用して 2 つのテーブルを作成して移入するとします。

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 オプションは、テーブルのエイリアスを含む他のすべてのオプションの直前のテーブルの名前に続きます。) たとえば、次のクエリーは都市 Nambucca および Bellingen (stores テーブルのパーティション p0) のいずれかの店舗の販売部門または配送部門 (departments テーブルのパーティション p1) で働いているすべての従業員の、名前、従業員 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 での結合の一般情報については、セクション13.2.10.2「JOIN 句」を参照してください。

DELETE ステートメントで PARTITION オプションを使用すると、オプションにリストされているパーティション (およびサブパーティション (ある場合)) でのみ削除される行がチェックされます。 次のようにほかのパーティションは無視されます。

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)

WHERE 条件と一致するパーティション p0 および p1 内の 2 つの行のみが削除されました。 SELECT を 2 回目に実行したときの結果から確認できるように、WHERE 条件に一致する 1 行がテーブルに残っていますが、別のパーティション (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)

同様に、DELETE 付きで PARTITION を使用すると、パーティションリストに指定されたパーティション内の行のみが削除をチェックされます。

行を挿入するステートメントの動作は、適切なパーティションが見つからないとステートメントが失敗する点が異なります。 これは、次のように 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 リストで指定されたいずれかのパーティションに書き込めない場合、ステートメント全体が失敗し、行は書き込まれません。 これについては、次の例 (employees テーブルを再使用) の INSERT ステートメントで示されています。

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
/*!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 などの自動パーティション分割を提供するストレージエンジンを使用するテーブルでは無効になっています。