mysql分区表

问题：如何对mysql的数据表进行分区，生成分区表？

解决：分区类型：
1、hash分区

基于给定的分区个数，将数据分配到不同的分区，HASH分区只能针对整数进行HASH，对于非整形的字段只能通过表达式将其转换成整数。

2、range分区

最为常用，基于属于一个给定连续区间的列值，把多行分配给分区。最常见的是基于时间字段. 基于分区的列最好是整型，如果日期型的可以使用函数转换为整型。

3、list分区

LIST分区和RANGE分区类似，区别在于LIST是枚举值列表的集合，RANGE是连续的区间值的集合。

4、key分区

KEY分区其实跟HASH分区差不多，不同点如下：
· KEY分区允许多列，而HASH分区只允许一列。
· 如果在有主键或者唯一键的情况下，key中分区列可不指定，默认为主键或者唯一键，如果没有，则必须显性指定列。
· KEY分区对象必须为列，而不能是基于列的表达式。
· KEY分区和HASH分区的算法不一样，PARTITION BY HASH (expr)，MOD取值的对象是expr返回的值，而PARTITION BY KEY (column_list)，基于的是列的MD5值。

方法：
创建分区：

hash分区
create talbe 'login_log' (
    'user_id' int(10) unsigned not null,
    'login_time' timestamp not null,
    'login_ip' int(10) unsigned not null,
    'login_type' tinyint(4) not null
) engine = innodb
partition by hash(user_id)
partition 4;

range分区
create talbe 'login_log' (
    'user_id' int(10) unsigned not null,
    'login_time' timestamp not null,
    'login_ip' int(10) unsigned not null,
    'login_type' tinyint(4) not null
) engine = innodb
partition by range (user_id) (
    partition p0 values less than (10000),  # 0~9999
    partition p1 values less than (20000),
    partition p2 values less than maxvalue
);   # >20000

create talbe 'login_log' (
    'user_id' int(10) unsigned not null,
    'login_time' datetime not null,
    'login_ip' int(10) unsigned not null,
    'login_type' tinyint(4) not null
) engine = innodb
partition by range (year(login_time)) （
    partition p0 values less than (2018),  # 2018年前的
    partition p1 values less than (2019),  # 2018年的
    partition p2 values less than maxvalue
）;

list分区
create talbe 'login_log' (
    'user_id' int(10) unsigned not null,
    'login_time' timestamp not null,
    'login_ip' int(10) unsigned not null,
    'login_type' tinyint(4) not null
) engine = innodb
partition by range (login_type) (
    partition p0 values in (1, 2, 3),
    partition p1 values in (4, 5)
); 

增加分区

alter table login_log add partition (partition p4 values less than (2020));
如果报错：MAXVALUE can only be used in last partition definition
alter table login_log reorganize partition pmore into (partition p4 values less than (2021), partition pmore values less maxvalue);

删除分区

alter table login_log drop partition p0;

交换分区

mysql5.7以上支持
新建表 arch_login_log
create talbe 'arch_login_log' (
    'user_id' int(10) unsigned not null,
    'login_time' datetime not null,
    'login_ip' int(10) unsigned not null,
    'login_type' tinyint(4) not null
) engine = innodb;
alter table login_log exchange partition p1 with table arch_login_log;
迁移完分区后需要删除
alter table login_log drop partition p1;

对现有表进行分区

alter table login_log partition by range(year(login_time)) (
    partition p1 values less than (2018),
    partition p2 values less than (2019),
    partition p3 values less than maxvalue
);

查询表的分区

select table_name, partition_name, partition_description, table_rows from information_schema.partitions where table_name='login_log' and table_schema='wenzhang';