15.2.6 MySQLパーティショニングの NULL 値の取り扱い|無料CGI PHPサイト(新着情報、営業日カレンダーなど)

15.2.6. MySQLパーティショニングの `NULL` 値の取り扱い

MySQLでのパーティションは、カラム値であろうと、ユーザによって提供された表現であろうと、NULL をパーティショニング表現の値として禁じるようなことは一切しません。NULL 値を、整数を生み出す表現の値として使用することが許可されていますが、NULL は数値でないことを覚えておいてください。MySQL5.1.8より、パーティションは NULL を全ての非 NULL 値より少ないものと認めます。これは、ORDER BY でも同じです。

これにより、NULL の取り扱いは異なるパーティショニングの種類によって、予期せぬ事態を招くことがあります。これにより、この章では各MySQLのパーティショニングのタイプが、行が記憶されるパーティションを選択するさい、どのように NULL 値を取り扱うかを紹介し、例を取り上げます。

パーティションを判定するカラム値が NULL となるよう RANGE によりパーティショニングされたテーブルに行を挿入した場合、行は最も低いパーティションに挿入されます。例えば、以下の2つの実装、作成されたテーブルを記します。

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 t1 (
    ->     c1 INT, 
    ->     c2 VARCHAR(20)
    -> ) 
    -> PARTITION BY RANGE(c1) ( 
    ->     PARTITION p0 VALUES LESS THAN (-5), 
    ->     PARTITION p1 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> 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)

どのパーティションに行が記憶されているかは、ファイルシステムを検査し、パーティションと対応している .MYD ファイルのサイズを比較することで割り出すことができます。

/var/lib/mysql/test> ls -l *.MYD
-rw-rw----  1 mysql mysql 20 2006-03-10 03:27 t1#P#p0.MYD
-rw-rw----  1 mysql mysql  0 2006-03-10 03:17 t1#P#p1.MYD
-rw-rw----  1 mysql mysql  0 2006-03-10 03:17 t1#P#p2.MYD
-rw-rw----  1 mysql mysql 20 2006-03-10 03:27 t2#P#p0.MYD
-rw-rw----  1 mysql mysql  0 2006-03-10 03:17 t2#P#p1.MYD
-rw-rw----  1 mysql mysql  0 2006-03-10 03:17 t2#P#p2.MYD
-rw-rw----  1 mysql mysql  0 2006-03-10 03:17 t2#P#p3.MYD

(パーティションのファイルは table_name#P#partition_name.extension フォーマットにより名づけられます。t1#P#p0.MYD はテーブル t1 のパーティション p0 データが記憶されているところです。注: MySQL 5.1.5以前には、これらのファイルはそれぞれ t1_p0.MYD や t2_p0.MYD と名づけられていました。この変化が更新に対してどういう影響を及ぼすかは、項C.1.11. 「Changes in release 5.1.6 (01 February 2006)」とバグ#13437を参照してください。)

これらの行が各テーブルの最も低いパーティションに記憶されていたことを証明するには、これらのパーティションを削除し、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 に関する情報については、項12.1.2. 「ALTER TABLE 構文」を参照してください。)

これはSQL関数を使用するパーティショニング表現に対しても同様です。例えば、以下のようなテーブルがあるとします。

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 を返します。NULL の dt カラム値を持つ行は、パーティショニング表現がそれ以外の値よりも少ない値に評価されたかのように扱われるため、 p0 パーティションに挿入される。

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

唯一 0 から 8 の間に c1 の値が含まれる行が ts1 に挿入可能です。NULL は 9 の様に、このレンジ外に位置します。NULL を含む値リストを持つテーブル ts2 や ts3 を、以下のとおり作成することができます。

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)、以下同様)。テーブル ts2 と ts3 両方に NULL を持つ行をカラム c1 に挿入することができます。

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)

ファイルシステムを検査することで、テーブル ts2 のパーティション p3 にはこれらステートメントの最初のものが挿入され、テーブル ts3 のパーティション p1 には2番目のステートメントが挿入されたことを確認することができます。

/var/lib/mysql/test> ls -l ts2*.MYD
-rw-rw----  1 mysql mysql  0 2006-03-10 10:35 ts2#P#p0.MYD
-rw-rw----  1 mysql mysql  0 2006-03-10 10:35 ts2#P#p1.MYD
-rw-rw----  1 mysql mysql  0 2006-03-10 10:35 ts2#P#p2.MYD
-rw-rw----  1 mysql mysql 20 2006-03-10 10:35 ts2#P#p3.MYD

/var/lib/mysql/test> ls -l ts3*.MYD
-rw-rw----  1 mysql mysql  0 2006-03-10 10:36 ts3#P#p0.MYD
-rw-rw----  1 mysql mysql 20 2006-03-10 10:36 ts3#P#p1.MYD
-rw-rw----  1 mysql mysql  0 2006-03-10 10:36 ts3#P#p2.MYD

新しい例のとおり、これらファイルをリストするため、ユニックスオペレーティングシステム上で bash シェルを使用します。この件に関しては、ユーザのプラットフォームが提供するものを使用してください。たとえば、WindowsのOS上でDOSシェルを使用している場合、最後のリストと等価のものは C:\Program Files\MySQL\MySQL Server 5.1\data\test ディレクトリ内の dir ts3*.MYD コマンドを起動することで取得できる可能性があります。

このセクションの前部で紹介したとおり、削除し、SELECT を実行することで値の記憶に使用されたパーティションを確認することができます。.

NULL は HASH や KEY によってパーティショニングされたテーブルとは同様に取り扱われます。こういったケースでは、NULL 値を生み出すパーティショニング表現は返される値が０であるかのように扱われます。この動作を確認するには、HASH によってパーティショニングされたテーブルを作成し、適当な値を含むレコードで実装させることでファイルシステムへの影響を調べることができます。たとえば、以下のステートメントで、test データベース内のテーブル th が作成されたとします。

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

Linux上でのMySQLのRPMインストールを想定すると、このステートメントは2つの .MYD ファイルを /var/lib/mysql/test につくり、それは bash シェルで以下の様に現れます。

/var/lib/mysql/test> ls th*.MYD -l
-rw-rw----  1 mysql mysql 0 2005-11-04 18:41 th#P#p0.MYD
-rw-rw----  1 mysql mysql 0 2005-11-04 18:41 th#P#p1.MYD

各ファイルのサイズが0 バイトであることに注目してください。では、c1 カラム値が NULL の行を th に挿入して、その行が挿入されたことを認証してください。

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

mysql> SELECT * FROM th;
+------+---------+
| c1   | c2      |
+------+---------+
| NULL | mothra  |
+------+---------+
1 row in set (0.01 sec)

どの整数 N にとっても、NULL MOD N の値は常に NULL になります。HASH や KEY によってパーティショニングされたテーブルに関しては、この結果は正しいパーティションを 0 として確定するために扱われます。システムシェルに戻ると、(このため bash を使用します)、再度データファイルをリストすることで、値が最初のパーティションに挿入されたことを確認できます。(デフォルトで p0 と名づけられる)

var/lib/mysql/test> ls *.MYD -l
-rw-rw----  1 mysql mysql 20 2005-11-04 18:44 th#P#p0.MYD
-rw-rw----  1 mysql mysql  0 2005-11-04 18:41 th#P#p1.MYD

他のデータファイルに影響することなく（ディスク上でサイズを増大）、th#P#p0.MYD ファイルのみ、INSERT ステートメントが改良したことが確認できます。

重要MySQL 5.1.8以前では、RANGE パーティショニングは、配置の断定に関して、パーティショニング表現値 NULL をゼロとして扱いました。(これを回避する方法は、ヌルを許容しないテーブルをデザインすることで、通常はカラムを NOT NULL と宣言することで実行しました)。この前期の動作による RANGE パーティショニングスキーマがある場合、MySQL 5.1.8以降にアップグレードする時再実装しなければいけません。

無料PHPプログラム

15.2.6. MySQLパーティショニングの NULL 値の取り扱い

15.2.6. MySQLパーティショニングの `NULL` 値の取り扱い