***** ## MySQL分区表 ### 分区表的特点 在逻辑上为一个表,在物理上存储在多个文件中 ``` create table `login_log`( login_id int(10) unsigned not null comment '登录用户id', login_time timestamp not null default current_timestamp, login_ip int(10) unsigned not null comment '登录类型' )engine=innodb default charset=utf8 partition by hash(login_id) partitions 4; ``` ### 分区键 分区引入了分区键的概念,分区键用于根据某个区间值、特定值、或者HASH函数值执行数据的聚集,让数据根据规则分布在不同的分区中。 ### 分区类型 * RANGE分区 * LIST分区 * HASH分区 无论那种分区类型,要么分区表上没有主键/唯一键,要么分区表的主键/唯一键都必须包括分区键,也就是说不能使用主键/唯一字段之外的其他字段分区 ### RANGE分区 #### RANGE分区特点 * 根据分区键值的范围把数据行存储到表的不同分区中 * 多个分区的范围要连续,但是不能重叠 * 分区不包括上限,取不到上限值 #### 建立RANGE分区 ~~~ create table `login_log_range`( login_id int(10) unsigned not null comment '登录用户ID', login_time timestamp not null default CURRENT_TIMESTAMP, login_ip int(10) unsigned not null comment '登录ip' )engine=innodb partition by range(login_id)( partition p0 values less than(10000), # 实际范围0-9999 partition p1 values less than(20000), # 实际范围10000-19999 partition p2 values less than(30000), partition p3 values less than maxvalue # 存储大于30000的数据 ); ~~~ #### RANGE分区使用场景 * 分区键为日期或是时间类型 * 经常运行包含分区键的查询,MySQL可以很快的确定只有某一个或某些分区需要扫描,例如检索商品login\_id小于10000的记录数,MySQL只需要扫描p0分区即可 * 定期按分区范围清理历史数据 ### HASH分区 #### HASH分区的特点 * 根据MOD(分区键,分区值)的值把数据行存储到表的不同分区内 * 数据可以平均的分布在各个分区中 * HASH分区的键值必须是一个INT类型的值,或是通过函数可以转为INT类型 #### 如何建立HASH分区表 ~~~ create table `login_log`( login_id int(10) unsigned not null comment '登录用户ID', login_time timestamp not null default CURRENT_TIMESTAMP, login_ip int(10) unsigned not null comment '登录ip' )engine=innodb default charset=utf8 partition by hash(login_id) partitions 4; create table `login_log`( login_id int(10) unsigned not null comment '登录用户ID', login_time timestamp not null default CURRENT_TIMESTAMP, login_ip int(10) unsigned not null comment '登录ip' )engine=innodb default charset=utf8 partition by hash(UNIX_TIMESTAMP(login_time)) partitions 4; ~~~ ### LIST分区 #### LIST分区特点 * 按分区键取值的列表进行分区 * 同范围分区一样,各分区的列表值不能重复 * 每一行数据必须能找到对应的分区列表,否则数据插入失败 #### 建立LIST分区 ~~~ create table `login_log_list`( login_id int(10) unsigned not null comment '登录用户ID', login_time timestamp not null default CURRENT_TIMESTAMP, login_ip int(10) unsigned not null comment '登录ip', login_type int(10) not null )engine=innodb partition by list(login_type)( partition p0 values in(1,3,5,7,9), partition p1 values in(2,4,6,8) ); ~~~ ### 如何选择合适的分区方式 业务场景 1.用户每次登陆都会记录到日志表中 2.用户登录日志保存一年,一年后可以删除 ~~~ create table `login_log_range`( login_id int(10) unsigned not null comment '登录用户id', login_time datetime not null default current_timestamp, login_ip int(10) unsigned not null comment '登录ip' )engine=innodb partition by range(year(login_time))( partition p0 values less than(2015), partition p1 values less than(2016), partition p2 values less than(2017) ); ~~~ 插入数据 ~~~ insert into login_log_range values (1,'2015-01-25',1), (2,'2015-07-25',2), (3,'2015-06-25',3), (4,'2016-03-25',2), (5,'2016-02-25',1); ~~~ 查询表 ~~~ select table_name,partition_name,partition_description,table_rows from information_schema.`partitions` where table_name = 'login_log_range' ~~~ ### 修改分区-添加分区 ~~~ alter table login_log_range add partition (partition p4 values less than(2018)) ~~~ ### 分区删除 ~~~ alter table login_log_range drop partition p0; ~~~ ### 使用分区表的注意事项 * 结合业务场景选择分区键,避免跨分区查询 * 对分区表进行查询最好在where从句中包含分区键 * 具有主键或唯一索引的表,主键或唯一索引必须是分区键的一部分