分区表

  • 创建分区表
CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
        PARTITION BY RANGE( YEAR(purchased) ) (
            PARTITION p0 VALUES LESS THAN (1990),
            PARTITION p1 VALUES LESS THAN (1995),
            PARTITION p2 VALUES LESS THAN (2000),
            PARTITION p3 VALUES LESS THAN (2005),
            PARTITION p4 VALUES LESS THAN (2010),
            PARTITION p5 VALUES LESS THAN (2015)
        );

应用场景

  • 表非常大以至于无法全部都放在内存中,或者只在表的最后部分有热点数据,其他均是历史数据
  • 分区表的数据更容易维护
    • 批量删除大量数据可以使用清除整个分区的方式
    • 对一个独立分区进行优化、检查、修复等操作
  • 分区表的数据可以分布在不同的物理设备上,从而高效地利用多个硬件设备
  • 可以使用分区表来避免某些特殊的瓶颈
    • innodb的单个索引的互斥访问
    • ext3文件系统的inode锁竞争
  • 可以备份和恢复独立的分区

分区表的限制

  • 一个表最多只能有1024个分区,在5.7版本的时候可以支持8196个分区
  • 在早期的mysql中,分区表达式必须是整数或者是返回整数的表达式,在mysql5.5中,某些场景可以直接使用列来进行分区
  • 如果分区字段中有主键或者唯一索引的列,那么所有主键列和唯一索引列都必须包含进来
  • 分区表无法使用外键约束

原理

select查询

​ 当查询一个分区表的时候,分区层先打开并锁住所有的底层表,优化器先判断是否可以过滤部分分区,然后再调用对应的存储引擎接口访问各个分区的数据

insert操作

​ 当写入一条记录的时候,分区层先打开并锁住所有的底层表,然后确定哪个分区接受这条记录,再将记录写入对应底层表

delete操作

​ 当删除一条记录时,分区层先打开并锁住所有的底层表,然后确定数据对应的分区,最后对相应底层表进行删除操作

update操作

​ 当更新一条记录时,分区层先打开并锁住所有的底层表,mysql先确定需要更新的记录再哪个分区,然后取出数据并更新,再判断更新后的数据应该再哪个分区,最后对底层表进行写入操作,并对源数据所在的底层表进行删除操作

分区表类型

  • 范围分区
    • 根据列值在给定范围内将行分配给分区
  • 列表分区
    • 类似于按range分区,区别在于list分区是基于列值匹配一个离散值集合中的某个值来进行选择
  • 列分区
    • mysql从5.5开始支持column分区,可以认为i是range和list的升级版,在5.5之后,可以使用column分区替代range和list,但是column分区只接受普通列不接受表达式
  • hash分区
    • 基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含myql中有效的、产生非负整数值的任何表达式
  • key分区
    • 类似于hash分区,区别在于key分区只支持一列或多列,且mysql服务器提供其自身的哈希函数,必须有一列或多列包含整数值
  • 子分区
    • 在分区的基础之上,再进行分区后存储

分区表使用

  • 全量扫描数据,不需要任何索引

根据分区规则大致定位需要的数据为止,通过使用where条件将需要的数据限制在少数分区中,这种策略适用于以正常的方式访问大量数据

  • 索引数据,并分离热点

如果数据有明显的热点,而且除了这部分数据,其他数据很少被访问到,那么可以将这部分热点数据单独放在一个分区中,让这个分区的数据能够有机会都缓存在内存中

注意问题

  • null值会使分区过滤无效
  • 分区列和索引列不匹配,会导致查询无法进行分区过滤
  • 选择分区的成本可能很高
  • 打开并锁住所有底层表的成本可能很高
  • 维护分区的成本可能很高

results matching " "

No results matching " "

results matching " "

No results matching " "