MySQL Partitioning调研

2022-05-18 12:11:42

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 = 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
上一篇：Android 字体设置-Typeface讲解


下一篇：MySQL (select_paren) union_order_or_limit 行为