MySQL分区介绍

概述

1. MySQL 5.1版本开始支持分区，支持大部分存储引擎（如 MyISAM、InnoDB、Memory等），不支持 MERGE、CSV 和 FEDERATED 等存储引擎；
2. 同一分区表的所有分区必须使用同一存储引擎；
3. 分区适用于一个表的所有数据和索引，不能只对其一分区；

作用

1. 相比单个磁盘或文件系统，可以存储更多数据；
2. 优化查询，在 where 条件语句包含分区条件时可以只扫描一个或多个分区来提高查询效率，5.7版本后可以直接在没有 where 条件后查询指定分区数据；

   SELECT * FROM t PARTITION(p0, p1);
   

3. 可以通过删除整个分区来快速删除过期或不需要的数据；
4. 跨多个磁盘来分散数据查询，以获得更大的查询吞吐量；
5. 记录热点分区数据，提高查询效率；

分区类型

MySQL主要有 RANGE、LIST、COLUMNS、HASH、KEY 等分区，分区下面可以继续分子分区；
RANGE、LIST 和 HASH 分区要求分区键必须是 INT 类型或者返回 INT 类型的表达式，KEY 和 COLUMNS 可以使用除 BLOB 和 TEXT 之外的类型；
分区必须使用分区表的主键或唯一索引进行分区，未设置主键时可以使用其他字段分区；

1. RANGE
   利用字段取值范围将数据分成分区，区间连续并不重叠，使用 VALUES LESS THAN 操作符进行分区定义；
   NULL 作为最小值；

   CREATE TABLE user (
   	id int not null,
   	name varchar(50),
   	age int not null
   )
   PARTITION BY RANGE (age) (
   	- 小于 10
   	PARTITION p0 VALUES LESS THAN (10),
   	- 10 至 19
   	PARTITION p0 VALUES LESS THAN (30),
   	PARTITION p0 VALUES LESS THAN (50),
   	- 大于等于 50
   	PARTITION p0 VALUES LESS THAN MAXVALUE
   );
   

2. LIST
   类似 RANGE 分区，基于枚举值列表集合进行分区，不存在枚举列表的值插入报错；
   NULL 必须设置为枚举值才能插入数据；

   CREATE TABLE goods (
   	id int not null,
   	type int not null
   )
   PARTITION BY LIST(type) (
   	PARTITION p0 VALUES IN (1, 3, 5),
   	PARTITION p0 VALUES IN (2, 4, 6),
   	PARTITION p0 VALUES IN (10)
   );
   

3. COLUMNS
   下分 RANGE COLUMNS 和 LIST COLUMNS 分区，支持整数、日期时间和字符串等类型作为分区键；
   相比 RANGE 和 LIST 分区，COLUMNS 支持多列（多个字段分区，5.7 版本支持多列但不支持表达式）;

   CREATE TABLE user (
   	id int not null,
   	age int not null,
   	height int not null
   )
   PARTITION BY RANGE COLUMNS(age, height) (
   	PARTITION p1 VALUES LESS THAN (10, 120),
   	PARTITION p1 VALUES LESS THAN (10, 130),
   	PARTITION p1 VALUES LESS THAN (20, 170),
   	PARTITION p1 VALUES LESS THAN (20, MAXVALUE),
   	PARTITION p1 VALUES LESS THAN (MAXVALUE, MAXVALUE)
   );
   

4. HASH
   分散热点读，确保在预先已知个数的数据在分区中尽可能分布均匀，使用散列函数进行分区；
   分为常规 HASH 分区和线性 HASH 分区；
   NULL 作为 0 值；
   常规 HASH 分区

    数据平均分布，提高查询效率；
    分区管理代价太大，在增加新分区或合并分区需要重新计算各数据分区编号；
    基于取模运算；
    		```
    CREATE TABLE user(
    	id int not null,
    	age int not null
    )
    - 分区编号 = MOD(age, 4)
    PARTITION BY HASH(age)  PARTITIONS 4;
    ```
   

   线性 HASH 分区
   基于一个线性的 2 的幂的运算方程；
   分区维护效率高，但数据分布不均衡；

    CREATE TABLE user(
    	id int not null,
    	age int not null
    )
    - 分区编号 = MOD(age, 4)
    PARTITION BY HASH(age)  PARTITIONS 4;
   

5. KEY
   类似 HASH 分区，不过 KEY 基于 MySQL 提供的 HASH 函数，其支持除 BLOB 和 Text 外其他类型的列作为分区键；
   不指定分区键时，默认选择使用主键作为分区键，没有主键使用非空唯一键；
   NULL 作为 0 值；

   CREATE TABLE user(
   	id int primary key,
   	name varcahr(50) not null
   )
   PARTITION BY KEY() PARTITIONS 4;
   

分区管理

1. 删除分区

   alter table user drop partition p0;
   

2. 增加分区

   -  RANGE，只能从最大端增加
   alter table user add partition (partition p4 values less than (70));
   -  LIST，分区枚举值不能重复
   alter table user add partition (partition p5 values in (8, 9));
   - HASH，减少分区数量，2 为减少至 2 个
   alter table user coalesce partition 2;
   - HASH，增加分区，3 为增加数量
   alter table user add partitions 3; 
   

3. 拆分合并分区

   - 拆分
   alter table user reorganize partition p3 into (
   	partition p6 values less than (45),
   	partition p6 values less than (50)
   );
   - 合并
   alter table user reorganize partition p1, p2, p3 into (
   	partition p1 values less than (50)
   );