Mysql5.7 分区

一、什么是数据库分区？

数据库分区是一种物理数据库设计技术，主要目的是在特定的sql操作中减少数据的读写的总量以及减少响应时间。

二、分区主要是两种形式 //这里一定要注意行和列的概念(row是行，column是列)

① 水平分区 ，表的不同行可以分配给不同的物理分区

水平分区(Horizontal Partitioning) 这种形式分区是对表的行进行分区，通过这样的方式不同分组里面的物理列分割的数据集得以组合，从而进行个体分割(单分区)或集体分割(1个或多个分区)。所有在表中定义的列在每个数据集中都能找到，所以表的特性依然得以保持。

② 垂直分区 其中一个表的不同列被分配到不同的物理分区 5.7的mysql不支持垂直分区

举个简单例子：一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，这时候就要把这些不经常使用的text和BLOB了划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。

三、 mysql5.7分区类型 (水平分区)

① range 范围分区 按范围分区的表的分区方式是，每个分区都包含分区表达式值位于给定范围内的行

CREATE table emp(
	id int NOT NULL auto_increment,
	store_id int not null,
	primary key(id)
)
partition by range (id) (  ##按主键id分区
	partition p0 values less than(6),  ## id小于6的分区到p0 不是 小于等于
	partition p1 values less than(11)  ## id小于11的分区到p1
)

当我们建立带有分区表以后，在我们的数据目录datadir进入表的目录 就可以看到 有分区的标识

当我们插入数据id = 1后，这时id=1的数据就进入了p0的分区，我们可以通过explain查看

 当我们插入数据id = 6的则进入了p1的分区

 这里需要注意的是：当我们插入id=11时，则会报错提示，我们没有建立11范围的分区，所以我们在建立分区的时候，应该把分区的范围都规划好。

注意：当我们设置分区时，必须要注意的是，分区的列必须是整数类型，如果表中存在主键和唯一索引，必须在分区的列，否则会报错；如果需要用store_id为分区的字段，这个例子可以建立联合主键primary key(id, store_id) 可以查看mysql5.7官方文档怎么说明的

特别提醒：这里我们加入需要把id的值设置为唯一的，因为上面的primary key(id, store_id）这样设置的话必须是id和store_id一起才是唯一的

如果还是要id是唯一的，且分区字段为store_id有两种做法：

a) 把id设置成字符串char36位的，我们这里保存uuid,这样始终id都是唯一的，但是这样的话id就不好看了，而且存储的字节比较长。

b) 我们建立一个t2表，专门来生成id

create table t2( id bigint not null auto_increment, primary. key(id) );

然后我们每次通过事务去提交, 或者这里写在触发器里面也可以

begin;
INSERT INTO t2(null);
INSERT INTO emp2(LAST_INSERT_ID(), '10');
COMMIT;

② LIST 分区 

CREATE TABLE h2 (
		 c1 INT,
		 c2 INT
	 )
	 PARTITION BY LIST(c1) (
		 PARTITION p0 VALUES IN (1, 4, 7),  ##c1 IN () 括号里面的值就会存在p0
		 PARTITION p1 VALUES IN (2, 5, 8)
	 );

③ HASH 分区

CREATE TABLE employees (
    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 HASH(store_id) ##具体每行记录会到那个分区会根据hash算法来实现
PARTITIONS 4;

 ④ key 分区

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

四、 查看元数据

 怎么查看数据库中的分区元数据库了？

 我们可以在information_schema数据库中的partitions表查看， 可以查看对应的数据库、表名、分区名称等信息。

 五、 分区的存储设置

① 查看是否支持show variables like 'innodb_file_per_table';  没有则在my.cnf中配置innodb_file_per_table = 1

在创建分区表的时候，指定data directory = ''

CREATE table emp101(
    id int NOT NULL auto_increment,
    create_time datetime,
    primary key(id, create_time)
)
partition by range (YEAR(create_time)) (
    partition p0 values LESS THAN (2020)  ##根据时间 2020年以前的数据放到下面的目录
    DATA directory = '/mdata/p1/data0',

    partition p1 values less than (2021)  ##根据时间 2021年以前的数据放到下面的目录
    DATA directory = '/mdata/p1/data1'
)

 六、分区的管理

① 添加分区 上面的例子，我现在需要添加一条 2022年以前的数据，该怎么操作了？

ALTER TABLE emp101 ADD PARTITION (PARTITION p2 VALUES LESS THAN (2022) DATA DIRECTORY = '/mdata/p1/data2');  添加22年以前的数据存放位置

ALTER TABLE emp101 ADD PARTITION (PARTITION p2 VALUES LESS THAN (2022) DATA DIRECTORY = '/mdata/p1/data2'); 

 还要一个情况，我们的rand分区能不能设置其他的情况，因为在上面的emp101中大于2022年的数据是插入不进去的。那我们就可以执行

alter TABLE emp101 ADD PARTITION (PARTITION p3 VALUES LESS THAN MAXVALUE DATA DIRECTORY = '/mdata/p1/data3' ); 把大于2022年的数据放入到分区p3里面。

② 删除分区

ALTER TABLE emp101 DROP PARTITION p2; ## 这种删除会直接把数据也删除的

如果希望删除所有分区中的所有数据，同时保留表定义及其分区方案，请使用该TRUNCATE TABLE语句

但是我们现在需要删除分区p2 但是保留数据该怎么做了？可以用REORGANIZE PARTITION命令，将下面的p0分区分解成下面两个n0和n1分区，下面只是一个简单的例子。

ALTER TABLE members
    REORGANIZE PARTITION p0 INTO (
        PARTITION n0 VALUES LESS THAN (1970),
        PARTITION n1 VALUES LESS THAN (1980)
);