NingG +

MySQL--数据去重

2013-01-14

一张表有几个字段：

字段 类型
IP varchar(30)
timeStart double(13,3)
durationTime double(13,3)
httpURL varchar(1000)
terminalType varchar(1000)

现在要去重，重复的标准：IP相同，httpURL也相同；保留重复数据中timeStart最小的那条记录。估计的瓶颈: 数据量很大，近千万条记录，需要考虑方案的可行性和效率。

猜测：MySQL数据库中有没有专门负责去重的机制？

经初步分析，可选择的方法有：

  1. 在同一张表中进行处理；
  2. 使用另一张表格；
  3. 使用文本进行处理；

针对上面3种方法，进行简要说明：

现在，咱们先按某一字段进行排序[1]：

  1. SELECT * FROM oldTalbe \
  2. ORDER BY IP,timeStart LIMIT 10;

现在有了预期的排序结果，但是怎样才能以这个结果来更新数据库呢？有一个简单的办法，新建一个额外表，并将查询的结果逐条插入。复制表结构[2]的sql语句如下：

  1. CREATE TABLE newTable LIKE oldTable;

将查询结果逐条的插入到newTable中[2][3]：

  1. INSERT INTO newTable \
  2. 			   SELECT * FROM oldTable \
  3. 			   ORDER BY IP,timeStart;

运行上面的代码时间：21s，总数据量：260万。（若不使用排序，只进行数据复制，耗时4.3s）我们无聊了(这一步仅仅是测试，自己玩的，不感兴趣，可以直接跳过)：尝试将无序的记录先添加入新建的表格中，针对同一个表格使用insert into和select，即sql语句如下：

  1. INSERT INTO newTable 
  2. 			   SELECT * FROM oldTable; 
  3. INSERT INTO newTable \
  4. 			   SELECT * FROM newTable \
  5. 			   ORDER BY IP,timeStart;

测试结果显示，后来执行完之后，数据翻倍变为520万，而且时间在4mins以上。

刚刚lby大牛路过，我把自己的问题描述了一下；他说自己之前做过，有两种办法，一时间只想到一种：先复制表结构到新表newTable，然后对于newTable添加一个约束Unique(IP,httpURL)，OK；现在可以向newTable中添加数据了，约束条件会帮助我们自动去重。完整的代码如下[4]：

  1. #复制表结构
  2. CREATE TABLE newTable LIKE oldTable;
  3.  
  4. #修改表字段，因为httpURL太大
  5. ALTER TABLE newTable MODIFY httpURL VARCHAR(300);
  6.  
  7. #为表格添加约束条件（在Mysql5中，如果约束字段过大，则报错，因此要先执行上面的修改表字段）
  8. ALTER TABLE newTable ADD UNIQUE(IP,timeStart);
  9.  
  10. #为新表中添加数据（约束条件自动去重）
  11. INSERT IGNORE newTable \
  12. 			   (SELECT * FROM newTable \
  13. 			   ORDER BY IP,timeStart);

NOTE:现有MySQL中注释方法：1）单行注释，“#”，“–-空格/tab”；2）多行注释，“/…/”

MySQL中提前终止或者后台运行SQL语句的操作：ctrl+c or ctrl+d；

Lby大牛刚才过来，提醒了一下可以考虑SQL中select distinct IP,httpURL的相关语句，至少可以使用下面的语句，统计一下去重后最终的记录个数：

  1. SELECT COUNT(DISTINCT IP,httpURL) \
  2. 				  FROM oldTable;

测试结果发现，上面的记录个数要比之前使用Unique进行约束方式，多了10条记录。猜测：跟httpURL字段被人为减少到varchar(300)有关。(后来的几次运行，此问题消失了)。

遗留问题：思考将上面的内容以脚本形式书写出来，并且将newTable、oldTable作为变量输入。思路：1）在SQL语句脚本内，设置变量；2）使用单独的shell脚本，配合SQL语句脚本。

NOTE:如何验证去重是否完成？基本思路：

  1. 从oldTable中随便取出一个IP对应的多条记录进行处理，获取其中不同httpURL的个数，以及每个httpURL对应的最小timeStart字段；
  2. 从newTable中找出对应的IP，查询其对应的httpURL，以及对应的时间。（与上面的结果进行对比，如果完全相同，则说明去重正确）

具体验证代码如下：

针对oldTable执行下面代码

  1. #对应上面的步骤1
  2. #从oldTable中查询IP,可以知道哪些IP的记录最多
  3. SELECT IP,COUNT(*) FROM oldTable GROUP BY IP ORDER BY COUNT(*);
  4.  
  5. #从上面的结果中，找出记录数较多的IP，查询其对应的httpURL以及最小的时间，要求：按照httpURL进行排序
  6. SELECT DISTINCT httpURL，MIN（timeStart） FROM oldTable WHERE IP='10.10.10.10' ORDER BY httpURL;

针对newTable执行下面代码

  1. #对应上面的步骤2
  2. #从newTable中找出对应IP的httpURL，以及timeStart
  3. SELECT httpURL，timeStart FROM newTable WHERE IP='10.10.10.10' ORDER BY httpURL;

如果上面针对oldTable和newTable的查询结果相同，则表示去重完成。（可以随意更换上面的IP值，进行验证）

刚刚还说上面的遗留问题，现在已经解决了，用了一下午时间吧。下面对于以SQL脚本实现上述功能，进行一个小结。我们使用MySQL的存储过程，来实现动态输入变量newTable、oldTable。最终代码如下：

  1. /*
  2.  *usage：
  3.  *      1) login the 'mysql' ;
  4.  *      2) source ~/deduc.sql;
  5.  *      3) CALL deduc('oldTable','newTable');
  6.  *
  7.  *function: de-duplication, remove the repeat record, and store the result in a new table;
  8.  *
  9.  *parameters:
  10.  *      oldTable: the original table, that we need to remove the repeat record;
  11.  *      newTable: the new build table, where we store the results.
  12.  *
  13.  *author：Ning Guo
  14.  *
  15.  *time: 1/15/2013
  16.  *
  17.  *程序运行效果：
  18.  *  260万数据，去重之后为60万，执行时间为：1min23s
  19.  */
  20.  
  21. USE chinacache;
  22. DROP PROCEDURE IF EXISTS deduc;
  23. DELIMITER //
  24. CREATE PROCEDURE deduc(
  25. 		$oldTable VARCHAR(100),
  26. 		$newTable VARCHAR(100)
  27. )
  28. BEGIN
  29. 		SET @SQL = concat('DROP TABLE IF EXISTS ',$newTable);
  30. 		PREPARE stmt1 FROM @SQL;
  31. 		EXECUTE stmt1;
  32. 		DEALLOCATE PREPARE stmt1;
  33.  
  34. 		SET @SQL = concat('CREATE TABLE ',$newTable,' LIKE ',$oldTable);
  35. 		PREPARE stmt1 FROM @SQL;
  36. 		EXECUTE stmt1;
  37. 		DEALLOCATE PREPARE stmt1;
  38.  
  39. 		SET @SQL = concat('ALTER TABLE ',$newTable,' MODIFY httpURL VARCHAR\(300\)');
  40. 		PREPARE stmt1 FROM @SQL;
  41. 		EXECUTE stmt1;
  42. 		DEALLOCATE PREPARE stmt1;
  43.  
  44. 		SET @SQL = concat('ALTER TABLE ',$newTable,' ADD CONSTRAINT UNIQUE\(IP\,httpURL\)');
  45. 		PREPARE stmt1 FROM @SQL;
  46. 		EXECUTE stmt1;
  47. 		DEALLOCATE PREPARE stmt1;
  48.  
  49. 		SET @SQL = concat('INSERT IGNORE ',$newTable,' \(SELECT \* FROM ',$oldTable,' ORDER BY IP\,timeStart\)');
  50. 		PREPARE stmt1 FROM @SQL;
  51. 		EXECUTE stmt1;
  52. 		DEALLOCATE PREPARE stmt1;
  53.  
  54. END //
  55. DELIMITER ;

由于MySQL不支持表名作为存储过程的变量，因此应该使用预定义语句：PREPARE、EXECUTE、DEALLOCATE PREPARE来进行实现[6]。

什么是存储过程？可以将其看做一个批处理文件，包含多条MySQL语句，其会根据输入的参数（可以没有输入参数），有选择地执行对应的程序；而且可以有返回参数。

DELIMITER //的使用：为了区分存储过程内;与存储过程结束的标志，使用DELIMITER来临时更改语句结束分隔符。

调用存储过程：CALL deduc()；

删除存储过程：DROP PROCEDURE deduc；

存储过程传入的参数不能作为表名：因此借鉴使用预定义语句PREPARE、EXECUTE、DEALLOCATE PREPARE。[8]

几个常用的查询命令：

命令 说明
show procedure status 查询所有procedure的名称、创建时间
show create procedure “procedure_name” 查询某一procedure的创建语句
  1. 《MySQL必知必会》Page33；
  2. http://www.2cto.com/database/201202/120259.html
  3. 《MySQL必知必会》Page136；
  4. http://blog.sina.com.cn/s/blog_6fb90ed30100o09p.html
  5. http://www.bhcode.net/article/20090220/4175.htm
  6. http://bbs.csdn.net/topics/330197598
  7. 《MySQL必知必会》Page163；
  8. http://blog.csdn.net/freecodetor/article/details/5818283

原文地址：https://ningg.top/mysql-data-cleaning/
微信公众号 ningg, 联系我

同类文章:

微信搜索: 公众号 ningg, 联系我, 交个朋友.

京ICP备19013747号-1
Top