mysql之表分区
1. 什么是分区
分区呢就是把一张表的数据分成N多个区块,这些区块可以在同一个磁盘上,也可以在不同的磁盘上,一张大表进行分区后,他还是一张表,但是他存放数据的区块变多了。
如:某用户表的记录超过了600万条,那么就可以根据入库日期将表分区,也可以根据所在地将表分区。当然也可根据其他的条件分区。
2. 分区类型
· RANGE分区:基于属于一个给定连续区间的列值,把多行分配给分区。
· LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
· HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
· KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
3. 建分区表
建 hash 分区表
-- hash分区: 我们要插入一条数据,不需要指定分区,MySQL会自动帮我们处理
create table user
(
id int auto_increment,
name varchar(20) null,
age int null,
salary int null,
remark varchar(200) null,
work_time datetime null,
constraint user_pk
primary key (id)
)
partition by hash ( id ) partitions 5;
INSERT INTO user ( name, age, salary, remark,work_time) VALUES ('小红', 12, 1000, '打杂','2011-05-01 12:00:00');
INSERT INTO user ( name, age, salary, remark,work_time) VALUES ('小明', 18, 1800, '管理员','2011-05-02 12:00:00');
INSERT INTO user ( name, age, salary, remark,work_time) VALUES ('小青', 21, 2100, '前台','2011-05-03 12:00:00');
INSERT INTO user ( name, age, salary, remark,work_time) VALUES ('小钢', 23, 3100, '保安','2011-05-04 12:00:00');
建range分区表
-- 范围分区(RANGE)
-- 根据分区键的范围存入不同的分区中,多个分区间键要连续,但不能重叠。
-- 分区键一般是日期或是时间类型,所有查询语句中都要包括分区键(避免跨分区查询),建议定期按分区范围清理历史数据
create table user2
(
id int auto_increment,
name varchar(20) null,
age int null,
salary int null,
remark varchar(200) null,
work_time datetime null,
constraint user2_pk
primary key (id,work_time)
)
partition by range (DAYOFYEAR( work_time) ) (
partition p1 values less than (DAYOFYEAR('2011-05-01')),
partition p2 values less than (DAYOFYEAR('2011-05-02')),
partition p3 values less than (DAYOFYEAR('2011-05-03')),
partition p4 values less than (DAYOFYEAR('2011-05-04')),
partition p5 values less than MAXVALUE
);
INSERT INTO user2 ( name, age, salary, remark,work_time) VALUES ('小红', 12, 1000, '打杂','2011-05-01 12:00:00');
INSERT INTO user2 ( name, age, salary, remark,work_time) VALUES ('小明', 18, 1800, '管理员','2011-05-02 12:00:00');
INSERT INTO user2 ( name, age, salary, remark,work_time) VALUES ('小青', 21, 2100, '前台','2011-05-03 12:00:00');
INSERT INTO user2 ( name, age, salary, remark,work_time) VALUES ('小钢', 23, 3100, '保安','2011-05-04 12:00:00');
4. 查看分区信息
-- 查看是否为分区别
show table status where name = 'user2';
-- 查看表的分区信息
show create table user2;
-- 查看表具体的分区信息
select * from information_schema.PARTITIONS where TABLE_NAME = 'user2'
-- 分析sql 扫描了哪些分区,及他们是如何使用的
explain partitions
select * from user2 where work_time < '2020-02-01';
5. 分区操作
-- 删除分区及分区数据 (数据丢失)
alter table user2 drop partition p5;
-- 增加分区
alter table user2 add partition (partition p5 values less than (DAYOFYEAR('2021-06-01')));
alter table user2 add partition (partition p6 values less than Maxvalue );
-- 分解分区,将p6 分区进行分割成 p6/p7 ,不丢失数据
alter table user2 reorganize partition p6 into
(
partition p6 values less than (dayofyear('2021-07-01')),
partition p7 values less than MAXVALUE
);
-- 合并3个分区 (数据不丢失)
alter table user2 reorganize partition p1,p2,p3 into
(partition p1 values less than (123));
-- 指定分区表分区进行查询
6. 注意事项
1. 单表或者单分区的数据一千万行,只要没有特别大的索引,没必要分区
2. 分区也不要提前预留太多,在使用之前预先创建即可。比如,如果是按月分区,每年年底时再把下一年度的 12 个新分区创建上即可。对于没有数据的历史分区,要及时的 drop 掉。
3.若表有primary key或unique key,则分区表的分区列必须包含在primary key或unique key列表⾥,这是为了确保主键的效率