6.2.3 Columns 分区
Columns分区是在MySQL 5.5引入的分区类型，引入Columns分区解决了MySQL5.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分区仅支持一个或者多个字段名作为分区键，不支持表达式作为分区键，区别于RANGE分区和LIST分区。
对比RANGE分区和LIST分区，Columns分区的亮点除了支持数据类型增加之外，另外一大亮点是Columns分区还支持多列分区。例如，我们创建了一个根据字段a，b组合的RANGE COLUMNS分区：
mysql> CREATE TABLE rc3 (
-> a INT,
-> b INT
-> )
-> PARTITION BY RANGE COLUMNS(a, b) (
-> PARTITION p01 VALUES LESS THAN (0,10),
-> PARTITION p02 VALUES LESS THAN (10,10),
-> PARTITION p03 VALUES LESS THAN (10,20),
-> PARTITION p04 VALUES LESS THAN (10,35),
-> PARTITION p05 VALUES LESS THAN (10,MAXVALUE),
-> PARTITION p06 VALUES LESS THAN (MAXVALUE, MAXVALUE)
-> );
Query OK, 0 rows affected (0.07 sec)
需要注意的是，RANGE Columns分区键的比较是基于元组的比较，也就是基于字段组的比较，这和之前RANGE分区键的比较有些差异，我们写入几条测试数据并观察测试数据的分区分布情况来看看：
写入a=1, b=10的记录，从information_schema.partitions 表发现数据实际上写入了p02分区，也就是说元组 (1, 10) < (10, 10)
mysql> insert into rc3 (a, b) values (1, 10);
Query OK, 1 row affected (0.00 sec)
mysql> select (1,10) < (10,10) from dual;
+------------------+
| (1,10) < (10,10) |
+------------------+
| 1 |
+------------------+
1 row in set (0.01 sec)
mysql> SELECT
-> partition_name part,
-> partition_expression expr,
-> partition_description descr,
-> table_rows
-> FROM
-> INFORMATION_SCHEMA.partitions
-> WHERE
-> TABLE_SCHEMA = schema()
-> AND TABLE_NAME='rc3';
+------+---------+-------------------+------------+
| part | expr | descr | table_rows |
+------+---------+-------------------+------------+
| p01 | `a`,`b` | 0,10 | 0 |
| p02 | `a`,`b` | 10,10 | 1 |
| p03 | `a`,`b` | 10,20 | 0 |
| p04 | `a`,`b` | 10,35 | 0 |
| p05 | `a`,`b` | 10,MAXVALUE | 0 |
| p06 | `a`,`b` | MAXVALUE,MAXVALUE | 0 |
+------+---------+-------------------+------------+
写入a=10, b=9的记录，从information_schema.partitions 表能够发现数据实际上写入了p02分区，也就是说元组 (10, 9) < (10, 10)，元组(10, 9)的大小判断不是简单的通过元组的首字段进行的：
mysql> insert into rc3(a, b) values(10, 9);
Query OK, 1 row affected (0.00 sec)
mysql> select (10,9) < (10,10) from dual;
+------------------+
| (10,9) < (10,10) |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT
-> partition_name part,
-> partition_expression expr,
-> partition_description descr,
-> table_rows
-> FROM
-> INFORMATION_SCHEMA.partitions
-> WHERE
-> TABLE_SCHEMA = schema()
-> AND TABLE_NAME='rc3';
+------+---------+-------------------+------------+
| part | expr | descr | table_rows |
+------+---------+-------------------+------------+
| p01 | `a`,`b` | 0,10 | 0 |
| p02 | `a`,`b` | 10,10 | 2 |
| p03 | `a`,`b` | 10,20 | 0 |
| p04 | `a`,`b` | 10,35 | 0 |
| p05 | `a`,`b` | 10,MAXVALUE | 0 |
| p06 | `a`,`b` | MAXVALUE,MAXVALUE | 0 |
+------+---------+-------------------+------------+
6 rows in set (0.01 sec)
写入a=10, b=10的记录，从information_schema.partitions 表能够发现数据实际上写入了p03分区，也就是说元组 (10, 10) <= (10, 10) <(10, 20)：
mysql> insert into rc3(a, b) values(10,10);
Query OK, 1 row affected (0.00 sec)
mysql> select (10,10) < (10,10) from dual;
+-------------------+
| (10,10) < (10,10) |
+-------------------+
| 0 |
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT
-> partition_name part,
-> partition_expression expr,
-> partition_description descr,
-> table_rows
-> FROM
-> INFORMATION_SCHEMA.partitions
-> WHERE
-> TABLE_SCHEMA = schema()
-> AND TABLE_NAME='rc3';
+------+---------+-------------------+------------+
| part | expr | descr | table_rows |
+------+---------+-------------------+------------+
| p01 | `a`,`b` | 0,10 | 0 |
| p02 | `a`,`b` | 10,10 | 2 |
| p03 | `a`,`b` | 10,20 | 1 |
| p04 | `a`,`b` | 10,35 | 0 |
| p05 | `a`,`b` | 10,MAXVALUE | 0 |
| p06 | `a`,`b` | MAXVALUE,MAXVALUE | 0 |
+------+---------+-------------------+------------+
6 rows in set (0.00 sec)
其实，RANGE Columns分区键的比较（元组的比较）其实就是多列排序，先根据a字段排序再根据b字段排序，根据排序结果来分区存放数据。和RANGE单字段分区排序的规则实际上是一致的。
6.2.4 Hash 分区
HASH分区主要用来分散热点读，确保数据在预先确定个数的分区中尽可能平均分布。对一个表执行HASH分区时，MySQL会对分区键应用一个散列函数，以此确定数据应当放在N个分区中的哪个分区中。
MySQL 支持两种HASH分区，常规HASH分区和线性HASH分区（LINEAR HASH分区）；常规HASH使用的是取模算法，线性HASH分区使用的是一个线性的2的幂的运算法则。
在这里，我们将要创建一个常规HASH分区的散列表emp，使用PARTITION BY HASH(expr) PARTITIONS num 子句对分区类型、分区键和分区个数进行定义：expr是某列值或一个基于某列值返回一个整数值的表达式，num是一个非负的整数，表示分割成分区的数量，默认num为1。下面语句创建了一个基于store_id列HASH分区的表，表被分成了4个分区。
mysql> CREATE TABLE emp (
-> id INT NOT NULL,
-> ename VARCHAR(30),
-> hired DATE NOT NULL DEFAULT '1970-01-01',
-> separated DATE NOT NULL DEFAULT '9999-12-31',
-> job VARCHAR(30) NOT NULL,
-> store_id INT NOT NULL
-> )
-> PARTITION BY HASH (store_id) PARTITIONS 4;
Query OK, 0 rows affected (0.05 sec)
其实对于一个表达式“expr”，我们是可以计算出它会被保存在哪个分区中，假设将要保存记录的分区编号为N ，那么“N = MOD(expr, num)”。例如，emp表有4个分区，插入一个store_id列值为234的记录到emp表中：
mysql> insert into emp values (1, 'Tom', '2010-10-10', '9999-12-31', 'Clerk', 234);
Query OK, 0 rows affected (0.05 sec)
如果插入一个col3列值为'2005-09-15'的记录到表t1中，那么保存该条记录的分区确定如下：
MOD(234,4)= 2
也就是store_id = 234这条记录将会被保存到第二个分区，下图显示了MySQL会检查store_id中的值，计算散列，确定给定行会出现在哪个分区：
通过执行计划可以确定store_id = 234这条记录存储在第二个分区内：
sql>explain partitions select * from emp where store_id = 234\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp
partitions: p2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra: Using where
1 row in set (0.00 sec)
表达式“expr”可以是MySQL中有效的任何函数或者其他表达式，只要它们返回一个既非常数、也非随机数的整数。每当插入/更新/删除一行数据的时候，这个表达式都需要计算一次，这意味着非常复杂的表达式可能会引起性能问题，MySQL也不推荐使用涉及到多列的哈希表达式。
常规HASH分区方式看上去挺不错的，通过取模的方式来数据尽可能平均分布在每个分区中，让每个分区管理的数据都减少了，提高了查询的效率；可是当我们需要增加分区或者合并分区的时候，问题就出现了。假设原来是5个常规HASH分区，现在需要新增一个常规HASH分区，原来的取模算法是MOD(expr, 5)，根据余数0~4分布在5个分区中，现在新增一个分区后，取模算法变成MOD(expr,6)，根据余数0~5 分区在6个分区中，原来5个分区中的数据大部分都需要通过重新计算重新分区。常规HASH在分区管理上带来的代价太大了，不适合需要灵活变动分区的需求。为了降低分区管理上的代价，MySQL提供了线性HASH分区，分区函数是一个线性的2的幂的运算法则。
线性HASH分区和常规HASH分区在语法上的唯一区别是在“PARTITION BY”子句中添加“LINEAR”关键字，例如：
mysql> CREATE TABLE emp (
-> id INT NOT NULL,
-> ename VARCHAR(30),
-> hired DATE NOT NULL DEFAULT '1970-01-01',
-> separated DATE NOT NULL DEFAULT '9999-12-31',
-> job VARCHAR(30) NOT NULL,
-> store_id INT NOT NULL
-> )
-> PARTITION BY LINEAR HASH (store_id) PARTITIONS 4;
Query OK, 0 rows affected (0.02 sec)
同样的，使用线性HASH的时候，指定记录保存在哪个分区是可以计算出来的，假设将要保存记录的分区编号为N，num是一个非负的整数，表示分割成分区的数量，那么N可以通过下面的算法得到：
首先，找到下一个大于等于num的2的幂，这个值设为V，V可以通过下面的公式得到：
V =Power (2,Ceiling (Log (2,num)))
例如，我们刚才创建的emp表预先设定了4个分区，也就是num= 4
V = Power (2, Ceiling (Log (2,num)))
= Power (2, Ceiling (Log (2, 4)))
= Power (2, Ceiling (2))
= Power (2, 2)
= 4
其次，设置 N = F(column_list) & (V - 1)
例如，我们刚才计算出 V = 4， 现在计算store_id = 234对应的N值
N = F (column_list) & (V - 1)
=234 & (4 - 1)
=2
当 N >=num:
设置 V =Ceiling(V/2)
设置 N = N & (V - 1)
对于 store_id = 234 这条记录，由于 N = 2 < 4，所以直接就能够判断这条记录会被存储在第二个分区。
有意思的是，当线性HASH的分区个数是2的N次幂的时候，线性HASH的分区结果和常规HASH的分区结果是一致的。
注意： 由于负数取模较复杂，仅以非负整数A举例，模数num为2幂次方，那么数值A对num取模能够转换为位与运算： MOD (A, num) = A & (num - 1)
假设分区个数num为2的幂次方，数值A（A为非负整数）的所在分区为N(A)：
常规HASH分区时，保存数值A所在分区N(A) = MOD(A, num) = A & (num - 1)；
线性HASH分区时，找到大于等于num的幂V = Power (2, Ceiling (Log (2, num))) = num，（num本身就是2的幂次方），其次，计算N = A & (num- 1)，考虑到A为非负整数，N = A & (num - 1) = MOD(A, num)，也就是N为数值A对分区个数num取模的结果，容易判定N < num，最终数值A所在的分区N(A) = N = A & (num -1)，和常规HASH分区计算得到结果一致。
线性HASH分区的优点在分区维护（包含增加、删除、合并、拆分分区）时，MySQL能够处理的更加迅速；缺点是，对比常规HASH分区（取模）的时候，线性HASH各个分区之间数据的分布不太均衡。
6.2.5 Key 分区
按照Key进行分区非常类似于按照HASH进行分区，只不过HASH分区允许使用用户自定义的表达式，而Key分区时不允许使用用户自定义的表达式，需要使用MySQL服务器提供的HASH函数；同时HASH分区只支持整数分区，而Key分区支持使用除BLOB or Text类型外其他类型的列作为分区键。
我们同样可以用PARTITION BY KEY(expr) 子句来创建一个Key分区表，expr是零个或者多个字段名名的列表。下面语句创建了一个基于 job 字段进行Key分区的表，表被分成了4个分区：
mysql> CREATE TABLE emp (
-> id INT NOT NULL,
-> ename VARCHAR(30),
-> hired DATE NOT NULL DEFAULT '1970-01-01',
-> separated DATE NOT NULL DEFAULT '9999-12-31',
-> job VARCHAR(30) NOT NULL,
-> store_id INT NOT NULL
-> )
-> PARTITION BY KEY (job) PARTITIONS 4;
Query OK, 0 rows affected (0.04 sec)
和HASH分区不同，创建Key分区表的时候，可以不指定分区键，默认会首先选择使用主键作为分区键，例如：
mysql> CREATE TABLE emp (
-> id INT NOT NULL,
-> ename VARCHAR(30),
-> hired DATE NOT NULL DEFAULT '1970-01-01',
-> separated DATE NOT NULL DEFAULT '9999-12-31',
-> job VARCHAR(30) NOT NULL,
-> store_id INT NOT NULL,
-> PRIMARY KEY (id)
-> )
-> PARTITION BY KEY ( ) PARTITIONS 4;
Query OK, 0 rows affected (0.02 sec)
在没有主键的情况，会选择非空唯一键作为分区键：
mysql> drop table emp;
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE emp (
-> id INT NOT NULL,
-> ename VARCHAR(30),
-> hired DATE NOT NULL DEFAULT '1970-01-01',
-> separated DATE NOT NULL DEFAULT '9999-12-31',
-> job VARCHAR(30) NOT NULL,
-> store_id INT NOT NULL,
-> UNIQUE KEY (id)
-> )
-> PARTITION BY KEY ( ) PARTITIONS 4;
Query OK, 0 rows affected (0.01 sec)
注意： 作为分区键的唯一键必须是非空的，如果不是非空的，依然会报错：
mysql> CREATE TABLE emp (
-> id INT,
-> ename VARCHAR(30),
-> hired DATE NOT NULL DEFAULT '1970-01-01',
-> separated DATE NOT NULL DEFAULT '9999-12-31',
-> job VARCHAR(30) NOT NULL,
-> store_id INT NOT NULL,
-> UNIQUE KEY (id,ename)
-> )
-> PARTITION BY KEY ( ) PARTITIONS 4;
ERROR 1488 (HY000): Field in list of fields for partition function not found in table
在没有主键、也没有唯一键的情况下，就不能不指定分区键了：
mysql> drop table emp;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE emp (
-> id INT NOT NULL,
-> ename VARCHAR(30),
-> hired DATE NOT NULL DEFAULT '1970-01-01',
-> separated DATE NOT NULL DEFAULT '9999-12-31',
-> job VARCHAR(30) NOT NULL,
-> store_id INT NOT NULL
-> )
-> PARTITION BY KEY ( ) PARTITIONS 4;
ERROR 1488 (HY000): Field in list of fields for partition function not found in table
和HASH分区类似，在KEY分区中使用关键字LINEAR具有同样的作用，也就是LINEAR KEY分区时，分区的编号是通过2的幂算法得到，而不是通过取模得到的。
KEY分区和HASH分区类似，在处理大量数据记录的时候，能够有效的分散热点。
