Mysql高级特性-表分区

我不是女神ヾ 2022-12-13 04:38 194阅读 0赞

## 一 概述 ##

**分区表**是一个独立的逻辑表，但是底层由多个物理子表组成。实现分区的代码实际上是对一组底层表的句柄对象（HandlerObject）的封装。对分区表的请求，都会通过句柄对象转化成对存储引擎  
的接口调用。所以分区对于SQL层来说是一个完全封装底层实现的黑盒子，对应用是透明的，但是从底层的文件系统来看就很容易发现，每一个分区表都有一个使用\#分隔命名的表文件。

分区的目的是将相关数据存放在一起，在创建表时使用partition by子句定义分区数据，这样在使用mysql数据的时候，优化器会根据查询条件选择对应的分区，这样避免了全局数据扫面。

**分区表的作用：**

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

**分区的不足：**

*  一个表最多只能有1024个分区（mysql5.6之后支持8192个分区）
 *  如果分区字段中有主键或者唯一索引列，那么所有主键列和唯一索引列都必须包含进来，如果表中有主键或唯一索引，那么分区键必须是主键或唯一索引。
 *  分区表中无法使用外键约束。
 *  在mysql5.1中分区表达式必须是整数，或者是返回整数的表达式，在5.5之后，某些场景可以直接使用字符串列和日期类型列来进行分区（使用varchar字符串类型列时，一般还是字符串的日期作为分区）。

**mysql分区与分表：**  
分表是将一张大表按照一定的规则分解成具有独立存储空间的多张独立表，称为子表。分区与分表的不同在于分表将大表分为若干个实体表，而分区是将数据数据分段划分在多个位置存放，可以放在同一块磁盘也可以放在不同机器，分区后表面上还是一张表，但是数据散列到多个位置，读的时候由db自动组织数据。

*  分区与分表都能提高数据查询效率，
 *  访问量很大，数据量也大，使用分表。访问量不大但数据量大，建议使用分区
 *  表分区相对于分表，操作方便，不需要创建子表。分表技术是比较麻烦的，需要手动去创建子表

## 二 分区的实现方式 ##

支持range（范围分区）、list（列表分区）、hash（哈希分区）、key分区

**range分区：**  
把指定的某一个范围的区间值来进行分区，确定的哪些行分配给某一个分区，

CREATE TABLE `user1` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Id',
      `name` varchar(255) NOT NULL COMMENT '名称',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='USER表'
    PARTITION by RANGE(id)
    (PARTITION p1 VALUES LESS THAN (10000) ENGINE = InnoDB,
    PARTITION p2 VALUES LESS THAN (20000) ENGINE = InnoDB,
    PARTITION p3 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)

如果是想按照日期格式分区，可以使用“TO\_DAYS”函数将日期格式转化为整数类型来分区

**list（列表分区）**

CREATE TABLE `user2` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Id',
      `name` varchar(255) NOT NULL COMMENT '名称',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='USER表'
    PARTITION by list(id)
    (PARTITION p1 VALUES IN(1,2,3,4,5),
    PARTITION p2 VALUES IN(6,7,8,9,10))

**hash（哈希分区）**

**key分区**  
KEY分区允许多列，而HASH分区只允许一列。

*  如果在有主键或者唯一键的情况下，key中分区列可不指定，默认为主键或者唯一键，如果没有，则必须显性指定列。  
    \-KEY分区对象必须为列，而不能是基于列的表达式
 *  KEY分区和HASH分区的算法不一样，PARTITION BY HASH (expr)，MOD取值的对象是expr返回的值，而PARTITION BY KEY (column\_list)，基于的是列的MD5值。

CREATE TABLE k1 (
        id INT NOT NULL PRIMARY KEY,    
        name VARCHAR(20)
    )
    PARTITION BY KEY()
    PARTITIONS 2;