什么是分区表
逻辑上表现一张表,与普通表进行的sql操作没有任何区别。物理上表现为通过多个文件保存数据文件,普通表是通过一个文件保存数据文件
分区表的影响
行数据保存在不同的磁盘文件中,mysql能同时对多个文件进行读写操作,提高mysql读写性能。
进行跨分区查询时,查询速度比不进行分区慢。进行查询时,可以在where条件上加上分区键筛选条件,避免跨分区扫描
创建分区表
//创建表时指定分区参数
CREATE TABLE `cate` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`cate_name` char(40) DEFAULT NULL,
`x` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB PARTITION by HASH(id) PARTITIONS 4
//PARTITION by 指定分区键
//PARTITIONS 分区个数
存放行数据的文件被拆分成4个文件
注意
- 如果需要建立分区表,只能存在一个主键/或一个唯一键。(主键包含在唯一键除外,可以同时存在主键和唯一,但是这种情况,分区键必须是主键)
- 如果表中存在 唯一键 或 主键,分区键必须是 唯一键或主键的一部分。
分区键 PARTITION by 的 选择
- 对字段进行hash取模后,确定存放到某个文件
HASH(字段),注意字段必须是一个整数,可以使用crc32()函数转成整数
能够尽可能的将数据平分到每一个分区,适合进行等值查询
CREATE TABLE `user_login_log` (
`user_login_log_id` int(10) unsigned primary key AUTO_INCREMENT COMMENT '主键',
`user_account_id` int(10) unsigned NOT NULL default 0 COMMENT '用户账号ID',
`ip` int UNSIGNED NOT NULL DEFAULT 0 COMMENT '登陆IP信息',
`msg` varchar(255) not null DEFAULT '' comment '登陆简要说明' ,
`create_time` datetime not null DEFAULT now() comment '登陆时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户登陆日志'
partition by hash(user_login_log_id)
partitions 4
- 按范围进行分区
数据行按照分区键指定的范围,存储到某个分区。
适合使用日期类型的字段作为分区键,定期删除/归档某段时间的行数据
CREATE TABLE `user_login_log` (
`user_account_id` int(10) unsigned NOT NULL default 0 COMMENT '用户账号ID',
`ip` int UNSIGNED NOT NULL DEFAULT 0 COMMENT '登陆IP信息',
`msg` varchar(255) not null DEFAULT '' comment '登陆简要说明' ,
`create_time` datetime not null DEFAULT now() comment '登陆时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户登陆日志'
partition by range(year(create_time))
(
partition p0 values less than(2015),
partition p1 values less than(2016),
partition p2 values less than(2017),
partition p3 values less than(MAXVALUE)
)
insert into user_login_log (user_account_id,ip,msg,create_time) values (1,INET_ATON('127.0.0.1'),"正常登陆","2014-8-10");
insert into user_login_log (user_account_id,ip,msg,create_time) values (2,INET_ATON('127.2.0.1'),"正常登陆","2014-8-2");
insert into user_login_log (user_account_id,ip,msg,create_time) values (1,INET_ATON('127.6.0.1'),"正常登陆","2019-8-10");
insert into user_login_log (user_account_id,ip,msg,create_time) values (3,INET_ATON('127.0.8.1'),"正常登陆","2015-8-10");
需要删除掉2015年前的日志只需对p0分区进行删除
alter table user_login_log drop partition p0
添加新的分区
alter table user_login_log drop PARTITION p3
alter table user_login_log add PARTITION (partition p3 VALUES less than(2018))
对分区进行归档操作
//创建一个与原表具有相同表结构的表(不能是分区表)
CREATE TABLE `arch_user_login_log` (
`user_account_id` int(10) unsigned NOT NULL default 0 COMMENT '用户账号ID',
`ip` int UNSIGNED NOT NULL DEFAULT 0 COMMENT '登陆IP信息',
`msg` varchar(255) not null DEFAULT '' comment '登陆简要说明' ,
`create_time` datetime not null DEFAULT now() comment '登陆时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户登陆日志'
//使用交换分区进行移动
alter TABLE user_login_log exchange partition p0 with table arch_user_login_log
//删除原表的分区
alter table arch_user_login_log drop partition p0
- 按列表进行分区
CREATE TABLE `user_login_log` (
`user_account_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户账号ID',
`ip` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '登陆IP信息',
`msg` varchar(255) NOT NULL DEFAULT '' COMMENT '登陆简要说明',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '登陆时间',
`status` tinyint DEFAULT 0 comment '账号状态: 0未激活,1正常,2禁止登陆'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户登陆日志'
partition by list (status)
(
partition p0 values IN (1),
partition p1 values in (2),
partition p2 values in (3)
)
查看每个分区保存了多少行数据
查看 information_schema.PARTTIONS表
select PARTITION_NAME,PARTITION_DESCRIPTION,TABLE_ROWS from information_schema.PARTITIONS where TABLE_NAME="user_login_log" and TABLE_SCHEMA="test"