mysql高效删除大数据量表中的重复数据

boss_t_tour表目前有150W数据，其中出现了15000多条有重复记录的数据，需要删除其中的8000多条多余的记录。

如果删除小表，不担心效率，可以用下面方式删除，
http://jimmy9495.iteye.com/admin/blogs/2072785
但是用上面的sql如果想在大表操作删除，肯定是不行的。


查看表中imsi除了空以外重复的数目。

SELECT COUNT(t.`imsi`),t.`imsi` FROM `boss_t_tour` t WHERE t.imsi <> ''
GROUP BY t.`imsi` HAVING COUNT(t.`imsi`) >1 

mysql大数据表中的快速删除部分数据办法：

1.创建删除重复的存储过程

DELIMITER $$

USE `bossdb` $$

DROP PROCEDURE IF EXISTS `del` $$

CREATE DEFINER = `root` @`localhost` PROCEDURE `del` () 
BEGIN
/** 定义后面循环用到的变量*/
  DECLARE coun1 INT ;
  DECLARE count2 INT ;
  
DROP TABLE IF EXISTS tmp_imsi;
DROP TABLE IF EXISTS tmp_all;
DROP TABLE IF EXISTS tmp_keep;
DROP TABLE IF EXISTS tmp_delete;
/** 创建临时表*/
CREATE TABLE tmp_imsi AS SELECT t.imsi AS imsi FROM boss_t_tour t WHERE t.imsi <> '' GROUP BY t.imsi HAVING COUNT(t.imsi) >1; 
CREATE TABLE tmp_all AS SELECT t.id,t.imsi AS imsi FROM boss_t_tour t WHERE t.imsi IN (SELECT imsi FROM tmp_imsi); 
CREATE TABLE tmp_keep AS SELECT MIN(a.id) AS id FROM tmp_all a GROUP BY a.imsi; 
CREATE TABLE tmp_delete AS SELECT a.id AS id FROM tmp_all a WHERE a.id NOT IN (SELECT id FROM tmp_keep) ;

/** 先删除索引提高删除速度*/
ALTER TABLE `bossdb`.`boss_t_tour`   
  DROP INDEX `imsi_index`,
  DROP INDEX `syncstatusInded`;

/** 循环删除开始*/

/** 原计划用此子查询删除，DELETE FROM boss_t_tour WHERE EXISTS (SELECT 1 FROM tmp_delete WHERE boss_t_tour.id = tmp_delete.id);*/
/** 但是发现mysql子查询删除效率奇慢,本机测试主表150W数据 临时表8000条数据 删除3000条用了一个小时，效率太差不敢在生产环境使用。*/
/** 用下面的循环删除效率高很多，删除8000多条数据5分钟 */

  SELECT COUNT(*) INTO coun1 FROM tmp_delete;
  WHILE coun1 > 0 DO 
    SELECT id INTO count2 FROM tmp_delete LIMIT 1 ;
    DELETE FROM boss_t_tour WHERE id = count2 ;
    DELETE FROM tmp_delete WHERE id = count2 ;
    COMMIT ;
    SET coun1 = coun1 - 1 ;
  END WHILE ;
/** 循环删除结束*/

/** 重建索引*/
ALTER TABLE `bossdb`.`boss_t_tour`   
  ADD  INDEX `imsi_index` (`imsi`),
  ADD  INDEX `syncstatusInded` (`sync_status`);

/** 删除临时表*/
DROP TABLE IF EXISTS tmp_imsi;
DROP TABLE IF EXISTS tmp_all;
DROP TABLE IF EXISTS tmp_keep;
DROP TABLE IF EXISTS tmp_delete;
  
END $$

DELIMITER ;

2.执行存储过程

CALL del();

本机执行5分钟完成。





写的过程中还发现个问题mysql的delete操作居然不能给 表定义别名。。。
MYSQL delete语句不支持别名？ http://blog.chinaunix.net/uid-20639775-id-3167446.html

评论

3 楼 jimmy9495 2014-06-03  

kidding87 写道

seaboycs 写道

当你的数据达到千万，亿级别时删索引就要花你太多太多时间。

先建表，倒过来数据后再建索引就块多了

mysql文档有一种解决方案，数据量级别太大目前的mysql貌似只能用建个新表再重命名。
建个新表，把要保留的数据存进去，再删了之前的表，把新表重命名成老表的名字。
再建索引。
http://www.2cto.com/database/201211/170796.html

2 楼 kidding87 2014-05-30  

seaboycs 写道

当你的数据达到千万，亿级别时删索引就要花你太多太多时间。

先建表，倒过来数据后再建索引就块多了

1 楼 seaboycs 2014-05-30  

当你的数据达到千万，亿级别时删索引就要花你太多太多时间。