MySQL 分区简介

MySQL自5.1开始对分区(Partition)有支持。

= 水平分区（根据列属性按行分）=
举个简单例子：一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其中一年的记录。

 水平分区的几种模式：

* Range（范围） –  基于属于一个给定连续区间的列值，把多行分配给分区。例如DBA可以将一个表通过年份划分成三个分区，80年代（1980's）的数据，90年代（1990's）的数据以及任何在2000年（包括2000年）后的数据。

RANGE分区基于一个给定的连续区间范围，早期版本RANGE主要是基于整数的分区。在5.7版本中DATE、DATETIME列也可以使用RANGE分区，同时在5.5以上的版本提供了基于非整形的RANGE COLUMN分区。RANGE分区必须的连续的且不能重叠。使用"VALUES LESS THAN ()" 来定义分区区间，非整形的范围值需要使用单引号，并且可以使用MAXVALUE作为分区的最高值。

* List（预定义列表） –  类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

* Hash（哈希） –  基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。例如DBA可以建立一个对表主键进行分区的表。

* Key（键值） – 上面Hash模式的一种延伸，这里的Hash Key是MySQL自身的哈希函数产生的。

* COLUMNS –  MYSQL5.5开始支持COLUMNS分区，可视为RANGE和LIST分区的一个进化。COLUMNS分区可以直接使用非整型的数据进行分区，分区根据类型直接比较而得到，不需要转化为整型。COLUMNS分区支持以下数据类型：

• 所有的整型类型，如INT,TINYINT,SMALLINT,BIGINT。对FLOAT和DECIMAL不支持
• 日期类型，DATE,DATETIME。其余日期类型不支持。
• 字符串类型，如CHAR,VARCHAR,BINARY,VARBINARY。不支持BLOB和TEXT。

* Composite（复合模式） - 很神秘吧，哈哈，其实是以上模式的组合使用而已，就不解释了。举例：在初始化已经进行了Range范围分区的表上，我们可以对其中一个分区再进行hash哈希分区。

= 垂直分区（按列分）=
举个简单例子：一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，这时候就要把这些不经常使用的text和BLOB了划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。MySQL目前不支持垂直分区。

Range 分区

创建分区

CREATE DATABASE part;
CREATE TABLE e (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
)
    PARTITION BY RANGE (id) (
        PARTITION p0 VALUES LESS THAN (50),
        PARTITION p1 VALUES LESS THAN (100),
        PARTITION p2 VALUES LESS THAN (150),
        PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

INSERT INTO e VALUES 
    (1669, "Jim", "Smith"),
    (337, "Mary", "Jones"),
    (16, "Frank", "White"),
    (2005, "Linda", "Black");

查看分区和分区的行数：

root@localhost 20:15:26[part]> SELECT PARTITION_NAME, TABLE_ROWS 
    ->     FROM INFORMATION_SCHEMA.PARTITIONS 
    ->     WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          1 |
| p1             |          0 |
| p2             |          0 |
| p3             |          3 |
+----------------+------------+

> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'part' AND TABLE_NAME LIKE 'e'; 

删除某个分区，不仅分区结构会被删除，分区里的数据也会被删除：

ALTER TABLE e DROP PARTITION p0;

增加分区：

> alter table e add partition (partition p4 values less than (5600)); 
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition

说明有MAXVALUE值后，直接加分区是不可行的，需要使用 reorginize partition() 重新组织分区。且 RANGE 分区在加分区的时候，只能从最大值后面加，而最大值前面不可以添加。

也可以重新组织分区，重组分区的做法是，把原来的分区结构删除，创建新的分区结构，但新的分区结构必须要能容纳原分区的数据，否则会有报错：ERROR 1526 (HY000): Table has no partition for value 1234

# 把原range分区，重组为list分区，此过程不会丢失数据
> alter table e partition by list(id)(PARTITION a VALUES IN (1,5,6,1669,2005),PARTITION b VALUES IN (1234,2,7,8,337,9898));
Query OK, 7 rows affected (0.12 sec)
Records: 7  Duplicates: 0  Warnings: 0

 如果在一个没有分区定义的表中增加分区，直接使用 add 添加会报错：

> alter table e add partition (partition p0 values less than(50),PARTITION p1 VALUES LESS THAN (100), PARTITION p2 VALUES LESS THAN (150)); 
ERROR 1505 (HY000): Partition management on a not partitioned table is not possible

可以使用 partition by 新建：

> alter table e partition by range(id)(PARTITION p1 VALUES LESS THAN (100),PARTITION p2 VALUES LESS THAN (1256320));
Query OK, 7 rows affected (0.08 sec)
Records: 7  Duplicates: 0  Warnings: 0

也可以新建一个具有分区的表，结构一致，然后用insert into 分区表 select * from 原始表;

LIST 分区

创建 list 分区：

CREATE TABLE tblist (
    id INT NOT NULL,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION a VALUES IN (1,5,6),
    PARTITION b VALUES IN (2,7,8),
    PARTITION c VALUES IN (3,9,10),
    PARTITION d VALUES IN (4,11,12)
);

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME  FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tblist'; 

增加分区：

ALTER TABLE tblist ADD PARTITION (PARTITION e VALUES IN (20));
# 注意：不能增加包含现有任意值的分区。

合并分区：

ALTER TABLE tblist REORGANIZE PARTITION  a,b INTO (PARTITION m VALUES IN (1,5,6,2,7,8,13));
# 将分区a,b合并为分区m
# 可以新增值，例如这里增加了 13 值
# 注意：同 RANGE 分区一样，只能合并相邻的几个分区，不能跨分区合并。例如不能合并a,c两个分区，只能通过合并a,b,c

拆分分区：

ALTER TABLE tblist REORGANIZE PARTITION  m,c INTO 
(PARTITION m VALUES IN (1,5,6,3,9,10),
PARTITION n VALUES IN (2,7,8));

注意：

1. 在5.7.12版本中测试发现，合并和拆分分区重新定义的枚举值可以不是原来的值，如果原来的枚举值包含了数据而新合并或拆分的分区枚举值又不不包含原来的枚举值会造成数据丢失。虽然不知道为什么mysql不会禁止该行为，但是人为的要求无论是合并还是拆分分区枚举值保持不变，或者只能增加不能减少，这样能保证数据不丢失。

2. 并和拆分后的分区由于是相邻的分区进行合并和拆分会根据原本的分区的值新的分区也会在原本的分区的顺序位置。

3. 分区的语法基本是一致的，只是定义分区范围略有不同。如 RANGE 分区采用： VALUES LESS THAN (value)，而 LIST 分区采用： VALUES IN (list) 。

删除分区：

ALTER TABLE tblist DROP PARTITION e;
# 注意：删除分区同时会将分区中的数据删除，同时枚举的list值也被删除，后面无法往表中插入该值的数据。

  

HASH 分区

基于给定的分区个数，将数据分配到不同的分区，HASH分区只能针对整数进行HASH，对于非整形的字段只能通过表达式将其转换成整数。表达式可以是mysql中任意有效的函数或者表达式，对于非整形的HASH往表插入数据的过程中会多一步表达式的计算操作，所以不建议使用复杂的表达式这样会影响性能。

MYSQL支持两种HASH分区，常规HASH(HASH)和线性HASH(LINEAR HASH) 。

1. 常规HASH

常规hash是基于分区个数的取模（%）运算。根据余数插入到指定的分区。

CREATE TABLE tbhash (
    id INT NOT NULL,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;

ALTER TABLE tbhash ADD INDEX ix_store_id(store_id);

INSERT INTO tbhash() VALUES(1,100),(1,101),(2,102),(3,103),(4,104);

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tbhash';

时间字段类型分区：

CREATE TABLE employees (
    id INT NOT NULL,
    hired DATE NOT NULL DEFAULT '1970-01-01',
)
PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4;

常规hash的分区非常的简便，通过取模的方式可以让数据非常平均的分布每一个分区，但是由于分区在创建表的时候已经固定了。如果新增或者收缩分区的数据迁移比较大。

2. 线性HASH(LINEAR HASH)

LINEAR HASH和HASH的唯一区别就是PARTITION BY LINEAR HASH。

CREATE TABLE tblinhash (
    id INT NOT NULL,
    hired DATE NOT NULL DEFAULT '1970-01-01'
)
PARTITION BY LINEAR HASH( YEAR(hired) )
PARTITIONS 6;

线性HASH的计算原理参考：http://www.cnblogs.com/chenmh/p/5644496.html

3. 分区管理

减去3个分区：

ALTER TABLE tblinhash COALESCE PARTITION 3;

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tblinhash';

注意：减去两个分区后，数据根据现有的分区进行了重新的分布，以'2003-04-14'为例：POWER(2, CEILING( LOG(2,3) ))=4，2003&（4-1）=3,3>=3,3&(CEILING(3/2)-1)=1,所以现在的'2003-04-14'这条记录由原来的p3变成了p1。

增加4个分区：

ALTER TABLE tblinhash add PARTITION partitions 4;

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tblinhash';

当在3个分区的基础上增加4个分区后，‘2003-04-14’由原来的p1变成了p3,而另一条记录由原来的p2变成了p6。

KEY分区

KEY分区和HASH分区相似，但是KEY分区支持除text和BLOB之外的所有数据类型的分区，而HASH分区只支持数字分区，KEY分区不允许使用用户自定义的表达式进行分区，KEY分区使用系统提供的HASH函数进行分区。当表中存在主键或者唯一键时，如果创建key分区时没有指定字段系统默认会首选主键列作为分区字列,如果不存在主键列会选择非空唯一键列作为分区列,注意唯一列作为分区列唯一列不能为null。

创建常规 KEY 分区：

CREATE TABLE tb_key (
    id INT ,
    var CHAR(32) 
)
PARTITION BY KEY(var)
PARTITIONS 10;

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tb_key';

创建 LINEAR KEY 分区：

同样key分区也存在线性KEY分区，概念和线性HASH分区一样。

CREATE TABLE tb_keyline (
    id INT NOT NULL,
    var CHAR(5)
)
PARTITION BY LINEAR KEY (var)
PARTITIONS 3;

COLUMN 分区 

COLUMN分区是5.5开始引入的分区功能，只有RANGE COLUMN和LIST COLUMN这两种分区；支持整形、日期、字符串；同RANGE和LIST的分区方式非常的相似。

COLUMNS和RANGE和LIST分区的区别

1. 针对日期字段的分区就不需要再使用函数进行转换了，例如针对date字段进行分区不需要再使用YEAR()表达式进行转换。

2. COLUMN分区支持多个字段作为分区键但是不支持表达式作为分区键。

COLUMNS支持的类型

整形支持：tinyint,smallint,mediumint,int,bigint;不支持decimal和float

时间类型支持：date,datetime

字符类型支持：char,varchar,binary,varbinary;不支持text,blob

一、RANGE COLUMNS分区

1. 日期字段分区

CREATE TABLE members (
    id INT,
    joined DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(joined) (
    PARTITION a VALUES LESS THAN ('1960-01-01'),
    PARTITION b VALUES LESS THAN ('1970-01-01'),
    PARTITION c VALUES LESS THAN ('1980-01-01'),
    PARTITION d VALUES LESS THAN ('1990-01-01'),
    PARTITION e VALUES LESS THAN MAXVALUE
);

insert into members(id,joined) values(1,'1950-01-01'),(1,'1960-01-01'),(1,'1980-01-01'),(1,'1990-01-01');

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION 
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='members';

2. 多字段组合分区

CREATE TABLE rcx (
    a INT,
    b INT
    )
PARTITION BY RANGE COLUMNS(a,b) (
     PARTITION p0 VALUES LESS THAN (5,10),
     PARTITION p1 VALUES LESS THAN (10,20),
     PARTITION p2 VALUES LESS THAN (15,30),
     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);

注意：

1）多字段的分区键比较是基于数组的比较。它先用插入的数据的第一个字段值和分区的第一个值进行比较，如果插入的第一个值小于分区的第一个值那么就不需要比较第二个值就属于该分区；如果第一个值等于分区的第一个值，开始比较第二个值同样如果第二个值小于分区的第二个值那么就属于该分区。

2）RANGE COLUMN的多列分区第一列的分区值一定是顺序增长的，不能出现交叉值，第二列的值随便，例如以下分区就会报错。

二、LIST COLUMNS分区

1. 非整形字段分区

CREATE TABLE listvar (
    id INT NOT NULL,
    hired DATETIME NOT NULL
)
PARTITION BY LIST COLUMNS(hired) 
(
    PARTITION a VALUES IN ('1990-01-01 10:00:00','1991-01-01 10:00:00'),
    PARTITION b VALUES IN ('1992-01-01 10:00:00'),
    PARTITION c VALUES IN ('1993-01-01 10:00:00'),
    PARTITION d VALUES IN ('1994-01-01 10:00:00')
);

ALTER TABLE listvar ADD INDEX ix_hired(hired);

INSERT INTO listvar() VALUES(1,'1990-01-01 10:00:00'),(1,'1991-01-01 10:00:00'),(1,'1992-01-01 10:00:00'),(1,'1993-01-01 10:00:00');

LIST COLUMNS分区对分整形字段进行分区就无需使用函数对字段处理成整形，所以对非整形字段进行分区建议选择COLUMNS分区。

2. 多字段分区

CREATE TABLE listvardou (
    id INT NOT NULL,
    hired DATETIME NOT NULL
)
PARTITION BY LIST COLUMNS(id,hired) 
(
    PARTITION a VALUES IN ( (1,'1990-01-01 10:00:00'),(1,'1991-01-01 10:00:00') ),
    PARTITION b VALUES IN ( (2,'1992-01-01 10:00:00') ),
    PARTITION c VALUES IN ( (3,'1993-01-01 10:00:00') ),
    PARTITION d VALUES IN ( (4,'1994-01-01 10:00:00') )
);

ALTER TABLE listvardou ADD INDEX ix_hired(hired);

INSERT INTO listvardou() VALUES(1,'1990-01-01 10:00:00'),(1,'1991-01-01 10:00:00'),(2,'1992-01-01 10:00:00'),(3,'1993-01-01 10:00:00');

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION 
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='listvardou';

移除表分区

ALTER TABLE tablename REMOVE PARTITIONING ;

注意：使用remove移除分区是仅仅移除分区的定义，并不会删除数据和 drop PARTITION 不一样，后者会连同数据一起删除。