请教mysql表分区后性能问题

业务需求一个表每天新增100+W数据,数据不断累积造成查询删除过慢,所以想用到表分区,将表进行按日分区方式,但测试效果无明显变化,请教各位怎么处理。
测试如下:
建立2个表
1.
CREATETABLEPortData
(
PortIDVARCHAR(20),
DevIDINT,
StatusVARCHAR(20),
GenerateTimeDATETIME,
IndexIndex_DevID(DevID),
IndexIndex_Time(GenerateTime)
)ENGINE=MyISAMDEFAULTCHARSET=utf8
/*!50100PARTITIONBYRANGE(to_days(GenerateTime))
(PARTITIONp2012_10_11VALUESLESSTHAN(to_days('2012-10-12'))ENGINE=MyISAM,
PARTITIONp2012_10_12VALUESLESSTHAN(to_days('2012-10-13'))ENGINE=MyISAM,
PARTITIONp2012_10_13VALUESLESSTHAN(to_days('2012-10-14'))ENGINE=MyISAM,
PARTITIONp2012_10_14VALUESLESSTHAN(to_days('2012-10-15'))ENGINE=MyISAM,
PARTITIONp2012_10_15VALUESLESSTHAN(to_days('2012-10-16'))ENGINE=MyISAM,
PARTITIONp2012_10_16VALUESLESSTHAN(to_days('2012-10-17'))ENGINE=MyISAM,
PARTITIONp2012_10_17VALUESLESSTHAN(to_days('2012-10-18'))ENGINE=MyISAM,
PARTITIONp2012_10_18VALUESLESSTHAN(to_days('2012-10-19'))ENGINE=MyISAM,
PARTITIONp2012_10_19VALUESLESSTHAN(to_days('2012-10-20'))ENGINE=MyISAM,
PARTITIONp_catch_allVALUESLESSTHANMAXVALUEENGINE=MyISAM)*/;
2.
CREATETABLEPortData_1
(
PortIDVARCHAR(20),
DevIDINT,
StatusVARCHAR(20),
GenerateTimeDATETIME,
IndexIndex_DevID(DevID),
IndexIndex_Time(GenerateTime)
)ENGINE=MyISAMDEFAULTCHARSET=utf8

2个表各插入1000W数据,GenerateTime的值:2012-10-1111:20:29——2012-10-2011:20:29

然后分别
select*fromportdatawheregeneratetime<'2012-10-15'andgeneratetime>'2012-10-12';
select*fromportdata_1wheregeneratetime<'2012-10-15'andgeneratetime>'2012-10-12';
发现所用时间差别不大

而且使用explain查看
mysql>explainselectcount(*)fromportdata_1wheregeneratetime<'2012-10-15'andgeneratetime>'2012-10-12';
|id|select_type|table|type|possible_keys|key|key_len|
ref|rows|Extra|
|1|SIMPLE|portdata_1|range|Index_Time|Index_Time|9|
NULL|2969959|Usingwhere;Usingindex|
没分区的扫描2969959
mysql>explainpartitionsselectcount(*)fromportdatawheregeneratetime<'2012-10-15'andgeneratetime>'2012-10-12';
|id|select_type|table|partitions
|type|possible_keys|key|key_len|ref|rows|Extra
|
|1|SIMPLE|portdata|p2012_10_11,p2012_10_12,p2012_10_13,p2012_10_14
|index|Index_Time|Index_Time|9|NULL|10000000|Usingwhere;
Usingindex|
分区的反而扫描10000000

请问性能好像没有提升,为什么没分区的扫描反而少一些？
是我分区选择方式有问题还是哪里做错了？
谢谢各位指点一二

9 个解决方案

#1

这个最好还是一天一张表表名以日期结尾或者每月一张表也行

9 个解决方案

#1

更多相关文章

随机推荐