表分区

谁借莪１个温暖的怀抱￠ 2021-11-09 03:20 378阅读 0赞

### 表分区 ###

*  简介
 *  分区的好处
 *  分区的限制和缺点
 *  分区的类型
 *  分区适合的场景

# 简介 #

MySQL在5.1版引入的分区是一种简单的水平拆分，用户需要在建表的时候加上分区参数，对应用是透明的无需修改代码  
对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成，实现分区的代码实际上是通过对一组底层表的对象封装，但对SQL层来说是一个完全封装底层的黑盒子。MySQL实现分区的方式也意味着索引也是按照分区的子表定义，没有全局索引  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_SnVzdGluTmVpbA_size_16_color_FFFFFF_t_70]  
用户的SQL语句是需要针对分区表做优化，SQL条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区，可以通过`EXPLAIN PARTITIONS`来查看某条SQL语句会落在那些分区上，从而进行SQL优化

# 分区的好处 #

*  可以让单表存储更多的数据
 *  分区表的数据更容易维护，可以通过清除整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作
 *  部分查询能够从查询条件确定只落在少数分区上，速度会很快
 *  分区表的数据还可以分布在不同的物理设备上，从而高效利用多个硬件设备
 *  可以使用分区表来避免某些特殊瓶颈，例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争
 *  可以备份和恢复单个分区

# 分区的限制和缺点 #

*  一个表最多只能有1024个分区
 *  如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来
 *  分区表无法使用外键约束
 *  NULL值会使分区过滤无效
 *  所有分区必须使用相同的存储引擎

# 分区的类型 #

*  RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区
 *  LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择
 *  HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式
 *  KEY分区：类似于HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值

# 分区适合的场景 #

*  最适合的场景数据的时间序列性比较强，则可以按时间来分区

CREATE TABLE members (
        firstname VARCHAR(25) NOT NULL,
        lastname VARCHAR(25) NOT NULL,
        username VARCHAR(16) NOT NULL,
        email VARCHAR(35),
        joined DATE NOT NULL
    )
    PARTITION BY RANGE( YEAR(joined) ) (
        PARTITION p0 VALUES LESS THAN (1960),
        PARTITION p1 VALUES LESS THAN (1970),
        PARTITION p2 VALUES LESS THAN (1980),
        PARTITION p3 VALUES LESS THAN (1990),
        PARTITION p4 VALUES LESS THAN MAXVALUE
    );

查询时加上时间范围条件效率会非常高，同时对于不需要的历史数据能很容的批量删除。

*  如果数据有明显的热点，而且除了这部分数据，其他数据很少被访问到，那么可以将热点数据单独放在一个分区，让这个分区的数据能够有机会都缓存在内存中，查询时只访问一个很小的分区表，能够有效使用索引和缓存

另外MySQL有一种早期的简单的分区实现 - 合并表（merge table），限制较多且缺乏优化，不建议使用，应该用新的分区机制来替代

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_SnVzdGluTmVpbA_size_16_color_FFFFFF_t_70]: /images/20211109/9f21b876551b42688ecbd6db9881b01e.png