分区历史：

　　Mysql5.1.3版本带着分区功能与大家见面了，在这之前如果想分表，可以先建立单独的表，再使用Merge引擎联合各表。Mysql分区功能走在老大ORACLE的后面，不过性能也不及oracle。

　　导读：

　　分区是一把双刃剑，在应用的时候要根据业务场景指定特定的规则。分区主要功能用于切分数据，用于保证在大数量的情况能快速的定位记录，前提是分区规则适合你的应用，其实就好比如果你要猜谜一样，对方给了一个提示，对于你找到谜底相对简单多了。

　　分区类型应用场景：

　　四种类型：RANGE、LIST、COLUMN(rangelist)、HASH、KEY

　　应用场景：存储历史记录，大数量的在线业务，数据分析系统，主要用于传统业务场景，另外请与sharding区分开来。

　　注意事项：

• 　　如果表中存在主键，则分区函数必须包含主键部分
• 　　各个分区对待分区列值为NULL的方式不同
• 　　官方非用户重新编译mysql版本最大分区数为1024
• 　　Drop partition会将该分区的所有数据删除
• 　　目前子分区都必须是同一引擎
• 　　timestamp字段作为partition的分区列是不被允许的，但是可以使用UNIX_TIMESTAMP()解决该问题，请参考Bug #42849,已经被加入到解决列表中,具体解决时间不清楚
• 　　分区表达式目前只支持一些函数，具体参考Partitioning Limitations Relating to Functions
• 　　分区不能太多，要适度。最好再你的生产服务器上测试，接下来会对折部分测试

　　实例测试：

　　目的：测试分区数为1,16,128,256,512,1024的各种情况下,INSERT和UPDATE有多大区别?

　　环境及数据：mysql5.5,mysql5.1,MEM 62G,2.5T，RHEL5 x86_64

　　innodb_additional_mem_pool_size=16M

　　建立一张测试表sbtest_part, 数据1024000行，以id作为RANGE分区列

CREATE TABLE `sbtest_part` ( 　　`id` int(10) unsigned NOT NULL AUTO_INCREMENT, 　　`k` int(10) unsigned NOT NULL DEFAULT ’0′, 　　`c` char(120) NOT NULL DEFAULT ”, 　　`pad` char(60) NOT NULL DEFAULT ”, 　　PRIMARY KEY (`id`), 　　KEY `k` (`k`) 　　) ENGINE=InnoDB DEFAULT CHARSET=utf8

　　步骤：

　　1. 修改sbtest_part的分区数，执行命令：

mysqlslap –delimiter=”;” –number-of-queries=2048 –iterations=1000 –query=”use test;INSERT INTO sbtest_part(k,c,pad) VALUES(1,’cccccckkkkk’,’pppppppkkkkkk’)” –user=root –socket=/data/mysqldata3307/sock/mysql.sock >> /tmp/16_test_insert.txt 　　mysqlslap –delimiter=”;” –number-of-queries=2048 –iterations=1000 –query=”use test;SET @a=FLOOR(1+RAND()*1024000);UPDATE sbtest_part SET c=’ppppkkk’ WHERE id=@a;” –user=root –socket=/data/mysqldata3307/sock/mysql.sock >> /tmp/16_test_update_c.txt 　　mysqlslap –delimiter=”;” –number-of-queries=2048 –iterations=1000 –query=”use test;SET @a=FLOOR(1+RAND()*1024000);UPDATE sbtest_part SET k=@a WHERE id=@a;” –user=root –socket=/data/mysqldata3307/sock/mysql.sock >> /tmp/16_test_update_k.txt

　　结果：

分区数	INSERT平均时间	UPDATE c平均时间	UPDATE k平均时间
1	0.155	0.140	0.143
16	0.184	0.133	0.163
64	0.267	0.170	0.207
128	0.338	0.232	0.259
256	0.524	0.355	0.409
512	0.900	0.667	0.698
1024	1.603	1.529	1.521

　　在1,16,128,256,512,1024个分区情况下中，两个相邻分区数之间增加的百分比对比表：

分区数	INSERT平均时间增加的百分比(%)	UPDATE c平均时间增加的百分比(%)	UPDATE k平均时间增加的百分比(%)
1	/	/	/
16	18.7	误差	14.0
64	45.1	27.8	27.0
128	26.6	36.5	25.1
256	55.0	53.0	57.9
512	71.8	87.9	70.7
1024	78.1	129.2	117.9

　　根据以上测试，初步可以判断分区数在128-256这个区间对INSERT、UPDATE操作影响比较大，所以在部署之前就要考虑这些效率问题。

　　当分区数在512、1024时，经过show processlist查看System lock、closing tables这两种状态在耗时比较长，这个应该是由于分区表数目过大。

　　另外模拟个情景：在100W数据或者更多的情况下，经常我们有这样的需求查找某段时间之内,某个任务的某个状态的那些人的所有信息?

　　表结构：CREATE TABLE `task_1` (

　　`UID` bigint(20) unsigned NOT NULL DEFAULT ’0′,

　　`TDID` int(10) unsigned NOT NULL,

　　`TYPE` tinyint(3) unsigned NOT NULL DEFAULT ’0′,

　　`s` tinyint(3) unsigned NOT NULL DEFAULT ’0′,

　　`date` int(8) unsigned NOT NULL DEFAULT ’0′,

　　PRIMARY KEY (`UID`,`TDID`,`date`)

　　) ENGINE=InnoDB DEFAULT CHARSET=utf8

　　/*!50100 PARTITION BY HASH (UID)

　　PARTITIONS 16 */

　　版本：5.1和5.5

　　我们会有两种方案：

• 　　为task_1建立索引(date,tdid,s)：
• 　　创建一张临时表task_2 ，只有列(date,tdid,s,uid),并对这些列做联合主键，再uid连接task_1，这样的好处是创建一个global index

版本	5.1版本		5.5版本
	task_1建立key(date,tdid)耗时	task_1没有索引耗时	task_1建立key(date,tdid)耗时	task_1没有索引耗时
方案1	0.00	0.70	0.04 sec	0.62 sec
方案2	0.17	0.11	0.13 sec	0.13 sec

　　第一种方案利于在搜索更加快速，弊于索引维护成本高，会跨分区进行索引IO会增大，而且在5.1生产环境上锁表时间长;方案二利处不修改原表，而且能够大幅提高SELECT性能，弊处冗余了数据