MySQL分页查询列表同时返回总数的三种方案及性能对比

saintyyu

发布于 2021-11-22 10:11:20

6.4K0

发布于 2021-11-22 10:11:20

文章被收录于专栏：IT专栏

背景

我们在使用Mybatis分页查询数据列表时，在用户的一个请求中常常需要同时返回当前页的列表数据以及满足条件的数据总条数。以下介绍了三种常见方案。具体使用哪种，具体场景具体分析。

实现方案

1）执行两次SQL，一次查列表，一次查总数

这种方法最简单，也最容易实现。缺点是需要执行两次SQL查询。

2）分页插件PageHelper

另一种常用的方式就是使用Mybatis提供的PageHelper插件。实际上PageHelper插件的原理同1）一样，就是执行两次SQL查询。

3）通过select ... found_rows()命令，可以只执行一次SQL查询。但这个功能要求connectionUrl参数包含allowMultiQueries=true，且对于如zebra等集成工具，就算配了allowMultiQueries=true，也不一定起作用。因而需要根据实际场景测试，再决定使用哪种方案。

xml配置文件示例代码如下：

<?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.domain.Order">

<select id="queryListAppendTotal"
        parameterType="com.domain.OrderExample"
        resultMap="BaseResultMap, recordCounts">
    select
    SQL_CALC_FOUND_ROWS
    <include refid="Base_Column_List"/>
    from order_example
    <if test="_parameter != null">
        <include refid="Example_Where_Clause"/>
    </if>
    <if test="orderByClause != null">
        order by ${orderByClause}
    </if>
    <if test="limit != null">
        <if test="offSet != null">
            limit ${offSet}, ${limit}
        </if>
        <if test="offSet == null">
            limit ${limit}
        </if>
    </if>
    ;SELECT found_rows() AS recordCounts;
</select>



<resultMap id="BaseResultMap" type="com.domain.OrderExample">
    <id column="id" jdbcType="BIGINT" property="id"/>
    <result column="order_id" jdbcType="VARCHAR" property="orderId"/>   
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
    <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
</resultMap>


<resultMap id="recordCounts" type="java.lang.Long">
  <result column="recordCounts" jdbcType="BIGINT"/>
</resultMap>
</mapper>

上述示例代码中入参使用了：parameterType="com.domain.OrderExample"，实际使用中可以根据需要设置具体的查询参数。另外，resultMap="BaseResultMap, recordCounts"这里的顺序不能换，总数recordCounts只能放在后面。

mapper代码示例：

package com.domain.OrderMapper;

public interface OrderMapper {
    //省略了无关代码

    List<Object> queryListAppendTotal(OrderExample example);

}

这里返回对象需要设置为List<Object>，否则调用方会报错。

调用OrderMapper方法的代码示例：

List<Object> orderInfoList = orderMapper.queryListAppendTotal(example);
if(CollectionUtils.isNotEmpty(oderInfoList) {
    List<Order> orderList = ((List<Order>)orderInfoList.get(0));
    Long total = ((List<Long>) orderInfoList.get(1)).get(0);
}

从上述调用示例代码可知，实际上，返回的List<Object>包含两个元素，第一个元素的实际类型为List<Order>，第二个元素的实际类型为List<Long>。调用方通过类型强转获取。

方案适用场景对比

上面三种方法实际上对应两种查询方式：一种是执行两次查询，一种是执行一次查询。那么这两种查询哪种性能更高呢？以下有几篇参考博客，建议先看一下，作为本文的参考。建议先看第1篇博客，再看第2篇，如果有关于explain命令结果不熟悉的，可以参考第3篇。

看完之后你可能会有些困惑，到底什么情况下哪种方法性能更高。我们具体来分析第1篇和第2篇博客，分析完了也就解惑了。我们把第1篇博客中的建表语句拷贝如下：

CREATE TABLE `count_test` (
  `a` int(10) NOT NULL auto_increment,
  `b` int(10) NOT NULL,
  `c` int(10) NOT NULL,
  `d` varchar(32) NOT NULL,
  PRIMARY KEY  (`a`),
  KEY `bc` (`b`,`c`)
) ENGINE=MyISAM

在第1篇博客中，执行一次查询的语句是：

SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM count_test WHERE b = 555 ORDER BY c LIMIT 5;

执行两次查询的语句是：

SELECT SQL_NO_CACHE * FROM count_test WHERE b = 666 ORDER BY c LIMIT 5;

SELECT SQL_NO_CACHE count(*) FROM count_test WHERE b = 666;

作者通过比较这两种情形下查询的耗时差异得出了结论：在WHERE/ORDER 子句中有合适索引的情况下，执行两次查询的效率比执行一次查询的效率高；在没有合适索引的情况下，执行一次查询的效率比两次查询的高。

第2篇博客给出了另外一种case，在那种case下，有合适的索引，但执行一次查询的效率比执行两次的效率更高，即第1篇博客的结论不成立。但第2篇博客只是给出了反例，并未对此做理论分析，本文就是为了弥补这一不足。

第1篇博客的关键点是它们select的都是*，而*在第一篇博客中代表a, b, c, d四个字段，且d字段不属于任何索引。这是导致两种查询耗时差异巨大的根本原因。因为d不属于任何索引，所以这两种查询方式都需要回表（如果你不知道啥叫回表，请参见第5篇博客）。而执行两次查询时，由于limit的限制，每次回表的数据行数最多5行（select count不会回表）；相反，执行一次查询时，因为要统计总数，所以需要回表的行数为所有满足条件的行。显然，这种情况下执行一次查询需要回表的行数远远大于执行两次查询。因而在这种情形下，执行两次查询的效率更高。在第2篇博客中，通过对select的字段做限制，从而得到了不同的结果。我这里给出一个更全面的示例，我们将查询语句换成以下情形：

SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS a, b, c FROM count_test WHERE b = 555 ORDER BY c LIMIT 5;

两相比较，本质上我们就是将查询字段d去掉了。因为d不在索引中，而且去掉了这个字段之后，剩下的字段就都在索引中了，因而查询不需要回表（你可能会有疑议，因为博客1中a，b，c三个字段对应了两个索引，而不是一个联合索引，为什么不需要回表呢？这个问题需要参考第4篇博客中介绍的辅助索引扩展）。在这种情形下，执行一次查询的效率高于执行两次查询。

到此，在查询条件有limit限制的前提下，我们可以得出以下结论：

1. 在查询不需要回表（索引包含了需要查询的所有字段）时，执行一次查询的性能略高（取决于数据量）于执行两次查询；

2. 在查询需要回表（索引只包含部分查询字段）时，执行两次查询的性能远高（取决于数据量）于执行一次查询；

3. 在全表扫描（数据表无索引或索引不包含查询字段）时，执行一次查询的性能远高（取决于数据量）于执行两次查询。

当然，在大多数情况下，我们都会为数据表建索引，因而上述第3条不太可能出现；而对于第2条，我们常常需要将表中所有字段返回，而大多数情况下，我们肯定不会将所有字段都放在一个索引中，因而大多数情况下，执行两次查询的性能比执行一次查询的性功能要好。

所以，最终结论还是开头那句话，具体使用哪种方式，需要具体场景具体分析。