MySQL8-中文参考-四十五-
MySQL8 中文参考(四十五)
26.2.1 范围分区
通过范围进行分区的表是这样分区的,即每个分区包含分区表达式值位于给定范围内的行。范围应该是连续的但不重叠,并且使用VALUES LESS THAN
运算符定义。在接下来的几个示例中,假设你正在创建一个类似以下内容的表,用于保存一家由 1 到 20 号店编号的连锁视频店的人事记录:
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
);
注意
这里使用的employees
表没有主键或唯一键。虽然示例在当前讨论的目的上可以正常工作,但你应该记住,在实践中,表极有可能具有主键、唯一键或两者,而用于分区列的可选选择取决于用于这些键的列,如果有的话。有关这些问题的讨论,请参阅 Section 26.6.1, “Partitioning Keys, Primary Keys, and Unique Keys”。
这个表可以根据你的需求以多种方式进行范围分区。一种方法是使用store_id
列。例如,你可以决定通过添加如下所示的PARTITION BY RANGE
子句将表分区为 4 部分:
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
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN (21)
);
在这个分区方案中,所有在 1 到 5 号店工作的员工对应的行都存储在p0
分区中,而在 6 到 10 号店工作的员工对应的行存储在p1
分区中,依此类推。每个分区按顺序定义,从最低到最高。这是PARTITION BY RANGE
语法的要求;在这方面,你可以将其类比为 C 或 Java 中一系列if ... elseif ...
语句。
很容易确定包含数据(72, 'Mitchell', 'Wilson', '1998-06-25', DEFAULT, 7, 13)
的新行被插入到p2
分区中,但当你的连锁店增加到第 21 家店时会发生什么?在这个方案下,没有覆盖store_id
大于 20 的行的规则,因此会导致错误,因为服务器不知道将其放在哪里。你可以通过在CREATE TABLE
语句中使用“catchall”VALUES LESS THAN
子句来避免这种情况,提供所有大于明确命名的最高值的值:
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
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
*PARTITION p3 VALUES LESS THAN MAXVALUE* );
(与本章中的其他示例一样,我们假设默认存储引擎是InnoDB
。)
避免找不到匹配值时出现错误的另一种方法是在INSERT
语句中使用IGNORE
关键字。有关示例,请参见 Section 26.2.2, “LIST Partitioning”。
MAXVALUE
表示一个始终大于最大可能整数值的整数值(在数学语言中,它充当最小上界)。现在,任何store_id
列值大于或等于 16(定义的最高值)的行都存储在分区p3
中。在将来的某个时候——当店铺数量增加到 25、30 或更多时,您可以使用ALTER TABLE
语句为 21-25、26-30 等店铺添加新分区(有关如何执行此操作的详细信息,请参见第 26.3 节,“分区管理”)。
以类似的方式,可以根据员工工作代码对表进行分区,即根据job_code
列值的范围进行分区。例如——假设两位数的工作代码用于普通(店内)工人,三位数的代码用于办公室和支持人员,四位数的代码用于管理职位——可以使用以下语句创建分区表:
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
)
PARTITION BY RANGE (job_code) (
PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (10000)
);
在这种情况下,所有与店内工人相关的行将存储在分区p0
中,与办公室和支持人员相关的行将存储在p1
中,与管理人员相关的行将存储在分区p2
中。
也可以在VALUES LESS THAN
子句中使用表达式。但是,MySQL 必须能够将表达式的返回值作为LESS THAN
(<
)比较的一部分进行评估。
您可以根据两个DATE
列中的一个基于表达式来进行分区,而不是根据店铺编号拆分表数据。例如,假设您希望根据每位员工离开公司的年份进行分区;也就是说,基于YEAR(separated)
的值。下面显示了实现这种分区方案的CREATE TABLE
语句示例:
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
);
在此方案中,所有在 1991 年之前离开的员工的行存储在分区p0
中;在 1991 年至 1995 年离开的员工中,存储在p1
中;在 1996 年至 2000 年离开的员工中,存储在p2
中;而在 2000 年之后离开的任何工人中,存储在p3
中。
也可以根据TIMESTAMP
列的值,使用UNIX_TIMESTAMP()
函数,基于RANGE
对表进行分区,如下例所示:
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)
);
不允许使用涉及TIMESTAMP
值的任何其他表达式(请参见 Bug #42849)。
范围分区在以下情况之一或多个情况为真时特别有用:
-
您希望或需要删除“旧”数据。如果您正在使用先前显示的用于
employees
表的分区方案,您可以简单地使用ALTER TABLE employees DROP PARTITION p0;
来删除所有在 1991 年之前停止为公司工作的员工的行。(有关更多信息,请参见 第 15.1.9 节“ALTER TABLE 语句” 和 第 26.3 节“分区管理”。)对于行数众多的表,这比运行类似于DELETE FROM employees WHERE YEAR(separated) <= 1990;
的DELETE
查询要高效得多。 -
您希望使用包含日期或时间值的列,或包含从其他系列产生的值。
-
您经常运行依赖于用于对表进行分区的列的查询。例如,当执行类似于
EXPLAIN SELECT COUNT(*) FROM employees WHERE separated BETWEEN '2000-01-01' AND '2000-12-31' GROUP BY store_id;
的查询时,MySQL 可以快速确定只需要扫描分区p2
,因为其余分区不可能包含任何满足WHERE
子句的记录。有关如何实现这一点的更多信息,请参见 第 26.4 节“分区修剪”。
这种分区的变体是 RANGE COLUMNS
分区。通过 RANGE COLUMNS
分区,可以使用多个列来定义适用于将行放置在分区中以及确定在执行分区修剪时包含或排除特定分区的分区范围。有关更多信息,请参见 第 26.2.3.1 节“RANGE COLUMNS 分区”。
基于时间间隔的分区方案。 如果您希望在 MySQL 8.0 中实现基于时间范围或间隔的分区方案,您有两个选项:
-
通过
RANGE
对表进行分区,并对分区表达式使用在DATE
、TIME
或DATETIME
列上操作并返回整数值的函数,如下所示:CREATE TABLE members ( firstname VARCHAR(25) NOT NULL, lastname VARCHAR(25) NOT NULL, username VARCHAR(16) NOT NULL, email VARCHAR(35), joined DATE NOT NULL ) PARTITION BY RANGE( YEAR(joined) ) ( PARTITION p0 VALUES LESS THAN (1960), PARTITION p1 VALUES LESS THAN (1970), PARTITION p2 VALUES LESS THAN (1980), PARTITION p3 VALUES LESS THAN (1990), PARTITION p4 VALUES LESS THAN MAXVALUE );
在 MySQL 8.0 中,还可以根据
TIMESTAMP
列的值使用RANGE
对表进行分区,使用UNIX_TIMESTAMP()
函数,如下例所示: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) );
在 MySQL 8.0 中,不允许使用涉及
TIMESTAMP
值的任何其他表达式。(参见 Bug #42849。)注意
在 MySQL 8.0 中,也可以使用
UNIX_TIMESTAMP(timestamp_column)
作为按LIST
分区的表的分区表达式。然而,通常不太实用。 -
通过
RANGE COLUMNS
按DATE
或DATETIME
列作为分区列对表进行分区。例如,members
表可以直接使用joined
列定义,如下所示:CREATE TABLE members ( firstname VARCHAR(25) NOT NULL, lastname VARCHAR(25) NOT NULL, username VARCHAR(16) NOT NULL, email VARCHAR(35), joined DATE NOT NULL ) PARTITION BY RANGE COLUMNS(joined) ( PARTITION p0 VALUES LESS THAN ('1960-01-01'), PARTITION p1 VALUES LESS THAN ('1970-01-01'), PARTITION p2 VALUES LESS THAN ('1980-01-01'), PARTITION p3 VALUES LESS THAN ('1990-01-01'), PARTITION p4 VALUES LESS THAN MAXVALUE );
注意
使用日期或时间类型的分区列,而不是DATE
或DATETIME
,在RANGE COLUMNS
中不受支持。
26.2.2 LIST 分区
MySQL 中的列表分区在许多方面类似于范围分区。与按RANGE
分区一样,每个分区必须明确定义。两种分区类型之间的主要区别在于,在列表分区中,每个分区是根据列值在一组值列表中的成员资格而定义和选择的,而不是在一组连续值范围中的一个。这是通过使用PARTITION BY LIST(*
expr*)
来完成的,其中expr
是一个列值或基于列值并返回整数值的表达式,然后通过VALUES IN (*
value_list*)
来定义每个分区,其中value_list
是一个逗号分隔的整数列表。
注意
在 MySQL 8.0 中,当按LIST
分区时,只能匹配一组整数(可能包括NULL
—请参见第 26.2.7 节,“MySQL 分区如何处理 NULL”)。
但是,在使用LIST COLUMN
分区时,可以在值列表中使用其他列类型,该分区稍后在本节中描述。
与按范围定义的分区不同,列表分区不需要按任何特定顺序声明。有关更详细的语法信息,请参见第 15.1.20 节,“CREATE TABLE Statement”。
对于接下来的示例,我们假设要分区的表的基本定义由此处显示的CREATE TABLE
语句提供:
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
);
(这是用作第 26.2.1 节,“RANGE 分区”示例基础的相同表。与其他分区示例一样,我们假设default_storage_engine
为InnoDB
。)
假设有 20 家视频商店分布在 4 个特许经营店中,如下表所示。
地区 | 商店 ID 号码 |
---|---|
北部 | 3, 5, 6, 9, 17 |
东部 | 1, 2, 10, 11, 19, 20 |
西部 | 4, 12, 13, 14, 18 |
中部 | 7, 8, 15, 16 |
要将此表分区,使属于同一地区的商店行存储在同一分区中,您可以使用此处显示的CREATE TABLE
语句:
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 LIST(store_id) (
PARTITION pNorth VALUES IN (3,5,6,9,17),
PARTITION pEast VALUES IN (1,2,10,11,19,20),
PARTITION pWest VALUES IN (4,12,13,14,18),
PARTITION pCentral VALUES IN (7,8,15,16)
);
这使得很容易向表中添加或删除与特定区域相关的员工记录。例如,假设西区的所有商店都被卖给另一家公司。在 MySQL 8.0 中,可以使用查询ALTER TABLE employees TRUNCATE PARTITION pWest
删除与该区域商店工作的员工相关的所有行,这比等效的DELETE
语句DELETE FROM employees WHERE store_id IN (4,12,13,14,18);
执行效率更高。(使用ALTER TABLE employees DROP PARTITION pWest
也会删除所有这些行,但也会从表的定义中删除分区pWest
;您需要使用ALTER TABLE ... ADD PARTITION
语句来恢复表的原始分区方案。)
与RANGE
分区一样,可以将LIST
分区与哈希或键分区组合以生成复合分区(子分区)。请参见第 26.2.6 节,“子分区”。
与RANGE
分区不同,没有像MAXVALUE
这样的“捕获所有”;分区表达式的所有预期值应该在PARTITION ... VALUES IN (...)
子句中涵盖。包含不匹配的分区列值的INSERT
语句会失败并显示错误,如下例所示:
mysql> CREATE TABLE h2 (
-> c1 INT,
-> c2 INT
-> )
-> PARTITION BY LIST(c1) (
-> PARTITION p0 VALUES IN (1, 4, 7),
-> PARTITION p1 VALUES IN (2, 5, 8)
-> );
Query OK, 0 rows affected (0.11 sec)
mysql> INSERT INTO h2 VALUES (3, 5);
ERROR 1525 (HY000): Table has no partition for value 3
当使用单个INSERT
语句将多行插入单个InnoDB
表时,InnoDB
将该语句视为单个事务,因此任何不匹配的值的存在都会导致该语句完全失败,因此不会插入任何行。
你可以通过使用IGNORE
关键字来忽略这种类型的错误,尽管对于每一行包含不匹配的分区列值的情况会发出警告,如下所示。
mysql> TRUNCATE h2;
Query OK, 1 row affected (0.00 sec)
mysql> TABLE h2;
Empty set (0.00 sec)
mysql> INSERT IGNORE INTO h2 VALUES (2, 5), (6, 10), (7, 5), (3, 1), (1, 9);
Query OK, 3 rows affected, 2 warnings (0.01 sec)
Records: 5 Duplicates: 2 Warnings: 2
mysql> SHOW WARNINGS;
+---------+------+------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------+
| Warning | 1526 | Table has no partition for value 6 |
| Warning | 1526 | Table has no partition for value 3 |
+---------+------+------------------------------------+
2 rows in set (0.00 sec)
您可以在以下TABLE
语句的输出中看到,包含不匹配的分区列值的行被静默拒绝,而不包含不匹配值的行被插入到表中:
mysql> TABLE h2;
+------+------+
| c1 | c2 |
+------+------+
| 7 | 5 |
| 1 | 9 |
| 2 | 5 |
+------+------+
3 rows in set (0.00 sec)
MySQL 还支持LIST COLUMNS
分区,这是LIST
分区的一种变体,允许您使用除整数以外的其他类型的列作为分区列,并使用多个列作为分区键。有关更多信息,请参见第 26.2.3.2 节,“LIST COLUMNS 分区”。
26.2.3 列分区
原文:
dev.mysql.com/doc/refman/8.0/en/partitioning-columns.html
26.2.3.1 范围列分区
26.2.3.2 列表列分区
接下来的两节讨论COLUMNS
分区,这是RANGE
和LIST
分区的变体。COLUMNS
分区允许在分区键中使用多个列。所有这些列都被考虑用于将行放入分区以及确定哪些分区要检查以匹配行进行分区修剪。
此外,RANGE COLUMNS
分区和LIST COLUMNS
分区都支持使用非整数列来定义值范围或列表成员。允许的数据类型如下列表所示:
-
所有整数类型:
TINYINT
,SMALLINT
,MEDIUMINT
,INT
(INTEGER
), 和BIGINT
。(这与按RANGE
和LIST
进行分区相同。)其他数值数据类型(如
DECIMAL
或FLOAT
和DATETIME
。不支持使用其他与日期或时间相关的数据类型作为分区列。
-
以下字符串类型:
CHAR
,VARCHAR
,BINARY
, 和VARBINARY
。TEXT
和BLOB
列不支持作为分区列。
下面两节关于RANGE COLUMNS
和LIST COLUMNS
分区的讨论假定您已经熟悉基于范围和列表的分区,这在 MySQL 5.1 及更高版本中得到支持;有关更多信息,请参见第 26.2.1 节,“范围分区”和第 26.2.2 节,“列表分区”。
原文:
dev.mysql.com/doc/refman/8.0/en/partitioning-columns-range.html
26.2.3.1 RANGE COLUMNS 分区
列范围分区类似于范围分区,但允许您根据多个列值的范围定义分区。此外,您可以使用非整数类型的列定义范围。
RANGE COLUMNS
分区与RANGE
分区在以下方面有显著不同:
-
RANGE COLUMNS
不接受表达式,只接受列名。 -
RANGE COLUMNS
接受一个或多个列的列表。RANGE COLUMNS
分区基于元组(列值列表)之间的比较,而不是标量值之间的比较。将行放置在RANGE COLUMNS
分区中也是基于元组之间的比较;这将在本节后面进一步讨论。 -
RANGE COLUMNS
分区列不限于整数列;字符串、DATE
和DATETIME
列也可以用作分区列。(详细信息请参阅第 26.2.3 节,“COLUMNS Partitioning”。)
创建由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 Statement”。
在刚刚展示的语法中,column_list
是一个或多个列的列表(有时称为分区列列表),value_list
是一个值列表(即,它是一个分区定义值列表)。必须为每个分区定义提供一个value_list
,并且每个value_list
必须具有与column_list
中列数相同的值。一般来说,如果在COLUMNS
子句中使用了N
列,则每个VALUES LESS THAN
子句也必须提供一个包含N
个值的列表。
分区列列表中的元素和定义每个分区的值列表中的元素必须以相同的顺序出现。此外,值列表中的每个元素必须与列列表中的相应元素具有相同的数据类型。然而,分区列列表和值列表中列名的顺序不必与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
包含列a
、b
、c
、d
。提供给COLUMNS
子句的分区列列表使用了这些列中的 3 列,顺序为a
、d
、c
。用于定义分区的每个值列表包含 3 个值,顺序相同;也就是说,每个值列表元组的形式为(INT
、INT
、CHAR(3)
),这对应于列a
、d
和c
使用的数据类型(按顺序)。
将行放入分区是通过比较要插入的行中与COLUMNS
子句中匹配的元组与用于定义表分区的VALUES LESS THAN
子句中使用的元组来确定的。因为我们比较的是元组(即值的列表或集合),而不是标量值,所以在与简单的RANGE
分区不同的情况下,与RANGE COLUMNS
分区一起使用的VALUES LESS THAN
的语义有所不同。在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,我们可以通过针对信息模式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
子句中引用了列a
和b
,如下所示创建:
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
中定义分区p0
的VALUES 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 节,“列分区”。)考虑一个名为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 如何处理字符集和校对规则的信息,请参阅第十二章,字符集、校对规则、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 语句”。
原文:
dev.mysql.com/doc/refman/8.0/en/partitioning-columns-list.html
26.2.3.2 列表列分区
MySQL 8.0 支持列表列
分区。这是列表
分区的一种变体,允许将多个列用作分区键,并且可以使用数据类型为整数类型以外的列作为分区列;您可以使用字符串类型、DATE
和DATETIME
列。(有关COLUMNS
分区列允许的数据类型的更多信息,请参见第 26.2.3 节,“列分区”。)
假设您有一个业务,客户分布在 12 个城市,为了销售和营销目的,您将这些城市组织成了每个包含 3 个城市的 4 个地区,如下表所示:
地区 | 城市 |
---|---|
1 | 奥斯卡沙姆,赫格斯比,蒙斯特罗斯 |
2 | 温默比,胡尔特斯弗雷德,韦斯特维克 |
3 | 尼舍,艾克绍,维特兰达 |
4 | 乌普维丁厄,阿尔韦斯塔,韦克西厄 |
使用列表列
分区,您可以创建一个客户数据表,根据客户所居住城市的名称将行分配给这些地区中的任何一个分区,如下所示:
CREATE TABLE customers_1 (
first_name VARCHAR(25),
last_name VARCHAR(25),
street_1 VARCHAR(30),
street_2 VARCHAR(30),
city VARCHAR(15),
renewal DATE
)
PARTITION BY LIST COLUMNS(city) (
PARTITION pRegion_1 VALUES IN('Oskarshamn', 'Högsby', 'Mönsterås'),
PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'Västervik'),
PARTITION pRegion_3 VALUES IN('Nässjö', 'Eksjö', 'Vetlanda'),
PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'Växjo')
);
与范围列
分区一样,您不需要在COLUMNS()
子句中使用表达式将列值转换为整数。(实际上,除了列名之外,不允许在COLUMNS()
中使用其他表达式。)
也可以使用DATE
和DATETIME
列,如下例所示,使用与之前customers_1
表相同的名称和列,但根据renewal
列使用列表列
分区,根据 2010 年 2 月的某周,将客户账户计划续订的情况存储在 4 个分区中的一个中:
CREATE TABLE customers_2 (
first_name VARCHAR(25),
last_name VARCHAR(25),
street_1 VARCHAR(30),
street_2 VARCHAR(30),
city VARCHAR(15),
renewal DATE
)
PARTITION BY LIST COLUMNS(renewal) (
PARTITION pWeek_1 VALUES IN('2010-02-01', '2010-02-02', '2010-02-03',
'2010-02-04', '2010-02-05', '2010-02-06', '2010-02-07'),
PARTITION pWeek_2 VALUES IN('2010-02-08', '2010-02-09', '2010-02-10',
'2010-02-11', '2010-02-12', '2010-02-13', '2010-02-14'),
PARTITION pWeek_3 VALUES IN('2010-02-15', '2010-02-16', '2010-02-17',
'2010-02-18', '2010-02-19', '2010-02-20', '2010-02-21'),
PARTITION pWeek_4 VALUES IN('2010-02-22', '2010-02-23', '2010-02-24',
'2010-02-25', '2010-02-26', '2010-02-27', '2010-02-28')
);
这种方法有效,但如果涉及的日期数量非常大,则定义和维护会变得繁琐;在这种情况下,通常更实用的是使用范围
或范围列
分区。在这种情况下,由于我们希望用作分区键的列是一个DATE
列,我们使用范围列
分区,如下所示:
CREATE TABLE customers_3 (
first_name VARCHAR(25),
last_name VARCHAR(25),
street_1 VARCHAR(30),
street_2 VARCHAR(30),
city VARCHAR(15),
renewal DATE
)
PARTITION BY RANGE COLUMNS(renewal) (
PARTITION pWeek_1 VALUES LESS THAN('2010-02-09'),
PARTITION pWeek_2 VALUES LESS THAN('2010-02-15'),
PARTITION pWeek_3 VALUES LESS THAN('2010-02-22'),
PARTITION pWeek_4 VALUES LESS THAN('2010-03-01')
);
更多信息请参见第 26.2.3.1 节,“范围列分区”。
此外(与范围列
分区一样),您可以在COLUMNS()
子句中使用多个列。
有关PARTITION BY LIST COLUMNS()
语法的更多信息,请参见第 15.1.20 节,“CREATE TABLE 语句”。
26.2.4 哈希分区
26.2.4.1 线性哈希分区
主要使用HASH
分区来确保数据在预定数量的分区之间均匀分布。使用范围或列表分区,您必须明确指定给定列值或一组列值应存储在哪个分区;使用哈希分区,这个决定已经为您处理,您只需指定一个基于列值的列值或表达式进行哈希处理,以及要将分区表分成的分区数。
要使用HASH
分区对表进行分区,必须在CREATE TABLE
语句中追加一个PARTITION BY HASH (*
expr*)
子句,其中expr
是返回整数的表达式。这可以简单地是 MySQL 整数类型之一的列名。此外,您很可能希望在此之后跟上PARTITIONS *
num*
,其中num
是表示要将表分成的分区数的正整数。
注意
为简单起见,接下来的示例中的表不使用任何键。您应该知道,如果表具有任何唯一键,那么用于此表的分区表达式中的每一列都必须是每个唯一键的一部分,包括主键。有关更多信息,请参见第 26.6.1 节“分区键、主键和唯一键”。
以下语句创建了一个表,该表在store_id
列上使用哈希分区,并分为 4 个分区:
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 HASH(store_id)
PARTITIONS 4;
如果不包括PARTITIONS
子句,则分区数默认为1
;在PARTITIONS
关键字后没有跟随数字会导致语法错误。
您还可以使用返回整数的 SQL 表达式作为expr
。例如,您可能希望根据雇员入职的年份进行分区。可以按照这里所示进行操作:
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 HASH( YEAR(hired) )
PARTITIONS 4;
expr
必须返回一个非常量、非随机的整数值(换句话说,它应该是变化的但确定的),并且不得包含任何禁止的构造,如第 26.6 节“分区的限制和限制”中所述。您还应该记住,每次插入、更新(或可能删除)一行时,都会评估此表达式;这意味着非常复杂的表达式可能会导致性能问题,特别是在执行影响大量行的操作(如批量插入)时。
最有效的哈希函数是一个仅作用于单个表列的函数,其值随着列值的增加或减少而一致变化,因为这允许在分区范围上进行“修剪”。也就是说,表达式与其基于的列值的变化越密切,MySQL 就能更有效地使用该表达式进行哈希分区。
例如,当date_col
是DATE
类型的列时,表达式TO_DAYS(date_col)
被认为与date_col
的值直接变化,因为对于date_col
值的每一次变化,表达式的值都以一种一致的方式变化。与date_col
相关的表达式YEAR(date_col)
的变化与date_col
不像TO_DAYS(date_col)
那么直接,因为并非每一次date_col
的变化都会产生与YEAR(date_col)
等效的变化。即便如此,YEAR(date_col)
是一个很好的哈希函数候选,因为它与date_col
的一部分直接变化,并且没有任何可能的date_col
变化会导致YEAR(date_col)
的不成比例变化。
相比之下,假设你有一个名为int_col
的列,其类型是INT
。现在考虑表达式POW(5-int_col,3) + 6
。这将是一个糟糕的哈希函数选择,因为int_col
值的变化不能保证产生表达式值的成比例变化。通过给定量改变int_col
的值可能会导致表达式值的差异很大。例如,将int_col
从5
更改为6
会导致表达式值减少-1
,但将int_col
的值从6
更改为7
会导致表达式值减少-7
。
换句话说,列值与表达式值之间的图形越接近由方程y=*
c*x
跟踪的直线,其中c
是某个非零常数,表达式越适合用于哈希。这与表达式越非线性,数据在分区之间分布越不均匀有关。
理论上,对涉及多个列值的表达式也可以进行修剪,但确定哪些表达式适合可能会非常困难和耗时。因此,不建议特别使用涉及多个列的哈希表达式。
当使用PARTITION BY HASH
时,存储引擎根据表达式的结果的模数确定使用哪个num
分区。换句话说,对于给定的表达式expr
,记录存储在分区号N
中,其中*
N* = MOD(*
expr*, *
num*)
。假设表t1
定义如下,因此有 4 个分区:
CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY HASH( YEAR(col3) )
PARTITIONS 4;
如果你向t1
插入一个col3
值为'2005-09-15'
的记录,则存储它的分区如下确定:
MOD(YEAR('2005-09-01'),4)
= MOD(2005,4)
= 1
MySQL 8.0 还支持一种称为线性哈希的HASH
分区的变体,它采用更复杂的算法来确定插入到分区表中的新行的位置。请参阅 Section 26.2.4.1, “线性哈希分区”,了解此算法的描述。
用户提供的表达式在每次插入或更新记录时进行评估。根据情况,当记录被删除时也可能进行评估。
原文:
dev.mysql.com/doc/refman/8.0/en/partitioning-linear-hash.html
26.2.4.1 线性哈希分区
MySQL 还支持线性哈希,与常规哈希不同之处在于线性哈希利用线性二次幂算法,而常规哈希使用哈希函数值的模。
在语法上,线性哈希分区和常规哈希之间唯一的区别是在PARTITION BY
子句中添加LINEAR
关键字,如下所示:
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 LINEAR HASH( YEAR(hired) )
PARTITIONS 4;
给定表达式expr
,当使用线性哈希时存储记录的分区是从num
个分区中的第N
个分区,其中N
根据以下算法派生:
-
找到大于
num
的下一个 2 的幂。我们称这个值为V
;可以计算如下:*V* = POWER(2, CEILING(LOG(2, *num*)))
(假设
num
为 13。那么LOG(2,13)
为 3.7004397181411。CEILING(3.7004397181411)
为 4,V
=POWER(2,4)
,即 16。) -
设置
N
=F
(column_list
) & (V
- 1)。 -
当
N
>=num
时:-
设置
V
=V
/ 2。 -
设置
N
=N
& (V
- 1)。
-
假设使用线性哈希分区并具有 6 个分区的表t1
是使用以下语句创建的:
CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY LINEAR HASH( YEAR(col3) )
PARTITIONS 6;
现在假设你想要将两条记录插入到具有col3
列值'2003-04-14'
和'1998-10-19'
的t1
中。第一条记录的分区号计算如下:
*V* = POWER(2, CEILING( LOG(2,6) )) = 8
*N* = YEAR('2003-04-14') & (8 - 1)
= 2003 & 7
= 3
(*3 >= 6 is FALSE: record stored in partition #3*)
第二条记录存储在的分区号计算如下:
*V* = 8
*N* = YEAR('1998-10-19') & (8 - 1)
= 1998 & 7
= 6
(*6 >= 6 is TRUE: additional step required*)
*N* = 6 & ((8 / 2) - 1)
= 6 & 3
= 2
(*2 >= 6 is FALSE: record stored in partition #2*)
通过线性哈希分区的优势在于添加、删除、合并和分割分区变得更快,这在处理包含极大量(TB 级)数据的表时可能会有益。缺点是与使用常规哈希分区获得的分布相比,数据在分区之间的均匀分布性较低。
26.2.5 键分区
按键分区类似于按哈希分区,不同之处在于哈希分区使用用户定义的表达式,而键分区的哈希函数由 MySQL 服务器提供。NDB Cluster 使用MD5()
来实现这一目的;对于使用其他存储引擎的表,服务器使用自己的内部哈希函数。
CREATE TABLE ... PARTITION BY KEY
的语法规则与创建哈希分区的表的规则类似。主要区别如下:
-
使用
KEY
而不是HASH
。 -
KEY
只接受零个或多个列名的列表。用作分区键的列必须包含表的主键的一部分或全部,如果表有主键的话。如果未指定列名作为分区键,则使用表的主键,如果有的话。例如,以下CREATE TABLE
语句在 MySQL 8.0 中是有效的:CREATE TABLE k1 ( id INT NOT NULL PRIMARY KEY, name VARCHAR(20) ) PARTITION BY KEY() PARTITIONS 2;
如果没有主键但有唯一键,则唯一键将用作分区键:
CREATE TABLE k1 ( id INT NOT NULL, name VARCHAR(20), UNIQUE KEY (id) ) PARTITION BY KEY() PARTITIONS 2;
但是,如果唯一键列未定义为
NOT NULL
,则前面的语句将失败。在这两种情况下,分区键是
id
列,即使在SHOW CREATE TABLE
的输出中或者在信息模式PARTITIONS
表的PARTITION_EXPRESSION
列中没有显示。与其他分区类型不同,
KEY
分区的列不限于整数或NULL
值。例如,以下CREATE TABLE
语句是有效的:CREATE TABLE tm1 ( s1 CHAR(32) PRIMARY KEY ) PARTITION BY KEY(s1) PARTITIONS 10;
如果指定了不同的分区类型,则前面的语句将无效。(在这种情况下,仅使用
PARTITION BY KEY()
也是有效的,并且与PARTITION BY KEY(s1)
具有相同的效果,因为s1
是表的主键。)关于这个问题的更多信息,请参见第 26.6 节,“分区的限制和限制”。
不支持具有索引前缀的列用作分区键。这意味着
CHAR
、VARCHAR
、BINARY
和VARBINARY
列可以用作分区键,只要它们不使用前缀;因为在索引定义中必须指定前缀,所以无法在分区键中使用BLOB
和TEXT
列。在 MySQL 8.0.21 之前,创建、修改或升级分区表时允许使用前缀的列,即使它们未包含在表的分区键中;在 MySQL 8.0.21 及更高版本中,这种宽容行为已被弃用,并且当使用一个或多个这样的列时,服务器会显示适当的警告或错误。有关更多信息和示例,请参见不支持键分区的列索引前缀。注意
使用
NDB
存储引擎的表隐式地通过KEY
进行分区,使用表的主键作为分区键(与其他 MySQL 存储引擎一样)。如果 NDB Cluster 表没有显式主键,则由NDB
存储引擎为每个 NDB Cluster 表生成的“隐藏”主键将用作分区键。如果为
NDB
表定义了显式分区方案,则表必须具有显式主键,并且分区表达式中使用的任何列必须是该键的一部分。但是,如果表使用“空”分区表达式——即PARTITION BY KEY()
而没有列引用,则不需要显式主键。您可以使用ndb_desc实用程序(使用
-p
选项)观察到这种分区。重要
对于使用键分区的表,您不能执行
ALTER TABLE DROP PRIMARY KEY
,因为这样做会生成错误 ERROR 1466 (HY000): Field in list of fields for partition function not found in table。对于使用KEY
进行分区的 NDB Cluster 表,这不是问题;在这种情况下,表将使用“隐藏”的主键重新组织为表的新分区键。参见第二十五章,MySQL NDB Cluster 8.0。
也可以通过线性键对表进行分区。这里是一个简单的例子:
CREATE TABLE tk (
col1 INT NOT NULL,
col2 CHAR(5),
col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;
LINEAR
关键字在 KEY
分区上具有与 HASH
分区相同的效果,分区号是使用二的幂算法而不是模算术推导出来的。参见第 26.2.4.1 节,“线性哈希分区”,了解该算法及其影响的描述。
26.2.6 子分区化
原文:
dev.mysql.com/doc/refman/8.0/en/partitioning-subpartitions.html
子分区化,也称为复合分区化,是对分区表中每个分区的进一步划分。考虑以下CREATE TABLE
语句:
CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) )
SUBPARTITIONS 2 (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN MAXVALUE
);
表ts
有 3 个RANGE
分区。这些分区——p0
、p1
和p2
——进一步划分为 2 个子分区。实际上,整个表被划分为3 * 2 = 6
个分区。但是,由于PARTITION BY RANGE
子句的作用,前两个仅存储purchased
列中值小于 1990 的记录。
可以对按RANGE
或LIST
分区的表进行子分区化。子分区可以使用HASH
或KEY
分区。这也称为复合分区化。
注意
SUBPARTITION BY HASH
和SUBPARTITION BY KEY
通常遵循与PARTITION BY HASH
和PARTITION BY KEY
相同的语法规则。一个例外是,SUBPARTITION BY KEY
(不像PARTITION BY KEY
)目前不支持默认列,因此必须指定用于此目的的列,即使表具有显式主键。这是一个我们正在努力解决的已知问题;有关更多信息和示例,请参见子分区的问题。
还可以使用SUBPARTITION
子句明确定义子分区,以指定各个子分区的选项。例如,以更冗长的方式创建与前面示例中所示的相同表ts
的方法如下:
CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0,
SUBPARTITION s1
),
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITION s2,
SUBPARTITION s3
),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s4,
SUBPARTITION s5
)
);
这里列出了一些需要注意的语法项:
-
每个分区必须具有相同数量的子分区。
-
如果您在分区表的任何分区上明确定义了任何子分区,必须定义它们全部。换句话说,以下语句将失败:
CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE( YEAR(purchased) ) SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( PARTITION p0 VALUES LESS THAN (1990) ( SUBPARTITION s0, SUBPARTITION s1 ), PARTITION p1 VALUES LESS THAN (2000), PARTITION p2 VALUES LESS THAN MAXVALUE ( SUBPARTITION s2, SUBPARTITION s3 ) );
即使使用
SUBPARTITIONS 2
,此语句仍将失败。 -
每个
SUBPARTITION
子句必须至少包括一个子分区的名称。否则,您可以为子分区设置任何所需选项,或允许其假定该选项的默认设置。 -
子分区名称必须在整个表中是唯一的。例如,以下
CREATE TABLE
语句是有效的:CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE( YEAR(purchased) ) SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( PARTITION p0 VALUES LESS THAN (1990) ( SUBPARTITION s0, SUBPARTITION s1 ), PARTITION p1 VALUES LESS THAN (2000) ( SUBPARTITION s2, SUBPARTITION s3 ), PARTITION p2 VALUES LESS THAN MAXVALUE ( SUBPARTITION s4, SUBPARTITION s5 ) );
26.2.7 MySQL 分区如何处理 NULL
原文:
dev.mysql.com/doc/refman/8.0/en/partitioning-handling-nulls.html
MySQL 中的分区对于将NULL
作为分区表达式的值(无论是列值还是用户提供的表达式的值)并不做任何限制。即使允许将NULL
用作必须产生整数的表达式的值,但重要的是要记住NULL
不是一个数字。MySQL 的分区实现将NULL
视为小于任何非NULL
值,就像ORDER BY
一样。
这意味着NULL
的处理在不同类型的分区之间有所不同,并且如果您没有为此做好准备,可能会产生您意想不到的行为。在这种情况下,我们在本节中讨论了每种 MySQL 分区类型在确定应将行存储在哪个分区时如何处理NULL
值,并为每种情况提供了示例。
使用 RANGE 分区处理 NULL。 如果您向由RANGE
分区的表插入一行,使得用于确定分区的列值为NULL
,则该行将插入到最低的分区中。考虑以下在名为p
的数据库中创建的两个表:
mysql> CREATE TABLE t1 (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY RANGE(c1) (
-> PARTITION p0 VALUES LESS THAN (0),
-> PARTITION p1 VALUES LESS THAN (10),
-> PARTITION p2 VALUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (0.09 sec)
mysql> CREATE TABLE t2 (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY RANGE(c1) (
-> PARTITION p0 VALUES LESS THAN (-5),
-> PARTITION p1 VALUES LESS THAN (0),
-> PARTITION p2 VALUES LESS THAN (10),
-> PARTITION p3 VALUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (0.09 sec)
通过以下查询INFORMATION_SCHEMA
数据库中的PARTITIONS
表,您可以看到这两个CREATE TABLE
语句创建的分区:
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
> FROM INFORMATION_SCHEMA.PARTITIONS
> WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 't_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t1 | p0 | 0 | 0 | 0 |
| t1 | p1 | 0 | 0 | 0 |
| t1 | p2 | 0 | 0 | 0 |
| t2 | p0 | 0 | 0 | 0 |
| t2 | p1 | 0 | 0 | 0 |
| t2 | p2 | 0 | 0 | 0 |
| t2 | p3 | 0 | 0 | 0 |
+------------+----------------+------------+----------------+-------------+
7 rows in set (0.00 sec)
(有关此表的更多信息,请参见第 28.3.21 节,“The INFORMATION_SCHEMA PARTITIONS Table”。)现在让我们用包含在用作分区键的列中的NULL
的单行填充这些表,并验证使用一对SELECT
语句插入了这些行:
mysql> INSERT INTO t1 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t2 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t1;
+------+--------+
| id | name |
+------+--------+
| NULL | mothra |
+------+--------+
1 row in set (0.00 sec)
mysql> SELECT * FROM t2;
+------+--------+
| id | name |
+------+--------+
| NULL | mothra |
+------+--------+
1 row in set (0.00 sec)
您可以通过重新运行针对INFORMATION_SCHEMA.PARTITIONS
的上一个查询并检查输出来查看用于存储插入行的分区:
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
> FROM INFORMATION_SCHEMA.PARTITIONS
> WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 't_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
*| t1 | p0 | 1 | 20 | 20 |*
| t1 | p1 | 0 | 0 | 0 |
| t1 | p2 | 0 | 0 | 0 |
*| t2 | p0 | 1 | 20 | 20 |*
| t2 | p1 | 0 | 0 | 0 |
| t2 | p2 | 0 | 0 | 0 |
| t2 | p3 | 0 | 0 | 0 |
+------------+----------------+------------+----------------+-------------+
7 rows in set (0.01 sec)
您还可以通过删除这些分区,然后重新运行SELECT
语句来演示这些行存储在每个表的最低编号分区中:
mysql> ALTER TABLE t1 DROP PARTITION p0;
Query OK, 0 rows affected (0.16 sec)
mysql> ALTER TABLE t2 DROP PARTITION p0;
Query OK, 0 rows affected (0.16 sec)
mysql> SELECT * FROM t1;
Empty set (0.00 sec)
mysql> SELECT * FROM t2;
Empty set (0.00 sec)
(有关ALTER TABLE ... DROP PARTITION
的更多信息,请参见第 15.1.9 节,“ALTER TABLE Statement”。)
对于使用 SQL 函数的分区表达式,NULL
也以这种方式处理。假设我们使用类似于以下的CREATE TABLE
语句定义一个表:
CREATE TABLE tndate (
id INT,
dt DATE
)
PARTITION BY RANGE( YEAR(dt) ) (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN MAXVALUE
);
与其他 MySQL 函数一样,YEAR(NULL)
返回NULL
。具有NULL
值的dt
列行被视为分区表达式评估为低于任何其他值的值,因此被插入到分区p0
中。
使用 LIST 分区处理 NULL 值。 通过LIST
分区的表仅在其中一个分区使用包含NULL
的值列表定义时才允许NULL
值。相反,通过LIST
分区的表如果在值列表中没有明确使用NULL
,则拒绝导致分区表达式产生NULL
值的行,如下例所示:
mysql> CREATE TABLE ts1 (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY LIST(c1) (
-> PARTITION p0 VALUES IN (0, 3, 6),
-> PARTITION p1 VALUES IN (1, 4, 7),
-> PARTITION p2 VALUES IN (2, 5, 8)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO ts1 VALUES (9, 'mothra');
ERROR 1504 (HY000): Table has no partition for value 9
mysql> INSERT INTO ts1 VALUES (NULL, 'mothra');
ERROR 1504 (HY000): Table has no partition for value NULL
只有c1
值在0
和8
之间(包括 0 和 8)的行才能插入到ts1
中。NULL
不在此范围内,就像数字9
一样。我们可以创建包含NULL
值列表的ts2
和ts3
表,如下所示:
mysql> CREATE TABLE ts2 (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY LIST(c1) (
-> PARTITION p0 VALUES IN (0, 3, 6),
-> PARTITION p1 VALUES IN (1, 4, 7),
-> PARTITION p2 VALUES IN (2, 5, 8),
-> PARTITION p3 VALUES IN (NULL)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE ts3 (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY LIST(c1) (
-> PARTITION p0 VALUES IN (0, 3, 6),
-> PARTITION p1 VALUES IN (1, 4, 7, NULL),
-> PARTITION p2 VALUES IN (2, 5, 8)
-> );
Query OK, 0 rows affected (0.01 sec)
在为分区定义值列表时,您可以(也应该)将NULL
视为任何其他值一样对待。例如,VALUES IN (NULL)
和VALUES IN (1, 4, 7, NULL)
都是有效的,就像VALUES IN (1, NULL, 4, 7)
,VALUES IN (NULL, 1, 4, 7)
等一样。您可以将具有c1
列为NULL
的行插入到ts2
和ts3
中:
mysql> INSERT INTO ts2 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO ts3 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)
通过针对INFORMATION_SCHEMA.PARTITIONS
发出适当的查询,您可以确定刚刚插入的行使用了哪些分区进行存储(我们假设,与前面的示例一样,分区表是在p
数据库中创建的):
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
> FROM INFORMATION_SCHEMA.PARTITIONS
> WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 'ts_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| ts2 | p0 | 0 | 0 | 0 |
| ts2 | p1 | 0 | 0 | 0 |
| ts2 | p2 | 0 | 0 | 0 |
*| ts2 | p3 | 1 | 20 | 20 |*
| ts3 | p0 | 0 | 0 | 0 |
*| ts3 | p1 | 1 | 20 | 20 |*
| ts3 | p2 | 0 | 0 | 0 |
+------------+----------------+------------+----------------+-------------+
7 rows in set (0.01 sec)
正如本节前面所示,您还可以通过删除这些分区并执行SELECT
来验证用于存储行的分区。
使用 HASH 和 KEY 分区处理 NULL 值。 对于使用HASH
或KEY
分区的表,NULL
的处理略有不同。在这些情况下,任何产生NULL
值的分区表达式都被视为其返回值为零。我们可以通过创建一个使用适当值的记录的HASH
分区表并查看其对文件系统的影响来验证这种行为。假设您使用以下语句创建了一个名为th
的表(也在p
数据库中):
mysql> CREATE TABLE th (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY HASH(c1)
-> PARTITIONS 2;
Query OK, 0 rows affected (0.00 sec)
可以使用以下查询查看属于该表的分区:
mysql> SELECT TABLE_NAME,PARTITION_NAME,TABLE_ROWS,AVG_ROW_LENGTH,DATA_LENGTH
> FROM INFORMATION_SCHEMA.PARTITIONS
> WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME ='th';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| th | p0 | 0 | 0 | 0 |
| th | p1 | 0 | 0 | 0 |
+------------+----------------+------------+----------------+-------------+
2 rows in set (0.00 sec)
每个分区的TABLE_ROWS
为 0。现在向th
插入两行,这两行的c1
列值分别为NULL
和 0,并验证这些行是否已插入,如下所示:
mysql> INSERT INTO th VALUES (NULL, 'mothra'), (0, 'gigan');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM th;
+------+---------+
| c1 | c2 |
+------+---------+
| NULL | mothra |
+------+---------+
| 0 | gigan |
+------+---------+
2 rows in set (0.01 sec)
对于任意整数N
,NULL MOD *
N*
的值始终为NULL
。对于按HASH
或KEY
分区的表,此结果被视为确定正确分区的0
。再次检查信息模式PARTITIONS
表,我们可以看到两行都被插入到分区p0
中:
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
> FROM INFORMATION_SCHEMA.PARTITIONS
> WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME ='th';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
*| th | p0 | 2 | 20 | 20 |*
| th | p1 | 0 | 0 | 0 |
+------------+----------------+------------+----------------+-------------+
2 rows in set (0.00 sec)
通过在表的定义中使用PARTITION BY KEY
替换PARTITION BY HASH
来重复上一个示例,您可以验证对于这种类型的分区,NULL
也被视为 0。
26.3 分区管理
原文:
dev.mysql.com/doc/refman/8.0/en/partitioning-management.html
26.3.1 RANGE 和 LIST 分区管理
26.3.2 HASH 和 KEY 分区管理
26.3.3 与表交换分区和子分区
26.3.4 分区维护
26.3.5 获取分区信息
有多种使用 SQL 语句修改分区表的方法;可以通过使用分区扩展来添加、删除、重新定义、合并或拆分现有分区来修改分区表,这些操作都是通过 ALTER TABLE
语句完成的。还有一些方法可以获取关于分区表和分区的信息。我们将在接下来的章节中讨论这些主题。
-
有关在按
RANGE
或LIST
分区的表中进行分区管理的信息,请参见 第 26.3.1 节,“RANGE 和 LIST 分区的管理”。 -
有关管理
HASH
和KEY
分区的讨论,请参见 第 26.3.2 节,“HASH 和 KEY 分区的管理”。 -
请参见 第 26.3.5 节,“获取分区信息”,了解 MySQL 8.0 提供的用于获取关于分区表和分区信息的机制。
-
有关对分区执行维护操作的讨论,请参见 第 26.3.4 节,“分区维护”。
注意
所有分区表的分区必须具有相同数量的子分区;一旦表被创建,就无法更改子分区。
要更改表的分区方案,只需使用带有 partition_options
选项的 ALTER TABLE
语句,其语法与用于创建分区表的 CREATE TABLE
相同;这个选项总是以关键字 PARTITION BY
开头。假设以下 CREATE TABLE
语句用于创建一个按范围分区的表:
CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE)
PARTITION BY RANGE( YEAR(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (2005)
);
要将此表重新分区,使其按键分为两个分区,使用 id
列值作为键的基础,可以使用以下语句:
ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2;
这对表的结构具有与删除表并使用 CREATE TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2;
重新创建相同的效果。
ALTER TABLE ... ENGINE = ...
仅更改表使用的存储引擎,并保留表的分区方案不变。该语句仅在目标存储引擎提供分区支持时成功。您可以使用ALTER TABLE ... REMOVE PARTITIONING
来移除表的分区;参见 Section 15.1.9, “ALTER TABLE Statement”。
重要提示
在给定的ALTER TABLE
语句中只能使用单个PARTITION BY
、ADD PARTITION
、DROP PARTITION
、REORGANIZE PARTITION
或COALESCE PARTITION
子句。如果您(例如)希望删除一个分区并重新组织表的其余分区,您必须在两个单独的ALTER TABLE
语句中执行此操作(一个使用DROP PARTITION
,然后第二个使用REORGANIZE PARTITION
)。
你可以使用ALTER TABLE ... TRUNCATE PARTITION
从一个或多个选定的分区中删除所有行。
26.3.1 范围和列表分区的管理
原文:
dev.mysql.com/doc/refman/8.0/en/partitioning-management-range-list.html
添加和删除范围和列表分区的操作方式类似,因此我们在本节讨论了这两种分区管理方式。 有关处理按哈希或键分区的表的信息,请参阅 第 26.3.2 节,“HASH 和 KEY 分区的管理”。
通过使用带有 DROP PARTITION
选项的 ALTER TABLE
语句,可以删除按 RANGE
或 LIST
分区的表中的分区。 假设您已创建了一个按范围分区的表,然后使用以下 CREATE TABLE
和 INSERT
语句插入了 10 条记录:
mysql> CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
-> PARTITION BY RANGE( YEAR(purchased) ) (
-> PARTITION p0 VALUES LESS THAN (1990),
-> PARTITION p1 VALUES LESS THAN (1995),
-> PARTITION p2 VALUES LESS THAN (2000),
-> PARTITION p3 VALUES LESS THAN (2005),
-> PARTITION p4 VALUES LESS THAN (2010),
-> PARTITION p5 VALUES LESS THAN (2015)
-> );
Query OK, 0 rows affected (0.28 sec)
mysql> INSERT INTO tr VALUES
-> (1, 'desk organiser', '2003-10-15'),
-> (2, 'alarm clock', '1997-11-05'),
-> (3, 'chair', '2009-03-10'),
-> (4, 'bookcase', '1989-01-10'),
-> (5, 'exercise bike', '2014-05-09'),
-> (6, 'sofa', '1987-06-05'),
-> (7, 'espresso maker', '2011-11-22'),
-> (8, 'aquarium', '1992-08-04'),
-> (9, 'study desk', '2006-09-16'),
-> (10, 'lava lamp', '1998-12-25');
Query OK, 10 rows affected (0.05 sec)
Records: 10 Duplicates: 0 Warnings: 0
您可以查看应该插入到分区 p2
中的项目,如下所示:
mysql> SELECT * FROM tr
-> WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';
+------+-------------+------------+
| id | name | purchased |
+------+-------------+------------+
| 2 | alarm clock | 1997-11-05 |
| 10 | lava lamp | 1998-12-25 |
+------+-------------+------------+
2 rows in set (0.00 sec)
您还可以使用分区选择获取此信息,如下所示:
mysql> SELECT * FROM tr PARTITION (p2);
+------+-------------+------------+
| id | name | purchased |
+------+-------------+------------+
| 2 | alarm clock | 1997-11-05 |
| 10 | lava lamp | 1998-12-25 |
+------+-------------+------------+
2 rows in set (0.00 sec)
更多信息请参见 第 26.5 节,“分区选择”。
要删除名为 p2
的分区,请执行以下命令:
mysql> ALTER TABLE tr DROP PARTITION p2;
Query OK, 0 rows affected (0.03 sec)
注意
NDBCLUSTER
存储引擎不支持 ALTER TABLE ... DROP PARTITION
。 但是,它支持本章中描述的与分区相关的其他 ALTER TABLE
扩展。
非常重要的一点是,当您删除一个分区时,也会删除存储在该分区中的所有数据。 通过重新运行先前的 SELECT
查询,您可以看到这一点:
mysql> SELECT * FROM tr WHERE purchased
-> BETWEEN '1995-01-01' AND '1999-12-31';
Empty set (0.00 sec)
注意
DROP PARTITION
受本地分区就地 API 支持,可与 ALGORITHM={COPY|INPLACE}
一起使用。 使用 ALGORITHM=INPLACE
的 DROP PARTITION
删除存储在分区中的数据并删除该分区。 但是,使用 ALGORITHM=COPY
或 old_alter_table=ON
的 DROP PARTITION
会重建分区表,并尝试将无法移动到另一个具有兼容 PARTITION ... VALUES
定义的分区的数据移动到另一个分区。 无法移动到另一个分区的数据将被删除。
因此,在执行 ALTER TABLE ... DROP PARTITION
之前,您必须具有表的 DROP
权限。
如果您希望删除所有分区中的所有数据,同时保留表定义及其分区方案,请使用TRUNCATE TABLE
语句。(请参阅第 15.1.37 节,“TRUNCATE TABLE Statement”。)
如果您打算更改表的分区而不丢失数据,请使用ALTER TABLE ... REORGANIZE PARTITION
。有关REORGANIZE PARTITION
的信息,请参见下文或第 15.1.9 节,“ALTER TABLE Statement”。
如果现在执行SHOW CREATE TABLE
语句,您可以看到表的分区结构已经发生了变化:
mysql> SHOW CREATE TABLE tr\G
*************************** 1\. row ***************************
Table: tr
Create Table: CREATE TABLE `tr` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE ( YEAR(purchased))
(PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB) */ 1 row in set (0.00 sec)
当你在更改后的表中插入具有purchased
列值在'1995-01-01'
和'2004-12-31'
之间(包括这两个日期)的新行时,这些行将存储在分区p3
中。您可以按照以下步骤验证这一点:
mysql> INSERT INTO tr VALUES (11, 'pencil holder', '1995-07-12');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM tr WHERE purchased
-> BETWEEN '1995-01-01' AND '2004-12-31';
+------+----------------+------------+
| id | name | purchased |
+------+----------------+------------+
| 1 | desk organiser | 2003-10-15 |
| 11 | pencil holder | 1995-07-12 |
+------+----------------+------------+
2 rows in set (0.00 sec)
mysql> ALTER TABLE tr DROP PARTITION p3;
Query OK, 0 rows affected (0.03 sec)
mysql> SELECT * FROM tr WHERE purchased
-> BETWEEN '1995-01-01' AND '2004-12-31';
Empty set (0.00 sec)
由于服务器不会像等效的DELETE
查询那样报告由于ALTER TABLE ... DROP PARTITION
而从表中删除的行数。
删除LIST
分区与删除RANGE
分区使用完全相同的ALTER TABLE ... DROP PARTITION
语法。然而,这对之后对表的使用有一个重要的区别:您不能再向表中插入具有被删除分区定义的值列表中的任何值的行。(请参阅第 26.2.2 节,“LIST 分区”,以获取示例。)
要向先前分区的表添加新的范围或列表分区,请使用ALTER TABLE ... ADD PARTITION
语句。对于按RANGE
分区的表,这可以用于在现有分区列表的末尾添加新的范围。假设您有一个包含组织成员数据的分区表,其定义如下:
CREATE TABLE members (
id INT,
fname VARCHAR(25),
lname VARCHAR(25),
dob DATE
)
PARTITION BY RANGE( YEAR(dob) ) (
PARTITION p0 VALUES LESS THAN (1980),
PARTITION p1 VALUES LESS THAN (1990),
PARTITION p2 VALUES LESS THAN (2000)
);
进一步假设成员的最小年龄为 16 岁。随着日历接近 2015 年底,您意识到必须很快准备好接纳 2000 年(及以后)出生的成员。您可以修改members
表以适应在 2000 年至 2010 年出生的新成员,如下所示:
ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));
对于按范围分区的表,您可以使用ADD PARTITION
仅向分区列表的高端添加新分区。尝试以这种方式在现有分区之间或之前添加新分区会导致错误,如下所示:
mysql> ALTER TABLE members
> ADD PARTITION (
> PARTITION n VALUES LESS THAN (1970));
ERROR 1463 (HY000): VALUES LESS THAN value must be strictly »
increasing for each partition
您可以通过将第一个分区重新组织为两个新分区,将它们之间的范围分割,来解决这个问题,就像这样:
ALTER TABLE members
REORGANIZE PARTITION p0 INTO (
PARTITION n0 VALUES LESS THAN (1970),
PARTITION n1 VALUES LESS THAN (1980)
);
使用SHOW CREATE TABLE
,您可以看到ALTER TABLE
语句已经产生了预期的效果:
mysql> SHOW CREATE TABLE members\G
*************************** 1\. row ***************************
Table: members
Create Table: CREATE TABLE `members` (
`id` int(11) DEFAULT NULL,
`fname` varchar(25) DEFAULT NULL,
`lname` varchar(25) DEFAULT NULL,
`dob` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE ( YEAR(dob))
(PARTITION n0 VALUES LESS THAN (1970) ENGINE = InnoDB,
PARTITION n1 VALUES LESS THAN (1980) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */ 1 row in set (0.00 sec)
另请参阅 Section 15.1.9.1, “ALTER TABLE Partition Operations”。
您还可以使用ALTER TABLE ... ADD PARTITION
来向由LIST
分区的表中添加新分区。假设一个表tt
是使用以下CREATE TABLE
语句定义的:
CREATE TABLE tt (
id INT,
data INT
)
PARTITION BY LIST(data) (
PARTITION p0 VALUES IN (5, 10, 15),
PARTITION p1 VALUES IN (6, 12, 18)
);
您可以添加一个新分区,用于存储具有data
列值7
、14
和21
的行,如下所示:
ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));
请记住,不能添加一个新的LIST
分区,其中包含已经包含在现有分区值列表中的任何值。如果尝试这样做,将导致错误:
mysql> ALTER TABLE tt ADD PARTITION
> (PARTITION np VALUES IN (4, 8, 12));
ERROR 1465 (HY000): Multiple definition of same constant »
in list partitioning
因为具有data
列值12
的任何行已经分配给了分区p1
,所以无法在表tt
上创建一个包含12
在其值列表中的新分区。为了实现这一点,您可以删除p1
,然后添加np
,然后一个新的p1
,并修改定义。然而,正如前面讨论的,这将导致所有存储在p1
中的数据丢失,而且通常情况下这并不是您真正想要做的。另一个解决方案可能是制作一个具有新分区的表的副本,并使用CREATE TABLE ... SELECT ...
将数据复制到其中,然后删除旧表并重命名新表,但是在处理大量数据时可能非常耗时。在需要高可用性的情况下,这也可能不可行。
您可以在单个ALTER TABLE ... ADD PARTITION
语句中添加多个分区,如下所示:
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
hired DATE NOT NULL
)
PARTITION BY RANGE( YEAR(hired) ) (
PARTITION p1 VALUES LESS THAN (1991),
PARTITION p2 VALUES LESS THAN (1996),
PARTITION p3 VALUES LESS THAN (2001),
PARTITION p4 VALUES LESS THAN (2005)
);
ALTER TABLE employees ADD PARTITION (
PARTITION p5 VALUES LESS THAN (2010),
PARTITION p6 VALUES LESS THAN MAXVALUE
);
幸运的是,MySQL 的分区实现提供了重新定义分区而不丢失数据的方法。让我们首先看一下涉及RANGE
分区的几个简单示例。回想一下现在定义如下的members
表:
mysql> SHOW CREATE TABLE members\G
*************************** 1\. row ***************************
Table: members
Create Table: CREATE TABLE `members` (
`id` int(11) DEFAULT NULL,
`fname` varchar(25) DEFAULT NULL,
`lname` varchar(25) DEFAULT NULL,
`dob` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE ( YEAR(dob))
(PARTITION n0 VALUES LESS THAN (1970) ENGINE = InnoDB,
PARTITION n1 VALUES LESS THAN (1980) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */ 1 row in set (0.00 sec)
假设您想将所有出生在 1960 年之前的会员行移动到一个单独的分区中。正如我们已经看到的,这不能通过ALTER TABLE ... ADD PARTITION
来实现。然而,您可以使用另一个与分区相关的扩展来ALTER TABLE
来完成这个任务:
ALTER TABLE members REORGANIZE PARTITION n0 INTO (
PARTITION s0 VALUES LESS THAN (1960),
PARTITION s1 VALUES LESS THAN (1970)
);
实际上,这个命令将分区p0
分割成两个新分区s0
和s1
。它还根据两个PARTITION ... VALUES ...
子句中体现的规则,将存储在p0
中的数据移动到新分区中,因此s0
只包含那些YEAR(dob)
小于 1960 的记录,而s1
包含那些YEAR(dob)
大于或等于 1960 但小于 1970 的行。
REORGANIZE PARTITION
子句也可用于合并相邻分区。您可以撤销对members
表的上一个语句的影响,如下所示:
ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (
PARTITION p0 VALUES LESS THAN (1970)
);
使用REORGANIZE PARTITION
拆分或合并分区时不会丢失任何数据。在执行上述语句时,MySQL 将所有存储在分区s0
和s1
中的记录移动到分区p0
中。
REORGANIZE PARTITION
的一般语法如下所示:
ALTER TABLE *tbl_name*
REORGANIZE PARTITION *partition_list*
INTO (*partition_definitions*);
这里,tbl_name
是分区表的名称,partition_list
是要更改的一个或多个现有分区的名称的逗号分隔列表。partition_definitions
是一个逗号分隔的新分区定义列表,遵循与CREATE TABLE
中使用的partition_definitions
列表相同的规则。在使用REORGANIZE PARTITION
时,您不限于将多个分区合并为一个,或将一个分区分割为多个。例如,您可以将members
表的四个分区重新组织为两个,如下所示:
ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (
PARTITION m0 VALUES LESS THAN (1980),
PARTITION m1 VALUES LESS THAN (2000)
);
您还可以在按LIST
进行分区的表上使用REORGANIZE PARTITION
。让我们回到向列表分区的tt
表添加新分区的问题,并因为新分区的值已经存在于现有分区的值列表中而失败。我们可以通过添加一个仅包含非冲突值的分区,然后重新组织新分区和现有分区,使存储在现有分区中的值现在移动到新分区来处理这个问题:
ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));
ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (
PARTITION p1 VALUES IN (6, 18),
PARTITION np VALUES in (4, 8, 12)
);
在使用ALTER TABLE ... REORGANIZE PARTITION
重新分区按RANGE
或LIST
进行分区的表时,请记住以下要点:
-
用于确定新分区方案的
PARTITION
选项受与CREATE TABLE
语句相同的规则约束。新的
RANGE
分区方案不能有任何重叠的范围;新的LIST
分区方案不能有任何重叠的值集。 -
partition_definitions
列表中的分区组合应该总体上与partition_list
中命名的组合分区涵盖相同的范围或值集。例如,在本节示例中使用的
members
表中,分区p1
和p2
一起涵盖 1980 年至 1999 年的年份。对这两个分区的任何重新组织应该总体上涵盖相同的年份范围。 -
对于按
RANGE
进行分区的表,您只能重新组织相邻分区;您不能跳过范围分区。例如,您不能使用以
ALTER TABLE members REORGANIZE PARTITION p0,p2 INTO ...
开头的语句重新组织示例members
表,因为p0
涵盖 1970 年之前的年份,而p2
涵盖 1990 年至 1999 年的年份,因此这些不是相邻的分区。(在这种情况下,您不能跳过分区p1
。) -
你不能使用
REORGANIZE PARTITION
来改变表使用的分区类型(例如,你不能将RANGE
分区更改为HASH
分区或反之)。你也不能使用这个语句来更改分区表达式或列。要完成这两项任务而不必删除和重新创建表,你可以使用ALTER TABLE ... PARTITION BY ...
,如下所示:ALTER TABLE members PARTITION BY HASH( YEAR(dob) ) PARTITIONS 8;
26.3.2 哈希和键分区的管理
原文:
dev.mysql.com/doc/refman/8.0/en/partitioning-management-hash-key.html
使用哈希或键分区的表在修改分区设置方面非常相似,与按范围或列表分区的表在许多方面不同。因此,本节仅讨论了对使用哈希或键分区的表进行修改。有关对按范围或列表分区的表添加和删除分区的讨论,请参见第 26.3.1 节,“范围和列表分区的管理”。
与可以从按RANGE
或LIST
分区的表中删除分区的方式不同,您无法像从中删除分区一样从按HASH
或KEY
分区的表中删除分区。但是,您可以使用ALTER TABLE ... COALESCE PARTITION
合并HASH
或KEY
分区。假设一个包含有关客户数据的clients
表被分成了 12 个分区,如下所示创建:
CREATE TABLE clients (
id INT,
fname VARCHAR(30),
lname VARCHAR(30),
signed DATE
)
PARTITION BY HASH( MONTH(signed) )
PARTITIONS 12;
要将分区数从 12 减少到 8,请执行以下ALTER TABLE
语句:
mysql> ALTER TABLE clients COALESCE PARTITION 4;
Query OK, 0 rows affected (0.02 sec)
COALESCE
在使用HASH
、KEY
、LINEAR HASH
或LINEAR KEY
分区的表上同样有效。以下是一个类似于前一个示例的示例,唯一不同之处在于表是通过LINEAR KEY
分区的:
mysql> CREATE TABLE clients_lk (
-> id INT,
-> fname VARCHAR(30),
-> lname VARCHAR(30),
-> signed DATE
-> )
-> PARTITION BY LINEAR KEY(signed)
-> PARTITIONS 12;
Query OK, 0 rows affected (0.03 sec)
mysql> ALTER TABLE clients_lk COALESCE PARTITION 4;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
COALESCE PARTITION
后面的数字是要合并到剩余部分中的分区数,换句话说,要从表中删除的分区数。
尝试删除比表中存在的分区更多的分区会导致如下错误:
mysql> ALTER TABLE clients COALESCE PARTITION 18;
ERROR 1478 (HY000): Cannot remove all partitions, use DROP TABLE instead
要将clients
表的分区数从 12 增加到 18,请使用如下ALTER TABLE ... ADD PARTITION
:
ALTER TABLE clients ADD PARTITION PARTITIONS 6;
26.3.3 与表交换分区和子分区
原文:
dev.mysql.com/doc/refman/8.0/en/partitioning-management-exchange.html
在 MySQL 8.0 中,可以使用ALTER TABLE *
pt* EXCHANGE PARTITION *
p* WITH TABLE *
nt*
来交换表分区或子分区与未分区表nt
,其中pt
是分区表,p
是要与未分区表nt
交换的pt
的分区或子分区,前提是以下陈述为真:
-
表
nt
本身没有分区。 -
表
nt
不是临时表。 -
表
pt
和nt
的结构在其他方面是相同的。 -
表
nt
不包含外键引用,也没有其他表有任何外键引用指向nt
。 -
nt
中没有位于p
的分区定义边界之外的行。如果使用WITHOUT VALIDATION
,则不适用此条件。 -
两个表必须使用相同的字符集和校对规则。
-
对于
InnoDB
表,两个表必须使用相同的行格式。要确定InnoDB
表的行格式,请查询INFORMATION_SCHEMA.INNODB_TABLES
。 -
任何分区级别的
MAX_ROWS
设置对于p
必须与为nt
设置的表级别MAX_ROWS
值相同。对于p
的任何分区级别的MIN_ROWS
设置也必须与为nt
设置的表级别MIN_ROWS
值相同。无论
pt
是否具有显式的表级别MAX_ROWS
或MIN_ROWS
选项生效,这在任何情况下都是正确的。 -
AVG_ROW_LENGTH
在表pt
和表nt
之间不能有差异。 -
表
pt
不能有任何使用DATA DIRECTORY
选项的分区。这个限制在 MySQL 8.0.14 及更高版本中对InnoDB
表解除。 -
INDEX DIRECTORY
在表和要与之交换的分区之间不能有差异。 -
任何表或分区
TABLESPACE
选项都不能在任何表中使用。
除了通常需要的ALTER
、INSERT
和CREATE
权限外,您必须具有DROP
权限才能执行ALTER TABLE ... EXCHANGE PARTITION
。
您还应该了解ALTER TABLE ... EXCHANGE PARTITION
的以下影响:
-
执行
ALTER TABLE ... EXCHANGE PARTITION
不会触发分区表或要交换表上的任何触发器。 -
交换表中的任何
AUTO_INCREMENT
列都会被重置。 -
使用
ALTER TABLE ... EXCHANGE PARTITION
时,IGNORE
关键字不起作用。
ALTER TABLE ... EXCHANGE PARTITION
的语法如下,其中pt
是分区表,p
是要交换的分区(或子分区),nt
是要与p
交换的非分区表:
ALTER TABLE *pt*
EXCHANGE PARTITION *p*
WITH TABLE *nt*;
可选地,你可以附加WITH VALIDATION
或WITHOUT VALIDATION
。当指定WITHOUT VALIDATION
时,ALTER TABLE ... EXCHANGE PARTITION
操作在交换分区到非分区表时不执行逐行验证,允许数据库管理员承担确保行在分区定义边界内的责任。WITH VALIDATION
是默认选项。
在单个ALTER TABLE EXCHANGE PARTITION
语句中,只能将一个分区或子分区与一个非分区表交换。要交换多个分区或子分区,请使用多个ALTER TABLE EXCHANGE PARTITION
语句。EXCHANGE PARTITION
不能与其他ALTER TABLE
选项结合使用。分区表使用的分区和(如果适用)子分区可以是 MySQL 8.0 支持的任何类型。
与非分区表交换分区
假设已经使用以下 SQL 语句创建和填充了分区表e
:
CREATE TABLE e (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30)
)
PARTITION BY RANGE (id) (
PARTITION p0 VALUES LESS THAN (50),
PARTITION p1 VALUES LESS THAN (100),
PARTITION p2 VALUES LESS THAN (150),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
);
INSERT INTO e VALUES
(1669, "Jim", "Smith"),
(337, "Mary", "Jones"),
(16, "Frank", "White"),
(2005, "Linda", "Black");
现在我们创建一个名为e2
的非分区副本e
。可以使用mysql客户端来完成,如下所示:
mysql> CREATE TABLE e2 LIKE e;
Query OK, 0 rows affected (0.04 sec)
mysql> ALTER TABLE e2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
通过查询信息模式PARTITIONS
表,你可以看到表e
中包含行的分区,就像这样:
mysql> SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 | 1 |
| p1 | 0 |
| p2 | 0 |
| p3 | 3 |
+----------------+------------+
2 rows in set (0.00 sec)
注意
对于分区InnoDB
表,信息模式PARTITIONS
表中TABLE_ROWS
列中给出的行数仅是 SQL 优化中使用的估计值,并不总是准确的。
要交换表e
中的分区p0
与表e2
,可以使用ALTER TABLE
,如下所示:
mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
Query OK, 0 rows affected (0.04 sec)
更准确地说,刚刚执行的语句导致在分区中找到的任何行与表中找到的行交换。你可以通过再次查询信息模式PARTITIONS
表来观察这是如何发生的。之前在分区p0
中找到的表行不再存在:
mysql> SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 | 0 |
| p1 | 0 |
| p2 | 0 |
| p3 | 3 |
+----------------+------------+
4 rows in set (0.00 sec)
如果查询表e2
,你会发现“缺失”的行现在可以在那里找到:
mysql> SELECT * FROM e2;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 16 | Frank | White |
+----+-------+-------+
1 row in set (0.00 sec)
与分区交换的表不一定要为空。为了演示这一点,我们首先向表e
插入一行新数据,确保这行数据存储在分区p0
中,方法是选择一个小于 50 的id
列值,并在之后通过查询PARTITIONS
表进行验证:
mysql> INSERT INTO e VALUES (41, "Michael", "Green");
Query OK, 1 row affected (0.05 sec)
mysql> SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 | 1 |
| p1 | 0 |
| p2 | 0 |
| p3 | 3 |
+----------------+------------+
4 rows in set (0.00 sec)
现在我们再次使用与之前相同的ALTER TABLE
语句交换分区p0
与表e2
:
mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
Query OK, 0 rows affected (0.28 sec)
以下查询的输出显示,在发出ALTER TABLE
语句之前存储在分区p0
中的表行和存储在表e2
中的表行现在已经交换位置:
mysql> SELECT * FROM e;
+------+-------+-------+
| id | fname | lname |
+------+-------+-------+
| 16 | Frank | White |
| 1669 | Jim | Smith |
| 337 | Mary | Jones |
| 2005 | Linda | Black |
+------+-------+-------+
4 rows in set (0.00 sec)
mysql> SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 | 1 |
| p1 | 0 |
| p2 | 0 |
| p3 | 3 |
+----------------+------------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM e2;
+----+---------+-------+
| id | fname | lname |
+----+---------+-------+
| 41 | Michael | Green |
+----+---------+-------+
1 row in set (0.00 sec)
不匹配的行
请记住,在发出ALTER TABLE ... EXCHANGE PARTITION
语句之前,在非分区表中找到的任何行必须满足存储在目标分区中的条件;否则,该语句将失败。为了看到这是如何发生的,首先向e2
插入一行数据,该行数据超出了表e
的分区p0
的定义范围。例如,插入一个id
列值过大的行;然后,再次尝试与分区交换表:
mysql> INSERT INTO e2 VALUES (51, "Ellen", "McDonald");
Query OK, 1 row affected (0.08 sec)
mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
ERROR 1707 (HY000): Found row that does not match the partition
只有WITHOUT VALIDATION
选项才能使此操作成功:
mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITHOUT VALIDATION;
Query OK, 0 rows affected (0.02 sec)
当将一个分区与包含不符合分区定义的行的表交换时,由数据库管理员负责修复不匹配的行,可以使用REPAIR TABLE
或ALTER TABLE ... REPAIR PARTITION
来执行。
不需要逐行验证即可交换分区
当将一个分区与包含许多行的表交换时,为了避免耗时的验证,可以在ALTER TABLE ... EXCHANGE PARTITION
语句中添加WITHOUT VALIDATION
来跳过逐行验证步骤。
以下示例比较了与和不带验证时交换分区与非分区表的执行时间差异。分区表(表e
)包含两个各有 100 万行的分区。表e
的 p0 中的行被移除,并且 p0 与一个有 100 万行的非分区表交换。WITH VALIDATION
操作耗时 0.74 秒。相比之下,WITHOUT VALIDATION
操作只需 0.01 秒。
# Create a partitioned table with 1 million rows in each partition
CREATE TABLE e (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30)
)
PARTITION BY RANGE (id) (
PARTITION p0 VALUES LESS THAN (1000001),
PARTITION p1 VALUES LESS THAN (2000001),
);
mysql> SELECT COUNT(*) FROM e;
| COUNT(*) |
+----------+
| 2000000 |
+----------+
1 row in set (0.27 sec)
# View the rows in each partition
SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+-------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+-------------+
| p0 | 1000000 |
| p1 | 1000000 |
+----------------+-------------+
2 rows in set (0.00 sec)
# Create a nonpartitioned table of the same structure and populate it with 1 million rows
CREATE TABLE e2 (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30)
);
mysql> SELECT COUNT(*) FROM e2;
+----------+
| COUNT(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.24 sec)
# Create another nonpartitioned table of the same structure and populate it with 1 million rows
CREATE TABLE e3 (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30)
);
mysql> SELECT COUNT(*) FROM e3;
+----------+
| COUNT(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.25 sec)
# Drop the rows from p0 of table e
mysql> DELETE FROM e WHERE id < 1000001;
Query OK, 1000000 rows affected (5.55 sec)
# Confirm that there are no rows in partition p0
mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 | 0 |
| p1 | 1000000 |
+----------------+------------+
2 rows in set (0.00 sec)
# Exchange partition p0 of table e with the table e2 'WITH VALIDATION'
mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITH VALIDATION;
Query OK, 0 rows affected (0.74 sec)
# Confirm that the partition was exchanged with table e2
mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 | 1000000 |
| p1 | 1000000 |
+----------------+------------+
2 rows in set (0.00 sec)
# Once again, drop the rows from p0 of table e
mysql> DELETE FROM e WHERE id < 1000001;
Query OK, 1000000 rows affected (5.55 sec)
# Confirm that there are no rows in partition p0
mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 | 0 |
| p1 | 1000000 |
+----------------+------------+
2 rows in set (0.00 sec)
# Exchange partition p0 of table e with the table e3 'WITHOUT VALIDATION'
mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e3 WITHOUT VALIDATION;
Query OK, 0 rows affected (0.01 sec)
# Confirm that the partition was exchanged with table e3
mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 | 1000000 |
| p1 | 1000000 |
+----------------+------------+
2 rows in set (0.00 sec)
如果将一个分区与包含不符合分区定义的行的表进行交换,数据库管理员有责任修复不匹配的行,可以使用REPAIR TABLE
或ALTER TABLE ... REPAIR PARTITION
来执行此操作。
用非分区表交换子分区
你也可以使用ALTER TABLE ... EXCHANGE PARTITION
语句,将分区表的一个子分区(参见第 26.2.6 节,“子分区”)与非分区表进行交换。在下面的示例中,我们首先创建一个按RANGE
分区并按KEY
子分区的表es
,像我们创建表e
一样填充这个表,然后创建一个空的、非分区的副本es2
,如下所示:
mysql> CREATE TABLE es (
-> id INT NOT NULL,
-> fname VARCHAR(30),
-> lname VARCHAR(30)
-> )
-> PARTITION BY RANGE (id)
-> SUBPARTITION BY KEY (lname)
-> SUBPARTITIONS 2 (
-> PARTITION p0 VALUES LESS THAN (50),
-> PARTITION p1 VALUES LESS THAN (100),
-> PARTITION p2 VALUES LESS THAN (150),
-> PARTITION p3 VALUES LESS THAN (MAXVALUE)
-> );
Query OK, 0 rows affected (2.76 sec)
mysql> INSERT INTO es VALUES
-> (1669, "Jim", "Smith"),
-> (337, "Mary", "Jones"),
-> (16, "Frank", "White"),
-> (2005, "Linda", "Black");
Query OK, 4 rows affected (0.04 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE es2 LIKE es;
Query OK, 0 rows affected (1.27 sec)
mysql> ALTER TABLE es2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.70 sec)
Records: 0 Duplicates: 0 Warnings: 0
虽然我们在创建表es
时没有明确命名任何子分区,但我们可以通过在从INFORMATION_SCHEMA
中的PARTITIONS
表中选择时包含SUBPARTITION_NAME
列来获取这些子分区的生成名称,如下所示:
mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS
-> FROM INFORMATION_SCHEMA.PARTITIONS
-> WHERE TABLE_NAME = 'es';
+----------------+-------------------+------------+
| PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS |
+----------------+-------------------+------------+
| p0 | p0sp0 | 1 |
| p0 | p0sp1 | 0 |
| p1 | p1sp0 | 0 |
| p1 | p1sp1 | 0 |
| p2 | p2sp0 | 0 |
| p2 | p2sp1 | 0 |
| p3 | p3sp0 | 3 |
| p3 | p3sp1 | 0 |
+----------------+-------------------+------------+
8 rows in set (0.00 sec)
以下ALTER TABLE
语句将表es
中的子分区p3sp0
与非分区表es2
进行交换:
mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2;
Query OK, 0 rows affected (0.29 sec)
你可以通过发出以下查询来验证行是否已经交换:
mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS
-> FROM INFORMATION_SCHEMA.PARTITIONS
-> WHERE TABLE_NAME = 'es';
+----------------+-------------------+------------+
| PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS |
+----------------+-------------------+------------+
| p0 | p0sp0 | 1 |
| p0 | p0sp1 | 0 |
| p1 | p1sp0 | 0 |
| p1 | p1sp1 | 0 |
| p2 | p2sp0 | 0 |
| p2 | p2sp1 | 0 |
| p3 | p3sp0 | 0 |
| p3 | p3sp1 | 0 |
+----------------+-------------------+------------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM es2;
+------+-------+-------+
| id | fname | lname |
+------+-------+-------+
| 1669 | Jim | Smith |
| 337 | Mary | Jones |
| 2005 | Linda | Black |
+------+-------+-------+
3 rows in set (0.00 sec)
如果表被子分区,你只能交换表的一个子分区,而不是整个分区,如下所示:
mysql> ALTER TABLE es EXCHANGE PARTITION p3 WITH TABLE es2;
ERROR 1704 (HY000): Subpartitioned table, use subpartition instead of partition
表结构严格比较;分区表和非分区表的列和索引的数量、顺序、名称和类型必须完全匹配。此外,两个表必须使用相同的存储引擎:
mysql> CREATE TABLE es3 LIKE e;
Query OK, 0 rows affected (1.31 sec)
mysql> ALTER TABLE es3 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.53 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE es3\G
*************************** 1\. row ***************************
Table: es3
Create Table: CREATE TABLE `es3` (
`id` int(11) NOT NULL,
`fname` varchar(30) DEFAULT NULL,
`lname` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)
mysql> ALTER TABLE es3 ENGINE = MyISAM;
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es3;
ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL
26.3.4 分区的维护
译文:
dev.mysql.com/doc/refman/8.0/en/partitioning-maintenance.html
可以使用针对此类目的而设计的 SQL 语句在分区表上执行许多表和分区维护任务。
可以使用支持分区表的 CHECK TABLE
、OPTIMIZE TABLE
、ANALYZE TABLE
和 REPAIR TABLE
语句来完成分区表的表维护。
您可以使用一些扩展功能来直接在一个或多个分区上执行此类操作,如下列表所述:
-
重建分区。 重建分区;这与删除分区中存储的所有记录,然后重新插入它们具有相同效果。这对于碎片整理很有用。
示例:
ALTER TABLE t1 REBUILD PARTITION p0, p1;
-
优化分区。 如果您从分区中删除了大量行,或者对具有可变长度行的分区表进行了许多更改(即具有
VARCHAR
、BLOB
或TEXT
列),您可以使用ALTER TABLE ... OPTIMIZE PARTITION
来回收未使用的空间并对分区数据文件进行碎片整理。示例:
ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;
在给定分区上使用
OPTIMIZE PARTITION
相当于在该分区上运行CHECK PARTITION
、ANALYZE PARTITION
和REPAIR PARTITION
。一些 MySQL 存储引擎,包括
InnoDB
,不支持每个分区的优化;在这些情况下,ALTER TABLE ... OPTIMIZE PARTITION
会分析并重建整个表,并发出适当的警告(Bug #11751825, Bug #42822)。为避免此问题,请改用ALTER TABLE ... REBUILD PARTITION
和ALTER TABLE ... ANALYZE PARTITION
。 -
分析分区。 这读取并存储分区的键分布。
示例:
ALTER TABLE t1 ANALYZE PARTITION p3;
-
修复分区。 这修复损坏的分区。
示例:
ALTER TABLE t1 REPAIR PARTITION p0,p1;
通常情况下,当分区包含重复键错误时,
REPAIR PARTITION
操作会失败。您可以使用ALTER IGNORE TABLE
选项,此时由于存在重复键而无法移动的所有行将从分区中删除(Bug #16900947)。 -
检查分区。 您可以以与对非分区表使用
CHECK TABLE
相同的方式检查分区中的错误。示例:
ALTER TABLE trb3 CHECK PARTITION p1;
这个语句告诉您表
t1
中分区p1
中的数据或索引是否损坏。如果是这种情况,请使用ALTER TABLE ... REPAIR PARTITION
来修复该分区。通常,当分区包含重复键错误时,
CHECK PARTITION
会失败。您可以在此选项中使用ALTER IGNORE TABLE
,在这种情况下,该语句将返回在发现重复键违规的分区中每一行的内容。仅报告表的分区表达式中的列的值。(Bug #16900947)
列出的每个语句还支持关键字ALL
代替分区名称列表。使用ALL
会导致该语句作用于表中的所有分区。
您还可以使用ALTER TABLE ... TRUNCATE PARTITION
截断分区。该语句可用于删除一个或多个分区中的所有行,方式与TRUNCATE TABLE
删除表中的所有行类似。
ALTER TABLE ... TRUNCATE PARTITION ALL
截断表中的所有分区。
26.3.5 获取有关分区的信息
本节讨论获取有关现有分区的信息,可以通过多种方式进行。获取此类信息的方法包括以下内容:
-
使用
SHOW CREATE TABLE
语句查看创建分区表时使用的分区子句。 -
使用
SHOW TABLE STATUS
语句来确定表是否被分区。 -
查询信息模式
PARTITIONS
表。 -
使用语句
EXPLAIN SELECT
来查看给定SELECT
使用的分区。
从 MySQL 8.0.16 开始,当对分区表进行插入、删除或更新操作时,二进制日志记录有关发生行事件的分区和(如果有)子分区的信息。即使涉及的表相同,也为发生在不同分区或子分区中的修改创建新的行事件。因此,如果一个事务涉及三个分区或子分区,将生成三个行事件。对于更新事件,分区信息被记录在“之前”图像和“之后”图像中。如果在使用mysqlbinlog查看二进制日志时指定了-v
或--verbose
选项,则会显示分区信息。仅当使用基于行的日志记录时(binlog_format=ROW
)才记录分区信息。
如本章其他地方所讨论的,SHOW CREATE TABLE
在其输出中包含用于创建分区表的PARTITION BY
子句。例如:
mysql> SHOW CREATE TABLE trb3\G
*************************** 1\. row ***************************
Table: trb3
Create Table: CREATE TABLE `trb3` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY RANGE (YEAR(purchased))
(PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB) */ 0 row in set (0.00 sec)
对于分区表,SHOW TABLE STATUS
的输出与非分区表相同,只是Create_options
列包含字符串partitioned
。Engine
列包含表的所有分区使用的存储引擎的名称。(有关此语句的更多信息,请参见第 15.7.7.38 节,“SHOW TABLE STATUS Statement”。)
您还可以从INFORMATION_SCHEMA
中获取有关分区的信息,其中包含一个PARTITIONS
表。请参阅第 28.3.21 节,“INFORMATION_SCHEMA PARTITIONS 表”。
可以通过使用解释
来确定分区表中哪些分区涉及给定的SELECT
查询。解释
输出中的partitions
列列出了查询将匹配的记录的分区。
假设创建并填充了一个名为trb1
的表如下:
CREATE TABLE trb1 (id INT, name VARCHAR(50), purchased DATE)
PARTITION BY RANGE(id)
(
PARTITION p0 VALUES LESS THAN (3),
PARTITION p1 VALUES LESS THAN (7),
PARTITION p2 VALUES LESS THAN (9),
PARTITION p3 VALUES LESS THAN (11)
);
INSERT INTO trb1 VALUES
(1, 'desk organiser', '2003-10-15'),
(2, 'CD player', '1993-11-05'),
(3, 'TV set', '1996-03-10'),
(4, 'bookcase', '1982-01-10'),
(5, 'exercise bike', '2004-05-09'),
(6, 'sofa', '1987-06-05'),
(7, 'popcorn maker', '2001-11-22'),
(8, 'aquarium', '1992-08-04'),
(9, 'study desk', '1984-09-16'),
(10, 'lava lamp', '1998-12-25');
你可以看到在查询中使用了哪些分区,比如SELECT * FROM trb1;
,如下所示:
mysql> EXPLAIN SELECT * FROM trb1\G
*************************** 1\. row ***************************
id: 1
select_type: SIMPLE
table: trb1
partitions: p0,p1,p2,p3
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10
Extra: Using filesort
在这种情况下,所有四个分区都被搜索。然而,当在查询中添加使用分区键的限制条件时,您可以看到只有包含匹配值的分区被扫描,如下所示:
mysql> EXPLAIN SELECT * FROM trb1 WHERE id < 5\G
*************************** 1\. row ***************************
id: 1
select_type: SIMPLE
table: trb1
partitions: p0,p1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10
Extra: Using where
解释
还提供了有关使用的键和可能的键的信息:
mysql> ALTER TABLE trb1 ADD PRIMARY KEY (id);
Query OK, 10 rows affected (0.03 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> EXPLAIN SELECT * FROM trb1 WHERE id < 5\G
*************************** 1\. row ***************************
id: 1
select_type: SIMPLE
table: trb1
partitions: p0,p1
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 7
Extra: Using where
如果使用解释
来检查针对非分区表的查询,不会产生错误,但partitions
列的值始终为NULL
。
解释
输出的rows
列显示表中的总行数。
另请参阅第 15.8.2 节,“解释语句”。
26.4 分区修剪
原文:
dev.mysql.com/doc/refman/8.0/en/partitioning-pruning.html
分区修剪优化基于一个相对简单的概念,可以描述为“不要扫描那些不可能有匹配值的分区”。假设通过以下语句创建了一个分区表t1
:
CREATE TABLE t1 (
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
region_code TINYINT UNSIGNED NOT NULL,
dob DATE NOT NULL
)
PARTITION BY RANGE( region_code ) (
PARTITION p0 VALUES LESS THAN (64),
PARTITION p1 VALUES LESS THAN (128),
PARTITION p2 VALUES LESS THAN (192),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
假设您希望从类似于以下语句的SELECT
语句中获取结果:
SELECT fname, lname, region_code, dob
FROM t1
WHERE region_code > 125 AND region_code < 130;
很容易看出,应该返回的行中没有任何位于分区p0
或p3
中的行;也就是说,我们只需要在分区p1
和p2
中搜索匹配的行。通过限制搜索范围,可以比扫描表中的所有分区更少的时间和精力来找到匹配的行。这种“剪掉”不需要的分区的操作称为修剪。当优化器在执行此查询时可以利用分区修剪时,查询的执行速度可能比针对包含相同列定义和数据的非分区表的相同查询快一个数量级。
优化器可以在WHERE
条件可以简化为以下两种情况之一时执行修剪:
-
*
partition_column* = *
constant*
-
*
partition_column* IN (*
constant1*, *
constant2*, ..., *
constantN*)
在第一种情况下,优化器简单地对给定值评估分区表达式,确定包含该值的分区,并仅扫描此分区。在许多情况下,等号可以替换为另一个算术比较,包括<
、>
、<=
、>=
和<>
。一些在WHERE
子句中使用BETWEEN
的查询也可以利用分区修剪。请参见本节后面的示例。
在第二种情况下,优化器对列表中的每个值评估分区表达式,创建一个匹配分区的列表,然后仅扫描此分区列表中的分区。
SELECT
、DELETE
和UPDATE
语句支持分区修剪。INSERT
语句也仅访问每个插入行的一个分区;即使对于使用HASH
或KEY
进行分区的表,这也是正确的,尽管目前在EXPLAIN
的输出中没有显示。
修剪也可以应用于短范围,优化器可以将其转换为等效值列表。例如,在前面的例子中,WHERE
子句可以转换为 WHERE region_code IN (126, 127, 128, 129)
。然后优化器可以确定列表中的前两个值位于分区 p1
中,剩下的两个值位于分区 p2
中,其他分区不包含相关值,因此不需要搜索匹配行。
优化器还可以对使用 RANGE COLUMNS
或 LIST COLUMNS
分区的表上涉及多列比较的 WHERE
条件执行修剪。
只要分区表达式由相等性或可减少为一组相等性的范围组成,或者分区表达式表示递增或递减关系,就可以应用这种优化。当分区表达式使用 YEAR()
或 TO_DAYS()
函数时,也可以应用修剪到基于 DATE
或 DATETIME
列分区的表。当分区表达式使用 TO_SECONDS()
函数时,也可以应用修剪到这样的表。
假设表 t2
,根据一个 DATE
列进行分区,是使用以下语句创建的:
CREATE TABLE t2 (
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
region_code TINYINT UNSIGNED NOT NULL,
dob DATE NOT NULL
)
PARTITION BY RANGE( YEAR(dob) ) (
PARTITION d0 VALUES LESS THAN (1970),
PARTITION d1 VALUES LESS THAN (1975),
PARTITION d2 VALUES LESS THAN (1980),
PARTITION d3 VALUES LESS THAN (1985),
PARTITION d4 VALUES LESS THAN (1990),
PARTITION d5 VALUES LESS THAN (2000),
PARTITION d6 VALUES LESS THAN (2005),
PARTITION d7 VALUES LESS THAN MAXVALUE
);
以下使用 t2
的语句可以利用分区修剪:
SELECT * FROM t2 WHERE dob = '1982-06-23';
UPDATE t2 SET region_code = 8 WHERE dob BETWEEN '1991-02-15' AND '1997-04-25';
DELETE FROM t2 WHERE dob >= '1984-06-21' AND dob <= '1999-06-21'
对于最后一条语句,优化器还可以执行以下操作:
-
找到包含范围低端的分区。
YEAR('1984-06-21')
返回值为1984
,该值位于分区d3
中。 -
找到包含范围高端的分区。
YEAR('1999-06-21')
计算结果为1999
,该值位于分区d5
中。 -
仅扫描这两个分区以及可能位于它们之间的任何分区。
在这种情况下,这意味着只有分区
d3
、d4
和d5
被扫描。其余分区可以安全地被忽略(并且被忽略)。
重要提示
对于针对分区表的语句中 WHERE
条件引用的无效 DATE
和 DATETIME
值,将被视为 NULL
。这意味着像 SELECT * FROM *
partitioned_table* WHERE *
date_column* < '2008-12-00'
这样的查询不会返回任何值(参见 Bug #40972)。
到目前为止,我们只看过使用 RANGE
分区的示例,但修剪也可以应用于其他分区类型。
考虑一个由LIST
分区的表,其中分区表达式是递增或递减的,例如这里显示的表t3
。(在本例中,为简洁起见,我们假设region_code
列的值限定在 1 到 10 之间。)
CREATE TABLE t3 (
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
region_code TINYINT UNSIGNED NOT NULL,
dob DATE NOT NULL
)
PARTITION BY LIST(region_code) (
PARTITION r0 VALUES IN (1, 3),
PARTITION r1 VALUES IN (2, 5, 8),
PARTITION r2 VALUES IN (4, 9),
PARTITION r3 VALUES IN (6, 7, 10)
);
对于像SELECT * FROM t3 WHERE region_code BETWEEN 1 AND 3
这样的语句,优化器确定值 1、2 和 3 在哪些分区中找到(r0
和r1
),并跳过其余的分区(r2
和r3
)。
对于由HASH
或[LINEAR] KEY
分区的表,如果WHERE
子句使用简单的=
关系针对分区表达式中使用的列,则也可以进行分区修剪。考虑创建如下表:
CREATE TABLE t4 (
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
region_code TINYINT UNSIGNED NOT NULL,
dob DATE NOT NULL
)
PARTITION BY KEY(region_code)
PARTITIONS 8;
将列值与常量进行比较的语句可以被修剪:
UPDATE t4 WHERE region_code = 7;
对于短范围,修剪也可以用于,因为优化器可以将这种条件转换为IN
关系。例如,使用之前定义的相同表t4
,可以修剪以下查询:
SELECT * FROM t4 WHERE region_code > 2 AND region_code < 6;
SELECT * FROM t4 WHERE region_code BETWEEN 3 AND 5;
在这两种情况下,优化器将WHERE
子句转换为WHERE region_code IN (3, 4, 5)
。
重要
只有当范围大小小于分区数时才使用这种优化。考虑这个语句:
DELETE FROM t4 WHERE region_code BETWEEN 4 AND 12;
WHERE
子句中的范围涵盖了 9 个值(4, 5, 6, 7, 8, 9, 10, 11, 12),但t4
只有 8 个分区。这意味着DELETE
无法被修剪。
当表由HASH
或[LINEAR] KEY
分区时,修剪只能用于整数列。例如,这个语句不能使用修剪,因为dob
是一个DATE
列:
SELECT * FROM t4 WHERE dob >= '2001-04-14' AND dob <= '2005-10-15';
然而,如果表在INT
列中存储年份值,则查询WHERE year_col >= 2001 AND year_col <= 2005
可以被修剪。
使用提供自动分区的存储引擎的表,例如 MySQL Cluster 使用的NDB
存储引擎,如果它们被显式分区,则可以被修剪。
26.5 分区选择
原文:
dev.mysql.com/doc/refman/8.0/en/partitioning-selection.html
支持为匹配给定WHERE
条件的行显式选择分区和子分区。分区选择类似于分区修剪,只检查特定分区是否匹配,但在两个关键方面有所不同:
-
要检查的分区由语句的发出者指定,与自动分区修剪不同。
-
分区修剪仅适用于查询,而显式选择分区支持查询和一些 DML 语句。
支持显式分区选择的 SQL 语句列在此处:
-
SELECT
-
DELETE
-
INSERT
-
REPLACE
-
UPDATE
-
LOAD DATA
。 -
LOAD XML
。
本节的其余部分讨论了显式分区选择,通常适用于刚刚列出的语句,并提供了一些示例。
显式分区选择是使用PARTITION
选项实现的。对于所有支持的语句,此选项使用以下语法:
PARTITION (*partition_names*)
*partition_names*:
*partition_name*, ...
此选项始终跟随分区或子分区所属的表的名称。partition_names
是要使用的分区或子分区的逗号分隔列表。此列表中的每个名称必须是指定表的现有分区或子分区的名称;如果找不到任何分区或子分区,则该语句将失败并显示错误(分区'partition_name
'不存在)。partition_names
中列出的分区和子分区可以以任何顺序列出,并且可以重叠。
使用PARTITION
选项时,仅检查列出的分区和子分区以匹配行。此选项可用于SELECT
语句,以确定哪些行属于给定分区。考虑一个名为employees
的分区表,使用以下语句创建和填充:
SET @@SQL_MODE = '';
CREATE TABLE employees (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
fname VARCHAR(25) NOT NULL,
lname VARCHAR(25) NOT NULL,
store_id INT NOT NULL,
department_id INT NOT NULL
)
PARTITION BY RANGE(id) (
PARTITION p0 VALUES LESS THAN (5),
PARTITION p1 VALUES LESS THAN (10),
PARTITION p2 VALUES LESS THAN (15),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
INSERT INTO employees VALUES
('', 'Bob', 'Taylor', 3, 2), ('', 'Frank', 'Williams', 1, 2),
('', 'Ellen', 'Johnson', 3, 4), ('', 'Jim', 'Smith', 2, 4),
('', 'Mary', 'Jones', 1, 1), ('', 'Linda', 'Black', 2, 3),
('', 'Ed', 'Jones', 2, 1), ('', 'June', 'Wilson', 3, 1),
('', 'Andy', 'Smith', 1, 3), ('', 'Lou', 'Waters', 2, 4),
('', 'Jill', 'Stone', 1, 4), ('', 'Roger', 'White', 3, 2),
('', 'Howard', 'Andrews', 1, 2), ('', 'Fred', 'Goldberg', 3, 3),
('', 'Barbara', 'Brown', 2, 3), ('', 'Alice', 'Rogers', 2, 2),
('', 'Mark', 'Morgan', 3, 3), ('', 'Karen', 'Cole', 3, 2);
您可以这样查看存储在分区p1
中的行:
mysql> SELECT * FROM employees PARTITION (p1);
+----+-------+--------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+--------+----------+---------------+
| 5 | Mary | Jones | 1 | 1 |
| 6 | Linda | Black | 2 | 3 |
| 7 | Ed | Jones | 2 | 1 |
| 8 | June | Wilson | 3 | 1 |
| 9 | Andy | Smith | 1 | 3 |
+----+-------+--------+----------+---------------+
5 rows in set (0.00 sec)
结果与通过查询SELECT * FROM employees WHERE id BETWEEN 5 AND 9
获得的结果相同。
要从多个分区获取行,请将它们的名称作为逗号分隔的列表提供。例如,SELECT * FROM employees PARTITION (p1, p2)
返回分区p1
和p2
中的所有行,同时排除其余分区的行。
对分区表的任何有效查询都可以通过PARTITION
选项重写,以限制结果为一个或多个所需的分区。您可以使用WHERE
条件、ORDER BY
和LIMIT
选项等。您还可以使用带有HAVING
和GROUP BY
选项的聚合函数。在先前定义的employees
表上运行以下每个查询都会产生有效结果:
mysql> SELECT * FROM employees PARTITION (p0, p2)
-> WHERE lname LIKE 'S%';
+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
| 4 | Jim | Smith | 2 | 4 |
| 11 | Jill | Stone | 1 | 4 |
+----+-------+-------+----------+---------------+
2 rows in set (0.00 sec)
mysql> SELECT id, CONCAT(fname, ' ', lname) AS name
-> FROM employees PARTITION (p0) ORDER BY lname;
+----+----------------+
| id | name |
+----+----------------+
| 3 | Ellen Johnson |
| 4 | Jim Smith |
| 1 | Bob Taylor |
| 2 | Frank Williams |
+----+----------------+
4 rows in set (0.06 sec)
mysql> SELECT store_id, COUNT(department_id) AS c
-> FROM employees PARTITION (p1,p2,p3)
-> GROUP BY store_id HAVING c > 4;
+---+----------+
| c | store_id |
+---+----------+
| 5 | 2 |
| 5 | 3 |
+---+----------+
2 rows in set (0.00 sec)
使用分区选择的语句可以与使用任何支持的分区类型的表一起使用。当使用[LINEAR] HASH
或[LINEAR] KEY
分区创建表时,如果未指定分区的名称,MySQL 会自动将分区命名为p0
、p1
、p2
、...、p*
N-1*
,其中N
是分区的数量。对于未明确命名的子分区,MySQL 会自动为每个分区p*
X*
中的子分区分配名称p*
X*sp0
、p*
X*sp1
、p*
X*sp2
、...、p*
X*sp*
M-1*
,其中M
是子分区的数量。在针对这个表执行SELECT
(或其他允许显式分区选择的 SQL 语句)时,您可以在PARTITION
选项中使用这些生成的名称,如下所示:
mysql> CREATE TABLE employees_sub (
-> id INT NOT NULL AUTO_INCREMENT,
-> fname VARCHAR(25) NOT NULL,
-> lname VARCHAR(25) NOT NULL,
-> store_id INT NOT NULL,
-> department_id INT NOT NULL,
-> PRIMARY KEY pk (id, lname)
-> )
-> PARTITION BY RANGE(id)
-> SUBPARTITION BY KEY (lname)
-> SUBPARTITIONS 2 (
-> PARTITION p0 VALUES LESS THAN (5),
-> PARTITION p1 VALUES LESS THAN (10),
-> PARTITION p2 VALUES LESS THAN (15),
-> PARTITION p3 VALUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (1.14 sec)
mysql> INSERT INTO employees_sub # reuse data in employees table
-> SELECT * FROM employees;
Query OK, 18 rows affected (0.09 sec)
Records: 18 Duplicates: 0 Warnings: 0
mysql> SELECT id, CONCAT(fname, ' ', lname) AS name
-> FROM employees_sub PARTITION (p2sp1);
+----+---------------+
| id | name |
+----+---------------+
| 10 | Lou Waters |
| 14 | Fred Goldberg |
+----+---------------+
2 rows in set (0.00 sec)
您还可以在SELECT
部分的INSERT ... SELECT
语句中使用PARTITION
选项,如下所示:
mysql> CREATE TABLE employees_copy LIKE employees;
Query OK, 0 rows affected (0.28 sec)
mysql> INSERT INTO employees_copy
-> SELECT * FROM employees PARTITION (p2);
Query OK, 5 rows affected (0.04 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM employees_copy;
+----+--------+----------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+--------+----------+----------+---------------+
| 10 | Lou | Waters | 2 | 4 |
| 11 | Jill | Stone | 1 | 4 |
| 12 | Roger | White | 3 | 2 |
| 13 | Howard | Andrews | 1 | 2 |
| 14 | Fred | Goldberg | 3 | 3 |
+----+--------+----------+----------+---------------+
5 rows in set (0.00 sec)
分区选择也可以与连接一起使用。假设我们使用以下语句创建和填充了两个表:
CREATE TABLE stores (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
city VARCHAR(30) NOT NULL
)
PARTITION BY HASH(id)
PARTITIONS 2;
INSERT INTO stores VALUES
('', 'Nambucca'), ('', 'Uranga'),
('', 'Bellingen'), ('', 'Grafton');
CREATE TABLE departments (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL
)
PARTITION BY KEY(id)
PARTITIONS 2;
INSERT INTO departments VALUES
('', 'Sales'), ('', 'Customer Service'),
('', 'Delivery'), ('', 'Accounting');
您可以明确地从连接中的任何或所有表中选择分区(或子分区,或两者)。(用于从给定表中选择分区的PARTITION
选项紧随表名之后,位于所有其他选项之前,包括任何表别名。)例如,以下查询获取所有在销售或交付部门(departments
表的分区p1
)中的商店(stores
表的分区p0
)中工作的员工的姓名、员工 ID、部门和城市:
mysql> SELECT
-> e.id AS 'Employee ID', CONCAT(e.fname, ' ', e.lname) AS Name,
-> s.city AS City, d.name AS department
-> FROM employees AS e
-> JOIN stores PARTITION (p1) AS s ON e.store_id=s.id
-> JOIN departments PARTITION (p0) AS d ON e.department_id=d.id
-> ORDER BY e.lname;
+-------------+---------------+-----------+------------+
| Employee ID | Name | City | department |
+-------------+---------------+-----------+------------+
| 14 | Fred Goldberg | Bellingen | Delivery |
| 5 | Mary Jones | Nambucca | Sales |
| 17 | Mark Morgan | Bellingen | Delivery |
| 9 | Andy Smith | Nambucca | Delivery |
| 8 | June Wilson | Bellingen | Sales |
+-------------+---------------+-----------+------------+
5 rows in set (0.00 sec)
有关 MySQL 中连接的一般信息,请参见第 15.2.13.2 节,“JOIN Clause”。
当PARTITION
选项与DELETE
语句一起使用时,只有在选项中列出的那些分区(和子分区,如果有)会被检查以删除行。任何其他分区都会被忽略,如下所示:
mysql> SELECT * FROM employees WHERE fname LIKE 'j%';
+----+-------+--------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+--------+----------+---------------+
| 4 | Jim | Smith | 2 | 4 |
| 8 | June | Wilson | 3 | 1 |
| 11 | Jill | Stone | 1 | 4 |
+----+-------+--------+----------+---------------+
3 rows in set (0.00 sec)
mysql> DELETE FROM employees PARTITION (p0, p1)
-> WHERE fname LIKE 'j%';
Query OK, 2 rows affected (0.09 sec)
mysql> SELECT * FROM employees WHERE fname LIKE 'j%';
+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
| 11 | Jill | Stone | 1 | 4 |
+----+-------+-------+----------+---------------+
1 row in set (0.00 sec)
只有与WHERE
条件匹配的p0
和p1
分区中的两行被删除。从第二次运行SELECT
时的结果中可以看到,表中仍然存在一行与WHERE
条件匹配,但位于不同的分区(p2
)。
使用显式分区选择的UPDATE
语句的行为相同;只有在确定要更新的行时,才考虑PARTITION
选项引用的分区中的行,可以通过执行以下语句来查看:
mysql> UPDATE employees PARTITION (p0)
-> SET store_id = 2 WHERE fname = 'Jill';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> SELECT * FROM employees WHERE fname = 'Jill';
+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
| 11 | Jill | Stone | 1 | 4 |
+----+-------+-------+----------+---------------+
1 row in set (0.00 sec)
mysql> UPDATE employees PARTITION (p2)
-> SET store_id = 2 WHERE fname = 'Jill';
Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM employees WHERE fname = 'Jill';
+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
| 11 | Jill | Stone | 2 | 4 |
+----+-------+-------+----------+---------------+
1 row in set (0.00 sec)
同样,当与DELETE
一起使用PARTITION
时,只有在分区列表中命名的分区中的行才会被检查删除。
对于插入行的语句,行为有所不同,找不到合适分区会导致语句失败。这对于INSERT
和REPLACE
语句都是适用的,如下所示:
mysql> INSERT INTO employees PARTITION (p2) VALUES (20, 'Jan', 'Jones', 1, 3);
ERROR 1729 (HY000): Found a row not matching the given partition set
mysql> INSERT INTO employees PARTITION (p3) VALUES (20, 'Jan', 'Jones', 1, 3);
Query OK, 1 row affected (0.07 sec)
mysql> REPLACE INTO employees PARTITION (p0) VALUES (20, 'Jan', 'Jones', 3, 2);
ERROR 1729 (HY000): Found a row not matching the given partition set
mysql> REPLACE INTO employees PARTITION (p3) VALUES (20, 'Jan', 'Jones', 3, 2);
Query OK, 2 rows affected (0.09 sec)
对于使用InnoDB
存储引擎的分区表写入多行的语句:如果VALUES
后面的列表中的任何行无法写入到partition_names
列表中指定的分区之一,则整个语句将失败,不会写入任何行。这在下面的示例中展示了INSERT
语句,重用了之前创建的employees
表:
mysql> ALTER TABLE employees
-> REORGANIZE PARTITION p3 INTO (
-> PARTITION p3 VALUES LESS THAN (20),
-> PARTITION p4 VALUES LESS THAN (25),
-> PARTITION p5 VALUES LESS THAN MAXVALUE
-> );
Query OK, 6 rows affected (2.09 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE employees\G
*************************** 1\. row ***************************
Table: employees
Create Table: CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fname` varchar(25) NOT NULL,
`lname` varchar(25) NOT NULL,
`store_id` int(11) NOT NULL,
`department_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (15) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (20) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (25) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ 1 row in set (0.00 sec)
mysql> INSERT INTO employees PARTITION (p3, p4) VALUES
-> (24, 'Tim', 'Greene', 3, 1), (26, 'Linda', 'Mills', 2, 1);
ERROR 1729 (HY000): Found a row not matching the given partition set
mysql> INSERT INTO employees PARTITION (p3, p4, p5) VALUES
-> (24, 'Tim', 'Greene', 3, 1), (26, 'Linda', 'Mills', 2, 1);
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
对于写入多行的INSERT
语句和REPLACE
语句,上述内容都是适用的。
对于使用提供自动分区的存储引擎(如NDB
)的表,分区选择被禁用。
26.6 关于分区的限制和限制
原文:
dev.mysql.com/doc/refman/8.0/en/partitioning-limitations.html
26.6.1 分区键、主键和唯一键
26.6.2 与存储引擎相关的分区限制
26.6.3 与函数相关的分区限制
本节讨论了 MySQL 分区支持的当前限制和限制。
禁止的结构。 不允许在分区表达式中使用以下结构:
-
存储过程、存储函数、可加载函数或插件。
-
声明变量或用户变量。
有关允许在分区表达式中使用的 SQL 函数列表,请参见 第 26.6.3 节,“与函数相关的分区限制”。
算术和逻辑运算符。 在分区表达式中允许使用算术运算符 +
, -
, 和 *
。然而,结果必须是整数值或 NULL
(除了 [LINEAR] KEY
分区,如本章其他地方讨论的那样;有关更多信息,请参见 第 26.2 节,“分区类型”)。
DIV
运算符也受支持;不允许使用 /
运算符。
位运算符 |
, &
, ^
, <<
, >>
, 和 ~
不允许在分区表达式中使用。
服务器 SQL 模式。 使用自定义分区的表不会保留创建时有效的 SQL 模式。如本手册其他地方所述(请参见 第 7.1.11 节,“服务器 SQL 模式”),许多 MySQL 函数和运算符的结果可能根据服务器 SQL 模式而变化。因此,在创建分区表后任何时候更改 SQL 模式可能导致这些表行为发生重大变化,并可能轻易导致数据损坏或丢失。因此,强烈建议您在创建分区表后永远不要更改服务器 SQL 模式。
对于服务器 SQL 模式中的一种更改使分区表无法使用的情况,请考虑以下 CREATE TABLE
语句,只有在 NO_UNSIGNED_SUBTRACTION
模式生效时才能成功执行:
mysql> SELECT @@sql_mode;
+------------+
| @@sql_mode |
+------------+
| |
+------------+
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),
-> PARTITION p4 VALUES LESS THAN (MAXVALUE)
-> );
ERROR 1563 (HY000): Partition constant is out of partition function domain
mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)
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),
-> PARTITION p4 VALUES LESS THAN (MAXVALUE)
-> );
Query OK, 0 rows affected (0.05 sec)
如果在创建 tu
后移除 NO_UNSIGNED_SUBTRACTION
服务器 SQL 模式,则可能无法再访问此表:
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
另请参阅 第 7.1.11 节,“服务器 SQL 模式”。
服务器 SQL 模式也会影响分区表的复制。源和副本上不同的 SQL 模式可能导致分区表达式的评估不同;这可能导致数据在源和副本的给定表的副本之间分布不同,并且甚至可能导致在源上成功的分区表插入在副本上失败。为获得最佳结果,您应始终在源和副本上使用相同的服务器 SQL 模式。
性能考虑。 分区操作对性能的一些影响如下:
-
文件系统操作。 分区和重新分区操作(例如
ALTER TABLE
使用PARTITION BY ...
,REORGANIZE PARTITION
, 或REMOVE PARTITIONING
)的实现取决于文件系统操作。这意味着这些操作的速度受到诸如文件系统类型和特性、磁盘速度、交换空间、操作系统的文件处理效率以及与文件处理相关的 MySQL 服务器选项和变量的影响。特别是,您应确保large_files_support
已启用,并且open_files_limit
已正确设置。涉及InnoDB
表的分区和重新分区操作可能通过启用innodb_file_per_table
变得更加高效。另请参阅 最大分区数。
-
表锁。 通常,对表执行分区操作的进程会对表进行写锁定。对这些表的读取相对不受影响;挂起的
INSERT
和UPDATE
操作将在分区操作完成后立即执行。有关InnoDB
特定的例外情况,请参阅 分区操作。 -
索引;分区修剪。 与非分区表一样,正确使用索引可以显着加快对分区表的查询速度。此外,设计分区表和对这些表的查询以利用分区修剪可以显著提高性能。有关更多信息,请参阅第 26.4 节,“分区修剪”。
支持对分区表进行索引条件下推。请参阅第 10.2.1.6 节,“索引条件下推优化”。
-
使用 LOAD DATA 的性能。 在 MySQL 8.0 中,
LOAD DATA
使用缓冲区来提高性能。您应该知道,为了实现这一点,每个分区的缓冲区使用 130 KB 内存。
最大分区数。 对于不使用NDB
存储引擎的给定表,最大可能的分区数为 8192。此数字包括子分区。
对于使用NDB
存储引擎的表的最大可能的用户定义分区数根据使用的 NDB Cluster 软件版本、数据节点数量和其他因素确定。有关更多信息,请参阅 NDB 和用户定义分区。
如果在创建具有大量分区的表(但少于最大值)时遇到错误消息,例如从存储引擎获取错误...:打开文件时资源不足,您可以通过增加open_files_limit
系统变量的值来解决此问题。但是,这取决于操作系统,并且在所有平台上可能不可行或不可取;有关更多信息,请参阅第 B.3.2.16 节,“文件未找到和类似错误”。在某些情况下,由于其他原因,使用大量(数百个)分区也可能不可取,因此使用更多分区并不会自动导致更好的结果。
另请参阅文件系统操作。
不支持对分区 InnoDB 表使用外键。 使用InnoDB
存储引擎的分区表不支持外键。更具体地说,这意味着以下两个语句是正确的:
-
不得包含外键引用的
InnoDB
表的定义使用用户定义的分区;包含外键引用的InnoDB
表的定义不得分区。 -
任何
InnoDB
表定义都不能包含对用户分区表的外键引用;任何具有用户定义分区的InnoDB
表都不能包含被外键引用的列。
刚刚列出的限制范围包括所有使用InnoDB
存储引擎的表。不允许创建CREATE TABLE
和ALTER TABLE
语句导致违反这些限制的表。
ALTER TABLE ... ORDER BY。 对分区表运行的ALTER TABLE ... ORDER BY *
column*
语句仅导致每个分区内的行排序。
ADD COLUMN ... ALGORITHM=INSTANT。 一旦在分区表上执行ALTER TABLE ... ADD COLUMN ... ALGORITHM=INSTANT
,就不再可能与该表交换分区。
通过修改主键对 REPLACE 语句的影响。 在某些情况下可能是可取的(参见第 26.6.1 节,“分区键、主键和唯一键”),修改表的主键。请注意,如果您的应用程序使用REPLACE
语句并且您这样做,这些语句的结果可能会发生 drastical 改变。有关更多信息和示例,请参见第 15.2.12 节,“REPLACE 语句”。
全文索引。 分区表不支持FULLTEXT
索引或搜索。
空间列。 具有空间数据类型(如POINT
或GEOMETRY
)的列不能在分区表中使用。
临时表。 临时表不能被分区。
日志表。 无法对日志表进行分区;对这样的表运行ALTER TABLE ... PARTITION BY ...
语句会失败并显示错误。
分区键的数据类型。 分区键必须是整数列或解析为整数的表达式。不能使用包含ENUM
列的表达式。列或表达式的值也可以是NULL
;请参见第 26.2.7 节,“MySQL 分区如何处理 NULL”。
这个限制有两个例外:
-
当通过[
LINEAR
]KEY
进行分区时,可以使用除TEXT
或BLOB
之外的任何有效的 MySQL 数据类型作为分区键,因为内部键哈希函数会从这些类型中生成正确的数据类型。例如,以下两个CREATE TABLE
语句是有效的:CREATE TABLE tkc (c1 CHAR) PARTITION BY KEY(c1) PARTITIONS 4; CREATE TABLE tke ( c1 ENUM('red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet') ) PARTITION BY LINEAR KEY(c1) PARTITIONS 6;
-
当使用
RANGE COLUMNS
或LIST COLUMNS
进行分区时,可以使用字符串、DATE
和DATETIME
列。例如,以下每个CREATE TABLE
语句都是有效的:CREATE TABLE rc (c1 INT, c2 DATE) PARTITION BY RANGE COLUMNS(c2) ( PARTITION p0 VALUES LESS THAN('1990-01-01'), PARTITION p1 VALUES LESS THAN('1995-01-01'), PARTITION p2 VALUES LESS THAN('2000-01-01'), PARTITION p3 VALUES LESS THAN('2005-01-01'), PARTITION p4 VALUES LESS THAN(MAXVALUE) ); CREATE TABLE lc (c1 INT, c2 CHAR(1)) PARTITION BY LIST COLUMNS(c2) ( PARTITION p0 VALUES IN('a', 'd', 'g', 'j', 'm', 'p', 's', 'v', 'y'), PARTITION p1 VALUES IN('b', 'e', 'h', 'k', 'n', 'q', 't', 'w', 'z'), PARTITION p2 VALUES IN('c', 'f', 'i', 'l', 'o', 'r', 'u', 'x', NULL) );
前述两个例外情况均不适用于BLOB
或TEXT
列类型。
子查询。 分区键不能是子查询,即使该子查询解析为整数值或NULL
。
不支持列索引前缀用于键分区。 在创建按键分区的表时,分区键中使用列前缀的任何列都不会用于表的分区函数。考虑以下CREATE TABLE
语句,其中有三个VARCHAR
列,主键使用了所有三列,并为其中两列指定了前缀:
CREATE TABLE t1 (
a VARCHAR(10000),
b VARCHAR(25),
c VARCHAR(10),
PRIMARY KEY (a(10), b, c(2))
) PARTITION BY KEY() PARTITIONS 2;
此语句被接受,但实际创建的表实际上是如同您发出了以下语句一样创建的,仅使用了不包含前缀的主键列(列b
)作为分区键:
CREATE TABLE t1 (
a VARCHAR(10000),
b VARCHAR(25),
c VARCHAR(10),
PRIMARY KEY (a(10), b, c(2))
) PARTITION BY KEY(b) PARTITIONS 2;
在 MySQL 8.0.21 之前,没有发出警告或提供任何其他指示表明发生了这种情况,除非所有指定为分区键的列都使用了前缀,此时语句会失败,但会显示一个误导性的错误消息,如下所示:
mysql> CREATE TABLE t2 (
-> a VARCHAR(10000),
-> b VARCHAR(25),
-> c VARCHAR(10),
-> PRIMARY KEY (a(10), b(5), c(2))
-> ) PARTITION BY KEY() PARTITIONS 2;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the
table's partitioning function
当执行ALTER TABLE
或升级此类表时也会发生这种情况。
从 MySQL 8.0.21 开始,不再支持这种宽松行为(并且可能在将来的 MySQL 版本中删除)。从 MySQL 8.0.21 开始,使用一个或多个具有前缀的列作为分区键会对每个这样的列产生警告,如下所示:
mysql> CREATE TABLE t1 (
-> a VARCHAR(10000),
-> b VARCHAR(25),
-> c VARCHAR(10),
-> PRIMARY KEY (a(10), b, c(2))
-> ) PARTITION BY KEY() PARTITIONS 2;
Query OK, 0 rows affected, 2 warnings (1.25 sec)
mysql> SHOW WARNINGS\G
*************************** 1\. row ***************************
Level: Warning
Code: 1681
Message: Column 'test.t1.a' having prefix key part 'a(10)' is ignored by the
partitioning function. Use of prefixed columns in the PARTITION BY KEY() clause
is deprecated and will be removed in a future release.
*************************** 2\. row ***************************
Level: Warning
Code: 1681
Message: Column 'test.t1.c' having prefix key part 'c(2)' is ignored by the
partitioning function. Use of prefixed columns in the PARTITION BY KEY() clause
is deprecated and will be removed in a future release. 2 rows in set (0.00 sec)
这包括分区函数中使用的列被隐式定义为通过使用空的PARTITION BY KEY()
子句来定义表的主键中的列的情况。
在 MySQL 8.0.21 及更高版本中,如果分区键指定的所有列都使用了前缀,则使用的CREATE TABLE
语句将因错误消息而失败,该消息正确标识了问题:
mysql> CREATE TABLE t1 (
-> a VARCHAR(10000),
-> b VARCHAR(25),
-> c VARCHAR(10),
-> PRIMARY KEY (a(10), b(5), c(2))
-> ) PARTITION BY KEY() PARTITIONS 2;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's
partitioning function (prefixed columns are not considered).
有关按键分区表的一般信息,请参见第 26.2.5 节,“键分区”。
子分区存在问题。 子分区必须使用HASH
或KEY
分区。只有RANGE
和LIST
分区可以进行子分区;HASH
和KEY
分区不能进行子分区。
SUBPARTITION BY KEY
要求明确指定子分区列或列,不同于PARTITION BY KEY
的情况,后者可以省略(在这种情况下,默认使用表的主键列)。考虑通过此语句创建的表:
CREATE TABLE ts (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30)
);
您可以通过类似于以下语句创建一个具有相同列、按KEY
分区的表:
CREATE TABLE ts (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30)
)
PARTITION BY KEY()
PARTITIONS 4;
前面的语句被视为如下所示编写,其中表的主键列用作分区列:
CREATE TABLE ts (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30)
)
PARTITION BY KEY(id)
PARTITIONS 4;
然而,尝试使用默认列作为子分区列创建子分区表的以下语句将失败,必须为语句指定列才能成功,如下所示:
mysql> CREATE TABLE ts (
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(30)
-> )
-> PARTITION BY RANGE(id)
-> SUBPARTITION BY KEY()
-> SUBPARTITIONS 4
-> (
-> PARTITION p0 VALUES LESS THAN (100),
-> PARTITION p1 VALUES LESS THAN (MAXVALUE)
-> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ')
mysql> CREATE TABLE ts (
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(30)
-> )
-> PARTITION BY RANGE(id)
-> SUBPARTITION BY KEY(id)
-> SUBPARTITIONS 4
-> (
-> PARTITION p0 VALUES LESS THAN (100),
-> PARTITION p1 VALUES LESS THAN (MAXVALUE)
-> );
Query OK, 0 rows affected (0.07 sec)
这是一个已知问题(参见 Bug #51470)。
数据目录和索引目录选项。 忽略表级DATA DIRECTORY
和INDEX DIRECTORY
选项(参见 Bug #32091)。您可以为InnoDB
表的各个分区或子分区使用这些选项。截至 MySQL 8.0.21,DATA DIRECTORY
子句中指定的目录必须为InnoDB
所知。有关更多信息,请参见使用 DATA DIRECTORY 子句。
修复和重建分区表。 CHECK TABLE
、OPTIMIZE TABLE
、ANALYZE TABLE
和REPAIR TABLE
语句支持分区表。
此外,您可以使用ALTER TABLE ... REBUILD PARTITION
来重建分区表的一个或多个分区;ALTER TABLE ... REORGANIZE PARTITION
也会导致分区重建。有关这两个语句的更多信息,请参见第 15.1.9 节,“ALTER TABLE Statement”。
ANALYZE
、CHECK
、OPTIMIZE
、REPAIR
和TRUNCATE
操作支持子分区。请参见第 15.1.9.1 节,“ALTER TABLE Partition Operations”。
用于分区和子分区的文件名分隔符。 表分区和子分区文件名包括生成的分隔符,如#P#
和#SP#
。此类分隔符的大小写可能有所不同,不应依赖于此。
26.6.1 分区键、主键和唯一键
原文:
dev.mysql.com/doc/refman/8.0/en/partitioning-limitations-partitioning-keys-unique-keys.html
本节讨论了分区键与主键和唯一键的关系。规定这种关系的规则可以表述如下:用于分区表的分区表达式中的所有列必须是表可能具有的每个唯一键的一部分。
换句话说,表上的每个唯一键都必须使用表的分区表达式中的每一列。(这也包括表的主键,因为根据定义,它是一个唯一键。这种特殊情况稍后在本节中讨论。)例如,以下每个表创建语句都是无效的:
CREATE TABLE t1 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1, col2)
)
PARTITION BY HASH(col3)
PARTITIONS 4;
CREATE TABLE t2 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1),
UNIQUE KEY (col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;
在每种情况下,建议的表至少应该有一个不包括分区表达式中使用的所有列的唯一键。
每个以下语句都是有效的,并代表了相应的无效表创建语句可以正常工作的一种方式:
CREATE TABLE t1 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1, col2, col3)
)
PARTITION BY HASH(col3)
PARTITIONS 4;
CREATE TABLE t2 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1, col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;
这个例子展示了这种情况下产生的错误:
mysql> CREATE TABLE t3 (
-> col1 INT NOT NULL,
-> col2 DATE NOT NULL,
-> col3 INT NOT NULL,
-> col4 INT NOT NULL,
-> UNIQUE KEY (col1, col2),
-> UNIQUE KEY (col3)
-> )
-> PARTITION BY HASH(col1 + col3)
-> PARTITIONS 4;
ERROR 1491 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
CREATE TABLE
语句失败,因为col1
和col3
都包含在建议的分区键中,但这两列都不是表上的两个唯一键的一部分。这显示了无效表定义的一种可能修复方法:
mysql> CREATE TABLE t3 (
-> col1 INT NOT NULL,
-> col2 DATE NOT NULL,
-> col3 INT NOT NULL,
-> col4 INT NOT NULL,
-> UNIQUE KEY (col1, col2, col3),
-> UNIQUE KEY (col3)
-> )
-> PARTITION BY HASH(col3)
-> PARTITIONS 4;
Query OK, 0 rows affected (0.05 sec)
在这种情况下,建议的分区键col3
是两个唯一键的一部分,表创建语句成功。
以下表根本无法分区,因为无法在分区键中包含任何同时属于唯一键的列:
CREATE TABLE t4 (
col1 INT NOT NULL,
col2 INT NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1, col3),
UNIQUE KEY (col2, col4)
);
由于每个主键根据定义都是唯一键,如果表有主键,这个限制也包括表的主键。例如,下面的两个语句是无效的:
CREATE TABLE t5 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
PRIMARY KEY(col1, col2)
)
PARTITION BY HASH(col3)
PARTITIONS 4;
CREATE TABLE t6 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
PRIMARY KEY(col1, col3),
UNIQUE KEY(col2)
)
PARTITION BY HASH( YEAR(col2) )
PARTITIONS 4;
在这两种情况下,主键都不包括分区表达式中引用的所有列。然而,下面的两个语句都是有效的:
CREATE TABLE t7 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
PRIMARY KEY(col1, col2)
)
PARTITION BY HASH(col1 + YEAR(col2))
PARTITIONS 4;
CREATE TABLE t8 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
PRIMARY KEY(col1, col2, col4),
UNIQUE KEY(col2, col1)
)
PARTITION BY HASH(col1 + YEAR(col2))
PARTITIONS 4;
如果一个表没有唯一键——这包括没有主键——那么这个限制就不适用,你可以使用分区表达式中的任何列或多列,只要列的类型与分区类型兼容。
出于同样的原因,除非该键包含表的分区表达式中使用的所有列,否则您不能随后向分区表添加唯一键。考虑在此处创建的分区表:
mysql> CREATE TABLE t_no_pk (c1 INT, c2 INT)
-> PARTITION BY RANGE(c1) (
-> PARTITION p0 VALUES LESS THAN (10),
-> PARTITION p1 VALUES LESS THAN (20),
-> PARTITION p2 VALUES LESS THAN (30),
-> PARTITION p3 VALUES LESS THAN (40)
-> );
Query OK, 0 rows affected (0.12 sec)
可以使用以下任一ALTER TABLE
语句向t_no_pk
添加主键:
# possible PK
mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1);
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
# drop this PK
mysql> ALTER TABLE t_no_pk DROP PRIMARY KEY;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
# use another possible PK
mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1, c2);
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
# drop this PK
mysql> ALTER TABLE t_no_pk DROP PRIMARY KEY;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
然而,下一个语句失败,因为c1
是分区键的一部分,但不是建议的主键的一部分:
# fails with error 1503
mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c2);
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
由于t_no_pk
只有c1
在其分区表达式中,尝试仅在c2
上添加唯一键将失败。但是,您可以添加一个使用c1
和c2
的唯一键。
这些规则也适用于您希望使用ALTER TABLE ... PARTITION BY
对现有的非分区表进行分区的情况。考虑一个如下所示创建的表np_pk
:
mysql> CREATE TABLE np_pk (
-> id INT NOT NULL AUTO_INCREMENT,
-> name VARCHAR(50),
-> added DATE,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.08 sec)
以下ALTER TABLE
语句将因为added
列不是表中任何唯一键的一部分而失败:
mysql> ALTER TABLE np_pk
-> PARTITION BY HASH( TO_DAYS(added) )
-> PARTITIONS 4;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
然而,使用id
列作为分区列的语句是有效的,如下所示:
mysql> ALTER TABLE np_pk
-> PARTITION BY HASH(id)
-> PARTITIONS 4;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
对于np_pk
,唯一可以用作分区表达式的列是id
;如果您希望使用分区表达式中的任何其他列对该表进行分区,您必须首先修改表,可以通过将所需的列添加到主键中或完全删除主键。
26.6.2 关于存储引擎的分区限制
译文链接:
dev.mysql.com/doc/refman/8.0/en/partitioning-limitations-storage-engines.html
在 MySQL 8.0 中,分区支持实际上并非由 MySQL Server 提供,而是由表存储引擎自身或本地分区处理程序提供。在 MySQL 8.0 中,只有InnoDB
和NDB
存储引擎提供本地分区处理程序。这意味着分区表不能使用除这些之外的任何其他存储引擎来创建。(您必须使用带有NDB
存储引擎的 MySQL NDB Cluster 来创建NDB
表。)
InnoDB 存储引擎。 InnoDB
外键和 MySQL 分区不兼容。分区的InnoDB
表不能具有外键引用,也不能具有被外键引用的列。具有或被外键引用的InnoDB
表不能进行分区。
对于使用InnoDB
的分区表,ALTER TABLE ... OPTIMIZE PARTITION
无法正确工作。对于这些表,请改用ALTER TABLE ... REBUILD PARTITION
和ALTER TABLE ... ANALYZE PARTITION
。有关更多信息,请参见第 15.1.9.1 节,“ALTER TABLE Partition Operations”。
用户定义的分区和 NDB 存储引擎(NDB Cluster)。 KEY
(包括LINEAR KEY
)分区是唯一支持的NDB
存储引擎的分区类型。在 NDB Cluster 中,通常情况下不可能使用除[LINEAR
] KEY
之外的任何分区类型来创建 NDB Cluster 表,尝试这样做会导致错误。
异常(不适用于生产):可以通过在 NDB Cluster SQL 节点上设置new
系统变量为ON
来覆盖此限制。如果您选择这样做,您应该意识到不支持使用除[LINEAR] KEY
之外的其他分区类型的表进行生产。在这种情况下,您可以创建和使用除KEY
或LINEAR KEY
之外的其他分区类型的表,但这完全是自担风险的。您还应该意识到,此功能现已被弃用,并可能在将来的 NDB Cluster 版本中不经通知地被移除。
可以为NDB
表定义的最大分区数取决于集群中的数据节点和节点组数量、正在使用的 NDB Cluster 软件版本以及其他因素。有关更多信息,请参见 NDB 和用户定义的分区。
在NDB
表中每个分区中可以存储的固定大小数据的最大量为 128 TB。以前,这个值是 16 GB。
会导致用户分区的NDB
表不满足以下两个要求之一或两者的CREATE TABLE
和ALTER TABLE
语句不被允许,并且会因错误而失败:
-
表必须具有显式主键。
-
表的分区表达式中列出的所有列必须是主键的一部分。
异常。 如果使用空列列表(即使用PARTITION BY KEY()
或PARTITION BY LINEAR KEY()
)创建用户分区的NDB
表,则不需要显式主键。
分区选择。 NDB
表不支持分区选择。有关更多信息,请参见第 26.5 节“分区选择”。
升级分区表。 在执行升级时,使用KEY
进行分区的表必须进行转储和重新加载。使用除InnoDB
之外的存储引擎进行分区的表无法从 MySQL 5.7 或更早版本升级到 MySQL 8.0 或更高版本;您必须在升级之前使用ALTER TABLE ... REMOVE PARTITIONING
删除这些表的分区,或者使用ALTER TABLE ... ENGINE=INNODB
将其转换为InnoDB
。
有关将MyISAM
表转换为InnoDB
的信息,请参见第 17.6.1.5 节“从 MyISAM 转换表到 InnoDB”。
26.6.3 与函数相关的分区限制
原文:
dev.mysql.com/doc/refman/8.0/en/partitioning-limitations-functions.html
这一部分讨论了 MySQL 分区中与分区表达式中使用的函数相关的限制。
只有以下列出的 MySQL 函数允许在分区表达式中使用:
-
ABS()
-
CEILING()
(参见 CEILING() 和 FLOOR() and FLOOR()")) -
DATEDIFF()
-
DAY()
-
DAYOFMONTH()
-
DAYOFWEEK()
-
DAYOFYEAR()
-
EXTRACT()
(参见 带有 WEEK 参数的 EXTRACT() 函数 function with WEEK specifier")) -
FLOOR()
(参见 CEILING() 和 FLOOR() and FLOOR()")) -
HOUR()
-
MICROSECOND()
-
MINUTE()
-
MOD()
-
MONTH()
-
QUARTER()
-
SECOND()
-
TIME_TO_SEC()
-
TO_DAYS()
-
TO_SECONDS()
-
UNIX_TIMESTAMP()
(带有TIMESTAMP
列) -
WEEKDAY()
-
YEAR()
-
YEARWEEK()
在 MySQL 8.0 中,支持对 TO_DAYS()
、TO_SECONDS()
、YEAR()
和 UNIX_TIMESTAMP()
函数进行分区修剪。更多信息请参见 第 26.4 节,“分区修剪”。
CEILING()和 FLOOR()。 如果这些函数的参数是精确数值类型,如INT
类型或DECIMAL
之一,则每个函数仅返回整数。这意味着,例如,以下CREATE TABLE
语句将因错误而失败,如下所示:
mysql> CREATE TABLE t (c FLOAT) PARTITION BY LIST( FLOOR(c) )(
-> PARTITION p0 VALUES IN (1,3,5),
-> PARTITION p1 VALUES IN (2,4,6)
-> );
ERROR 1490 (HY000): The PARTITION function returns the wrong type
带有 WEEK 指定符的 EXTRACT()函数。 当作为EXTRACT(WEEK FROM *
col*)
使用时,EXTRACT()
函数返回的值取决于default_week_format
系统变量的值。因此,当指定单位为WEEK
时,EXTRACT()
不允许作为分区函数。(Bug #54483)
查看第 14.6.2 节,“数学函数”,了解这些函数的返回类型的更多信息,以及第 13.1 节,“数值数据类型”。
第二十七章 存储对象
目录
27.1 定义存储程序
27.2 使用存储过程
27.2.1 存储过程语法
27.2.2 存储过程和 MySQL 权限
27.2.3 存储过程元数据
27.2.4 存储过程、函数、触发器和 LAST_INSERT_ID()
27.3 使用触发器
27.3.1 触发器语法和示例
27.3.2 触发器元数据
27.4 使用事件调度程序
27.4.1 事件调度程序概述
27.4.2 事件调度程序配置
27.4.3 事件语法
27.4.4 事件元数据
27.4.5 事件调度程序状态
27.4.6 事件调度程序和 MySQL 权限
27.5 使用视图
27.5.1 视图语法
27.5.2 视图处理算法
27.5.3 可更新和可插入视图
27.5.4 视图 WITH CHECK OPTION 子句
27.5.5 视图元数据
27.6 存储对象访问控制
27.7 存储程序二进制日志记录
27.8 存储程序限制
27.9 视图限制
本章讨论了以 SQL 代码形式定义并存储在服务器上以供以后执行的存储数据库对象。
存储对象包括以下对象类型:
-
存储过程:使用
CREATE PROCEDURE
创建的对象,并使用CALL
语句调用。存储过程没有返回值,但可以修改其参数以供调用者稍后检查。它还可以生成结果集以返回给客户端程序。 -
存储函数:使用
CREATE FUNCTION
创建的对象,类似于内置函数。您可以在表达式中调用它,并在表达式评估期间返回一个值。 -
触发器:使用
CREATE TRIGGER
创建的与表关联的对象。当表发生特定事件时(如插入或更新),触发器被激活。 -
事件:使用
CREATE EVENT
创建的对象,并根据计划由服务器调用。 -
视图:使用
CREATE VIEW
创建的对象,当引用时产生一个结果集。视图充当虚拟表。
本文档中使用的术语反映了存储对象层次结构:
-
存储程序包括存储过程和函数。
-
存储程序包括存储过程、触发器和事件。
-
存储对象包括存储程序和视图。
本章描述了如何使用存储对象。以下各节提供了有关与这些对象相关的语句的 SQL 语法的附加信息,以及有关对象处理的信息:
-
对于每种对象类型,都有
CREATE
、ALTER
和DROP
语句来控制对象的存在和定义方式。参见第 15.1 节,“数据定义语句”。 -
CALL
语句用于调用存储过程。参见第 15.2.1 节,“CALL 语句”。 -
存储程序定义包括一个主体,可以使用复合语句、循环、条件语句和声明变量。参见第 15.6 节,“复合语句语法”。
-
存储程序引用的对象的元数据更改会被检测到,并在下次执行程序时导致受影响语句的自动重新解析。有关更多信息,请参见第 10.10.3 节,“准备语句和存储程序的缓存”。
27.1 定义存储程序
原文:
dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html
每个存储程序包含一个由 SQL 语句组成的主体。该语句可以是由分号(;
)字符分隔的多个语句组成的复合语句。例如,以下存储过程具有由BEGIN ... END
块组成的主体,其中包含一个SET
语句和一个包含另一个SET
语句的REPEAT
循环:
CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
SET @x = 0;
REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END;
如果您使用mysql客户端程序定义包含分号字符的存储程序,则会出现问题。默认情况下,mysql本身将分号识别为语句分隔符,因此您必须临时重新定义分隔符,以使mysql将整个存储程序定义传递给服务器。
要重新定义mysql的分隔符,请使用delimiter
命令。以下示例展示了如何为刚刚显示的dorepeat()
过程执行此操作。将分隔符更改为//
以便将整个定义作为单个语句传递给服务器,然后在调用过程之前将其恢复为;
。这样可以使过程体中使用的;
分隔符传递到服务器,而不是被mysql本身解释。
mysql> delimiter //
mysql> CREATE PROCEDURE dorepeat(p1 INT)
-> BEGIN
-> SET @x = 0;
-> REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> CALL dorepeat(1000);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x;
+------+
| @x |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)
您可以将分隔符重新定义为除//
之外的其他字符串,并且分隔符可以由单个字符或多个字符组成。应避免使用反斜杠(\
)字符,因为这是 MySQL 的转义字符。
以下是一个接受参数、使用 SQL 函数执行操作并返回结果的函数示例。在这种情况下,不需要使用delimiter
,因为函数定义不包含内部的;
语句分隔符:
mysql> CREATE FUNCTION hello (s CHAR(20))
mysql> RETURNS CHAR(50) DETERMINISTIC
-> RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world! |
+----------------+
1 row in set (0.00 sec)
27.2 使用存储过程
27.2.1 存储过程语法
27.2.2 存储过程和 MySQL 权限
27.2.3 存储过程元数据
27.2.4 存储过程、函数、触发器和 LAST_INSERT_ID()
MySQL 支持存储过程(过程和函数)。存储过程是一组可以存储在服务器中的 SQL 语句。一旦完成这个过程,客户端就不需要不断重新发出各个语句,而是可以引用存储过程。
在某些情况下,存储过程可能特别有用:
-
当多个客户端应用程序使用不同语言编写或在不同平台上运行,但需要执行相同的数据库操作时。
-
当安全性至关重要时。例如,银行使用存储过程和函数进行所有常见操作。这提供了一个一致且安全的环境,而例程可以确保每个操作都被正确记录。在这样的设置中,应用程序和用户无法直接访问数据库表,而只能执行特定的存储过程。
存储过程可以提供更好的性能,因为服务器和客户端之间需要传输的信息更少。这样做的代价是这会增加数据库服务器的负载,因为更多的工作是在服务器端完成的,而在客户端(应用程序)端完成的工作较少。如果许多客户机(如 Web 服务器)由一个或几个数据库服务器提供服务,请考虑这一点。
存储过程还使您能够在数据库服务器中拥有函数库。这是现代应用程序语言共享的功能,这些语言内部允许这样的设计(例如,通过使用类)。即使在数据库使用范围之外,利用这些客户端应用程序语言功能对程序员也是有益的。
MySQL 遵循 SQL:2003 的存储过程语法,这也是 IBM 的 DB2 所使用的。这里描述的所有语法都受支持,任何限制和扩展都在适当的地方有文档记录。
其他资源
-
在处理存储过程和函数时,您可能会发现存储过程用户论坛很有用。
-
有关 MySQL 中存储过程的一些常见问题的答案,请参阅附录 A.4,“MySQL 8.0 FAQ:存储过程和函数”。
-
对于存储过程的使用有一些限制。请参阅第 27.8 节,“存储程序的限制”。
-
存储过程的二进制日志记录过程如第 27.7 节,“存储程序二进制日志记录”中所述。
27.2.1 存储过程语法
原文:
dev.mysql.com/doc/refman/8.0/en/stored-routines-syntax.html
存储过程可以是过程或函数。存储过程是使用CREATE PROCEDURE
和CREATE FUNCTION
语句创建的(参见 Section 15.1.17, “CREATE PROCEDURE and CREATE FUNCTION Statements”)。通过CALL
语句调用过程(参见 Section 15.2.1, “CALL Statement”),只能使用输出变量传回值。函数可以像其他函数一样从语句内部调用(即通过调用函数的名称),并且可以返回一个标量值。存储过程的主体可以使用复合语句(参见 Section 15.6, “Compound Statement Syntax”)。
存储过程可以使用DROP PROCEDURE
和DROP FUNCTION
语句删除(参见 Section 15.1.29, “DROP PROCEDURE and DROP FUNCTION Statements”),并且可以使用ALTER PROCEDURE
和ALTER FUNCTION
语句进行修改(参见 Section 15.1.7, “ALTER PROCEDURE Statement”)。
存储过程或函数与特定数据库相关联。这有几个含义:
-
当调用例程时,会执行隐式的
USE *
db_name*
(在例程终止时撤消)。存储过程内部不允许使用USE
语句。 -
您可以使用数据库名称限定例程名称。这可用于引用不在当前数据库中的例程。例如,要调用与
test
数据库关联的存储过程p
或函数f
,可以使用CALL test.p()
或test.f()
。 -
当一个数据库被删除时,与之相关的所有存储过程也会被删除。
存储函数不能是递归的。
存储过程中允许递归,但默认情况下被禁用。要启用递归,请将max_sp_recursion_depth
服务器系统变量设置为大于零的值。存储过程递归会增加线程堆栈空间的需求。如果增加max_sp_recursion_depth
的值,可能需要通过增加服务器启动时thread_stack
的值来增加线程堆栈大小。有关更多信息,请参见第 7.1.8 节,“服务器系统变量”。
MySQL 支持一个非常有用的扩展,允许在存储过程中使用常规的SELECT
语句(即,不使用游标或本地变量)。这种查询的结果集直接发送到客户端。多个SELECT
语句会生成多个结果集,因此客户端必须使用支持多个结果集的 MySQL 客户端库。这意味着客户端必须使用至少与 MySQL 4.1 版本一样新的客户端库。客户端在连接时还应指定CLIENT_MULTI_RESULTS
选项。对于 C 程序,可以使用mysql_real_connect()
C API 函数来实现。请参见 mysql_real_connect(),以及多语句执行支持。
在 MySQL 8.0.22 及更高版本中,存储过程中的语句引用的用户变量在存储过程首次调用时确定其类型,并在此后的每次调用存储过程时保留此类型。
27.2.2 存储例程和 MySQL 权限
原文:
dev.mysql.com/doc/refman/8.0/en/stored-routines-privileges.html
MySQL 授权系统如下考虑存储例程:
-
需要
CREATE ROUTINE
权限来创建存储例程。 -
需要
ALTER ROUTINE
权限来修改或删除存储例程。如果需要,此权限将自动授予例程的创建者,并在删除例程时从创建者那里删除。 -
执行存储例程需要
EXECUTE
权限。但是,如果需要,此权限将自动授予例程的创建者(在删除例程时从创建者那里删除)。此外,例程的默认SQL SECURITY
特性为DEFINER
,这使得具有与例程关联的数据库访问权限的用户可以执行该例程。 -
如果
automatic_sp_privileges
系统变量为 0,则不会自动授予和删除例程创建者的EXECUTE
和ALTER ROUTINE
权限。 -
例程的创建者是用于执行其
CREATE
语句的帐户。这可能与例程定义中命名为DEFINER
的帐户不同。 -
例程
DEFINER
命名的帐户可以查看所有例程属性,包括其定义。因此,该帐户完全可以访问由以下产生的例程输出:-
信息模式
ROUTINES
表的内容。 -
SHOW CREATE FUNCTION
和SHOW CREATE PROCEDURE
语句。 -
SHOW FUNCTION CODE
和SHOW PROCEDURE CODE
语句。 -
SHOW FUNCTION STATUS
和SHOW PROCEDURE STATUS
语句。
-
-
对于非例程
DEFINER
命名的帐户,访问例程属性取决于授予帐户的权限:-
使用
SHOW_ROUTINE
权限或全局SELECT
权限,帐户可以查看所有例程属性,包括其定义。 -
通过在包含例程的范围内授予
CREATE ROUTINE
、ALTER ROUTINE
或EXECUTE
权限,帐户可以查看所有例程属性,除了其定义。
-
27.2.3 存储过程元数据
原文:
dev.mysql.com/doc/refman/8.0/en/stored-routines-metadata.html
获取存储过程的元数据:
-
查询
INFORMATION_SCHEMA
数据库的ROUTINES
表。参见 Section 28.3.30, “The INFORMATION_SCHEMA ROUTINES Table”。 -
使用
SHOW CREATE PROCEDURE
和SHOW CREATE FUNCTION
语句查看过程定义。参见 Section 15.7.7.9, “SHOW CREATE PROCEDURE Statement”。 -
使用
SHOW PROCEDURE STATUS
和SHOW FUNCTION STATUS
语句查看过程特征。参见 Section 15.7.7.28, “SHOW PROCEDURE STATUS Statement”。 -
使用
SHOW PROCEDURE CODE
和SHOW FUNCTION CODE
语句查看过程的内部实现表示。参见 Section 15.7.7.27, “SHOW PROCEDURE CODE Statement”。
27.2.4 存储过程、函数、触发器和 LAST_INSERT_ID()
原文:
dev.mysql.com/doc/refman/8.0/en/stored-routines-last-insert-id.html
在存储过程(procedure 或 function)或触发器的主体内,LAST_INSERT_ID()
的值会像在这些对象的主体外执行语句时一样发生变化(参见第 14.15 节,“信息函数”)。存储过程或触发器对LAST_INSERT_ID()
值的影响取决于存储过程的类型:
-
如果存储过程执行更改
LAST_INSERT_ID()
值的语句,则后续语句会看到更改后的值。 -
对于改变值的存储函数和触发器,在函数或触发器结束时,值会恢复,因此后续语句不会看到更改后的值。
27.3 使用触发器
27.3.1 触发器语法和示例
27.3.2 触发器元数据
触发器是与表关联的命名数据库对象,当表发生特定事件时激活。触发器的一些用途包括对要插入表中的值执行检查,或对参与更新的值执行计算。
当语句在关联表中插入、更新或删除行时,触发器被定义为激活。这些行操作是触发器事件。例如,行可以通过 INSERT
或 LOAD DATA
语句插入,每插入一行触发器就会激活一次。触发器可以设置为在触发事件之前或之后激活。例如,您可以在将每行插入到表中之前或之后激活触发器。
重要提示
MySQL 触发器仅在通过 SQL 语句对表进行更改时激活。这包括对底层可更新视图的基本表进行的更改。触发器不会在通过不向 MySQL 服务器传输 SQL 语句的 API 对表进行的更改时激活。这意味着触发器不会被使用 NDB
API 进行的更新所激活。
触发器不会因 INFORMATION_SCHEMA
或 performance_schema
表的更改而激活。这些表实际上是视图,视图上不允许触发器。
以下各节描述了创建和删除触发器的语法,展示了如何使用它们的一些示例,并指示如何获取触发器元数据。
其他资源
-
在处理触发器时,您可能会发现 MySQL 用户论坛 有所帮助。
-
有关 MySQL 中触发器常见问题的答案,请参阅 第 A.5 节 “MySQL 8.0 FAQ: 触发器”。
-
对触发器的使用有一些限制;请参阅 第 27.8 节 “存储程序限制”。
-
触发器的二进制日志记录如 第 27.7 节 “存储程序二进制日志记录” 中所述。
27.3.1 触发器语法和示例
要创建触发器或删除触发器,请使用CREATE TRIGGER
或DROP TRIGGER
语句,描述在第 15.1.22 节,“CREATE TRIGGER Statement”和第 15.1.34 节,“DROP TRIGGER Statement”中。
这里有一个简单的示例,将触发器与表关联起来,以激活INSERT
操作。 触发器充当累加器,对表的某一列插入的值进行求和。
mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.01 sec)
CREATE TRIGGER
语句创建一个名为ins_sum
的触发器,与account
表关联。 它还包括指定触发器动作时间、触发事件以及触发器激活时要执行的操作的子句:
-
关键字
BEFORE
表示触发器动作时间。 在这种情况下,触发器在每行插入到表之前激活。 这里允许的其他关键字是AFTER
。 -
关键字
INSERT
表示触发事件;也就是说,激活触发器的操作类型。 在示例中,INSERT
操作会导致触发器激活。 您还可以为DELETE
和UPDATE
操作创建触发器。 -
FOR EACH ROW
后面的语句定义了触发器体;也就是说,每次触发器激活时执行的语句,这发生在触发事件影响的每一行。 在示例中,触发器体是一个简单的SET
,将插入到amount
列中的值累积到用户变量中。 该语句将列称为NEW.amount
,意思是“要插入新行的amount
列的值”。
要使用触发器,请将累加器变量设置为零,执行一个INSERT
语句,然后查看变量之后的值:
mysql> SET @sum = 0;
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
mysql> SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
| 1852.48 |
+-----------------------+
在这种情况下,INSERT
语句执行后@sum
的值为14.98 + 1937.50 - 100
,即1852.48
。
要销毁触发器,请使用DROP TRIGGER
语句。 如果触发器不在默认模式中,则必须指定模式名称:
mysql> DROP TRIGGER test.ins_sum;
如果删除表,则表的任何触发器也将被删除。
触发器名称存在于模式命名空间中,这意味着所有触发器在模式内必须具有唯一名称。 不同模式中的触发器可以具有相同的名称。
可以为给定表定义多个具有相同触发事件和操作时间的触发器。例如,您可以为表定义两个BEFORE UPDATE
触发器。默认情况下,具有相同触发事件和操作时间的触发器按照它们创建的顺序激活。要影响触发器顺序,请在FOR EACH ROW
之后指定一个子句,指示FOLLOWS
或PRECEDES
以及一个具有相同触发事件和操作时间的现有触发器的名称。使用FOLLOWS
,新触发器在现有触发器之后激活。使用PRECEDES
,新触发器在现有触发器之前激活。
例如,以下触发器定义为account
表定义了另一个BEFORE INSERT
触发器:
mysql> CREATE TRIGGER ins_transaction BEFORE INSERT ON account
FOR EACH ROW PRECEDES ins_sum
SET
@deposits = @deposits + IF(NEW.amount>0,NEW.amount,0),
@withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0);
Query OK, 0 rows affected (0.01 sec)
此触发器ins_transaction
类似于ins_sum
,但分别累积存款和取款。它具有一个PRECEDES
子句,使其在ins_sum
之前激活;如果没有该子句,它将在ins_sum
之后激活,因为它是在ins_sum
之后创建的。
在触发器主体内,OLD
和NEW
关键字使您能够访问触发器影响的行中的列。OLD
和NEW
是 MySQL 触发器的扩展;它们不区分大小写。
在INSERT
触发器中,只能使用NEW.*
col_name*
;没有旧行。在DELETE
触发器中,只能使用OLD.*
col_name*
;没有新行。在UPDATE
触发器中,您可以使用OLD.*
col_name*
来引用更新前行的列,使用NEW.*
col_name*
来引用更新后行的列。
以OLD
命名的列是只读的。您可以引用它(如果您具有SELECT
权限),但不能修改它。如果您对其具有SELECT
权限,则可以引用以NEW
命名的列。在BEFORE
触发器中,如果您对其具有UPDATE
权限,则还可以使用SET NEW.*
col_name* = *
value*
来更改其值。这意味着您可以使用触发器修改要插入新行或用于更新行的值。(在AFTER
触发器中,这样的SET
语句没有效果,因为行更改已经发生。)
在BEFORE
触发器中,AUTO_INCREMENT
列的NEW
值为 0,而不是在实际插入新行时自动生成的序列号。
通过使用BEGIN ... END
结构,可以定义执行多个语句的触发器。在BEGIN
块内,还可以使用其他在存储过程中允许的语法,如条件和循环。然而,就像对于存储过程一样,如果使用mysql程序定义执行多个语句的触发器,则需要重新定义mysql语句定界符,以便在触发器定义内使用;
语句定界符。以下示例说明了这些要点。它定义了一个UPDATE
触发器,检查要用于更新每行的新值,并修改值使其在 0 到 100 的范围内。这必须是BEFORE
触发器,因为必须在使用该值更新行之前检查该值:
mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
FOR EACH ROW
BEGIN
IF NEW.amount < 0 THEN
SET NEW.amount = 0;
ELSEIF NEW.amount > 100 THEN
SET NEW.amount = 100;
END IF;
END;//
mysql> delimiter ;
将存储过程单独定义,然后使用简单的CALL
语句从触发器中调用它可能更容易。如果要从多个触发器内执行相同的代码,这也是有利的。
触发器执行时,触发器执行的语句中出现的内容有限制:
-
触发器不能使用
CALL
语句调用返回数据给客户端或使用动态 SQL 的存储过程。(存储过程可以通过OUT
或INOUT
参数将数据返回给触发器。) -
触发器不能使用明确或隐式开始或结束事务的语句,例如
START TRANSACTION
,COMMIT
或ROLLBACK
。(ROLLBACK to SAVEPOINT
是允许的,因为它不会结束事务。)。
另请参阅 Section 27.8, “存储程序的限制”。
MySQL 在触发器执行期间处理错误如下:
-
如果
BEFORE
触发器失败,则不会执行对应行的操作。 -
BEFORE
触发器在尝试插入或修改行时被激活,无论尝试是否成功。 -
仅当任何
BEFORE
触发器和行操作成功执行时,才会执行AFTER
触发器。 -
BEFORE
或AFTER
触发器中的错误导致触发器调用的整个语句失败。 -
对于事务性表,语句失败应导致语句执行的所有更改回滚。触发器失败会导致语句失败,因此触发器失败也会导致回滚。对于非事务性表,无法进行此类回滚,因此尽管语句失败,但在错误点之前执行的任何更改仍然有效。
触发器可以通过名称直接引用表,例如此示例中显示的名为testref
的触发器:
CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0
);
delimiter |
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW
BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END;
|
delimiter ;
INSERT INTO test3 (a3) VALUES
(NULL), (NULL), (NULL), (NULL), (NULL),
(NULL), (NULL), (NULL), (NULL), (NULL);
INSERT INTO test4 (a4) VALUES
(0), (0), (0), (0), (0), (0), (0), (0), (0), (0);
假设您将以下数值插入到表test1
中,如下所示:
mysql> INSERT INTO test1 VALUES
(1), (3), (1), (7), (1), (8), (4), (4);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
结果,四个表包含以下数据:
mysql> SELECT * FROM test1;
+------+
| a1 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM test2;
+------+
| a2 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM test3;
+----+
| a3 |
+----+
| 2 |
| 5 |
| 6 |
| 9 |
| 10 |
+----+
5 rows in set (0.00 sec)
mysql> SELECT * FROM test4;
+----+------+
| a4 | b4 |
+----+------+
| 1 | 3 |
| 2 | 0 |
| 3 | 1 |
| 4 | 2 |
| 5 | 0 |
| 6 | 0 |
| 7 | 1 |
| 8 | 1 |
| 9 | 0 |
| 10 | 0 |
+----+------+
10 rows in set (0.00 sec)
27.3.2 触发器元数据
要获取有关触发器的元数据:
-
查询
INFORMATION_SCHEMA
数据库的TRIGGERS
表。参见 Section 28.3.45, “The INFORMATION_SCHEMA TRIGGERS Table”。 -
使用
SHOW CREATE TRIGGER
语句。参见 Section 15.7.7.11, “SHOW CREATE TRIGGER Statement”。 -
使用
SHOW TRIGGERS
语句。参见 Section 15.7.7.40, “SHOW TRIGGERS Statement”。
27.4 使用事件调度器
27.4.1 事件调度器概述
27.4.2 事件调度器配置
27.4.3 事件语法
27.4.4 事件元数据
27.4.5 事件调度器状态
27.4.6 事件调度器和 MySQL 权限
MySQL 事件调度器管理事件的调度和执行,即按照时间表运行的任务。以下讨论涵盖了事件调度器,并分为以下几个部分:
-
第 27.4.1 节,“事件调度器概述”,介绍了 MySQL 事件的简介和概念概述。
-
第 27.4.3 节,“事件语法”,讨论了用于创建、修改和删除 MySQL 事件的 SQL 语句。
-
第 27.4.4 节,“事件元数据”,展示了如何获取有关事件的信息以及 MySQL 服务器如何存储这些信息。
-
第 27.4.6 节,“事件调度器和 MySQL 权限”,讨论了处理事件所需的权限以及在执行时事件对权限的影响。
存储过程需要mysql
系统数据库中的events
数据字典表。此表在 MySQL 8.0 安装过程中创建。如果您从早期版本升级到 MySQL 8.0,请确保执行升级过程,以确保您的系统数据库是最新的。请参阅第三章,升级 MySQL。
其他资源
-
对事件的使用有一些限制;请参阅第 27.8 节,“存储程序的限制”。
-
事件的二进制日志记录如第 27.7 节,“存储程序的二进制日志记录”所述进行。
-
您可能还会发现MySQL 用户论坛很有帮助。
27.4.1 事件调度程序概述
MySQL 事件是按照时间表运行的任务。因此,我们有时将它们称为定时事件。当您创建一个事件时,实际上是创建了一个命名的数据库对象,其中包含一个或多个要在一个或多个常规间隔执行的 SQL 语句,开始和结束于特定日期和时间。从概念上讲,这类似于 Unix 的crontab
(也称为“cron job”)或 Windows 任务计划程序的概念。
这种类型的定时任务有时也被称为“时间触发器”,暗示这些是由时间流逝触发的对象。虽然这基本上是正确的,但我们更倾向于使用术语事件,以避免与第 27.3 节“使用触发器”中讨论的触发器混淆。事件更具体地不应与“临时触发器”混淆。触发器是一个数据库对象,其语句在发生在给定表上的特定类型事件时执行,而(定时)事件是一个对象,其语句在经过指定的时间间隔后执行。
虽然 SQL 标准中没有关于事件调度的规定,但其他数据库系统中有先例,您可能会注意到这些实现与 MySQL 服务器中的实现之间的一些相似之处。
MySQL 事件具有以下主要特性和属性:
-
在 MySQL 中,事件通过其名称和分配给它的模式唯一标识。
-
事件根据时间表执行特定操作。此操作包括一个 SQL 语句,如果需要,可以是一个
BEGIN ... END
块中的复合语句(请参阅第 15.6 节“复合语句语法”)。事件的时间可以是一次性的或循环的。一次性事件仅执行一次。循环事件在常规间隔重复其操作,并且循环事件的时间表可以分配一个特定的开始日期和时间,结束日期和时间,两者都有,或两者都没有。(默认情况下,循环事件的时间表在创建时立即开始,并持续无限期,直到被禁用或删除。)如果重复事件在其调度间隔内没有终止,则可能会导致多个事件实例同时执行。如果这是不希望的,您应该实施一种机制来防止同时实例。例如,您可以使用
GET_LOCK()
函数,或行或表锁定。 -
用户可以使用专为此目的设计的 SQL 语句创建、修改和删除计划事件。语法无效的事件创建和修改语句将失败,并显示适当的错误消息。用户可能在事件的操作中包含需要用户实际上没有的权限的语句。事件创建或修改语句成功,但事件的操作失败。有关详细信息,请参见 第 27.4.6 节,“事件调度程序和 MySQL 权限”。
-
许多事件的属性可以使用 SQL 语句进行设置或修改。这些属性包括事件的名称、定时、持久性(即在其计划过期后是否保留)、状态(启用或禁用)、要执行的操作以及分配给它的模式。参见 第 15.1.3 节,“ALTER EVENT 语句”。
事件的默认定义者是创建事件的用户,除非事件已被修改,在这种情况下,定义者是发出影响该事件的最后一个
ALTER EVENT
语句的用户。任何具有对定义事件的数据库上的EVENT
权限的用户都可以修改事件。有关详细信息,请参见 第 27.4.6 节,“事件调度程序和 MySQL 权限”。 -
事件的操作语句可以包含大多数存储过程中允许的 SQL 语句。有关限制,请参见 第 27.8 节,“存储程序的限制”。
27.4.2 事件调度器配置
原文:
dev.mysql.com/doc/refman/8.0/en/events-configuration.html
事件由一个特殊的事件调度器线程执行;当我们提到事件调度器时,实际上是指这个线程。当运行时,具有PROCESS
权限的用户可以在SHOW PROCESSLIST
的输出中看到事件调度器线程及其当前状态,如下面的讨论所示。
全局event_scheduler
系统变量确定事件调度器在服务器上是否启用和运行。它具有以下值之一,这些值会影响事件调度的描述:
-
ON
: 事件调度器已启动;事件调度器线程运行并执行所有预定事件。ON
是默认的event_scheduler
值。当事件调度器为
ON
时,事件调度器线程将作为守护进程在SHOW PROCESSLIST
的输出中列出,并且其状态如下所示:mysql> SHOW PROCESSLIST\G *************************** 1\. row *************************** Id: 1 User: root Host: localhost db: NULL Command: Query Time: 0 State: NULL Info: show processlist *************************** 2\. row *************************** Id: 2 User: event_scheduler Host: localhost db: NULL Command: Daemon Time: 3 State: Waiting for next activation Info: NULL 2 rows in set (0.00 sec)
通过将
event_scheduler
的值设置为OFF
来停止事件调度。 -
OFF
: 事件调度器已停止。事件调度器线程不运行,不会显示在SHOW PROCESSLIST
的输出中,也不会执行任何预定事件。当事件调度器停止时(
event_scheduler
为OFF
),可以通过将event_scheduler
的值设置为ON
来启动它。(见下一项。) -
DISABLED
: 此值使事件调度器无法运行。当事件调度器为DISABLED
时,事件调度器线程不运行(因此不会出现在SHOW PROCESSLIST
的输出中)。此外,事件调度器状���无法在运行时更改。
如果事件调度器状态未设置为DISABLED
,则可以在event_scheduler
的值之间切换为ON
和OFF
(使用SET
)。在设置此变量时,也可以使用0
表示OFF
,使用1
表示ON
。因此,在mysql客户端中可以使用以下任何一条语句来启动事件调度器:
SET GLOBAL event_scheduler = ON;
SET @@GLOBAL.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@GLOBAL.event_scheduler = 1;
类似地,可以使用以下任何一条语句关闭事件调度器:
SET GLOBAL event_scheduler = OFF;
SET @@GLOBAL.event_scheduler = OFF;
SET GLOBAL event_scheduler = 0;
SET @@GLOBAL.event_scheduler = 0;
注意
如果事件调度器已启用,则启用super_read_only
系统变量会阻止其在events
数据字典表中更新事件“上次执行”时间戳。这会导致事件调度器在下次尝试执行计划事件时停止,并在写入服务器错误日志后发出消息。(在这种情况下,event_scheduler
系统变量不会从ON
更改为OFF
。一个含义是,此变量拒绝了 DBA 意图,即事件调度器启用或禁用,其实际状态为启动或停止可能是不同的。)如果在启用后随后禁用super_read_only
,服务器会根据需要自动重新启动事件调度器,截至 MySQL 8.0.26。在 MySQL 8.0.26 之前,需要手动重新启动事件调度器以再次启用它。
尽管ON
和OFF
有数值等价物,但由SELECT
或SHOW VARIABLES
显示的event_scheduler
的值始终为OFF
、ON
或DISABLED
。DISABLED
没有数值等价物。因此,在设置此变量时,通常优先选择ON
和OFF
而不是1
和0
。
请注意,尝试设置event_scheduler
而不将其指定为全局变量会导致错误:
mysql< SET @@event_scheduler = OFF;
ERROR 1229 (HY000): Variable 'event_scheduler' is a GLOBAL
variable and should be set with SET GLOBAL
重要
只能在服务器启动时将事件调度器设置为DISABLED
。如果event_scheduler
为ON
或OFF
,则无法在运行时将其设置为DISABLED
。此外,如果事件调度器在启动时设置为DISABLED
,则无法在运行时更改event_scheduler
的值。
要禁用事件调度器,请使用以下两种方法之一:
-
作为启动服务器时的命令行选项:
--event-scheduler=DISABLED
-
在服务器配置文件(
my.cnf
,或 Windows 系统上的my.ini
)中,包含可以被服务器读取的行(例如,在[mysqld]
部分):event_scheduler=DISABLED
要启用事件调度器,请在重新启动服务器时不使用--event-scheduler=DISABLED
命令行选项,或在服务器配置文件中删除或注释包含event-scheduler=DISABLED
的行,或者在适当的情况下。另外,当启动服务器时,您可以使用ON
(或1
)或OFF
(或0
)来代替DISABLED
值。
注意
当event_scheduler
设置为DISABLED
时,可以发出事件操作语句。在这种情况下不会生成警告或错误(前提是语句本身有效)。但是,在将此变量设置为ON
(或1
)之前,计划事件无法执行。一旦完成此操作,事件调度程序线程将执行所有满足调度条件的事件。
使用--skip-grant-tables
选项启动 MySQL 服务器会将event_scheduler
设置为DISABLED
,覆盖在命令行或my.cnf
或my.ini
文件中设置的任何其他值(Bug #26807)。
有关用于创建、修改和删除事件的 SQL 语句,请参见第 27.4.3 节,“事件语法”。
MySQL 在INFORMATION_SCHEMA
数据库中提供了一个EVENTS
表。可以查询此表以获取关于服务器上已定义的计划事件的信息。更多信息请参见第 27.4.4 节,“事件元数据”和第 28.3.14 节,“INFORMATION_SCHEMA EVENTS 表”。
有关事件调度和 MySQL 权限系统的信息,请参见第 27.4.6 节,“事件调度程序和 MySQL 权限”。
27.4.3 事件语法
MySQL 提供了几个用于处理计划事件的 SQL 语句:
-
使用
CREATE EVENT
语句定义新事件。参见 Section 15.1.13, “CREATE EVENT Statement”。 -
通过
ALTER EVENT
语句可以更改现有事件的定义。参见 Section 15.1.3, “ALTER EVENT Statement”。 -
当不再需要或不再需要计划事件时,可以由其定义者使用
DROP EVENT
语句从服务器中删除。参见 Section 15.1.25, “DROP EVENT Statement”。事件是否在其计划结束后继续存在还取决于其ON COMPLETION
子句,如果有的话。参见 Section 15.1.13, “CREATE EVENT Statement”。任何具有数据库上定义事件的
EVENT
权限的用户都可以删除事件。参见 Section 27.4.6, “The Event Scheduler and MySQL Privileges”。
27.4.4 事件元数据
要获取有关事件的元数据:
-
查询
INFORMATION_SCHEMA
数据库的EVENTS
表。参见 Section 28.3.14, “The INFORMATION_SCHEMA EVENTS Table”。 -
使用
SHOW CREATE EVENT
语句。参见 Section 15.7.7.7, “SHOW CREATE EVENT Statement”。 -
使用
SHOW EVENTS
语句。参见 Section 15.7.7.18, “SHOW EVENTS Statement”。
事件调度器时间表示
MySQL 中的每个会话都有一个会话时区(STZ)。这是会话time_zone
值,当会话开始时从服务器的全局time_zone
值初始化,但在会话期间可能会更改。
当CREATE EVENT
或ALTER EVENT
语句执行时,使用当前会话时区来解释事件定义中指定的时间。这成为事件时区(ETZ);也就是用于事件调度并在事件执行时生效的时区。
为了在数据字典中表示事件信息,execute_at
、starts
和ends
时间被转换为 UTC 并与事件时区一起存储。这使得事件执行可以按照定义进行,而不受服务器时区或夏令时效果的影响。last_executed
时间也以 UTC 存储。
事件时间可以通过从信息模式EVENTS
表或SHOW EVENTS
中选择来获取,但它们以 ETZ 或 STZ 值报告。以下表总结了事件时间的表示。
值 | EVENTS 表 |
SHOW EVENTS |
---|---|---|
执行时间 | ETZ | ETZ |
开始时间 | ETZ | ETZ |
结束时间 | ETZ | ETZ |
上次执行时间 | ETZ | n/a |
创建时间 | STZ | n/a |
上次修改时间 | STZ | n/a |
27.4.5 事件调度程序状态
事件调度程序会将执行过程中出现错误或警告的事件信息写入 MySQL 服务器的错误日志。参见第 27.4.6 节,“事件调度程序和 MySQL 权限”中的示例。
为了获取有关事件调度程序状态的信息,以进行调试和故障排除,运行mysqladmin debug(参见第 6.5.2 节,“mysqladmin — 一个 MySQL 服务器管理程序”);运行此命令后,服务器的错误日志将包含与事件调度程序相关的输出,类似于这里显示的内容:
Events status:
LLA = Last Locked At LUA = Last Unlocked At
WOC = Waiting On Condition DL = Data Locked
Event scheduler status:
State : INITIALIZED
Thread id : 0
LLA : n/a:0
LUA : n/a:0
WOC : NO
Workers : 0
Executed : 0
Data locked: NO
Event queue status:
Element count : 0
Data locked : NO
Attempting lock : NO
LLA : init_queue:95
LUA : init_queue:103
WOC : NO
Next activation : never
在事件调度程序执行的语句中,诊断消息(不仅限于错误,还包括警告)会被写入错误日志,并且在 Windows 上也会写入应用程序事件日志。对于频繁执行的事件,可能会导致许多日志消息。例如,对于SELECT ... INTO *
var_list*
语句,如果查询没有返回任何行,会出现带有错误代码 1329 的警告(没有数据
),并且变量值保持不变。如果查询返回多行,会出现错误 1172(结果包含多于一行
)。对于任一条件,您可以通过声明条件处理程序来避免警告被记录;参见第 15.6.7.2 节,“DECLARE ... HANDLER 语句”。对于可能检索多行的语句,另一种策略是使用LIMIT 1
将结果集限制为单行。
27.4.6 事件调度程序和 MySQL 权限
要启用或禁用计划事件的执行,需要设置全局event_scheduler
系统变量的值。这需要足够的权限来设置全局系统变量。参见第 7.1.9.1 节,“系统变量权限”。
EVENT
权限管理事件的创建、修改和删除。可以使用GRANT
授予此权限。例如,此GRANT
语句将在用户jon@ghidora
上为名为myschema
的模式授予EVENT
权限:
GRANT EVENT ON myschema.* TO jon@ghidora;
(我们假设该用户帐户已经存在,并且我们希望其保持不变。)
要授予同一用户在所有模式上的EVENT
权限,请使用以下语句:
GRANT EVENT ON *.* TO jon@ghidora;
EVENT
权限具有全局或模式级别范围。因此,尝试在单个表上授予它会导致错误,如下所示:
mysql> GRANT EVENT ON myschema.mytable TO jon@ghidora;
ERROR 1144 (42000): Illegal GRANT/REVOKE command; please
consult the manual to see which privileges can be used
重要的是要理解事件是以其定义者的权限执行的,并且它不能执行其定义者没有所需权限的任何操作。例如,假设jon@ghidora
对myschema
具有EVENT
权限。还假设该用户对myschema
具有SELECT
权限,但对该模式没有其他权限。jon@ghidora
可以创建一个新事件,如下所示:
CREATE EVENT e_store_ts
ON SCHEDULE
EVERY 10 SECOND
DO
INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP());
用户等待一分钟左右,然后执行SELECT * FROM mytable;
查询,期望在表中看到几行新数据。然而,表是空的。由于用户没有表的INSERT
权限,因此事件没有效果。
如果检查 MySQL 错误日志(*
hostname*.err
),您会看到事件正在执行,但它尝试执行的操作失败:
2013-09-24T12:41:31.261992Z 25 [ERROR] Event Scheduler:
[jon@ghidora][cookbook.e_store_ts] INSERT command denied to user
'jon'@'ghidora' for table 'mytable'
2013-09-24T12:41:31.262022Z 25 [Note] Event Scheduler:
[jon@ghidora].[myschema.e_store_ts] event execution failed.
2013-09-24T12:41:41.271796Z 26 [ERROR] Event Scheduler:
[jon@ghidora][cookbook.e_store_ts] INSERT command denied to user
'jon'@'ghidora' for table 'mytable'
2013-09-24T12:41:41.272761Z 26 [Note] Event Scheduler:
[jon@ghidora].[myschema.e_store_ts] event execution failed.
由于这个用户很可能无法访问错误日志,可以通过直接执行事件的操作语句来验证该事件是否有效:
mysql> INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP());
ERROR 1142 (42000): INSERT command denied to user
'jon'@'ghidora' for table 'mytable'
检查信息模式EVENTS
表显示e_store_ts
存在且已启用,但其LAST_EXECUTED
列为NULL
:
mysql> SELECT * FROM INFORMATION_SCHEMA.EVENTS
> WHERE EVENT_NAME='e_store_ts'
> AND EVENT_SCHEMA='myschema'\G
*************************** 1\. row ***************************
EVENT_CATALOG: NULL
EVENT_SCHEMA: myschema
EVENT_NAME: e_store_ts
DEFINER: jon@ghidora
EVENT_BODY: SQL
EVENT_DEFINITION: INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP())
EVENT_TYPE: RECURRING
EXECUTE_AT: NULL
INTERVAL_VALUE: 5
INTERVAL_FIELD: SECOND
SQL_MODE: NULL
STARTS: 0000-00-00 00:00:00
ENDS: 0000-00-00 00:00:00
STATUS: ENABLED
ON_COMPLETION: NOT PRESERVE
CREATED: 2006-02-09 22:36:06
LAST_ALTERED: 2006-02-09 22:36:06
LAST_EXECUTED: NULL
EVENT_COMMENT: 1 row in set (0.00 sec)
要撤销EVENT
权限,请使用REVOKE
语句。在此示例中,从jon@ghidora
用户账户中移除了模式myschema
上的EVENT
权限:
REVOKE EVENT ON myschema.* FROM jon@ghidora;
重要提示
从用户那里撤销EVENT
权限不会删除或禁用该用户可能创建的任何事件。
事件不会因为创建它的用户被重命名或删除而迁移或删除。
假设用户jon@ghidora
已被授予myschema
模式上的EVENT
和INSERT
权限。然后该用户创建了以下事件:
CREATE EVENT e_insert
ON SCHEDULE
EVERY 7 SECOND
DO
INSERT INTO myschema.mytable;
创建了这个事件后,root
撤销了jon@ghidora
的EVENT
权限。然而,e_insert
继续执行,每七秒插入一行到mytable
中。如果root
发出了以下任一语句,情况也是如此:
-
DROP USER jon@ghidora;
-
RENAME USER jon@ghidora TO someotherguy@ghidora;
您可以通过在发出DROP USER
或RENAME USER
语句之前和之后检查信息模式EVENTS
表来验证这一点。
事件定义存储在数据字典中。要删除由另一个用户账户创建的事件,您必须是 MySQL root
用户或具有必要权限的其他用户。
用户的EVENT
权限存储在mysql.user
和mysql.db
表的Event_priv
列中。在这两种情况下,该列保存值'Y
'或'N
'中的一个。'N
'是默认值。对于给定用户,只有当该用户具有全局EVENT
权限(即,如果使用GRANT EVENT ON *.*
授予了权限)时,mysql.user.Event_priv
才设置为'Y
'。对于模式级别的EVENT
权限,GRANT
在mysql.db
中创建一行,并将该行的Db
列设置为模式名称,User
列设置为用户名称,Event_priv
列设置为'Y
'。永远不应该直接操作这些表,因为GRANT EVENT
和REVOKE EVENT
语句会在其上执行所需的操作。
五个状态变量提供了与事件相关操作的计数(但不包括事件执行的语句;请参阅第 27.8 节,“存储程序的限制”)。这些是:
-
Com_create_event
: 自上次服务器重启以来执行的CREATE EVENT
语句的次数。 -
Com_alter_event
: 自上次服务器重启以来执行的ALTER EVENT
语句的次数。 -
Com_drop_event
: 自上次服务器重启以来执行的DROP EVENT
语句的次数。 -
Com_show_create_event
: 自上次服务器重启以来执行的SHOW CREATE EVENT
语句的次数。 -
Com_show_events
: 自上次服务器重启以来执行的SHOW EVENTS
语句的次数。
通过运行语句SHOW STATUS LIKE '%event%';
,您可以一次查看所有这些的当前值。
27.5 使用视图
27.5.1 视图语法
27.5.2 视图处理算法
27.5.3 可更新和可插入视图
27.5.4 带有 CHECK OPTION 子句的视图
27.5.5 视图元数据
MySQL 支持视图,包括可更新的视图。视图是存储的查询,当调用时产生一个结果集。视图充当虚拟表。
以下讨论描述了创建和删除视图的语法,并展示了如何使用它们的一些示例。
其他资源
-
在处理视图时,您可能会发现MySQL 用户论坛很有帮助。
-
关于 MySQL 中视图的一些常见问题的答案,请参见第 A.6 节,“MySQL 8.0 FAQ:视图”。
-
使用视图时有一些限制;参见第 27.9 节,“视图限制”。
27.5.1 视图语法
CREATE VIEW
语句创建一个新视图(参见 Section 15.1.23, “CREATE VIEW Statement”)。要修改视图的定义或删除视图,请使用ALTER VIEW
(参见 Section 15.1.11, “ALTER VIEW Statement”)或DROP VIEW
(参见 Section 15.1.35, “DROP VIEW Statement”)。
视图可以从多种类型的SELECT
语句创建。它可以引用基本表或其他视图。它可以使用连接、UNION
和子查询。SELECT
甚至不需要引用任何表。以下示例定义了一个视图,从另一个表中选择了两列,以及从这些列计算出的表达式:
mysql> CREATE TABLE t (qty INT, price INT);
mysql> INSERT INTO t VALUES(3, 50), (5, 60);
mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysql> SELECT * FROM v;
+------+-------+-------+
| qty | price | value |
+------+-------+-------+
| 3 | 50 | 150 |
| 5 | 60 | 300 |
+------+-------+-------+
mysql> SELECT * FROM v WHERE qty = 5;
+------+-------+-------+
| qty | price | value |
+------+-------+-------+
| 5 | 60 | 300 |
+------+-------+-------+
27.5.2 视图处理算法
CREATE VIEW
或 ALTER VIEW
的可选 ALGORITHM
子句是 MySQL 对标准 SQL 的扩展。它影响 MySQL 处理视图的方式。ALGORITHM
有三个值:MERGE
、TEMPTABLE
或 UNDEFINED
。
-
对于
MERGE
,引用视图的语句文本和视图定义被合并,以便视图定义的部分替换语句的相应部分。 -
对于
TEMPTABLE
,视图的结果被检索到一个临时表中,然后用于执行语句。 -
对于
UNDEFINED
,MySQL 会选择使用哪种算法。如果可能的话,它会优先选择MERGE
而不是TEMPTABLE
,因为MERGE
通常更有效率,而且如果使用临时表,则视图无法更新。 -
如果没有
ALGORITHM
子句,则默认算法由optimizer_switch
系统变量的derived_merge
标志的值确定。有关更多讨论,请参见 Section 10.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization”。
明确指定 TEMPTABLE
的一个原因是,在创建临时表后并在用于完成语句处理之前,可以释放对基础表的锁。这可能导致比 MERGE
算法更快地释放锁,以便使用视图的其他客户端不会被阻塞太久。
视图算法可能为 UNDEFINED
有三个原因:
-
CREATE VIEW
语句中没有ALGORITHM
子句。 -
CREATE VIEW
语句具有显式的ALGORITHM = UNDEFINED
子句。 -
为只能使用临时表处理的视图指定了
ALGORITHM = MERGE
。在这种情况下,MySQL 会生成警告并将算法设置为UNDEFINED
。
如前所述,MERGE
是通过将视图定义的相应部分合并到引用该视图的语句中来处理的。以下示例简要说明了 MERGE
算法的工作原理。这些示例假设存在一个具有以下定义的视图 v_merge
:
CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS
SELECT c1, c2 FROM t WHERE c3 > 100;
示例 1:假设我们发出以下语句:
SELECT * FROM v_merge;
MySQL 处理该语句如下:
-
v_merge
变为t
-
*
变为vc1, vc2
,对应于c1, c2
-
视图
WHERE
子句被添加
要执行的结果语句变为:
SELECT c1, c2 FROM t WHERE c3 > 100;
示例 2:假设我们发出以下语句:
SELECT * FROM v_merge WHERE vc1 < 100;
这个语句的处理方式与前一个类似,只是vc1 < 100
变成了c1 < 100
,并且视图的WHERE
子句被添加到语句的WHERE
子句中,使用AND
连接词(并且添加括号以确保子句的部分以正确的优先级执行)。最终要执行的语句变为:
SELECT c1, c2 FROM t WHERE (c3 > 100) AND (c1 < 100);
实际上,要执行的语句具有以下形式的WHERE
子句:
WHERE (select WHERE) AND (view WHERE)
如果无法使用MERGE
算法,则必须使用临时表。阻止合并的结构与阻止派生表和公共表达式合并的结构相同。例如,在子查询中使用SELECT DISTINCT
或LIMIT
。有关详细信息,请参见 Section 10.2.2.4,“使用合并或实体化优化派生表、视图引用和公共表达式”。
27.5.3 可更新和可插入视图
一些视图是可更新的,并且对它们的引用可以用于指定在数据更改语句中要更新的表。也就是说,您可以在UPDATE
、DELETE
或INSERT
等语句中使用它们来更新基础表的内容。派生表和公共表达式也可以在多表UPDATE
和DELETE
语句中指定,但只能用于读取数据以指定要更新或删除的行。通常,视图引用必须是可更新的,这意味着它们可以合并而不是实体化。复合视图有更复杂的规则。
要使视图可更新,视图中的行与基础表中的行之间必须是一对一的关系。还有一些其他构造使视图不可更新。更具体地说,如果视图包含以下任何内容,则视图不可更新:
-
聚合函数或窗口函数(
SUM()
、MIN()
、MAX()
、COUNT()
等) -
DISTINCT
-
GROUP BY
-
HAVING
-
UNION
或UNION ALL
-
在选择列表中的子查询
在选择列表中,非依赖子查询对于
INSERT
失败,但对于UPDATE
和DELETE
可行。对于选择列表中的依赖子查询,不允许数据更改语句。 -
某些连接(请参阅本节后面的其他连接讨论)
-
在
FROM
子句中引用不可更新的视图 -
在
FROM
子句中引用WHERE
子句中的子查询 -
仅引用文字值(在这种情况下,没有要更新的基础表)
-
ALGORITHM = TEMPTABLE
(始终使用临时表会使视图不可更新) -
对基表的任何列的多次引用(对于
INSERT
失败,对于UPDATE
和DELETE
可行)
视图中的生成列被视为可更新,因为可以对其进行赋值。但是,如果显式更新此类列,则唯一允许的值是DEFAULT
。有关生成列的信息,请参见第 15.1.20.8 节,“CREATE TABLE and Generated Columns”。
有时,多表视图可能是可更新的,假设它可以使用MERGE
算法处理。为使其工作,视图必须使用内连接(而不是外连接或UNION
)。此外,视图定义中只能更新一个表,因此SET
子句必须仅命名视图中一个表的列。即使理论上可更新,也不允许使用UNION ALL
的视图。
就插入性(使用INSERT
语句可更新)而言,如果可更新视图还满足视图列的以下附加要求,则可插入:
-
视图列名称不能重复。
-
视图必须包含基表中没有默认值的所有列。
-
视图列必须是简单的列引用。不能是表达式,比如这些:
3.14159 col1 + 3 UPPER(col2) col3 / col4 (*subquery*)
MySQL 在CREATE VIEW
时设置一个称为视图可更新性标志的标志。如果视图对UPDATE
和DELETE
(以及类似操作)是合法的,则将该标志设置为YES
(true)。否则,将该标志设置为NO
(false)。信息模式VIEWS
表中的IS_UPDATABLE
列显示此标志的状态。这意味着服务器始终知道视图是否可更新。
如果视图不可更新,则UPDATE
、DELETE
和INSERT
等语句是非法的并将被拒绝。(即使视图是可更新的,也可能无法插入,如本节其他地方所述。)
视图的可更新性可能会受到updatable_views_with_limit
系统变量值的影响。请参阅第 7.1.8 节,“服务器系统变量”。
对于以下讨论,假设存在这些表和视图:
CREATE TABLE t1 (x INTEGER);
CREATE TABLE t2 (c INTEGER);
CREATE VIEW vmat AS SELECT SUM(x) AS s FROM t1;
CREATE VIEW vup AS SELECT * FROM t2;
CREATE VIEW vjoin AS SELECT * FROM vmat JOIN vup ON vmat.s=vup.c;
允许INSERT
、UPDATE
和DELETE
语句如下:
-
INSERT
:INSERT
语句的插入表可以是一个合并的视图引用。如果视图是一个连接视图,则视图的所有组件必须是可更新的(非物化的)。对于多表可更新视图,如果插入到单个表,则INSERT
可以工作。该语句无效,因为连接视图的一个组件是不可更新的:
INSERT INTO vjoin (c) VALUES (1);
这个语句是有效的;视图不包含实体组件:
INSERT INTO vup (c) VALUES (1);
-
UPDATE
:在UPDATE
语句中要更新的表或表可以是合并的视图引用。如果一个视图是连接视图,视图的至少一个组件必须是可更新的(这与INSERT
不同)。在多表
UPDATE
语句中,语句的更新表引用必须是基本表或可更新的视图引用。未更新的表引用可以是物化视图或派生表。这个语句是有效的;列
c
来自连接视图的可更新部分:UPDATE vjoin SET c=c+1;
这个语句是无效的;列
x
来自不可更新的部分:UPDATE vjoin SET x=x+1;
这个语句是有效的;多表
UPDATE
的更新表引用是一个可更新的视图(vup
):UPDATE vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ... SET c=c+1;
这个语句是无效的;它试图更新一个实体派生表:
UPDATE vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ... SET s=s+1;
-
DELETE
:在DELETE
语句中要从中删除的表或表必须是合并视图。不允许连接视图(这与INSERT
和UPDATE
不同)。这个语句是无效的,因为视图是一个连接视图:
DELETE vjoin WHERE ...;
这个语句是有效的,因为视图是一个合并的(可更新的)视图:
DELETE vup WHERE ...;
这个语句是有效的,因为它从一个合并的(可更新的)视图中删除:
DELETE vup FROM vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ...;
附加讨论和示例如下。
本节早期讨论指出,如果视图不是所有列都是简单列引用(例如,如果包含表达式或复合表达式的列),则视图是不可插入的。尽管这样的视图不可插入,但如果只更新非表达式列,则可以更新。考虑这个视图:
CREATE VIEW v AS SELECT col1, 1 AS col2 FROM t;
这个视图不可插入,因为col2
是一个表达式。但如果更新不尝试更新col2
,则可以更新。这个更新是允许的:
UPDATE v SET col1 = 0;
此更新不允许,因为它试图更新一个表达式列:
UPDATE v SET col2 = 0;
如果表包含一个AUTO_INCREMENT
列,在对不包括AUTO_INCREMENT
列的表上插入可插入视图时,不会改变LAST_INSERT_ID()
的值,因为插入默认值到视图中不是可见的列的副作用不应该可见。
27.5.4 视图 WITH CHECK OPTION 子句
可以为可更新视图提供WITH CHECK OPTION
子句,以防止插入到select_statement
中WHERE
子句不为真的行。它还防止更新使WHERE
子句为真但更新会导致其不为真的行(换句话说,它防止可见行被更新为不可见行)。
在可更新视图的WITH CHECK OPTION
子句中,LOCAL
和CASCADED
关键字确定了在视图以另一个视图的形式定义时进行检查测试的范围。当没有给出关键字时,默认为CASCADED
。
WITH CHECK OPTION
测试符合标准:
-
使用
LOCAL
,视图WHERE
子句会被检查,然后检查递归到底层视图并应用相同的规则。 -
使用
CASCADED
,视图WHERE
子句会被检查,然后检查递归到底层视图,为它们添加WITH CASCADED CHECK OPTION
(用于检查目的;它们的定义保持不变),并应用相同的规则。 -
没有检查选项时,视图
WHERE
子句不会被检查,然后检查会递归到底层视图,并应用相同的规则。
考虑以下表和一组视图的定义:
CREATE TABLE t1 (a INT);
CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2
WITH CHECK OPTION;
CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0
WITH LOCAL CHECK OPTION;
CREATE VIEW v3 AS SELECT * FROM v1 WHERE a > 0
WITH CASCADED CHECK OPTION;
这里的v2
和v3
视图是以另一个视图v1
的形式定义的。
对于v2
的插入会根据其LOCAL
检查选项进行检查,然后检查递归到v1
并再次应用规则。v1
的规则导致检查失败。v3
的检查也失败:
mysql> INSERT INTO v2 VALUES (2);
ERROR 1369 (HY000): CHECK OPTION failed 'test.v2'
mysql> INSERT INTO v3 VALUES (2);
ERROR 1369 (HY000): CHECK OPTION failed 'test.v3'
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 我与微信审核的“相爱相杀”看个人小程序副业
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· spring官宣接入deepseek,真的太香了~