【MyBatis】【SQL】删除最快纪录诞生,从一千万条记录中删除八百万条仅用2分6秒
2024-09-01 17:07:24
在 https://www.cnblogs.com/xiandedanteng/p/11669629.html 里我做个一个循环按时间查ID并删除之的程序,运行时间是4分7秒。
但是这个程序走了很多次没有必要的IO,也就是把不需要的指比如要删除的ID在Java所在的机器和DB服务器中传来传去,这个过程在没必要的耗时,不如把日期扔给DB告诉它删除之前的记录然后返回修改的记录数就行了,于是我书写了以下SQL语句:
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace="com.hy.mapper.EmpMapper"> <select id="selectById" resultType="com.hy.entity.Employee"> select id,name,age,cdate as ctime from emp where id=#{id} </select> <insert id="batchInsert"> insert into emp(name,age,cdate) values <foreach collection="list" item="emp" separator=","> (#{emp.name},#{emp.age},#{emp.ctime,jdbcType=TIMESTAMP}) </foreach> </insert> <insert id="singleInsert"> insert into emp(name,age,cdate) values (#{name},#{age},#{ctime,jdbcType=TIMESTAMP}) </insert> <select id="selectIdsByDate" resultType="java.lang.Long"> select id from emp where cdate<#{date,jdbcType=TIMESTAMP} limit 10000 </select> <delete id="deleteByIds"> delete from emp where id in <foreach collection="list" open="(" close=")" separator="," item="id" index="i"> #{id} </foreach> </delete> <delete id="deleteByDate"> delete from emp where id in (select id from (select id from emp where cdate<#{date,jdbcType=TIMESTAMP}) as tb) </delete> </mapper>
对应的接口是这样的:
package com.hy.mapper; import java.util.List; import org.apache.ibatis.annotations.Param; import com.hy.entity.Employee; public interface EmpMapper { Employee selectById(long id); int batchInsert(List<Employee> emps); // 用@Param标签指明和SQL的参数对应能避免出现org.apache.ibatis.binding.BindingException异常 int singleInsert(@Param("name")String name,@Param("age")int age,@Param("ctime")String ctime); List<Long> selectIdsByDate(String date); int deleteByIds(List<Long> ids); int deleteByDate(String date); }
Java程序则简单多了:
package com.hy.action; import java.io.Reader; import java.util.ArrayList; import java.util.List; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.apache.log4j.Logger; import com.hy.entity.Employee; import com.hy.mapper.EmpMapper; public class BatchDelete2 { private static Logger logger = Logger.getLogger(SelectById.class); public static void main(String[] args) throws Exception{ long startTime = System.currentTimeMillis(); Reader reader=Resources.getResourceAsReader("mybatis-config.xml"); SqlSessionFactory ssf=new SqlSessionFactoryBuilder().build(reader); reader.close(); SqlSession session=ssf.openSession(); try { EmpMapper mapper=session.getMapper(EmpMapper.class); int changed=mapper.deleteByDate("2018-02-02"); session.commit(); System.out.println("All deleted="+changed); }catch(Exception ex) { logger.error(ex); session.rollback(); }finally { session.close(); long endTime = System.currentTimeMillis(); logger.info("Time elapsed:" + toDhmsStyle((endTime - startTime)/1000) + "."); } } // format seconds to day hour minute seconds style // Example 5000s will be formatted to 1h23m20s private static String toDhmsStyle(long allSeconds) { String DateTimes = null; long days = allSeconds / (60 * 60 * 24); long hours = (allSeconds % (60 * 60 * 24)) / (60 * 60); long minutes = (allSeconds % (60 * 60)) / 60; long seconds = allSeconds % 60; if (days > 0) { DateTimes = days + "d" + hours + "h" + minutes + "m" + seconds + "s"; } else if (hours > 0) { DateTimes = hours + "h" + minutes + "m" + seconds + "s"; } else if (minutes > 0) { DateTimes = minutes + "m" + seconds + "s"; } else { DateTimes = seconds + "s"; } return DateTimes; } }
控制台输出也很有喜感:
All deleted=8035199 INFO [main] - Time elapsed:2m6s.
数据库里也达到了想要的结果:
时间减半,相对于循环删除的方案https://www.cnblogs.com/xiandedanteng/p/11669629.html ,优势是显而易见的。
本作代码下载地址:https://files.cnblogs.com/files/xiandedanteng/InsertMillionComparison2019101014.rar
--END-- 2019年10月14日10:44:47
最新文章
- HTTP 的重定向301,302,303,307(转)
- 创建并追加img元素(jquery!)
- 二 Java利用等待/通知机制实现一个线程池
- Mysql-ERROR 1044 (42000): Access denied for user &#39;&#39;@&#39;localhost&#39; to database &#39;mysql&#39;
- A*算法深入
- Linux普通文件和设备的异同点
- JavaFX基础学习之URLConnection
- 如何处理导出的csv无法查看身份证后三位的情况?
- 数据库设计很棒的参考CDM-PDM-LDM-PowerDesigner
- python 多线程 ping
- Hadoop HDFS, YARN ,MAPREDUCE,MAPREDUCE ON YARN
- 一个WPF只能输入数字的行为。
- canvas 绘画随机点
- Django跨域(前端跨域)
- sql语句查询结果排序
- vue.js如何实现点击按钮动态添加li
- avalonjs 实现简单购物车
- C# 裁剪图片
- 20155222卢梓杰 课堂测试ch06补做
- HDU 1573 CRT
热门文章
- 数据分析基础之pandas &; numpy
- .NET webapi 的单元测试
- 6.AOP配置与应用(xml的方式)
- Image Processing and Analysis_8_Edge Detection:A Computational Approach to Edge Detection——1986
- 【异常】Cannot run program ";git"; (in directory ";/mnt/software/azkaban-3.79.0";): error=2, No such file or directory
- PXC集群信息查询
- Python Flask学习笔记(1)
- PHP 提取数组中奇数或偶数的元素array_filter
- Linux系统管理常用命令
- 关于STM32的I2C硬件DMA实现