Mysql5.7 分区_天道酬勤-明天会更好_mysql 分区

irpas 02-07 1441

一、什么是数据库分区？

数据库分区是一种物理数据库设计技术，主要目的是在特定的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) );