MariaDB删除重复记录性能测试

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

环境

测试表的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 ;