初识MySQL分区技术
2015-04-17 14:32:22 
阿炯
1. 背景介绍当MySQL中一个表的总记录数超过了千万后，会出现性能的大幅度下降吗？答案是肯定的，但是性能下降的比率不一而同，要看系统的架构、应用程序，甚至还要根据索引、服务器硬件等多种因素而定。在多达上亿的数据量分表之后的单个表也已经突破千万的数据量，导致单个表的更新等均影响着系统的运行效率。甚至是一条简单的SQL都有可能压垮整个数据库，如整个表对某个字段的排序操作等。
MySQL分区表
* 分区和未分区占用文件空间大致相同(数据和索引文件)，
* 如果查询语句中有未建立索引字段，分区时间远远优于未分区时间，
* 如果查询语句中字段建立了索引，分区和未分区的差别缩小，分区略优于未分区。
目前针对海量数据的优化主要有2中方法：大表拆小表的方式和SQL语句的优化。SQL语句的优化可以通过增加索引等来调整，但是数据量的增大将会导致索引的维护代价增大。在此不详述，建议大家参考相应的High Performance MySQL等书籍。另外，大表拆小表的方式主要有两种：
垂直分表：
图1. 垂直分区示意图
对于垂直分表，它将一个N1+N2个字段的表Tab拆分成N1字段的子表Tab1和(N2+1)字段的子表Tab2；其中子表Tab2包含了关于子表Tab1的主键信息，否则两个表的关联关系就会丢失。当然垂直分表会带来程序端SQL的修改，若是应用程序已经应用很长的一段时间，然后程序的升级将是耗时而且易出错的，即升级的代价将会很大。
水平分表：
图2. 水平分区示意图
水平分区技术将一个表拆成多个表，比较常用的方式是将表中的记录按照某种Hash算法进行拆分，简单的拆分方法如取模方式。同样，这种分区方法也必须对前端的应用程序中的SQL进行修改方可使用。而且对于一个SQL，它可能会修改两个表，那么你必须得写成2个SQL语句从而可以完成一个逻辑的事务，使得程序的判断逻辑越来越复杂，这样也会导致程序的维护代价高，也就失去了采用数据库的优势。因此，分区技术可以有力地避免如上的弊端，成为解决海量数据存储的有力方法。
2. MySQL分区介绍
MySQL的分区技术不同与之前的分表技术，它与水平分表有点类似，但是它是在逻辑层进行的水平分表，对与应用程序而言它还是一张表。可以在对用户无感知的情况下，将对表数据的物理文件进行分区。MySQL的分区支持Memory、MyISAM、InnoDB等存储引擎。
注意：MySQL中如果存在主键，或者唯一索引，那么分区字段必须包含这些字段。
表分区，是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成。
分表：指的是通过一定规则，将一张表分解成多张不同的表，比如将用户订单记录根据时间成多个表。分表与分区的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。
表分区有什么好处
1)分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。
2)和单个磁盘或者文件系统相比，可以存储更多数据
3)优化查询。在where语句中包含分区条件时，可以只扫描一个或多个分区表来提高IO吞吐量而最终提高查询效率；涉及sum和count语句时，也可以在多个分区上并行处理，最后汇总结果。
4)分区表更容易维护。例如：想批量删除大量数据可以清除整个分区。比如存储日志按月分区可以快速删除历史日志。
5)可以使用分区表来避免某些特殊的瓶颈，例如InnoDB的单个索引的互斥访问，ext3文件系统的inode锁竞争等。
分区表的限制因素
1)一个表最多只能有1024个分区
2)MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。
3)如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。
4)分区表中无法使用外键约束。
5)MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。
分区相关的命令
检查MySQL版本是否支持分区
//查看表分区状态 查看 have_parttition_engine 是否为YES
SHOW VARIABLES LIKE '%parttition%';
检测SQL查询扫描了哪个分区
//检查SQL语句使用了哪个分区
explain partitions SQL;
源表是一张网页抓取文章结果表，这里为了测试有精简，并在views与comments字段进行了如后的修改
CREATE TABLE `wc` (
`cid` bigint(20) NOT NULL AUTO_INCREMENT,
`title` varchar(255) DEFAULT NULL,
`author` varchar(100) DEFAULT NULL,
`pubdt` datetime DEFAULT NULL,
`content` text NOT NULL,
`views` int(11) NOT NULL DEFAULT '0',
`comments` int(11) NOT NULL DEFAULT '0',
`url` varchar(1024) NOT NULL,
`origtype` enum('wz','lt','bk','wb','jw','tb','mg') NOT NULL DEFAULT 'wz',
`capdt` datetime DEFAULT NULL,
PRIMARY KEY `idview` (`cid`,`views`),
KEY `capdt` (`capdt`),
KEY `url` (`url`(500))
) ENGINE=MyISAM DEFAULT CHARSET=gbk;
为某字段设定随机整数，调用rand()函数生成，再用floor与ceil取其最大与最小整数值
floor(rand()*100),ceil(rand()*10);
insert into wc select content_id,title,author,published,content,views,comments,webpage_url,origin_type,capture_time from web_content;
update wc set views=floor(rand()*100),comments=ceil(rand()*10);
update wc set views=views+10 where comments>views;
2.1 MySQL分区类型
MySQL 从5.1有5中分区类型：
RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区(Range方式定义每一个分区区间，比如按从小到大的顺序来定义)；
LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择；
HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。
KEY 分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。
Columns分区：5.5中引入的分区类型，解决了5.5版本之前range分区和list分区只支持整数分区的问题。 Columns分区可以细分为 range columns分区和 list columns分区，他们都支持整数，日期时间，字符串三大数据类型。（不支持text和blob类型作为分区键） 该分区还支持多列分区。
子分区：分区表中对每个分区再次分割，又成为复合分区。是分区表中每个分区的再次分割，子分区既可以使用HASH希分区，也可以使用KEY分区：
1)如果一个分区中创建了子分区，其他分区也要有子分区
2)如果创建了了分区，每个分区中的子分区数必有相同
3)同一分区内的子分区，名字不相同，不同分区内的子分区名子可以相同（5.1.50不适用)
分区对于NULL值的处理：MySQ允许分区键值为NULL，分区键可能是一个字段或者一个用户定义的表达式。一般情况下，MySQL在分区的时候会把NULL值当作零值或者一个最小值进行处理。Range分区中：NULL值被当作最小值来处理；List分区中：NULL值必须出现在列表中，否则不被接受；Hash/Key分区中：NULL值会被当作零值来处理。
2.2 RANGE分区
对于RANGE分区，举个例子：
例1. 假定你创建了一个如下的一个表，该表保存网络爬虫抓取的文章记录，views是其被浏览的次数。 想将其分成4个小分区，那么你可以采用RANGE分区，创建的数据库表如下：
CREATE TABLE `wcv` (
`cid` bigint(20) NOT NULL AUTO_INCREMENT,
`title` varchar(255) DEFAULT NULL,
`author` varchar(100) DEFAULT NULL,
`pubdt` datetime DEFAULT NULL,
`content` text NOT NULL,
`views` int(11) NOT NULL DEFAULT '0',
`comments` int(11) NOT NULL DEFAULT '0',
`url` varchar(1024) NOT NULL,
`origtype` enum('wz','lt','bk','wb','jw','tb','mg') NOT NULL DEFAULT 'wz',
`capdt` datetime DEFAULT NULL,
PRIMARY KEY `idview` (`cid`,`views`),
KEY `capdt` (`capdt`),
KEY `url` (`url`(500))
) ENGINE=MyISAM DEFAULT CHARSET=gbk
partition by range (views)(
partition p0 values less than(25),
partition p1 values less than(50),
partition p2 values less than(75),
partition p3 values less than(100)
) ;
这个例子，它的key是一个整型的数据，那是否对于其它类型的字段就无法作为key呢？答案是否定的，例子2说明这种情况。文件分布情况如下：
-rw-rw---- 1 mysql mysql 5.1K 4月 16 09:34 wcv.frm
-rw-rw---- 1 mysql mysql 48 4月 16 09:34 wcv.par
-rw-rw---- 1 mysql mysql 419M 4月 16 09:35 wcv#P#p0.MYD
-rw-rw---- 1 mysql mysql 24M 4月 16 09:35 wcv#P#p0.MYI
-rw-rw---- 1 mysql mysql 416M 4月 16 09:35 wcv#P#p1.MYD
-rw-rw---- 1 mysql mysql 23M 4月 16 09:35 wcv#P#p1.MYI
-rw-rw---- 1 mysql mysql 416M 4月 16 09:35 wcv#P#p2.MYD
-rw-rw---- 1 mysql mysql 24M 4月 16 09:35 wcv#P#p2.MYI
-rw-rw---- 1 mysql mysql 415M 4月 16 09:35 wcv#P#p3.MYD
-rw-rw---- 1 mysql mysql 23M 4月 16 09:35 wcv#P#p3.MYI
例2. 假定你创建了一个如上的表，想把不同时期的文章进行分别存储，那么你可以将日期字段'capdt'作为一个key，创建的SQL语句如下：
CREATE TABLE `wcp` (
`cid` bigint(20) NOT NULL AUTO_INCREMENT,
`title` varchar(255) DEFAULT NULL,
`author` varchar(100) DEFAULT NULL,
`pubdt` datetime DEFAULT NULL,
`content` text NOT NULL,
`views` int(11) NOT NULL DEFAULT '0',
`comments` int(11) NOT NULL DEFAULT '0',
`url` varchar(1024) NOT NULL,
`origtype` enum('wz','lt','bk','wb','jw','tb','mg') NOT NULL DEFAULT 'wz',
`capdt` datetime NOT NULL DEFAULT '2015-01-01 00:00:00',
PRIMARY KEY `cid` (`cid`),
KEY `capdt` (`capdt`),
KEY `url` (`url`(500))
) ENGINE=MyISAM DEFAULT CHARSET=gbk
partition by range (hour(capdt))(
partition p0 values less than(12),
partition p1 values less than(16),
partition p2 values less than(20),
partition p3 values less than(23)
) ;
这样你就可以对一个日期类型的字段调用mysql的日期函数hour()转换为一种整数类型，从而可以作为RANGE分区的key。这个时候，你可以看到，按照分区后的物理文件是相对独立的：
-rw-rw---- 1 mysql mysql 4.6K 4月 16 13:15 wcp.frm
-rw-rw---- 1 mysql mysql 48 4月 16 13:15 wcp.par
-rw-rw---- 1 mysql mysql 298M 4月 16 13:16 wcp#P#p0.MYD
-rw-rw---- 1 mysql mysql 14M 4月 16 13:16 wcp#P#p0.MYI
-rw-rw---- 1 mysql mysql 482M 4月 16 13:16 wcp#P#p1.MYD
-rw-rw---- 1 mysql mysql 23M 4月 16 13:17 wcp#P#p1.MYI
-rw-rw---- 1 mysql mysql 630M 4月 16 13:17 wcp#P#p2.MYD
-rw-rw---- 1 mysql mysql 27M 4月 16 13:17 wcp#P#p2.MYI
-rw-rw---- 1 mysql mysql 255M 4月 16 13:17 wcp#P#p3.MYD
-rw-rw---- 1 mysql mysql 15M 4月 16 13:17 wcp#P#p3.MYI
可知，每个分区有自己独立的数据文件和索引文件，这是为什么你对某一个查询，它只会访问它需要访问的数据块，而不访问根本不是结果的物理块，从而可以大大提高系统的效率。
注意：网上有很多文章说按某一字段间接使用(像做hour()函数运算)，但在实际使用中，这种方法并没有奏效。在查询时间上起作用也只是该字段的索引的功劳，要使其生效就要改一下sql语句，其次还要mysql的版本支持。
[wcd]> explain partitions select cid,title,capdt from wcp2 where capdt>'2015-01-19 18:00:00';
+------+-------------+-------+-------------+-------+---------------+-------+---------+------+--------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------------+-------+---------------+-------+---------+------+--------+-------------+
| 1 | SIMPLE | wcp2 | p0,p1,p2,p3 | range | capdt | capdt | 8 | NULL | 385146 | Using where |
+------+-------------+-------+-------------+-------+---------------+-------+---------+------+--------+-------------+
像下面这种的就更没有效用了。
[wcd]> explain partitions select cid,title,capdt from wcp2 where hour(capdt)>=18;
+------+-------------+-------+-------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | wcp2 | p0,p1,p2,p3 | ALL | NULL | NULL | NULL | NULL | 1785490 | Using where |
+------+-------------+-------+-------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
在使用索引时，还只能用它该字段的单独索引，不能与其它字段建立的联合索引。要使用分区的功能，就要改写声明使用分区指令：
wcp3使用联合索引：KEY `idcap` (`cid`,`capdt`)
[wcd]> explain select cid,title,capdt from wcp3 PARTITION (p2,p3) where capdt>'2015-01-19 18:00:00';
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | wcp3 | ALL | NULL | NULL | NULL | NULL | 948924 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
[wcd]> explain partitions select cid,title,capdt from wcp3 PARTITION (p2,p3) where capdt>'2015-01-19 18:00:00' limit 2000,15;
+------+-------------+-------+------------+-------+---------------+-------+---------+------+--------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------------+-------+---------------+-------+---------+------+--------+-------------+
| 1 | SIMPLE | wcp3 | p2,p3 | range | capdt | capdt | 8 | NULL | 655616 | Using where |
+------+-------------+-------+------------+-------+---------------+-------+---------+------+--------+-------------+
使用PARTITION后，查询结果很可观。可以参考：http://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html
truncate 语句对分区表似乎无效，只能用delete清空表。
可能会出现的问题
===============================
mysql ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
因为分区键不是主键/unique的其中一个组成部分而报错，分区字段，必须包含在主键字段内。可将其加入，如以表的'views'字段作为分区标志，就可与其它字段一起使用：
PRIMARY KEY `idview` (`cid`,`views`)
在5.1中分区表对唯一约束有明确的规定，每一个唯一约束必须包含在分区表的分区键(也包括主键约束)。
官方文档是这样的：
All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have.
In other words, every unique key on the table must use every column in the table's partitioning expression. (This also includes the table's primary key, since it is by definition a unique key. This particular case is discussed later in this section.) For example, each of the following table creation
如何查看使用分区表后查询的效果：
[wcd]> explain select count(1) from wc where views >25 and views <50;
+------+-------------+-------+-------+---------------+---------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+---------+--------------------------+
| 1 | SIMPLE | wc | index | NULL | PRIMARY | 12 | NULL | 1785490 | Using where; Using index |
+------+-------------+-------+-------+---------------+---------+---------+------+---------+--------------------------+
1 row in set (0.00 sec)
[wcd]> explain select count(1) from wcv where views >25 and views <50;
+------+-------------+-------+-------+---------------+---------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+--------+--------------------------+
| 1 | SIMPLE | wcv | index | NULL | PRIMARY | 12 | NULL | 445558 | Using where; Using index |
+------+-------------+-------+-------+---------------+---------+---------+------+--------+--------------------------+
1 row in set (0.06 sec)
[wcd]> explain select cid,title,views from wc where views >60 limit 30000,10;
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | wc | ALL | NULL | NULL | NULL | NULL | 1785490 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
[wcd]> explain select cid,title,views from wcv where views >60 limit 30000,10;
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | wcv | ALL | NULL | NULL | NULL | NULL | 893139 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)
[wcd]> explain partitions select cid,title,views from wcv where views >60 limit 30000,10;
+------+-------------+-------+------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | wcv | p2,p3 | ALL | NULL | NULL | NULL | NULL | 893139 | Using where |
+------+-------------+-------+------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)
2.3 LIST分区
LIST分区与RANGE分区有类似的地方，不同的是，LIST方式非连续区间，而是一个枚举列表的集合。只有存在于这个枚举列表集合内的数据才可以被插入，否则不可以写入。举个与例1类似的例子如下：
例3. 按文章的评论数目(comments)来列表分区
CREATE TABLE `wct` (
`cid` bigint(20) NOT NULL AUTO_INCREMENT,
`title` varchar(255) DEFAULT NULL,
`author` varchar(100) DEFAULT NULL,
`pubdt` datetime DEFAULT NULL,
`content` text NOT NULL,
`views` int(11) NOT NULL DEFAULT '0',
`comments` int(11) NOT NULL DEFAULT '0',
`url` varchar(1024) NOT NULL,
`origtype` enum('wz','lt','bk','wb','jw','tb','mg') NOT NULL DEFAULT 'wz',
`capdt` datetime NOT NULL DEFAULT '2015-01-01 00:00:00',
PRIMARY KEY `cidment` (`cid`,`comments`),
KEY `capdt` (`capdt`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk
partition by list(comments)(
partition p0 values in(0,1,2,3),
partition p1 values in(4,5,6),
partition p2 values in(7,8,9,10)
) ;
与range分区类似，在检索数据的范围上，使用了分区功能的在总的检索数量上少很多。
[wcd]> explain partitions select cid,title,comments from wct where comments=5 limit 2000,10;
+------+-------------+-------+------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | wct | p1 | ALL | NULL | NULL | NULL | NULL | 536298 | Using where |
+------+-------------+-------+------------+------+---------------+------+---------+------+--------+-------------+
[wcd]> explain partitions select cid,title,comments from wc where comments=5 limit 2000,10;
+------+-------------+-------+------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | wc | NULL | ALL | NULL | NULL | NULL | NULL | 1785490 | Using where |
+------+-------------+-------+------------+------+---------------+------+---------+------+---------+-------------+
2.4 HASH分区
HASH分区主要用来确保数据在预先确定数目的分区中平均分布。它可以基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。通过对分区key表达式取模的方式，对数据进行均匀的分区。
//id进行hash运算后分成4个分区
PARTITION BY HASH(id) PARTITIONS 4;
但是这种方式太不灵活了，扩展性非常差，不利于以后分区的扩展，所以MySQL提供另外一种线形Hash。
PARTITION BY LINEAR HASH(id) PARTTITIONS 8;
通过线形hash分区后的数据，在扩容的时候如果分区数为2的N次幂，那么计算的结果于普通hash相同，这样避免了日后扩容的数据迁移，但是也导致数据不均的问题。
例4. 假定你创建了一个如下的一个表，有一个发表日期(pubdt)字段，将其所在的年份作为一个key，创建的SQL语句如下：
那么要插入一条'2005-09-15'发表的文章，那么按照取模函数会将其放置到第2分区中：
MOD(YEAR('2005-09-01'), 4)= MOD(2005,4)= 1 //即第2分区
CREATE TABLE `wch` (
`cid` bigint(20) NOT NULL AUTO_INCREMENT,
`title` varchar(255) DEFAULT NULL,
`author` varchar(100) DEFAULT NULL,
`pubdt` datetime DEFAULT NULL,
`content` text NOT NULL,
`views` int(11) NOT NULL DEFAULT '0',
`comments` int(11) NOT NULL DEFAULT '0',
`url` varchar(1024) NOT NULL,
`origtype` enum('wz','lt','bk','wb','jw','tb','mg') NOT NULL DEFAULT 'wz',
`capdt` datetime NOT NULL DEFAULT '2015-01-01 00:00:00',
PRIMARY KEY `cidpub` (`cid`,`pubdt`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk
partition by hash(year(pubdt))
partitions 4
;
分4个分区：p0-p4
[wcd]> select cid,title,pubdt from wch where pubdt between '2015-01-01 00:00:00' and '2015-12-31 23:59:59' limit 1000,10;
...
10 rows in set (0.10 sec)
[wcd]> select cid,title,pubdt from wc where pubdt between '2015-01-01 00:00:00' and '2015-12-31 23:59:59' limit 1000,10;
10 rows in set (1.91 sec)
在数据量大时，才很占优势。
2.5 KEY分区
与HASH分区类似，但它的key可以不是整数类型，如字符串等类型的字段。MySQL 簇(Cluster)使用函数MD5()来实现KEY分区；对于使用其他存储引擎的表，服务器使用其自己内部的哈希函数，这些函数是基于与 PASSWORD()一样的运算法则。与Hash分区很类似，区别在于只支持数字，并且定义分区字段的时候不允许使用表达式，但是可以提供多个分区字段。另外Hash分区采用MOD的方式进行计算，Key分区采用MySQL内部的Hash算法进行计算。Key分区在存在主键或唯一索引时候可以不指定分区字段，不指定的情况会默认使用主键或唯一索引。
//根据id进行分区，并划分四个分区
PARTITION BY KEY(id) PARTITIONS 4;
2.6 Columns方式
Columns方式算是对Range和LIST方式的一个补充，因为Range和LIST仅仅支持整数字段分区，所以通过Columns方式可以实现非整数分区，目前支持以下类型：整型、date、datetime、char、varchar、binary、varbinary等类型。
Columns方式不可以在定义分区key的时候进行表达式运算，但是可以定义多个分区key。
PARTITION BY RANGE COLUMNS(sum, avg) (
PARTITION p0 VALUES LESS THAN (20, 5),
PARTITION p1 VALUES LESS THAN (40, 10),
PARTITION p2 VALUES LESS THAN (60, 15),
PARTITION p3 VALUES LESS THAN MAXVALUE
)
2.7 复合分区
MySQL允许对Range和List分区后的每个分区再次进行Hash或Key分区操作。
PARTITION BY LIST(type)
SUBPARTITION BY KEY(id)
SUBPARTTIONS 4
(
...
)
2.8 不同分区技术的对比
如上分别列出了不同的分区技术，接下来进行对比，如下所示：
分区类型 优点 缺点 共性
Range 适合与日期类型，支持复合分区 有限的分区 一般只针对某一列
List 适合与有固定取值的列，支持复合分区 有限的分区，插入记录在这一列的值不值List中，则数据丢失 一般只针对某一列
Hash 线性Hash使得增加、删除和合并分区更快捷 线性Hash的数据分布不均匀，而一般Hash的数据分布较均匀 一般只针对某一列
Key 列可以为字符型等其它非Int类型 效率较之前的低，因为函数为复杂的函数(如MD5或SHA函数) 一般只针对某一列
分区字段中的NULL
分区方式 处理方式
Hash 作为0值
Key 作为0值
Range 作为最小值
List 必须包含在枚举列表
2.9 分区管理
Range、List
//删除某个分区
ALTER TABLE `表名` DROP PARTITION `分区名`;
//增加分区
ALTER TABLE `表名` ADD PARTITION (PARTITION p0 VALUES LESS THAN (10));
//修改分区 并自动迁移数据
ALTER TABLE `表名` REORGANIZE PARTITION INTO `一个或分区名`(更新后的分区)
Range修改分区的时候，修改多个分区必须为连续的分区不可以跳跃的修改。List分区修改或增加的时候，不允许插入枚举列表中已经存在的值。
Hash、Key
Hash和Key两种分区类型不允许直接删除某个分区，但是可以将分区进行合并。
//修改分区个数为2 并合并数据
ALTER TABLE `表名` COALESCE PARTITION PARTITION 2;
COALESCE只可以减少分区个数不可以增加分区个数。
//增加分区个数 增加6个分区
ALTER TABLE `表名` ADD PARTITION PARTITIONS 6;
可以通过以上方式，增加指定数量的分区。下面将分别讲解介绍。
1、删除分区
mysql> alter table user drop partition p5;
在有分区情况下可以不利用delete查询快速地完成垃圾数据的清理。对于有分区的情况下，只需要将某个分区删除掉即可，时间提升是非常高的。
2、新增分区
1)range添加新分区：
mysql> alter table user add partition(partition p5 values less than MAXVALUE);
2)list添加分区：
mysql> alter table list_part add partition(partition p5 values in (5,6,8));
3)hash重新分区：
mysql> alter table hash_part add partition partitions 4;
4)key重新分区：
mysql> alter table key_part add partition partitions 4;
5)子分区添加新分区：
mysql> alter table sub1_part add partition(partition p3 values less than MAXVALUE);
3、重新分区
1)range重新分区
mysql> ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE);
2)list重新分区
mysql> ALTER TABLE list_part REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES in (1,2,3,4,5));
== 分区管理 ==
* 删除分区
ALERT TABLE users DROP PARTITION p0;
删除分区 p0。
* 重建分区
o RANGE 分区重建
ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));
将原来的 p0,p1 分区合并起来，放到新的 p0 分区中。
o LIST 分区重建
ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));
将原来的 p0,p1 分区合并起来，放到新的 p0 分区中。
o HASH/KEY 分区重建
ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2;
用 REORGANIZE 方式重建分区的数量变成2，在这里数量只能减少不能增加，想要增加可以用 ADD PARTITION 方法。
* 新增分区
o 新增 RANGE 分区
ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19)
DATA DIRECTORY = '/data8/data'
INDEX DIRECTORY = '/data9/idx');
新增一个RANGE分区。
o 新增 HASH/KEY 分区
ALTER TABLE users ADD PARTITION PARTITIONS 8;
将分区总数扩展到8个。
4、移除表分区
alter table tb_freeoa REMOVE PARTITIONING;
3.总结和不足
所以，分区的好处有很多：
1. 与单个磁盘或文件系统分区相比，可以存储更多的数据，通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量(DATA DIRECTORY、INDEX DIRECTORY指定存储路径)
2. 对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据
3. 一些查询可以得到极大的优化，如where语句数据可以只保存在一个或多个分区内，涉及到例如SUM() 和 COUNT()这样聚合函数的查询，可以很容易地进行并行处理
在设计分区过程中，需要考虑的因素有很多，如：
– 分区的列
– 分区使用的函数，特别为非Integer类型的列
– 服务器性能
– 内存大小
根据分区技术，有一些技巧：
– 若索引的大小 > RAM，考虑选用分区，不采用索引
– 尽量不采用Primary Key做分区的key
– 当CPU性能高的时候，考虑使用Archive存储引擎
– 对于大量的历史数据，考虑使用Archive+PARTITION
–总之，MySQL分区技术是一种逻辑的水平分表技术；
它只访问需要访问的分区，从而提高性能；
支持range, hash, key, list和复合分区方法；
支持MySQL服务器所支持的任何存储引擎；
除了Key分区方法，Partition的key 必须是整数(或者能转化成整数)，另外innodb的分区表性能上会比一般的innodb表稍差。
分区的新增、删除每次只能是一个，目前不支持操作多个区。
range分区对于不在分区范围内的数据写入时会报错，不能写入。对于未指定对最大值进行处理的分区需要及时添加相应的分区！否则需要指定对最大值的处理。
最终结论
* 对于大数据量，建议使用分区功能
* 去除不必要的字段
* 根据手册， 增加myisam_max_sort_file_size 会增加分区性能
参考来源：
http://dev.mysql.com/doc/refman/5.6/en/select.html
该文章最后由 阿炯 于 2018-06-19 06:12:08 更新，目前是第 3 版。