业务需求一个表每天新增100+W数据,数据不断累积造成查询删除过慢,所以想用到表分区,将表进行按日分区方式,但测试效果无明显变化,请教各位怎么处理。
测试如下:
建立2个表
1.
CREATE TABLE PortData
(
PortID VARCHAR(20),
DevID INT,
Status VARCHAR(20),
GenerateTime DATETIME,
Index Index_DevID(DevID),
Index Index_Time(GenerateTime)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (to_days(GenerateTime))
(PARTITION p2012_10_11 VALUES LESS THAN (to_days('2012-10-12')) ENGINE = MyISAM,
PARTITION p2012_10_12 VALUES LESS THAN (to_days('2012-10-13')) ENGINE = MyISAM,
PARTITION p2012_10_13 VALUES LESS THAN (to_days('2012-10-14')) ENGINE = MyISAM,
PARTITION p2012_10_14 VALUES LESS THAN (to_days('2012-10-15')) ENGINE = MyISAM,
PARTITION p2012_10_15 VALUES LESS THAN (to_days('2012-10-16')) ENGINE = MyISAM,
PARTITION p2012_10_16 VALUES LESS THAN (to_days('2012-10-17')) ENGINE = MyISAM,
PARTITION p2012_10_17 VALUES LESS THAN (to_days('2012-10-18')) ENGINE = MyISAM,
PARTITION p2012_10_18 VALUES LESS THAN (to_days('2012-10-19')) ENGINE = MyISAM,
PARTITION p2012_10_19 VALUES LESS THAN (to_days('2012-10-20')) ENGINE = MyISAM,
PARTITION p_catch_all VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */;
2.
CREATE TABLE PortData_1
(
PortID VARCHAR(20),
DevID INT,
Status VARCHAR(20),
GenerateTime DATETIME,
Index Index_DevID(DevID),
Index Index_Time(GenerateTime)
) ENGINE=MyISAM DEFAULT CHARSET=utf82个表各插入1000W数据,GenerateTime的值:2012-10-11 11:20:29 —— 2012-10-20 11:20:29然后分别
select * from portdata where generatetime < '2012-10-15' and generatetime > '2012-10-12';
select * from portdata_1 where generatetime < '2012-10-15' and generatetime > '2012-10-12';
发现所用时间差别不大而且使用explain查看
mysql>explain select count(*) from portdata_1 where generatetime < '2012-10-15' and generatetime > '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 | Using where; Using index |
没分区的扫描2969959
mysql>explain partitions select count(*) from portdata where generatetime < '2012-10-15' and generatetime > '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 | Using where;
Using index |
分区的反而扫描10000000请问性能好像没有提升,为什么没分区的扫描反而少一些?
是我分区选择方式有问题还是哪里做错了?
谢谢各位指点一二
测试如下:
建立2个表
1.
CREATE TABLE PortData
(
PortID VARCHAR(20),
DevID INT,
Status VARCHAR(20),
GenerateTime DATETIME,
Index Index_DevID(DevID),
Index Index_Time(GenerateTime)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (to_days(GenerateTime))
(PARTITION p2012_10_11 VALUES LESS THAN (to_days('2012-10-12')) ENGINE = MyISAM,
PARTITION p2012_10_12 VALUES LESS THAN (to_days('2012-10-13')) ENGINE = MyISAM,
PARTITION p2012_10_13 VALUES LESS THAN (to_days('2012-10-14')) ENGINE = MyISAM,
PARTITION p2012_10_14 VALUES LESS THAN (to_days('2012-10-15')) ENGINE = MyISAM,
PARTITION p2012_10_15 VALUES LESS THAN (to_days('2012-10-16')) ENGINE = MyISAM,
PARTITION p2012_10_16 VALUES LESS THAN (to_days('2012-10-17')) ENGINE = MyISAM,
PARTITION p2012_10_17 VALUES LESS THAN (to_days('2012-10-18')) ENGINE = MyISAM,
PARTITION p2012_10_18 VALUES LESS THAN (to_days('2012-10-19')) ENGINE = MyISAM,
PARTITION p2012_10_19 VALUES LESS THAN (to_days('2012-10-20')) ENGINE = MyISAM,
PARTITION p_catch_all VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */;
2.
CREATE TABLE PortData_1
(
PortID VARCHAR(20),
DevID INT,
Status VARCHAR(20),
GenerateTime DATETIME,
Index Index_DevID(DevID),
Index Index_Time(GenerateTime)
) ENGINE=MyISAM DEFAULT CHARSET=utf82个表各插入1000W数据,GenerateTime的值:2012-10-11 11:20:29 —— 2012-10-20 11:20:29然后分别
select * from portdata where generatetime < '2012-10-15' and generatetime > '2012-10-12';
select * from portdata_1 where generatetime < '2012-10-15' and generatetime > '2012-10-12';
发现所用时间差别不大而且使用explain查看
mysql>explain select count(*) from portdata_1 where generatetime < '2012-10-15' and generatetime > '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 | Using where; Using index |
没分区的扫描2969959
mysql>explain partitions select count(*) from portdata where generatetime < '2012-10-15' and generatetime > '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 | Using where;
Using index |
分区的反而扫描10000000请问性能好像没有提升,为什么没分区的扫描反而少一些?
是我分区选择方式有问题还是哪里做错了?
谢谢各位指点一二
是不是你的分区表数据都是在12到15号范围啊 select count(*)是多少数据
count(*)都是300W,就是12,13,14 3天的数据,
因为p2012_10_11表示'2012-10-11 00:00:00'--'2012-10-11 23:59:59'的数据
PARTITION p2012_10_11 VALUES LESS THAN (to_days('2012-10-12')) ENGINE = MyISAM,
但select 中并无2012-10-11 的数据,为什么会用到p2012_10_11表示很奇怪
求教真相!