数据库分区

概述

什么是数据分区

mysql数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面（可以通过my.cnf中的datadir来查看），在innoDB擎中，一张表主要对应着二个文件，一个是frm存放表结构的，一个是ibd 存放数据和索引的。如果一张表的数据量太大的话，那么ibd文件就会变的很大，查找数据就会变的很慢。
这个时候我们可以利用mysql的分区功能，在物理上将这一张表对应的三个文件，分割成许多个小块，我们查找一条数据时，就不用全部查找了，只要知道这条数据在哪一块，然后在那一块找就行了。如果表的数据太大，可能一个磁盘放不下，这个时候，我们可以把数据分配到不同的磁盘里面去。

分区的二种方式

横向分区

举例来说明，假如有100W条数据，分成十份，前10W条数据放到第一个分区，第二个10W条数据放到第二个分区，依此类推。也就是把表分成了十分，而表的结构并没有变化。
纵向分区

举例来说明，在设计用户表的时候，开始的时候没有考虑好，而把个人的所有信息都放到了一张表里面去，这样这个表里面就会有比较大的字段，分区的时候，可以把这样的大字段，分开来。将一张表变成两张表来处理。

分区类型

Range分区

Range分区最为常用，基于属于一个给定连续区间的列值，把多行分配给分区。最常见的是基于时间字段，基于分区的列最好是整型，如果日期型的可以使用函数转换为整型。

查看data存放目录

SHOW VARIABLES LIKE '%datadir%';

创建分区

CREATE TABLE `order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `access_time` datetime NOT NULL,
  PRIMARY KEY (`id`,`access_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (to_days(access_time))
(PARTITION p1 VALUES LESS THAN (to_days(20190101)) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (to_days(20190102)) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (to_days(20190103)) ENGINE = InnoDB) */;
 
 
-- 或者
CREATE TABLE `order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `access_time` datetime NOT NULL,
  PRIMARY KEY (`id`,`access_time`)
) 
PARTITION BY RANGE (TO_DAYS(access_time) ) (
    PARTITION p1 VALUES LESS THAN ( TO_DAYS('20190101') ),
    PARTITION p2 VALUES LESS THAN ( TO_DAYS('20190102') ),
    PARTITION p3 VALUES LESS THAN ( TO_DAYS('20190103') ),
    PARTITION p4 VALUES LESS THAN ( TO_DAYS('20190104') ),
    PARTITION p5 VALUES LESS THAN ( TO_DAYS('20190105') ),
    PARTITION p6 VALUES LESS THAN ( TO_DAYS('20190106') ),
    PARTITION p7 VALUES LESS THAN ( TO_DAYS('20190107') ),
    PARTITION p8 VALUES LESS THAN ( TO_DAYS('20190108') ),
    PARTITION p9 VALUES LESS THAN ( TO_DAYS('20190109') ),
    PARTITION p10 VALUES LESS THAN ( TO_DAYS('20190110') ),
    PARTITION p11 VALUES LESS THAN (MAXVALUE)   -- p11是一个默认分区，所有大于20171211的记录都会在这个分区。
);

创建后可以看到，每个分区都会对应1个ibd文件：

多列分区

create table rc3(
  a int,
  b int
)
partition by range columns(a,b)(
  partition p0 values less than (0,10),
  partition p1 values less than (10,20),
  partition p2 values less than (20,30),
  partition p3 values less than (30,40),
  partition p4 values less than (40,50),
  partition p5 values less than (maxvalue,maxvalue)
)

新增分区

alter table `order` add partition(
    partition p4 values less than (to_days('20190105'))
);

1630899938(1).jpg

拆分分区

ALTER TABLE `order` REORGANIZE PARTITION p4 INTO (
    PARTITION s0 VALUES less than ( to_days( '20190104' )), 
    PARTITION s1 VALUES less than ( to_days( '20190105' )) 
);

1630899990(1).jpg

删除分区

alter table `order` drop partition p1;

1630900042(1).jpg

合并分区

alter table `order` reorganize partition s0,s1 into ( 
    partition p4 values less than (to_days('20190105')) 
);

1630900074(1).jpg

LIST分区

LIST分区和RANGE分区类似，区别在于LIST是枚举值列表的集合，RANGE是连续的区间值的集合。二者在语法方面非常的相似。同样建议LIST分区列是非null列，否则插入null值如果枚举列表里面不存在null值会插入失败，这点和其它的分区不一样，RANGE分区会将其作为最小分区值存储，HASH\KEY分为会将其转换成0存储，主要LIST分区只支持整形，非整形字段需要通过函数转换成整形。

格式如下：

create table t_list( 
    a int(11), 
    b int(11) 
)(partition by list (b) 
  partition p0 values in (1,3,5,7,9), 
　partition p1 values in (2,4,6,8,0)
);

KEY分区其实跟HASH分区差不多，不同点如下：

KEY分区允许多列，而HASH分区只允许一列。
如果在有主键或者唯一键的情况下，key中分区列可不指定，默认为主键或者唯一键，如果没有，则必须显性指定列。
KEY分区对象必须为列，而不能是基于列的表达式。
KEY分区和HASH分区的算法不一样，PARTITION BY HASH (expr)，MOD取值的对象是expr返回的值，而PARTITION BY KEY (column_list)，基于的是列的MD5值。

Hash 分区

我们在实际工作中经常遇到像会员表的这种表。并没有明显可以分区的特征字段。但表数据有非常庞大，为了把这类的数据进行分区打散mysql 提供了hash分区。基于给定的分区个数，将数据分配到不同的分区。HASH分区只能针对整数进行HASH，对于非整形的字段只能通过表达式将其转换成整数。表达式可以是mysql中任意有效的函数或者表达式，对于非整形的HASH往表插入数据的过程中会多一步表达式的计算操作，所以不建议使用复杂的表达式这样会影响性能。

格式如下：

CREATE TABLE my_member (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    created DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH(id)
PARTITIONS 4;

注意：

HASH分区可以不用指定PARTITIONS子句，如上文中的PARTITIONS 4，则默认分区数为1。
不允许只写PARTITIONS，而不指定分区数。
同RANGE分区和LIST分区一样，PARTITION BY HASH (expr)子句中的expr返回的必须是整数值。

HASH分区的底层实现其实是基于MOD函数。譬如，对于下表

CREATE TABLE t1 (
    col1 INT,
    col2 CHAR(5), 
    col3 DATE
) PARTITION BY HASH( YEAR(col3) ) 
PARTITIONS 4;

-- 如果你要插入一个col3为“2017-09-15”的记录，则分区的选择是根据以下值决定的：
MOD(YEAR('2017-09-01'),4) = MOD(2017,4) = 1

LINEAR HASH分区

LINEAR HASH分区是HASH分区的一种特殊类型，与HASH分区是基于MOD函数不同的是，它基于的是另外一种算法。

格式如下：

CREATE TABLE my_members (
    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 LINEAR HASH( id )
PARTITIONS 4;

说明： 它的优点是在数据量大的场景，譬如TB级，增加、删除、合并和拆分分区会更快，缺点是，相对于HASH分区，它数据分布不均匀的概率更大。

KEY分区

KEY分区其实跟HASH分区差不多，不同点如下：

KEY分区允许多列，而HASH分区只允许一列。
如果在有主键或者唯一键的情况下，key中分区列可不指定，默认为主键或者唯一键，如果没有，则必须显性指定列。
KEY分区对象必须为列，而不能是基于列的表达式。
KEY分区和HASH分区的算法不一样，PARTITION BY HASH (expr)，MOD取值的对象是expr返回的值，而PARTITION BY KEY (column_list)，基于的是列的MD5值。

格式如下：

CREATE TABLE k1 (
    id INT NOT NULL PRIMARY KEY,    
    name VARCHAR(20)
)
PARTITION BY KEY()
PARTITIONS 2;

在没有主键或者唯一键的情况下，格式如下：

CREATE TABLE tm1 (
    s1 CHAR(32)
)
PARTITION BY KEY(s1)
PARTITIONS 10;

其他命令

重新定义hash分区表

Alter table `order` partition by hash(TO_DAYS(access_time))partitions 7; -- 不会丢失数据

重新定义range分区表:

Alter table `order` partitionbyrange(TO_DAYS(access_time))(
    PARTITION p1 VALUES LESS THAN ( TO_DAYS('20190101') ),
    PARTITION p2 VALUES LESS THAN ( TO_DAYS('20190102') )
); ----不会丢失数据

删除表的所有分区:

Alter table `order` removepartitioning; -- 不会丢失数据

重建分区

这和先删除保存在分区中的所有记录，然后重新插入它们，具有同样的效果。它可用于整理分区碎片。

ALTER TABLE `order` rebuild partitionp1,p2;

优化分区

如果从分区中删除了大量的行，或者对一个带有可变长度的行（也就是说，有VARCHAR，BLOB，或TEXT类型的列）作了许多修改，可以使用“ALTER TABLE ... OPTIMIZE PARTITION”来收回没有使用的空间，并整理分区数据文件的碎片。

ALTER TABLE `order` optimize partition p1,p2;

分析分区：

读取并保存分区的键分布。

ALTER TABLE `order` analyze partition p1,p2;

修补分区

ALTER TABLE `order` repairpartition p1,p2;

检查分区

可以使用几乎与对非分区表使用CHECK TABLE 相同的方式检查分区。

ALTER TABLE `order` CHECK partition p1,p2;

这个命令可以告诉你表order的分区p1,p2中的数据或索引是否已经被破坏。如果发生了这种情况，使用“ALTER TABLE ... REPAIR PARTITION”来修补该分区。

获取mysql分区表信息的几种方法

show create table 表名;   -- 可以查看创建分区表的create语句
show table status 表名;   -- 可以查看表是不是分区表

-- 查看information_schema.partitions表，可以查看表具有哪几个分区、分区的方法、分区中数据的记录数等信息
SELECT
    partition_name part,
    partition_expression expr,
    partition_description descr,
    table_rows 
FROM
    information_schema.PARTITIONS 
WHERE
    table_schema = SCHEMA () 
    AND table_name = 'order';

explain partitions select ... ...   -- 语句通过此语句来显示扫描哪些分区，及他们是如何使用的.

查询

我们在执行查询的时候，必须带上分区字段。这样可以使用分区剪裁功能

 explain partitions select * from `order` where access_time >= '20190102124503' and access_time<='20190104111230';

1630900748(1).jpg

注意： 执行计划中的partitions的内容，只查询了p3，p4，p5，p6三个分区，由此来看，使用to_days函数确实可以实现分区裁剪。

timestamp类型

上面是基于datetime的，如果是timestamp类型，我们遇到上面问题呢？

事实上，MySQL提供了一种基于UNIX_TIMESTAMP函数的RANGE分区方案，而且，只能使用UNIX_TIMESTAMP函数，如果使用其它函数，譬如to_days，会报如下错误：“ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed”。下面来测试一下基于UNIX_TIMESTAMP函数的RANGE分区方案，看其能否实现分区裁剪。

格式如下：

CREATE TABLE order_timestamp (
    id INT,
    access_time TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(access_time) ) (
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-02 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-03 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-04 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-05 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-06 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-07 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-08 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-09 00:00:00') ),
    PARTITION p9 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-10 00:00:00') ),
    PARTITION p10 VALUES LESS THAN (UNIX_TIMESTAMP('2017-12-11 00:00:00') )
);

插入数据并查看上述查询的执行计划

explain partitions select * from order_timestamp where access_time >= '20171207124503' and access_time<='20171210111230';

1630902495(1).jpg

补充

在5.7版本之前，对于DATA和DATETIME类型的列，如果要实现分区裁剪，只能使用YEAR() 和TO_DAYS() 函数，在5.7版本中，又新增了TO_SECONDS() 函数。

总结

什么时候要分区

大表始终是一个很好的分区候选对象，超过2Gb的一切都是一个开始。
如果您的表中包含历史数据，并且数据将按时间顺序添加。例如，如果您按月或天为基础按批次加载数据，则更是如此。在这种类型的分区中，一个分区只能保留最新的分区可更新，而其他分区(旧)则是只读的。
如果您需要在不同类型的存储(在某些情况下甚至是服务器)之间分配表。例如，大多数查询都在最近三年的分区上运行，但是您需要在表中保留10年的数据。最古老的7年很少被查询。这样，您可以将最新的三个分区放置在高性能SSD驱动器上，而将最旧的7个分区放置在较旧和较便宜但速度较慢的HD驱动器上。

分区的优势

MySQL分区的有限主要包括以下4个方面：

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

分表与分区的区别

分区和水平分表功能类似，将一个大表的数据分割到多张小表中去，由于查询不需要全表扫描了，只需要扫描某些分区，所以分区能提高查询速度。

水平分表需要用户预先手动显式创建出多张分表(如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，要不然怎么确定分区范围呢，所以尽量NOT NULL。
最大分区数目不能超过1024。
不支持外键。
只能对数据表的整型列进行分区，或者数据列可以通过分区函数转化成整型列。
分区表不影响自增列。

数据库分区一篇就透了

数据库分区

概述

什么是数据分区

分区的二种方式

分区类型

Range分区

查看data存放目录

创建分区

多列分区

新增分区

拆分分区

删除分区

合并分区

LIST分区

Hash 分区

LINEAR HASH分区

KEY分区

其他命令

重新定义hash分区表

重新定义range分区表:

删除表的所有分区:

重建分区

优化分区

分析分区：

修补分区

检查分区

获取mysql分区表信息的几种方法

查询

timestamp类型

补充

总结

什么时候要分区

分区的优势

分表与分区的区别

注意事项