我们已经讨论过了分表优化适应的场合,分表优化属于“显示”优化,这种优化我们看得见,但日后修改及维护非常麻烦,MySQL就为我们提供了“分区”的概念,类似于我们的系统分区。
分区允许根据设置为任意大小的规则,跨文件系统分配单个表的多个部分。实际上,表的不同部分在不同的位置被存储为单独的表。用户所选择的、实现数据分割的规则被称为分区函数,这在MySQL中它可以是模数,或者是简单的匹配一个连续的数值区间或数值列表,或者是一个内部HASH函数,或一个线性HASH函数。借助 MySQL 分区,可以提高数据库性能,并简化超大型数据库的管理。MySQL 支持水平分区,可将数据库中的行拆分为更小的数据集,随后分布于多个目录和磁盘中。
分区有助于提高查询性能,这是因为当特定的操作只需访问较小的数据集而非一整个大型表。此外,还可以将分区后的表指派到不同的物理驱动器上,这样便可在同时访问多个分区时减少物理 I/O 争用,磁盘raid是一个很好的选择。
分区还有助于简化数据管理。举例来说,我们可以从分区的表中删除特定分区,同时确保其余分区完整无缺(与对整个表做批量删除操作后会产生碎片截然不同)。整个过程无需我们干预。
引用一张MySQL官方的图片可以很好的认识分区
我们可以通过多种分区方法来准确控制数据的分区方式:
范围分区(Range Partitioning)
每一个分区中都包含能对应到一组特定值(范围)的一些行,适用的情景包括常用查询直接以对表进行分区的列为条件,或者需要通过删除表来快速删除旧数据时。比如:按主键id范围来分区,1-10000,10001-20000...等等。
列示分区(List Partitioning)
列示分区与范围分区类似。主要区别在于,列示分区模式允许根据我们预先定义的值列表(而不是一系列连续值构成的范围)来对数据进行划分。比如:一个类型字段“type”,它有100种值,我们可以利用列示分区来分成100个区,这种分区具有局限性,维护起来也较范围分区麻烦。
列分区(Columns Partitioning)
列分区支持在分区键中使用多个列。这些列将用于决定在分区中放置那些行并在分区修剪操作中决定在哪那些行要清掉。列分区是对范围分区和列表分区的扩展。
散列分区(Hash Partition)
散列分区主要用于确保在预先设定数量的多个分区上平均分配数据。在范围分区和列表分区中,您必须明确指定特定列值存储在哪个分区中。而在散列分区中,MySQL 将自动为您做这个工作,您只需根据执行散列的列值和分区表所划分成的分区数量来指定列值或表达式即可。比如:有10个已建好的分区,当新插入数据时根据算法会分配到指定分区上去。
线性散列分区是列表分区的一个变体,它使用一种更复杂的数据分区算法,可在处理 TB 级表时大幅提高添加、删除、合并和拆分分区的速度。
键分区(Key Partitioning)
键分区与散列分区类似,MySQL 通过一个系统生成的散列键来确保数据的平均分布,而不是使用用户定义的表达式做散列分区。键分区支持在分区中使用各种类型的列数据。
子分区(Sub-Partitioning)
子分区支持对分区表中各个分区进行进一步划分,专用于处理超大型表,可将数据和索引分布在许多服务器和磁盘上。
通过上述介绍认识到分区给我们带来的好处显而易见,可以大大提升查询速度、平均分配I/O访问的瓶颈限制、查询总数时只需返回每个分区总数之和就可、可以更有规律的组织数据的存储等等。
那么下面就开始我们的分区操作吧!
我们首先要确定我们的MySQL版本是否支持分区,在MySQL命令符状态下输入:
mysql> SHOW VARIABLES LIKE '%partition%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| have_partitioning | YES |
+-------------------+-------+
1 row in set (0.00 sec)
看到结果显示为“YES”,表示支持分区。
对于创建了分区的表,可以使用你的MySQL 服务器所支持的任何存储引擎;MySQL 分区引擎在一个单独的层中运行,并且可以和任何这样的层进行相互作用。在MySQL中,同一个分区表的所有分区必须使用同一个存储引擎;例如,不能对一个分区使用MyISAM,而对另一个使用InnoDB。但是,这并不妨碍在同一个 MySQL 服务器中,甚至在同一个数据库中,对于不同的分区表使用不同的存储引擎。
输入下列命令查看MySQL支持的存储引擎:
mysql> SHOW PLUGINS;
+------------+----------+----------------+---------+---------+
| Name | Status | Type | Library | License |
+------------+----------+----------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ndbcluster | ACTIVE | STORAGE ENGINE | NULL | GPL |
+------------+----------+----------------+---------+---------+
11 rows in set (0.00 sec)
存储引擎版本:
mysql> SELECT PLUGIN_NAME as Name,PLUGIN_VERSION as Version,PLUGIN_STATUS as Status FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_TYPE='STORAGE ENGINE';
+--------------------+---------+--------+
| Name | Version | Status |
+--------------------+---------+--------+
| binlog | 1.0 | ACTIVE |
| CSV | 1.0 | ACTIVE |
| MEMORY | 1.0 | ACTIVE |
| MRG_MYISAM | 1.0 | ACTIVE |
| MyISAM | 1.0 | ACTIVE |
| PERFORMANCE_SCHEMA | 0.1 | ACTIVE |
| BLACKHOLE | 1.0 | ACTIVE |
| ARCHIVE | 3.0 | ACTIVE |
| InnoDB | 5.6 | ACTIVE |
| partition | 1.0 | ACTIVE |
+--------------------+---------+--------+
10 rows in set (0.00 sec)
Range分区:
分区之前必须注意几点:
1.Range分区的列只接受列名,不接受表达式;
2.Range分区的列可以是多个列;
3.Range分区的列并不会只限于整数,字符串,时间,日期等等,你可以做很多选择;
4.Range分区的列是基于元组(连续的值),而不是单纯比较数值。
开始建立分区:
还是拿前面的用户表为例子,在SQL命令行状态下输入:
CREATE TABLE `USER` (
`USR_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户编号',
`CUSTORM_ID` int(11) DEFAULT NULL COMMENT '客户代码',
`STATUS` int(11) DEFAULT NULL COMMENT '用户状态',
`CREATE_DATE` datetime DEFAULT NULL COMMENT '创建日期',
`ACTIV_CODE` varchar(32) DEFAULT NULL COMMENT '激活码',
`USR_TYPE` int(11) DEFAULT NULL COMMENT '用户类型',
PRIMARY KEY (`USR_ID`)
)
PARTITION BY RANGE (USR_ID) (
PARTITION partition_0 VALUES LESS THAN (10000),
PARTITION partition_1 VALUES LESS THAN (20000)
);
上半部分为建表语句,下半部分为创建分区语句,注意他们中间没有“;”间隔,意思很容易理解:USR_ID小于1万的分区为partition_0,1万--2万之间分区为partition_1。这种分区方法比较简单,是针对一个字段来分区的,下面我们看看多个字段的分区方法是怎样的。
在分区之前需要查看你的MySQL版本,只有版本在5.5以上才支持以下分区方法,这点需要注意以下,查看MySQL版本的命令:
SELECT VERSION();
我们同样在SQL状态下输入命令:
CREATE TABLE `USER` (
`USR_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户编号',
`CUSTORM_ID` int(11) DEFAULT NULL COMMENT '客户代码',
`STATUS` int(11) DEFAULT NULL COMMENT '用户状态',
`CREATE_DATE` datetime DEFAULT NULL COMMENT '创建日期',
`ACTIV_CODE` varchar(32) DEFAULT NULL COMMENT '激活码',
`USR_TYPE` int(11) DEFAULT NULL COMMENT '用户类型',
PRIMARY KEY (`USR_ID`)
)
PARTITION BY RANGE COLUMNS(USR_ID,USR_TYPE,YEAR(CREATE_DATE)) (
PARTITION partition_0 VALUES LESS THAN (10000,10,1990),
PARTITION partition_1 VALUES LESS THAN (20000,20,2000),
PARTITION partition_2 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
);
这样就可以很轻松的对多个字段进行分区操作了,"MAXVALUE"很好理解,表示最大的可能的整数值,同理于Integer.MAX_VALUE。YEAR(CREATE_DATE)) 就是将日期转换成“年”,这种用法也比较普遍。
CREATE TABLE `USER` (
`USR_ID` INT(11) NOT NULL AUTO_INCREMENT COMMENT '用户编号',
`CUSTORM_ID` INT(11) DEFAULT NULL COMMENT '客户代码',
`STATUS` INT(11) DEFAULT NULL COMMENT '用户状态',
`CREATE_DATE` DATETIME DEFAULT NULL COMMENT '创建日期',
`ACTIV_CODE` VARCHAR(32) DEFAULT NULL COMMENT '激活码',
`USR_TYPE` INT(11) DEFAULT NULL COMMENT '用户类型',
PRIMARY KEY (`USR_ID`)
)
ENGINE=INNODB PARTITION BY RANGE (USR_ID) (
PARTITION partition_0 VALUES LESS THAN (10000),
PARTITION partition_1 VALUES LESS THAN (20000),
PARTITION partition_2 VALUES LESS THAN (MAXVALUE)
);
PARTITION BY RANGE (USR_ID)(
PARTITION partition_0 VALUES LESS THAN (10000) ENGINE=INNODB,
PARTITION partition_1 VALUES LESS THAN (20000) ENGINE=INNODB,
PARTITION partition_2 VALUES LESS THAN (MAXVALUE) ENGINE=INNODB
);
ALTER TABLE USER PARTITION BY RANGE(USR_ID,USR_TYPE,CREATE_DATE)(
PARTITION partition_0 VALUES LESS THAN (10000,10,'1999-12-31'),
PARTITION partition_1 VALUES LESS THAN (20000,20,'2999-12-31'),
PARTITION partition_2 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
);
#删除分区
ALTER TABLE USER DROP PARTITION partition_0; //删除partition_0分区,你也可以指定任意分区
#删除分区数据
ALTER TABLE USER TRUNCATE PARTITION partition_0,partition_1; //删除partition_0,partition_1两个分区下的数据
ALTER TABLE USER TRUNCATE PARTITION ALL; //删除全部数据
#重定义分区(包括重命名分区,转移数据;合并分区)
ALTER TABLE USER REORGANIZE PARTITION partition_0,partition_1 INTO (PARTITION p1 VALUES LESS THAN(20000),
PARTITION p2 VALUES LESS THAN(40000));
#重建分区
ALTER TABLE USER REBUILD PARTITION partition_0/ALL; //相当于DROP所有记录,然后再REINSERT;可以解决磁盘碎片
#优化表
ALTER TABLE USER OPTIMIZE PARTITION partition_0; //在大量DELETE表数据后,可以回收空间和碎片整理。但在5.5.30后支持。在5.5.30之前可以通过RECREATE+ANALYZE来替代,如果用REBUILD+ANALYZE速度慢
#ANALZYE表
ALTER TABLE USER ANALYZE PARTITION partition_0/ALL;
#CHECK表
ALTER TABLE USER CHECK PARTITION partition_0/ALL;
#查看建表语句
SHOW CREATE TABLE USER;
#查看表是否是分区表,字段CREATE_OPTIONS: PARTITIONED
SHOW TABLE STATUS LIKE 'USER';
#查看索引
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='USER';
#查看分区表
SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='USER';
#查看分区是否被SELECT使用
EXPLAIN PARTITIONS SELECT * FROM USER WHERE CREATE_DATE < '1990-01-01' OR CREATE_DATE > '2016-01-01';
以上代码均通过了测试,可以放心使用。
CREATE TABLE `USER` (
`USR_ID` INT(11) NOT NULL AUTO_INCREMENT COMMENT '用户编号',
`CUSTORM_ID` INT(11) DEFAULT NULL COMMENT '客户代码',
`STATUS` INT(11) DEFAULT NULL COMMENT '用户状态',
`CREATE_DATE` DATETIME DEFAULT NULL COMMENT '创建日期',
`ACTIV_CODE` VARCHAR(32) DEFAULT NULL COMMENT '激活码',
`USR_TYPE` INT(11) DEFAULT NULL COMMENT '用户类型',
PRIMARY KEY (`USR_ID`)
)
PARTITION BY LIST (USR_TYPE)(
PARTITION partition_0 VALUES IN (1,2,3),
PARTITION partition_1 VALUES IN (4,5,6),
PARTITION partition_2 VALUES IN (7,8,9)
);
MySQL会提示错误:[Err] 1503 - A PRIMARY KEY must include all columns in the table's partitioning function,意思就是分区字段必须包含在主键之中,这是MySQL的硬性规定,我们只有遵守,不知道新版本可不可以,解决办法就是将分区字段加入到联合主键中:
CREATE TABLE `USER` (
`USR_ID` INT(11) NOT NULL AUTO_INCREMENT COMMENT '用户编号',
`CUSTORM_ID` INT(11) DEFAULT NULL COMMENT '客户代码',
`STATUS` INT(11) DEFAULT NULL COMMENT '用户状态',
`CREATE_DATE` DATETIME DEFAULT NULL COMMENT '创建日期',
`ACTIV_CODE` VARCHAR(32) DEFAULT NULL COMMENT '激活码',
`USR_TYPE` INT(11) DEFAULT NULL COMMENT '用户类型',
PRIMARY KEY (USR_ID,USR_TYPE)
)
PARTITION BY LIST (USR_TYPE)(
PARTITION partition_0 VALUES IN (1,2,3),
PARTITION partition_1 VALUES IN (4,5,6),
PARTITION partition_2 VALUES IN (7,8,9)
);
CREATE TABLE `USER` (
`USR_ID` INT(11) NOT NULL AUTO_INCREMENT COMMENT '用户编号',
`CUSTORM_ID` INT(11) DEFAULT NULL COMMENT '客户代码',
`STATUS` INT(11) DEFAULT NULL COMMENT '用户状态',
`CREATE_DATE` DATETIME DEFAULT NULL COMMENT '创建日期',
`ACTIV_CODE` VARCHAR(32) DEFAULT NULL COMMENT '激活码',
`USR_TYPE` INT(11) DEFAULT NULL COMMENT '用户类型',
PRIMARY KEY (USR_ID,USR_TYPE)
)
PARTITION BY LIST(USR_TYPE)(
PARTITION partition_0 VALUES IN (1,2,3),
PARTITION partition_1 VALUES IN (4,5,6),
PARTITION partition_2 VALUES IN (7,8,9)
);
ALTER TABLE USER DROP PARTITION partition_0;
来进行删除,它与具有同样作用的DELETE QUERY
DELETE QUERY DELETE FROM USER WHERE USR_TYPE IN (1,2,3);
比起来要有效率得多。
INSERT INTO USER VALUES(1,1,1,1,1,10);
这是因为“USR_TYPE”列值"10"不包含在已定义的分区值内。要重点注意的是,LIST分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其他值在内的定义,将要匹配的任何值都必须在值列表中找到。
CREATE TABLE `USER` (
`USR_ID` INT(11) NOT NULL AUTO_INCREMENT COMMENT '用户编号',
`CUSTORM_ID` INT(11) DEFAULT NULL COMMENT '客户代码',
`STATUS` INT(11) DEFAULT NULL COMMENT '用户状态',
`CREATE_DATE` DATETIME DEFAULT NULL COMMENT '创建日期',
`ACTIV_CODE` VARCHAR(32) DEFAULT NULL COMMENT '激活码',
`USR_TYPE` INT(11) DEFAULT NULL COMMENT '用户类型',
PRIMARY KEY (`USR_ID`)
)
PARTITION BY HASH (USR_ID)(
PARTITION 4
);
如果没有包括一个PARTITIONS子句,那么分区的数量将默认为1。 例外: 对于NDB Cluster(簇)表,默认的分区数量将与簇数据节点的数量相同,这种修正可能是考虑任何MAX_ROWS 设置,以便确保所有的行都能合适地插入到分区中。
CREATE TABLE t1 (
col1 INT,
col2 CHAR(5),
col3 DATE
)
PARTITION BY HASH(YEAR(col3) ){
PARTITIONS 4
};
如果插入一个col3列值为'2005-09-15'的记录到表t1中,那么保存该条记录的分区确定如下:
MOD(YEAR('2005-09-01'),4)
= MOD(2005,4)
= 1
每当插入或更新一条记录,用户函数都要计算一次。当删除记录时,用户函数也可能要进行计算,这取决于所处的环境。
MySQL还支持线性哈希功能,它与常规哈希的区别在于,线性哈希功能使用的一个线性的2的幂(powers-of-two)运算法则,而常规 哈希使用的是求哈希函数值的模数。
PARTITION BY LINEAR HASH(USR_ID)
CREATE TABLE `USER` (
`USR_ID` INT(11) NOT NULL AUTO_INCREMENT COMMENT '用户编号',
`CUSTORM_ID` INT(11) DEFAULT NULL COMMENT '客户代码',
`STATUS` INT(11) DEFAULT NULL COMMENT '用户状态',
`CREATE_DATE` DATETIME DEFAULT NULL COMMENT '创建日期',
`ACTIV_CODE` VARCHAR(32) DEFAULT NULL COMMENT '激活码',
`USR_TYPE` INT(11) DEFAULT NULL COMMENT '用户类型',
PRIMARY KEY (`USR_ID`)
)
PARTITION BY LINEAR KEY (USR_ID)(
PARTITIONS 4
);
在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用,分区的编号是通过2的幂(powers-of-two)算法得到,而不是通过模数算法。
CREATE TABLE `gp_city` (
`ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`NAME` varchar(32) NOT NULL COMMENT '城市名称',
`CODE` varchar(16) NOT NULL COMMENT '城市编码',
`PROVINCE_CODE` varchar(16) NOT NULL COMMENT '所属省份编码',
`COUNTRY_CODE` varchar(16) NOT NULL COMMENT '所属国家',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=346 DEFAULT CHARSET=utf8 COMMENT='城市';
ALTER TABLE gp_city PARTITION BY RANGE(ID)(
PARTITION partition_0 VALUES LESS THAN (10),
PARTITION partition_1 VALUES LESS THAN (20),
PARTITION partition_2 VALUES LESS THAN (30),
PARTITION partition_3 VALUES LESS THAN (40),
PARTITION partition_4 VALUES LESS THAN (MAXVALUE)
);
分区前gp_city只有gp_city.frm与gp_city.ibd两个数据文件其中*.frm存储表结构,*.ibd 存储表数据。
所以在无特殊情况下我们应该优先选择分区而不是水平分表。