MySQL分区表的使用

确认MySQL服务器是否支持分区表

SHOW PLUGINS;

需要有上图红色标记这个参数

MySQL分区表的特点

在逻辑上为一个表，在物理上存储在多个文件中

CREATE TABLE `user_login_log`(
`user_id` INT(0) UNSIGNED NOT NULL COMMENT '用户登录id',
`login_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '用户登录时间',
`login_ip` INT(10) UNSIGNED NOT NULL COMMENT '用户登录ip',
`login_type`TINYINT(4) NOT NULL COMMENT '登录类型'
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='用户登录日志表'
PARTITION BY HASH(user_id)
    PARTITIONS 4

非分区表中包含的文件

user_login_log.frm 结构文件
user_login_log.ibd 数据文件

分区表中包含的文件

user_login_log.frm 结构文件
user_login_log#P#p0.ibd 数据文件
user_login_log#P#p1.ibd 数据文件
user_login_log#P#p2.ibd 数据文件
user_login_log#P#p3.ibd 数据文件

按HASH分区

HASH分区的特点

• 根据MOD(分区键,分区数)的值把数据行存储到表的不同分区中
• 数据可以平均的分布到各个分区中
• HASH分区的键值必须是一个INT类型的值，或是通过函数可以转为INT整型

按范围分区（RANGE）

RANGE分区特点

• 根据分区键值的范围把数据行存储到表的不同分区中
• 多个分区的范围要连续，但是不能重叠
• 默认情况下使用VALUES LESS THAN 属性，即每个分区不包括指定的那个值

CREATE TABLE `user_login_log`(
`user_id` INT(0) UNSIGNED NOT NULL COMMENT '用户登录id',
`login_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '用户登录时间',
`login_ip` INT(10) UNSIGNED NOT NULL COMMENT '用户登录ip',
`login_type`TINYINT(4) NOT NULL COMMENT '登录类型'
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='用户登录日志表'
PARTITION BY RANGE (user_id)
    PARTITION p0 VALUES LESS THAN(10000), //0-9999
    PARTITION p1 VALUES LESS THAN(20000),
    PARTITION p2 VALUES LESS THAN(30000),
    PARTITION p3 VALUES LESS THAN MAXVALUE;	//所有大于30000的数(需要注意 )

RANGE分区的适用场景

• 分区键为日期或是时间类型
• 所有查询中都包括分区键
• 定期按分区范围清理历史数据

LIST分区

LIST分区特点

• 按分区键取值的列表进行分区
• 同范围分区一样，各分区的列表值不能重复
• 每一行数据必须能找到对应的分区列表，否则数据插入失败

CREATE TABLE `user_login_log`(
`user_id` INT(0) UNSIGNED NOT NULL COMMENT '用户登录id',
`login_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '用户登录时间',
`login_ip` INT(10) UNSIGNED NOT NULL COMMENT '用户登录ip',
`login_type`TINYINT(4) NOT NULL COMMENT '登录类型'
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='用户登录日志表'
PARTITION BY LIST (login_type)
    PARTITION p0 VALUES in (1,3,5,7,9),
    PARTITION p1 VALUES in (2,4,6,8),

查看分区表数据

SELECT
	table_name,
	partition_name,
	partition_description,
	table_rows,
FROM
	information_schema.`PARTITIONS`
WHERE table_name = 'user_login_log';

添加分区

ALTER TABLE user_login_log ADD PARTITION (PARTITION p4 VALUES LESS THAN(2018));

删除分区

ALTER TABLE user_login_log DROP PARTITION p0 

历史数据归档分区迁移

• 只支持5.7以上的版本
• 先创建一个结构一样的非分区表

ALTER TABLE user_login_log exchange PARTITION p1 WITH TABLE temp_user_login_log

使用分区表注意事项

• 结合业务场景选择分区键，避免跨分区查询
• 对分区表进行查询最好在WHERE从句中包含的分区键
• 具有主键或唯一索引的表，主键或唯一索引必须是分区键的一部分