Spec-Zone.ru › MySQL 5.7

22.5 Выбор разбиений

MySQL 5.7 поддерживает явный выбор разбиений и подразбиений, которые при выполнении оператора должны проверяться на наличие строк, соответствующих заданному WHERE условию. Выбор разбиений похож на обрезку разбиений, так как проверяются только определённые разбиения на соответствие, но отличается в двух ключевых аспектах:

  1. Разбиения, которые необходимо проверить, указываются издателем оператора, в отличие от автоматической обрезки разбиений.

  2. В то время как обрезка разбиений применяется только к запросам, явный выбор разбиений поддерживается как для запросов, так и для ряда операторов DML.

Операторы SQL, поддерживающие явный выбор разбиений, перечислены здесь:

  • SELECT

  • DELETE

  • INSERT

  • REPLACE

  • UPDATE

  • LOAD DATA.

  • LOAD XML.

Остальная часть этого раздела описывает явный выбор разбиений, как он применяется к только что перечисленным операторам, и приводит некоторые примеры.

Явный выбор разбиений реализуется с помощью 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)

Операторы, использующие выбор разбиений, могут быть применены к таблицам, использующим любой из типов разбиений, поддерживаемых в MySQL 5.7. Когда таблица создаётся с использованием [LINEAR] HASH или [LINEAR] KEY разбиений и имена разбиений не заданы, MySQL автоматически назначает разбиениям имена p0, p1, p2, ..., pN-1, где N — количество разбиений. Для подразбиений, не имеющих явных имён, MySQL автоматически присваивает им имена pX, pXsp0, pXsp1, ..., 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   # re-use 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 опцию в части SELECT оператора INSERT ... 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)

Выбор разбиений также может использоваться с объединениями. Предположим, что мы создали и заполнили две таблицы с помощью операторов, показанных здесь:

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 для выбора разбиений из заданной таблицы сразу следует за именем таблицы, перед всеми другими опциями, включая любые псевдонимы таблиц.) Например, следующий запрос получает имя, идентификатор сотрудника, отдел и город всех сотрудников, работающих в отделе продаж или доставки (разбиение p1 таблицы departments) в магазинах в любом из городов Намбукка и Беллинген (разбиение p0 таблицы stores):

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.9.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=latin1
/*!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, записывающим несколько строк.

В MySQL 5.7.1 и более поздних версиях выбор разбиений отключён для таблиц, использующих движок хранения, обеспечивающий автоматическое разбиение, такой как NDB. (Ошибка #14827952)

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

Spec-Zone.ru

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