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: 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 tu\G *************************** 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 = constantpartition_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