26.5 Выбор разбиений
Поддерживается явный выбор разбиений и подразбиений для строк, соответствующих заданному WHERE условию. Выбор разбиений похож на обрезку разбиений, в том смысле, что проверяются только определённые разбиения на соответствие, но отличается по двум ключевым моментам:
Разбиения, которые нужно проверить, указываются отправителем запроса, в отличие от автоматической обрезки разбиений.
В то время как обрезка разбиений применяется только к запросам, явный выбор разбиений поддерживается как для запросов, так и для ряда инструкций 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, ..., p, где N-1N — количество разбиений. Для подрабиений, не имеющих явных имён, MySQL автоматически назначает подрабиениям в каждом разбиении p имена Xp, Xsp0p, Xsp1p, ..., Xsp2p, где XspM-1M — количество подрабиений. При выполнении запроса к этой таблице, например 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 в части 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 см. в разделе 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.
© 2025 Oracle
Licensed under the GPLv2 License.
