mysql表重複資料刪除記錄方法總結及效率對比

下面這些方法在我虛擬機器上做的測試，記憶體384M,交換分區1024M, test共300W資料，重複記錄3.5W，需求如題目所示，表結構如下：

CREATEDATABASE/*!32312 IF NOT EXISTS*/`test` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `test`;

/*Table structure for table `test` */

DROPTABLEIFEXISTS `test`;

CREATETABLE `test` (
`id` int(11) NOTNULL AUTO_INCREMENT,
`name` char(20) DEFAULTNULL COMMENT '姓名',
`age` tinyint(4) DEFAULTNULL COMMENT '年齡',
`mate` tinyint(4) DEFAULT'1' COMMENT '有無配偶(1-有 0-無)',
PRIMARYKEY (`id`),
KEY `idx_name` (`name`),
KEY `idx_age` (`age`)
) ENGINE=MyISAM AUTO_INCREMENT=10DEFAULT CHARSET=utf8;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

現有記錄：

insertinto `test`(`id`,`name`,`age`,`mate`) values (2,'aaaaa',28,0),
(3,'bbbb',23,0),
(4,'cccc',25,1),
(5,'dddd',26,0),
(6,'eeee',24,0),
(7,'fffff',18,0),
(8,'eeee',40,1),
(9,'eeee',60,1);

想去掉name重名的記錄，方法如下：

   1.給name欄位修改成唯一索引：

dropindex idx_name on test;
altertable test adduniqueindex (name);

這樣當向表中添加相同記錄的時候，會返回1062的添加失敗資訊。
但是有一種情況是表中已經有n個重複的記錄，這時候我們才想起來要添加唯一索引，再執行上面的操作時，資料庫會告訴你已經有重複的記錄了，建立索引失敗，這時候，我們可以用下面的操作：

alter ignore table test addunique idx_name (name);

它會重複資料刪除的記錄（別怕，會保留一條）(但是這個命令在MySQL5.1.37之前是可以的，在5.1.48以後就行不通了)，然後建立唯一索引，高效而且人性化。

    2.重建表方法一：

建立另外一個表，為了防止原來的表結構丟失，可以先建立一個這樣的“暫存資料表”，

USE `test`;

/*Table structure for table `test` */

DROPTABLEIFEXISTS `uniq_test`;

CREATETABLE `uniq_test` (
`id` int(11) NOTNULL AUTO_INCREMENT,
`name` char(20) DEFAULTNULL COMMENT '姓名',
`age` tinyint(4) DEFAULTNULL COMMENT '年齡',
`mate` tinyint(4) DEFAULT'1' COMMENT '有無配偶(1-有 0-無)',
PRIMARYKEY (`id`),
KEY `idx_name` (`name`),
KEY `idx_age` (`age`)
) ENGINE=MyISAM AUTO_INCREMENT=10DEFAULT CHARSET=utf8;

從test表中尋找資料，添加到uniq_test中：

insertinto uniq_test select*from test groupby name;
drop table test;
rename table uniq_test to test;

3.重複資料刪除記錄法：

建立一個表用來存放，要刪除的記錄的id資訊:

CREATETABLE `tmp_ids` (
`id` int(11),
`name` char( 20)  
) ENGINE=MyISAM;

如果要刪除的記錄不多的話，可以把這個表建立成記憶體表形式：

CREATETABLE `tmp_ids` (
`id` int(11),
`name` char( 20)
) ENGINE=HEAP;

然後在test表中重複資料刪除記錄：

insertinto tmp_ids selectmin(id),name from test groupby name havingcount(*)>1 order by null;
delete a.* from test a,tmp_ids b where b.name=a.name and a.id>b.id;
truncatetable tmp_ids;

4.效率低下方法

DELETE test AS a FROM test AS a,
(
SELECT*
FROM test
GROUPBY name
HAVINGcount(1) >1
orderbynull
) AS b
WHERE a.name = b.name AND a.id > b.id;

總結：

第一種方法曆史22分鐘，系統負載5左右；

第二種方法效率非常低下，把未知索引檔案破壞，終止執行

第三種方法曆時17分鐘，其中

insertinto tmp_ids selectmin(id),name from test groupby name havingcount(*)>1orderbynull

曆時15分鐘，刪除動作曆時2分鐘，系統負載3左右

第四種方法，執行過程中，把它test的索引檔案都破壞了，可見“威力”之大；