本节提供 MySQL 5.7 中分区的概念性概述。
有关分区限制和功能限制的信息,请参阅第 22.6 节,“分区限制”。
SQL 标准没有提供太多关于数据存储物理方面的指导。SQL 语言本身旨在独立于它所使用的架构、表、行或列的任何数据结构或媒体工作。尽管如此,大多数高级数据库管理系统已经发展出一些方法来确定用于存储特定数据的物理位置,包括文件系统、硬件或两者。在 MySQL 中,
InnoDB
存储引擎长期以来一直支持表空间的概念,甚至在引入分区之前,MySQL 服务器就可以配置为使用不同的物理目录来存储不同的数据库(参见第 8.12.3 节,“使用符号链接”,解释了这是如何完成的)。
分区使这个概念更进一步,它使您能够根据可以根据需要设置的规则在文件系统中分布各个表的部分。实际上,表的不同部分作为单独的表存储在不同的位置。用户选择的数据划分规则称为分区函数,在 MySQL 中可以是模数、针对一组范围或值列表的简单匹配、内部散列函数或线性散列函数。该函数根据用户指定的分区类型进行选择,并将用户提供的表达式的值作为其参数。此表达式可以是列值、作用于一个或多个列值的函数,或者一组一个或多个列值,具体取决于所使用的分区类型。
在、 和 [ ]分区的情况下RANGE
,
分区列的值被传递给分区函数,该函数返回一个整数值,表示应存储该特定记录的分区号。此函数必须是非常量和非随机的。它可能不包含任何查询,但可以使用在 MySQL 中有效的 SQL 表达式,只要该表达式返回一个或一个整数
,这样
LIST
LINEAR
HASH
NULL
intval
-MAXVALUE <= intval <= MAXVALUE
(MAXVALUE
用于表示所讨论整数类型的最小上限。
-MAXVALUE
表示最大下限。)
对于 [ LINEAR
] KEY
、
RANGE COLUMNS
和LIST
COLUMNS
分区,分区表达式由一个或多个列的列表组成。
对于 [ LINEAR
]KEY
分区,分区函数由 MySQL 提供。
有关允许的分区列类型和分区函数的更多信息,请参阅第 22.2 节,“分区类型”,以及第 13.1.18 节,“CREATE TABLE 语句”,其中提供了分区语法描述和其他示例。有关分区函数限制的信息,请参阅 第 22.6.3 节,“与函数相关的分区限制”。
这被称为水平分区——也就是说,一个表的不同行可以分配给不同的物理分区。MySQL 5.7 不支持 垂直分区,将一张表的不同列分配给不同的物理分区。目前没有计划将垂直分区引入 MySQL。
有关确定您的 MySQL 服务器二进制文件是否支持用户定义分区的信息,请参阅 第 22 章,分区。
要创建分区表,您可以使用 MySQL 服务器支持的大多数存储引擎;MySQL 分区引擎在单独的层中运行,并且可以与其中任何一个进行交互。在 MySQL 5.7 中,同一个分区表的所有分区必须使用相同的存储引擎;例如,您不能将MyISAM
一个分区InnoDB
用于另一个分区。但是,没有什么可以阻止您在同一个 MySQL 服务器甚至同一个数据库中为不同的分区表使用不同的存储引擎。
MySQL 分区不能与
MERGE
、CSV
或
FEDERATED
存储引擎一起使用。
可以使用KEY
或进行
分区,但使用此存储引擎的表不支持其他类型的用户定义分区。此外,
使用用户定义分区的表必须有一个明确的主键,表的分区表达式中引用的任何列都必须是主键的一部分。但是,如果用于创建或修改用户分区表的or
语句的or子句中没有列出任何列
,则该表不需要具有显式主键。有关更多信息,请参阅
第 21.2.7.1 节,“NDB Cluster 中不符合 SQL 语法”。
LINEAR
KEY
NDB
NDB
PARTITION BY KEY
PARTITION BY
LINEAR KEY
CREATE
TABLE
ALTER
TABLE
NDB
要为分区表使用特定的存储引擎,[STORAGE] ENGINE
只需像对非分区表一样使用该选项。但是,您应该记住,在语句
中使用任何分区选项之前[STORAGE] ENGINE
,需要列出(和其他表选项) 。此示例显示如何创建一个表,该表按哈希分区为 6 个分区并使用存储引擎:
CREATE
TABLE
InnoDB
CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
ENGINE=INNODB
PARTITION BY HASH( MONTH(tr_date) )
PARTITIONS 6;
每个PARTITION
子句都可以包含一个
[STORAGE] ENGINE
选项,但在 MySQL 5.7 中这不起作用。
分区适用于一个表的所有数据和索引;您不能仅对数据进行分区而不对索引进行分区,反之亦然,也不能仅对表的一部分进行分区。
可以使用用于创建分区表
的语句的子句的
DATA DIRECTORY
和
INDEX DIRECTORY
选项将
每个分区的数据和索引分配到特定目录。PARTITION
CREATE TABLE
DATA DIRECTORY
Windows 上的表
的INDEX
DIRECTORY
单个分区或子分区不支持。MyISAM
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 (name)
);
因为键pk
和uk
没有共同的列,所以没有列可用于分区表达式。在这种情况下可能的解决方法包括将name
列添加到表的主键、将id
列添加到uk
,或者干脆完全删除唯一键。有关详细信息,请参阅
第 22.6.1 节,“分区键、主键和唯一键”。
此外,MAX_ROWS
和
MIN_ROWS
可用于分别确定每个分区中可存储的最大行数和最小行数。有关这些选项的更多信息,
请参阅第 22.3 节,“分区管理” 。
该MAX_ROWS
选项也可用于创建具有额外分区的 NDB Cluster 表,从而允许更大的哈希索引存储。有关更多信息,请参阅
DataMemory
数据节点配置参数的文档以及
第 21.2.2 节,“NDB 集群节点、节点组、片段副本和分区”。
此处列出了分区的一些优点:
分区使得在一个表中存储的数据比在单个磁盘或文件系统分区中存储的数据更多成为可能。
通过删除仅包含该数据的分区(或多个分区),通常可以轻松地从分区表中删除失去用处的数据。相反,在某些情况下,通过添加一个或多个新分区来专门存储该数据,可以极大地促进添加新数据的过程。
由于满足给定
WHERE
子句的数据可以仅存储在一个或多个分区上,这会自动从搜索中排除任何剩余分区,因此可以极大地优化某些查询。由于在创建分区表后可以更改分区,因此您可以重新组织数据以增强在首次设置分区方案时可能不经常使用的频繁查询。这种排除不匹配分区(以及它们包含的任何行)的能力通常称为 分区修剪。有关详细信息,请参阅第 22.4 节,“分区修剪”。此外,MySQL 支持查询的显式分区选择。例如, 只选择分区中 符合 条件的
SELECT * FROM t PARTITION (p0,p1) WHERE c < 5
那些行。在这种情况下,MySQL 不会检查表的任何其他分区;当您已经知道要检查哪个或哪些分区时,这可以大大加快查询速度。数据修改语句 、、、、、、、 也 支持 分区 选择。 有关更多信息和示例,请参阅这些语句的描述。p0
p1
WHERE
t
DELETE
INSERT
REPLACE
UPDATE
LOAD DATA
LOAD XML