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