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 tu\G*************************** 1. row ***************************       Table: tuCreate Table: CREATE TABLE `tu` (  `c1` bigint(20) unsigned DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin11 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中使用的函数如下：

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 allowedmysql> 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 allowedmysql> 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 allowedmysql> 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: 0mysql> 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 tu\G*************************** 1. row ***************************       Table: tuCreate Table: CREATE TABLE `tu` (  `c1` bigint(20) unsigned DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin11 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 domainmysql> 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