MySQL表分区
MySQL分区表介绍
分区是一种表的设计模式，正确的分区可以极大地提升数据库的查询效率，完成更高质量的SQL编程。但是如果错误地使用分区，那么分区可能带来毁灭性的的结果。
分区功能并不是在存储引擎层完成的，因此不只有InnoDB存储引擎支持分区，常见的存储引擎MyISAM、NDB等都支持分区。但是并不是所有的存储引擎都支持，如CSV、FEDORATED、MERGE等就不支持分区。在使用此分区功能前，应该对选择的存储引擎对分区的支持有所了解。
MySQL数据库在5.1版本时添加了对分区的支持，分区的过程是将一个表或索引分解为多个更小、更可管理的部分。就访问数据库的应用而言，从逻辑上讲，只有一个表或一个索引，但是在物理上这个表或索引可能由数十个物理分区组成。每个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。
MySQL数据库支持的分区类型为水平分区（指将同一个表中不同行的记录分配到不同的物理文件中），并不支持垂直分区（指将同一表中不同列的记录分配到不同的物理文件中）。此外，MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中。目前，MySQL数据库还不支持全局分区。
可以通过以下命令来查看当前数据库是否启用了分区功能：
（1）mysql5.6以下版本
mysql> show plugins like '%partiotion%';mysql> show variables like '%partition%';
Empty set (0.00 sec)如果查询结果显示Empty，表示不支持分区。
（2）mysql5.6及以上版本
mysql> show plugins;
+----------------------------+----------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
+----------------------------+----------+--------------------+---------+---------+
42 rows in set (0.00 sec)上面的查询方法会显示所有插件，如果有如下的话，表示支持分区。
partition | ACTIVE | STORAGE ENGINE | NULL | GPL 查询mysql版本的方法：
- Linux下执行如下命令：mysql -V
- 在mysql命令行下执行命令：mysql> status;
有时候可能会有这么一种误区，只要启用了分区，数据库就会运行的更快。这个结论结论是存在很多问题的，就经验来看，分区可能会给某些SQL语句性能带来提高，但是分区主要用于数据库高可用性的管理。在OLTP应用中，对于分区的使用应该非常小心，总之，如果只是一味地使用分区，而不理解分区是如何工作的，也不清楚你的应用如何使用分区，那么分区极有可能会对性能产生负面的影响。
MySQL分区类型
RANGE分区
我们介绍的第一种分区类型是RANGE分区，也是最常用的一种分区类型。RANGE分区基于属于一个给定连续区间的列值，把多行分配给分区。
这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。下面创建一个以id列为区间分区表，当id小于10时，数据插入p0分区；当id大于等于10小于20时，数据插入p1分区。
CREATE TABLE t (
id int
) ENGINE = innodb
PARTITION BY RANGE (id) (
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20)
);查看表在磁盘上的物理文件，启用分区之后，表不再由一个ibd文件组成了。而是由建立分区时的各个分区ibd文件组成。
接着插入数据：
INSERT INTO t
SELECT 9;
INSERT INTO t
SELECT 10;
INSERT INTO t
SELECT 15;因为表t根据id进行分区，所以数据是根据列id的值的范围存放在不同的物理文件中的，可以通过查询information_schema架构下的partitions表来查看每个分区的具体信息：
SELECT *
FROM information_schema.partitions
WHERE table_schema = DATABASE()
AND table_name = 't'TABLE_ROWS列反映了每个分区中记录的数量。由于之前向表中插入了9、10、15三条记录，因此可以看到，当前分区p0中有1条记录，p1分区中有两条记录。PARTITION_METHOD表示分区的类型，这里显示的是RANGE。
对于表t，由于我们定义了分区，因此对于插入的值应该严格遵守分区的定义，当插入一个不在分区中定义的值时，MySQL数据库会抛出一个异常。如下所示：
mysql> INSERT INTO t SELECT 30;
1526 - Table has no partition for value 30对于上述问题，我们可以对分区添加一个MAXVALUE值的分区，MAXVALUE可以理解为正无穷，因此所有大于等于20且小于MAXVALUE的值被放入p2分区。
mysql> ALTER TABLE t
ADD PARTITION (PARTITION p2 VALUES LESS THAN MAXVALUE);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0RANGE分区主要用于日期列的分区，例如对于销售类的表，可以根据年来分区存放销售记录，如下面的分区表sales。
CREATE TABLE sales (
money int UNSIGNED NOT NULL,
date datetime
) ENGINE = innodb
PARTITION BY RANGE (year(date)) (
PARTITION p2014 VALUES LESS THAN (2015),
PARTITION p2015 VALUES LESS THAN (2016),
PARTITION p2016 VALUES LESS THAN (2017)
);插入数据：
INSERT INTO sales
SELECT 100, '2013-01-01';
INSERT INTO sales
SELECT 100, '2013-02-01';
INSERT INTO sales
SELECT 200, '2013-01-02';
INSERT INTO sales
SELECT 200, '2014-03-01';
INSERT INTO sales
SELECT 100, '2015-03-01';这样创建的好处就是便于对sales这张表的管理。如果我们要删除2015年的数据，不需要执行delete from sales where date>=’2015-01-01′ and dater<= ‘2016-01-01’，只需要删除2015年所在的分区即可。
ALTER TABLE sales
DROP PARTITION p2015;这样创建的另一个好处就是可以加快某些查询操作，如果我们只需要查询2015年整年的销售额，可以这样：
EXPLAIN partitions SELECT *
FROM sales
WHERE date >= '2014-01-01'
AND date <= '2014-12-31'通过explain partitions命令我们可以发现，在上述语句中，SQL优化只需要去搜索p2014这个分区，而不会去搜索所有的分区，称为分区修剪（partition pruning），故查询的速度得到了大幅度的提升。需要注意的是，如果执行下列语句，结果是一样的，但是优化器的选择可能又会不同了。
EXPLAIN partitions SELECT *
FROM sales
WHERE date >= '2014-01-01'
AND date <= '2015-01-01'这次条件改为date<='2015-01-01'而不是date<='2014-12-31'时，优化器会选择搜索两个分区，这是我们不希望看到的，因此对于启用分区，应该根据分区的特性来编写最优的SQL。
在进行分区时，如果出现“This partition function is not allowed”的错误提示，则你可能使用了非支持函数。
另外，需要注意一点的时，对于RANGE分区的查询，优化器只能对YEAR()，TO_YEAR()，TO_SECONDS()，UNIX_TIMESTAMP()这类函数进行优化选择，如果你使用了其他的函数或方法编写了符合分区特性的SQL语句，就不一定能够使用查询优化，所以再编写SQL语句时尽量测试。
LIST分区
LIST分区和RANGE分区类似，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择，而非连续的。
LIST分区通过使用“PARTITION BY LIST(expr)”来实现，其中“expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过“VALUES IN (value_list)”的方式来定义每个分区，其中“value_list”是一个通过逗号分隔的整数列表。
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,
store_id INT
);假定有20个音像店，分布在4个有经销权的地区。
不同于RANGE分区中定义的VALUES LESS THAN语句，LIST分区使用VALUES IN，因为每个分区的值是离散的，因此只能定义值。按照属于同一个地区商店的行保存在同一个分区中的方式来分割表，可以使用下面的“CREATE TABLE”语句：
CREATE TABLE employees (
id INT NOT NULL,
name VARCHAR (30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
store_id INT
) PARTITION BY LIST (store_id)
(
PARTITION pNorth VALUES IN (3, 5, 6, 9, 17),
PARTITION pEast VALUES IN (1, 2, 10, 11, 19, 20),
PARTITION pWest VALUES IN (4, 12, 13, 14, 18),
PARTITION pCentral VALUES IN (7, 8, 15, 16)
);这使得在表中增加或删除指定地区的雇员记录变得容易起来。例如，假定西区的所有音像店都卖给了其他公司。那么与在西区音像店工作雇员相关的所有记录（行）可以使用“ALTER TABLE employees DROP PARTITION pWest；”来进行删除，它与具有同样作用的DELETE （删除）查询“DELETE query DELETE FROM employees WHERE store_id IN (4,12,13,14,18)；”比起来，要有效得多。
如果试图插入列值（或分区表达式的返回值）不在分区值列表中的一行时，那么“INSERT”查询将失败并报错。例如，假定LIST分区的采用上面的方案，下面的查询将失败：
INSERT INTO
employees
VALUES
(
224,
'Linus',
'2015-05-01',
'2015-10-12',
42,
21
);这是因为“store_id”列值21不能在用于定义分区pNorth, pEast, pWest,或pCentral的值列表中找到。要重点注意的是，LIST分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。
LIST分区除了能和RANGE分区结合起来生成一个复合的子分区，与HASH和KEY分区结合起来生成复合的子分区也是可能的。
HASH分区
Hash分区主要用来确保数据在预先确定数目的分区中平均分布，Hash括号内只能是整数列或返回确定整数的函数，实际上就是使用返回的整数对分区数取模。
要使用HASH分区来分割一个表，要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句，其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL整型的一列的名字。此外，你很可能需要在后面再添加一个“PARTITIONS num”子句，其中num是一个非负的整数，它表示表将要被分割成分区的数量。
如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1。
drop table if exists staff;
create table staff(
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 default 0,
store_id int not null default 0
)
partition by hash(store_id)
partitions 4;drop table if exists staff;
create table staff(
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 default 0,
store_id int not null default 0
)
partition by hash(year(hired))
partitions 4;Hash分区也存在与传统Hash分表一样的问题，可扩展性差。MySQL也提供了一个类似于一致Hash的分区方法－线性Hash分区，只需要在定义分区时添加LINEAR关键字。
drop table if exists staff;
create table staff(
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 default 0,
store_id int not null default 0
)
partition by linear hash(year(hired))
partitions 4;线性哈希功能，它与常规哈希的区别在于，线性哈希功能使用的一个线性的2的幂（powers-of-two）运算法则，而常规哈希使用的是求哈希函数值的模数。
KEY分区
Key分区与Hash分区很相似，只是Hash函数不同，定义时把Hash关键字替换成Key即可，同样Key分区也有对应与线性Hash的线性Key分区方法。
说明：在MySQL5.1版本中，RANGE,LIST,HASH分区要求分区键必须是INT类型，或者通过表达式返回INT类型。但KEY分区的时候，可以使用其他类型的列(BLOB，TEXT类型除外)作为分区键。
drop table if exists staff;
create table staff(
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 default 0,
store_id int not null default 0
)
partition by key(store_id)
partitions 4;在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用，分区的编号是通过2的幂（powers-of-two）算法得到，而不是通过模数算法。
另外，当表存在主键或唯一索引时可省略Key括号内的列名，Mysql将按照主键－唯一索引的顺序选择，当找不到唯一索引时报错。
