mysql数据foreach操作和批量操作的效率比较

原创
2016/06/16 09:30
阅读数 3.6K

一直在考虑一个问题,就是数据库插入的时候在java业务层是用for循环单条插入效率高还是直接一个List数据插入效率高,还是效率是一样的?

为此做了一个demo

两个测试请求

    @RequestMapping(value = "/test/addForeach")
    @ResponseBody
    public String addForEach() {
        Student student = new Student();
        student.setAge(10);
        student.setName("#zhangsan#");
        student.setSex(1);
        long s = System.currentTimeMillis();
        for(int i=0; i<1000; i++) {
            studentService.addStudent(student);
        }
        long e = System.currentTimeMillis();
        System.out.println("Foreach Insert Takes: " + (e-s));
        return "ok";
    }

    @RequestMapping(value = "/test/addList")
    @ResponseBody
    public String addList() {
        Student student = new Student();
        student.setAge(10);
        student.setName("#zhangsan#");
        student.setSex(1);
        List<Student> studentList = new ArrayList<Student>();
        long s = System.currentTimeMillis();
        for(int i=0; i<1000; i++) {
            studentList.add(student);
        }
        studentService.addStudentList(studentList);
        long e = System.currentTimeMillis();
        System.out.println("List Insert Takes: " + (e-s));
        return "ok";
    }

service层代码就简单的调用了mapper封装的接口

/**
 * Created by   mjchow
 * Date         2016/2/21
 * Time         15:53
 */
@Service
public class StudentServiceImpl implements StudentService {

    @Resource
    private StudentMapper studentMapper;

    @Override
    public void addStudent(Student s) {
        studentMapper.insert(s);
    }

    @Override
    public void addStudentList(List<Student> studentList) {
        studentMapper.insertList(studentList);
    }
}

mapper.xml文件

  <insert id="insert" parameterType="mjchow.com.github.entity.Student" >
    insert into student (id, name, age, sex)
    values (#{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER}, 
      #{sex,jdbcType=INTEGER})
  </insert>

  <insert id="insertList" parameterType="java.util.List">
      INSERT INTO student(id, name, age, sex)
      VALUES
      <foreach collection="list" item="item" index="index" separator=",">
          (#{item.id}, #{item.name}, #{item.age}, #{item.sex})
      </foreach>
  </insert>

下面来看看分别插入1000天数据所花费的时间

以上可以看出循环单条插入的效率比较低

 

但是如果我把插入的放到业务逻辑代码放到Service层中,两种的运行的时间都是毫秒级别的,不清楚这是为什么?

    @Override
    public void addStudentList(List<Student> studentList) {
        long s = System.currentTimeMillis();
        for(Student student: studentList) {
            ;
        }
        studentMapper.insertList(studentList);
        long e = System.currentTimeMillis();
        System.out.println("Service List Insert Takes: " + (e-s));
    }

    @Override
    public void addStudentListForeach(List<Student> studentList) {
        long s = System.currentTimeMillis();
        for(Student student: studentList) {
            studentMapper.insert(student);
        }
        long e = System.currentTimeMillis();
        System.out.println("Service Foreach Insert Takes: " + (e-s));
    }

这里可以看出相差不了多少,但是List的插入比foreach插入效率稍微好一点

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部