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

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

如果删除小表,不担心效率,可以用下面方式删除,
[url]http://jimmy9495.iteye.com/admin/blogs/2072785[/url]
但是用上面的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语句不支持别名? [url]http://blog.chinaunix.net/uid-20639775-id-3167446.html[/url]
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值