2、分区表

什么是分区表

逻辑上表现一张表，与普通表进行的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"