MySQL 5.5 分区性能
分区历史：
Mysql5.1.3版本带着分区功能与大家见面了，在这之前如果想分表，可以先建立单独的表，再使用Merge引擎联合各表。Mysql分区功能走在老大ORACLE的后面，不过性能也不及oracle。
导读：
分区是一把双刃剑，在应用的时候要根据业务场景指定特定的规则。分区主要功能用于切分数据，用于保证在大数量的情况能快速的定位记录，前提是分区规则适合你的应用，其实就好比如果你要猜谜一样，对方给了一个提示，对于你找到谜底相对简单多了。
分区类型\应用场景：
四种类型：RANGE、LIST、COLUMN（range\list）、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性能，弊处冗余了数据
文章出处：http://www.mysqlops.com/2011/10/27/mysql5-5-partition-performance.html
