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列表⾥，这是为了确保主键的效率