В этом разделе рассматривается оптимизация, известная как удаление не нужных разделов. Основной принцип удаления не нужных разделов относительно прост и может быть описан как “Не сканировать разделы, где не может быть совпадающих значений”. Предположим, что у вас есть таблица с разделами t1, определённая этим оператором:

CREATE TABLE t1 (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    region_code TINYINT UNSIGNED NOT NULL,
    dob DATE NOT NULL
)
PARTITION BY RANGE( region_code ) (
    PARTITION p0 VALUES LESS THAN (64),
    PARTITION p1 VALUES LESS THAN (128),
    PARTITION p2 VALUES LESS THAN (192),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

Рассмотрим случай, когда вы хотите получить результаты из оператора SELECT , такого как этот:

SELECT fname, lname, region_code, dob
    FROM t1
    WHERE region_code > 125 AND region_code < 130;

Легко заметить, что ни одна из строк, которые должны быть возвращены, не находится ни в одном из разделов p0 или p3; то есть, нам нужно искать только в разделах p1 и p2, чтобы найти соответствующие строки. Таким образом, можно затратить гораздо меньше времени и усилий на поиск соответствующих строк, чем потребовалось бы для сканирования всех разделов в таблице. Это “удаление” ненужных разделов известно как удаление. Когда оптимизатор может использовать удаление разделов при выполнении этого запроса, выполнение запроса может быть в разы быстрее, чем тот же запрос к неразделённой таблице, содержащей те же определения столбцов и данные.

Оптимизатор может выполнять удаление, когда условие WHERE может быть сокращено до одного из следующих двух случаев:

В первом случае оптимизатор просто вычисляет выражение разбиения для заданного значения, определяет, в какой раздел входит это значение, и сканирует только этот раздел. Во многих случаях знак равенства может быть заменён другой арифметической операцией сравнения, включая <, >, <=, >= и <>. Некоторые запросы, использующие BETWEEN в предложении WHERE, также могут использовать удаление разделов. Смотрите примеры в более поздних разделах этого раздела.

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

MySQL может применять удаление разделов к операторам SELECT, DELETE и UPDATE. Оператор INSERT также обращается только к одному разделу на вставленную строку; это верно даже для таблицы, разнесённой по HASH или KEY, хотя это не отображается в выходных данных оператора EXPLAIN.

Удаление также может применяться к коротким диапазонам, которые оптимизатор может преобразовать в эквивалентные списки значений. Например, в предыдущем примере предложение WHERE может быть преобразовано в WHERE region_code IN (126, 127, 128, 129). Затем оптимизатор может определить, что первые два значения в списке находятся в разделе p1, оставшиеся два значения — в разделе p2, и что другие разделы не содержат соответствующих значений, поэтому их не нужно проверять на соответствие строк.

Оптимизатор также может выполнять удаление для условий WHERE, которые включают сравнения указанных типов по нескольким столбцам для таблиц, использующих RANGE COLUMNS или LIST COLUMNS разбиение.

Этот тип оптимизации может применяться всякий раз, когда выражение разбиения состоит из равенства или диапазона, который можно сократить до набора равенств, или когда выражение разбиения представляет собой возрастающую или убывающую взаимосвязь. Удаление также может применяться к таблицам, разнесённым по столбцу DATE или DATETIME, когда выражение разбиения использует функцию YEAR() или TO_DAYS(). Кроме того, в MySQL 5.7 удаление может применяться к таким таблицам, когда выражение разбиения использует функцию TO_SECONDS().

Предположим, что таблица t2, определённая как показано здесь, разделена по столбцу DATE:

CREATE TABLE t2 (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    region_code TINYINT UNSIGNED NOT NULL,
    dob DATE NOT NULL
)
PARTITION BY RANGE( YEAR(dob) ) (
    PARTITION d0 VALUES LESS THAN (1970),
    PARTITION d1 VALUES LESS THAN (1975),
    PARTITION d2 VALUES LESS THAN (1980),
    PARTITION d3 VALUES LESS THAN (1985),
    PARTITION d4 VALUES LESS THAN (1990),
    PARTITION d5 VALUES LESS THAN (2000),
    PARTITION d6 VALUES LESS THAN (2005),
    PARTITION d7 VALUES LESS THAN MAXVALUE
);

Следующие операторы, использующие t2, могут использовать удаление разделов:

SELECT * FROM t2 WHERE dob = '1982-06-23';

UPDATE t2 SET region_code = 8 WHERE dob BETWEEN '1991-02-15' AND '1997-04-25';

DELETE FROM t2 WHERE dob >= '1984-06-21' AND dob <= '1999-06-21'

В случае последнего оператора оптимизатор также может действовать следующим образом:

До сих пор мы рассматривали только примеры, использующие RANGE разделение, но удаление может применяться и с другими типами разделения.

Рассмотрим таблицу, разнесённую по LIST, где выражение разбиения возрастает или убывает, например, таблица t3, показанная здесь. (В этом примере для краткости предположим, что столбец region_code ограничен значениями от 1 до 10 включительно.)

CREATE TABLE t3 (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    region_code TINYINT UNSIGNED NOT NULL,
    dob DATE NOT NULL
)
PARTITION BY LIST(region_code) (
    PARTITION r0 VALUES IN (1, 3),
    PARTITION r1 VALUES IN (2, 5, 8),
    PARTITION r2 VALUES IN (4, 9),
    PARTITION r3 VALUES IN (6, 7, 10)
);

Для оператора, такого как SELECT * FROM t3 WHERE region_code BETWEEN 1 AND 3, оптимизатор определяет, в каких разделах находятся значения 1, 2 и 3 (r0 и r1) и пропускает остальные (r2 и r3).

Для таблиц, разнесённых по HASH или [LINEAR] KEY, удаление разделов также возможно в тех случаях, когда предложение WHERE использует простое = отношение по столбцу, используемому в выражении разбиения. Рассмотрим таблицу, созданную таким образом:

CREATE TABLE t4 (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    region_code TINYINT UNSIGNED NOT NULL,
    dob DATE NOT NULL
)
PARTITION BY KEY(region_code)
PARTITIONS 8;

Оператор, сравнивающий значение столбца с константой, может быть удалён:

UPDATE t4 WHERE region_code = 7;

Удаление также может использоваться для коротких диапазонов, так как оптимизатор может преобразовать такие условия в IN отношения. Например, используя ту же таблицу t4, как определена ранее, запросы, такие как эти, могут быть удалены:

SELECT * FROM t4 WHERE region_code > 2 AND region_code < 6;

SELECT * FROM t4 WHERE region_code BETWEEN 3 AND 5;

В обоих этих случаях предложение WHERE преобразуется оптимизатором в WHERE region_code IN (3, 4, 5).

DELETE FROM t4 WHERE region_code BETWEEN 4 AND 12;

Когда таблица разделена по HASH или [LINEAR] KEY, удаление может применяться только к целочисленным столбцам. Например, этот оператор не может использовать удаление, потому что dob является столбцом DATE:

SELECT * FROM t4 WHERE dob >= '2001-04-14' AND dob <= '2005-10-15';

Однако, если таблица хранит значения года в столбце INT, то запрос, имеющий WHERE year_col >= 2001 AND year_col <= 2005, может быть удалён.

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