MySQLPartitioning調研

勉仁發表於2016-09-15
MySql

MySQL Partitioning調研

用來記錄MySQL Partitioning功能、相關限制的調研

RANGE Partition

語法見MySQL CREATE TABLE Syntax

partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1|2}](column_list)
        | RANGE{(expr) | COLUMNS(column_list)}
        | LIST{(expr) | COLUMNS(column_list)} }
    [PARTITIONS num]
    [SUBPARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1|2}](column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]

partition_definition:
    PARTITION partition_name
        [VALUES
            {LESS THAN {(expr | value_list) | MAXVALUE}
            |
            IN (value_list)}]

RANGE VALUES

每一個partition的value定義是從低到高。當插入的數值不在給出的值範圍內的時候會報錯，定義的value不在partition expression值範圍內也會報錯。

例如下面例子，由於-2不在partitoin expressioin c1的值域範圍內而報錯。

mysql>  CREATE TABLE tu (c1 BIGINT UNSIGNED)
PARTITION BY RANGE(c1) (
PARTITION p0 VALUES LESS THAN (-2),
PARTITION p1 VALUES LESS THAN (3),
PARTITION p2 VALUES LESS THAN (5),
PARTITION p3 VALUES LESS THAN (10 - 4),
PARTITION p4 VALUES LESS THAN (MAXVALUE)
);
ERROR 1563 (HY000): Partition constant is out of partition function domain

同時需要注意，RANGE分割槽定義使用的是less than，所以邊界上的值屬於下一個分割槽。

例如上面表格將p0中的value替換為2執行下面語句插入，

insert into tu values(3);
mysql> select * from tu partition(p1);
Empty set (0.00 sec)
mysql> select * from tu partition(p2);
+------+
| c1   |
+------+
|   3 |
+------+

建表語句values中的可計算表示式會存計算後的結果，下面例子中10-4就會計算出結果6儲存。

mysql>  CREATE TABLE tu (c1 BIGINT UNSIGNED)
PARTITION BY RANGE(c1) (
PARTITION p0 VALUES LESS THAN (2),
PARTITION p1 VALUES LESS THAN (3),
PARTITION p2 VALUES LESS THAN (5),
PARTITION p3 VALUES LESS THAN (10 - 4),
PARTITION p4 VALUES LESS THAN (MAXVALUE)
);

mysql> show create table tuG
*************************** 1. row ***************************
       Table: tu
Create Table: CREATE TABLE `tu` (
  `c1` bigint(20) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (c1)
(PARTITION p0 VALUES LESS THAN (2) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (3) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (5) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (6) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

alte table add/drop partition

如果最初定義的value範圍不夠，可以通過ALTER TABLE來新增partition。

ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));

如果需要刪除某個partition，也可以指定drop。

ALTER TABLE tr DROP PARTITION p2;

其他建表示例

Note：對於TIMESTAMP列作為range partition的分割槽列，僅支援使用UNIX_TIMESTAMP函式。
原因見MySQL Bug #42849

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT `1970-01-01`,
    separated DATE NOT NULL DEFAULT `9999-12-31`,
    job_code INT,
    store_id INT
)
PARTITION BY RANGE ( YEAR(separated) ) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (2001),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);


CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP(`2008-01-01 00:00:00`) ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP(`2008-04-01 00:00:00`) ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP(`2008-07-01 00:00:00`) ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP(`2008-10-01 00:00:00`) ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP(`2009-01-01 00:00:00`) ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP(`2009-04-01 00:00:00`) ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP(`2009-07-01 00:00:00`) ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP(`2009-10-01 00:00:00`) ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP(`2010-01-01 00:00:00`) ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

Note：檢視MySQL執行所選擇的分割槽可以使用EXPLAIN PARTITIONS

例如：

mysql> explain partitions select * from employees where year(separated) = 1990;
+----+-------------+-----------+-------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table     | partitions  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-----------+-------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | employees | p0,p1,p2,p3 | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+-----------+-------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain partitions select * from employees where separated = `1990-1-1`;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | employees | p0         | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

limitation

所有unique key(包括primary key)必須包含所有的partition columns

Partition Key的資料型別

除了partition by KEY， RANGE COLUMNS 或者 LIST COLUMNS，其他必須是integer的column或者表示式解析後是integer。同時partition key不能是subquery。

在MySQL中，subpartition只能是HASH/KEY partitioning。RANGE/LIST被subpartitioned

partition expression

1、儲存過程、定義函式、外掛程式和宣告的變數不能出現在partition expression中。

2、很多函式不可以出現在partition expression中，MySQL允許在partition expression中使用的函式如下：

ABS() CEILING() (seeCEILING() and FLOOR()) DAY()
DAYOFMONTH() DAYOFWEEK() DAYOFYEAR()
DATEDIFF() EXTRACT() (seeEXTRACT() function with WEEK specifier) FLOOR() (seeCEILING() and FLOOR())
HOUR() MICROSECOND() MINUTE()
MOD() MONTH() QUARTER()
SECOND() TIME_TO_SEC() TO_DAYS()
TO_SECONDS() UNIX_TIMESTAMP()(with TIMESTAMPcolumns) WEEKDAY()
YEAR()   YEARWEEK()

3、算數運算子約束

+，-，*在partition expression中是被允許的，DIV運算也可以，但是`/`是不允許出現的。位操作符|, &, ^, <<, >>, 和 ~ 都不允許。

Note：這些限制不僅在partition expression中，在range分割槽的values表示式中也一樣有如此限制。values中的表示式會存結果，而不是表示式。

mysql> create table tp(c1 int) partition by range(c1 - 10) (partition p0 values less than ((`aA` = `aa`)), partition p1 values less than (5), partition p2 values less than MAXVALUE);
ERROR 1564 (HY000): This partition function is not allowed

mysql> create table tp(c1 int) partition by range(c1 - 10) (partition p0 values less than ((cast(`123` as unsigned))), partition p1 values less than (5), partition p2 values less than MAXVALUE);
ERROR 1564 (HY000): This partition function is not allowed

mysql> create table tp(c1 int) partition by range(c1 - 10) (partition p0 values less than (10/2), partition p1 values less than (5), partition p2 values less than MAXVALUE);
ERROR 1564 (HY000): This partition function is not allowed

mysql> create table tp(c1 int) partition by range(c1 - 10) (partition p0 values less than (8 DIV 2), partition p1 values less than (5), partition p2 values less than MAXVALUE);
Query OK, 0 rows affected (0.09 sec)

SQL Mode

使用者建立的分割槽表不會保留建立時候的SQL Mode。許多函式和操作的結果會受SQL Mode影響，因此建立分割槽表後改變SQL Mode可能會導致這些表的行為變化、衝突或者丟失資料。強烈建議建立分割槽表後不要改變SQL Mode。

同樣SQL Mode在MySQL中也會影響partition table的副本，導致master和slave資料分佈不一致，或者在master成功，在slave失敗。

舉例：

1、 錯誤處理。DIV函式受ERROR_FOR_DIVISION_BY_ZERO影響。

預設情況下，DIV 0，MOD 0 返回NULL。當設定ERROR_FOR_DIVISION_BY_ZERO後就會報錯。

mysql>  SELECT @@sql_mode;
+-------------------+
| @@sql_mode        |
+-------------------+
| STRICT_ALL_TABLES |
+-------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE tn (c1 INT) PARTITION BY LIST(1 DIV c1) (PARTITION p0 VALUES IN (NULL),PARTITION p1 VALUES IN (1));
mysql> INSERT INTO tn VALUES (NULL), (0), (1);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from tn partition(p0);
+------+
| c1   |
+------+
| NULL |
|    0 |
+------+
2 rows in set (0.00 sec)

設定SQL Mode `ERROR_FOR_DIVISION_BY_ZERO`後，再次插入資料包錯。
mysql> SET sql_mode=`STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> INSERT INTO tn VALUES (NULL), (0), (1);
ERROR 1365 (22012): Division by 0

但原有資料讀取和新建表不受影響
mysql> select * from tn where c1 = 0;
+------+
| c1   |
+------+
|    0 |
+------+
1 row in set, 1 warning (0.00 sec)

mysql> CREATE TABLE tn1 (c1 INT) PARTITION BY LIST(1 DIV c1) (PARTITION p0 VALUES IN (NULL),PARTITION p1 VALUES IN (1));
Query OK, 0 rows affected (0.07 sec)

2、表訪問。UNSIGNED與SIGNED數值減法預設情況下產生UNSIGNED數值，如果是SIGNED數值就會報錯。當設定 NO_UNSIGNED_SUBTRACTION後，結果就是SIGNED值。

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 - 4),PARTITION p4 VALUES LESS THAN (MAXVALUE));

mysql> show create table tuG
*************************** 1. row ***************************
       Table: tu
Create Table: CREATE TABLE `tu` (
  `c1` bigint(20) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (c1 - 10)
(PARTITION p0 VALUES LESS THAN (-5) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (0) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (5) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (6) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)


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

再建立對應表也會失敗，因為-5不在c1 - 10的範圍內。
mysql>  CREATE TABLE tu2 (c1 BIGINT UNSIGNED) PARTITION BY RANGE(c1 - 10) ( PARTITION p0 VALUES LESS THAN (-5), PARTITION p1 VALUES LESS THAN (3),PARTITION p2 VALUES LESS THAN (5),PARTITION p3 VALUES LESS THAN (10 - 4),PARTITION p4 VALUES LESS THAN (MAXVALUE));
ERROR 1563 (HY000): Partition constant is out of partition function domain

將-5改為2即成功。
mysql>  CREATE TABLE tu2 (c1 BIGINT UNSIGNED) PARTITION BY RANGE(c1 - 10) ( PARTITION p0 VALUES LESS THAN (2), PARTITION p1 VALUES LESS THAN (3),PARTITION p2 VALUES LESS THAN (5),PARTITION p3 VALUES LESS THAN (10 - 4),PARTITION p4 VALUES LESS THAN (MAXVALUE));
Query OK, 0 rows affected (0.13 sec)

但是插入數字如果小於10依然會報錯。
mysql> insert into tu values(9);
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in `(`test`.`tu`.`c1` - 10)`

最大partition數目

包含subpartition的最大partition數目除了NDB 儲存引擎外為8192。

不支援query cache

分割槽表不支援Query cache。

Per-partition key caches.

MyISAM 支援。

InnoDB 儲存Partition table不支援外來鍵

ALTER TABLE … ORDER BY.

僅排序各個partition內資料。

Replace/Insert 指定partition可能報指定資料不在選擇的partition內

mysql>CREATE TABLE tr (c1 BIGINT UNSIGNED) PARTITION BY RANGE(c1) (
        PARTITION p0 VALUES LESS THAN (2),
        PARTITION P1 VALUES LESS THAN (10)
        );
mysql> insert into tr partition(p0) values(5);
ERROR 1748 (HY000): Found a row not matching the given partition set

FULLTEXT indexes.不支援

Partition Pruning

當有如下條件的時候肯定可以做Partition Pruning.

  • partition_column = constant
  • partition_column IN (constant1, constant2, …, constantN)

經試驗：
當partitoin_key為column的時候，可以根據給出的partition_column >、<、<=、>=來確定需要的partitions。
如果partition_key為+、-、*表示式則不會。

但是當表示式為 YEAR() 、TO_DAYS()或 TO_SECONDS()給出上述條件則可以確定需要的partitions。但是不具備year(partitoin_column) op const_value來確定partition的能力。

MySQL 實現基礎檔案

./partition_element.h
./sql_partition.cc
./partition_info.cc


相關文章