MySQL-分区表

MySQL-分区表

分区表(于MySQL 5.1引入,v 5.5后可以逐步考虑用于生产环境)

是一个独立的逻辑表,但是底层有多个物理子表组成。实现分区的代码实际上是对一组底层表的句柄对象的封装。

实现分区表的方式

对底层表的封装,意味着索引也是按照分区的子表定义的,而没有全局索引。(所以即使有唯一性索引,在不同子表中可能会有重复数据)

优势
  1. 可以将相关的数据存放在一起。
  2. 如果想一次批量删除整个分区的数据也会变得很方便。

适用场景

  • 表非常大以至于无法全部放在内存中,或者只在表的最后部分有热点数据,其它均是历史数据。

  • 分区表更以维护,可以通过清除整个分区的方式删除大量数据。

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

  • 可以表面某些特殊的瓶颈,如InnoDB的单个索引的互斥访问、ext3文件系统的inode锁竞争。

获取更多分区知识

分区技术的限制

  • 每个表最多只能有1024个分区。
  • MySQL 5.1中,分区表达式必须是整数,或者返回整数表达式。MySQL 5.5中,某些场景可以直接用列来分区。
  • 如果分区字段中有主键或者所以索引的列,那么所有主键列和唯一索引列都必须包含进来。
  • 无法使用外键约束。
  • 对于MyISAM分区表,不能再使用LOAD INDEX INTO CACHE操作。
  • 某些存储引擎不支持分区。
  • 所有分区都必须使用相同的存储引擎。
  • 分区表达式有所限制

分区表的原理

分区表由多个相关的底层表实现,这些底层表也是句柄对象,所以可以直接访问各个分区。

分区表索引知识在各个底层表上各自加上一个完全相同的索引。从存储引擎的角度看,底层表和一个普通表没有区别。

常见的分区技术

  • 根据键值进行分区,来减少InnoDB的互斥量竞争
  • 使用数学模函数来进行分区,然后将数据轮询放入不同的分区。以日期为例,可以对日期做模7运算。
  • 拥有自增id主键列的表,id在某种程度上可以代替创建时间来做最近的热点数据存放:HASH(id DIV 10000),还可以避免因为时间超出范围新增分区的问题。

如何使用分区

单表数据量超大时索引失效

​ 在数据量超大(原书参考值为2012年的机器,10TB数据)的时候,B-Tree索引就无法起作用了(索引文件相比于内存大小要大得多)。除非B-Tree索引覆盖查询,否则数据库服务器根据索引扫描的结果回表查询,如果数据量巨大,将会产生大量随机I/O,随之,数据库的响应时间将不可接受。

​ 同时,当数据量超大时,维护索引的成本非常高。

那么如何解决上述问题,分区是一个良好的解决方案。

将单表分区成数个区域,通过分区函数,可以快速地定位到数据的区域。而且相比于索引,分区不需要额外的数据结构记录每个分区的数据,代价更低。只需要一个简单的表达式就可以指向正确的分区

具体的分区策略

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

​ 可以只是用简单的分区方式存放表,不要任何索引,只要将查询定位到需要的大致数据位置,通过where条件,将需要的数据限制在少数分区中,则效率是很高的。WARNNING:查询需要扫描的分区个数限制在一个很小的数量。

  • 索引数据,并分离热点

​ 如果数据有明显的“热点”,可以将热点数据单独放在一个分区,让这个分区的数据能够有机会都缓存在内存中。

分区存在的问题

  • NULL值会使分区过滤无效

​ 如果分区表达式的值可以是NULL:第一个分区会使一个特殊分区。以partition by range year(order_date)为例,所有在order_date列为NULL或者非法值的数据都会被放到第一个分区。那么所有的查询在定位分区后都会增加扫描第一个分区。而且如果第一个分区很大的时候,查询的成本会被这个“拖油瓶”分区无情的增加。

​ 创建一个无用的第一分区可以解决这个问题,partition p_nulls values less than (0);

  • 分区列和索引列不匹配

​ 对于分区列和索引列不匹配的查询,虽然查询能够使用索引,但是无法通过分区定位到目标数据的分区(也就是数据分布相对更加分散),需要遍历每个分区内的索引,除非查询中的条件同时也包含分区条件。所以期望分区条件范围被热门查询索引所包含。

  • 选择分区的成本可能很高

​ 对于范围分区技术,需要适当限制分区的数量,否则对于大量数据批量导入的场景,选择分区的成本过高。对于大多数系统,100个左右的分区是没有问题的。

  • 打开并锁住所有底层表的成本可能很高

  • 维护分区的成本可能很高

    新增或者删除分区的维护操作速度很快。但是重组分区或者类似ALTER语句的操作,需要复制数据->创建临时表->复制数据->提出原分区,成本可能很高。

©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

推荐阅读更多精彩内容

  • 1.分区表的原理 分区表是由多个相关的底层表实现,这些底层表也是由句柄对象表示,所以我们也可以直接访问各个分区,存...
    CXYMichael阅读 1,557评论 0 0
  • Mysql在创建表时使用partition by子句定义每个分区存放的数据,将数据安装一个较粗的刻度分在不同的表中...
    liuwj的ing阅读 635评论 0 2
  • mysql高级之表分区 下列说明为个人见解,欢迎交流指正。 1、表分区简介 1.1 问题概述 问题引出:假设一个商...
    道无虚阅读 33,468评论 1 19
  • 人这辈子,活着不容易。短的是人生,长的却是磨难。 前半生,懵懵懂懂;后半生,忙忙碌碌。回头看这一生,百感交集。 一...
    啵迩哆阅读 346评论 0 0
  • 你的身边的环境成就了你的思维格局和行动。 Zena因为选择的问题,最近有点漂浮,有点不知所措。人人都害怕在选...
    从心印心阅读 274评论 0 0