一、分区介绍

二、分区类型

range 分区:基于一个给定的连续区间范围(区间要求连续并且不能重叠),把数据分配到不同的分区
list     分区:类似于range分区,区别在于list分区是居于枚举出的值列表分区,range是基于给定的连续区间范围分区
hash  分区:基于给定的分区个数,把数据分配到不同的分区
key    分区:类似于hash分区

注意:无论哪种分区,要么你分区表上没有主键/唯一键,要么分区表的主键/唯一键都必须包含分区键,也就是说不能使用主键/唯一键字段之外的其它字段分区。
如果你想在分区表使用时间字段来作为分区键,你应该把ID和时间字段设为分区表的组合主键。

三、分区的优点

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

四、查看数据库文件

通过show variables like ‘%datadir%’;命令查看mysql的data存放目录,进入所在的数据库目录。不同的引擎数据库文件格式不同。

mysql> show variables like '%datadir%';
+---------------+---------------------------------------------+
| Variable_name | Value                                       |
+---------------+---------------------------------------------+
| datadir       | C:\ProgramData\MySQL\MySQL Server 5.7\Data\ |
+---------------+---------------------------------------------+

innodb: 只有设置成独立表空间才能做成功表分区 
.frm : 表结构
.ibd : 数据 + 索引

MySql  表的分区介绍-LMLPHP


五、分区详解和应用

MySQL有五种分区类型 range、list、hash、key、子分区,其中最常用的是range和list分区

首先需要确定你使用的数据库有没有开启分区功能

-- 查看mysql版本
select version( )    ,   mysql5.1开始支持数据表分区

-- 查看分区插件是否激活 partition active
show plugins; 

MySql  表的分区介绍-LMLPHP

(1) range分区
给定一个连续区间的范围值进行分区,某个字段的值满足这个范围就会被分配到该分区。适用于字段的值是连续的区间的字段,如 日期范围, 连续的数字。

-- 语法

create table <table> (
    // 字段
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
partition by range (分区字段) (
  partition <分区名称> values less than (Value),
  partition <分区名称> values less than (Value),
  ...
  partition <分区名称> values less than maxvalue
);

-- 字段介绍
range:表示按范围分区 
分区字段:表示要按照哪个字段进行分区,可以是一个字段名,也可以是对某个字段进行表达式运算如year(create_time),使用range最终的值必须是数字 
分区名称: 要保证不同,也可以采用 p0、p1、p2 这样的分区名称, 
less than : 表示小于 
Value : 表示要小于某个具体的值,如 less than (10) 那么分区字段的值小于10的都会被分到这个分区 
maxvalue: 表示一个最大的值

注意:range 对应的分区键值必须是数字值,可以使用range columns(分区字段) 对非int型做分区,如字符串,对于日期类型的可以使用year()、to_days()、to_seconds()等函数。

-- 创建方式
分区可以在创建表的时候进行分区,也可以在创建表之后进行分区

alter table <table> partition by RANGE(id) (
    PARTITION p0 VALUES LESS THAN (1000000),
    PARTITION p1 VALUES LESS THAN (2000000),
    PARTITION p2 VALUES LESS THAN (3000000),
    PARTITION p3 VALUES LESS THAN (4000000),
    PARTITION p4 VALUES LESS THAN MAXVALUE 
);

CREATE TABLE `tbl_user_part` (
   `id` int(11) NOT NULL ,
   `username` varchar(255) DEFAULT NULL,
   `email` varchar(20)     DEFAULT NULL,
   `age` tinyint(4)        DEFAULT NULL,
   `type` int(11)          DEFAULT NULL,
   `create_time` datetime  DEFAULT CURRENT_TIMESTAMP
   -- PRIMARY KEY (`id`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE (age) (
    PARTITION p0 VALUES LESS THAN (20),
    PARTITION p1 VALUES LESS THAN (40),
    PARTITION p2 VALUES LESS THAN (60),
    PARTITION p3 VALUES LESS THAN (80),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

-- 常见错误
在创建分区的时候经常会遇到这个错误:A PRIMARY KEY must include all columns in the table’s partitioning function。意思是说分区的字段必须是要包含在主键当中。
 可以使用PRIMARY KEY (id,xxx)来将多个字段作为主键。在做分区表时,选择分区的依据字段时要谨慎,需要仔细斟酌这个字段拿来做为分区依据是否合适,这个字段加入到主键中做为复合主键是否适合。
 
(2) list  分区-- 语法

create table <table> (
    // 字段
) ENGINE=数据库引擎  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1

partition by LIST (分区字段或者基于该字段的返回的整数值的表达式) (
  partition <分区名称> values IN (Value1,Value2, Value3),
  ...
  partition <分区名称> values IN (Value4, Value5),
);

(3) 关于 hash 和 key 分区这里不做介绍.........


六、管理分区

mysql提供了添加、删除、重定义、合并、拆分分区的命令,这些操作都可以通过alter table 命令来实现

-- 删除list或者range分区(同时删除分区对应的数据)
alter table <table> drop partition <分区名称>;
注意: 删除分区的同时会删除数据

-- 取消分区,不删除数据
alter table <table> remove partitioning;

-- range添加新分区
alter table <table> add partition(partition p4 values less than MAXVALUE);
注意:RANGE 的分区方式在加分区的时候,只能从最大值后面加,而最大值前面不可以添加

-- list添加新分区
alter table <table> add partition(partition p4 values in (25,26,28));
02-22 12:12