MySQL--MySQL分区

原博: https://www.cnblogs.com/microcat/p/6673835.html
参考:http://bbs.51cto.com/thread-1080714-1.html (原博的参考)
MySQL 从5.1 版本开始支持分区的功能。分区是指根据一定的规则，数据库把一个表分解成多个更小的、更容易管理的部分。就访问数据库的应用而言，逻辑上只有一个表或一个索引，但是实际上这个表可能是由数10个物理分区对象组成，每个分区都是一个独立的对象，可以独自处理，可以作为表的一部分进行处理。分区对应用来说是完全透明的，不影响应用的业务逻辑。

MySQL 分区的优点主要包括以下4个方面。

和单个磁盘或者文件系统分区相比，可以存储更多的数据。

优化查询。在 WHERE 子句中包含分区条件时，可以只扫描必要的一个或多个分区来提高查询效率；同时在涉及 SUM() 和 COUNT() 这类聚合函数的查询时，可以容易的在每个分区上并行处理，最终只需要汇总所有分区得到的结果。

对于已经过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来快速删除数据。

跨多个磁盘来分散数据查询，以获得更大的查询吞吐量。

注意：在 MySQL 5.1 版本中，分区的实现仍然是 pre-alpha 版本，该版本中分区特性不是特别适用于生产环境。

1. 分区概述

分区有利于管理非常大的表。分区引入了分区键(partition key)的概念，分区键用于根据某个区间值(或者范围值)、特定值列表或者 HASH 函数值执行数据的聚集，让数据根据规则分布在不同的分区中，让一个大对象变成小对象。
MySQL 可以通过下面语句判断是否支持分区(5.6 之前)：

SHOW VARIABLES LIKE '%partition%';

如果输出：

have_partitioning   YES

表示支持分区。

或者通过(5.6及之后)：

SHOW PLUGINS;

显示所有插件，如果有partition ACTIVE STORAGE ENGINE GPL 插件则表明支持分区

注意:使用 SHOW ENGINES 命令的输出信息中，不会显示任何有关分区支持的信息，必须使用 SHOW VARIABLES 来判断当前版本是否支持分区。SHOW PLUGINS 命令的输出信息中能够检查到当前版本是否安装了分区插件。

MySQL支持使用大部分存储引擎(比如 MyISAM、InnoDB、Memory 等存储引擎)创建分区表；MySQL 不支持使用 MERGE 或 CSV 存储引擎来创建分区表。
在 MySQL 5.1 版本中，同一个分区表的所有分区必须使用同一个存储引擎；即同一个表上，不能对一个分区使用 MyISAM 引擎，对另一个分区使用 InnoDB；但是，可以在同一个 MySQL 服务器中，甚至同一个数据库中，对于不同的分区表使用不同的存储引擎。
和非分区表设置存储引擎一样，分区表设置存储引擎，只能用 [STORAGE] ENGINE 子句。 [STORAGE] ENGINE 子句必须列在 CREATE TABLE 语句中的其他任何分区选项之前。
MySQL 的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区；反过来也是一样的，不能只对索引分区而不对表分区，同时也不能只对表的一部分数据进行分区。MySQL 的分区表上创建的索引一定是本地 LOCAL 索引。

2. 分区类型（5.1）

RANGE 分区：基于一个给定连续区间范围，把数据分配到不同的分区。
　　LIST 分区：类似 RANGE 分区，区别在 LIST 分区是基于枚举出的值列表分区，RANGE 是基于给定的连续区间范围分区。
　　HASH 分区：基于给定的分区个数，把数据分配到不同的分区。
　　KEY 分区：类似于 HASH 分区。

在 MySQL 5.1 版本中，RANGE 分区、LIST 分区、HASH 分区都要求分区键必须是 INT 类型，或者通过表达式返回 INT 类型，也就是说 MySQL 5.1 仅仅支持整数分区，唯一的例外就是分区类型为 KEY 分区时，可以使用其他类型的列(BLOG 或 TEXT 列类型除外)作为分区键。

在 MySQL 5.5 或以上的版本中，已经支持非整数的 RANGE 和 LIST 分区了。

无论是哪种 MySQL 分区类型，要么分区表上没有主键/唯一键，要么分区表的主键/唯一键都必须包含分区键，也就是说不能使用主键/唯一键字段之外的其他字段分区。

分区的名字基本上遵循 MySQL 标识符的原则。MySQL 命名中的大小写敏感：在 MySQL 中，数据库和表对应于数据目录中的目录和文件。所以，操作系统的大小写敏感性决定数据库和表命名的大小写敏感。但是需要注意的是，分区的名字是不区分大小写的。

RANGE 分区
　　　　按照 RANGE 分区的表是利用取值范围将数据分成分区，区间要连续并且不能相互重叠，使用 VALUES LESS THAN 操作符进行分区定义。

可以在设置分区的时候使用 VALUES LESS THAN MAXVALUE 子句，该子句提供给所有大于明确指定的最高值的值，MAXVALUE 表示最大的可能的整数值。

在 RANGE 分区中，分区键如果是 NULL 值会被当做一个最小值来处理。

MySQL 5.1 支持整数列分区，要想在日期或者字符串列上进行分区，就得使用函数进行转换。但要是查询如果不用函数转换，那么就无法利用 RANGE 分区特性来提高查询性能。

MySQL 5.5 改进了 RANGE 分区功能，提供了 RANGE COLUMNS 分区支持非整数分区，这样创建日期分区就不需要通过函数进行转换了。

MySQL 5.1 分区日期处理上支持的函数只有两个 YEAR() 和 TO_DAYS()。

MySQL 5.5 日期处理上增加了支持函数 TO_SECONDS()，把日期转换成秒，从而能够实现比按天分区更细化的分区。

RANGE 分区功能特别适用于以下两种情况:

当需要删除过期的数据时，只需要简单的

ALTER TABLE table_name

DROP PARTITION partition_name

来删除partition_name 分区中的数据（同时也删除了该分区）。对于具有上百万条记录的表来说，删除分区要比运行一个 DELETE 语句有效的多。

经常运行包含分区键的查询，MySQL 可以很快地确定只有某一个或者某些分区需要扫描，因为其他分区不可能包含有符合该 WHERE 子句的任何记录。

1 create table rc4 (
2     a int
3 ) PARTITION by range(a) (
4     partition p01 values less than (0),
5     partition p02 values less than (10),
6     partition p03 values less than (20),
7     partition p04 values less than (35),
8     partition p05 values less than MAXVALUE
9 );

LIST 分区
　　　　LIST 分区是建立离散的值列表告诉数据库特定的值属于哪个分区，LIST 分区在很多方面类似于 RANGE 分区，区别在 LIST 分区是从属于一个枚举列表的值得集合，RANGE 分区是从属于一个连续区间值的集合。

LIST 分区通过使用 PARTITION BY LIST(expr) 子句来实现，expr 是某列值或一个基于某列值返回一个整数值的表达式，然后通过 VALUES IN(value_list) 的方式来定义分区，其中 value_list 是一个逗号分隔的整数列表。与 RANGE 分区不同，LIST 分区不必声明任何特定的顺序。

如果试图插入的列值（或者分区表达式的返回值）不包含分区值列表中时，那么 INSERT 操作会失败并报错。要重点注意的是，LIST 分期不存在类似 VALUES LESS THAN MAXVALUE 这样包含其他值在内的定义方式。将要匹配的任何值都必须在值列表中找得到。

    create table expenses (
    expense_date DATE not null,
    category INT,
    amount DECIMAL(10,3)
 ) partition by list(category) (
    partition p0 values in (3,5),
    partition p1 values in (1,10),
    partition p2 values in (4,9),
    partition p3 values in (2),
    partition p4 values in (6)
   );

Columns 分区
　　　　Columns 分区是在 MySQL 5.5 引入的分区类型，引入 Columns 分区解决了 MySQL 5.5 版本之前 RANGE 分区和 LIST 分区只支持整数分区，从而导致需要额外的函数计算得到整数或者通过额外的转换表来转换为整数再分区的问题。Columns 分区可以细分为 RANGE Columns 分区和 LIST Columns 分区，RANGE Columns 分区和 LIST Columns 分区都支持整数、日期时间、字符串三大数据类型。

所有整数类型：tinyint、smallint、mediumint、int 和 bigint；其他数值类型都不支持，例如不支持 Decimal 和 Float。

日期时间类型：date 和 datetime。

字符类型：char、varchar、binary 和 varbinary；不支持 text 和 blob 类型作为分区键。

MySQL 5.5 中，Columns 分区的亮点除了支持数据类型增加之外，另外一大亮点是 Columns 分区还支持多列分区。

需要注意的是，RANGE Columns 分区键的比较是基于元祖的比较，也就是基于字段组的比较，这和之前 RANGE 分区键的比较有些差异。

其实，RANGE Columns 分区键的比较(元祖的比较)其实就是多列排序，先根据 a 字段排序再根据 b 字段排序，根据排序结果来分区存放数据。和 RANGE 单字段分区排序的规则实际上是一致的。

复制代码
 1 create table rc3 (
 2     a int,
 3     b INT
 4 ) PARTITION by range COLUMNS (a, b) (
 5     partition p01 values less than (0, 10),
 6     partition p02 values less than (10, 10),
 7     partition p03 values less than (10, 20),
 8     partition p04 values less than (10, 35),
 9     partition p05 values less than (10, MAXVALUE),
10     partition p06 values less than (MAXVALUE, MAXVALUE)
11 );
复制代码

HASH 分区
　　　　HASH 分区主要用来分散热点读，确保数据在预先确定个数的分区中尽可能平均分布。对一个表执行 HASH 分区时，MySQL 会对分区键应用一个散列函数，以此确定数据应当放在 N 个分区中的哪个分区中。

MySQL 支持两种 HASH 分区，常规 HASH 分区和线性 HASH 分区（LINEAR HASH 分区）；常规 HASH 使用的是取模算法，线性 HASH 分区使用的是一个线性的 2 的幂的运算法则。

假设将要保存的记录的分区编号为 N，那么 N = MOD(expr, num)。

表达式 expr 可以是 MySQL 中有效的任何函数或者其他表达式，只要他们返回一个既非常熟也非随机数的整数。每当插入/更新/删除一行数据时，这个表达式都需要计算一次，这意味着非常复杂的表达式可能会引起性能问题，MySQL 也不推荐使用涉及多列的哈希表达式。

常规 HASH 分区方式通过取模的方式来使数据尽可能平均分布在每个分区中，让每个分区管理的数据都减少了，提高了查询的效率；但是当需要增加分区或者合并分区的时候，原有的数据大部分都需要通过重新计算分区。常规 HASH 在分区管理上带来的代价太大了，不适合需要灵活变动分区的需求。

为了降低分区管理上的代价，MySQL 提供了线性 HASH 分区，分区函数是一个线性的 2 的幂运算法则。

线性 HASH 也可以计算出记录将保存的分区。

假设将要保存的分区编号为 N，num 是一个非负的整数，表示分割成分区的数量，那么 N 可以通过以下算法得到：

首先，找到下一个大于等于 num 的 2的幂，这个值设为 V，V可以通过下面的公式的到：

V = Power(2, Ceiling(Log(2, num))

其次，设置 N = F(column_list) & (V-1)

当 N >= num

设置 V = Ceiling(V/2)，设置 N = N & (V-1)

当线性 HASH 的分区个数是 2 的 N 次幂时，线性 HASH 的分区结果和常规 HASH 的分区结果是一致的。

线性 HASH 分区的优点是，在分区维护（包含增加、删除、合并、拆分分区）时，MySQL 能够处理的更加迅速；缺点是，对比常规 HASH 分区（取模）的时候，线性 HASH 各个分区之数据的分布不太均匀。

复制代码
1 create table emp (
2     id int not null,
3     ename varchar(30),
4     hired date not null default '1970-01-01',
5     separated date not null default '9999-12-31',
6     job varchar(30) not null,
7     store_id int not NULL
8 ) partition by hash (store_id) partitions 4;
复制代码

Key 分区
　　　　按照 Key 进行分区非常类似于按照 HASH 进行分区，只不过 HASH 分区允许使用用户自定义的表达式，而 Key 分区不允许使用用户自定义的表达式，需要使用 MySQL 服务器提供的 HASH 函数；同时 HASH 分区只支持整数分区，而 Key 分区支持使用出 BLOB or TEXT 类型外其他类型的列作为分区键。

与 HASH 分区不同，创建 Key 分区表的时候，可以不指定分区键，默认会首先选择使用主键作为分区键。没有主键的情况，会选择非空唯一键作为分区键。

注意：作为分区键的唯一键必须是非空的，如果不是非空的，依然会报错。

在没有主键也没有唯一键的情况下，就不能不指定分区键了。

在按照 Key 分区的分区表上，不能够执行 ALTER TABLE DROP PRIMARY KEY 来删除主键，MySQL 会返回错误 Field in list of fields for partition function not found in table.

和 HASH 分区类似，在 KEY 分区中使用关键字 LINEAR 具有同样的作用，也就是 LINEAR KEY 分区时，分区的编号是通过 2 的幂算法得到的，而不是通过取模得到的。

KEY 分区和 HASH 分区类似，在处理大量数据记录时，能够有效的分散热点。

复制代码
1 CREATE table emp2 ( 
2     id int not null,
3     ename varchar(30),
4     hired date not null default '1970-01-01',
5     separated date not null default '9999-12-31',
6     job varchar(30) not null,
7     store_id int not NULL
8 ) partition by key (job) partitions 4;
复制代码

子分区
　　　　子分区（subpartitioning）是分区表中对每个分区的再次分隔，又被称为复合分区(composite patitioning)。MySQL 从 MySQL 5.1 开始支持对已经通过 RANGE 或者 LIST 分区了的表再进行子分区，子分区既可以使用 HASH 分区，也可以使用 KEY 分区。

复合分区适用于保存非常大量的数据记录。

MySQL 分区对 NULL 值的方式
　　　　MySQL 不禁止在分区键值上使用 NULL，分区键可能是一个字段或者一个用户定义的表达式。一般情况下，MySQL 的分区把 NULL 当做零值，或者一个最小值进行处理。

RANGE 分区中，NULL 值会被当做最小值来处理；LIST 分区中，NULL 值必须出现在枚举列表中，否则不被接受；HASH/KEY 分区中，NULL 值会被当做零值来处理。

由于针对不同的分区类型，NULL 值时而被当做零值处理，时而被当做最小值处理，为了避免在处理 NULL 值时出现误判，更推荐通过设置非空和默认值来绕开 MySQL 默认对 NULL 值的处理。

查看数据的分区分布情况

 SELECT partition_name part, partition_expression expr, partition_description descr, table_rows 
    　　　　FROM INFORMATION_SCHEMA.partitions 
    　　　　WHERE TABLE_SCHEMA = schema() 
    　　　　AND TABLE_NAME='table_name';

3) 分区管理

MySQL 5.1 提供了添加、删除、重定义、合并、拆分分区的命令，这些操作都可以通过 ALTER TABLE 命令来实现。

RANGE & LIST 分区管理
　　　　在添加、删除、重新定义分区的处理上，RANGE 分区和 LIST 分区非常相似。

从一个 RANGE 或者 LIST 分区的表中删除一个分区，可以使用

ALTER TABLE table_name

　　　　　　　　DROP PARTITION  partition_name

语句来实现。

删除分区的命令执行之后，并不显示从表中删除的行数，并不是真的没有记录被删除。

删除分区的同时，也删除了该分区中的所有数据。

删除 LIST 分区和删除 RANGE 分区使用的语句完全相同，只不过删除 LIST 分区之后，由于在 LIST 分区的定义中不在包含被已删除了的分区的值列表，所以后续无法写入包含已经删除了的分区的值列表的数据。

为一个 RANGE 或者 LIST 分区的表增加一个分区，可以使用

ALTER TABLE table_name

　　　　　　　　ADD PARTITON partition_name

的语句来实现。对于 RANGE 分区来说，只能通过 ADD PARTITION 方式添加新的分区到分区列表的最大一段。

给 LIST 分区增加新分区的方式也类似。

增加 LIST 分区时，不能添加一个包含现有分区值列表中的任意值的分区，也就是说对一个固定的分区键值，必须指定并且只能指定一个唯一的分区，否则会出现错误。

MySQL 也提供了在不丢失数据的情况下，通过重新定义分区的语句 ALTER TABLE REORGANIZE INTO 重定义分区。

ALTER TABLE table_name

　　　　　　　　REORGANIZE PARTITION partition_name1[, partition_name2,...]

　　　　　　　　INTO (

　　　　　　　　　　PARTITION ...[,

　　　　　　　　　　...]

　　　　　　　　)

重新定义分区 可以用来拆分一个 RANGE 分区为多个 RANGE 分区，也可以用来合并多个相邻 RANGE 分区为一个 RANGE 分区或者多个 RANGE 分区。

重新定义 RANGE 分区时，只能够重新定义相邻的分区，不能跳过某个 RANGE 分区进行重新定义，同时重新定义的分区区间必须和原分区区间覆盖相同的区间；也不能使用重新定义分区来改变表分区的类型。

同样的，对 LIST 分区，也可以使用 ALTER TABLE REORGANIZE PARTITION INTO 语句重新定义分区。

类似的重定义 RANGE 分区，重新定义 LIST 分区时，只能够重新定义相邻的分区，不能跳过 LIST 分区重新定义，同时重新定义的分区区间必须和原分区区间覆盖相同的区间；也不能使用重新定义分区来改变表分区的类型。例如，不能把 LIST 分区 变为 RANGE 分区，也不能把 RANGE 分区变成 LIST 分区。

HASH & KEY 分区管理
　　　　在改变分区设置方面，HASH 分区和 KEY 分区的表非常类似。不能以 RANGE 或者 LIST 分区表中删除分区的相同方式来从 HASH 或者 KEY 分区的表中删除分区，而可以通过 ALTER TABLE COALESCE PARTITION 语句来合并 HASH 分区或者 KEY 分区。

COALESCE 不能用来增加分区的数量，否则会出现 Can not remove all partitions, use DROP TABLE instead.

要增加分区，可以通过 ALTER TABLE ADD PARTITION 语句来实现。通过

ALTER TABLE table_name
　　　　　　　　　　ADD PARTITION PARTITIONS n

语句新增 HASH 分区或者 KEY 分区时，其实是对原表新增 n 个分区，而不是增加到 哪个分区。