原创

MySQL删除线上业务过期数据两种方案测试数据

工作中你是否遇到过线上业务由于设计不当导致某些数据表数据爆炸?千万级表的删除怎样才能平稳快速搞定呢?本文提供两种思路亲自演示从建表到删除的方案。

环境准备

1.新建 1000w 数据的表两个

CREATE TABLE `test_delete` (
  `id` int(11) NOT NULL,
  `sex` varchar(1) DEFAULT NULL COMMENT '性别',
  `name` varchar(20) DEFAULT NULL COMMENT '名字',
  `company` varchar(20) DEFAULT NULL COMMENT '公司',
  `department` varchar(20) DEFAULT NULL COMMENT '部门',
  `position` varchar(20) DEFAULT NULL COMMENT '职位',
  `lastUpdateTime` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_time` (`lastUpdateTime`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `test_delete2` (
  `id` int(11) NOT NULL,
  `sex` varchar(1) DEFAULT NULL COMMENT '性别',
  `name` varchar(20) DEFAULT NULL COMMENT '名字',
  `company` varchar(20) DEFAULT NULL COMMENT '公司',
  `department` varchar(20) DEFAULT NULL COMMENT '部门',
  `position` varchar(20) DEFAULT NULL COMMENT '职位',
  `lastUpdateTime` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_time` (`lastUpdateTime`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2.插入测试数据

package cn.gameboys.mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

/**
 * Description:
 *
 * @author gameboys(www.gameboys.cn)
 * @date 2019年11月28日
 */
public class DeleteTest {

	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		final String url = "jdbc:mysql://192.168.6.66/test3?useUnicode=true&characterEncoding=utf8";
		final String name = "com.mysql.jdbc.Driver";
		final String user = "gameboys";
		final String password = "gameboys1234";
		Connection conn = null;
		Class.forName(name); // 指定连接类型
		conn = DriverManager.getConnection(url, user, password); // 获取连接
		if (conn != null) {
			System.out.println("获取连接成功");
			insert(conn);
		} else {
			System.out.println("获取连接失败");
		}
	}

	public static void insert(Connection conn) {
		// 一共插入数据
		int totalCount = 10000000;
		// 每次sql插入数据
		int perTimeCount = 100000;
		// 开始时间
		Long begin = new Date().getTime();
		//String prefix = "INSERT INTO test_delete (id,sex,name,company,department,position,lastUpdateTime) VALUES ";
		String prefix = "INSERT INTO test_delete2 (id,sex,name,company,department,position,lastUpdateTime) VALUES ";
		try {
			// 保存sql后缀
			StringBuffer suffix = new StringBuffer();
			// 设置事务为非自动提交
			conn.setAutoCommit(false);
			// 比起st,pst会更好些
			PreparedStatement pst = (PreparedStatement) conn.prepareStatement(""); // 准备执行语句
			// 外层循环,总提交事务次数
			for (int i = 1; i <= totalCount; i++) {
				//suffix = new StringBuffer();
				// 第j次提交步长
				// 构建SQL后缀
				suffix.append("('" + i + "','1'" + ",'我是名字" + i + "'" + ",'np公司名'" + ",'np部门'" + ",'np职位',"+i+"),");
				if (i % perTimeCount == 0) {
					// 构建完整SQL
					String sql = prefix + suffix.substring(0, suffix.length() - 1);
					// 添加执行SQL
					pst.addBatch(sql);
					// 执行操作
					pst.executeBatch();
					// 提交事务
					conn.commit();
					// 清空上一次添加的数据
					suffix = new StringBuffer();
				}
			}
			// 头等连接
			pst.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// 结束时间
		Long end = new Date().getTime();
		// 耗时
		System.out.println(totalCount + "条数据插入花费时间 : " + (end - begin) / 1000 + " s");
		System.out.println("插入完成");
	}
}

  • 复制 JAVA 代码
  • 修改 url,name,user,password 成为自己的
  • 运行代码
  • 耐心等待 200s 左右数据生成完毕
  • 通过 SQL 语句来查看插入结果-->Select count(*) from test_delete;

3.两种方式来删除

数据准备好了,笔者亲自测试了两种方案,两种方案各有千秋

3.1.通过中间表来删除

三步 sql 搞定:创建中间表->将需要的数据插入新表->重命名表,具体操作如下

[SQL] --  创建中间库
CREATE TABLE `test_delete_tmp` (
  `id` int(11) NOT NULL,
  `sex` varchar(1) DEFAULT NULL COMMENT '性别',
  `name` varchar(20) DEFAULT NULL COMMENT '名字',
  `company` varchar(20) DEFAULT NULL COMMENT '公司',
  `department` varchar(20) DEFAULT NULL COMMENT '部门',
  `position` varchar(20) DEFAULT NULL COMMENT '职位',
  `lastUpdateTime` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_time` (`lastUpdateTime`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
受影响的行: 0
时间: 0.056ms
[SQL]
--  将需要的数据插入新表
insert into test_delete_tmp(id, sex, name, company, department, position,lastUpdateTime) select id, sex, name, company, department, position,lastUpdateTime from test_delete where lastUpdateTime>9000000;
受影响的行: 1000000
时间: 9.858ms
[SQL]
--  将需要的数据插入新表
insert into test_delete_tmp(id, sex, name, company, department, position,lastUpdateTime) select id, sex, name, company, department, position,lastUpdateTime from test_delete where lastUpdateTime>9500000;
受影响的行: 500000
时间: 4.979ms
[SQL]
--  将需要的数据插入新表
insert into test_delete_tmp(id, sex, name, company, department, position,lastUpdateTime) select id, sex, name, company, department, position,lastUpdateTime from test_delete where lastUpdateTime>9900000;
受影响的行: 100000
时间: 0.892ms
[SQL]
--  重命名表
RENAME TABLE test_delete TO test_delete_old, test_delete_tmp TO test_delete;
受影响的行: 0
时间: 0.063ms

结论: 这个方案操作简单、速度快,1000w 数据的表,正常数据在 10w 左右插入耗时不到 1s,50w 插入耗时 5s,100w 数据在 10s 左右,该方案的限制是自己业务能承受的阻塞时间,如果无法接受长时间的阻塞,可以使用下面的方案。

3.2.通过查询 id 慢慢删除

实现方式有多种,核心流程不变如下

  • 从备库拉取过期的主键 ID(主键删除非常快)
  • 将导出的主键封装成 sql 语句
  • 通过 sql 删除脏数据(这里注意服务器性能选择执行一条 sql 休息 1s)

为了少依赖,易修改,最后笔者选择了 shell 脚本来操作,具体脚本如下,

#!/bin/sh
ShellPath=$(cd "$(dirname "$0")"; pwd)

idInfo=${ShellPath}/idInfo
resultInfo=${ShellPath}/resultInfo

#拉取过期的ID
mysql -uroot -proot1234 -e "use test3;select id from test_delete where lastUpdateTime < 5000000" >${idInfo}

#将ID每1000个组成一个sql
cat ${idInfo} |awk '{a = sprintf("%d", NR / 100); if($1!=id){b[a]=b[a]","$1}} END {OFS="|";for(i in b){ c=length(b[i]);print i,substr(b[i],2,c)}}' |sort -k1 -n |awk -F\| '{print "delete from test_delete2 where id in("$2");"}' >${resultInfo}

#遍历sql文件,每间隔1s执行一次sql
cat ${resultInfo}|while read line
do
	echo ${line}
	sleep 1
	`$(mysql -uroot -proot1234 test3 -s -e "${line}")`
done

脚本分为三步对应流程里面的三步,都可独立拉出来使用。

结论: 这种方式删除数据较慢,通用,稳定,适合删除超级大表,甚至可以达到 2E 数据的表,笔者就使用这种方式从一个 1.7E 数据的表中删除过期的数据,对业务毫无影响,只是较为费时。

总结:

两种方案各有千秋,如果数据量少,对数据库卡顿几秒无影响,使用第一种,快速,如果数据量很大,第二种更稳。

正文到此结束