MySQL 9.0 参考手册  /  ...  /  RANGE COLUMNS 分区

26.2.3.1 RANGE COLUMNS 分区

范围列分区类似于范围分区,但允许您使用基于多个列值的范围来定义分区。此外,您可以使用除整数类型以外的其他类型的列来定义范围。

RANGE COLUMNS 分区在以下方面与 RANGE 分区有很大不同

  • RANGE COLUMNS 不接受表达式,只接受列名。

  • RANGE COLUMNS 接受一个或多个列的列表。

    RANGE COLUMNS 分区基于 元组(列值列表)之间的比较,而不是标量值之间的比较。行在 RANGE COLUMNS 分区中的放置也基于元组之间的比较;这将在本节后面进一步讨论。

  • RANGE COLUMNS 分区列不受限于整数列;字符串、DATEDATETIME 列也可以用作分区列。(有关详细信息,请参见 第 26.2.3 节,“COLUMNS 分区”。)

使用 RANGE COLUMNS 创建分区的表的语法如下所示

CREATE TABLE table_name
PARTITION 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][, ...]
注意

此处没有显示创建分区表时可以使用的所有 CREATE TABLE 选项。有关完整信息,请参见 第 15.1.20 节,“CREATE TABLE 语句”

在上面显示的语法中,column_list 是一个或多个列的列表(有时称为 分区列列表),value_list 是一个值的列表(即它是 分区定义值列表)。必须为每个分区定义提供一个 value_list,并且每个 value_list 必须与 column_list 中的列数相同。一般来说,如果您在 COLUMNS 子句中使用 N 列,则每个 VALUES LESS THAN 子句也必须提供一个包含 N 个值的列表。

分区列列表和定义每个分区的 value 列表中的元素必须按相同的顺序出现。此外,value 列表中的每个元素必须与 column 列表中的对应元素具有相同的数据类型。但是,分区列列表中的列名称顺序和 value 列表的顺序不必与 CREATE TABLE 语句主体中的表列定义顺序相同。与按 RANGE 分区的表一样,您可以使用 MAXVALUE 来表示一个值,使得插入到给定列中的任何合法值都小于该值。以下是一个 CREATE TABLE 语句的示例,有助于说明所有这些要点

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)

rcx 包含列 abcd。提供给 COLUMNS 子句的分区列列表使用其中 3 列,顺序为 adc。用于定义每个分区的每个 value 列表包含 3 个值,顺序相同;也就是说,每个 value 列表元组的形式为 (INTINTCHAR(3)),这对应于列 adc(按此顺序)使用的数据类型。

将行放入分区是通过将要插入的行的元组(与 COLUMNS 子句中的列列表匹配)与用于定义表分区的 VALUES LESS THAN 子句中使用的元组进行比较来确定的。由于我们比较的是元组(即值的列表或集合),而不是标量值,因此 VALUES LESS THANRANGE COLUMNS 分区中使用时的语义与简单 RANGE 分区的情况略有不同。在 RANGE 分区中,生成表达式值为 VALUES LESS THAN 中的限制值的行永远不会放置在相应的分区中;但是,当使用 RANGE COLUMNS 分区时,有时可能存在分区列列表的第一个元素的值等于 VALUES LESS THAN 值列表的第一个元素的值的行,该行将被放置在相应的分区中。

考虑使用此语句创建的 RANGE 分区表

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

如果我们向此表中插入 3 行,使得 a 列的值对于每行都是 5,则所有 3 行都存储在分区 p1 中,因为 a 列的值在每种情况下都不小于 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: 0

mysql> 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)

现在考虑一个类似的表 rc1,它使用 RANGE COLUMNS 分区,并且在 COLUMNS 子句中引用了 ab 两列,如下所示

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)
);

如果我们像刚刚插入到 r1 中那样,将完全相同的行插入到 rc1 中,则行的分布将完全不同

mysql> INSERT INTO rc1 VALUES (5,10), (5,11), (5,12);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT PARTITION_NAME, TABLE_ROWS
    ->     FROM INFORMATION_SCHEMA.PARTITIONS
    ->     WHERE TABLE_NAME = 'rc1';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          2 |
| p3             |          1 |
+----------------+------------+
2 rows in set (0.00 sec)

这是因为我们正在比较行,而不是标量值。我们可以将插入的行值与用于定义表 rc1 中分区 p0VALUES THAN LESS THAN 子句中的限制行值进行比较,如下所示

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)

2 个元组 (5,10)(5,11) 的值小于 (5,12),因此它们存储在分区 p0 中。由于 5 不小于 5,并且 12 不小于 12,因此 (5,12) 被认为不小于 (5,12),并且存储在分区 p1 中。

前面示例中的 SELECT 语句也可以使用显式行构造函数编写,如下所示

SELECT ROW(5,10) < ROW(5,12), ROW(5,11) < ROW(5,12), ROW(5,12) < ROW(5,12);

有关在 MySQL 中使用行构造函数的更多信息,请参见 第 15.2.15.5 节,“行子查询”

对于仅使用单个分区列的 RANGE COLUMNS 分区的表,行在分区中的存储方式与等效的按 RANGE 分区的表相同。以下 CREATE TABLE 语句创建一个使用 1 个分区列的 RANGE COLUMNS 分区的表

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) 插入到此表中,我们可以看到它们的放置方式与我们之前创建和填充的表 r 相同

mysql> INSERT INTO rx VALUES (5,10), (5,11), (5,12);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT PARTITION_NAME,TABLE_ROWS
    ->     FROM INFORMATION_SCHEMA.PARTITIONS
    ->     WHERE TABLE_NAME = 'rx';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |          3 |
+----------------+------------+
2 rows in set (0.00 sec)

也可以创建 RANGE COLUMNS 分区的表,其中一个或多个列的限制值在后续的分区定义中重复。只要用于定义分区的列值元组严格递增,就可以这样做。例如,以下每个 CREATE TABLE 语句都是有效的

CREATE TABLE rc2 (
    a INT,
    b INT
)
PARTITION BY RANGE COLUMNS(a,b) (
    PARTITION p0 VALUES LESS THAN (0,10),
    PARTITION p1 VALUES LESS THAN (10,20),
    PARTITION p2 VALUES LESS THAN (10,30),
    PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)
 );

CREATE TABLE rc3 (
    a INT,
    b INT
)
PARTITION BY RANGE COLUMNS(a,b) (
    PARTITION p0 VALUES LESS THAN (0,10),
    PARTITION p1 VALUES LESS THAN (10,20),
    PARTITION p2 VALUES LESS THAN (10,30),
    PARTITION p3 VALUES LESS THAN (10,35),
    PARTITION p4 VALUES LESS THAN (20,40),
    PARTITION p5 VALUES LESS THAN (MAXVALUE,MAXVALUE)
 );

以下语句也成功,即使乍一看它似乎不会成功,因为列 b 的限制值为分区 p0 的 25 和分区 p1 的 20,并且列 c 的限制值为分区 p1 的 100 和分区 p2 的 50

CREATE TABLE rc4 (
    a INT,
    b INT,
    c INT
)
PARTITION BY RANGE COLUMNS(a,b,c) (
    PARTITION p0 VALUES LESS THAN (0,25,50),
    PARTITION p1 VALUES LESS THAN (10,20,100),
    PARTITION p2 VALUES LESS THAN (10,30,50),
    PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
 );

在设计使用RANGE COLUMNS分区的表时,您可以始终使用mysql客户端来测试连续的分区定义,方法是比较所需的元组,如下所示

mysql> SELECT (0,25,50) < (10,20,100), (10,20,100) < (10,30,50);
+-------------------------+--------------------------+
| (0,25,50) < (10,20,100) | (10,20,100) < (10,30,50) |
+-------------------------+--------------------------+
|                       1 |                        1 |
+-------------------------+--------------------------+
1 row in set (0.00 sec)

如果CREATE TABLE语句包含的分区定义不是严格递增的,则会失败并出现错误,如以下示例所示

mysql> CREATE TABLE rcf (
    ->     a INT,
    ->     b INT,
    ->     c INT
    -> )
    -> PARTITION BY RANGE COLUMNS(a,b,c) (
    ->     PARTITION p0 VALUES LESS THAN (0,25,50),
    ->     PARTITION p1 VALUES LESS THAN (20,20,100),
    ->     PARTITION p2 VALUES LESS THAN (10,30,50),
    ->     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
    ->  );
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

遇到此类错误时,您可以通过在分区定义的列列表之间进行小于比较来推断哪些分区定义无效。在本例中,问题在于分区p2的定义,因为用于定义它的元组不小于用于定义分区p3的元组,如下所示

mysql> SELECT (0,25,50) < (20,20,100), (20,20,100) < (10,30,50);
+-------------------------+--------------------------+
| (0,25,50) < (20,20,100) | (20,20,100) < (10,30,50) |
+-------------------------+--------------------------+
|                       1 |                        0 |
+-------------------------+--------------------------+
1 row in set (0.00 sec)

在使用RANGE COLUMNS时,MAXVALUE也可以出现在多个VALUES LESS THAN子句的同一列中。但是,连续分区定义中各个列的限制值应为递增的,并且只有一个分区定义了MAXVALUE用作所有列值的上限,并且此分区定义应该出现在PARTITION ... VALUES LESS THAN子句列表中的最后。此外,您不能在多个分区定义中使用MAXVALUE作为第一列的限制值。

如前所述,在RANGE COLUMNS分区中,还可以使用非整数列作为分区列。(有关这些列的完整列表,请参见第 26.2.3 节,“COLUMNS 分区”。)考虑一个名为employees的表(未分区),使用以下语句创建

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 NOT NULL,
    store_id INT NOT NULL
);

使用RANGE COLUMNS分区,您可以创建此表的版本,该版本根据员工的姓氏将每行存储在四个分区之一中,如下所示

CREATE TABLE employees_by_lname (
    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 NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE COLUMNS (lname)  (
    PARTITION p0 VALUES LESS THAN ('g'),
    PARTITION p1 VALUES LESS THAN ('m'),
    PARTITION p2 VALUES LESS THAN ('t'),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

或者,您可以通过执行以下ALTER TABLE语句,使之前创建的employees表使用此方案进行分区

ALTER TABLE employees PARTITION BY RANGE COLUMNS (lname)  (
    PARTITION p0 VALUES LESS THAN ('g'),
    PARTITION p1 VALUES LESS THAN ('m'),
    PARTITION p2 VALUES LESS THAN ('t'),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);
注意

由于不同的字符集和排序规则具有不同的排序顺序,因此使用的字符集和排序规则可能会影响将使用字符串列作为分区列时,将存储在按RANGE COLUMNS分区的表的哪个分区中的给定行。此外,在创建此类表后,更改给定数据库、表或列的字符集或排序规则可能会导致行分布方式发生变化。例如,使用区分大小写的排序规则时,'and' 排列在 'Andersen' 之前,但使用不区分大小写的排序规则时,情况则相反。

有关 MySQL 如何处理字符集和排序规则的信息,请参见第 12 章,字符集、排序规则、Unicode

同样,您可以使用此处显示的ALTER TABLE语句,使employees表以某种方式进行分区,以便将每行存储在几个分区之一中,具体取决于相应的员工被雇用的十年

ALTER TABLE employees PARTITION BY RANGE COLUMNS (hired)  (
    PARTITION p0 VALUES LESS THAN ('1970-01-01'),
    PARTITION p1 VALUES LESS THAN ('1980-01-01'),
    PARTITION p2 VALUES LESS THAN ('1990-01-01'),
    PARTITION p3 VALUES LESS THAN ('2000-01-01'),
    PARTITION p4 VALUES LESS THAN ('2010-01-01'),
    PARTITION p5 VALUES LESS THAN (MAXVALUE)
);

有关PARTITION BY RANGE COLUMNS语法的更多信息,请参见第 15.1.20 节,“CREATE TABLE 语句”