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

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` ADDINDEX `imsi_index` (`imsi`),ADDINDEX `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 ;

CALL del();