22.2.7 How MySQL Partitioning Handles NULL — MySQL 5.7

22.2.7 Как MySQL Partitioning обрабатывает NULL

Разбиение в MySQL не запрещает NULL в качестве значения выражения разбиения, будь то значение столбца или значение выражения, заданного пользователем. Несмотря на то, что разрешается использовать NULL в качестве значения выражения, которое в противном случае должно возвращать целое число, важно помнить, что NULL не является числом. Реализация разбиения MySQL рассматривает NULL как меньшее любого не-NULL значения, точно так же, как и ORDER BY.

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

Обработка NULL с разбиением RANGE. Если вы вставляете строку в таблицу, разделенную по RANGE, так что значение столбца, используемое для определения раздела, равно NULL, строка вставляется в самый нижний раздел. Рассмотрим эти две таблицы в базе данных под названием p, созданные следующим образом:

mysql> CREATE TABLE t1 (
    ->     c1 INT,
    ->     c2 VARCHAR(20)
    -> )
    -> PARTITION BY RANGE(c1) (
    ->     PARTITION p0 VALUES LESS THAN (0),
    ->     PARTITION p1 VALUES LESS THAN (10),
    ->     PARTITION p2 VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE TABLE t2 (
    ->     c1 INT,
    ->     c2 VARCHAR(20)
    -> )
    -> PARTITION BY RANGE(c1) (
    ->     PARTITION p0 VALUES LESS THAN (-5),
    ->     PARTITION p1 VALUES LESS THAN (0),
    ->     PARTITION p2 VALUES LESS THAN (10),
    ->     PARTITION p3 VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (0.09 sec)

Вы можете увидеть разделы, созданные этими двумя CREATE TABLE операторами, используя следующий запрос к таблице PARTITIONS в базе данных INFORMATION_SCHEMA:

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
     >   FROM INFORMATION_SCHEMA.PARTITIONS
     >   WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 't_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t1         | p0             |          0 |              0 |           0 |
| t1         | p1             |          0 |              0 |           0 |
| t1         | p2             |          0 |              0 |           0 |
| t2         | p0             |          0 |              0 |           0 |
| t2         | p1             |          0 |              0 |           0 |
| t2         | p2             |          0 |              0 |           0 |
| t2         | p3             |          0 |              0 |           0 |
+------------+----------------+------------+----------------+-------------+
7 rows in set (0.00 sec)

(Для получения дополнительной информации об этой таблице см. Раздел 24.3.16, «Таблица INFORMATION_SCHEMA PARTITIONS».) Теперь давайте заполним каждую из этих таблиц одной строкой, содержащей NULL в столбце, используемом в качестве ключа разбиения, и проверим, что строки были вставлены с помощью пары SELECT операторов:

mysql> INSERT INTO t1 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+------+--------+
| id   | name   |
+------+--------+
| NULL | mothra |
+------+--------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t2;
+------+--------+
| id   | name   |
+------+--------+
| NULL | mothra |
+------+--------+
1 row in set (0.00 sec)

Вы можете увидеть, какие разделы используются для хранения вставленных строк, повторно выполнив предыдущий запрос к INFORMATION_SCHEMA.PARTITIONS и просмотрев вывод:

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
     >   FROM INFORMATION_SCHEMA.PARTITIONS
     >   WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 't_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t1         | p0             |          1 |             20 |          20 |
| t1         | p1             |          0 |              0 |           0 |
| t1         | p2             |          0 |              0 |           0 |
| t2         | p0             |          1 |             20 |          20 |
| t2         | p1             |          0 |              0 |           0 |
| t2         | p2             |          0 |              0 |           0 |
| t2         | p3             |          0 |              0 |           0 |
+------------+----------------+------------+----------------+-------------+
7 rows in set (0.01 sec)

Вы также можете продемонстрировать, что эти строки были сохранены в самом нижнем разделе каждой таблицы, удалив эти разделы, а затем повторно выполнив SELECT операторы:

mysql> ALTER TABLE t1 DROP PARTITION p0;
Query OK, 0 rows affected (0.16 sec)

mysql> ALTER TABLE t2 DROP PARTITION p0;
Query OK, 0 rows affected (0.16 sec)

mysql> SELECT * FROM t1;
Empty set (0.00 sec)

mysql> SELECT * FROM t2;
Empty set (0.00 sec)

(Для получения дополнительной информации о ALTER TABLE ... DROP PARTITION см. Раздел 13.1.8, «Оператор ALTER TABLE».)

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

CREATE TABLE tndate (
    id INT,
    dt DATE
)
PARTITION BY RANGE( YEAR(dt) ) (
    PARTITION p0 VALUES LESS THAN (1990),
    PARTITION p1 VALUES LESS THAN (2000),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

Как и другие функции MySQL, YEAR(NULL) возвращает NULL. Строка со значением столбца dt, равным NULL, обрабатывается так, как будто выражение разбиения вычислялось до значения, меньшего любого другого значения, и поэтому вставляется в раздел p0.

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

mysql> CREATE TABLE ts1 (
    ->     c1 INT,
    ->     c2 VARCHAR(20)
    -> )
    -> PARTITION BY LIST(c1) (
    ->     PARTITION p0 VALUES IN (0, 3, 6),
    ->     PARTITION p1 VALUES IN (1, 4, 7),
    ->     PARTITION p2 VALUES IN (2, 5, 8)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO ts1 VALUES (9, 'mothra');
ERROR 1504 (HY000): Table has no partition for value 9

mysql> INSERT INTO ts1 VALUES (NULL, 'mothra');
ERROR 1504 (HY000): Table has no partition for value NULL

В ts1 могут вставляться только строки, имеющие значение c1 между 0 и 8 включительно. NULL выходит за пределы этого диапазона, как и число 9. Мы можем создать таблицы ts2 и ts3, содержащие списки значений, содержащие NULL, как показано здесь:

mysql> CREATE TABLE ts2 (
    ->     c1 INT,
    ->     c2 VARCHAR(20)
    -> )
    -> PARTITION BY LIST(c1) (
    ->     PARTITION p0 VALUES IN (0, 3, 6),
    ->     PARTITION p1 VALUES IN (1, 4, 7),
    ->     PARTITION p2 VALUES IN (2, 5, 8),
    ->     PARTITION p3 VALUES IN (NULL)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE ts3 (
    ->     c1 INT,
    ->     c2 VARCHAR(20)
    -> )
    -> PARTITION BY LIST(c1) (
    ->     PARTITION p0 VALUES IN (0, 3, 6),
    ->     PARTITION p1 VALUES IN (1, 4, 7, NULL),
    ->     PARTITION p2 VALUES IN (2, 5, 8)
    -> );
Query OK, 0 rows affected (0.01 sec)

При определении списков значений для разбиения вы можете (и должны) рассматривать NULL так же, как любое другое значение. Например, оба VALUES IN (NULL) и VALUES IN (1, 4, 7, NULL) являются допустимыми, так же как и VALUES IN (1, NULL, 4, 7), VALUES IN (NULL, 1, 4, 7) и так далее. Вы можете вставить строку, содержащую NULL для столбца c1 в каждую из таблиц ts2 и ts3:

mysql> INSERT INTO ts2 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO ts3 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)

Выполняя соответствующий запрос к таблице схемы информации PARTITIONS, вы можете определить, какие разделы использовались для хранения только что вставленных строк (мы предполагаем, как и в предыдущих примерах, что разделенные таблицы были созданы в базе данных p):

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
     >   FROM INFORMATION_SCHEMA.PARTITIONS
     >   WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 'ts_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| ts2        | p0             |          0 |              0 |           0 |
| ts2        | p1             |          0 |              0 |           0 |
| ts2        | p2             |          0 |              0 |           0 |
| ts2        | p3             |          1 |             20 |          20 |
| ts3        | p0             |          0 |              0 |           0 |
| ts3        | p1             |          1 |             20 |          20 |
| ts3        | p2             |          0 |              0 |           0 |
+------------+----------------+------------+----------------+-------------+
7 rows in set (0.01 sec)

Как показано ранее в этом разделе, вы также можете проверить, какие разделы использовались для хранения строк, удалив эти разделы, а затем выполнив SELECT.

Обработка NULL с разбиением HASH и KEY. NULL обрабатывается несколько иначе для таблиц, разделенных по HASH или KEY. В этих случаях любое выражение разбиения, которое возвращает значение NULL, обрабатывается так, как будто его возвращаемое значение равно нулю. Мы можем проверить это поведение, изучив влияние на файловую систему создания таблицы, разделенной по HASH, и заполнения ее записью, содержащей соответствующие значения. Предположим, что у вас есть таблица th (также в базе данных p), созданная с помощью следующего оператора:

mysql> CREATE TABLE th (
    ->     c1 INT,
    ->     c2 VARCHAR(20)
    -> )
    -> PARTITION BY HASH(c1)
    -> PARTITIONS 2;
Query OK, 0 rows affected (0.00 sec)

Разделы этой таблицы можно просмотреть, используя показанный здесь запрос:

mysql> SELECT TABLE_NAME,PARTITION_NAME,TABLE_ROWS,AVG_ROW_LENGTH,DATA_LENGTH
     >   FROM INFORMATION_SCHEMA.PARTITIONS
     >   WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME ='th';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| th         | p0             |          0 |              0 |           0 |
| th         | p1             |          0 |              0 |           0 |
+------------+----------------+------------+----------------+-------------+
2 rows in set (0.00 sec)

TABLE_ROWS для каждого раздела равно 0. Теперь вставьте две строки в th, значения столбца c1 которых равны NULL и 0, и проверьте, что эти строки были вставлены, как показано здесь:

mysql> INSERT INTO th VALUES (NULL, 'mothra'), (0, 'gigan');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM th;
+------+---------+
| c1   | c2      |
+------+---------+
| NULL | mothra  |
+------+---------+
|    0 | gigan   |
+------+---------+
2 rows in set (0.01 sec)

Вспомните, что для любого целого числа N значение NULL MOD N всегда равно NULL. Для таблиц, разделенных по HASH или KEY, этот результат обрабатывается для определения правильного раздела как 0. Проведя еще раз проверку в таблице схемы информации PARTITIONS, мы можем увидеть, что обе строки были вставлены в раздел p0:

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
     >   FROM INFORMATION_SCHEMA.PARTITIONS
     >   WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME ='th';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| th         | p0             |          2 |             20 |          20 |
| th         | p1             |          0 |              0 |           0 |
+------------+----------------+------------+----------------+-------------+
2 rows in set (0.00 sec)

Повторив последний пример, используя PARTITION BY KEY вместо PARTITION BY HASH в определении таблицы, вы можете проверить, что NULL также обрабатывается как 0 для этого типа разбиения.