MariaDB删除重复记录性能测试

日期:2019-11-29编辑作者:数据库

删除重复记录,只保留id最大的一条记录的性能测试

不管是程序BUG,还是业务变更,重复数据这个老生常谈的问题,总是会出现。以下是我在MariaDB或是MySQL下处理的一些经验。在SQL Server中,使用窗口函数是很容易实现的。不过听说MySQL 8.0和MariaDB 10.2以上均支持窗口函数了。等有机会再来测试使用窗口函数来删除重复记录。

环境

测试表的id为是唯一的,或是自增的主键。

mysql不能直接写循环,只能写在存储过程里。

存储过程usp_batch_insert的参数num_count为插入总行数,参数batch_commit为每批提交的行数。

由于是测试,先把bin log关闭。在生产上做删除重复记录操作,不能随意关闭,根据业务而定。

SET session sql_log_bin = 0;

创建测试表t3

CREATE TABLE `t3` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `f1` varchar(32) DEFAULT NULL,
  `f2` varchar(32) DEFAULT NULL,
  `ctime` datetime(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

每批量提交的记录表t3_log

CREATE TABLE `t3_log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `i` bigint(20) DEFAULT NULL,
  `ctime` datetime(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

构造数据的存储过程

DELIMITER $$
DROP PROCEDURE IF EXISTS `usp_batch_insert`;
CREATE PROCEDURE `usp_batch_insert`(IN `num_count` int,IN `batch_commit` int)
BEGIN
DECLARE i INT; 
SET i = 1;
SET AUTOCOMMIT = 0;

WHILE i <= num_count DO 
  INSERT INTO t3 (f1, f2, ctime) 
  SELECT REPLACE(UUID(), '-', '') AS a, REPLACE(UUID(), '-', '') AS b, NOW(3) AS c;
  SET i = i + 1;
  IF MOD(i, batch_commit) <= 0 THEN
    INSERT INTO t3_log (i, ctime) VALUES (i, NOW(3));
      COMMIT;
    END IF;
END WHILE; 

SET AUTOCOMMIT = 1;
END; $$
DELIMITER ;

生成200万的测试数据

CALL usp_batch_insert(2000000, 5000);

把一部分数据重复

INSERT INTO t3 (f1,f2,ctime) 
SELECT f1, f2, NOW(3) FROM t3 LIMIT 123456,10000;

INSERT INTO t3 (f1,f2,ctime) 
SELECT f1, f2, NOW(3) FROM t3 LIMIT 15234567,254321;

INSERT INTO t3 (f1,f2,ctime) 
SELECT f1, f2, NOW(3) FROM t3 LIMIT 345678,654321;

INSERT INTO t3 (f1,f2,ctime) 
SELECT f1, f2, NOW(3) FROM t3 LIMIT 654321,45678;

INSERT INTO t3 (f1,f2,ctime) 
SELECT f1, f2, NOW(3) FROM t3 LIMIT 886,123456;

INSERT INTO t3 (f1,f2,ctime) 
SELECT f1, f2, NOW(3) FROM t3 LIMIT 15,2000;

数据已经构造完,全表280多万行记录,需要删除的数据有80多万。

下面就来测试下全过程的时间,总耗时是216秒,其中删除部分约29秒。

如果要保留最小id的那行记录,则把max()函数修改为min()函数。

CREATE OR REPLACE TABLE _tmp_t3 (id INT NOT NULL PRIMARY KEY);

INSERT INTO _tmp_t3 (id)
SELECT id
FROM t3 
WHERE id NOT IN (
        SELECT maxid FROM 
                (SELECT max(id) AS  maxid FROM t3
                        GROUP BY f1, f2             
                ) b
);

DELETE a FROM t3 as a INNER JOIN _tmp_t3 as b on b.id = a.id;

如果29秒可能会影响业务,可以做成存储过程,分批删除。

DELIMITER $$
DROP PROCEDURE IF EXISTS `usp_batch_delete`;

CREATE PROCEDURE `usp_batch_delete`(IN `batch_commit` int)
BEGIN
DECLARE i INT; 
DECLARE num_count INT;
SET i = 1; 
SELECT MAX(id) INTO num_count FROM _tmp_t3;
SET AUTOCOMMIT = 0;

WHILE i <= num_count DO

DELETE a FROM t3 as a INNER JOIN _tmp_t3 as b on b.id = a.id AND b.id = i;
SET i = i + 1;

IF MOD(1, batch_commit) >= 0 THEN
COMMIT;
END IF;

END WHILE;

SET AUTOCOMMIT = 1;
END; $$
DELIMITER ;

www.129028.com金沙,背景

表t_record中的数据fromUserId, toUserId两个字段组合作为唯一的标识,删除重复记录,只留下最大id(或最新时间)的记录。id为自增无重复的主键。

表t_record的id作为自增的主键。

表t_record大概有6万多的数据。以下测试均在资源很差的主机上,t_record没有在使用的情况下的结果。

方法1

查询重复的记录

SELECT fromUserId, toUserId, count(*)
FROM t_record as tr
GROUP BY fromUserId, toUserId
HAVING count(*) > 1;

把重复记录的两个字段放到临时表_tmp1中

CREATE TABLE _tmp1
SELECT fromUserId, toUserId
FROM t_record as tr
GROUP BY fromUserId, toUserId
HAVING count(*) > 1;

把应该删除的id查询出来,放到临时表_tmp2中

CREATE TABLE _tmp2
SELECT id
FROM t_record as a
WHERE (a.fromUserId, a.toUserId) in (

SELECT fromUserId, toUserId from _tmp1
)
and a.id not in (
SELECT MAX(id)
FROM t_record as tr
GROUP BY fromUserId, toUserId
HAVING count(*) > 1
);

删除原表的记录

DELETE from t_record
where id in (
    SELECT id from _tmp2
);

通过以前3个步骤,没有删除数据大概需要23秒左右。

本文由www.129028.com金沙发布于数据库,转载请注明出处:MariaDB删除重复记录性能测试

关键词:

探究SQL添加非聚集索引【www.129028.com金沙】,性能提高几十倍之

       原sql大概需要左联left join 十几个 ,leftjoin前后的两个表又是笛卡尔积。因此,只要其中有一个表的数据有很...

详细>>

MariaDB的存储过程和函数【www.129028.com金沙】

创建存储过程 DELIMITER $$DROP PROCEDURE IF EXISTS `sp_test1`;CREATE PROCEDURE sp_test1(IN a int, IN b int, OUT sum int )BEGIN DECLARE c int;if a ...

详细>>

表变量,临时表

--表变量update invent set goodInfo=nulldeclare @tmp_goods table( gno char(5))insert into @tmp_goods select gno from invent group by gnodeclare @gno ...

详细>>

如何根据日志的输出内容来触发其它操作

www.129028.com金沙,MySQL slave错误日志中报“[ERROR] Error reading packet from server:Lostconnection to MySQL server during query(server_errn...

详细>>