MySQL之分区

1.简介

分区是指根据一定的规则，数据库把一个表分解成多个更小的、更容易管理的部分。
逻辑上只有一个表或一个索引，但实际上这个表可能由数10个物理分区对象组成，每个分区都是一个独立的对象，可以独立处理，可以作为表的一部分进行处理。

就是将一张表(数据量非常大的表)按照一定的规则划分称几块，分开管理

2.分区的优点

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

3.分区的支持

通过：show variables like ‘%partition%’;查看当前的MySQL是否支持分区

结果为YES表示当前MySQL支持分区

4.分区的类型

1. RANGE 分区：基于一个给定连续区间范围，把数据分配到不同的分区。
2. LIST 分区：类似RANGE 分区，区别在LIST跟去是基于枚举出的值列表分区，RANGE 是基于给定的连续区间范围分区。
3. HASH 分区：基于给定的分区个数，把数据分配到不同的分区
4. KEY 分区：类似于HASH分区。

5.创建分区需要注意的事项

1. 当当前的表中没有主键的时候，分区类型就不能包含主键
2. 当当前表中存在主键的时候，分区的时候需要带有主键

6.RANGE分区

1. 创建RANGE分区

-- 创建当前的range分区，使用主键id方式创建分区
CREATE TABLE t_range_part(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL,
age INT
)
PARTITION BY RANGE (id)(
    PARTITION a1 VALUES LESS THAN (5),
    PARTITION a2 VALUES LESS THAN (10),
    PARTITION a3 VALUES LESS THAN (15),
    PARTITION a4 VALUES LESS THAN (20),
    PARTITION a5 VALUES LESS THAN MAXVALUE
);

2. 添加数据

INSERT INTO t_range_part(`name`,age)
VALUES('admin',20),('root',21),('guest',22),('lisi',18),('wangwu',19),('zhaoliu',21);

3. 查看当前分区

-- 查看当前分区中数据
SELECT PARTITION_NAME,PARTITION_ORDINAL_POSITION,PARTITION_DESCRIPTION,TABLE_ROWS
FROM information_schema.`PARTITIONS`
WHERE  TABLE_NAME='t_range_part';

4. 删除分区

ALTER TABLE t_range_part DROP PARTITION a5

5. 添加分区

alter table t_range_part add partition (partition a5 values less than (50));

6. 合并分区

alter table t_range_part reorganize  PARTITION  a1,a2,a3 into(
   partition a1 VALUES less than (15)
);

7. 拆分分区

-- 拆分分区
alter table t_range_part reorganize partition a5 into(
   partition a2 values less than (25),
   partition a3 values less than (50)
);

8. 添加不存在于分区的数据
   需要添加default分区,就是添加的id不在分区的范围之内

ALTER TABLE t_range_part ADD PARTITION (PARTITION a_default VALUES LESS THAN MAXVALUE);

此时添加的数据就会落入默认的分区内

7.List分区

List分区是建立离散的值列表告诉数据库特定的值属于哪个分区，List分区在横夺方面类似RANGE 分区，区别在LIST分区是从属于一个枚举列表的值的集合，RANGE分区是从属于一个连续区间值得集合。

1. 创建LIst分区

CREATE TABLE t_list_part(
idCard VARCHAR(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`level` INT NOT NULL COMMENT '用户等级'
)
PARTITION BY LIST (LEVEL)(
    PARTITION p0 VALUES IN(1,2,3,4),
    PARTITION p2 VALUES IN(5,6,7,8),
    PARTITION p3 VALUES IN(9,10,11,12)
);

2. 添加数据

INSERT INTO t_list_part (idCard,`name`,`level`)
VALUES ('0001','admin',12),('0002','root',8),('0003','guest',3),('0004','user',6);

3. 查看分区数据
   
4. 添加不再分区内的数据

insert into  t_list_part (idCard,`name`,`level`)
values('0005','person',18);

结果：直接报错。

5. 添加分区

ALTER TABLE t_list_part ADD PARTITION (PARTITION p4 VALUES IN (13,14,15,16));

6. 删除分区

ALTER TABLE t_list_part DROP PARTITION p4;

7. 合并和拆分分区跟上面RANGE一致

8.Columns分区

1. 创建Columns分区
   语法：

CREATE TABLE 表名(....)
	PARTITION BY RANGE COLUMNS(列名,列名2)(
	PARTITION 分区名1 VALUES LESS THAN (列名1中的最大值,列名二的最大值-1),
	PARTITION 分区名2 VALUES LESS THAN (列名1中的最大值,列名二的最大值-1),
	. ... 
	)

CREATE TABLE t_columns_part(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL,
age INT NOT NULL
)
PARTITION BY RANGE COLUMNS (id,age)(
    PARTITION p1 VALUES LESS THEN (0,10),
    PARTITION p1 VALUES LESS THEN (10,10),
    PARTITION p1 VALUES LESS THEN (10,20),
)

9.Hash分区

HASH 分区主要用来分散点，确保数据在确定的分区中尽可能地分布均匀。

MySQLzhon 支持两种HASH分区：常规HASH分区和线性HASH分区

• 常规地hash分区使用地时取模算法
• 线性分区使用地是一个线性地2的幂的运算法则

1. 创建常规的hash分区

CREATE TABLE t_hash_part(
   id INT NOT NULL,
   `name` VARCHAR(20) NOT NULL,
   age INT NOT NULL
)
PARTITION BY HASH (id) --指定当前的hash使用id,这里必须是整数类型
PARTITIONS 4; --设置分区大小为4

2. 创建线性的hash分区

CREATE TABLE t_liner_hash_part(
   id INT NOT NULL,
   `name` VARCHAR(20) NOT NULL,
   age INT NOT NULL
)
PARTITION BY LINEAR HASH (id) --指定当前的hash使用id,这里必须是整数类型
PARTITIONS 4; --设置分区大小为4

3. 添加数据

insert into t_hash_part
values(1,'admin',18),(3,'admin',19),(4,'admin',20),(6,'admin',22);

4. 查看分区

SELECT PARTITION_NAME,PARTITION_ORDINAL_POSITION,PARTITION_DESCRIPTION,TABLE_ROWS
FROM information_schema.`PARTITIONS`
WHERE  TABLE_NAME='t_hash_part';

5. 修改分区

-- 重新定义分区大小
alter table 表名 coalesce partition 分区大小;

注意：coalesce 不能用来增加分区，否者会报错

6. 添加分区

alter table 表名 add partition partitions 8;

10.创建key分区

按照Key进行分区非常类似按照HASH进行分区，只不过HASH分区允许使用用户自定义的表达式，而Key不允许使用用户自定义的表达式，需要使用MySQL服务器提供的HASH函数。

1. 创建key分区

CREATE TABLE t_key_part(
   id INT NOT NULL,
   `name` VARCHAR(20) NOT NULL,
   age INT NOT NULL
)
PARTITION BY KEY(id) --指定当前的hash使用id,这里必须是整数类型
PARTITIONS 4; --设置分区大小为4

在创建Key分区的时候如果没有指定列名，会选择主键作为分区键，如果没有主键的话会选择非空唯一键(作为唯一键必须是非空的)作为分区键。

2. 其他操作于上面的hash分区一致

11.子分区

子分区是分区表中对每个分区的再次分割，又被称为复合分区。

1. 创建子分区

CREATE TABLE 表名(....)
	PARTITION BY RANGE(列名)   -- 根据列名进行RANGE分区
	SUBPARTITION BY HASH(列名) -- 根据列名进行子HASH分区
	SUBPARTITIONS 2			   -- 子分区的大小为2
	(
		PARTITION 子分区名1 VALUES LESS THAN (区间1), -- 这里是RANGE分区 
		PARTITION 子分区名2 VALUES LESS THAN (区间2),
		PARTITION 子分区名3 VALUES LESS THAN MAXVALUE
	)

这里标识当前的分区有三个分区使用RANGE 分区，其中三个分区中具有两个子分区使用(HASH分区)

12.总结

1.MySQL中的分区有四种：RANGE\LIST\HASH\KEY

2.RANGE 分区中使用 values less then,LIST中使用values in ,hash和key中只要指定partitions 区间大小

3.分区中还可以添加子分区

4.可以通过information_schema中的PARTITIONS表获得当前分区的信息

以上纯属个人见解，如有问题请联系本人！