MySQL-分区表
分区表（于MySQL 5.1引入，v 5.5后可以逐步考虑用于生产环境）
是一个独立的逻辑表，但是底层有多个物理子表组成。实现分区的代码实际上是对一组底层表的句柄对象的封装。
实现分区表的方式
对底层表的封装，意味着索引也是按照分区的子表定义的，而没有全局索引。（所以即使有唯一性索引，在不同子表中可能会有重复数据）
优势
- 可以将相关的数据存放在一起。
- 如果想一次批量删除整个分区的数据也会变得很方便。
适用场景
-
表非常大以至于无法全部放在内存中，或者只在表的最后部分有热点数据，其它均是历史数据。
-
分区表更以维护，可以通过清除整个分区的方式删除大量数据。
-
分区标的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。
-
可以表面某些特殊的瓶颈，如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语句的操作，需要复制数据->创建临时表->复制数据->提出原分区，成本可能很高。
网友评论