MySQL之分区

最新推荐文章于 2022-08-27 16:29:07 发布

hycxag

最新推荐文章于 2022-08-27 16:29:07 发布

阅读量355

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/hycxag/article/details/105247041

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

分合思想

技术不要拘泥于一点，重要的是编程思想，即思想是最重要的。当数据量大的时候，需要具有分的思想去细化粒度。当数据量太碎片的时候，需要具有合的思想来粗化粒度。

表分区介绍

InnoDB存储结构

首先要先介绍一下InnoDB逻辑存储结构和区的概念，它的所有数据都被逻辑地存放在表空间，表空间又由段，区，页组成。
在这里插入图片描述

段

段就是上图的segment区域，常见的段有数据段、索引段、回滚段等，在InnoDB存储引擎中，对段的管理都是由引擎自身所完成的。

区

区就是上图的extent区域，区是由连续的页组成的空间，无论页的大小怎么变，区的大小默认总是为1MB。为了保证区中的页的连续性，InnoDB存储引擎一次从磁盘申请4-5个区，InnoDB页的大小默认为16kb，即一个区一共有64（1MB/16kb=16）个连续的页。每个段开始，先用32页（page）大小的碎片页来存放数据，在使用完这些页之后才是64个连续页的申请。这样做的目的是，对于一些小表或者是undo类的段，可以开始申请较小的空间，节约磁盘开销。

页

页就是上图的page区域，也可以叫块。页是InnoDB磁盘管理的最小单位。默认大小为16KB，可以通过参数innodb_page_size来设置。常见的页类型有：数据页，undo页，系统页，事务数据页，插入缓冲位图页，插入缓冲空闲列表页，未压缩的二进制大对象页，压缩的二进制大对象页等。

表分区

分区是根据一定的规则，数据库把一个表分解成多个更小的、更容易管理的部分。就访问数据库应用而言，逻辑上就只有一个表或者一个索引，但实际上这个表可能有N个物理分区对象组成，每个分区都是一个独立的对象，可以独立处理，可以作为表的一部分进行处理。分区对应用来说是完全透明的，不影响应用的业务逻辑。几个分区就有几个.idb文件，不是我们上述extent区域。分区是将一个表或索引分解成多个更小，更可管理的部分。每个区都是独立的，可以独立处理，也可以作为一个更大对象的一部分进行处理。这个是MySQL支持的功能，业务代码无需改动。要知道MySQL是面向OLTP的数据，它不像TIDB等其他DB。
有时候可能会有这么一种误区，只要启用了分区，数据库就会运行的更快。这个结论结论是存在很多问题的，就经验来看，分区可能会给某些SQL语句性能带来提高，但是分区主要用于数据库高可用性的管理。在OLTP应用中，对于分区的使用应该非常小心，总之，如果只是一味地使用分区，而不理解分区是如何工作的，也不清楚你的应用如何使用分区，那么分区极有可能会对性能产生负面的影响。
MySQL数据库支持的分区类型为水平分区（指将同一个表中不同行的记录分配到不同的物理文件中），并不支持垂直分区（指将同一表中不同列的记录分配到不同的物理文件中）。此外，MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中。目前，MySQL数据库还不支持全局分区。
　无论哪种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。

show global variable like '%partition%';
show plugins\G

表分区优点

和单个磁盘或者文件系统分区相比，可以存储更多数据
优化查询。在where子句中包含分区条件时，可以只扫描必要的一个或者多个分区来提高查询效率；同时在涉及sum()和count()这类聚合函数的查询时，可以容易的在每个分区上并行处理，最终只需要汇总所有分区得到的结果
对于已经过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来快速删除数据
跨多个磁盘来分散数据查询，以获得更大的查询吞吐量

启动分区后形成的文件

.frm文件：表结构文件
.ibd文件：InnoDB中，索引和数据都在同个文件.ibdata（你的执行结果可能是.MYD数据文件和.MYI索引文件，没关系，这是MyIsAm存储引擎，对应着InnoDB的.ibd文件
.par文件：你执行的结果可能有.par文件也可能没有。注意：从MySql 5.7.6开始，不再创建.par分区定义文件。分区定义存储在内部数据字典中。

数据处理

分区表后，提高了MySql性能。如果一张表的话，那就只有一个.ibd文件，一颗大的B+树。如果分表后，将按分区规则，分成不同的区，也就是一个大的B+树，分成多个小的树。
读的效率肯定提升了，如果走分区键索引的话，先走对应分区的辅助索引B+树，再走对应分区的聚集索引B+树。如果没有走分区键，将会在所有分区都会执行一次。会造成多次逻辑IO！平时开发如果想查看sql语句的分区查询可以使用explain partitons select xxxxx语句。可以看到一句select语句走了几个分区。

表分区类型

目前MySQL支持以下几种类型的分区，RANGE分区，LIST分区，HASH分区，KEY分区。如果表存在主键或者唯一索引时，分区列必须是唯一索引的一个组成部分。实战十有八九都是用RANGE分区。

RANGE分区

RANGE分区是实战最常用的一种分区类型，行数据基于属于一个给定的连续区间的列值被放入分区。但是记住，当插入的数据不在一个分区中定义的值的时候，会抛异常。RANGE分区主要用于日期列的分区，比如交易表啊，销售表啊等。可以根据年月来存放数据。如果你分区走的唯一索引中date类型的数据，那么注意了，优化器只能对YEAR(),TO_DAYS(),TO_SECONDS(),UNIX_TIMESTAMP()这类函数进行优化选择。实战中可以用int类型，那么只用存yyyyMM就好了。也不用关心函数了。

CREATE TABLE `m_test_db`.`Order` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `partition_key` INT NOT NULL,
  `amt` DECIMAL(5) NULL,
  PRIMARY KEY (`id`, `partition_key`)) 
PARTITION BY RANGE(partition_key) PARTITIONS 5( 
	PARTITION part0 VALUES LESS THAN (201901),  
	PARTITION part1 VALUES LESS THAN (201902),  
	PARTITION part2 VALUES LESS THAN (201903),  
	PARTITION part3 VALUES LESS THAN (201904),  
	PARTITION part4 VALUES LESS THAN (201905)
) ;

插入如下数据：
INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('1', '201901', '1000');
INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('2', '201902', '800');
INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('3', '201903', '1200');

现在我们查询一下，通过EXPLAIN PARTITION命令发现SQL优化器只需搜对应的区，不会搜索所有分区
在这里插入图片描述
如果sql语句有问题，那么会走所有区。会很危险。所以分区表后，select语句必须走分区键。

LIST分区

LIST分区和RANGE分区很相似，只是分区列的值是离散的，不是连续的。LIST分区使用VALUES IN，因为每个分区的值是离散的，因此只能定义值。

CREATE TABLE employees (
  id INT NOT NULL,
  name VARCHAR(30),
  hired DATE NOT NULL DEFAULT '1970-01-01',
  separated DATE NOT NULL DEFAULT '9999-12-31',
  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)
);

HASH分区

说到哈希，那么目的很明显了，将数据均匀的分布到预先定义的各个分区中，保证每个分区的数量大致相同。
要使用HASH分区来分割一个表，要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句，其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL 整型的一列的名字。此外，你很可能需要在后面再添加一个“PARTITIONS num”子句，其中num是一个非负的整数，它表示表将要被分割成分区的数量，如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1。

CREATE TABLE employees_h (
  id INT NOT NULL,
  lname VARCHAR(30),
  store_id INT
)
  PARTITION BY HASH(store_id)
  PARTITIONS 4;

MySQL还支持线性哈希功能，它与常规哈希的区别在于，线性哈希功能使用的一个线性的2的幂（powers-of-two）运算法则，而常规哈希使用的是求哈希函数值的模数。
线性哈希分区和常规哈希分区在语法上的唯一区别在于，在“PARTITION BY” 子句中添加“LINEAR”关键字。

CREATE TABLE employees_lh (
  id INT NOT NULL,
  name VARCHAR(30),
  hired DATE NOT NULL DEFAULT '1970-01-01',
  separated DATE NOT NULL DEFAULT '9999-12-31',
  store_id INT
)
  PARTITION BY LINEAR HASH(YEAR(hired))
  PARTITIONS 4;

按照线性哈希分区的优点在于增加、删除、合并和拆分分区将变得更加快捷，有利于处理含有极其大量（1000吉）数据的表。它的缺点在于，与使用常规HASH分区得到的数据分布相比，各个分区间数据的分布不大可能均衡。

KEY分区

KEY分区和HASH分区相似，不同之处在于HASH分区使用用户定义的函数进行分区，KEY分区使用数据库提供的函数进行分区。

COLUMNS分区

在前面说了RANGE、LIST、HASH和KEY这四种分区中，分区的条件是：数据必须为整形（interger），如果不是整形，那应该需要通过函数将其转化为整形，如YEAR()，TO_DAYS()，MONTH()等函数。MySQL5.5版本开始支持COLUMNS分区，可视为RANGE分区和LIST分区的一种进化。COLUMNS分区可以直接使用非整形的数据进行分区，分区根据类型直接比较而得，不需要转化为整形。此外，RANGE COLUMNS分区可以对多个列的值进行分区。
COLUMNS分区支持以下的数据类型：

所有的整形类型，如INT、SMALLINT、TINYINT和BIGINT。而FLOAT和DECIMAL则不予支持。
日期类型，如DATE何DATETIME。其余的日期类型不予支持。
字符串类型，如CHAR、VARCHAR、BINARY和VARBINARY。而BLOB和TEXT类型不予支持。

对于日期类型的分区，我们不再需要YEAR()和TO_DATS()函数了，而直接可以使用COLUMNS，如：

CREATE TABLE `t_c` (
  `key` varchar(50),
  `value` varchar(50),
  `create_time` datetime
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  PARTITION BY RANGE COLUMNS (create_time) (
  PARTITION p0 VALUES LESS THAN ('2017-01-01 00:00:00'),
  PARTITION p1 VALUES LESS THAN ('2017-03-01 00:00:00')
);
//字符串分区
CREATETABLE`monitor_2`(
  `key`varchar(15),
  `value`varchar(50),
  `create_time`datetime,
  `city`VARCHAR(15)
)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4
PARTITIONBYLISTCOLUMNS(city)(
PARTITIONp0VALUESIN('shanghai','beijing','shenzhen'),
PARTITIONp1VALUESIN('hubei','henan','hunan')
);

对比RANGE分区和LIST分区，Columns分区的亮点除了支持数据类型增加之外，另外一大亮点是Columns分区还支持多列分区。如：

CREATE TABLE `monitor_3` (
  `key` varchar(15),
  `value` varchar(50),
  `create_time` datetime,
  `test` VARCHAR(1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE COLUMNS(create_time,test) (
PARTITION p0 VALUES LESS THAN ('2017-01-01 00:00:00','yes'),
PARTITION p1 VALUES LESS THAN ('2017-03-01 00:00:00','no'),
PARTITION p2 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);

MySQL 5.5开始支持COLUMNS分区，对于之前的RANGE和LIST分区，用户可以用RANGE COLUMNS和LIST COLUMNS分区进行很好的代替了。

MySQL子分区

子分区（subparttitioning）是在分区的基础上再进行分区，有时也称这种分区为复合分区。MySQL数据库允许在RANGE和LIST的分区上再进行HASH或KEY的子分区，如：

createtablets(aint,bdate)engine=innodb
  partitionbyrange(year(b))
  subpartitionbyhash(to_days(b))
  subpartitions2(
  partitionp0valueslessthan(1990),
  partitionp1valueslessthan(2000),
  partitionp2valueslessthanmaxvalue
);

// 结果为
mysql> system ls -ln /data/mysql/3306/data/db
total 592
-rw-r----- 1 27 27    67 Feb 27 12:03 db.opt
-rw-r----- 1 27 27  8578 Feb 27 15:54 ts.frm
-rw-r----- 1 27 27 98304 Feb 27 15:54 ts#P#p0#SP#p0sp0.ibd
-rw-r----- 1 27 27 98304 Feb 27 15:54 ts#P#p0#SP#p0sp1.ibd
-rw-r----- 1 27 27 98304 Feb 27 15:54 ts#P#p1#SP#p1sp0.ibd
-rw-r----- 1 27 27 98304 Feb 27 15:54 ts#P#p1#SP#p1sp1.ibd
-rw-r----- 1 27 27 98304 Feb 27 15:54 ts#P#p2#SP#p2sp0.ibd
-rw-r----- 1 27 27 98304 Feb 27 15:54 ts#P#p2#SP#p2sp1.ibd

表ts先根据b列进行了RANGE分区，然后又进行了一次HASH分区，所以分区的数量应该为（3×2=）6个，这通过查看物理磁盘上的文件也可以得到证实。我们也可以通过使用subpartition语法来显示地指出各个子分区的名字。
子分区的建立需要注意以下几个问题：

每个子分区的数量必须相同。
要在一个分区表的任何分区上使用subpartition来明确定义任何子分区，就必须定义所有的子分区。
每个subpartition子句必须包括子分区的一个名字。
子分区的名字必须是唯一的。

表分区中注意事项

表分区与水平分表的区别

水平分表需要用户预先手动显式创建出多张分表(如tbl_user0, tbl_user1, tbl_user2)，在物理上实实在在的创建多张表，通过客户端代理(Sharding-JDBC等)或者中间件代理(Mycat等)来实现分表逻辑。
分区是MySQL的一个插件Plugin功能，将一张大表的数据在数据库底层分成多个分区文件(如tbl_user#P#p0.ibd, tbl_user#P#p1.ibd, tbl_user#P#p2.ibd)，和水平分表不同的是分区不需要显式的创建“分表”,数据库会自动创建分区文件的，用户看到的只是一张普通的表，其实是对应的是多个分区，这个是对用户是屏蔽的、透明的，在使用上和使用一张表完全一样，不需要借助任何功能来实现。分区是一种逻辑上的水平分表，在物理层面还是一张表。

区别于分区的是，分区一般都是放在单机里的，用的比较多的是时间范围分区，方便归档。只不过分库分表需要代码实现，分区则是mysql内部实现。分库分表和分区并不冲突，可以结合使用。

分区中NULL值

MySQL数据库允许对NULL值做分区，但是处理的方法与其他数据库可能完全不同。**MySQL数据库的分区总是视NULL值小于任何的一个非NULL值，这和MySQL数据库中处理NULL值的ORDER BY操作是一样的。**因此对于不同的分区类型，MySQL数据库对于NULL值的处理也是各不相同。

对于RANGE分区，如果向分区列插入了NULL值，则MySQL数据库会将该值放入最左边的分区。
对于LIST分区，如果向分区列插入了NULL值，则必须显示地指出哪个分区放入NULL值，否则会报错。
对于HASH和KEY分区，对于NULL值的处理方法和RANGE分区、LIST分区不一样。任何分区函数都会将含有NULL值的记录返回为0。

分区和性能

分区真的会加快数据库的查询吗？实际上可能根本感觉不到查询速度的提升，甚至会发现查询速度急剧下降，因此在合理使用分区之前，必须了解分区的使用环境。
数据库的应用分为两类：一类是OLTP（在线事务处理），如Blog、电子商务、网络游戏等；另一类是OLAP（在线分析处理），如数据仓库、数据集市。**对于OLAP的应用，分区的确是可以很好地提高查询的性能，因为OLAP应用大多数查询需要频繁地扫描一张很大的表。**假设有一张1亿行的表，其中有一个时间戳属性列。用户的查询需要从这张表中获取一年的数据。如果按时间戳进行分区，则只需要扫描相应的分区即可。这就是前面介绍的分区修剪技术。
对于OLTP的应用，分区应该非常小心。在这种应用下，通常不可能会获取一张大表10%的数据，大部分都是通过索引返回几条记录即可。而根据B+树索引的原理可知，对于一张大表，一般的B+树需要2~3次的磁盘IO。因此B+树可以很好地完成操作，不需要分区的帮助，并且设计不好的分区会带来严重的性能问题。
如很多开发团队会认为含有1000w行的表是一张非常巨大的表，所以他们往往会选择采用分区，如对主键做10个HASH的分区，这样每个分区就只有100w的数据了，因此查询应该变得更快了。如select * from table where pk=@pk。但是有没有考虑过这样一种情况：100w和1000w行的数据本身构成的B+树的层次都是一样的，可能都是2~3层。那么上述走主键分区的索引并不会带来性能的提高。好的，如果1000w的B+树高度是3,100w的B+树高度是2，那么上述按主键分区的索引可以避免1次IO，从而提高查询的效率。这没问题，但是这张表只有主键索引，没有任何其他的列需要查询的。如果还有类似如下的SQL：select * from table where key=@key，这时对于key的查询需要扫描所有的10个分区，即使每个分区的查询开销为2次IO，则一共需要20次IO。而对于原来单表的设计，对于KEY的查询只需要2~3次IO。

分区键必须包含在主键字段

MySQL的分区字段，必须包含在主键字段内。主键的限制，每一个分区表中的公式中的列，必须在primary key/unique key中包括。分区字段必须包含在主键字段内，至于为什么MySQL会这样考虑，CSDN的斑竹是这么解释的：为了确保主键的效率，否则同一主键区的东西一个在Ａ分区，一个在Ｂ分区，显然会比较麻烦。

MySQL 5.7对分区的改进

在MySQL 5.6里面，分区的信息是在MySQL Server层维护的（在.par文件里面），InnoDB引擎层是不知道有分区这个概念的，InnoDB引擎层把每一个分区都当成一张普通的InnoDB表。在打开一个分区表时，会打开很多个分区，打开这些分区表就相当于打开了同等数量的InnoDB表，这需要更多内存存放InnoDB表的元数据和各种与ibd文件打开相关的各种cache与handler的信息。在MySQL 5.7里面，InnoDB引入了Native Partitioning，它把分区的信息从Server层移到了InnoDB层，打开一个分区表和打开一个InnoDB表的内存开销基本是一样的。

使用remove移除分区是仅仅移除分区的定义，并不会删除数据和drop PARTITION不一样，后者会连同数据一起删除
ALTER TABLE tablename REMOVE PARTITIONING ;
通过这种删除分区的方式会将分区中的数据也删除,但是通过删除分区的方式删除数据会比delete快很多，因为它相当于删除一个数据库一样因为每个分区都是一个独立的数据文件。用来删除历史分区数据是非常好的办法。
alter table employees drop  PARTITION p4;
增加分区只能在最大端增加
alter table employees add PARTITION  (PARTITION p4 VALUES LESS THAN MAXVALUE);
拆分合并分区统称为重新定义分区，拆分分为不会造成数据的丢失，只将会将数据从一个分区移动到另一个分区。
无论是拆分还是合并分区都不能改变分区原本的覆盖范围，并且合并分区只能合并连续的分区不能跳过分区合并；并且不能改变分区的类型，例如不能把range分区改成key分区等。
ALTER TABLE employees REORGANIZE PARTITION p0 INTO ( //将P0拆分成s1,s2两个分区
    PARTITION s0 VALUES LESS THAN (3),
    PARTITION s1 VALUES LESS THAN (6)
);
ALTER TABLE employees REORGANIZE PARTITION s1,p1,p2 INTO ( //将s1,p1,p2合并为a,b两个分区
    PARTITION a VALUES LESS THAN (5),
    PARTITION b VALUES LESS THAN (16)
);

参考文献

MySQL 分库分表与分区的区别和思考
 MySQL性能优化分区之实战
 搞懂MySQL分区
 MySql表分区详解
 MySQL 5.7对分区的改进
 MySQL RANGE分区

hycxag

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MySQL之分区

分合思想技术不要拘泥于一点，重要的是编程思想，即思想是最重要的。当数据量大的时候，需要具有分的思想去细化粒度。当数据量太碎片的时候，需要具有合的思想来粗化粒度。表分区介绍InnoDB存储结构首先要先介绍一下InnoDB逻辑存储结构和区的概念，它的所有数据都被逻辑地存放在表空间，表空间又由段，区，页组成。段段就是上图的segment区域，常见的段有数据段、索引段、回滚段等，在InnoD...
复制链接

扫一扫