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

背景

我们在使用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 test="orderByClause != null">
        order by ${orderByClause}
    <if test="limit != null">
        <if test="offSet != null">
            limit ${offSet}, ${limit}
        <if test="offSet == null">
            limit ${limit}
    ;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,