MySQL5.1分区表学习笔记

MySQL 从 5.1.x 的版本开始支持分区表，直到现在的最新版本 5.1.56 分区表已经比较成熟，并且该版本也是很稳定的 MySQL 版本。另

MySQL 从 5.1.x 的版本开始支持分区表，直到现在的最新版本 5.1.56 分区表已经比较成熟，并且该版本也是很稳定的 MySQL 版本。另外，MySQL 5.5开始支持RANGE COLUMNS和LIST COLUMNS的分区，也就是说非整型的列不再需要通过函数转化为整型，同时也可以对多个列进行分区。

由于分区功能并不是在存储引擎完成的，因此大部分常见的引擎都支持，例如 InnoDB、MyISAM 和 NDB 等，但 CSV、FEDERATED和MERGE等不支持。并且仅支持水平分区，不支持垂直分区。

分区表的优势可想而知，正如官方的参考手册中所提到的：与单个磁盘或文件系统分区相比，可以存储更多的数据；一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE 语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区；涉及到例如SUM() 和 COUNT()这样聚合函数的查询，可以很容易地进行并行处理；通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量等等。

MySQL 支持四种类型的分区：
1、RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区；
2、LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择；
3、HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算；
4、KEY 分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。

MySQL 5.1 提供了许多修改分区表的方式。添加、删除、重新定义、合并或拆分已经存在的分区是可能的。所有这些操作都可以通过使用ALTER TABLE 命令的分区扩展来实现。关于如何添加和删除分区的处理，RANGE和LIST分区非常相似，，HASH和KEY分区也非常相似。基于这个原因，我们先介绍RANGE和HASH这两种分区的管理。

下面通过 RANGE 分区的实例操作学习分区表的所支持的操作，稍候将介绍 HASH 分区的实例操作：
首先，可以通过使用SHOW VARIABLES命令来确定MySQL是否支持分区（注意：mysql> 为提示符）
mysql> show variables like '%partition%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| have_partition_engine | YES |
+-----------------------+-------+
如果 value 值为 YES，则说明可以继续接下来的操作。

按照官方手册中提供的例子（稍有改动），创建 RANGE 类型的分区表：
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (1),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (21)
)

增加分区，名称为 p3
mysql> alter table employees add partition ( partition p3 values less than (31));

删除名称为 p3 分区
mysql> alter table employees drop partition p3;

拆分名称为 p2 分区为 p2 p3 两个分区，注意被拆分的分区只能是分区表的最后一个分区
mysql> alter table employees reorganize partition p2 into (partition p2 values less than (21), partition p3 values less than (31));

合并 名称为 p2 p3 的两个分区为一个分区 p2 ，注意合并后分区 p2 的值不能小于原来 p3 分区的值
mysql> alter table employees reorganize partition p2,p3 into (partition p2 values less than (31));

注意：
1、如果不存在手工扩展分区的问题，可以使用 “VALUES LESS THAN MAXVALUE” 定义分区。
2、LIST分区没有类似如 “VALUES LESS THAN MAXVALUE” 这样的包含其他值在内的定义，将要匹配的任何值都必须在值列表中找到。
3、值为 NULL 的情况，如果是RANGE分区则MySQL 会将该值放到最左边的分区，因为 NULL 值被视为小于任何一个非 NULL 值得，这和 Oracle 刚好相反；如果是LIST分区则必须明确的指出哪个分区放 NULL 值。

再创建 HASH 分区表
CREATE TABLE employees2 (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4

增加分区数量
mysql> alter table employees2 add partition partitions 1;

减少分区数量
mysql> alter table employees2 coalesce partition 1;

注意：“ALTER TABLE ... REORGANIZE PARTITION”不能用于按照HASH或HASH分区的表。

同样，也可以优化上述的两张表
mysql> alter table employees rebuild partition p0,p1;

注意：“ALTER TABLE ... REORGANIZE PARTITION”也能让分区的数据文件重建。

查看 SQL 执行计划
mysql> explain partitions select * from employees;

同时，MySQL 也支持子分区，也可以每个RANGE分区的数据和索引都使用一个单独的磁盘。
CREATE TABLE employees3 (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (YEAR(hired))
SUBPARTITION BY HASH(TO_DAYS(hired)) (
PARTITION p0 VALUES LESS THAN (2010) (
SUBPARTITION s0 DATA DIRECTORY = '/disk0/data' INDEX DIRECTORY = '/disk0/idx',
SUBPARTITION s1 DATA DIRECTORY = '/disk1/data' INDEX DIRECTORY = '/disk1/idx'
),
PARTITION p1 VALUES LESS THAN (2011) (
SUBPARTITION s2 DATA DIRECTORY = '/disk2/data' INDEX DIRECTORY = '/disk2/idx',
SUBPARTITION s3 DATA DIRECTORY = '/disk3/data' INDEX DIRECTORY = '/disk3/idx'
),
PARTITION p2 VALUES LESS THAN (2012) (
SUBPARTITION s4 DATA DIRECTORY = '/disk4/data' INDEX DIRECTORY = '/disk4/idx',
SUBPARTITION s5 DATA DIRECTORY = '/disk5/data' INDEX DIRECTORY = '/disk5/idx'
)
);
需要注意的是 InnoDB 存储引擎会忽略 DATA DIRECTORY 和 INDEX DIRECTORY语法，因此上述分区表的数据和索引文件分开放置是无效的。

详细请参考官方的手册。