26.6 Restrictions and Limitations on Partitioning — MySQL 8.4

26.6 Ограничения и пределы при разбиении таблиц

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

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

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

Список SQL-функций, разрешенных в выражениях для разбиения таблиц, см. в Разделе 26.6.3, “Ограничения разбиения, связанные с функциями”.

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

Оператор DIV также поддерживается; оператор / не разрешен.

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

Режим сервера SQL. Таблицы с пользовательским определением разбиения не сохраняют режим SQL, действующий в момент их создания. Как обсуждалось в другом месте этого руководства (см. Раздел 7.1.11, “Режимы SQL сервера”), результаты многих функций и операторов MySQL могут меняться в зависимости от режима SQL сервера. Следовательно, изменение режима SQL в любое время после создания разбиений таблиц может привести к существенным изменениям в поведении таких таблиц и легко к повреждению или потере данных. По этим причинам настоятельно рекомендуется не изменять режим 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

См. также Раздел 7.1.11, “Режимы SQL сервера”.

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

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

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

См. также Максимальное количество разбиений.
Блокировки таблиц. Обычно процесс, выполняющий операцию разбиения таблицы, накладывает запись блокировку на таблицу. Чтение из таких таблиц практически не затронуто; ожидающие операции INSERT и UPDATE выполняются после завершения операции разбиения. Для InnoDB-специфических исключений из этого ограничения см. Операции разбиения.
Индексы; обрезка разбиений. Как и в случае с неразделенными таблицами, правильное использование индексов может значительно ускорить запросы к разнесенным таблицам. Кроме того, проектирование разнесенных таблиц и запросов к этим таблицам с использованием обрезки разбиений может значительно улучшить производительность. Подробности см. в Разделе 26.4, “Обрезка разбиений”.

Поддержка вычисления условий индекса для разнесенных таблиц. См. Раздел 10.2.1.6, “Оптимизация с вычислением условий индекса”.
Производительность с LOAD DATA. В MySQL 8.4, LOAD DATA использует буферизацию для повышения производительности. Следует помнить, что буфер использует 130 КБ памяти на разбиение для достижения этого.

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

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

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

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

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

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

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

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

END_OF_DOCUMENT_MARKER ```

ДОБАВЛЕНИЕ СТОЛБЦА ... ALGORITHM=INSTANT. После выполнения ALTER TABLE ... ADD COLUMN ... ALGORITHM=INSTANT над таблицей с разбиением, обмен разделами с этой таблицей больше невозможен.

Воздействие на операторы REPLACE при изменении первичных ключей. В некоторых случаях (см. Раздел 26.6.1, «Ключи разбиения, первичные ключи и уникальные ключи») может потребоваться изменить первичный ключ таблицы. Обратите внимание, что если ваше приложение использует операторы REPLACE, то результаты этих операторов могут существенно измениться. Подробнее см. Раздел 15.2.12, «Оператор REPLACE» и пример.

Индексы FULLTEXT. Таблицы с разбиением не поддерживают индексы или поиск FULLTEXT.

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

Временные таблицы. Временные таблицы не могут быть разбиты.

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

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

Существуют два исключения из этого ограничения:

При разбиении по [LINEAR] KEY можно использовать столбцы любого допустимого типа данных MySQL, кроме TEXT или BLOB в качестве ключей разбиения, так как внутренние функции хэширования ключей производят правильный тип данных из этих типов. Например, следующие два оператора 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, а первичный ключ использует все три столбца и задаёт префикс для одного из них. Этот оператор отклоняется с ошибкой, как показано здесь:

mysql> USE d;
Database changed
mysql> CREATE TABLE t1 (
    ->     a VARCHAR(10000),
    ->     b VARCHAR(25),
    ->     c VARCHAR(10),
    ->     PRIMARY KEY (a(10), b, c)
    -> ) PARTITION BY KEY() PARTITIONS 2;
ERROR 6123 (HY000): Column 'd.t1.a' having prefix key part 'a(10)' in the
PARTITION BY KEY() clause is not supported.

Общие сведения о разбиении таблиц по ключу см. в Разделе 26.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 игнорируются (см. баг #32091). Вы можете использовать эти параметры для отдельных разделов или подраздёлов таблиц InnoDB. Каталог, указанный в условии DATA DIRECTORY, должен быть известен InnoDB. Дополнительная информация находится в разделе об использовании условия DATA DIRECTORY.

Ремонт и перестройка таблиц с разбиением. Операторы CHECK TABLE, OPTIMIZE TABLE, ANALYZE TABLE и REPAIR TABLE поддерживаются для таблиц с разбиением.

Кроме того, вы можете использовать ALTER TABLE ... REBUILD PARTITION для перестройки одного или нескольких разделов таблицы с разбиением; ALTER TABLE ... REORGANIZE PARTITION также приводит к перестройке разделов. Подробнее см. в разделе 15.1.9 «Оператор ALTER TABLE».

ANALYZE, CHECK, OPTIMIZE, REPAIR и TRUNCATE операции поддерживаются для подраздёлов. См. Раздел 15.1.9.1 «Операции ALTER TABLE для разбиения».

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