美文网首页
MySQL分区表

MySQL分区表

作者: 十毛tenmao | 来源:发表于2019-03-29 00:21 被阅读0次

在最近的项目中,我们需要保存大量的数据,而且这些数据是有有效期的,为了提供查询效率以及快速删除过期数据,我们选择了MySQL的分区机制。把数据按照时间进行分区。

分区类型


  • Range分区:最为常用,基于属于一个给定连续区间的列值,把多行分配给分区。最常见的是基于时间字段. 基于分区的列最好是整型,如果日期型的可以使用函数转换为整型。
  • List分区:LIST分区和RANGE分区类似,区别在于LIST是枚举值列表的集合,RANGE是连续的区间值的集合。
  • Hash分区:基于给定的分区个数,将数据分配到不同的分区,HASH分区只能针对整数进行HASH,对于非整形的字段只能通过表达式将其转换成整数。
  • Key分区:KEY分区其实跟HASH分区差不多,不同点如下:
    • KEY分区允许多列,而HASH分区只允许一列。
    • 如果在有主键或者唯一键的情况下,key中分区列可不指定,默认为主键或者唯一键,如果没有,则必须显性指定列。
    • KEY分区对象必须为列,而不能是基于列的表达式。
    • KEY分区和HASH分区的算法不一样,PARTITION BY HASH (expr),MOD取值的对象是expr返回的值,而PARTITION BY KEY (column_list),基于的是列的MD5值。

分区命令


  • 创建分区
CREATE TABLE `access_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `access_time` datetime NOT NULL,
  PRIMARY KEY (`id`,`access_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (to_days(access_time))
(PARTITION p1 VALUES LESS THAN (to_days(20190101)) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (to_days(20190102)) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (to_days(20190103)) ENGINE = InnoDB) */;

创建后可以看到,每个分区都会对应1个ibd文件


分区表
  • 新增分区
alter table access_log add partition(
    partition p4 values less than (to_days('20190105'))
);
  • 删除分区
alter table access_log drop partition p1;
  • 拆分分区
alter table access_log reorganize partition p4 into(
    -> partition s0 values less than(to_days('20190104')),
    -> partition s1 values less than(to_days('20190105'))
    -> );
  • 合并分区
alter table access_log reorganize partition s0,s1 into ( 
    partition p4 values less than (to_days('20190105')) 
);

注意事项

  • MySQL分区中如果存在主键或唯一键,则分区列必须包含在其中(否则判断主键或唯一时,需要扫描所有分区)
  • 分区字段不能为NULL,要不然怎么确定分区范围呢,所以尽量NOT NULL
  • 最大分区数目不能超过1024
  • 不支持外键
  • 只能对数据表的整型列进行分区,或者数据列可以通过分区函数转化成整型列
  • 分区表影响自增列

常见问题

  • A PRIMARY KEY must include all columns in the table's partitioning function:这样的话判断主键是否唯一就可以在单个分区内部完成,否则就需要跨所有的分区
  • MAXVALUE can only be used in last partition definition:RANGE表分区后不能带MAXVALUE分区,否则无法增加分区。或者就只能重新分区了
alter table access_log partition by range(to_days(access_time))(
partition p1 values less than (to_days('20191202')), 
partition p2 values less than (to_days('20191203')), 
    partition po values less than (maxvalue)
)
  • Table has no partition for value 737425:因为分区的范围没有包含所有可能的记录的值

参考

相关文章

  • MySQL分区表

    确认mysql是否支持分区表 mysql分区表的特点 创建mysql数据表为hash表 常用mysql分区的类型 ...

  • MySQL-分区表

    MySQL-分区表 分区表(于MySQL 5.1引入,v 5.5后可以逐步考虑用于生产环境) 是一个独立的逻辑表,...

  • Mysql 分区表

    MySQL分区表支持RANGE,LIST,HASH,KEY,COLUMNS多种分区算法。 分区表的唯一索引和主键索...

  • MySQL版本

    MySQL版本对比 服务器性能 1.mysql,percona mysql,mariadb均开源,支持分区表,My...

  • pg inherit

    借助表的继承特性PostgreSQL实现了分区表功能,虽然相比Oracle、MySQL的分区表来说其实现过程比较麻...

  • mysql分区表

    首先检查Mysql服务器是否支持分区表: mysql>show plugins 如果显示有partition项,那...

  • hive的严格模式和分区

    相比mysql,hive有严格模式存在,为了不错误扫描整个数据,hive的分区表会将数据分成几个分区。查询分区表中...

  • Mysql 分区表删除

    分区表删除部分分区 使用场景:从 MySQL 5.1 开始,支持分区 创建日志表时建议使用分区方式 在上表的分区表...

  • mysql分区表测试

    mysql分区表测试 mysql部署情况 使用docker-compose在10.xx.xx.1机器进行部署, 端...

  • MySQL的分区、分表、集群、优化

    MySQL的分区 MySQL分区表是在数据库层面,MySQL自己实现的分表功能,在很大程度上简化了分表的难度。物理...

网友评论

      本文标题:MySQL分区表

      本文链接:https://www.haomeiwen.com/subject/hmiqbqtx.html