MySQL5.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性能，弊处冗余了数据

提示：5.5版本对于ADD INDEX,DROP INDEX操作做了大的修改，以至于不需要再复制原表的数据，提高了ADD INDEX ,DROP INDEX的相率，这个再下一章会详细介绍，即MySQL 5.5版本对普通索引增删性能的优化。