学习地址:
MySQL :: MySQL 5.7 Reference Manual :: 22 Partitioning
分区简介
查看当前数据库是否支持分区
SELECT
PLUGIN_NAME AS NAME,
PLUGIN_VERSION AS Version,
PLUGIN_STATUS AS STATUS
FROM
INFORMATION_SCHEMA.PLUGINS
WHERE
PLUGIN_TYPE = 'STORAGE ENGINE';
分区优势:
-
分区可以在一个表中存储比单个磁盘或文件系统分区上更多的数据。
-
通过删除仅包含该数据的分区(或多个分区),通常可以轻松地从分区表中删除失去其用途的数据。相反,在某些情况下,通过添加一个或多个新分区来专门存储该数据,可以极大地促进添加新数据的过程。
-
由于满足给定
WHERE
子句的数据只能存储在一个或多个分区上,因此可以极大地优化某些查询,这会自动从搜索中排除任何剩余的分区。因为在创建分区表后可以更改分区,所以您可以重新组织数据以增强在首次设置分区方案时可能不经常使用的频繁查询。这种排除不匹配分区(以及它们包含的任何行)的能力通常称为 分区修剪此外,MySQL 支持查询的显式分区选择。例如, 仅选择分区中与 条件匹配的SELECT * FROM t PARTITION (p0,p1) WHERE c < 5那些行 。在这种情况下,MySQL 不会检查 table 的任何其他分区;当您已经知道要检查哪个或哪些分区时,这可以大大加快查询速度。
分区类型
范围分区
这种类型的分区根据落在给定范围内的列值将行分配给分区。按范围分区的表的分区方式是,每个分区都包含分区表达式值位于给定范围内的行。范围应该是连续的但不重叠,并且使用 VALUES LESS THAN
运算符定义。
给定表
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
);
按照store_id来进行范围分区
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
按照separated年来分区
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY RANGE ( YEAR(separated) ) (
PARTITION p0 VALUES LESS THAN (1991),
PARTITION p1 VALUES LESS THAN (1996),
PARTITION p2 VALUES LESS THAN (2001),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
时间分区是一个经常使用的分区方式,一些日常时间函数
MySQL :: MySQL 5.7 Reference Manual :: 12.7 Date and Time Functions
LIST 分区
类似于按 分区RANGE
,除了分区是根据与一组离散值中的一个匹配的列来选择的。
按照store_id结合特定的规则来list分区,使特定的id集合落入同一张表
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY LIST(store_id) (
PARTITION pNorth VALUES IN (3,5,6,9,17),
PARTITION pEast VALUES IN (1,2,10,11,19,20),
PARTITION pWest VALUES IN (4,12,13,14,18),
PARTITION pCentral VALUES IN (7,8,15,16)
);
哈希分区
使用这种类型的分区,分区是根据用户定义的表达式返回的值来选择的,该表达式对要插入到表中的行中的列值进行操作。该函数可以包含任何在 MySQL 中有效的表达式,它产生一个非负整数值。
表达式
PARTITION BY HASH (
expr
) PARTITIONS
num
expr
必须返回一个非常量、非随机的整数值(换句话说,它应该是可变的但具有确定性)
如果不包含PARTITIONS
子句,则分区数默认为1
.
按照store_id来哈希分区 分为4个分区
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;
将日期取年然后哈希四个分区
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4;
Key分区
这种类型的分区类似于按 分区 HASH
,除了只提供一个或多个要评估的列,并且 MySQL 服务器提供自己的散列函数。这些列可以包含非整数值,因为 MySQL 提供的散列函数保证整数结果,而不管列数据类型如何。
KEY
只接受零个或多个列名的列表。用作分区键的任何列都必须包含表的部分或全部主键(如果表有主键)。
如果没有将列名指定为分区键,则使用表的主键(如果有的话)。
CREATE TABLE k1 (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(20)
)
PARTITION BY KEY()
PARTITIONS 2;
如果没有主键但有唯一键,则唯一键用于分区键:
CREATE TABLE k1 (
id INT NOT NULL,
name VARCHAR(20),
UNIQUE KEY (id)
)
PARTITION BY KEY()
PARTITIONS 2;
也可以指定特殊的列
CREATE TABLE tm1 (
s1 CHAR(32) PRIMARY KEY
)
PARTITION BY KEY(s1)
PARTITIONS 10;
子分区、复合分区
先按年分为三个分区,然后每个分区又按照天分为两个分区
CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) )
SUBPARTITIONS 2 (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN MAXVALUE
);
另一种写法
CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0,
SUBPARTITION s1
),
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITION s2,
SUBPARTITION s3
),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s4,
SUBPARTITION s5
)
);
NULL值处理
分区键如果出现空值,不同分区形式对于数据插入处理方式不同。特别是生产环境添加新的字段之后又同时添加索引
使用 RANGE 分区处理 NULL。 如果将行插入到分区表中, RANGE
使得用于确定分区的列值为NULL
,则该行将插入到最低分区中。
使用 LIST 分区处理 NULL。 LIST
当且 仅当它的一个 分区NULL
是使用包含 NULL。一句话,NULL值只能插入到NULL分区中。
使用 HASH 和 KEY 分区处理 NULL。 NULL
对于由HASH
或 分区的表,处理方式略有不同KEY
。在这些情况下,任何产生NULL
值的分区表达式都被视为其返回值为零。NULL值作为0索引分区。
分区管理
RANGE 和 LIST 分区的管理
创建分区
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 )
);
删除分区
ALTER TABLE tr DROP PARTITION p2;
删除分区时,同时会删除该分区对应的数据
清空分区
truncate table tr.p3;
数据和索引文件被删除并重新创建,分区定义 ( .par
) 文件不受影响
添加分区
CREATE TABLE members (
id INT,
fname VARCHAR(25),
lname VARCHAR(25),
dob DATE
)
PARTITION BY RANGE( YEAR(dob) ) (
PARTITION p0 VALUES LESS THAN (1980),
PARTITION p1 VALUES LESS THAN (1990),
PARTITION p2 VALUES LESS THAN (2000)
);
分区拆分
ALTER TABLE members REORGANIZE PARTITION p0 INTO ( PARTITION n0 VALUES LESS THAN (1970), PARTITION n1 VALUES LESS THAN (1980) );
分区合并
ALTER TABLE members REORGANIZE PARTITION n0, n1 INTO (
PARTITION p0 VALUES LESS THAN (1980)
);
HASH 和 KEY 分区的管理
CREATE TABLE clients ( id INT, fname VARCHAR(30), lname VARCHAR(30), signed DATE ) PARTITION BY HASH( MONTH(signed) ) PARTITIONS 12;
分区数量减少
ALTER TABLE clients COALESCE PARTITION 4;
分区数量增加
ALTER TABLE clients ADD PARTITION PARTITIONS 6;
分区维护
重建分区
删除存储在分区中的所有记录,然后重新插入它们具有相同的效果。这对于碎片整理很有用。
ALTER TABLE t1 REBUILD PARTITION p0, p1;
优化分区
在给定分区上使用OPTIMIZE PARTITION
等同于在该分区上运行CHECK PARTITION
、ANALYZE PARTITION
和REPAIR PARTITION
ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;
分析分区
读取并存储分区的密钥分布
ALTER TABLE t1 ANALYZE PARTITION p3;
修复分区
修复损坏的分区
ALTER TABLE t1 REPAIR PARTITION p0,p1;
检查分区
分区中的数据或索引t1
是否已损坏
ALTER TABLE trb3 CHECK PARTITION p1;
INFORMATION_SCHEMA 分区表
提供有关表分区的信息。此表中的每一行对应于分区表的一个单独分区或子分区
CREATE TEMPORARY TABLE `PARTITIONS` (
`TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
`TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
`TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
`PARTITION_NAME` varchar(64) DEFAULT NULL,
`SUBPARTITION_NAME` varchar(64) DEFAULT NULL,
`PARTITION_ORDINAL_POSITION` bigint(21) unsigned DEFAULT NULL,
`SUBPARTITION_ORDINAL_POSITION` bigint(21) unsigned DEFAULT NULL,
`PARTITION_METHOD` varchar(18) DEFAULT NULL,
`SUBPARTITION_METHOD` varchar(12) DEFAULT NULL,
`PARTITION_EXPRESSION` longtext,
`SUBPARTITION_EXPRESSION` longtext,
`PARTITION_DESCRIPTION` longtext,
`TABLE_ROWS` bigint(21) unsigned NOT NULL DEFAULT '0',
`AVG_ROW_LENGTH` bigint(21) unsigned NOT NULL DEFAULT '0',
`DATA_LENGTH` bigint(21) unsigned NOT NULL DEFAULT '0',
`MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
`INDEX_LENGTH` bigint(21) unsigned NOT NULL DEFAULT '0',
`DATA_FREE` bigint(21) unsigned NOT NULL DEFAULT '0',
`CREATE_TIME` datetime DEFAULT NULL,
`UPDATE_TIME` datetime DEFAULT NULL,
`CHECK_TIME` datetime DEFAULT NULL,
`CHECKSUM` bigint(21) unsigned DEFAULT NULL,
`PARTITION_COMMENT` varchar(80) NOT NULL DEFAULT '',
`NODEGROUP` varchar(12) NOT NULL DEFAULT '',
`TABLESPACE_NAME` varchar(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
该PARTITIONS表具有以下列:
-
TABLE_CATALOG
表所属目录的名称。该值始终为def
。 -
TABLE_SCHEMA
表所属的架构(数据库)的名称。 -
TABLE_NAME
包含分区的表的名称。 -
PARTITION_NAME
分区的名称。 -
PARTITION_METHOD
值RANGE
,LIST
,HASH
,LINEAR HASH
,KEY
, 或LINEAR KEY
; -
PARTITION_DESCRIPTION
此列用于 RANGE 和 LIST 分区。对于RANGE
分区,它包含在分区VALUES LESS THAN
子句中设置的值,可以是整数或MAXVALUE
. 对于LIST
分区,此列包含分区VALUES IN
子句中定义的值,该子句是逗号分隔的整数值列表。对于PARTITION_METHOD
不是RANGE
or 的分区LIST
,此列始终为NULL
。 -
TABLE_ROWS
分区中的表行数。 对于分区InnoDB表,列中给出的行数TABLE_ROWS
只是 SQL 优化中使用的估计值,可能并不总是准确的。 -
AVG_ROW_LENGTH
存储在此分区或子分区中的行的平均长度,以字节为单位。
DATA_LENGTH
这与除以 相同TABLE_ROWS
。 -
DATA_LENGTH
此分区或子分区中存储的所有行的总长度,以字节为单位;即分区或子分区中存储的总字节数。
-
MAX_DATA_LENGTH
此分区或子分区中可以存储的最大字节数。
-
INDEX_LENGTH
此分区或子分区的索引文件的长度,以字节为单位。
-
DATA_FREE
分配给分区或子分区但未使用的字节数。
-
CREATE_TIME
创建分区或子分区的时间。
-
UPDATE_TIME
上次修改分区或子分区的时间。
-
CHECK_TIME
上次检查此分区或子分区所属的表的时间。
-
CHECKSUM
校验和值(如果有);否则
NULL
。 -
PARTITION_COMMENT
评论的文本,如果分区有的话。如果不是,则此值为空。
分区注释的最大长度定义为 1024 个字符,
PARTITION_COMMENT
列的显示宽度也是 1024 个字符,以匹配此限制。
分区修剪优化
WHERE
只要条件可以简化为以下两种情况之一, 优化器就可以执行优化 :
-
partition_column
=constant
-
partition_column
IN (constant1
,constant2
, ...,constantN
)
在第一种情况下,优化器只计算给定值的分区表达式,确定哪个分区包含该值,然后只扫描这个分区。在许多情况下,等号可以替换为另一种算术比较,包括<
、>
、 <=
、>=
和 <>
。BETWEEN
在子句中使用的一些查询 WHERE
也可以利用分区修剪。请参阅本节后面的示例。
在第二种情况下,优化器为列表中的每个值评估分区表达式,创建匹配分区列表,然后仅扫描此分区列表中的分区。
分区选择
sql中显示的指定查询分区
语法:PARTITION (partition_names)
SELECT * FROM employees PARTITION (p1);
分区限制
1.分区表的分区表达式中使用的所有列必须是该表可能具有的每个唯一键的一部分。表上的每个唯一键都必须使用表分区表达式中的每一列。
2. InnoDB外键和 MySQL 分区不兼容。分区 InnoDB
表不能有外键引用,也不能有外键引用的列。InnoDB
不能对具有外键或被外键引用的表进行分区。InnoDB
不支持对子分区使用多个磁盘。(目前只有 支持 MyISAM
。)
3.分区对于DDL的影响
-
CREATE VIEW不会导致任何锁定。
-
ALTER TABLE ... EXCHANGE PARTITION修剪锁;只有交换的表和交换的分区被锁定。
ALTER TABLE ... TRUNCATE PARTITION修剪锁;只有要清空的分区被锁定。
此外,ALTER TABLE 语句在表级别采用元数据锁。
4.分区表达式中仅允许使用以下列表中显示的 MySQL 函数: