Mysql 分區介紹(四) —— RANGE COLUMNS分區

標籤：mysql分區   mysql range分區   資料庫分區   

RANGE COLUMNS和RANGE分區是非常類似的, 但是這兩個也有很多不同的地方。

• 1. RANGE COLUMNS 不可以使用運算式, 只能使用列名
• 2. RANGE COLUMNS 接受一個或多個欄位的列表
• 3. RANGE COLUMNS 分區列是不限制於數字列的;字串, DATE和DATETIME 列也可以使用在分區列

基本定義:

CREATE TABLE table_namePARTITIONED BY RANGE COLUMNS(column_list) (    PARTITION partition_name VALUES LESS THAN (value_list)[,    PARTITION partition_name VALUES LESS THAN (value_list)][,    ...])column_list:    column_name[, column_name][, ...]value_list:    value[, value][, ...]

column_list是一個或多個列名, value_list是和column_list相對應的一個或多個值

mysql> CREATE TABLE rcx (    ->     a INT,    ->     b INT,    ->     c CHAR(3),    ->     d INT    -> )    -> PARTITION BY RANGE COLUMNS(a,d,c) (    ->     PARTITION p0 VALUES LESS THAN (5,10,‘ggg‘),    ->     PARTITION p1 VALUES LESS THAN (10,20,‘mmm‘),    ->     PARTITION p2 VALUES LESS THAN (15,30,‘sss‘),    ->     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)    -> );Query OK, 0 rows affected (0.15 sec)

如果我們寫入三條資料到這個表每個列的值是5, 三條資料都將儲存在p1分區, 因為每個列的值都不小於5, 所以我們可以查詢INFORMATION_SCHEMA.PARTITIONS:

mysql> INSERT INTO r1 VALUES (5,10), (5,11), (5,12);Query OK, 3 rows affected (0.00 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> SELECT PARTITION_NAME,TABLE_ROWS    ->     FROM INFORMATION_SCHEMA.PARTITIONS    ->     WHERE TABLE_NAME = ‘r1‘;+----------------+------------+| PARTITION_NAME | TABLE_ROWS |+----------------+------------+| p0             |          0 || p1             |          3 |+----------------+------------+2 rows in set (0.00 sec)

同樣的, RANGE COLUMNS和RANGE分區一樣, 也是支援MAXVALUE的。

CREATE TABLE rc1 (    a INT,    b INT)PARTITION BY RANGE COLUMNS(a, b) (    PARTITION p0 VALUES LESS THAN (5, 12),    PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE));

但是在此時, 寫入的資料分布也會發生很大變化

mysql> INSERT INTO rc1 VALUES (5,10), (5,11), (5,12);Query OK, 3 rows affected (0.00 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> SELECT PARTITION_NAME,TABLE_ROWS    ->     FROM INFORMATION_SCHEMA.PARTITIONS    ->     WHERE TABLE_NAME = ‘rc1‘;+--------------+----------------+------------+| TABLE_SCHEMA | PARTITION_NAME | TABLE_ROWS |+--------------+----------------+------------+| p            | p0             |          2 || p            | p1             |          1 |+--------------+----------------+------------+2 rows in set (0.00 sec)

因為我們比較的是行資料而非標量值

mysql> SELECT (5,10) < (5,12), (5,11) < (5,12), (5,12) < (5,12);+-----------------+-----------------+-----------------+| (5,10) < (5,12) | (5,11) < (5,12) | (5,12) < (5,12) |+-----------------+-----------------+-----------------+|               1 |               1 |               0 |+-----------------+-----------------+-----------------+1 row in set (0.00 sec)

如果是單個欄位的RANGE COLUMNS分區, 那麼和RANGE分區是一致的

CREATE TABLE rx (    a INT,    b INT)PARTITION BY RANGE COLUMNS (a)  (    PARTITION p0 VALUES LESS THAN (5),    PARTITION p1 VALUES LESS THAN (MAXVALUE));

如果我們新增行(5,10), (5, 11)和(5,12)到表中, 我們可以看到他們儲存的位置是一樣的

mysql> INSERT INTO rx VALUES (5,10), (5,11), (5,12);Query OK, 3 rows affected (0.00 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> SELECT PARTITION_NAME,TABLE_ROWS    ->     FROM INFORMATION_SCHEMA.PARTITIONS    ->     WHERE TABLE_NAME = ‘rx‘;+--------------+----------------+------------+| TABLE_SCHEMA | PARTITION_NAME | TABLE_ROWS |+--------------+----------------+------------+| p            | p0             |          0 || p            | p1             |          3 |+--------------+----------------+------------+2 rows in set (0.00 sec)

Mysql 分區介紹(四) —— RANGE COLUMNS分區