四、MySQL分区表的使用

1、 什么是分区表

  • 分区表,是指根据一定规则,将数据库中的一张表分解成多个更小的,容易管理的部分。从逻辑上看,只有一张表,但是底层却是由多个物理分区组成。

2、表分区有什么好处

  • 1、分区表的数据可以分布在不同的物理设备上,从而高效地利用多个硬件设备。

  • 2、和单个磁盘或者文件系统相比,可以存储更多数据

  • 3、优化查询。在where语句中包含分区条件时,可以只扫描一个或多个分区表来提高查询效率;涉及sum和count语句时,也可以在多个分区上并行处理,最后汇总结果。

  • 4、分区表更容易维护。例如:想批量删除大量数据可以清除整个分区。

  • 5、可以使用分区表来避免某些特殊的瓶颈,例如InnoDB的单个索引的互斥访问,ext3问价你系统的inode锁竞争等。

3、分区表的限制因素

  • 1、一个表最多只能有1024个分区

  • 2、 MySQL5.1中,分区表达式必须是整数,或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。

  • 3、如果分区字段中有主键或者唯一索引的列,那么多有主键列和唯一索引列都必须包含进来。即:分区字段要么不包含主键或者索引列,要么包含全部主键和索引列。

  • 4、分区表中无法使用外键约束

  • 5、MySQL的分区适用于一个表的所有数据和索引,不能只对表数据分区而不对索引分区,也不能只对索引分区而不对表分区,也不能只对表的一部分数据分区。

4、MySQL分区表的使用前准备

  • 日常工作中,存储日志表使用分区表是个不错的选择。

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

     mysql > SHOW PLUGINS 
    

    • partition ACTIVE 表示支持分区表

5、MySQL分区表的特点

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


  • 非分区表在磁盘中存放在 customer_login_log.frm 原数据信息 / customer_login_log.ibd Innodb数据文件

  • 分区表在磁盘中存放在 customer_login_log.frm / customer_login_log#P#p0.ibd / customer_login_log#P#p1.ibd / customer_login_log#P#p2.ibd / customer_login_log#P#p3.ibd /

  • 按HASH分区的特点

    • 根据MOD(分区键,分区数)的值把数据行存储到表的不同分区中。

    • 数据可以平均的分布在各个分区中。

    • HASH分区的键值必须是一个INT类型的值,或是通过函数可以转化为INT类型。

  • 如何建立HASH分区表


  • RANGE分区表

    • 根据分区键值的范围把数据行存储到表的不同分区中。

    • 多个分区的范围要连续,但是不能重叠。

    • 默认情况下使用values less than 属性,即每个分区不包括指定的那个值。

    • 适用场景

      • 分区键为日期或者是时间类型。

      • 所有查询中都包括分区键,避免跨分区扫描。

      • 定期按分区范围清洗历史数据。

  • LIST分区

    • LIST分区的特点:

      • 按分区键取值的列表进行分区。

      • 同范围分区一样,各分区的列表值不能重复。

      • 每一行数据必须能找到对应的分区列表,否则数据插入失败。

  • 如何建立LIST分区


2、如何为customer_login_log 表分区

  • 该表主要是记录用户登陆的日志,增长很快。用户登陆日志保存一年,一年之后可以删除。

  • 在该业务场景下,最好使用RANGE分区,数据过多,可采用删除分区的方法。


  • 当我们执行插入语句的时候,会报如下的错误,Error : Table has no partition for value 2017

  • 此时是因为没有2017所在的分区,所以插入数据失败,我们需要修改表或者修改插入的数据。

分区表的维护

  • 鉴于分区表的分区都是手动添加的,那么随着时间推移,到了我们统计的分区的截止日期。为了避免插入数据到range分区失败,要做修改并添加分区的计划任务。

  • 手动添加新的分区


  • 分区表删除无效不用的分区操作.如删除2015年之前的分区数据。

  • 对过期数据归档操作

    • 如果使用的Mysql >=5.7 ,归档分区历史数据非常方便。
    • 分区数据归档迁移条件:
      • 1:Mysql >=5.7 。

      • 2:结构相同。

      • 3:归档到的数据表一定要是非分区表。

      • 4:非临时表,不能有外键约束。

      • 5:归档引擎要是: archive。

  • 然后需要手动删除原表中的p2分区。
  • 最后将归档的表改为归档引擎。 alter table arch_customer_login_log engine= archive; 只能进行查询操作,不能进行写操作。

6、总结

  • 结合业务场景选择分区键,避免跨分区查询。

  • 对分区表进行查询最好在where从句中包含分区键。

  • 具有主键或唯一索引的表,主键或唯一索引必须是分区键的一部分。


微信扫码关注java技术栈,获取Java面试题和架构师相关题目和视频。

推荐阅读更多精彩内容