DROP TABLE IF EXISTS `dbbase`.`mytable`;
CREATE TABLE `dbbase`.`mytable` (
`id` bigint(20) unsigned NOT NULL,
`datatype` tinyint(3) unsigned NOT NULL,
`Handle` bigint(20) unsigned DEFAULT NULL,
`ChannelNO` tinyint(3) unsigned NOT NULL,
`Timezone` int(11) DEFAULT NULL,
`BeginTime` decimal(18,0) DEFAULT NULL,
`EndTime` decimal(18,0) DEFAULT NULL,
`OffsetAddr` bigint(20) unsigned NOT NULL,
`Length` bigint(20) unsigned NOT NULL,
`DVR` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`,`ChannelNO`) USING BTREE,
KEY `offsetaddr` (`OffsetAddr`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (ChannelNO)
(PARTITION p0 VALUES LESS THAN (1) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (2) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (3) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (4) ENGINE = MyISAM,
PARTITION p4 VALUES LESS THAN (5) ENGINE = MyISAM,
PARTITION p5 VALUES LESS THAN (6) ENGINE = MyISAM,
PARTITION p6 VALUES LESS THAN (7) ENGINE = MyISAM,
PARTITION p7 VALUES LESS THAN (8) ENGINE = MyISAM,
PARTITION p8 VALUES LESS THAN (9) ENGINE = MyISAM,
PARTITION p9 VALUES LESS THAN (10) ENGINE = MyISAM,
PARTITION p10 VALUES LESS THAN (11) ENGINE = MyISAM,
PARTITION p11 VALUES LESS THAN (12) ENGINE = MyISAM,
PARTITION p12 VALUES LESS THAN (13) ENGINE = MyISAM,
PARTITION p13 VALUES LESS THAN (14) ENGINE = MyISAM,
PARTITION p14 VALUES LESS THAN (15) ENGINE = MyISAM,
PARTITION p15 VALUES LESS THAN (16) ENGINE = MyISAM,
PARTITION p16 VALUES LESS THAN (17) ENGINE = MyISAM,
PARTITION p17 VALUES LESS THAN (18) ENGINE = MyISAM,
PARTITION p18 VALUES LESS THAN (19) ENGINE = MyISAM,
PARTITION p19 VALUES LESS THAN (20) ENGINE = MyISAM,
PARTITION p20 VALUES LESS THAN (21) ENGINE = MyISAM,
PARTITION p21 VALUES LESS THAN (22) ENGINE = MyISAM,
PARTITION p22 VALUES LESS THAN (23) ENGINE = MyISAM,
PARTITION p23 VALUES LESS THAN (24) ENGINE = MyISAM,
PARTITION p24 VALUES LESS THAN (25) ENGINE = MyISAM,
PARTITION p25 VALUES LESS THAN (26) ENGINE = MyISAM,
PARTITION p26 VALUES LESS THAN (27) ENGINE = MyISAM,
PARTITION p27 VALUES LESS THAN (28) ENGINE = MyISAM,
PARTITION p28 VALUES LESS THAN (29) ENGINE = MyISAM,
PARTITION p29 VALUES LESS THAN (30) ENGINE = MyISAM,
PARTITION p30 VALUES LESS THAN (31) ENGINE = MyISAM */;以上是我的表结构,采用了分区,建了两个索引PRIMARY KEY (`id`,`ChannelNO`) USING BTREE, KEY `offsetaddr` (`OffsetAddr`)。
表内有十几万条记录,当我用一下语句进行删除的时候:
DELETE FROM RECORDDATA WHERE offsetaddr between 0 and 999999;
删除7万条记录的时候,耗时达到7s!请问如何优化,谢谢。
要删除60K条记录,如果采用每次删除5k的话,那总共需要删除30次,这30次总耗时会比一次删除60k要快些吗?
直接将key_buffer_size从原来的64M调到256M后,执行时间显著提高。
另外,是不是分区也会影响删除的效率呢?
你的分区并不是按照offsetaddr 来的,所以分区对你的删除并无影响。