MySQL 5.5 分区性能

1
MySQL HTML C/C++ Go 16407 次浏览

分区历史:
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

请尽量让自己的答案能够对别人有帮助

1个答案

默认排序 按投票排序