MyBatis+Spring SQL效率测试报告

1. 数据库结构

 

2. insert 测试

insert 的测试包括

1) 批量拼接values()插入

2) 有事务for循环插入

3) 无事务for循环插入

测试 SQL:

 <!-- 普通 insert -->
    <insert id="insert"
            parameterType="com.qunar.mybatistask.bean.Post"
            keyProperty="id">
        <![CDATA[

            insert into post (
                title,
                content,
                author,
                status,
                created
            ) values (
                #{title},
                #{content},
                #{author},
                #{status},
                #{created}
            )

        ]]>
    </insert>

    <!-- 拼接values insert -->
    <insert id="batchInsert"
            parameterType="java.util.List">
        <![CDATA[
            insert into post (
                title,
                content,
                author,
                status,
                created
            ) values
         ]]>
        <foreach collection="list" item="post" separator=",">
            (
            #{post.title},
            #{post.content},
            #{post.author},
            #{post.status},
            #{post.created}
            )
        </foreach>
    </insert>

测试代码:

service

    /**
     * 批量拼接VALUES() insert
     *
     * @param postList
     * @return
     */
    @Override
    @Transactional(propagation = Propagation.REQUIRED)
    public int batchInsert(List<Post> postList) {
        int singleNum = 1000;
        int affectedRows = 0;
        for (int i = 0; i < Math.ceil((double)(postList.size() / singleNum)); i++) {
            affectedRows += sqlSession.insert("post.batchInsert", postList.subList(i * singleNum, (i + 1) * singleNum));
        }
        return affectedRows;
    }

    /**
     * 事务内循环insert
     *
     * @param postList
     * @return
     */
    @Override
    @Transactional(propagation = Propagation.REQUIRED)
    public int createList(List<Post> postList) {
        int affectedRows = 0;
        for (Post post : postList) {
            affectedRows += sqlSession.insert("post.insert", post);
        }
        return affectedRows;
    }

test case:

    /**
     * 批量插入效率测试
     *
     * Method: batchInsert(List<Post> postList)
     * 
     */
    @Test
    public void testBatchInsert() throws Exception {
        List<Post> postList = Lists.newArrayList();
        for (int i = 0; i < 10000; i++) {
            Post post = new Post();
            post.setAuthor("test");
            post.setContent("test");
            post.setCreated(new Date());
            post.setTitle("test");
            post.setStatus(PostStatus.NORMAL);
            postList.add(post);
        }

        // 批量拼接SQL插入
        long start = System.nanoTime();
        int affectedRows = postService.batchInsert(postList);
        double duration = System.nanoTime() - start;
        System.out.format("batch: %.2f\n", duration / 1.0e9);
        System.out.println("affected rows: " + affectedRows);

        // 事务内循环插入
        start = System.nanoTime();
        affectedRows = postService.createList(postList);
        duration = System.nanoTime() - start;
        System.out.format("transaction: %.2f\n", duration / 1.0e9);
        System.out.println("affected rows: " + affectedRows);

        // 无事务直接循环插入
        start = System.nanoTime();
        affectedRows = 0;
        for (Post post : postList)
            affectedRows += postService.create(post);
        duration = System.nanoTime() - start;
        System.out.format("simple: %.2f\n", duration / 1.0e9);
        System.out.println("affected rows: " + affectedRows);
    }

结果 

batch: 1.44
affected rows: 10000
transaction: 2.87
affected rows: 10000
simple: 77.57
affected rows: 10000

 

总结:

排行

1) 使用拼接的手段,这种插入其实就是batch,只不过这是手动batch

2) 使用事务循环插入,相对于无事务快很多的原因大概是数据库连接和事务开启的次数

3) 无事务循环插入, 我想应该没人这么写

 

2. 单表循环查询与拼接in查询测试

SQL

<select id="selectById" parameterType="int" resultType="com.qunar.mybatistask.bean.Post">
         <![CDATA[

            select
                id,
                title,
                content,
                author,
                status,
                created
            from
                post
            where
                id = #{id}

        ]]>
    </select>

    <!-- 拼接where in条件查询 -->
    <select id="selectIn" parameterType="java.util.List" resultType="com.qunar.mybatistask.bean.Post">
        <![CDATA[
            select
                id,
                title,
                content,
                author,
                status,
                created
            from
                post
            where
                id in
        ]]>
        <foreach collection="list" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </select>

Service

    @Override
    public Post selectById(int id) {
        return sqlSession.selectOne("post.selectById", id);
    }

    @Override
    public List<Post> selectByIds(List<Integer> ids) {
        List<Post> postList = Lists.newArrayList();
        int singleNum = 1000;
        int start;
        int end;
        for (int i = 0; i < Math.ceil(((double)ids.size() / (double)singleNum)); i++) {
            start = i * singleNum;
            end = (i + 1) * singleNum;
            end = end > ids.size() ? ids.size() : end;
            List<Post> result = sqlSession.selectList("post.selectIn", ids.subList(start, end));
            postList.addAll(result);
        }
        return postList;
    }

test case

    /**
     * 使用IN查询效率测试
     *
     * @throws Exception
     */
    @Test
    public void testInSelect() throws Exception {
        List<Integer> ids = Lists.newArrayList();
        for (int i = 1; i < 10000; i++) {
            ids.add(i);
        }

        // in 查询
        long start = System.nanoTime();
        List<Post> list = postService.selectByIds(ids);
        double duration = System.nanoTime() - start;
        System.out.format("in select: %.2f\n", duration / 1.0e9);
        System.out.println("list size: " + list.size());

        // 循环查询
        list = Lists.newArrayList();
        start = System.nanoTime();
        for (int id : ids)
            list.add(postService.selectById(id));
        duration = System.nanoTime() - start;
        System.out.format("simple select: %.2f\n", duration / 1.0e9);
        System.out.println("list size: " + list.size());
    }

结果

in select: 0.55
list size: 9999
simple select: 6.24
list size: 9999

 

总结:

我想应该没人会用for循环去做查询吧

 

3. 多表联结查询, join, form 2个table, in, exists 比较

SQL

    <!-- 用于循环查询 -->
    <select id="selectAll" resultType="com.qunar.mybatistask.bean.Comment">
        <![CDATA[
          select
            cmt.id as id,
            cmt.post_id as postId,
            cmt.content as content
          from
            cmt
        ]]>
    </select>

    <!-- join 查询 -->
    <select id="selectJoin" resultType="com.qunar.mybatistask.bean.Comment">
        <![CDATA[
          select
            cmt.id as id,
            cmt.post_id as postId,
            cmt.content as content
          from
            cmt
          join
            post
          on
            post.id = cmt.post_id
        ]]>
    </select>

    <!-- from 2个table -->
    <select id="selectTowTable" resultType="com.qunar.mybatistask.bean.Comment">
        <![CDATA[
          select
            cmt.id as id,
            cmt.post_id as postId,
            cmt.content as content
          from
            cmt, post
          where cmt.post_id = post.id
        ]]>
    </select>

    <!-- in 联表查询 -->
    <select id="selectIn" resultType="com.qunar.mybatistask.bean.Comment">
      <![CDATA[
          select
            cmt.id as id,
            cmt.post_id as postId,
            cmt.content as content
          from
            cmt
          where
            cmt.post_id
          in
            (
              select
                post.id
              from
                post
            )
      ]]>
    </select>

    <!-- exists 联表查询 -->
    <select id="selectExists" resultType="com.qunar.mybatistask.bean.Comment">
      <![CDATA[
          select
            cmt.id as id,
            cmt.post_id as postId,
            cmt.content as content
          from
            cmt
          where
            exists
            (
              select
                post.id
              from
                post
              where
                post.id = cmt.id
            )
      ]]>
    </select>

service

    @Override
    public List<Comment> selectTwoTable() {
        return sqlSession.selectList("comment.selectTowTable");
    }

    @Override
    public List<Comment> selectJoin() {
        return sqlSession.selectList("comment.selectJoin");
    }

    @Override
    public List<Comment> selectIn() {
       return sqlSession.selectList("comment.selectIn");
    }

    @Override
    public List<Comment> selectExists() {
        return sqlSession.selectList("comment.selectExists");
    }

    @Override
    public List<Comment> selectAll() {
        return sqlSession.selectList("comment.selectAll");
    }

test case

    /**
     * 测试JOIN查询效率
     *
     */
    @Test
    public void testJoinSelect() {
        // join 查询
        long start = System.nanoTime();
        List<Comment> list = commentService.selectJoin();
        double duration = System.nanoTime() - start;
        System.out.format("join select: %.2f\n", duration / 1.0e9);
        System.out.println("list size: " + list.size());

        // From 两个表查询
        start = System.nanoTime();
        list = commentService.selectTwoTable();
        duration = System.nanoTime() - start;
        System.out.format("2 table select: %.2f\n", duration / 1.0e9);
        System.out.println("list size: " + list.size());

        // in多表查询
        start = System.nanoTime();
        list = commentService.selectIn();
        duration = System.nanoTime() - start;
        System.out.format("in multi table select: %.2f\n", duration / 1.0e9);
        System.out.println("list size: " + list.size());

        // exists多表查询
        start = System.nanoTime();
        list = commentService.selectExists();
        duration = System.nanoTime() - start;
        System.out.format("exists multi table select: %.2f\n", duration / 1.0e9);
        System.out.println("list size: " + list.size());

        // 分次查询, 太慢了, 忽略这种方法的测试吧
//        start = System.nanoTime();
//        list = commentService.selectAll();
//        for (Comment comment : list) {
//            postService.selectById(comment.getPostId());
//        }
//        duration = System.nanoTime() - start;
//        System.out.format("separate select: %.2f\n", duration / 1.0e9);
//        System.out.println("list size: " + list.size());
    }

结果

join select: 2.44
list size: 210000
2 table select: 2.26
list size: 210000
in multi table select: 2.03
list size: 210000
exists multi table select: 2.35
list size: 210000

总结:

21W条数据下效率都差不多,而且我们一般会使用limit去限制查询的条数,所以应该他们的效率差距应该很小,我通过观察explain发现实际上join和from 2个table的方式的查询的执行计划是一模一样的,而in和exists的执行计划也是一模一样的

这里的表结构相对简单,也基本能用上索引 post_id 和 post.id 这些primary, 具体更加复杂的情况也许会影响这几种查询方式的执行计划, 才会体现出他们直接的差距, 当然我也相信他们执行的效率很大程度上是决定于mysql的优化器的优化策略,而这个优化策略很难人为的去判断,所以也不好说