嗨!
我需要在mysql(5.1)上扫描一个非常大的表,
这是表格或多或少的样子:
CREATE TABLE `big_table` (
`id` BIGINT(11) NOT NULL AUTO_INCREMENT,
`main_id` INT(11) DEFAULT NULL,
`key` VARCHAR(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `main_id_key` (`main_id`,`key`),
) ENGINE=INNODB AUTO_INCREMENT=2315678197 DEFAULT CHARSET=utf8
我需要将main_id键的所有唯一值放入新表中 .
使用以下查询需要花费大量时间(在非常快的服务器上仍然运行3天后):
CREATE TABLE `get_unique` (
`main_id` int(11) NOT NULL,
`key` varchar(20) NOT NULL,
PRIMARY KEY (`main_id`,`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT IGNORE INTO get_unique
SELECT main_id,key FROM big_table
So my question is - Will this be faster?
CREATE TABLE `get_unique` (
`main_id` int(11) NOT NULL,
`key` varchar(20) NOT NULL,
PRIMARY KEY (`main_id`,`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO get_unique
SELECT main_id,key FROM big_table
GROUP BY 1,2
1 回答
是
GROUP BY main_id, key
的执行速度比INSERT IGNORE
快许多倍 .SELECT.. GROUP BY main_id, key
将通过使用覆盖索引更快地执行并导致更少数量的记录,而INSERT IGNORE
将涉及插入的每一行的INDEX KEY
查找 .