22.6 Restrictions and Limitations on Partitioning — MySQL 5.7

22.6 Ограничения и пределы при разбиении

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

Запрещённые конструкции. Следующие конструкции не допускаются в выражениях разбиения:

Хранимые процедуры, хранимые функции, загружаемые функции или плагины.
Объявленные переменные или переменные пользователя.

Список SQL-функций, разрешённых в выражениях разбиения, см. в разделе 22.6.3 «Ограничения разбиения, связанные с функциями».

Арифметические и логические операторы. Использование арифметических операторов +, - и * разрешено в выражениях разбиения. Однако результат должен быть целочисленным значением или NULL (за исключением разбиения [LINEAR] KEY, как обсуждалось в других разделах этой главы; см. раздел 22.2 «Типы разбиения» для получения дополнительной информации).

Оператор DIV также поддерживается, а оператор / не разрешён. (Ошибка #30188, Ошибка #33182)

Битовые операторы |, &, ^, <<, >> и ~ не разрешены в выражениях разбиения.

Выражения HANDLER. Ранее оператор HANDLER не поддерживался с таблицами, разбитыми на разделы. Это ограничение устранено начиная с MySQL 5.7.1.

Режим сервера SQL. Таблицы с пользовательским разбиением не сохраняют режим SQL, действующий в момент их создания. Как обсуждалось в разделе 5.1.10 «Режимы SQL сервера», результаты многих функций и операторов MySQL могут изменяться в зависимости от режима SQL сервера. Поэтому изменение режима SQL в любое время после создания таблиц с разбиением может привести к существенным изменениям в поведении таких таблиц и легко может привести к повреждению или потере данных. По этим причинам настоятельно рекомендуется никогда не изменять режим SQL сервера после создания таблиц с разбиением.

Примеры. Приведенные ниже примеры иллюстрируют некоторые изменения в поведении таблиц с разбиением из-за изменения режима SQL сервера:

Обработка ошибок. Предположим, что вы создаёте таблицу с разбиением, выражение разбиения которой такое как column DIV 0 или column MOD 0, как показано здесь:
```
mysql> CREATE TABLE tn (c1 INT)
    ->     PARTITION BY LIST(1 DIV c1) (
    ->       PARTITION p0 VALUES IN (NULL),
    ->       PARTITION p1 VALUES IN (1)
    -> );
Query OK, 0 rows affected (0.05 sec)
```
По умолчанию MySQL возвращает NULL для результата деления на ноль, без вывода каких-либо ошибок:
```
mysql> SELECT @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)


mysql> INSERT INTO tn VALUES (NULL), (0), (1);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
```
Однако, при изменении режима SQL сервера на обработку деления на ноль как ошибки и принудительном соблюдении строгой обработки ошибок, то же самое утверждение INSERT терпит неудачу, как показано ниже:
```
mysql> SET sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tn VALUES (NULL), (0), (1);
ERROR 1365 (22012): Division by 0
```

Доступность таблицы. Иногда изменение режима SQL сервера может сделать таблицы с разбиением непригодными для использования. Следующее утверждение CREATE TABLE может быть выполнено успешно только в том случае, если режим NO_UNSIGNED_SUBTRACTION активен:

mysql> SELECT @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE tu (c1 BIGINT UNSIGNED)
    ->   PARTITION BY RANGE(c1 - 10) (
    ->     PARTITION p0 VALUES LESS THAN (-5),
    ->     PARTITION p1 VALUES LESS THAN (0),
    ->     PARTITION p2 VALUES LESS THAN (5),
    ->     PARTITION p3 VALUES LESS THAN (10),
    ->     PARTITION p4 VALUES LESS THAN (MAXVALUE)
    -> );
ERROR 1563 (HY000): Partition constant is out of partition function domain

mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@sql_mode;
+-------------------------+
| @@sql_mode              |
+-------------------------+
| NO_UNSIGNED_SUBTRACTION |
+-------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE tu (c1 BIGINT UNSIGNED)
    ->   PARTITION BY RANGE(c1 - 10) (
    ->     PARTITION p0 VALUES LESS THAN (-5),
    ->     PARTITION p1 VALUES LESS THAN (0),
    ->     PARTITION p2 VALUES LESS THAN (5),
    ->     PARTITION p3 VALUES LESS THAN (10),
    ->     PARTITION p4 VALUES LESS THAN (MAXVALUE)
    -> );
Query OK, 0 rows affected (0.05 sec)

Если вы удалите режим SQL сервера NO_UNSIGNED_SUBTRACTION после создания tu, вы можете больше не иметь доступа к этой таблице:

mysql> SET sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM tu;
ERROR 1563 (HY000): Partition constant is out of partition function domain
mysql> INSERT INTO tu VALUES (20);
ERROR 1563 (HY000): Partition constant is out of partition function domain

См. также раздел 5.1.10 «Режимы SQL сервера».

Режимы SQL сервера также влияют на репликацию таблиц с разбиением. Различные режимы SQL на источнике и реплике могут привести к тому, что выражения разбиения будут оцениваться по-разному; это может привести к тому, что распределение данных между разделами будет отличаться в исходной копии и копии реплики данной таблицы, и даже может привести к тому, что вставки в таблицы с разбиением, успешно выполняющиеся на источнике, будут неудачными на реплике. Для достижения наилучших результатов всегда следует использовать один и тот же режим SQL сервера на источнике и на реплике.

Соображения по производительности. Ниже перечислены некоторые эффекты операций разбиения на производительность:

Операции с файловой системой. Операции разбиения и переразбиения (такие как ALTER TABLE с PARTITION BY ..., REORGANIZE PARTITION или REMOVE PARTITIONING) зависят от операций с файловой системой для своей реализации. Это означает, что скорость этих операций зависит от таких факторов, как тип файловой системы и её характеристики, скорость диска, подкачка, эффективность обработки файлов операционной системой, а также параметры и переменные сервера MySQL, относящиеся к обработке файлов. В частности, следует убедиться, что large_files_support включен и что open_files_limit установлен должным образом. Для разбиение таблиц, использующих движок хранения MyISAM, увеличение myisam_max_sort_file_size может повысить производительность; операции разбиения и переразбиения, затрагивающие таблицы InnoDB, могут быть сделаны более эффективными путём включения innodb_file_per_table.

См. также Максимальное количество разделов.
Использование дескрипторов файлов MyISAM и разбиения. Для таблицы разбиения MyISAM MySQL использует 2 дескриптора файла для каждого раздела для каждой такой открытой таблицы. Это означает, что вам нужно больше дескрипторов файлов для выполнения операций над таблицей разбиения MyISAM, чем над таблицей, идентичной ей, за исключением того, что последняя таблица не разбита, особенно при выполнении операций ALTER TABLE.

Предположим таблицу MyISAM t с 100 разделами, такую как таблица, созданная этим SQL-заявлением:
```
CREATE TABLE t (c1 VARCHAR(50))
PARTITION BY KEY (c1) PARTITIONS 100
ENGINE=MYISAM;
```
Примечание

Для краткости мы используем разбиение KEY для таблицы, показанной в этом примере, но использование дескрипторов файлов, как описано здесь, применимо ко всем разбиение таблицам MyISAM, независимо от типа используемого разбиения. Разбиение таблицы, использующие другие движки хранения, такие как InnoDB, не затронуты этой проблемой.

Теперь предположим, что вы хотите переразбить t так, чтобы у неё было 101 раздел, используя показанное здесь утверждение:
```
ALTER TABLE t PARTITION BY KEY (c1) PARTITIONS 101;
```
Для обработки этого ALTER TABLE оператора MySQL использует 402 дескриптора файлов — то есть по два для каждого из 100 исходных разделов, плюс два для каждого из 101 нового раздела. Это связано с тем, что все разделы (старые и новые) должны быть открыты одновременно во время реорганизации данных таблицы. Рекомендуется, если вы ожидаете выполнять такие операции, убедиться, что переменная системы open_files_limit не установлена слишком низко, чтобы вместить их.
Блокировки таблиц. Как правило, процесс, выполняющий операцию разбиения таблицы, захватывает запись блокировку на таблице. Чтение из таких таблиц практически не затрагивается; ожидаемые операции INSERT и UPDATE выполняются, как только операция разбиения завершена. Для InnoDB-специфических исключений из этого ограничения см. Операции разбиения.
Движок хранения. Операции разбиения, запросы и операции обновления, как правило, выполняются быстрее с таблицами MyISAM, чем с таблицами InnoDB или NDB.
Индексы; отсечение разбиений. Как и в неразбитых таблицах, правильное использование индексов может значительно ускорить запросы к разбитым таблицам. Кроме того, проектирование разбитых таблиц и запросов к этим таблицам для использования отсечения разбиений может значительно повысить производительность. Для получения дополнительной информации см. Раздел 22.4, «Отсечение разбиений».

Ранее отсечение условия индекса для разбитых таблиц не поддерживалось. Это ограничение было устранено в MySQL 5.7.3. См. Раздел 8.2.1.5, «Оптимизация отсечения условия индекса».
Производительность с LOAD DATA. В MySQL 5.7, LOAD DATA использует буферизацию для повышения производительности. Следует помнить, что буфер использует 130 КБ памяти на раздел для достижения этого.

Максимальное количество разделов. Максимальное возможное количество разделов для заданной таблицы, не использующей движок хранения NDB, составляет 8192. Это число включает подразделы.

Максимальное возможное число пользовательских разделов для таблицы, использующей движок хранения NDB, определяется в соответствии с версией используемого программного обеспечения NDB Cluster, количеством узлов данных и другими факторами. Для получения дополнительной информации см. NDB и пользовательское разбиение.

Если при создании таблиц с большим количеством разделов (но меньше максимального), вы столкнетесь с сообщением об ошибке, таким как Получена ошибка ... от движка хранения: Нет ресурсов при открытии файла, вы можете решить проблему, увеличив значение переменной системы open_files_limit. Однако это зависит от операционной системы и может быть недоступно или нежелательно на всех платформах; см. Раздел B.3.2.16, «Файл не найден и аналогичные ошибки» для получения дополнительной информации. В некоторых случаях использование большого количества (сотен) разделов также может быть нежелательным из-за других проблем, поэтому использование большего количества разделов не гарантирует автоматически лучших результатов.

См. также Операции с файловой системой.

Кэш запросов не поддерживается. Кэш запросов не поддерживается для разбиение таблиц и автоматически отключается для запросов, включающих разбитые таблицы. Кэш запросов не может быть включен для таких запросов.

Кэши ключей по разделу. В MySQL 5.7 поддерживаются кэши ключей для разбиение таблиц MyISAM, используя команды CACHE INDEX и LOAD INDEX INTO CACHE. Кэши ключей могут быть определены для одного, нескольких или всех разделов, а индексы для одного, нескольких или всех разделов могут быть предварительно загружены в кэши ключей.

Внешние ключи не поддерживаются для разбиение таблиц InnoDB. Разбиение таблицы, использующие движок хранения InnoDB, не поддерживают внешние ключи. Более конкретно, это означает, что следующие два утверждения верны:

Ни одно определение таблицы InnoDB, использующей пользовательское разбиение, не может содержать ссылок на внешние ключи; никакая таблица InnoDB, определение которой содержит ссылки на внешние ключи, не может быть разбита.
Ни одно определение таблицы InnoDB не может содержать ссылку на внешний ключ на разбитую таблицу; никакая таблица InnoDB с пользовательским разбиением не может содержать столбцы, на которые ссылаются внешние ключи.

Область действия перечисленных выше ограничений включает все таблицы, использующие движок хранения InnoDB. Команды CREATE TABLE и ALTER TABLE, которые могут привести к нарушению этих ограничений, не разрешены.

ALTER TABLE ... ORDER BY. Команда ALTER TABLE ... ORDER BY column, запущенная на разбитой таблице, приводит к упорядочению строк только внутри каждого раздела.

Влияние на команды REPLACE при изменении первичных ключей. В некоторых случаях (см. Раздел 22.6.1, «Ключи разбиения, первичные ключи и уникальные ключи») желательно изменить первичный ключ таблицы. Имейте в виду, что если ваше приложение использует команды REPLACE, то результаты этих команд могут быть значительно изменены. Для получения дополнительной информации и примера см. Раздел 13.2.8, «REPLACE Statement».

Индексы FULLTEXT. Разбиение таблицы не поддерживают индексы FULLTEXT или поиск, даже для разбитых таблиц, использующих движки хранения InnoDB или MyISAM.

Пространственные столбцы. Столбцы со пространственными типами данных, такими как POINT или GEOMETRY, не могут быть использованы в разбитых таблицах.

Временные таблицы. Временные таблицы не могут быть разбиты. (Ошибка #17497)

Таблицы логов. Невозможно разделить таблицы логов; оператор ALTER TABLE ... PARTITION BY ... для такой таблицы завершается с ошибкой.

Тип данных разделителя. Разделитель должен быть столбцом целочисленного типа или выражением, результатом которого является целое число. Выражения, использующие столбцы типа ENUM, использовать нельзя. Значение столбца или выражения также может быть NULL. (См. Раздел 22.2.7, «Как MySQL обрабатывает NULL при разбиении».)

Есть два исключения из этого ограничения:

При разбиении по [LINEAR] KEY можно использовать столбцы любого допустимого типа данных MySQL, кроме TEXT или BLOB, поскольку внутренние функции хеширования ключей MySQL производят правильный тип данных из этих типов. Например, следующие два оператора CREATE TABLE являются допустимыми:
```
CREATE TABLE tkc (c1 CHAR)
PARTITION BY KEY(c1)
PARTITIONS 4;

CREATE TABLE tke
    ( c1 ENUM('red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet') )
PARTITION BY LINEAR KEY(c1)
PARTITIONS 6;
```

При разбиении по RANGE COLUMNS или LIST COLUMNS можно использовать столбцы строкового, DATE, и DATETIME типов. Например, каждый из следующих операторов CREATE TABLE является допустимым:

CREATE TABLE rc (c1 INT, c2 DATE)
PARTITION BY RANGE COLUMNS(c2) (
    PARTITION p0 VALUES LESS THAN('1990-01-01'),
    PARTITION p1 VALUES LESS THAN('1995-01-01'),
    PARTITION p2 VALUES LESS THAN('2000-01-01'),
    PARTITION p3 VALUES LESS THAN('2005-01-01'),
    PARTITION p4 VALUES LESS THAN(MAXVALUE)
);

CREATE TABLE lc (c1 INT, c2 CHAR(1))
PARTITION BY LIST COLUMNS(c2) (
    PARTITION p0 VALUES IN('a', 'd', 'g', 'j', 'm', 'p', 's', 'v', 'y'),
    PARTITION p1 VALUES IN('b', 'e', 'h', 'k', 'n', 'q', 't', 'w', 'z'),
    PARTITION p2 VALUES IN('c', 'f', 'i', 'l', 'o', 'r', 'u', 'x', NULL)
);

Ни одно из предыдущих исключений не относится к столбцам типа BLOB или TEXT.

Подзапросы. Разделитель не может быть подзапросом, даже если этот подзапрос возвращает целое значение или NULL.

Префиксы индексов столбцов не поддерживаются для разбиения по ключу. При создании таблицы, разбиение которой основано на ключе, столбцы в ключе, использующие префиксы, не используются в функции разбиения таблицы. Рассмотрим следующий оператор CREATE TABLE, который имеет три столбца типа VARCHAR, а первичный ключ использует все три столбца и задаёт префиксы для двух из них:

CREATE TABLE t1 (
    a VARCHAR(10000),
    b VARCHAR(25),
    c VARCHAR(10),
    PRIMARY KEY (a(10), b, c(2))
) PARTITION BY KEY() PARTITIONS 2;

Этот оператор принимается, но результирующая таблица создаётся так, как будто вы выполнили следующий оператор, используя только столбец первичного ключа, который не включает префикс (столбец b) для разделителя:

CREATE TABLE t1 (
    a VARCHAR(10000),
    b VARCHAR(25),
    c VARCHAR(10),
    PRIMARY KEY (a(10), b, c(2))
) PARTITION BY KEY(b) PARTITIONS 2;

Не выводится никаких предупреждений или других указаний на это, за исключением случая, когда все столбцы, указанные для разделителя, используют префиксы, в этом случае оператор завершается с ошибкой, как показано здесь:

mysql> CREATE TABLE t2 (
    ->     a VARCHAR(10000),
    ->     b VARCHAR(25),
    ->     c VARCHAR(10),
    ->     PRIMARY KEY (a(10), b(5), c(2))
    -> ) PARTITION BY KEY() PARTITIONS 2;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the
table's partitioning function

Это также происходит при изменении или обновлении таких таблиц и включает случаи, когда столбцы, используемые в функции разбиения, неявно определены как столбцы первичного ключа таблицы путём использования пустого PARTITION BY KEY() предложения.

Это известная проблема, которая решена в MySQL 8.0 путём устаревания допускающего поведения; в MySQL 8.0, если в функцию разбиения таблицы включены столбцы, использующие префиксы, сервер записывает соответствующее предупреждение для каждого такого столбца или выводит описательную ошибку, если это необходимо. (Возможность использования столбцов с префиксами в ключах разбиения может быть полностью удалена в будущих версиях MySQL.)

Дополнительную информацию о разбиении таблиц по ключу см. в Разделе 22.2.5, «Разбиение по ключу».

Проблемы с подразбиениями. Подразбиения должны использовать разбиение по HASH или KEY. Только RANGE и LIST разбиения могут быть подразбиты; HASH и KEY разбиения не могут быть подразбиты.

SUBPARTITION BY KEY требует явного указания столбца или столбцов для подразбиения, в отличие от PARTITION BY KEY, где его можно опустить (в этом случае используется столбец первичного ключа таблицы по умолчанию). Рассмотрим таблицу, созданную этим оператором:

CREATE TABLE ts (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30)
);

Вы можете создать таблицу с теми же столбцами, разделив её по KEY, используя такой оператор:

CREATE TABLE ts (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30)
)
PARTITION BY KEY()
PARTITIONS 4;

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

CREATE TABLE ts (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30)
)
PARTITION BY KEY(id)
PARTITIONS 4;

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

mysql> CREATE TABLE ts (
    ->     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     name VARCHAR(30)
    -> )
    -> PARTITION BY RANGE(id)
    -> SUBPARTITION BY KEY()
    -> SUBPARTITIONS 4
    -> (
    ->     PARTITION p0 VALUES LESS THAN (100),
    ->     PARTITION p1 VALUES LESS THAN (MAXVALUE)
    -> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ')

mysql> CREATE TABLE ts (
    ->     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     name VARCHAR(30)
    -> )
    -> PARTITION BY RANGE(id)
    -> SUBPARTITION BY KEY(id)
    -> SUBPARTITIONS 4
    -> (
    ->     PARTITION p0 VALUES LESS THAN (100),
    ->     PARTITION p1 VALUES LESS THAN (MAXVALUE)
    -> );
Query OK, 0 rows affected (0.07 sec)

Это известная проблема (см. ошибку #51470).

Параметры DATA DIRECTORY и INDEX DIRECTORY. DATA DIRECTORY и INDEX DIRECTORY имеют следующие ограничения при использовании с разнесёнными таблицами:

Параметры таблицы DATA DIRECTORY и INDEX DIRECTORY игнорируются (см. ошибку #32091).
В Windows параметры DATA DIRECTORY и INDEX DIRECTORY не поддерживаются для отдельных разделов или подразделов таблиц MyISAM. Однако вы можете использовать DATA DIRECTORY для отдельных разделов или подразделов таблиц InnoDB.

Восстановление и перестроение разнесённых таблиц. Операторы CHECK TABLE, OPTIMIZE TABLE, ANALYZE TABLE и REPAIR TABLE поддерживаются для разнесённых таблиц.

Кроме того, вы можете использовать ALTER TABLE ... REBUILD PARTITION для перестройки одного или нескольких разделов разнесённой таблицы; ALTER TABLE ... REORGANIZE PARTITION также приводит к перестройке разделов. Дополнительную информацию об этих двух операторах см. в Разделе 13.1.8, «Оператор ALTER TABLE».

Начиная с MySQL 5.7.2, операции ANALYZE, CHECK, OPTIMIZE, REPAIR и TRUNCATE поддерживаются с подразбиениями. REBUILD также был допустимым синтаксисом до MySQL 5.7.5, хотя это не имело эффекта. (Ошибка #19075411, Ошибка #73130) См. также Раздел 13.1.8.1, «Операции ALTER TABLE с разбиениями».

mysqlcheck, myisamchk и myisampack не поддерживаются для разнесённых таблиц.

Параметр FOR EXPORT (FLUSH TABLES). Параметр FOR EXPORT оператора FLUSH TABLES не поддерживается для разнесённых таблиц InnoDB в MySQL 5.7.4 и более ранних версиях. (Ошибка #16943907)

Разделители имён файлов для разделов и подразделов. Имена файлов разнесённых таблиц и их подразделов включают сгенерированные разделители, такие как #P# и #SP#. Регистр таких разделителей может варьироваться и не должен использоваться для сравнения.