MySQL パーティション テーブル パーティションのパーティション フィールドをオンラインで変更し、パーティション (2) --> サブパーティションと Null 値の入力処理について詳しく学習します。

-- MySQL 分区、子分区および录入力 Null の処理状況。 完官方文档做的笔记.

-- KEY パーティショニング
キーによるパーティショニングは、ハッシュによるパーティショニングと似ていますが、ハッシュ パーティショニングがユーザー定義の式を使用する場合、キー パーティショニングのハッシュ関数がMySQLサーバー。この内部ハッシュ関数は、 PASSWORD() と同じアルゴリズム。
HASH ではなく KEY が使用されます。
KEY は、1 つ以上の列名のリストのみを受け取ります。テーブルに主キーがある場合、パーティション化キーとして使用される列は、テーブルの主キーの一部またはすべてを構成する必要があります。
KEY は 0 個以上の列名のリストを受け取ります。パーティション化キーとして列名が指定されていない場合は、テーブルの主キーがあればそれが使用されます。たとえば、次の CREATE TABLE ステートメントは MySQL 5.5 で有効です:

 mysql> CREATE TABLE k1 (
  ->     id INT NOT NULL PRIMARY KEY,
  ->     name VARCHAR(20)
  -> )
  -> PARTITION BY KEY()
  -> PARTITIONS 2;
 Query OK, 0 rows affected (0.06 sec)

 If there is no primary key but there is a unique key, then the unique key is used for the partitioning key:
 mysql> CREATE TABLE k2 (
  ->     id INT NOT NULL,
  ->     name VARCHAR(20),
  ->     UNIQUE KEY (id)
  -> )
  -> PARTITION BY KEY()
  -> PARTITIONS 2;
 Query OK, 0 rows affected (0.02 sec)

ただし、一意のキー列が NOT NULL として定義されていない場合、前のステートメントは失敗します。

どちらの場合も、SHOW CREATE TABLE の出力や INFORMATION_SCHEMA.PARTITIONS テーブルの PARTITION_EXPRESSION 列には表示されませんが、パーティション化キーは id 列です。
以下のように:

mysql>  SELECT t.TABLE_NAME, t.PARTITION_NAME,t.TABLE_ROWS  FROM INFORMATION_SCHEMA.PARTITIONS t WHERE table_name='k2';
+------------+----------------+------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS |
+------------+----------------+------------+
| k2         | p0             |          3 |
| k2         | p1             |          4 |
+------------+----------------+------------+
2 rows in set (0.01 sec)

他のパーティション化タイプの場合とは異なり、 KEY によるパーティション分割に使用される列は、整数値や NULL 値に制限されません。
たとえば、次の CREATE TABLE ステートメントは有効です: <br/>没主キー，没有在定义時候指定分区字段，会抱错：

mysql> CREATE TABLE tm3 (
    ->     s1 CHAR(32) 
    -> )
    -> PARTITION BY KEY()
    -> PARTITIONS 10;
ERROR 1488 (HY000): Field in list of fields for partition function not found in table
在定义中加入分区字段，add the column in define , it is ok
mysql> CREATE TABLE tm3 (
    ->     s1 CHAR(32) 
    -> )
    -> PARTITION BY KEY(s1)
    -> PARTITIONS 10;
Query OK, 0 rows affected (0.07 sec)

mysql>

子分区サブパーティション化
サブパーティション化 (複合パーティション化とも呼ばれる) は、パーティション化されたテーブル内の各パーティションをさらに分割したものです。
たとえば、次の CREATE TABLE ステートメントを考えてみましょう:

mysql> CREATE TABLE ts (id INT, purchased DATE)
    ->     PARTITION BY RANGE( YEAR(purchased) )
    ->     SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
    ->         PARTITION p0 VALUES LESS THAN (1990) (
    ->             SUBPARTITION s0,
    ->             SUBPARTITION s1
    ->         ),
    ->         PARTITION p1 VALUES LESS THAN (2000) (
    ->             SUBPARTITION s2,
    ->             SUBPARTITION s3
    ->         ),
    ->         PARTITION p2 VALUES LESS THAN MAXVALUE (
    ->             SUBPARTITION s4,
    ->             SUBPARTITION s5
    ->         )
    ->     );

Query OK, 0 rows affected (0.04 sec)


CREATE TABLE ts3 (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0,
            SUBPARTITION s1
        ),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE (
            SUBPARTITION s2,
            SUBPARTITION s3
        )
    );

(1) 各パーティション同じ数のサブパーティションが必要です。そうでない場合は、fail

 mysql> CREATE TABLE ts3 (id INT, purchased DATE)
  ->     PARTITION BY RANGE( YEAR(purchased) )
  ->     SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
  ->         PARTITION p0 VALUES LESS THAN (1990) (
  ->             SUBPARTITION s0,
  ->             SUBPARTITION s1
  ->         ),
  ->         PARTITION p1 VALUES LESS THAN (2000),
  ->         PARTITION p2 VALUES LESS THAN MAXVALUE (
  ->             SUBPARTITION s2,
  ->             SUBPARTITION s3
  ->         )
  ->     );
 ERROR 1064 (42000): Wrong number of subpartitions defined, mismatch with previous setting near '
   PARTITION p2 VALUES LESS THAN MAXVALUE (
    SUBPARTITION s2,
  ' at line 8
 mysql>

(2) 各 SUBPARTITION 句には、(少なくとも) サブパーティションの名前が含まれている必要があります。

それ以外の場合は、サブパーティションに任意のオプションを設定するか、サブパーティションが仮定することを許可できます。そのオプションのデフォルト設定です。

(3) サブパーティション名はテーブル全体で一意である必要があります。

(4) サブパーティションは、データとインデックスを多くのディスクに分散するために、特に大きなテーブルで使用できます。 6 つのディスクが /disk0、/disk1、/disk2 などとしてマウントされているとします。次に、次の例を考えてみましょう。

mysql> CREATE TABLE ts5 (id INT, purchased DATE)
    ->     PARTITION BY RANGE( YEAR(purchased) )
    ->     SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
    ->         PARTITION p0 VALUES LESS THAN (1990) (
    ->             SUBPARTITION s0
    ->                 DATA DIRECTORY = '/disk0/data'
    ->                 INDEX DIRECTORY = '/disk0/idx',
    ->             SUBPARTITION s1
    ->                 DATA DIRECTORY = '/disk1/data'
    ->                 INDEX DIRECTORY = '/disk1/idx'
    ->         ),
    ->         PARTITION p1 VALUES LESS THAN (2000) (
    ->             SUBPARTITION s2
    ->                 DATA DIRECTORY = '/disk2/data'
    ->                 INDEX DIRECTORY = '/disk2/idx',
    ->             SUBPARTITION s3
    ->                 DATA DIRECTORY = '/disk3/data'
    ->                 INDEX DIRECTORY = '/disk3/idx'
    ->         ),
    ->         PARTITION p2 VALUES LESS THAN MAXVALUE (
    ->             SUBPARTITION s4
    ->                 DATA DIRECTORY = '/disk4/data'
    ->                 INDEX DIRECTORY = '/disk4/idx',
    ->             SUBPARTITION s5
    ->                 DATA DIRECTORY = '/disk5/data'
    ->                 INDEX DIRECTORY = '/disk5/idx'
    ->         )
    ->     );
Query OK, 0 rows affected (0.04 sec)

In this case, a separate disk is used for the data and for the indexes of each RANGE. Many other variations are possible;

another example might be: 
mysql> CREATE TABLE ts6 (id INT, purchased DATE)
    ->     PARTITION BY RANGE(YEAR(purchased))
    ->     SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
    ->         PARTITION p0 VALUES LESS THAN (1990) (
    ->             SUBPARTITION s0a
    ->                 DATA DIRECTORY = '/disk0'
    ->                 INDEX DIRECTORY = '/disk1',
    ->             SUBPARTITION s0b
    ->                 DATA DIRECTORY = '/disk2'
    ->                 INDEX DIRECTORY = '/disk3'
    ->         ),
    ->         PARTITION p1 VALUES LESS THAN (2000) (
    ->             SUBPARTITION s1a
    ->                 DATA DIRECTORY = '/disk4/data'
    ->                 INDEX DIRECTORY = '/disk4/idx',
    ->             SUBPARTITION s1b
    ->                 DATA DIRECTORY = '/disk5/data'
    ->                 INDEX DIRECTORY = '/disk5/idx'
    ->         ),
    ->         PARTITION p2 VALUES LESS THAN MAXVALUE (
    ->             SUBPARTITION s2a,
    ->             SUBPARTITION s2b
    ->         )
    ->     );
Query OK, 0 rows affected (0.04 sec)

将来、2000 年から始まる 10 年間の購入数が増加し、デフォルトの場所では十分なスペースが確保できなくなった場合、対応する行はALTER TABLE ... REORGANIZE PARTITION ステートメント。 これを行う方法の説明については、セクション17.3「パーティション管理」を参照してください。

NO_DIR_IN_CREATE サーバー SQL モードが有効な場合、パーティション定義では DATA DIRECTORY および INDEX DIRECTORY オプションは許可されません。 MySQL 5.5.5 以降、サブパーティションを定義するときにこれらのオプションも禁止されています (Bug#42954)。

MySQL パーティショニングによる NULL の処理方法
MySQL のパーティショニングは、パーティショニング式の値として NULL を禁止するものではありません
それが列の値であるか、ユーザー指定の式の値であるか。整数を生成する必要がある式の値として NULL を使用することは許可されていますが、NULL は数値ではないことに留意することが重要です。 MySQLのパーティショニング 実装では、ORDER BY と同様に、NULL を非 NULL 値よりも小さいものとして扱います。

これは、NULL の扱いが異なるタイプのパーティショニング間で異なり、準備ができていないと予期しない動作が発生する可能性があることを意味します。 <br/>このため、このセクションでは、行を保存するパーティションを決定する際に各 MySQL パーティショニング タイプが NULL 値をどのように処理するかを説明し、それぞれの例を示します

RANGE による NULL の処理パーティション分割。  パーティションを決定するために使用される列の値が NULL になるように、RANGE でパーティション化されたテーブルに行を挿入すると、

その行は最下位のパーティションに挿入されます。たとえば、次のように作成された p という名前のデータベース内の 2 つのテーブルについて考えてみましょう:

(1) Rang Partition,OK
PARTITIONS に対して次のクエリを使用すると、これら 2 つの CREATE TABLE ステートメントによって作成されたパーティションを確認できます。 INFORMATION_SCHEMA データベースのテーブル:

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

次に、パーティション化キーとして使用される列に NULL を含む 1 つの行をこれらの各テーブルに入力し、その行が 1 組の SELECT ステートメントを使用して挿入されたことを確認します。 :

You can see which partitions are used to store the inserted rows by rerunning the previous query against INFORMATION_SCHEMA.PARTITIONS and inspecting the output:

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;
+------+--------+
| c1   | c2     |
+------+--------+
| NULL | mothra |
+------+--------+
1 row in set (0.01 sec)

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

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH    FROM INFORMATION_SCHEMA.PARTITIONS

WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME LIKE 't_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t1         | p0             |          1 |          16384 |       16384 |
| t1         | p1             |          0 |              0 |       16384 |
| t1         | p2             |          0 |              0 |       16384 |
| t2         | p0             |          1 |          16384 |       16384 |
| t2         | p1             |          0 |              0 |       16384 |
| t2         | p2             |          0 |              0 |       16384 |
| t2         | p3             |          0 |              0 |       16384 |
| ts         | p0             |          0 |              0 |       16384 |
| ts         | p0             |          0 |              0 |       16384 |
| ts         | p1             |          0 |              0 |       16384 |
| ts         | p1             |          0 |              0 |       16384 |
| ts         | p2             |          0 |              0 |       16384 |
| ts         | p2             |          0 |              0 |       16384 |
+------------+----------------+------------+----------------+-------------+
13 rows in set (0.00 sec)


You can also demonstrate that these rows were stored in the lowest partition of each table by dropping these partitions,

and then re-running the SELECT statements:

 <br/>

(2) Handling of NULL with LIST partitioning. 必须将null在定义中加入才能录入null的分区数据

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)

否则insert null的分区数据会抱错: ERROR 1504 (HY000): Table has no partition for value NULL

(3) Handling of NULL with HASH and KEY partitioning. <br/>

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

There is no data record in beginnig.
mysql>   SELECT TABLE_NAME,PARTITION_NAME,TABLE_ROWS,AVG_ROW_LENGTH,DATA_LENGTH
    ->          FROM INFORMATION_SCHEMA.PARTITIONS
    ->          WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME ='th';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| th         | p0             |          0 |              0 |       16384 |
| th         | p1             |          0 |              0 |       16384 |
+------------+----------------+------------+----------------+-------------+
2 rows in set (0.00 sec)

mysql> INSERT INTO th VALUES (NULL, 'mothra'), (0, 'gigan');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

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

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

Recall that for any integer N, the value of NULL MOD N is always NULL. For tables that are partitioned by HASH or KEY, this result is treated for determining the correct partition as 0. Checking the INFORMATION_SCHEMA.PARTITIONS table once again, we can see that both rows were inserted into partition p0:

 

MySQL对分区中null值得处理, rang,key,以及hash中,都是直接放入min的分区中. list分区中则是放入事先定义好的包含null的分区中,如果list分区事先没有定义包含null值的分区,那么录入的时候会抱错

 以上就是MySQL 分区表 partition线上修改分区字段，后续进一步学习partition (2) --> 子分区以及对录入Null值的处理情况.的内容，更多相关内容请关注PHP中文网（www.php.cn）！