本章讨论 用户定义的分区 。
表分区与窗口函数使用的分区不同。 有关窗口函数的信息,请参见 第12.21节“窗口函数” 。
在MySQL 8.0,分区支持通过提供
InnoDB
和
NDB
存储引擎。
MySQL 8.0当前不支持使用除
InnoDB
or
之外的任何存储引擎对表进行分区
NDB
,例如
MyISAM
。
使用不提供本机分区支持的存储引擎创建分区表的尝试因
ER_CHECK_NOT_IMPLEMENTED而
失败
。
Oracle提供的MySQL 8.0的社区二进制文件包括由提供分区支持
InnoDB
和
NDB
存储引擎。
有关MySQL Enterprise Edition二进制文件中提供的分区支持的信息,请参阅
第30章,
MySQL Enterprise Edition
。
如果从源代码编译MySQL 8.0,则使用
InnoDB
支持
配置构建
就足以生成具有
InnoDB
表的
分区支持的二进制文件
。
有关更多信息,请参见
第2.9节“从源安装MySQL”
。
不需要做任何其他事情来启用分区支持
InnoDB
(例如,文件中不需要特殊条目
my.cnf
)。
无法禁用
InnoDB
存储引擎的
分区支持
。
有关 分区和分区概念的介绍, 请参见 第23.1节“MySQL中的分区概述” 。
支持几种类型的分区,以及子分区; 请参见 第23.2节“分区类型” 和 第23.2.6节 “子分区 ” 。
第23.3节“分区管理” 包括在现有分区表中添加,删除和更改分区的方法。
第23.3.4节“分区维护” 讨论了与分区表一起使用的表维护命令。
数据库中
的
PARTITIONS
表
INFORMATION_SCHEMA
提供有关分区和分区表的信息。
有关
更多信息,
请参见
第25.17节“INFORMATION_SCHEMA PARTITIONS表”
。
有关此表的查询的一些示例,请参见
第23.2.7节“MySQL分区如何处理NULL”
。
有关MySQL 8.0中分区的已知问题,请参见 第23.6节“分区的限制和限制” 。
在使用分区表时,您还可以找到以下资源。
其他资源。 有关MySQL中用户定义分区的其他信息来源包括:
对于那些对MySQL Partitioning技术感兴趣或正在尝试的人来说,这是一个官方讨论论坛。 它包含来自MySQL开发人员和其他人的公告和更新。 它由分区开发和文档团队的成员监控。
MySQL Partitioning Architect和首席开发人员MikaelRonström经常在这里发表有关他使用MySQL Partitioning和NDB Cluster的文章。
一个MySQL新闻网站,其中包含与MySQL相关的博客,任何人都应该对使用我的MySQL感兴趣。 我们建议您在此处查看使用MySQL Partitioning的人员保存的博客链接,或者将自己的博客添加到所涵盖的博客中。
本节提供了MySQL 8.0中分区的概念性概述。
有关分区限制和功能限制的信息,请参见 第23.6节“分区的限制和限制” 。
SQL标准没有提供有关数据存储的物理方面的指导。
SQL语言本身旨在独立于与其一起工作的模式,表,行或列的任何数据结构或媒体。
尽管如此,大多数高级数据库管理系统已经发展出一些方法来确定用于存储文件系统,硬件甚至两者的特定数据的物理位置。
在MySQL中,
InnoDB
存储引擎长期以来一直支持表空间的概念(参见
第15.6.3节“表空间”)
),MySQL服务器,甚至在引入分区之前,可以配置为使用不同的物理目录来存储不同的数据库(参见
第8.12.2节“使用符号链接”
,以解释如何完成此操作)。
通过使您能够根据您可以根据需要设置的规则在文件系统中分发单个表的各个部分, 分区 将这一概念更进一步。 实际上,表的不同部分作为单独的表存储在不同的位置。 用于完成数据划分的用户选择规则称为 分区功能 在MySQL中,它可以是模数,与一组范围或值列表的简单匹配,内部散列函数或线性散列函数。 根据用户指定的分区类型选择该函数,并将用户提供的表达式的值作为其参数。 此表达式可以是列值,作用于一个或多个列值的函数,或一组一个或多个列值,具体取决于所使用的分区类型。
在
,和[
]
分区
的情况下
RANGE
,
分区列的值被传递给分区函数,该分区函数返回一个整数值,该值表示应该存储该特定记录的分区的编号。
此函数必须是非常量且非随机的。
它可能不包含任何查询,但可以使用在MySQL中有效的SQL表达式,只要该表达式返回一个
或整数
,以便
LIST
LINEAR
HASH
NULL
intval
-MAXVALUE <= intval
<= MAXVALUE
(
MAXVALUE
用于表示所讨论的整数类型的最小上限。
-MAXVALUE
表示最大下限。)
对于[
LINEAR
]
KEY
,
RANGE COLUMNS
和
LIST
COLUMNS
分区,分区表达式由一列或多列组成。
对于[
LINEAR
]
KEY
分区,分区功能由MySQL提供。
有关允许的分区列类型和分区函数的更多信息,请参见 第23.2节“分区类型” ,以及 第13.1.20节“CREATE TABLE语法” ,它提供了分区语法描述和其他示例。 有关分区函数限制的信息,请参见 第23.6.3节“分区与函数相关的限制” 。
这称为 水平分区 - 也就是说,表的不同行可以分配给不同的物理分区。 MySQL 8.0不支持 垂直分区 ,其中表的不同列分配给不同的物理分区。 目前还没有计划将垂直分区引入MySQL。
要创建分区表,必须使用支持它们的存储引擎。 在MySQL 8.0中,同一分区表的所有分区必须使用相同的存储引擎。 但是,没有什么可以阻止您在同一个MySQL服务器上甚至在同一个数据库中为不同的分区表使用不同的存储引擎。
在MySQL 8.0中,唯一支持分区的存储引擎是
InnoDB
和
NDB
。
分区不能用于不支持它的存储引擎;
这些包括
MyISAM
,
MERGE
,
CSV
,和
FEDERATED
存储引擎。
使用此存储引擎的表不支持对其进行
分区
KEY
或
LINEAR
KEY
可能进行
分区
NDB
,但不支持其他类型的用户定义分区。
此外,
NDB
使用用户定义分区
的
表必须具有显式主键,并且表的分区表达式中引用的任何列都必须是主键的一部分。
但是,如果在
用于创建或修改用户分区
表
的
or
语句
的
PARTITION BY KEY
or
PARTITION BY
LINEAR KEY
子句中
未列出任何列
,则表不需要具有显式主键。
有关更多信息,请参见
第22.1.7.1节“NDB群集中不符合SQL语法”
。
CREATE
TABLE
ALTER
TABLE
NDB
创建分区表时,默认存储引擎的使用方式与创建任何其他表时一样;
要覆盖此行为,
[STORAGE]
ENGINE
只需像
使用
未分区的表一样
使用该
选项。
目标存储引擎必须提供本机分区支持,否则语句将失败。
您应该记住,
在
语句
中使用任何分区选项
之前
[STORAGE] ENGINE
,需要列出(和其他表选项)
。
此示例显示如何创建一个通过散列分区为6个分区并使用
存储引擎的表(无论其值如何
):
CREATE
TABLE
InnoDB
default_storage_engine
CREATE TABLE ti(id INT,amount DECIMAL(7,2),tr_date DATE) ENGINE = INNODB 哈希分区(月(tr_date)) 分数6;
每个
PARTITION
子句都可以包含一个
[STORAGE] ENGINE
选项,但在MySQL 8.0中这没有任何效果。
除非另有说明,否则本讨论中的其余示例均假定
default_storage_engine
为
InnoDB
。
分区适用于表的所有数据和索引; 您不能只分区数据而不分区索引,反之亦然,也不能只分区表的一部分。
可以使用
用于创建分区表
的
语句
的
子句
DATA DIRECTORY
和
INDEX DIRECTORY
选项
将每个分区的数据和索引分配给特定目录
。
PARTITION
CREATE TABLE
表的
DATA DIRECTORY
各个分区和子分区
仅
支持
该
选项
InnoDB
。
表的分区表达式中使用的所有列必须是表可能具有的每个唯一键的一部分,包括任何主键。 这意味着无法对以下SQL语句创建的此类表进行分区:
CREATE TABLE tnp( id INT NOT NULL AUTO_INCREMENT, ref BIGINT NOT NULL, name VARCHAR(255), PRIMARY KEY pk(id), UNIQUE KEY uk(姓名) );
由于键
pk
并
uk
没有共同的列,因此没有可用于分区表达式的列。
在这种情况下可能的解决方法包括将
name
列
添加
到表的主键,添加
id
列
uk
,或者只是完全删除唯一键。
有关
更多信息
,
请参见
第23.6.1节“对键,主键和唯一键进行分区”
。
另外,
MAX_ROWS
和
MIN_ROWS
可被用于确定行,分别的最大和最小数字,可以被存储在每个分区中。
有关
这些选项的更多信息
,
请参见
第23.3节“分区管理”
。
该
MAX_ROWS
选项对于创建具有额外分区的NDB Cluster表也很有用,因此可以更好地存储哈希索引。
有关详细信息,请参阅
DataMemory
数据节点配置参数
的文档
以及
第22.1.2节“NDB群集节点,节点组,副本和分区”
。
此处列出了分区的一些优点:
通过分区,可以在一个表中存储比在单个磁盘或文件系统分区上保存的数据更多的数据。
通过删除仅包含该数据的分区(或多个分区),通常可以轻松地从分区表中删除失去其实用性的数据。 相反,在某些情况下,通过添加一个或多个新分区来特别存储该数据,可以极大地促进添加新数据的过程。
由于满足给定
WHERE
子句的数据只能存储在一个或多个分区上,因此
可以大大优化某些查询
,这会自动从搜索中排除任何剩余的分区。
由于在创建分区表后可以更改分区,因此可以重新组织数据以增强在首次设置分区方案时可能不常使用的频繁查询。
这种排除不匹配分区(以及它们包含的任何行)的能力通常称为
分区修剪
。
有关更多信息,请参见
第23.4节“分区修剪”
。
此外,MySQL支持查询的显式分区选择。
例如,
SELECT *
FROM t
PARTITION (p0,p1) WHERE c < 5
在分区仅选择那些行
p0
和
p1
该匹配
WHERE
条件。
在这种情况下,MySQL不会检查表的任何其他分区
t
;
当您已经知道要检查哪个或哪些分区时,这可以大大加快查询速度。
选择分区还支持数据修改语句
DELETE
,
INSERT
,
REPLACE
,
UPDATE
,和
LOAD
DATA
,
LOAD XML
。
有关更多信息和示例,请参阅这些语句的说明。
本节讨论MySQL 8.0中可用的分区类型。 这些包括此处列出的类型:
RANGE分区。
这种类型的分区基于落在给定范围内的列值将行分配给分区。
请参见
第23.2.1节“范围分区”
。
有关此类型扩展的信息
RANGE COLUMNS
,请参见
第23.2.3.1节“RANGE COLUMNS分区”
。
列表分区。
与分区相似
RANGE
,除了基于匹配一组离散值之一的列选择分区。
请参见
第23.2.2节“列表分区”
。
有关此类型扩展的信息
LIST COLUMNS
,请参见
第23.2.3.2节“列表列分区”
。
HASH分区。
使用这种类型的分区,将根据用户定义的表达式返回的值选择分区,该表达式对要插入表中的行中的列值进行操作。
该函数可以包含在MySQL中有效的任何表达式,该表达式产生非负整数值。
LINEAR HASH
也可以使用
此类型的扩展名
。
请参见
第23.2.4节“HASH分区”
。
KEY分区。
这种类型的分区类似于分区
HASH
,除了只提供一个或多个要评估的列,MySQL服务器提供自己的散列函数。
这些列可以包含非整数值,因为MySQL提供的散列函数可以保证整数结果,而不管列数据类型如何。
LINEAR KEY
也可以使用
此类型的扩展名
。
请参见
第23.2.5节“KEY分区”
。
数据库分区的一个非常常见的用途是按日期隔离数据。
一些数据库系统支持显式日期分区,MySQL在8.0中没有实现。
但是,不能在MySQL中很难创建基于分区方案
DATE
,
TIME
或
DATETIME
列,或基于表达式利用这样的列。
当由分区
KEY
或
LINEAR
KEY
,可以使用一个
DATE
,
TIME
或
DATETIME
列作为分区列不进行列值的任何修饰。
例如,这个表创建语句在MySQL中完全有效:
CREATE TABLE成员( firstname VARCHAR(25)NOT NULL, lastname VARCHAR(25)NOT NULL, username VARCHAR(16)NOT NULL, 电子邮件VARCHAR(35), 加入DATE NOT NULL ) PARTITION BY KEY(已加入) 分数6;
在MySQL 8.0中,还可以使用
DATE
或
DATETIME
列作为分区列使用
RANGE COLUMNS
和
LIST
COLUMNS
分区。
其他分区类型需要一个分区表达式,它产生一个整数值或
NULL
。
如果你想通过使用基于日期的分区
RANGE
,
LIST
,
HASH
,或者
LINEAR HASH
,你可以简单地使用,其操作上的功能
DATE
,
TIME
或
DATETIME
列,并返回这样的值,如下所示:
CREATE TABLE成员( firstname VARCHAR(25)NOT NULL, lastname VARCHAR(25)NOT NULL, username VARCHAR(16)NOT NULL, 电子邮件VARCHAR(35), 加入DATE NOT NULL ) 按比例分区(年份(已加入))( 分数p0值低于(1960), 分区p1值不到(1970年), 分区p2值低于(1980), 分区p3值低于(1990), 分区p4值低于MAXVALUE );
使用日期进行分区的其他示例可以在本章的以下部分中找到:
有关基于日期的分区的更复杂示例,请参阅以下部分:
MySQL的分区是采用最优化
TO_DAYS()
,
YEAR()
和
TO_SECONDS()
功能。
但是,您可以使用其他日期和时间函数返回一个整数或者
NULL
,例如
WEEKDAY()
,
DAYOFYEAR()
或
MONTH()
。
有关此类函数的更多信息
,
请参见
第12.7节“日期和时间函数”
。
重要的是要记住 - 无论您使用何种分区 - 分区始终自动编号,并在创建时按顺序编号,从
0
。
将新行插入分区表时,正是这些分区号用于标识正确的分区。
例如,如果你的表使用4个分区,这些分区编号
0
,
1
,
2
,和
3
。
对于
RANGE
和
LIST
分区类型,必须确保为每个分区号定义了分区。
对于
HASH
分区,用户提供的表达式必须求值为大于的整数值
0
。
对于
KEY
分区,这个问题由MySQL服务器内部使用的散列函数自动处理。
分区的名称通常遵循管理其他MySQL标识符的规则,例如表和数据库的标识符。
但是,您应该注意分区名称不区分大小写。
例如,以下
CREATE
TABLE
语句失败,如下所示:
mysql>CREATE TABLE t2 (val INT)
- >PARTITION BY LIST(val)(
- >PARTITION mypart VALUES IN (1,3,5),
- >PARTITION MyPart VALUES IN (2,4,6)
- >);
错误1488(HY000):重复的分区名称mypart
发生故障是因为MySQL看不到分区名称
mypart
和
MyPart
。
当您指定表的分区数时,必须将其表示为不带前导零的正,非零整数文字,并且可能不是表达式,如
0.8E+01
或
6-2
,即使它计算为整数值。
不允许使用小数部分。
在接下来的部分中,我们不一定提供可用于创建每个分区类型的语法的所有可能形式; 有关此信息,请参见 第13.1.20节“CREATE TABLE语法” 。
按范围分区的表的分区方式是每个分区包含分区表达式值位于给定范围内的行。
范围应该是连续的但不重叠,并使用
VALUES LESS THAN
运算符
定义
。
对于接下来的几个示例,假设您要创建一个表,如下所示,以保存20个视频商店链的人事记录,编号为1到20:
CREATE TABLE员工( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), 雇用DATE NOT NULL DEFAULT'1970-01-01', 分隔DATE NOT NULL DEFAULT'9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL );
employees
此处使用
的
表没有主键或唯一键。
虽然这些示例的工作方式与本讨论的目的相同,但您应该记住,实际上表中很可能有主键,唯一键或两者,并且分区列的允许选择取决于用于这些列的列密钥,如果有的话。
有关这些问题的讨论,请参见
第23.6.1节“分区键,主键和唯一键”
。
根据您的需要,可以通过多种方式按范围对此表进行分区。
一种方法是使用该
store_id
列。
例如,您可能决定通过添加
PARTITION BY RANGE
如下所示
的
子句
来对表4进行分区
:
CREATE TABLE员工( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), 雇用DATE NOT NULL DEFAULT'1970-01-01', 分隔DATE NOT NULL DEFAULT'9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL ) PAR BY BY RANGE(store_id)( 分区p0值小于(6), 分区p1值小于(11), 分区p2值小于(16), 分区p3值低于(21) );
在该分区方案中,对应于在商店1到5工作的雇员的所有行存储在分区中
p0
,对于在商店6到10中使用的那些行存储在分区中
p1
,等等。
每个分区按从低到高的顺序定义。
这是
PARTITION BY RANGE
语法
的要求
;
if ... elseif ...
在这方面,
你可以认为它类似于
C或Java中
的一系列
语句。
这是很容易确定包含数据的新行
(72, 'Mitchell', 'Wilson', '1998-06-25', NULL,
13)
插入分区
p2
,但是当你的链增加了21会发生什么
ST
店?
在此方案下,没有规则覆盖
store_id
大于20
的行
,因此导致错误,因为服务器不知道将其放置在何处。
您可以通过
在
语句中
使用
“
catchall
”
VALUES LESS
THAN
子句
来防止这种情况发生
,该
CREATE
TABLE
语句提供的所有值都大于显式命名的最高值:
CREATE TABLE员工(
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
雇用DATE NOT NULL DEFAULT'1970-01-01',
分隔DATE NOT NULL DEFAULT'9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PAR BY BY RANGE(store_id)(
分区p0值小于(6),
分区p1值小于(11),
分区p2值小于(16),
PARTITION p3值比MAXVALUE低
);
(与本章中的其他示例一样,我们假设默认存储引擎是
InnoDB
。)
找不到匹配值时避免错误的另一种方法是使用
IGNORE
关键字作为
INSERT
语句的
一部分
。
有关示例,请参见
第23.2.2节“LIST分区”
。
有关的一般信息
,
另请参见
第13.2.6节“INSERT语法”
IGNORE
。
MAXVALUE
表示一个整数值,该值始终大于最大可能的整数值(在数学语言中,它用作
最小上限
)。
现在,
store_id
列值大于或等于16(定义的最高值)的
任何行都
存储在分区中
p3
。
在未来的某个时刻 - 当商店数量增加到25个,30个或更多时 - 您可以使用
ALTER
TABLE
语句为商店21-25,26-30等添加新分区(参见
第23.3节, “分区管理”
,有关如何执行此操作的详细信息。
以同样的方式,您可以根据员工职务代码对表进行分区 - 即基于
job_code
列值的
范围
。
例如 - 假设两位数的工作代码用于常规(店内)工作人员,三位数代码用于办公室和支持人员,四位数代码用于管理职位 - 您可以创建分区表使用以下声明:
CREATE TABLE员工( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), 雇用DATE NOT NULL DEFAULT'1970-01-01', 分隔DATE NOT NULL DEFAULT'9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL ) 按范围划分(job_code)( 分区p0值小于(100), 分区p1值小于(1000), 分区p2值小于(10000) );
在这种情况下,与店内工人相关的所有行都将存储在分区中
p0
,与办公室和支持人员
p1
相关的行以及与分区中的管理人员相关的行
p2
。
也可以在
VALUES LESS
THAN
子句中
使用表达式
。
但是,MySQL必须能够将表达式的返回值作为
LESS
THAN
(
<
)比较的
一部分进行评估
。
您可以使用基于两
DATE
列
中的一
列
的表达式,而不是根据商店编号拆分表数据
。
例如,假设您希望根据每个员工离开公司的年份进行分区;
也就是说,的价值
YEAR(separated)
。
CREATE
TABLE
此处显示了实现此类分区方案
的
语句
示例
:
CREATE TABLE员工( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), 雇用DATE NOT NULL DEFAULT'1970-01-01', 分隔DATE NOT NULL DEFAULT'9999-12-31', job_code INT, store_id INT ) 按比例分区(年份(分开))( 分数p0值低于(1991), 分区p1值低于(1996), 分区p2值低于(2001), PARTITION p3值比MAXVALUE低 );
在这个方案中,对于1991年之前离开的所有员工,行存储在分区中
p0
;
对于那些谁留在1991年至1995年,在
p1
;
对于那些谁留在1996年年内至2000年,在
p2
;
以及在2000年之后离开的任何工人
p3
。
也可以
使用
函数根据
列
RANGE
的值对
表进行分区
,如下例所示:
TIMESTAMP
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 VALUE少于(UNIX_TIMESTAMP('2008-01-01 00:00:00')), PARTITION p1 VALUE少于(UNIX_TIMESTAMP('2008-04-01 00:00:00')), PARTITION p2 VALUE少于(UNIX_TIMESTAMP('2008-07-01 00:00:00')), PARTITION p3 VALUES比(UNIX_TIMESTAMP('2008-10-01 00:00:00')), PARTITION p4 VALUES少于(UNIX_TIMESTAMP('2009-01-01 00:00:00')), PARTITION p5 VALUES比(UNIX_TIMESTAMP('2009-04-01 00:00:00')), PARTITION p6 VALUES(UNIX_TIMESTAMP('2009-07-01 00:00:00')), PARTITION p7 VALUES少于(UNIX_TIMESTAMP('2009-10-01 00:00:00')), PARTITION p8 VALUE少于(UNIX_TIMESTAMP('2010-01-01 00:00:00')), 分区p9值小于(MAXVALUE) );
TIMESTAMP
不允许
涉及
值的
任何其他表达式
。
(参见Bug#42849。)
当满足以下一个或多个条件时,范围分区特别有用:
您想要或需要删除
“
旧
”
数据。
如果您使用前面显示的分区方案
employees
,则可以简单地使用
ALTER TABLE employees DROP PARTITION p0;
删除与1991年之前停止为公司工作的员工相关的所有行。(请参见
第13.1.9节“ALTER TABLE语法”
和
第23.3节,“分区管理”
,以获取更多信息。)对于包含大量行的表,这比运行
DELETE
查询(例如)
更有效
DELETE FROM employees WHERE YEAR(separated) <=
1990;
。
您希望使用包含日期或时间值的列,或包含来自其他一些系列的值。
您经常运行直接依赖于用于对表进行分区的列的查询。
例如,当执行诸如
EXPLAIN
SELECT
COUNT(*) FROM employees WHERE separated BETWEEN '2000-01-01'
AND '2000-12-31' GROUP BY store_id;
MySQL
的查询时
,MySQL可以快速确定只
p2
需要扫描
分区
,因为其余分区不能包含满足该
WHERE
子句的
任何记录
。
有关如何完成此操作的详细信息
,
请参见
第23.4节“分区修剪”
。
这种分区的一种变体是
RANGE
COLUMNS
分区。
通过分区,
RANGE
COLUMNS
可以使用多列来定义分区范围,这些分区范围既适用于分区中行的放置,也适用于在执行分区修剪时确定包含或排除特定分区。
有关
更多信息
,
请参见
第23.2.3.1节“RANGE COLUMNS分区”
。
基于时间间隔的分区方案。 如果您希望在MySQL 8.0中基于时间范围或时间间隔实现分区方案,您有两种选择:
由分区表
RANGE
,以及用于分隔表达,采用功能上的操作
DATE
,
TIME
或
DATETIME
柱并返回一个整数值,如下所示:
CREATE TABLE成员( firstname VARCHAR(25)NOT NULL, lastname VARCHAR(25)NOT NULL, username VARCHAR(16)NOT NULL, 电子邮件VARCHAR(35), 加入DATE NOT NULL ) 按比例分区(年份(已加入))( 分数p0值低于(1960), 分区p1值不到(1970年), 分区p2值低于(1980), 分区p3值低于(1990), 分区p4值低于MAXVALUE );
在MySQL 8.0中,还可以
使用
函数
RANGE
基于
TIMESTAMP
列
的值对
表进行分区
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 VALUE少于(UNIX_TIMESTAMP('2008-01-01 00:00:00')), PARTITION p1 VALUE少于(UNIX_TIMESTAMP('2008-04-01 00:00:00')), PARTITION p2 VALUE少于(UNIX_TIMESTAMP('2008-07-01 00:00:00')), PARTITION p3 VALUES比(UNIX_TIMESTAMP('2008-10-01 00:00:00')), PARTITION p4 VALUES少于(UNIX_TIMESTAMP('2009-01-01 00:00:00')), PARTITION p5 VALUES比(UNIX_TIMESTAMP('2009-04-01 00:00:00')), PARTITION p6 VALUES(UNIX_TIMESTAMP('2009-07-01 00:00:00')), PARTITION p7 VALUES少于(UNIX_TIMESTAMP('2009-10-01 00:00:00')), PARTITION p8 VALUE少于(UNIX_TIMESTAMP('2010-01-01 00:00:00')), 分区p9值小于(MAXVALUE) );
在MySQL 8.0中,
TIMESTAMP
不允许
涉及
值的
任何其他表达式
。
(参见Bug#42849。)
在MySQL 8.0中,也可以
UNIX_TIMESTAMP(timestamp_column)
将分区表达式用作分区的表
LIST
。
但是,这样做通常是不切实际的。
RANGE COLUMNS
使用
DATE
或
DATETIME
列作为分区列
对表进行
分区。
例如,
members
可以
joined
直接
使用
列
定义表
,如下所示:
CREATE TABLE成员( firstname VARCHAR(25)NOT NULL, lastname VARCHAR(25)NOT NULL, username VARCHAR(16)NOT NULL, 电子邮件VARCHAR(35), 加入DATE NOT NULL ) RANGE COLUMNS(加入)分区( 分区p0值低于('1960-01-01'), 分区p1值低于('1970-01-01'), 分区p2值不到('1980-01-01'), 分区p3的价值低于('1990-01-01'), 分区p4值低于MAXVALUE );
MySQL中的列表分区在很多方面类似于范围分区。
与分区依据一样
RANGE
,必须明确定义每个分区。
两种类型的分区之间的主要区别在于,在列表分区中,每个分区是根据一组值列表中的一个列值中的列值的成员资格来定义和选择的,而不是在一组连续范围中的一个中定义和选择的。值。
这是通过使用
where
值是列值或基于列值的表达式并返回整数值,然后通过a定义每个分区来完成的
,其中
是逗号分隔的整数列表。
PARTITION BY
LIST(
expr
)expr
VALUES IN
(
value_list
)value_list
在MySQL 8.0中,可以
在分区时
仅匹配整数列表(并且可能
NULL
- 请
参见第23.2.7节“MySQL分区如何处理NULL”
)
LIST
。
但是,在使用
LIST COLUMN
分区时,
可以在值列表中使用其他列类型
,本节稍后将对此进行描述。
与范围定义的分区的情况不同,列表分区不需要以任何特定顺序声明。 有关更详细的语法信息,请参见 第13.1.20节“CREATE TABLE语法” 。
对于下面的示例,我们假设要分区的表的基本定义由
CREATE
TABLE
此处显示
的
语句
提供
:
CREATE TABLE员工( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), 雇用DATE NOT NULL DEFAULT'1970-01-01', 分隔DATE NOT NULL DEFAULT'9999-12-31', job_code INT, store_id INT );
(这是用作
第23.2.1节“RANGE分区”中
示例的基础的表
。与其他分区示例一样,我们假设
default_storage_engine
是
InnoDB
。)
假设在4个特许经营店中分布有20个视频商店,如下表所示。
区域 | 商店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员工( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), 雇用DATE NOT NULL DEFAULT'1970-01-01', 分隔DATE NOT NULL DEFAULT'9999-12-31', job_code INT, store_id INT ) PARISTION BY LIST(store_id)( (3,5,6,9,17)中的分区值, (1,2,10,11,19,20)中的分区值, (4,12,13,14,18)中的分区值(PYest VALUES) 分配中心价值(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
分区与散列或密钥分区
相结合
,以产生复合分区(子分区)。
请参见
第23.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)
- >);
查询OK,0行受影响(0.11秒) mysql> ERROR 1525(HY000):表没有值为3的分区INSERT INTO h2 VALUES (3, 5);
当使用单个
INSERT
语句
将多个行插入
单个
InnoDB
表时,请
InnoDB
将该语句视为单个事务,以便存在任何不匹配的值会导致语句完全失败,因此不会插入任何行。
您可以使用
IGNORE
关键字
忽略此类错误
。
如果这样做,则不会插入包含不匹配的分区列值的行,但
会
插入
具有匹配值的任何行
,并且不会报告任何错误:
MySQL的>TRUNCATE h2;
查询正常,1行受影响(0.00秒) MySQL的>SELECT * FROM h2;
空集(0.00秒) MySQL的>INSERT IGNORE INTO h2 VALUES (2, 5), (6, 10), (7, 5), (3, 1), (1, 9);
查询OK,3行受影响(0.00秒) 记录:5个重复:2个警告:0 MySQL的>SELECT * FROM h2;
+ ------ + ------ + | c1 | c2 | + ------ + ------ + | 7 | 5 | | 1 | 9 | | 2 | 5 | + ------ + ------ + 3组(0.00秒)
MySQL 8.0还提供对
LIST
COLUMNS
分区的
支持,
分区是一种
LIST
分区
变体,
它允许您使用除整数类型之外的类型列来分区列,并使用多个列作为分区键。
有关更多信息,请参见
第23.2.3.2节“列表列分区”
。
接下来的两节讨论
COLUMNS
分区
,它们是变体
RANGE
和
LIST
分区。
COLUMNS
分区允许在分区键中使用多个列。
所有这些列都被考虑在内,以便在分区中放置行,以及确定在分区修剪中检查哪些分区的匹配行。
此外,
RANGE COLUMNS
分区和
LIST COLUMNS
分区都支持使用非整数列来定义值范围或列表成员。
允许的数据类型显示在以下列表中:
在接下来的两节中
讨论
RANGE COLUMNS
和
LIST COLUMNS
分区假设您已经熟悉MySQL 5.1及更高版本支持的基于范围和列表的分区;
有关这些的更多信息,请分别参见
第23.2.1节“RANGE分区”
和
第23.2.2节“LIST分区”
。
范围列分区与范围分区类似,但允许您使用基于多个列值的范围来定义分区。 此外,您可以使用除整数类型之外的类型列来定义范围。
RANGE COLUMNS
分区与
RANGE
以下方式的
分区显着
不同:
RANGE COLUMNS
不接受表达式,只接受列的名称。
RANGE COLUMNS
接受一列或多列。
RANGE COLUMNS
分区基于
元组
(列值列表)之间的比较,而不是标量值之间的比较。
RANGE
COLUMNS
分区
中行的放置
也基于元组之间的比较;
这将在本节后面进一步讨论。
RANGE COLUMNS
分区列不限于整数列;
字符串
DATE
和
DATETIME
列也可以用作分区列。
(有关
详细信息
,
请参见
第23.2.3节“列分区”
。)
创建分区的表的基本语法
RANGE COLUMNS
如下所示:
table_name
由RANGE COLUMNS(column_list
)划分的创建表() 分区partition_name
价值低于(value_list
)[, 分区partition_name
价值低于(value_list
)] [, ...] )column_list
:column_name
[,column_name
] [,...]value_list
:value
[,value
] [,...]
CREATE TABLE
此处显示的
并非
创建分区表时可以使用的
所有
选项。
有关完整信息,请参见
第13.1.20节“CREATE TABLE语法”
。
在刚刚显示的语法中,
column_list
是一列或多列(有时称为
分区列列表
),并且
value_list
是值列表(即,它是
分区定义值列表
)。
value_list
必须为每个分区定义提供
A
,并且每个分区定义
value_list
必须具有与列相同数量的值
column_list
。
一般来说,如果
N
在
COLUMNS
子句中
使用
列
,则
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)
- >);
查询OK,0行受影响(0.15秒)
表
rcx
包含列
a
,
b
,
c
,
d
。
供给至分区列列表
COLUMNS
子句使用这些列3中,在顺序
a
,
d
,
c
。
用于定义分区的每个值列表包含3个相同顺序的值;
也就是说,每个值列表元组的形式为(
INT
,
INT
,
CHAR(3)
),其对应于由列中使用的数据类型
a
,
d
和
c
(按该顺序)。
将行放入分区是通过比较要插入的与该
COLUMNS
子句
中的列列表匹配的行
的元组
与
子句中使用的元组
VALUES LESS THAN
来定义表的分区来确定的。
因为我们比较元组(即列表或值集)而不是标量值,所以
VALUES LESS THAN
与
RANGE COLUMNS
分区一起
使用
的语义
与简单
RANGE
分区
的情况略有不同
。
在
RANGE
分区中,生成表达式值的行等于a中的限制值
VALUES LESS THAN
永远不会放在相应的分区中;
但是,使用时
RANGE
COLUMNS
在分区时,有时可能是一个行,其分区列列表的第一个元素的值与
VALUES LESS THAN
值列表中
第一个元素的
值
相等,
以放置在相应的分区中。
考虑
RANGE
此语句创建
的
分区表:
CREATE TABLE r1( 一个INT, b INT ) 按范围划分(a)( 分区p0值小于(5), 分区p1值小于(MAXVALUE) );
如果我们在这个表中插入3行,使得
每一行
的列值
a
都是
5
,那么所有3行都存储在分区中,
p1
因为
a
列值在每种情况下都不小于5,正如我们通过对其执行正确的查询所看到的那样。
INFORMATION_SCHEMA.PARTITIONS
表:
MySQL的>INSERT INTO r1 VALUES (5,10), (5,11), (5,12);
查询OK,3行受影响(0.00秒) 记录:3个重复:0个警告:0 mysql>SELECT PARTITION_NAME,TABLE_ROWS
- >FROM INFORMATION_SCHEMA.PARTITIONS
- >WHERE TABLE_NAME = 'r1';
+ ---------------- + ------------ + | PARTITION_NAME | TABLE_ROWS | + ---------------- + ------------ + | p0 | 0 | | p1 | 3 | + ---------------- + ------------ + 2行(0.00秒)
现在考虑一个类似的表
rc1
,它使用
RANGE COLUMNS
两个列的分区
a
并
b
在
COLUMNS
子句中
引用
,如下所示创建:
CREATE TABLE rc1( 一个INT, b INT ) 按范围栏划分(a,b)( 分区p0值小于(5,12), PARTITION p3值小于(MAXVALUE,MAXVALUE) );
如果我们插入一模一样的行成
rc1
,因为我们刚刚插入
r1
,行的分布是完全不同的:
MySQL的>INSERT INTO rc1 VALUES (5,10), (5,11), (5,12);
查询OK,3行受影响(0.00秒) 记录:3个重复:0个警告:0 mysql>SELECT PARTITION_NAME,TABLE_ROWS
- >FROM INFORMATION_SCHEMA.PARTITIONS
- >WHERE TABLE_NAME = 'rc1';
+ -------------- + ---------------- + ------------ + | TABLE_SCHEMA | PARTITION_NAME | TABLE_ROWS | + -------------- + ---------------- + ------------ + | p | p0 | 2 | | p | p1 | 1 | + -------------- + ---------------- + ------------ + 2行(0.00秒)
这是因为我们比较行而不是标量值。
我们可以比较插入的行值和
VALUES THAN LESS
THAN
用于
p0
在表中
定义分区
的
子句
的限制行值
rc1
,如下所示:
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排(0.00秒)
2个元组
(5,10)
并且
(5,11)
评估为小于
(5,12)
,因此它们存储在分区中
p0
。
由于5不小于5且12不小于12,
(5,12)
因此认为不小于
(5,12)
,并且存储在分区中
p1
。
SELECT
前面示例中
的
语句也可以使用显式行构造函数编写,如下所示:
选择行(5,10)<行(5,12),行(5,11)<行(5,12),行(5,12)<行(5,12);
有关在MySQL中使用行构造函数的更多信息,请参见 第13.2.11.5节“行子查询” 。
对于
RANGE COLUMNS
仅使用单个分区列
分区的表,分区中
的行的存储与分区的等效表的存储相同
RANGE
。
以下
CREATE TABLE
语句创建
RANGE COLUMNS
使用1分区列
分区的表
:
CREATE TABLE rx( 一个INT, b INT ) 按范围栏划分(a)( 分区p0值小于(5), 分区p1值小于(MAXVALUE) );
如果我们插入的行
(5,10)
,
(5,11)
和
(5,12)
到这个表中,我们可以看到,他们的位置是一样的,因为它是表
r
,我们创建和早期填充:
MySQL的>INSERT INTO rx VALUES (5,10), (5,11), (5,12);
查询OK,3行受影响(0.00秒) 记录:3个重复:0个警告:0 mysql>SELECT PARTITION_NAME,TABLE_ROWS
- >FROM INFORMATION_SCHEMA.PARTITIONS
- >WHERE TABLE_NAME = 'rx';
+ -------------- + ---------------- + ------------ + | TABLE_SCHEMA | PARTITION_NAME | TABLE_ROWS | + -------------- + ---------------- + ------------ + | p | p0 | 0 | | p | p1 | 3 | + -------------- + ---------------- + ------------ + 2行(0.00秒)
还可以创建分区的表,
RANGE COLUMNS
其中在连续的分区定义中重复一列或多列的限制值。
只要用于定义分区的列值元组严格增加,就可以执行此操作。
例如,以下每个
CREATE
TABLE
语句都有效:
CREATE TABLE rc2( 一个INT, b INT ) 按范围栏划分(a,b)( 分区p0值小于(0,10), 分区p1值小于(10,20), 分区p2值小于(10,30), PARTITION p3值小于(MAXVALUE,MAXVALUE) ); CREATE TABLE rc3( 一个INT, b INT ) 按范围栏划分(a,b)( 分区p0值小于(0,10), 分区p1值小于(10,20), 分区p2值小于(10,30), 分区p3值低于(10,35), 分区p4值不到(20,40), PARTITION p5值小于(MAXVALUE,MAXVALUE) );
以下语句也会成功,即使乍一看它也不会成功,因为列的限制值为
b
25表示分区
p0
,20表示分区
p1
,列的限制值为
c
100表示分区
p1
,50表示分区
p2
:
CREATE TABLE rc4( 一个INT, b INT, c INT ) 按范围栏划分(a,b,c)( 分区p0值小于(0,25,50), 分区p1值小于(10,20,100), 分区p2值小于(10,30,50) PARTITION p3值小于(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排(0.00秒)
如果
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值必须严格增加每个分区的值);
当您收到此类错误时,可以通过
在列列表之间
进行
“
小于
”
比较
来推断出哪些分区定义无效
。
在这种情况下,问题在于分区的定义,
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排(0.00秒)
使用时,也可以
MAXVALUE
在多个
VALUES LESS
THAN
子句中
显示同一列
RANGE
COLUMNS
。
但是,连续分区定义中各列的限制值应该增加,应该定义的分区不应超过
MAXVALUE
所有列值的上限,并且此分区定义应出现在
PARTITION ...
VALUES LESS THAN
子句
列表的最后
。
此外,您不能
MAXVALUE
在多个分区定义中将第一列用作限制值。
如前所述,
RANGE
COLUMNS
分区
也可以
使用非整数列作为分区列。
(有关
这些内容的完整列表,
请参见
第23.2.3节“列分区”
。)考虑
employees
使用以下语句创建的
名为
(未分区)的表:
CREATE TABLE员工( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), 雇用DATE NOT NULL DEFAULT'1970-01-01', 分隔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), 雇用DATE NOT NULL DEFAULT'1970-01-01', 分隔DATE NOT NULL DEFAULT'9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL ) 按栏分区(lname)( 分区p0值小于('g'), 分区p1值小于('m'), 分区p2值小于('t'), 分区p3值小于(MAXVALUE) );
或者,您可以
employees
通过执行以下
ALTER
TABLE
语句,
使用此方案对先前创建
的
表进行分区
:
ALTER TABLE员工按范围列分区(lname)( 分区p0值小于('g'), 分区p1值小于('m'), 分区p2值小于('t'), 分区p3值小于(MAXVALUE) );
由于不同的字符集和排序规则具有不同的排序顺序,因此
RANGE COLUMNS
当使用字符串列作为分区列时,使用
的字符集和排序规则可能会影响由
给定行
分区的表的哪个分区
。
此外,在创建此类表之后更改给定数据库,表或列的字符集或排序规则可能会导致更改行的分布方式。
例如,在使用区分大小写的排序规则时,
'and'
请先排序
'Andersen'
,但在使用不区分大小写的排序规则时,反之亦然。
有关MySQL如何处理字符集和排序规则的信息,请参阅 第10章, 字符集,排序规则,Unicode 。
类似地,您可以使
employees
表格按照以下方式进行分区:基于使用
ALTER
TABLE
此处显示
的
语句
雇用相应员工的十年,每行存储在多个分区之一中
:
ALTER TABLE员工按范围列分区(租用)( 分区p0值不到('1970-01-01'), 分区p1值低于('1980-01-01'), 分区p2值低于('1990-01-01'), 分区p3价值低于('2000-01-01'), 分区p4价值低于('2010-01-01'), 分区p5值小于(MAXVALUE) );
有关
语法
的其他信息
,
请参见
第13.1.20节“CREATE TABLE语法”
PARTITION BY RANGE COLUMNS
。
MySQL 8.0提供了对
LIST
COLUMNS
分区的
支持
。
这是
LIST
分区的
一种变体,
它允许使用多个列作为分区键,并将整数类型以外的数据类型列用作分区列;
您可以使用字符串类型
DATE
和
DATETIME
列。
(有关
COLUMNS
分区列的
允许数据类型的更多信息
,请参见
第23.2.3节“
列
分区”
。)
假设您的业务在12个城市拥有客户,出于销售和营销目的,您将组织到3个城市的4个区域,如下表所示:
区域 | 城市 |
---|---|
1 | Oskarshamn,Högsby,Mönsterås |
2 | Vimmerby,Hultsfred,Västervik |
3 | Nässjö,Eksjö,Vetlanda |
4 | Uppvidinge,Alvesta,Växjo |
通过
LIST COLUMNS
分区,您可以为客户数据创建一个表,该表根据客户所在城市的名称将行分配给与这些区域对应的4个分区中的任何一个,如下所示:
CREATE TABLE customers_1( first_name VARCHAR(25), last_name VARCHAR(25), street_1 VARCHAR(30), street_2 VARCHAR(30), city VARCHAR(15), 更新日期 ) 列表栏(城市)划分( PARAGETION 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') );
与分区依据一样
RANGE COLUMNS
,您不需要在
COLUMNS()
子句中
使用表达式
将列值转换为整数。
(事实上,不允许使用除列名以外的表达式
COLUMNS()
。)
也可以使用
DATE
和
DATETIME
列,如以下示例所示,它使用与
customers_1
前面所示
的
表
相同的名称和列
,但使用
LIST COLUMNS
基于
renewal
列的
分区
将行存储在4个分区之一中,具体取决于2010年2月的一周客户的帐户计划续订:
CREATE TABLE customers_2( first_name VARCHAR(25), last_name VARCHAR(25), street_1 VARCHAR(30), street_2 VARCHAR(30), city VARCHAR(15), 更新日期 ) 列表栏划分(续期)( 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') );
这有效,但如果涉及的日期数量变得非常大,则定义和维护变得很麻烦;
在这种情况下,采用
RANGE
或
RANGE COLUMNS
分割
通常更为实际
。
在这种情况下,由于我们希望用作分区键的
DATE
列
是
列,我们使用
RANGE COLUMNS
分区,如下所示:
CREATE TABLE customers_3( first_name VARCHAR(25), last_name VARCHAR(25), street_1 VARCHAR(30), street_2 VARCHAR(30), city VARCHAR(15), 更新日期 ) 按范围栏划分(续期)( 分数pWeek_1的价值低于('2010-02-09'), 分数pWeek_2的价值低于('2010-02-15'), 分数pWeek_3价值低于('2010-02-22'), 分数pWeek_4价值低于('2010-03-01') );
有关 更多信息 , 请参见 第23.2.3.1节“RANGE COLUMNS分区” 。
此外(与
RANGE COLUMNS
分区一样),您可以在
COLUMNS()
子句中
使用多个列
。
有关
语法
的其他信息
,
请参见
第13.1.20节“CREATE TABLE语法”
PARTITION BY LIST COLUMNS()
。
分区
HASH
主要用于确保在预定数量的分区之间均匀分布数据。
使用范围或列表分区时,必须明确指定应存储给定列值或列值的哪个分区;
通过散列分区,您可以自行决定此决策,并且只需要根据要散列的列值和分区表要分区的分区数指定列值或表达式。
要使用分区对表进行
HASH
分区,必须在
CREATE
TABLE
语句后
附加
一个
子句,其中
是一个返回整数的表达式。
这可以只是列的名称,其类型是MySQL的整数类型之一。
此外,您最有可能想要使用
,其中
是一个正整数,表示要将表分成的分区数。
PARTITION BY HASH
(
expr
)expr
PARTITIONS
num
num
为简单起见,以下示例中的表不使用任何键。 您应该知道,如果表具有任何唯一键,则此表的分区表达式中使用的每个列都必须是每个唯一键的一部分,包括主键。 有关 更多信息 , 请参见 第23.6.1节“对键,主键和唯一键进行分区” 。
以下语句创建一个在
store_id
列
上使用散列的表,
并分为4个分区:
CREATE TABLE员工( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), 雇用DATE NOT NULL DEFAULT'1970-01-01', 分隔DATE NOT NULL DEFAULT'9999-12-31', job_code INT, store_id INT ) PARASHTION by HASH(store_id) PARTITIONS 4;
如果不包含
PARTITIONS
子句,则分区数默认为
1
;
使用
PARTITIONS
后面没有数字
的
关键字会导致语法错误。
您还可以使用返回整数的SQL表达式
expr
。
例如,您可能希望根据雇用员工的年份进行分区。
这可以如下所示完成:
CREATE TABLE员工( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), 雇用DATE NOT NULL DEFAULT'1970-01-01', 分隔DATE NOT NULL DEFAULT'9999-12-31', job_code INT, store_id INT ) 哈希分居(年(雇用)) PARTITIONS 4;
expr
必须返回非常量非随机整数值(换句话说,它应该是变化的但是确定性的),并且不得包含
第23.6节“分区的限制和限制”中
所述的任何禁止的构造
。
您还应该记住,每次插入或更新(或可能删除)行时都会评估此表达式;
这意味着非常复杂的表达式可能会导致性能问题,尤其是在执行一次影响大量行的操作(例如批量插入)时。
最有效的散列函数是在单个表列上操作的函数,其值随列值一致地增加或减少,因为这允许 在分区范围上 “ 修剪 ” 。 也就是说,表达式越接近它所基于的列的值,MySQL就越有效地使用表达式进行散列分区。
例如,where
date_col
是一个类型的列
DATE
,然后表达式
TO_DAYS(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
xc
理论上,对于涉及多个列值的表达式,修剪也是可能的,但是确定哪个表达式是合适的可能是非常困难和耗时的。 因此,不特别推荐使用涉及多列的散列表达式。
当
PARTITION BY HASH
被使用时,存储引擎确定哪个分区
num
分区使用基于表达式的结果的模量。
换句话说,对于给定的表达式
expr
,存储记录的分区是分区号
N
,其中
。
假设该表
定义如下,因此它有4个分区:
N
=
MOD(expr
,
num
)t1
CREATE TABLE t1(col1 INT,col2 CHAR(5),col3 DATE) 哈希分区(年份(col3)) PARTITIONS 4;
如果您在
t1
其
col3
值中
插入记录
'2005-09-15'
,则存储它的分区将按如下方式确定:
MOD(YEAR( '2005-09-01'),4) = MOD(2005,4) = 1
MySQL 8.0还支持
HASH
称为
线性散列
的
分区
变体,
它使用更复杂的算法来确定插入分区表中的新行的位置。
有关
此算法的说明,
请参见
第23.2.4.1节“线性哈希分区”
。
每次插入或更新记录时,都会评估用户提供的表达式。 它也可能 - 取决于具体情况 - 在删除记录时进行评估。
MySQL还支持线性散列,它不同于常规散列,因为线性散列使用线性二次幂算法,而常规散列使用散列函数值的模数。
从语法上讲,线性散列分区和常规散列之间的唯一区别是
LINEAR
在
PARTITION
BY
子句中
添加了
关键字
,如下所示:
CREATE TABLE员工( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), 雇用DATE NOT NULL DEFAULT'1970-01-01', 分隔DATE NOT NULL DEFAULT'9999-12-31', job_code INT, store_id INT ) 线性哈希分区(年(雇用)) PARTITIONS 4;
给出的表达式
expr
,其中,所述记录被存储在使用线性散列分区是分区号
N
从间
num
隔板,其中
N
,根据以下算法导出:
找到大于2的下一个幂
num
。
我们称之为这个价值
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)
假设
t1
使用此语句创建使用线性哈希分区并具有6个分区的表:
CREATE TABLE t1(col1 INT,col2 CHAR(5),col3 DATE) 线性哈希分区(年份(col3)) 分数6;
现在假设您要将两个记录插入到
t1
具有
col3
列值
'2003-04-14'
和
'1998-10-19'
。
第一个的分区号确定如下:
V
= POWER(2,CEILING(LOG(2,6)))= 8N
=年('2003-04-14')和(8-1) = 2003&7 = 3 (3> = 6为FALSE:记录存储在分区#3中)
存储第二条记录的分区数量如下所示:
V
= 8N
=年('1998-10-19')和(8-1) = 1998年和7年 = 6 (6> = 6为TRUE:需要额外步骤)N
= 6&((8/2) - 1) = 6&3 = 2 (2> = 6为FALSE:记录存储在分区#2中)
线性哈希分区的优点是分区的添加,删除,合并和拆分要快得多,这在处理包含极大量(兆兆字节)数据的表时非常有用。 缺点是与使用常规散列分区获得的分布相比,数据不太可能在分区之间均匀分布。
按密钥分区类似于通过散列进行分区,除了散列分区采用用户定义的表达式之外,密钥分区的散列函数由MySQL服务器提供。
NDB Cluster
MD5()
用于此目的;
对于使用其他存储引擎的表,服务器使用自己的内部散列函数,该函数基于与之相同的算法
PASSWORD()
。
语法规则
CREATE TABLE ... PARTITION BY
KEY
类似于创建由哈希分区的表的
规则
。
这里列出了主要差异:
KEY
使用而不是
HASH
。
KEY
仅获取零个或多个列名称的列表。
用作分区键的任何列必须包含表的主键的部分或全部,如果表有一个。
如果没有将列名指定为分区键,则使用表的主键(如果有)。
例如,以下
CREATE
TABLE
语句在MySQL 8.0中有效:
创建表k1( id INT NOT NOT PRIMARY KEY, 名称VARCHAR(20) ) PARTITION BY KEY() PARTITIONS 2;
如果没有主键但有唯一键,则唯一键用于分区键:
创建表k1( id INT NOT NULL, 名称VARCHAR(20), 独特的钥匙(id) ) PARTITION BY KEY() PARTITIONS 2;
但是,如果未将唯一键列定义为
NOT NULL
,则前一个语句将失败。
在这两种情况下,分区键都是
id
列,即使它未显示在表的输出
SHOW CREATE
TABLE
或
PARTITION_EXPRESSION
列中
INFORMATION_SCHEMA.PARTITIONS
。
与其他分区类型的情况不同,用于分区的列
KEY
不限于整数或
NULL
值。
例如,以下
CREATE
TABLE
语句有效:
CREATE TABLE tm1( s1 CHAR(32)PRIMARY KEY ) 按键分区(s1) 分数10;
前面的语句将
不会
是有效的,将被指定不同的分区类型。
(在这种情况下,简单地使用
PARTITION BY
KEY()
也是有效的,并且具有相同的效果
PARTITION BY KEY(s1)
,因为
s1
是表的主键。)
有关此问题的其他信息,请参见 第23.6节“分区的限制和限制” 。
使用
NDB
存储引擎的
表
被隐式分区
KEY
,再次使用表的主键作为分区键。
如果NDB Cluster表没有显式主键,
则
存储引擎为每个NDB Cluster表
生成
的
“
隐藏
”
主键
NDB
将用作分区键。
如果为
NDB
表
定义显式分区方案
,则表必须具有显式主键,并且分区表达式中使用的任何列都必须是此键的一部分。
但是,如果表使用
“
空
”
分区表达式(即
PARTITION BY
KEY()
没有列引用),则不需要显式主键。
您可以使用
ndb_desc
实用程序(使用该
-p
选项)
观察此分区
。
对于键分区表,您不能执行
ALTER TABLE DROP PRIMARY KEY
,因为这样做会生成错误
ERROR 1466(HY000):表中未找到分区函数的字段列表中的字段
。
这对于被分区的NDB Cluster表来说不是问题
KEY
;
在这种情况下,使用
“
隐藏
”
主键作为表的新分区键
重新组织
表。
请参见
第22章,
MySQL NDB Cluster 8.0
。
也可以通过线性键对表进行分区。 这是一个简单的例子:
CREATE TABLE tk( col1 INT NOT NULL, col2 CHAR(5), col3 DATE ) 线性键分区(col1) PARTITIONS 3;
所述
LINEAR
关键字对相同的效果
KEY
分隔因为它在
HASH
分区,使用权力-的二个一个算法,而不是模运算而导出的分区号。
有关
此算法及其含义的说明,
请参见
第23.2.4.1节“线性HASH分区”
。
子
分区
-
也称为
复合分区
- 是分区表中每个分区的进一步划分。
请考虑以下
CREATE
TABLE
声明:
CREATE TABLE ts(id INT,购买日期) 按比例分区(年(购买)) HASH的子公司(TO_DAYS(已购买)) SUBPARTITIONS 2( 分数p0值低于(1990), 分区p1值小于(2000), 分区p2值小于MAXVALUE );
表
ts
有3个
RANGE
分区。
这些分区-中的每一个
p0
,
p1
和
p2
-is进一步分成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,购买日期) 按比例分区(年(购买)) HASH的消息(TO_DAYS(购买))( 分数p0值低于(1990)( SUBPARTITION s0, SUBPARTITION s1 ) 分区p1值低于(2000)( SUBPARTITION s2, SUBPARTITION s3 ) 分区p2值低于MAXVALUE( SUBPARTITION s4, SUBPARTITION s5 ) );
这里列出了一些注释的句法项:
每个分区必须具有相同数量的子分区。
如果
SUBPARTITION
在分区表的
任何分区
上
显式定义任何子分区
,则必须全部定义它们。
换句话说,以下语句将失败:
CREATE TABLE ts(id INT,购买日期) 按比例分区(年(购买)) HASH的消息(TO_DAYS(购买))( 分数p0值低于(1990)( SUBPARTITION s0, SUBPARTITION s1 ) 分区p1值小于(2000), 分区p2值低于MAXVALUE( SUBPARTITION s2, SUBPARTITION s3 ) );
即使使用此语句仍然会失败
SUBPARTITIONS 2
。
每个
SUBPARTITION
子句必须包含(至少)子分区的名称。
否则,您可以为子分区设置任何所需选项,或允许其采用该选项的默认设置。
子分区名称在整个表中必须是唯一的。
例如,以下
CREATE
TABLE
语句有效:
CREATE TABLE ts(id INT,购买日期) 按比例分区(年(购买)) HASH的消息(TO_DAYS(购买))( 分数p0值低于(1990)( SUBPARTITION s0, SUBPARTITION s1 ) 分区p1值低于(2000)( SUBPARTITION s2, SUBPARTITION s3 ) 分区p2值低于MAXVALUE( SUBPARTITION s4, SUBPARTITION s5 ) );
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
- >);
查询正常,0行受影响(0.09秒) 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
- >);
查询正常,0行受影响(0.09秒)
您可以
CREATE
TABLE
使用以下查询针对
数据库
中的
PARTITIONS
表
查看这两个
语句
创建的分区
INFORMATION_SCHEMA
:
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行(0.00秒)
(有关此表的更多信息,请参见
第25.17节“INFORMATION_SCHEMA PARTITIONS表”
。)现在让我们使用包含
NULL
在用作分区键的列中的
一行的每一个表填充这些表
,并验证是否插入了行使用一对
SELECT
陈述:
MySQL的>INSERT INTO t1 VALUES (NULL, 'mothra');
查询正常,1行受影响(0.00秒) MySQL的>INSERT INTO t2 VALUES (NULL, 'mothra');
查询正常,1行受影响(0.00秒) MySQL的>SELECT * FROM t1;
+ ------ + -------- + | id | 名字| + ------ + -------- + | NULL | mothra | + ------ + -------- + 1排(0.00秒) MySQL的>SELECT * FROM t2;
+ ------ + -------- + | id | 名字| + ------ + -------- + | NULL | mothra | + ------ + -------- + 1排(0.00秒)
您可以通过重新运行上一个查询
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行(0.01秒)
您还可以通过删除这些分区,然后重新运行
SELECT
语句
来证明这些行存储在每个表的编号最小的分区中
:
MySQL的>ALTER TABLE t1 DROP PARTITION p0;
查询OK,0行受影响(0.16秒) MySQL的>ALTER TABLE t2 DROP PARTITION p0;
查询OK,0行受影响(0.16秒) MySQL的>SELECT * FROM t1;
空集(0.00秒) MySQL的>SELECT * FROM t2;
空集(0.00秒)
(有关更多信息
ALTER TABLE ... DROP
PARTITION
,请参见
第13.1.9节“ALTER TABLE语法”
。)
NULL
也以这种方式处理对使用SQL函数的表达式进行分区。
假设我们使用如下
CREATE
TABLE
语句
定义表
:
CREATE TABLE tndate( id INT, dt DATE ) 按比例分区(年(dt))( 分数p0值低于(1990), 分区p1值小于(2000), 分区p2值小于MAXVALUE );
与其他MySQL函数一样,
YEAR(NULL)
返回
NULL
。
用一排
dt
的列值
NULL
被视为虽然分隔表情评估到低于任何其他值的值,因此被插入到分区
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)
- >);
查询OK,0行受影响(0.01秒) mysql> ERROR 1504(HY000):表没有值为9的分区INSERT INTO ts1 VALUES (9, 'mothra');
mysql> ERROR 1504(HY000):表没有值为NULL的分区INSERT INTO ts1 VALUES (NULL, 'mothra');
只能
插入
具有
c1
介于
0
和
之间
的
值的
行
。
落在这个范围之外,就像数字一样
。
我们可以创建表
并
包含值列表
,如下所示:
8
ts1
NULL
9
ts2
ts3
NULL
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)
- >);
查询OK,0行受影响(0.01秒) 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)
- >);
查询OK,0行受影响(0.01秒)
在定义分区的值列表时,您可以(并且应该)像处理
NULL
任何其他值一样
对待
。
例如,无论是
VALUES IN (NULL)
和
VALUES IN (1, 4, 7, NULL)
是有效的,因为是
VALUES IN (1, NULL, 4, 7)
,
VALUES IN
(NULL, 1, 4, 7)
等。
您可以将具有行
NULL
列
c1
到每个表
ts2
和
ts3
:
MySQL的>INSERT INTO ts2 VALUES (NULL, 'mothra');
查询正常,1行受影响(0.00秒) MySQL的>INSERT INTO ts3 VALUES (NULL, 'mothra');
查询正常,1行受影响(0.00秒)
通过发出适当的查询对
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行(0.01秒)
如本节前面所示,您还可以通过删除这些分区然后执行a来验证用于存储行的分区
SELECT
。
使用HASH和KEY分区处理NULL。
NULL
对于由
HASH
或
分区的表,处理方式有所不同
KEY
。
在这些情况下,产生
NULL
值的
任何分区表达式
都被视为其返回值为零。
我们可以通过检查文件系统对创建分区的表的影响
HASH
并使用包含适当值的记录填充它
来验证此行为
。
假设您有一个
使用以下语句创建
的表
th
(也在
p
数据库中):
mysql>CREATE TABLE th (
- >c1 INT,
- >c2 VARCHAR(20)
- >)
- >PARTITION BY HASH(c1)
- >PARTITIONS 2;
查询正常,0行受影响(0.00秒)
可以使用此处显示的查询查看属于此表的分区:
mysql> SELECT TABLE_NAME,PARTITION_NAME,TABLE_ROWS,AVG_ROW_LENGTH,DATA_LENGTH > FROM INFORMATION_SCHEMA.PARTITIONS > WHERE TABLE_SCHEMA ='p'和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行(0.00秒)
TABLE_ROWS
对于每个分区为0.现在在
th
其
c1
列值为
NULL
0和0的位置
插入两行
,并验证是否插入了这些行,如下所示:
MySQL的>INSERT INTO th VALUES (NULL, 'mothra'), (0, 'gigan');
查询正常,1行受影响(0.00秒) MySQL的>SELECT * FROM th;
+ ------ + --------- + | c1 | c2 | + ------ + --------- + | NULL | mothra | + ------ + --------- + | 0 | 吉甘| + ------ + --------- + 2行(0.01秒)
回想一下,对于任何整数
N
,值
始终为
。
对于由
or
分区的表,将
对此结果进行处理以确定正确的分区为
。
再次
检查
表,我们可以看到两行都插入到分区中
:
NULL MOD
N
NULL
HASH
KEY
0
INFORMATION_SCHEMA.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行(0.00秒)
通过使用
PARTITION BY
KEY
代替
PARTITION BY HASH
表的定义
重复最后一个示例
,您可以验证
NULL
对于这种类型的分区也被视为0。
有许多方法可以使用SQL语句来修改分区表;
可以使用
ALTER
TABLE
语句
的分区扩展来添加,删除,重新定义,合并或拆分现有分区
。
还有一些方法可以获取有关分区表和分区的信息。
我们将在后面的章节中讨论这些主题。
有关使用
RANGE
或
分区的表中的分区管理的信息
LIST
,请参见
第23.3.1节“RANGE和LIST分区的管理”
。
有关管理
HASH
和
KEY
分区
的讨论
,请参见
第23.3.2节“HASH和KEY分区的管理”
。
有关 MySQL 8.0中提供的用于获取有关分区表和分区的信息的机制的讨论, 请参见 第23.3.5节“获取 有关分区的信息”。
有关在分区上执行维护操作的讨论,请参见 第23.3.4节“分区维护” 。
分区表的所有分区必须具有相同数量的子分区; 创建表后,无法更改子分区。
要更改表的分区方案,只需要使用
ALTER
TABLE
带有
partition_options
选项
的
语句,该
选项的语法与
CREATE
TABLE
创建分区表时
使用的语法相同
;
此选项(也)始终以关键字开头
PARTITION
BY
。
假设使用以下
CREATE
TABLE
语句创建按范围分区的表:
CREATE TABLE trb3(id INT,名称VARCHAR(50),购买日期) 按范围划分(年(购买))( 分数p0值低于(1990), 分区p1值低于(1995), 分区p2值低于(2000), 分区p3值低于(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
删除表的分区;
请参见
第13.1.9节“ALTER TABLE语法”
。
只有一个单一的
PARTITION BY
,
ADD
PARTITION
,
DROP PARTITION
,
REORGANIZE PARTITION
,或
COALESCE
PARTITION
子句可以在给定的使用
ALTER
TABLE
说明。
如果您(例如)希望删除分区并重新组织表的剩余分区,则必须在两个单独的
ALTER
TABLE
语句中执行此操作(一个使用
DROP
PARTITION
,然后使用另一个
REORGANIZE PARTITION
)。
您可以使用删除一个或多个所选分区中的所有行
ALTER
TABLE ...
TRUNCATE PARTITION
。
以类似的方式处理范围和列表分区的添加和删除,因此我们将在本节中讨论对这两种分区的管理。 有关使用散列或密钥分区的表的信息,请参见 第23.3.2节“HASH和KEY分区的管理” 。
从通过任意分区的表中删除分区
RANGE
或
LIST
可以使用来实现
ALTER
TABLE
的发言
DROP
PARTITION
选项。
假设你已经创建了一个由范围分区,然后用10条记录使用下面的填充表格
CREATE
TABLE
和
INSERT
报表:
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)
- >);
查询OK,0行受影响(0.28秒) 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');
查询OK,10行受影响(0.05秒) 记录:10个重复:0个警告:0
您可以看到应该将哪些项插入分区
p2
,如下所示:
mysql>SELECT * FROM tr
- >WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';
+ ------ + ------------- + ------------ + | id | 名字| 购买| + ------ + ------------- + ------------ + | 2 | 闹钟| 1997-11-05 | | 10 | 熔岩灯| 1998-12-25 | + ------ + ------------- + ------------ + 2行(0.00秒)
您还可以使用分区选择获取此信息,如下所示:
MySQL的> SELECT * FROM tr PARTITION (p2);
+ ------ + ------------- + ------------ +
| id | 名字| 购买|
+ ------ + ------------- + ------------ +
| 2 | 闹钟| 1997-11-05 |
| 10 | 熔岩灯| 1998-12-25 |
+ ------ + ------------- + ------------ +
2行(0.00秒)
有关 更多信息 , 请参见 第23.5节“分区选择” 。
要删除命名的分区
p2
,请执行以下命令:
MySQL的> ALTER TABLE tr DROP PARTITION p2;
查询正常,0行受影响(0.03秒)
该
NDBCLUSTER
存储引擎不支持
ALTER TABLE ... DROP
PARTITION
。
但是,它确实支持
ALTER
TABLE
本章中描述
的其他与分区相关的扩展
。
请务必记住,
删除分区时,还会删除该分区中存储的所有数据
。
通过重新运行上一个
SELECT
查询
,您可以看到这种情况
:
mysql>SELECT * FROM tr WHERE purchased
- >BETWEEN '1995-01-01' AND '1999-12-31';
空集(0.00秒)
DROP PARTITION
本机分区就地API支持,可以使用
ALGORITHM={COPY|INPLACE}
。
DROP
PARTITION
与
ALGORITHM=INPLACE
存储在该分区删除数据并丢弃分区。
但是,
DROP PARTITION
使用
ALGORITHM=COPY
或
old_alter_table=ON
重建分区表并尝试将数据从已删除的分区移动到具有兼容
PARTITION ... VALUES
定义的
另一个分区
。
将删除无法移动到其他分区的数据。
因此,您必须拥有
DROP
表
的
权限才能
ALTER TABLE ... DROP
PARTITION
在该表上
执行
。
如果希望在保留表定义及其分区方案的同时删除所有分区中的所有数据,请使用该
TRUNCATE
TABLE
语句。
(请参见
第13.1.37节“TRUNCATE TABLE语法”
。)
如果您打算在
不
丢失数据的
情况下
更改表的分区
,请
ALTER
TABLE ... REORGANIZE PARTITION
改用。
参见下面或在
第13.1.9,“ALTER TABLE语法”
,有关的信息
REORGANIZE PARTITION
。
如果现在执行
SHOW
CREATE
TABLE
语句,则可以看到表的分区组成是如何更改的:
MySQL的> SHOW CREATE TABLE tr\G
*************************** 1。排******************** *******
表:tr
创建表:CREATE TABLE`tr`(
`id` int(11)DEFAULT NULL,
`name` varchar(50)DEFAULT NULL,
`purchase` date DEFAULT NULL
)ENGINE = InnoDB DEFAULT CHARSET = latin1
/ *!50100按范围划分(年(购买))
(PARTITION p0 VALUES比(1990)ENGINE = InnoDB少,
分区p1值小于(1995)ENGINE = InnoDB,
分区p3的价值低于(2005)ENGINE = InnoDB,
分区p4的价值低于(2010)ENGINE = InnoDB,
分区p5的价值低于(2015)ENGINE = InnoDB)* /
1排(0.00秒)
当您将新行插入到已更改的表中且
purchased
列值介于
'1995-01-01'
和
'2004-12-31'
包含
之间时
,这些行将存储在分区中
p3
。
您可以按如下方式验证:
MySQL的>INSERT INTO tr VALUES (11, 'pencil holder', '1995-07-12');
查询正常,1行受影响(0.00秒) mysql>SELECT * FROM tr WHERE purchased
- >BETWEEN '1995-01-01' AND '2004-12-31';
+ ------ + ---------------- + ------------ + | id | 名字| 购买| + ------ + ---------------- + ------------ + | 1 | 桌面组织者| 2003-10-15 | | 11 | 笔筒| 1995-07-12 | + ------ + ---------------- + ------------ + 2行(0.00秒) MySQL的>ALTER TABLE tr DROP PARTITION p3;
查询正常,0行受影响(0.03秒) mysql>SELECT * FROM tr WHERE purchased
- >BETWEEN '1995-01-01' AND '2004-12-31';
空集(0.00秒)
ALTER TABLE ... DROP PARTITION
服务器不会报告
因此而从表中删除的行数,
因为它等同于
DELETE
查询。
删除
LIST
分区使用与
删除
分区完全相同的
ALTER TABLE ... DROP PARTITION
语法
RANGE
。
但是,之后对表的使用有一个重要的区别:您不能再向表中插入任何具有定义已删除分区的值列表中包含的值的行。
(有关
示例
,
请参见
第23.2.2节“列表分区”
。)
要将新范围或列表分区添加到先前分区的表,请使用该
ALTER TABLE ... ADD PARTITION
语句。
对于分区的表
RANGE
,可以使用此范围将新范围添加到现有分区列表的末尾。
假设您有一个包含组织成员资格数据的分区表,其定义如下:
CREATE TABLE成员( id INT, fname VARCHAR(25), lname VARCHAR(25), dob DATE ) 按范围划分(年(dob))( 分数p0值低于(1980), 分区p1值低于(1990), 分区p2值小于(2000) );
进一步假设成员的最低年龄是16岁。随着日历接近2015年底,您意识到您将很快接纳2000年(及之后)出生的成员。
您可以修改该
members
表以适应2000年至2010年出生的新成员,如下所示:
ALTER TABLE成员ADD PARTITION(PARTITION p3 VALUES(2010));
对于按范围分区的表,您可以使用仅
ADD PARTITION
将新分区添加到分区列表的高端。
尝试在现有分区之间或之前以这种方式添加新分区会导致错误,如下所示:
MySQL的>ALTER TABLE members
>ADD PARTITION (
>PARTITION n VALUES LESS THAN (1970));
ERROR 1463(HY000):价值必须严格超值» 增加每个分区
您可以通过将第一个分区重新组织为两个新分区来解决这个问题,这两个分区分割它们之间的范围,如下所示:
ALTER TABLE成员 重新划分p0 INTO( 分数n0值不到(1970年), 分数n1值低于(1980) );
使用
SHOW
CREATE TABLE
您可以看到该
ALTER TABLE
语句具有所需的效果:
MySQL的> SHOW CREATE TABLE members\G
*************************** 1。排******************** *******
表:成员
创建表:CREATE TABLE`member`(
`id` int(11)DEFAULT NULL,
`fname` varchar(25)DEFAULT NULL,
`lname` varchar(25)DEFAULT NULL,
`dob`日期DEFAULT NULL
)ENGINE = InnoDB DEFAULT CHARSET = latin1
/ *!50100按范围划分(年(dob))
(PARTITION n0 VALUES比(1970)发动机= InnoDB少,
分区n1值小于(1980)ENGINE = InnoDB,
分区p1值小于(1990)ENGINE = InnoDB,
PARTITION p2 VALUE少于(2000)ENGINE = InnoDB,
分区p3的价值低于(2010)ENGINE = InnoDB)* /
1排(0.00秒)
另请参见 第13.1.9.1节“ALTER TABLE分区操作” 。
您还可以使用
ALTER TABLE ... ADD
PARTITION
向分区的表添加新分区
LIST
。
假设
tt
使用以下
CREATE
TABLE
语句
定义
表:
CREATE TABLE tt( id INT, 数据INT ) 按名单划分(数据)( PAROTION p0 VALUES IN(5,10,15), 分区p1值(6,12,18) );
可以添加在其中具有所述行存储一个新的分区
data
的列值
7
,
14
和
21
如下所示:
更改表格添加分区((7,14,21)中的分区p2值);
请记住,您
无法
添加
LIST
包含已包含在现有分区的值列表中的任何值的新分区。
如果您尝试这样做,将导致错误:
mysqlALTER TABLE tt ADD PARTITION
>>(PARTITION np VALUES IN (4, 8, 12));
ERROR 1465(HY000):同一常数的多重定义» 在列表分区中
由于具有
data
列值的
任何行
12
都已分配给分区
p1
,因此无法在
tt
包含
12
其值列表的
表上创建新分区
。
要实现此目的,您可以删除
p1
,添加
np
一个
p1
带有修改定义
的新内容
。
但是,如前所述,这会导致存储的所有数据丢失
p1
- 而且通常情况下这不是您真正想要做的事情。
另一种解决方案似乎是使用新分区制作表的副本,并使用将数据复制到其中
CREATE
TABLE ...
SELECT ...
,然后删除旧表并重命名新表,但在处理大量数据时这可能非常耗时。
在需要高可用性的情况下,这也可能不可行。
您可以在单个
ALTER TABLE
... ADD PARTITION
语句中
添加多个分区,
如下所示:
CREATE TABLE员工( id INT NOT NULL, fname VARCHAR(50)NOT NULL, lname VARCHAR(50)NOT NULL, 雇用DATE NOT NULL ) 按比例分区(年(雇用))( PARITION p1 VALUES(1991), 分区p2值低于(1996), 分区p3价值低于(2001年), 分区p4值低于(2005) ); ALTER TABLE员工添加分区( 分区p5价值低于(2010年), 分区p6值低于MAXVALUE );
幸运的是,MySQL的分区实现提供了重新定义分区而不会丢失数据的方法。
我们先来看几个涉及
RANGE
分区
的简单示例
。
回想一下
members
现在定义
的
表,如下所示:
MySQL的> SHOW CREATE TABLE members\G
*************************** 1。排******************** *******
表:成员
创建表:CREATE TABLE`member`(
`id` int(11)DEFAULT NULL,
`fname` varchar(25)DEFAULT NULL,
`lname` varchar(25)DEFAULT NULL,
`dob`日期DEFAULT NULL
)ENGINE = InnoDB DEFAULT CHARSET = latin1
/ *!50100按范围划分(年(dob))
(PARTITION n0 VALUES比(1970)发动机= InnoDB少,
分区n1值小于(1980)ENGINE = InnoDB,
分区p1值小于(1990)ENGINE = InnoDB,
PARTITION p2 VALUE少于(2000)ENGINE = InnoDB,
分区p3的价值低于(2010)ENGINE = InnoDB)* /
1排(0.00秒)
假设您想将表示1960年之前出生的成员的所有行移动到单独的分区中。
正如我们已经看到的,这不可能使用
ALTER
TABLE ... ADD PARTITION
。
但是,您可以使用另一个与分区相关的扩展
ALTER
TABLE
来完成此任务:
ALTER TABLE成员REORGANIZE PARTITION n0 INTO( 分数s0值小于(1960), 分数s1值小于(1970) );
实际上,此命令分区拆分
p0
成两个新的分区
s0
和
s1
。
它还
p0
根据两个
PARTITION ... VALUES
...
子句中
s0
包含
的规则将
存储的数据移动
到新分区中
,因此
仅
包含那些
YEAR(dob)
小于1960的
记录,
并
s1
包含那些
YEAR(dob)
大于或等于1960但更少的行。比1970年。
甲
REORGANIZE PARTITION
条款还可以使用用于合并相邻的分区。
您可以反转上一个语句对
members
表的影响,如下所示:
ALTER TABLE成员REORGANIZE PARTITION s0,s1 INTO( 分数p0值小于(1970) );
使用分割或合并分区时不会丢失数据
REORGANIZE PARTITION
。
在执行上面的语句中,MySQL将所有的已存储在分区中的记录
s0
,并
s1
为分区
p0
。
REORGANIZE PARTITION
这里显示了
一般语法
:
ALTER TABLEtbl_name
重新分区partition_list
INTO(partition_definitions
);
这里,
tbl_name
是分区表的名称,
partition_list
是一个逗号分隔的一个或多个要更改的现有分区的名称列表。
partition_definitions
是一个以逗号分隔的新分区定义列表,它遵循与
partition_definitions
使用
的
列表
相同的规则
CREATE
TABLE
。
在使用时,您不限于将多个分区合并为一个分区,或将一个分区拆分为多个分区
REORGANIZE
PARTITION
。
例如,您可以将
members
表的
所有四个分区重新组织
为两个,如下所示:
ALTER TABLE成员REORGANIZE PARTITION p0,p1,p2,p3 INTO( 分数m0值小于(1980), 分数m1值小于(2000) );
您还可以使用
REORGANIZE PARTITION
分区的表
LIST
。
让我们回到向列表分区
tt
表
添加新分区
并失败的问题,因为新分区的值已经存在于其中一个现有分区的值列表中。
我们可以通过添加仅包含非冲突值的分区来处理此问题,然后重新组织新分区和现有分区,以便现在存储在现有分区中的值移动到新分区:
ALTER TABLE tt ADD PARTITION(PARTITION np VALUES IN(4,8)); ALTER TABLE tt重组分区p1,np INTO( PARITION p1 VALUES IN(6,18), (4,8,12)中的分区np值 );
以下是使用
ALTER TABLE ... REORGANIZE PARTITION
重新分区由
RANGE
或
分区的表
时
要
记住的一些要点
LIST
:
PARTITION
用于确定新分区方案
的
选项遵循与
CREATE
TABLE
语句
相同的规则
。
新的
RANGE
分区方案不能有任何重叠范围;
新的
LIST
分区方案不能具有任何重叠的值集。
partition_definitions
列表中
的分区组合
应该考虑与在中命名的组合分区相同的范围或整体值集
partition_list
。
例如,分区
p1
和
p2
一起涵盖1980年至1999年
members
在本节中用作示例
的
表中。
这两个分区的任何重组都应涵盖相同的年份范围。
对于分区的表
RANGE
,您只能重新组织相邻的分区;
你不能跳过范围分区。
例如,您无法
members
使用以...开头的语句
重新组织示例
表,
ALTER TABLE members REORGANIZE PARTITION p0,p2
INTO ...
因为它
p0
涵盖1970年之前
p2
的年份和1990年至1999年的年份,因此这些不是相邻的分区。
(
p1
在这种情况下,
您不能跳过分区
。)
您不能使用
REORGANIZE PARTITION
更改表使用的分区类型(例如,您不能将
RANGE
分区
更改
为
HASH
分区或反之)。
您也不能使用此语句来更改分区表达式或列。
要完成这些任务中的任何一个而不删除和重新创建表,您可以使用
ALTER
TABLE ... PARTITION BY ...
,如下所示:
ALTER TABLE成员 哈希分区(年(dob)) PARTITIONS 8;
对于在分区设置中进行更改,通过散列或按键分区的表彼此非常相似,并且两者在与按范围或列表分区的表的方式上有很多不同。 因此,本节介绍了通过散列或仅按键分区的表的修改。 有关添加和删除按范围或列表分区的表分区的讨论,请参见 第23.3.1节“RANGE和LIST分区的管理” 。
不能删除从由分区表分区
HASH
或
KEY
在你可以从表由划分以同样的方式
RANGE
或
LIST
。
但是,您可以
使用
合并
HASH
或
KEY
分区
ALTER TABLE ... COALESCE
PARTITION
。
假设
clients
包含有关客户端数据
的
表分为12个分区,如下所示创建:
CREATE TABLE客户端( id INT, fname VARCHAR(30), lname VARCHAR(30), 签署日期 ) 哈希分区(月(签名)) PARTITIONS 12;
要将分区数从12减少到8,请执行以下
ALTER
TABLE
语句:
MySQL的> ALTER TABLE clients COALESCE PARTITION 4;
查询OK,0行受影响(0.02秒)
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;
查询正常,0行受影响(0.03秒) MySQL的>ALTER TABLE clients_lk COALESCE PARTITION 4;
查询正常,0行受影响(0.06秒) 记录:0重复:0警告:0
以下
COALESCE PARTITION
数字是要合并到其余部分的分区数 - 换句话说,它是要从表中删除的分区数。
尝试删除多于表中的分区会导致出现如下错误:
MySQL的> ALTER TABLE clients COALESCE PARTITION 18;
错误1478(HY000):无法删除所有分区,请改用DROP TABLE
要将
clients
表
的分区数
从12增加到18,请使用
ALTER TABLE ... ADD PARTITION
如下所示:
ALTER TABLE客户端添加PARTITION PARTITIONS 6;
在MySQL 8.0中,可以使用表来交换表分区或子分区
,其中
是分区表,并且
是
要与未分区表交换
的分区或子
分区
,前提是以下语句为真:
ALTER
TABLE
pt
EXCHANGE PARTITION
p
WITH TABLE
nt
pt
p
pt
nt
表
nt
本身不是分区的。
表
nt
不是临时表。
表的结构
pt
和
nt
其他方面相同。
表不
nt
包含外键引用,并且没有其他表具有引用的任何外键
nt
。
其中没有行
nt
位于分区定义的边界之外
p
。
如果
WITHOUT VALIDATION
使用
此条件不适用
。
对于
InnoDB
表,两个表都使用相同的行格式。
要确定
InnoDB
表
的行格式,请进行
查询
INFORMATION_SCHEMA.INNODB_TABLES
。
nt
没有任何使用该
DATA DIRECTORY
选项的
分区
。
对于
InnoDB
MySQL 8.0.14及更高版本中的表,
此限制已取消
。
除了
ALTER
,
INSERT
和
CREATE
通常需要的权限
ALTER
TABLE
声明,你必须有
DROP
执行权限
ALTER
TABLE ...
EXCHANGE PARTITION
。
您还应该了解以下效果
ALTER
TABLE ...
EXCHANGE PARTITION
:
执行
ALTER
TABLE ... EXCHANGE PARTITION
不会在分区表或要交换的表上调用任何触发器。
AUTO_INCREMENT
交换表中的
任何
列都将重置。
使用时,
IGNORE
关键字无效
ALTER TABLE ... EXCHANGE
PARTITION
。
ALTER TABLE ...
EXCHANGE PARTITION
此处显示了
语法
,其中
pt
是分区表,
p
是要交换的分区(或子分区),是要与之交换
nt
的非分区表
p
:
ALTER TABLEpt
EXCHANGE PARTITIONp
WITH TABLEnt
;
或者,您可以追加
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中支持的任何类型。
假设
e
已使用以下SQL语句创建并填充
分区表
:
创建表e( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30) ) 按范围划分(id)( 分区p0值小于(50), 分区p1值小于(100), 分区p2值小于(150), 分区p3值小于(MAXVALUE) ); 插入电子邮件 (1669,“吉姆”,“史密斯”), (337,“玛丽”,“琼斯”), (16,“弗兰克”,“白色”), (2005年,“琳达”,“黑色”);
现在我们创建一个
e
名为
的非分区副本
e2
。
这可以使用
mysql
客户端完成,如下所示:
MySQL的>CREATE TABLE e2 LIKE e;
查询正常,0行受影响(0.04秒) MySQL的>ALTER TABLE e2 REMOVE PARTITIONING;
查询正常,0行受影响(0.07秒) 记录:0重复:0警告:0
您可以
e
通过查询
INFORMATION_SCHEMA.PARTITIONS
表
来查看表
中的
哪些分区
包含行
,如下所示:
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行(0.00秒)
对于分区
InnoDB
表,行计数给出在
TABLE_ROWS
该列
INFORMATION_SCHEMA.PARTITIONS
表仅是一个估计值在SQL优化使用,并不总是准确的。
交换分区
p0
表
e
与表
e2
,你可以使用
ALTER
TABLE
,如下所示:
MySQL的> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
查询正常,0行受影响(0.04秒)
更确切地说,刚刚发出的语句会导致分区中找到的任何行与表中找到的行交换。
您可以
INFORMATION_SCHEMA.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行(0.00秒)
如果查询表
e2
,您可以看到
现在可以在那里找到
“
缺失
”
行:
MySQL的> SELECT * FROM e2;
+ ---- + ------- ------- + +
| id | fname | lname |
+ ---- + ------- ------- + +
| 16 | 弗兰克| 白色|
+ ---- + ------- ------- + +
1排(0.00秒)
要与分区交换的表不一定必须为空。
为了演示这一点,我们首先在表中插入一个新行
e
,确保
p0
通过选择
id
小于50
的
列值
将该行存储在分区中
,
然后通过查询
PARTITIONS
表
来验证此行
:
MySQL的>INSERT INTO e VALUES (41, "Michael", "Green");
查询OK,1行受影响(0.05秒) 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行(0.00秒)
现在我们再次
使用与
之前
相同的
语句
p0
与表
交换分区
:
e2
ALTER
TABLE
MySQL的> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
查询OK,0行受影响(0.28秒)
以下查询的输出显示存储在分区中
p0
的表行和
e2
发布
ALTER
TABLE
语句
之前
存储在表中的表行
现在已切换位置:
MySQL的>SELECT * FROM e;
+ ------ + ------- ------- + + | id | fname | lname | + ------ + ------- ------- + + | 16 | 弗兰克| 白色| | 1669 | 吉姆| 史密斯| | 337 | 玛丽| 琼斯| | 2005年| 琳达| 黑色| + ------ + ------- ------- + + 4行(0.00秒) 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行(0.00秒) MySQL的>SELECT * FROM e2;
+ ---- + --------- + ------- + | id | fname | lname | + ---- + --------- + ------- + | 41 | 迈克尔| 绿色| + ---- + --------- + ------- + 1排(0.00秒)
您应该记住,在发出
ALTER
TABLE ...
EXCHANGE PARTITION
语句
之前在非分区表中找到的任何行
必须满足它们存储在目标分区中所需的条件。
否则,声明失败。
要查看这是如何发生的,首先
在表
e2
的分区的分区定义的边界之外
插入一行
。
例如,插入一个
列值太大
的行
;
然后,尝试再次与分区交换表:
p0
e
id
MySQL的>INSERT INTO e2 VALUES (51, "Ellen", "McDonald");
查询正常,1行受影响(0.08秒) mysql> ERROR 1707(HY000):找到与分区不匹配的行ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
只有该
WITHOUT VALIDATION
选项
才
允许此操作成功:
MySQL的> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITHOUT VALIDATION;
查询OK,0行受影响(0.02秒)
当分区与包含与分区定义不匹配的行的表交换时,数据库管理员有责任修复不匹配的行,这可以使用
REPAIR
TABLE
或
执行
ALTER
TABLE ... REPAIR PARTITION
。
为了避免在使用具有多行的表交换分区时进行耗时的验证,可以通过附加
WITHOUT
VALIDATION
到
ALTER
TABLE ... EXCHANGE PARTITION
语句
来跳过逐行验证步骤
。
以下示例比较了使用非分区表交换分区时的执行时间与使用和不使用验证之间的差异。
分区表(表
e
)包含两个分区,每个分区有100万行。
删除表e的p0中的行,并将p0与100万行的非分区表交换。
该
WITH
VALIDATION
操作需要0.74秒。
相比之下,该
WITHOUT VALIDATION
操作需要0.01秒。
#在每个分区中创建一个包含100万行的分区表 创建表e( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30) ) 按范围划分(id)( 分区p0值小于(1000001), 分区p1值小于(2000001), ); mysql> SELECT COUNT(*)FROM e; | COUNT(*)| + ---------- + | 2000000 | + ---------- + 1排(0.27秒) #查看每个分区中的行 SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME ='e'; + ---------------- + ------------- + | PARTITION_NAME | TABLE_ROWS | + ---------------- + ------------- + | p0 | 1000000 | | p1 | 1000000 | + ---------------- + ------------- + 2行(0.00秒) #创建一个具有相同结构的非分区表,并用100万行填充它 创建表e2( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30) ); mysql> SELECT COUNT(*)FROM e2; + ---------- + | COUNT(*)| + ---------- + | 1000000 | + ---------- + 1排(0.24秒) #创建另一个具有相同结构的非分区表,并用100万行填充它 创建表e3( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30) ); mysql> SELECT COUNT(*)FROM e3; + ---------- + | COUNT(*)| + ---------- + | 1000000 | + ---------- + 1排(0.25秒) #从表e的p0中删除行 mysql> DELETE FROM e WHERE id <1000001; 查询OK,1000000行受影响(5.55秒) #确认分区p0中没有行 mysql> SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME ='e'; + ---------------- + ------------ + | PARTITION_NAME | TABLE_ROWS | + ---------------- + ------------ + | p0 | 0 | | p1 | 1000000 | + ---------------- + ------------ + 2行(0.00秒) #表e的交换分区p0,表e2'WITH VALIDATION' mysql> ALTER TABLE e EXCHANGE PARTITION p0 with TABLE e2 with VALIDATION; 查询正常,0行受影响(0.74秒) #确认已使用表e2交换分区 mysql> SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME ='e'; + ---------------- + ------------ + | PARTITION_NAME | TABLE_ROWS | + ---------------- + ------------ + | p0 | 1000000 | | p1 | 1000000 | + ---------------- + ------------ + 2行(0.00秒) #再次从表e的p0中删除行 mysql> DELETE FROM e WHERE id <1000001; 查询OK,1000000行受影响(5.55秒) #确认分区p0中没有行 mysql> SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME ='e'; + ---------------- + ------------ + | PARTITION_NAME | TABLE_ROWS | + ---------------- + ------------ + | p0 | 0 | | p1 | 1000000 | + ---------------- + ------------ + 2行(0.00秒) #表e的交换分区p0与表e3'WITHOUT VALIDATION' mysql> ALTER TABLE e EXCHANGE PARTITION p0 with TABLE e3没有验证; 查询OK,0行受影响(0.01秒) #确认已使用表e3交换分区 mysql> SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME ='e'; + ---------------- + ------------ + | PARTITION_NAME | TABLE_ROWS | + ---------------- + ------------ + | p0 | 1000000 | | p1 | 1000000 | + ---------------- + ------------ + 2行(0.00秒)
如果使用包含与分区定义不匹配的行的表交换分区,则数据库管理员有责任修复不匹配的行,这可以使用
REPAIR
TABLE
或
执行
ALTER
TABLE ... REPAIR PARTITION
。
您还可以
使用
语句
将子分区表的子分区(请参见
第23.2.6节“
子分区
”
)与非分区表进行
交换
ALTER
TABLE ...
EXCHANGE PARTITION
。
在下面的示例中,我们首先创建一个
es
由分区
RANGE
和子分区的
KEY
表,按照我们的表填充此表
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)
- >);
查询OK,0行受影响(2.76秒) mysql>INSERT INTO es VALUES
- >(1669, "Jim", "Smith"),
- >(337, "Mary", "Jones"),
- >(16, "Frank", "White"),
- >(2005, "Linda", "Black");
查询OK,4行受影响(0.04秒) 记录:4个重复:0警告:0 MySQL的>CREATE TABLE es2 LIKE es;
查询OK,0行受影响(1.27秒) MySQL的>ALTER TABLE es2 REMOVE PARTITIONING;
查询正常,0行受影响(0.70秒) 记录:0重复:0警告:0
虽然我们在创建表时没有明确命名任何子分区
es
,但我们可以通过
在从该表中选择时
包含
表
的
SUBPARTITION_NAME
列
来获取这些
子分区的
生成名称
,如下所示:
PARTITIONS
INFORMATION_SCHEMA
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行(0.00秒)
以下
ALTER
TABLE
语句将
p3sp0
表
es
中的
子分区
与非
分区
表
进行
交换
es2
:
MySQL的> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2;
查询OK,0行受影响(0.29秒)
您可以通过发出以下查询来验证是否已交换行:
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行(0.00秒) MySQL的>SELECT * FROM es2;
+ ------ + ------- ------- + + | id | fname | lname | + ------ + ------- ------- + + | 1669 | 吉姆| 史密斯| | 337 | 玛丽| 琼斯| | 2005年| 琳达| 黑色| + ------ + ------- ------- + + 3组(0.00秒)
如果表是子分区的,则只能使用未分区的表交换表的子分区(而不是整个分区),如下所示:
mysql> ERROR 1704(HY000):子分区表,使用子分区而不是分区ALTER TABLE es EXCHANGE PARTITION p3 WITH TABLE es2;
表结构以严格的方式进行比较; 分区表和非分区表的列和索引的数量,顺序,名称和类型必须完全匹配。 此外,两个表必须使用相同的存储引擎:
MySQL的>CREATE TABLE es3 LIKE e;
查询OK,0行受影响(1.31秒) MySQL的>ALTER TABLE es3 REMOVE PARTITIONING;
查询OK,0行受影响(0.53秒) 记录:0重复:0警告:0 MySQL的>SHOW CREATE TABLE es3\G
*************************** 1。排******************** ******* 表:es3 创建表:CREATE TABLE`es3`( `id` int(11)NOT NULL, `fname` varchar(30)DEFAULT NULL, `lname` varchar(30)DEFAULT NULL )ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 1排(0.00秒) MySQL的>ALTER TABLE es3 ENGINE = MyISAM;
查询OK,0行受影响(0.15秒) 记录:0重复:0警告:0 mysql> ERROR 1497(HY000):在此版本的MySQL中不允许分区中的处理程序组合ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es3;
可以使用用于此类目的的SQL语句对分区表执行许多表和分区维护任务。
分区表的表维护可以使用语句来实现
CHECK TABLE
,
OPTIMIZE
TABLE
,
ANALYZE
TABLE
,和
REPAIR
TABLE
,被支撑为分区表。
您可以使用许多扩展来
ALTER
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检查分区p1;
此命令将告诉您
p1
表
中分区
中
的数据或索引
t1
是否已损坏。
如果是这种情况,请使用
ALTER
TABLE ... REPAIR PARTITION
修复分区。
通常,
CHECK PARTITION
当分区包含重复键错误时失败。
您可以使用
ALTER
IGNORE TABLE
此选项,在这种情况下,该语句将返回找到重复键冲突的分区中每行的内容。
仅报告表的分区表达式中的列的值。
(缺陷#16900947)
刚刚显示的列表中的每个语句也支持关键字
ALL
代替分区名称列表。
使用
ALL
使语句作用于表中的所有分区。
您还可以使用截断分区
ALTER
TABLE ...
TRUNCATE PARTITION
。
此语句可用于从一个或多个分区中
TRUNCATE
TABLE
删除所有行,其
方式与
从表
中
删除所有行的
方式非常相似
。
ALTER TABLE
...
TRUNCATE PARTITION ALL
截断表中的所有分区。
本节讨论获取有关现有分区的信息,这可以通过多种方式完成。 获得此类信息的方法包括:
使用该
SHOW CREATE TABLE
语句查看创建分区表时使用的分区子句。
使用该
SHOW TABLE STATUS
语句确定表是否已分区。
使用该语句
EXPLAIN
SELECT
查看给定的分区
SELECT
。
从MySQL 8.0.16开始,当对分区表进行插入,删除或更新时,二进制日志会记录有关分区和(如果有)行事件发生的子分区的信息。
即使涉及的表相同,也会为在不同分区或子分区中进行的修改创建新的行事件。
因此,如果事务涉及三个分区或子分区,则会生成三个行事件。
对于更新事件,为
“
之前
”
图像和
“
之后
”
图像
记录分区信息
。
如果指定
-v
或,
则显示分区信息
--verbose
使用
mysqlbinlog
查看二进制日志时的选项
。
仅在使用基于行的日志记录时记录分区信息(
binlog_format=ROW
)。
如本章其他部分所述,
SHOW
CREATE TABLE
在其输出中包含
PARTITION BY
用于创建分区表
的
子句。
例如:
MySQL的> SHOW CREATE TABLE trb3\G
*************************** 1。排******************** *******
表:trb3
创建表:CREATE TABLE`trb3`(
`id` int(11)DEFAULT NULL,
`name` varchar(50)DEFAULT NULL,
`purchase` date DEFAULT NULL
)ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
/ *!50100按范围划分(年(购买))
(PARTITION p0 VALUES比(1990)ENGINE = InnoDB少,
分区p1值小于(1995)ENGINE = InnoDB,
PARTITION p2 VALUE少于(2000)ENGINE = InnoDB,
分区p3值低于(2005)ENGINE = InnoDB)* /
0行(0.00秒)
SHOW TABLE STATUS
除了
Create_options
列包含字符串
之外,分区表
的输出与非
分区表
的输出
相同
partitioned
。
该
Engine
列包含表的所有分区使用的存储引擎的名称。
(
有关此语句的更多信息
,
请参见
第13.7.6.36节“SHOW TABLE STATUS语法”
。)
您还可以从中获取有关分区的信息
INFORMATION_SCHEMA
,其中包含
PARTITIONS
表。
请参见
第25.17节“INFORMATION_SCHEMA PARTITIONS表”
。
可以使用确定给定
SELECT
查询
中涉及分区表的哪些分区
EXPLAIN
。
该
partitions
列
EXPLAIN
输出列出了哪些记录将被查询匹配的分区。
假设
trb1
创建并填充
表
,如下所示:
CREATE TABLE trb1(id INT,名称VARCHAR(50),购买日期) 按范围划分(id) ( 分区p0值小于(3), 分区p1值小于(7), 分区p2值小于(9), 分区p3值低于(11) ); 插入trb1值 (1,'桌面组织者','2003-10-15'), (2,'CD播放器','1993-11-05'), (3,'电视机','1996-03-10'), (4,'书柜','1982-01-10'), (5,'运动自行车','2004-05-09'), (6,'沙发','1987-06-05'), (7,'爆米花机','2001-11-22'), (8,'水族馆','1992-08-04'), (9,'学习桌','1984-09-16'), (10,'熔岩灯','1998-12-25');
您可以查看查询中使用的分区
SELECT * FROM trb1;
,如下所示:
MySQL的> EXPLAIN SELECT * FROM trb1\G
*************************** 1。排******************** *******
id:1
select_type:SIMPLE
表:trb1
分区:p0,p1,p2,p3
类型:全部
possible_keys:NULL
key:NULL
key_len:NULL
ref:NULL
行:10
额外:使用filesort
在这种情况下,搜索所有四个分区。 但是,当查询中添加了使用分区键的限制条件时,您可以看到只扫描包含匹配值的分区,如下所示:
MySQL的> EXPLAIN SELECT * FROM trb1 WHERE id < 5\G
*************************** 1。排******************** *******
id:1
select_type:SIMPLE
表:trb1
分区:p0,p1
类型:全部
possible_keys:NULL
key:NULL
key_len:NULL
ref:NULL
行:10
额外:使用在哪里
EXPLAIN
还提供有关使用的密钥和可能的密钥的信息:
MySQL的>ALTER TABLE trb1 ADD PRIMARY KEY (id);
查询OK,10行受影响(0.03秒) 记录:10个重复:0个警告:0 MySQL的>EXPLAIN SELECT * FROM trb1 WHERE id < 5\G
*************************** 1。排******************** ******* id:1 select_type:SIMPLE 表:trb1 分区:p0,p1 类型:范围 possible_keys:PRIMARY 关键:主要 key_len:4 ref:NULL 行:7 额外:使用在哪里
If
EXPLAIN
用于检查针对非分区表的查询,不会产生错误,但
partitions
列
的值
始终为
NULL
。
所述
rows
的柱
EXPLAIN
的输出显示表中的行的总数。
另请参见 第13.8.2节“EXPLAIN语法” 。
称为
分区修剪
的优化
基于相对简单的概念,可以将其描述为
“
不扫描不存在匹配值的分区
”
。
假设
t1
此语句创建
了分区表
:
CREATE TABLE t1( fname VARCHAR(50)NOT NULL, lname VARCHAR(50)NOT NULL, region_code TINYINT UNSIGNED NOT NULL, dob DATE NOT NULL ) 按范围划分(region_code)( 分区p0值小于(64), 分区p1值小于(128), 分区p2值小于(192), PARTITION p3值比MAXVALUE低 );
假设您希望从以下
SELECT
语句中
获得结果
:
SELECT fname,lname,region_code,dob 从t1 WHERE region_code> 125 AND region_code <130;
这是很容易看出,没有行的哪些应该被返回是在任一分区的
p0
或
p3
;
也就是说,我们只需要在分区中搜索
p1
并
p2
找到匹配的行。
通过限制搜索,可以比查找表中的所有分区花费更少的时间和精力来查找匹配的行。
这种
“
切掉
”
不需要的分区称为
修剪
。
当优化器在执行此查询时可以使用分区修剪时,查询的执行速度可以比针对包含相同列定义和数据的非分区表的相同查询快一个数量级。
只要
WHERE
条件可以减少到以下两种情况之一,
优化器就可以执行修剪
:
partition_column
=
constant
partition_column
IN
(constant1
,
constant2
, ...,
constantN
)
在第一种情况下,优化器只是评估给定值的分区表达式,确定哪个分区包含该值,并仅扫描该分区。
在许多情况下,等号可以与另一个算术比较来代替,包括
<
,
>
,
<=
,
>=
,和
<>
。
BETWEEN
在
WHERE
子句中
使用的一些查询
也可以利用分区修剪。
请参阅本节后面的示例。
在第二种情况下,优化器评估列表中每个值的分区表达式,创建匹配分区的列表,然后仅扫描此分区列表中的分区。
SELECT
,
DELETE
和
UPDATE
语句支持分区修剪。
一个
INSERT
声明还访问每个插入的行只有一个分区;
即使对于分区的表
HASH
或者
KEY
当前未在输出中显示
的表也是如此
EXPLAIN
。
修剪也可以应用于短范围,优化程序可以将其转换为等效的值列表。
例如,在前面的示例中,
WHERE
子句可以转换为
WHERE region_code IN (126, 127, 128,
129)
。
然后,优化器可以确定列表中的前两个值是在分区中找到的
p1
,其余两个值在分区中
p2
,而其他分区不包含相关值,因此不需要搜索匹配的行。
优化器还可以针对
WHERE
涉及使用
RANGE COLUMNS
或
LIST
COLUMNS
分区的
表的多个列上的前述类型的比较的条件
执行修剪
。
只要分区表达式由等式或可以减少到一组等式的范围组成,或者当分区表达式表示增大或减小关系时,就可以应用这种类型的优化。
当分区表达式使用
或
函数
时,
也可以对分区
DATE
或
DATETIME
列
上的表应用修剪
。
当分区表达式使用该
函数
时,也可以对这些表应用修剪
。
YEAR()
TO_DAYS()
TO_SECONDS()
假设
使用此处显示的语句创建
t2
在
DATE
列
上分区的
表
:
创建表t2( fname VARCHAR(50)NOT NULL, lname VARCHAR(50)NOT NULL, region_code TINYINT UNSIGNED NOT NULL, dob DATE NOT NULL ) 按范围划分(年(dob))( 分数d0值小于(1970), 分区d1值低于(1975), 分区d2值低于(1980), 分区d3的价值低于(1985年), 分区d4的价值低于(1990年), 分区d5的价值低于(2000年), 分区d6的价值低于(2005年), 分区d7的值低于MAXVALUE );
以下使用的语句
t2
可以使用分区修剪:
SELECT * FROM t2 WHERE dob ='1982-06-23'; UPDATE t2 SET region_code = 8在'1991-02-15'和'1997-04-25'之间做什么? DELETE FROM t2 WHERE dob> ='1984-06-21'ANDb <='1999-06-21'
在最后一个语句的情况下,优化器也可以如下操作:
找到包含范围低端的分区 。
YEAR('1984-06-21')
产生值
1984
,在分区中找到
d3
。
找到包含范围高端的分区 。
YEAR('1999-06-21')
评估为
1999
,在分区中找到
d5
。
仅扫描这两个分区以及可能位于它们之间的任何分区 。
在这种情况下,这意味着只有分区
d3
,
d4
以及
d5
被扫描。
可以安全地忽略其余分区(并忽略)。
在
对分区表的语句
的
条件中
引用的
无效
DATE
和
DATETIME
值
WHERE
被视为
NULL
。
这意味着诸如
不返回任何值
的查询
(参见Bug#40972)。
SELECT * FROM
partitioned_table
WHERE
date_column
<
'2008-12-00'
到目前为止,我们只查看了使用
RANGE
分区的
示例
,但修剪也可以应用于其他分区类型。
考虑一个分区的表
LIST
,其中分区表达式增加或减少,例如
t3
此处显示
的表
。
(在此示例中,为简洁起见,我们假设
region_code
列限制在1到10之间,包括1和10之间的值。)
创建表t3( fname VARCHAR(50)NOT NULL, lname VARCHAR(50)NOT NULL, region_code TINYINT UNSIGNED NOT NULL, dob DATE NOT NULL ) 按列表分区(region_code)( (1,3)中的分区r0值, 分区r1值((2,5,8), 分区r2值(4,9), 分数r3值(6,7,10) );
对于诸如语句
SELECT * FROM t3 WHERE
region_code BETWEEN 1 AND 3
,优化器确定在其中划分所述值1,2,和3中发现(
r0
和
r1
),并跳过其余的(
r2
和
r3
)。
对于由
HASH
or
[LINEAR] KEY
分区的
表,
在
WHERE
子句
=
对分区表达式中使用的列使用
简单
关系的
情况下,也可以进行分区修剪
。
考虑一个像这样创建的表:
创建表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;
可以修剪将列值与常量进行比较的语句:
更新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和5;
在这两种情况下,该
WHERE
子句都由优化器转换为
WHERE region_code IN
(3, 4, 5)
。
仅当范围大小小于分区数时才使用此优化。 请考虑以下声明:
删除t4 WHERE region_code在4和12之间;
该
WHERE
条款中的范围涵盖9个值(4,5,6,7,8,9,10,11,12),但
t4
只有8个分区。
这意味着
DELETE
无法修剪。
当表被
HASH
or
分区时
[LINEAR] KEY
,修剪只能用于整数列。
例如,此语句不能使用修剪,因为它
dob
是一
DATE
列:
SELECT * FROM t4 WHERE dob> ='2001-04-14'ANDb <='2005-10-15';
但是,如果表在
INT
列
中存储年份值
,则
WHERE year_col >= 2001 AND year_col <=
2005
可以修剪
查询
。
使用提供自动分区的存储引擎的表(如
NDB
MySQL Cluster使用
的
存储引擎)可以在显式分区时进行修剪。
支持显式选择与给定
WHERE
条件
匹配的行的分区和子分区
。
分区选择类似于分区修剪,因为只检查特定分区的匹配,但在两个关键方面有所不同:
要检查的分区由语句的颁发者指定,这与分区修剪不同,后者是自动的。
分区修剪仅适用于查询,而查询和许多DML语句都支持显式选择分区。
此处列出了支持显式分区选择的SQL语句:
本节的其余部分讨论了显式分区选择,因为它通常适用于刚刚列出的语句,并提供了一些示例。
使用
PARTITION
选项
实现显式分区选择
。
对于所有受支持的语句,此选项使用此处显示的语法:
分区(partition_names
)partition_names
:partition_name
,...
此选项始终遵循分区或分区所属的表的名称。
partition_names
是要使用的以逗号分隔的分区或子分区列表。
此列表中的每个名称必须是指定表的现有分区或子分区的名称;
如果找不到任何分区或子分区,则该语句将失败并显示错误(
分区'
partition_name
'不存在
)。
命名的分区和子分区
partition_names
可以按任何顺序列出,并且可以重叠。
使用该
PARTITION
选项时,仅检查列出的分区和子分区的匹配行。
可以在
SELECT
语句中
使用此选项
来确定哪些行属于给定分区。
考虑
employees
使用此处显示的语句
命名
,创建和填充
的分区表
:
SET @@ SQL_MODE =''; CREATE TABLE员工( id INT NOT NOT AUTO_INCREMENT PRIMARY KEY, fname VARCHAR(25)NOT NULL, lname VARCHAR(25)NOT NULL, store_id INT NOT NULL, department_id INT NOT NULL ) 按范围划分(id)( 分区p0值小于(5), 分区p1值小于(10), 分区p2值小于(15), PARTITION p3值比MAXVALUE低 ); 插入员工价值观 ('','Bob','Taylor',3,2),('','Frank','Williams',1,2), ('','Ellen','Johnson',3,4),('','Jim','Smith',2,4), ('','玛丽','琼斯',1,1),('','琳达','黑',2,3), ('','Ed','琼斯',2,1),('','六月','威尔逊',3,1), ('','安迪','史密斯',1,3),('','娄','沃特斯',2,4), ('','吉尔','石',1,4),('','罗杰','怀特',3,2), ('','霍华德','安德鲁斯',1,2),('','弗雷德','戈德伯格',3,3), ('','芭芭拉','布朗',2,3),('','爱丽丝','罗杰斯',2,2), ('','马克','摩根',3,3),('','凯伦','科尔',3,2);
您可以看到哪些行存储在分区中,
p1
如下所示:
MySQL的> SELECT * FROM employees PARTITION (p1);
+ ---- + ------- + -------- + ---------- + --------------- +
| id | fname | lname | store_id | department_id |
+ ---- + ------- + -------- + ---------- + --------------- +
| 5 | 玛丽| 琼斯| 1 | 1 |
| 6 | 琳达| 黑色| 2 | 3 |
| 7 | Ed | 琼斯| 2 | 1 |
| 8 | 六月| 威尔逊| 3 | 1 |
| 9 | 安迪| 史密斯| 1 | 3 |
+ ---- + ------- + -------- + ---------- + --------------- +
5行(0.00秒)
结果与查询获得的结果相同
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 | 吉姆| 史密斯| 2 | 4 | | 11 | 吉尔| 石头| 1 | 4 | + ---- + ------- ------- + + ---------- + --------------- + 2行(0.00秒) mysql>SELECT id, CONCAT(fname, ' ', lname) AS name
- >FROM employees PARTITION (p0) ORDER BY lname;
+ ---- + ---------------- + | id | 名字| + ---- + ---------------- + | 3 | 艾伦约翰逊| | 4 | 吉姆史密斯| | 1 | 鲍勃泰勒| | 2 | Frank Williams | + ---- + ---------------- + 4行(0.06秒) 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行(0.00秒)
使用分区选择的语句可以与使用任何支持的分区类型的表一起使用。
当使用创建表
[LINEAR] HASH
或
[LINEAR] KEY
分区而不是指定分区的名字,MySQL的自动命名分区
p0
,
p1
,
p2
,...
,那里
是分区的数量。
对于子分区不明确命名,MySQL的自动分配到每个分区中的子分区
的名称
,
,
,...
,那里
是子分区的数量。
对此表执行时a
p
N-1
N
p
X
p
X
sp0p
X
sp1p
X
sp2p
X
spM-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
- >);
查询正常,0行受影响(1.14秒) mysql>INSERT INTO employees_sub
#在employees表中重用数据 - >SELECT * FROM employees;
查询OK,18行受影响(0.09秒) 记录:18个重复:0个警告:0 mysql>SELECT id, CONCAT(fname, ' ', lname) AS name
- >FROM employees_sub PARTITION (p2sp1);
+ ---- + --------------- + | id | 名字| + ---- + --------------- + | 10 | Lou Waters | | 14 | 弗雷德戈德堡| + ---- + --------------- + 2行(0.00秒)
您还
PARTITION
可以在
语句
的
SELECT
部分中
使用
选项
INSERT
...
SELECT
,如下所示:
MySQL的>CREATE TABLE employees_copy LIKE employees;
查询OK,0行受影响(0.28秒) mysql>INSERT INTO employees_copy
- >SELECT * FROM employees PARTITION (p2);
查询正常,5行受影响(0.04秒) 记录:5个重复:0个警告:0 MySQL的>SELECT * FROM employees_copy;
+ ---- + -------- + ---------- + ---------- + ------------- - + | id | fname | lname | store_id | department_id | + ---- + -------- + ---------- + ---------- + ------------- - + | 10 | 娄| 沃特世| 2 | 4 | | 11 | 吉尔| 石头| 1 | 4 | | 12 | 罗杰| 白色| 3 | 2 | | 13 | 霍华德| 安德鲁斯| 1 | 2 | | 14 | 弗雷德| 戈德堡| 3 | 3 | + ---- + -------- + ---------- + ---------- + ------------- - + 5行(0.00秒)
分区选择也可以与连接一起使用。 假设我们使用此处显示的语句创建并填充两个表:
CREATE TABLE商店( id INT NOT NOT AUTO_INCREMENT PRIMARY KEY, city VARCHAR(30)NOT NULL ) 哈希分区(id) PARTITIONS 2; INSERT INTO存储VALUES ('','Nambucca'),('','Uranga'), ('','贝林根'),('','格拉夫顿'); CREATE TABLE部门( id INT NOT NOT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30)NOT NULL ) PARTITION BY KEY(id) PARTITIONS 2; 插入部门VALUES ('','销售'),('','客户服务'), ('','交付'),('','会计');
您可以从连接中的任何或所有表中显式选择分区(或子分区,或两者)。
(
PARTITION
用于从给定表中选择分区
的
选项紧跟在表的名称之前,在所有其他选项之前,包括任何表别名。)例如,以下查询获取所有员工的名称,员工ID,部门和城市谁在销售或交付部门工作(分区
p1
中的
departments
在商店表)在任的Nambucca和贝林根(分区的城市
p0
中的
stores
表):
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;
+ ------------- + --------------- + ----------- + ------- ----- + | 员工ID | 名称| 城市| 部门| + ------------- + --------------- + ----------- + ------- ----- + | 14 | 弗雷德戈德堡| 贝林根| 交货| | 5 | 玛丽琼斯| Nambucca | 销售| | 17 | 马克摩根| 贝林根| 交货| | 9 | 安迪史密斯| Nambucca | 交货| | 8 | June Wilson | 贝林根| 销售| + ------------- + --------------- + ----------- + ------- ----- + 5行(0.00秒)
有关MySQL中的连接的一般信息,请参见 第13.2.10.2节“JOIN语法” 。
当该
PARTITION
选项与
DELETE
语句一起
使用时
,仅检查随该选项列出的那些分区(和子分区,如果有的话)是否要删除行。
忽略任何其他分区,如下所示:
MySQL的>SELECT * FROM employees WHERE fname LIKE 'j%';
+ ---- + ------- + -------- + ---------- + --------------- + | id | fname | lname | store_id | department_id | + ---- + ------- + -------- + ---------- + --------------- + | 4 | 吉姆| 史密斯| 2 | 4 | | 8 | 六月| 威尔逊| 3 | 1 | | 11 | 吉尔| 石头| 1 | 4 | + ---- + ------- + -------- + ---------- + --------------- + 3组(0.00秒) mysql>DELETE FROM employees PARTITION (p0, p1)
- >WHERE fname LIKE 'j%';
查询正常,2行受影响(0.09秒) MySQL的>SELECT * FROM employees WHERE fname LIKE 'j%';
+ ---- + ------- ------- + + ---------- + --------------- + | id | fname | lname | store_id | department_id | + ---- + ------- ------- + + ---------- + --------------- + | 11 | 吉尔| 石头| 1 | 4 | + ---- + ------- ------- + + ---------- + --------------- + 1排(0.00秒)
仅
删除
分区中的两行
p0
并
p1
匹配
WHERE
条件。
正如您
SELECT
在第二次运行
时从结果中看到的
那样,表中仍有一行符合
WHERE
条件,但位于不同的分区(
p2
)中。
UPDATE
使用显式分区选择的语句的行为方式相同;
PARTITION
在确定要更新的行时,只考虑选项
引用的分区中
的行,如执行以下语句可以看到:
mysql>UPDATE employees PARTITION (p0)
- >SET store_id = 2 WHERE fname = 'Jill';
查询正常,0行受影响(0.00秒) 匹配行数:0已更改:0警告:0 MySQL的>SELECT * FROM employees WHERE fname = 'Jill';
+ ---- + ------- ------- + + ---------- + --------------- + | id | fname | lname | store_id | department_id | + ---- + ------- ------- + + ---------- + --------------- + | 11 | 吉尔| 石头| 1 | 4 | + ---- + ------- ------- + + ---------- + --------------- + 1排(0.00秒) mysql>UPDATE employees PARTITION (p2)
- >SET store_id = 2 WHERE fname = 'Jill';
查询正常,1行受影响(0.09秒) 匹配的行数:1已更改:1警告:0 MySQL的>SELECT * FROM employees WHERE fname = 'Jill';
+ ---- + ------- ------- + + ---------- + --------------- + | id | fname | lname | store_id | department_id | + ---- + ------- ------- + + ---------- + --------------- + | 11 | 吉尔| 石头| 2 | 4 | + ---- + ------- ------- + + ---------- + --------------- + 1排(0.00秒)
以相同的方式,当
PARTITION
使用时
DELETE
,仅检查分区列表中命名的分区中的行或删除分区列表中的分区。
对于插入行的语句,行为的不同之处在于找不到合适的分区导致语句失败。
这对于两个
INSERT
和
REPLACE
语句
都是如此
,如下所示:
mysql> ERROR 1729(HY000):发现一行不匹配给定的分区集 mysql>INSERT INTO employees PARTITION (p2) VALUES (20, 'Jan', 'Jones', 1, 3);
INSERT INTO employees PARTITION (p3) VALUES (20, 'Jan', 'Jones', 1, 3);
查询正常,1行受影响(0.07秒) mysql> REPLACE INTO员工PARTITION(p0)VALUES(20,'Jan','Jones',3,2); 错误1729(HY000):找到与给定分区集不匹配的行 mysql> REPLACE INTO员工PARTITION(p3)VALUES(20,'Jan','Jones',3,2); 查询正常,2行受影响(0.09秒)
对于使用
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
- >);
查询OK,6行受影响(2.09秒) 记录:6个重复:0个警告:0 MySQL的>SHOW CREATE TABLE employees\G
*************************** 1。排******************** ******* 表:员工 创建表:CREATE TABLE`员工`( `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 PAR BY BY RANGE(id) (PARTITION p0值小于(5)ENGINE = InnoDB, 分区p1值小于(10)ENGINE = InnoDB, 分区p2值小于(15)ENGINE = InnoDB, 分区p3值少于(20)ENGINE = InnoDB, 分区p4值低于(25)ENGINE = InnoDB, PARTITION p5的价值低于MAXVALUE ENGINE = InnoDB)* / 1排(0.00秒) mysql>INSERT INTO employees PARTITION (p3, p4) VALUES
- >(24, 'Tim', 'Greene', 3, 1), (26, 'Linda', 'Mills', 2, 1);
错误1729(HY000):找到与给定分区集不匹配的行 mysql>INSERT INTO employees PARTITION (p3, p4. p5) VALUES
- >(24, 'Tim', 'Greene', 3, 1), (26, 'Linda', 'Mills', 2, 1);
查询正常,2行受影响(0.06秒) 记录:2个重复:0个警告:0
对于
写入多行的
INSERT
语句和
REPLACE
语句,
前面的描述都是正确的
。
对于使用提供自动分区的存储引擎的表,禁用分区选择,例如
NDB
。
本节讨论当前对MySQL分区支持的限制和限制。
禁止的结构。 分区表达式中不允许使用以下结构:
存储过程,存储函数,UDF或插件。
声明变量或用户变量。
有关分区表达式中允许的SQL函数列表,请参见 第23.6.3节“分区与函数相关的限制” 。
算术和逻辑运算符。
算术运算符的应用
+
,
-
以及
*
允许在分区表达式。
但是,结果必须是整数值或
NULL
(除非在
[LINEAR] KEY
分区
的情况下
,如本章其他部分所述;
有关更多信息,
请参见
第23.2节“分区类型”
)。
该位运营商
|
,
&
,
^
,
<<
,
>>
,并
~
没有在分区表达式允许的。
服务器SQL模式。 使用用户定义分区的表不会保留在创建它们时生效的SQL模式。 如本手册其他 部分所述 (请参见 第5.1.11节“服务器SQL模式” ),许多MySQL函数和运算符的结果可能会根据服务器SQL模式而更改。 因此,在创建分区表后随时更改SQL模式可能会导致此类表的行为发生重大更改,并且很容易导致数据损坏或丢失。 出于这些原因, 强烈建议您在创建分区表后永远不要更改服务器SQL模式 。
例子。 以下示例说明了由于服务器SQL模式的更改而导致分区表行为的一些更改:
错误处理。
正如其他地方所讨论的那样,处理
“
特殊
”
值(如零)
NULL
可能因不同服务器SQL模式而不同(请参见
第5.1.11节“服务器SQL模式”
)。
例如,
ERROR_FOR_DIVISION_BY_ZERO
可以影响是否可以将0作为值插入到其分区表达式使用
或
的表中
column
DIV
value
column
MOD
value
.
表可访问性。
有时,服务器SQL模式的更改可能会使分区表无法使用。
CREATE
TABLE
仅当
NO_UNSIGNED_SUBTRACTION
模式
有效时,才能成功执行
以下
语句
:
MySQL的>SELECT @@sql_mode;
+ ------------ + | @@ sql_mode | + ------------ + | | + ------------ + 1排(0.00秒) 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):分区常量超出分区函数域);
MySQL的>SET sql_mode='NO_UNSIGNED_SUBTRACTION';
查询正常,0行受影响(0.00秒) MySQL的>SELECT @@sql_mode;
+ ------------------------- + | @@ sql_mode | + ------------------------- + | NO_UNSIGNED_SUBTRACTION | + ------------------------- + 1排(0.00秒) 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)
- >);
查询OK,0行受影响(0.05秒)
如果
NO_UNSIGNED_SUBTRACTION
在创建后
删除
服务器SQL模式
tu
,则可能无法再访问此表:
MySQL的>SET sql_mode='';
查询正常,0行受影响(0.00秒) mysql> ERROR 1563(HY000):分区常量超出分区函数域 mysql> ERROR 1563(HY000):分区常量超出分区函数域SELECT * FROM tu;
INSERT INTO tu VALUES (20);
另请参见 第5.1.11节“服务器SQL模式” 。
服务器SQL模式也会影响分区表的复制。 主服务器和从服务器上的不同SQL模式可能导致分区表达式的计算方式不同; 这可能导致分区之间的数据分布在给定表的主服务器和从服务器副本中不同,甚至可能导致插入到主服务器上成功的分区表中从服务器上失败。 为获得最佳结果,应始终在主服务器和从服务器上使用相同的服务器SQL模式。
性能考虑因素 以下列表给出了分区操作对性能的一些影响:
文件系统操作。
分区和重新分区操作(例如
ALTER
TABLE
with
PARTITION BY ...
,
REORGANIZE PARTITION
或
REMOVE
PARTITIONING
)取决于其实现的文件系统操作。
这意味着这些操作的速度受诸如文件系统类型和特征,磁盘速度,交换空间,操作系统的文件处理效率以及与服务器处理相关的MySQL服务器选项和变量等因素的影响。
特别是,您应该确保
large_files_support
已启用并且
open_files_limit
设置正确。
涉及的分区和重新分区操作
InnoDB
通过启用可以使表更有效
innodb_file_per_table
。
另请参见 最大分区数 。
表锁。
通常,对表执行分区操作的进程会对表执行写锁定。
这些表中的读数相对不受影响;
一旦分区操作完成,就执行
挂起
INSERT
和
UPDATE
操作。
有关
InnoDB
此限制的特定例外,请参阅
分区操作
。
指标; 分区修剪。 与非分区表一样,正确使用索引可以显着加快对分区表的查询速度。 此外,在这些表上设计分区表和查询以利用 分区修剪 可以显着提高性能。 有关 更多信息 , 请参见 第23.4节“分区修剪” 。
分区表支持索引条件下推。 请参见 第8.2.1.5节“索引条件下推优化” 。
使用LOAD DATA的性能。
在MySQL 8.0中,
LOAD
DATA
使用缓冲来提高性能。
您应该知道缓冲区每个分区使用130 KB内存来实现此目的。
最大分区数。
不使用
NDB
存储引擎
的给定表的最大可能分区数
为8192.此数字包括子分区。
使用
NDB
存储引擎
的表的最大可能用户定义分区数
是根据所使用的NDB Cluster软件的版本,数据节点的数量和其他因素确定的。
有关
更多信息,
请参阅
NDB和用户定义的分区
。
如果在创建具有大量分区(但小于最大值)的表时,您会遇到
来自存储引擎
的错误消息,例如
Got error ...:打开文件时资源不足
,您可能能够解决问题通过增加
open_files_limit
系统变量
的值
。
但是,这取决于操作系统,并且可能在所有平台上都不可能或不可取;
有关更多信息
,
请参见
第B.4.2.17节“找不到文件和类似错误”
。
在某些情况下,由于其他问题,使用大量(数百个)分区也可能不可取,因此使用更多分区不会自动产生更好的结果。
另请参见 文件系统操作 。
分区的InnoDB表不支持外键。
使用
InnoDB
存储引擎的
分区表
不支持外键。
更具体地说,这意味着以下两个陈述是正确的:
InnoDB
使用用户定义的分区
的
表的定义可能不包含外键引用;
没有
InnoDB
可以对其定义包含外键引用的表进行分区。
没有
InnoDB
表定义可能包含对用户分区表的外键引用;
没有
InnoDB
用户定义分区的表可能包含外键引用的列。
刚刚列出的限制范围包括使用
InnoDB
存储引擎的
所有表
。
CREATE
TABLE
和
ALTER
TABLE
不会导致表违反这些限制的语句。
ALTER TABLE ... ORDER BY。
针对分区表运行
的
语句仅导致每个分区内的行排序。
ALTER TABLE ... ORDER BY
column
通过修改主键对REPLACE语句的影响。
在某些情况下(参见
第23.6.1节“分区键,主键和唯一键”
)可能需要修改表的主键。
请注意,如果您的应用程序使用
REPLACE
语句而您执行此操作,则这些语句的结果可能会发生巨大变化。
有关
更多信息和示例,
请参见
第13.2.9节“REPLACE语法”
。
FULLTEXT索引。
分区表不支持
FULLTEXT
索引或搜索。
空间列。
具有空间数据类型的列,例如
POINT
或
GEOMETRY
不能在分区表中使用。
记录表。
无法对日志表进行分区;
ALTER
TABLE ... PARTITION BY ...
这样的表上
的
语句失败并出现错误。
分区键的数据类型。
分区键必须是整数列或解析为整数的表达式。
ENUM
不能使用
使用
列的
表达式
。
列或表达式值也可以是
NULL
;
请参见
第23.2.7节“MySQL分区如何处理NULL”
。
此限制有两个例外:
通过[
LINEAR
]进行
分区时
KEY
,可以使用除
分区键
之外的任何有效MySQL数据类型的列,
TEXT
或者
BLOB
作为分区键,因为内部键散列函数可以从这些类型生成正确的数据类型。
例如,以下两个
CREATE
TABLE
语句有效:
CREATE TABLE tkc(c1 CHAR) 按键分区(c1) PARTITIONS 4; CREATE TABLE tke (c1 ENUM('红色','橙色','黄色','绿色','蓝色','靛蓝','紫罗兰')) 线性键分区(c1) 分数6;
使用
RANGE COLUMNS
或进行
分区时
LIST COLUMNS
,可以使用字符串
DATE
,和
DATETIME
列。
例如,以下每个
CREATE
TABLE
语句都有效:
CREATE TABLE rc(c1 INT,c2 DATE) 按栏数划分(c2)( 分数p0值低于('1990-01-01'), 分区p1的价值低于('1995-01-01'), 分区p2价值低于('2000-01-01'), 分区p3的价值低于('2005-01-01'), 分区p4值小于(MAXVALUE) ); CREATE TABLE lc(c1 INT,c2 CHAR(1)) 列表栏分区(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) );
子查询。
分区键可能不是子查询,即使该子查询解析为整数值或
NULL
。
子分区的问题。
子
分区
必须使用
HASH
或
KEY
分区。
只有
RANGE
和
LIST
分区可能是分区的;
HASH
和
KEY
分区不能分区。
SUBPARTITION BY KEY
要求明确指定子分区列,不同于
PARTITION BY KEY
可以省略的情况(在这种情况下,默认情况下使用表的主键列)。
考虑一下这个语句创建的表:
创建表ts( id INT NOT NOT AUTO_INCREMENT PRIMARY KEY, 名称VARCHAR(30) );
您可以创建一个具有相同列的表
KEY
,使用如下所示的语句进行
分区
:
创建表ts( id INT NOT NOT AUTO_INCREMENT PRIMARY KEY, 名称VARCHAR(30) ) PARTITION BY KEY() PARTITIONS 4;
前面的语句被视为已经像这样编写,表的主键列用作分区列:
创建表ts( id INT NOT NOT AUTO_INCREMENT PRIMARY KEY, 名称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):SQL语法错误; 检查手册);
对应于您的MySQL服务器版本,以便在')附近使用正确的语法 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)
- >);
查询正常,0行受影响(0.07秒)
这是一个已知问题(参见Bug#51470)。
DATA DIRECTORY和INDEX DIRECTORY选项。
忽略
表级
DATA DIRECTORY
和
INDEX
DIRECTORY
选项(请参阅Bug#32091)。
您可以将这些选项用于
InnoDB
表的
各个分区或子分区
。
修复和重建分区表。
该声明
CHECK TABLE
,
OPTIMIZE
TABLE
,
ANALYZE
TABLE
,并
REPAIR
TABLE
支持分区表。
此外,您可以使用
ALTER TABLE ... REBUILD
PARTITION
重建分区表的一个或多个分区;
ALTER TABLE ... REORGANIZE
PARTITION
还会导致重建分区。
有关这两个语句的更多信息
,
请参见
第13.1.9节“ALTER TABLE语法”
。
ANALYZE
,
CHECK
,
OPTIMIZE
,
REPAIR
,和
TRUNCATE
操作与子分区支持。
请参见
第13.1.9.1节“ALTER TABLE分区操作”
。
本节讨论将密钥与主键和唯一键分区的关系。 管理此关系的规则可以表示如下:分区表的分区表达式中使用的所有列必须是表可能具有的每个唯一键的一部分。
换句话说, 表上的每个唯一键必须使用表的分区表达式中的每一列 。 (这也包括表的主键,因为它根据定义是一个唯一键。这个特殊情况将在本节后面讨论。)例如,以下每个表创建语句都是无效的:
CREATE TABLE t1( col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, 独特的钥匙(col1,col2) ) 哈希分区(col3) PARTITIONS 4; 创建表t2( col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, 独特的钥匙(col1), 独特的钥匙(col3) ) 哈希分区(col1 + col3) PARTITIONS 4;
在每种情况下,建议的表将至少有一个唯一键,不包括分区表达式中使用的所有列。
以下每个语句都是有效的,并且表示可以使相应的无效表创建语句起作用的一种方式:
CREATE TABLE t1( col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, 独特的钥匙(col1,col2,col3) ) 哈希分区(col3) PARTITIONS 4; 创建表t2( col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, 独特的钥匙(col1,col3) ) 哈希分区(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)
- > ERROR 1491(HY000):PRIMARY KEY必须包含表格分区功能中的所有列PARTITIONS 4;
该
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;
查询OK,0行受影响(0.05秒)
在这种情况下,建议的分区键
col3
是两个唯一键的一部分,表创建语句成功。
根本无法对下表进行分区,因为无法在分区键中包含属于这两个唯一键的任何列:
创建表t4( col1 INT NOT NULL, col2 INT NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, UNIQUE KEY(col1,col3), 独特的钥匙(col2,col4) );
由于每个主键根据定义是唯一键,因此该限制还包括表的主键(如果有)。 例如,接下来的两个语句无效:
创建表t5( col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, 主键(col1,col2) ) 哈希分区(col3) PARTITIONS 4; 创建表t6( col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, PRIMARY KEY(col1,col3), 独特的钥匙(col2) ) HASH分区(年份(col2)) PARTITIONS 4;
在这两种情况下,主键都不包括分区表达式中引用的所有列。 但是,接下来的两个语句都是有效的:
创建表t7( col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, 主键(col1,col2) ) 哈希分区(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), 独特的钥匙(col2,col1) ) 哈希分区(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)
- >);
查询正常,0行受影响(0.12秒)
可以
t_no_pk
使用以下任一
ALTER
TABLE
语句
添加主键
:
#可能的PK MySQL的>ALTER TABLE t_no_pk ADD PRIMARY KEY(c1);
查询OK,0行受影响(0.13秒) 记录:0重复:0警告:0 #放弃这个PK MySQL的>ALTER TABLE t_no_pk DROP PRIMARY KEY;
查询正常,0行受影响(0.10秒) 记录:0重复:0警告:0 #使用另一种可能的PK MySQL的>ALTER TABLE t_no_pk ADD PRIMARY KEY(c1, c2);
查询正常,0行受影响(0.12秒) 记录:0重复:0警告:0 #放弃这个PK MySQL的>ALTER TABLE t_no_pk DROP PRIMARY KEY;
查询正常,0行受影响(0.09秒) 记录:0重复:0警告:0
但是,下一个语句失败,因为它
c1
是分区键的一部分,但不是建议的主键的一部分:
#失败,错误1503
mysql> ERROR 1503(HY000):PRIMARY KEY必须包含表格分区功能中的所有列ALTER TABLE t_no_pk ADD PRIMARY KEY(c2);
由于
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)
- >);
查询正常,0行受影响(0.08秒)
以下
ALTER
TABLE
语句失败并显示错误,因为该
added
列不是表中任何唯一键的一部分:
mysql>ALTER TABLE np_pk
- >PARTITION BY HASH( TO_DAYS(added) )
- > ERROR 1503(HY000):PRIMARY KEY必须包含表的分区函数中的所有列PARTITIONS 4;
但是,使用
id
分区列
的
列的
此语句
是有效的,如下所示:
mysql>ALTER TABLE np_pk
- >PARTITION BY HASH(id)
- >PARTITIONS 4;
查询OK,0行受影响(0.11秒) 记录:0重复:0警告:0
在这种情况下
np_pk
,唯一可以用作分区表达式一部分的列是
id
;
如果您希望使用分区表达式中的任何其他列对此表进行分区,则必须先修改表,方法是将所需的一列或多列添加到主键,或者完全删除主键。
在MySQL 8.0中,MySQL服务器实际上并不提供分区支持,而是由表存储引擎自己的或本机分区处理程序提供。
在MySQL 8.0中,只有
InnoDB
存储引擎提供本机分区处理程序。
这意味着无法使用任何其他存储引擎创建分区表。
MySQL Cluster的
NDB
存储引擎还提供本机分区支持,但MySQL 8.0目前不支持。
ALTER
TABLE ... OPTIMIZE PARTITION
无法正常使用的分区表
InnoDB
。
使用
ALTER TABLE ... REBUILD PARTITION
和
ALTER TABLE ... ANALYZE PARTITION
,相反,这样的表。
有关更多信息,请参见
第13.1.9.1节“ALTER TABLE分区操作”
。
用户定义的分区和NDB存储引擎(NDB集群)。
分区
KEY
(包括
LINEAR KEY
)是
NDB
存储引擎
支持的唯一分区类型
。
在正常情况下,NDB群集中不可能使用除[
LINEAR
]
之外的任何分区类型创建NDB群集表
KEY
,并且尝试这样做会失败并显示错误。
异常(不用于生产)
:可以通过
new
在NDB Cluster SQL节点上
设置
系统变量
来覆盖此限制
ON
。
如果您选择这样做,您应该知道
[LINEAR] KEY
在生产中不支持
使用分区类型以外的表
。
在这种情况下,您可以创建和使用除
KEY
or
之外的分区类型的表
LINEAR KEY
,但是完全由您自己承担风险
。
可以为
NDB
表
定义的最大分区数
取决于群集中的数据节点和节点组的数量,正在使用的NDB群集软件的版本以及其他因素。
有关
更多信息,
请参阅
NDB和用户定义的分区
。
NDB
表中
每个分区可存储的最大固定大小数据量
为128 TB。
以前,这是16 GB。
CREATE
TABLE
和
ALTER
TABLE
不会导致用户分区
NDB
表不满足以下两个要求之一或两者的
语句
,并且失败并显示错误:
该表必须具有显式主键。
表的分区表达式中列出的所有列都必须是主键的一部分。
例外。
如果
NDB
使用空列列表(即使用
PARTITION BY KEY()
或
PARTITION BY
LINEAR KEY()
)
创建
用户分区
表
,则不需要显式主键。
升级分区表。
执行升级时,
KEY
必须转储并重新加载
分区的表
。
使用
InnoDB
非MySQL
存储引擎的分区表
无法从MySQL 5.7或更早版本升级到MySQL 8.0或更高版本;
您必须从这些表中删除分区,
ALTER TABLE ...
REMOVE PARTITIONING
或者
在升级之前
将它们转换为
InnoDB
使用
ALTER TABLE ...
ENGINE=INNODB
。
有关将
MyISAM
表
转换为的信息
InnoDB
,请参见
第15.6.1.3节“将表从MyISAM转换为InnoDB”
。
本节讨论MySQL分区中的限制,特别是与分区表达式中使用的函数有关。
在分区表达式中只允许使用以下列表中显示的MySQL函数:
FLOOR()
(见
CEILING()和FLOOR()
)
UNIX_TIMESTAMP()
(带
TIMESTAMP
列)
在MySQL 8.0,分区修剪被支撑为
TO_DAYS()
,
TO_SECONDS()
,
YEAR()
,和
UNIX_TIMESTAMP()
功能。
有关
更多信息
,
请参见
第23.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):PARTITION函数返回错误的类型);
带有WEEK说明符的EXTRACT()函数。
函数返回的值
EXTRACT()
用作时
,取决于
系统变量
的值
。
因此,
在将单位指定
为时,
不允许作为分区功能
。
(Bug#54483)
EXTRACT(WEEK FROM
col
)default_week_format
EXTRACT()
WEEK
有关 这些函数的返回类型的更多信息 , 请参见 第12.6.2节“数学函数” ,以及 第11.2节“数值类型” 。