Skip to content

Latest commit

 

History

History
919 lines (763 loc) · 42.3 KB

27.mysql_pagination_query_comparison.md

File metadata and controls

919 lines (763 loc) · 42.3 KB

mysql 分页查询性能对比与优化

​
​

1 摘要

在前边介绍了使用 Mybatis PageHelper 分页插件进行分页，但是在实际测试过程中效果并不是很好，主要是查询时间比较长，即性能较差。于是作者将自己的分页方案与 Mybatis PageHelper 分页插件进行了对比测试。以下为测试结果。

​

2 数据准备

2.1 数据模型

/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:     2019/11/27 14:32:00                          */
/*==============================================================*/


drop table if exists article;

drop table if exists article_tag;

drop table if exists article_to_tag;

/*==============================================================*/
/* Table: article                                               */
/*==============================================================*/
create table article
(
   id                   bigint unsigned not null auto_increment comment '文章 id,主键',
   title                varchar(100) not null default '' comment '文章标题',
   content              varchar(5000) not null default '' comment '文章内容',
   primary key (id)
)
ENGINE = InnoDB
CHARSET = UTF8MB4;

alter table article comment '文章表';

/*==============================================================*/
/* Table: article_tag                                           */
/*==============================================================*/
create table article_tag
(
   id                   bigint unsigned not null auto_increment comment '文章标签 id,主键',
   tag_name             varchar(20) not null default '' comment '标签名称',
   primary key (id)
)
ENGINE = InnoDB
CHARSET = UTF8MB4;

alter table article_tag comment '文章标签表';

/*==============================================================*/
/* Table: article_to_tag                                        */
/*==============================================================*/
create table article_to_tag
(
   id                   bigint unsigned not null auto_increment comment '文章-标签关联表 id,主键',
   article_id           bigint unsigned not null default 0 comment '文章 id',
   tag_id               bigint unsigned not null default 0 comment '标签 id',
   primary key (id)
)
ENGINE = InnoDB
CHARSET = UTF8MB4;

alter table article_to_tag comment '文章-标签关联表';

参考:Boot 2.X 集成 Mybatis PageHelper 分页插件完美解决一对多分页查询问题 --- 2019-11-27

中的数据库模型

索引(除主键以外):

-- 文章-文章标签添加索引(2019-11-26)
ALTER TABLE `article_to_tag` ADD INDEX article_id_tag_id_article_to_tag(article_id,tag_id);

文章与标签的关联表中添加的关联文章、标签的联合索引(如果不添加索引，查询时间过长，容易导致数据库卡死)

2.2 数据量

一百万条

2.3 数据生成

JDBC 链接工具类

DataSourceUtil.java

package com.ljq.demo.util;

import java.sql.*;

/**
 * @Description: 数据库连接工具
 * @Author: junqiang.lu
 * @Date: 2019/11/26
 */
public class DataSourceUtil {

    /**
     * 数据库驱动类
     */
    private static final String DRIVER_CLASS = "com.mysql.cj.jdbc.Driver";
    /**
     * 数据库连接地址
     */
    private static final String DB_URL = "jdbc:mysql://127.0.0.1:13306/demo?useUnicode=true&" +
            "characterEncoding=utf8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&" +
            "serverTimezone=GMT%2B8&useSSL=true&allowMultiQueries=true&autoReconnect=true";
    /**
     * 数据库连接用户名
     */
    private static final String DB_USER_NAME = "root";
    /**
     * 数据库连接密码
     */
    private static final String DB_PASSWORD = "root";
    /**
     * 数据库连接
     */
    private static volatile Connection connection;

    private DataSourceUtil(){
    }


    /**
     * 插入(可批量)
     *
     * @param sql
     * @return
     * @throws SQLException
     */
    public static int insert(String sql) throws SQLException, ClassNotFoundException {
        init();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        return preparedStatement.executeUpdate();
    }

    /**
     * 初始化数据库连接
     *
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    private static Connection init() throws ClassNotFoundException, SQLException {
        if (connection == null) {
            synchronized (DataSourceUtil.class) {
                if (connection == null) {
                    Class.forName(DRIVER_CLASS);
                    connection = DriverManager.getConnection(DB_URL, DB_USER_NAME, DB_PASSWORD);
                }
            }
        }
        return connection;
    }

}

测试数据生成

DataSourceUtilTest.java

package com.ljq.demo.util;

import org.apache.commons.lang3.RandomUtils;
import org.junit.Test;

import java.sql.SQLException;

public class DataSourceUtilTest {

    /**
     * 测试批量插入大量文章
     *
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    @Test
    public void insertArticle() throws SQLException, ClassNotFoundException {
        /**
         * 计划插入数据条数为 1 000 010 条
         * 实际插入条数为 1 000 000 条
         * 这一程序主要用于快速插入巨量数据,至于没有精确计算零头,
         * 主要是为了方便理解程序
         */

        // 需要插入数据的总条数
        int countTotal = 1000010;
        // 每次插入数据的条数
        int countLimit = 5000;
        // 插入数据的次数
        int countPage = countTotal / countLimit;
        String title = "将进酒";
        String content = "君不见黄河之水天上来，奔流到海不复回。" +
                "君不见高堂明镜悲白发，朝如青丝暮成雪。" +
                "人生得意须尽欢，莫使金樽空对月。" +
                "天生我材必有用，千金散尽还复来。" +
                "烹羊宰牛且为乐，会须一饮三百杯。" +
                "岑夫子，丹丘生，将进酒，杯莫停。" +
                "与君歌一曲，请君为我侧耳听。" +
                "钟鼓馔玉不足贵，但愿长醉不复醒。" +
                "古来圣贤皆寂寞，惟有饮者留其名。" +
                "陈王昔时宴平乐，斗酒十千恣欢谑。" +
                "主人何为言少钱，径须沽取对君酌。" +
                "五花马，千金裘，" +
                "呼儿将出换美酒，与尔同销万古愁。";
        for (int j = 0; j < countPage; j++) {
            StringBuilder builder = new StringBuilder();
            builder.append("INSERT INTO `article`(`title`, `content`) VALUES");
            for (int i = 0; i < countLimit - 1; i++) {
                builder.append("('" + title + "','" + content + "'),");
            }
            builder.append("('" + title + "','" + content + "');");
            DataSourceUtil.insert(builder.toString());
            System.out.println("累计插入条数: " + countLimit * (j + 1));
        }
    }

    /**
     * 测试批量插入文章-标签关联信息
     */
    @Test
    public void insertArticleToTag() throws SQLException, ClassNotFoundException {
        /**
         * 计划插入数据条数为 1 155 013 条
         * 实际插入数据条数为 1 155 013 条
         * 该程序为精确插入数据条数,将零头也计算在内
         */

        // 数据总条数
        int countTotal = 1000008;
        // 每次插入条数
        int countLimit = 5000;
        // 数据插入次数
        int countPage = countTotal / countLimit;
        for (int i = 0; i < countPage; i++) {
            StringBuilder builder = new StringBuilder();
            builder.append("INSERT INTO `article_to_tag`(`article_id`, `tag_id`) VALUES");
            for (int j = 0; j < countLimit - 1; j++) {
                int articleId = countLimit * i + j + 1;
                builder.append("(" + articleId + "," + RandomUtils.nextInt(1,8) + "),");
            }
            builder.append("(" + countLimit * (i + 1) + "," + RandomUtils.nextInt(1,8) + ")");
            DataSourceUtil.insert(builder.toString());
            System.out.println("累计插入条数: " + countLimit * (i + 1));
        }
        int remainder = countTotal % countLimit;
        if (remainder < 1) {
            return;
        }
        StringBuilder builder = new StringBuilder();
        builder.append("INSERT INTO `article_to_tag`(`article_id`, `tag_id`) VALUES");
        for (int i = 0; i < remainder - 1; i++) {
            int articleId = countLimit * countPage + i + 1;
            builder.append("(" + articleId + "," + RandomUtils.nextInt(1, 8) + "),");
        }
        builder.append("(" + countTotal + "," + RandomUtils.nextInt(1, 8) + ");");
        DataSourceUtil.insert(builder.toString());
        System.out.println("累计插入条数: " + countTotal);

    }


    /**
     * 测试随机数
     */
    @Test
    public void randomTest(){
        /**
         * RandomUtils.nextInt(1,8) 方法
         * 输出的值是  1 <= X < 8
         * 注意: 不包括 8
         */

        int count = 50;
        for (int i = 0; i < count; i++) {
            System.out.println(RandomUtils.nextInt(1,8));
        }

    }
}

​

3 核心对比测试代码

3.1 Mybatis PageHelper 主表关联查询

java 类代码片段:

../demo-service/src/main/java/com/ljq/demo/springboot/service/impl/ArticleServiceImpl.java

	/**
	 * 查询列表
	 *
	 * @param articleListParam
	 * @return
	 * @throws Exception
	 */
	@Override
	public ApiResult list(ArticleListParam articleListParam) throws Exception {
		long start = System.currentTimeMillis();
		QueryUtil queryMap = new QueryUtil(BeanUtil.beanToMap(articleListParam, false, true));
		PageInfo<ArticleEntity> pageInfo = PageHelper.startPage(articleListParam.getCurrPage(), articleListParam.getPageLimit())
				.setOrderBy(articleListParam.getProperties() + " " + articleListParam.getDirection())
				.doSelectPageInfo(() -> articleDao.queryListPage(queryMap));
		long end = System.currentTimeMillis();
		log.info("查询耗时: {}", (end - start));
		return ApiResult.success(pageInfo);
	}

Mapper 文件代码片段:

../demo-dao/src/main/resources/mapper/ArticleDao.xml

	<!-- 文章表结果集resultMap -->
    <resultMap type="com.ljq.demo.springboot.entity.ArticleEntity" id="articleMap">
        <result property="id" column="id"/>
        <result property="title" column="title"/>
        <result property="content" column="content"/>
		<collection property="tagList" column="id" javaType="java.util.List" ofType="com.ljq.demo.springboot.entity.ArticleTagEntity"
					select="com.ljq.demo.springboot.dao.article.ArticleTagDao.queryTagsByArticleId">
			<id property="id" column="id" />
			<result property="tagName" column="tag_name" />
		</collection>
    </resultMap>

   <!-- 文章表-基础字段 -->
	<sql id="article_base_field">
        a.`id`,
        a.`title`,
        a.`content`
	</sql>

	<!-- 列表查询 -->
	<select id="queryListPage" parameterType="java.util.Map" resultMap="articleMap">
		SELECT
		<include refid="article_base_field" />
		FROM article a
		LEFT JOIN `article_to_tag` att ON att.article_id = a.id
		LEFT JOIN `article_tag` at ON at.id = att.tag_id
		WHERE 1 = 1
		<if test="title != null and '' != title">
			AND a.title LIKE CONCAT(CONCAT("%", #{title}), "%")
		</if>
		<if test="articleTag != null and '' != articleTag">
			AND at.tag_name LIKE CONCAT(CONCAT("%", #{articleTag}), "%")
		</if>
		GROUP BY a.id
	</select>

3.2 Mybatis PageHelper 主表不关联查询

java 类代码片段:

../demo-service/src/main/java/com/ljq/demo/springboot/service/impl/ArticleServiceImpl.java

	/**
	 * 查询列表-对比测试-2
	 *
	 * @param articleListParam
	 * @return
	 * @throws Exception
	 */
	@Override
	public ApiResult list2(ArticleListParam articleListParam) throws Exception {
		long start = System.currentTimeMillis();
		QueryUtil queryMap = new QueryUtil(BeanUtil.beanToMap(articleListParam, false, true));
		PageInfo<ArticleEntity> pageInfo = PageHelper.startPage(articleListParam.getCurrPage(), articleListParam.getPageLimit())
				.setOrderBy(articleListParam.getProperties() + " " + articleListParam.getDirection())
				.doSelectPageInfo(() -> articleDao.queryListPage2(queryMap));
		long end = System.currentTimeMillis();
		log.info("查询耗时: {}", (end - start));
		return ApiResult.success(pageInfo);
	}

Mapper 文件代码片段:

../demo-dao/src/main/resources/mapper/ArticleDao.xml

	<!-- 文章表结果集resultMap -->
    <resultMap type="com.ljq.demo.springboot.entity.ArticleEntity" id="articleMap">
        <result property="id" column="id"/>
        <result property="title" column="title"/>
        <result property="content" column="content"/>
		<collection property="tagList" column="id" javaType="java.util.List" ofType="com.ljq.demo.springboot.entity.ArticleTagEntity"
					select="com.ljq.demo.springboot.dao.article.ArticleTagDao.queryTagsByArticleId">
			<id property="id" column="id" />
			<result property="tagName" column="tag_name" />
		</collection>
    </resultMap>

   <!-- 文章表-基础字段 -->
	<sql id="article_base_field">
        a.`id`,
        a.`title`,
        a.`content`
	</sql>

	<!-- 列表查询-2 -->
	<select id="queryListPage2" parameterType="java.util.Map" resultMap="articleMap">
		SELECT
		<include refid="article_base_field" />
		FROM article a
		LEFT JOIN `article_to_tag` att ON att.article_id = a.id
		WHERE 1 = 1
		<if test="title != null and '' != title">
			AND a.title LIKE CONCAT(CONCAT("%", #{title}), "%")
		</if>
		GROUP BY a.id
	</select>

3.3 子查询主表关联查询

java 类代码片段:

../demo-service/src/main/java/com/ljq/demo/springboot/service/impl/ArticleServiceImpl.java

	/**
	 * 查询列表-对比测试-3
	 *
	 * @param articleListParam
	 * @return
	 * @throws Exception
	 */
	@Override
	public ApiResult list3(ArticleListParam articleListParam) throws Exception {
		long start = System.currentTimeMillis();
		QueryUtil queryMap = new QueryUtil(BeanUtil.beanToMap(articleListParam, false, true));
		int countTotal = articleDao.countComplex(queryMap);
		PageUtil pageUtil;
		if (countTotal > 0) {
			List<ArticleEntity> entityList = articleDao.queryListComplex(queryMap);
			pageUtil = new PageUtil(entityList,countTotal, queryMap.getPageLimit(), queryMap.getCurrPage());
		} else {
			pageUtil = new PageUtil(null, countTotal, queryMap.getPageLimit(), queryMap.getCurrPage());
		}
		long end = System.currentTimeMillis();
		log.info("查询耗时: {}", (end - start));
		return ApiResult.success(pageUtil);
	}

Mapper 文件代码片段:

../demo-dao/src/main/resources/mapper/ArticleDao.xml

	<!-- 文章表结果集resultMap 2-->
	<resultMap type="com.ljq.demo.springboot.entity.ArticleEntity" id="articleMap2">
		<result property="id" column="id"/>
		<result property="title" column="title"/>
		<result property="content" column="content"/>
		<collection property="tagList" javaType="java.util.List" ofType="com.ljq.demo.springboot.entity.ArticleTagEntity" >
			<id property="id" column="at_id" />
			<result property="tagName" column="at_tag_name" />
		</collection>
	</resultMap>

   <!-- 文章表-基础字段 -->
	<sql id="article_base_field">
        a.`id`,
        a.`title`,
        a.`content`
	</sql>

	<!-- 文章表-列表字段 -->
	<sql id="article_list_field">
        <include refid="article_base_field" />
        ,
        at.`id` at_id,
        at.`tag_name` at_tag_name
	</sql>

	<!-- 列表查询-3 -->
	<select id="queryListComplex" parameterType="java.util.Map" resultMap="articleMap2">
		SELECT
		<include refid="article_list_field" />
		FROM (
		SELECT item_a.*
		FROM `article` item_a
		LEFT JOIN `article_to_tag` item_att ON item_att.article_id = item_a.id
		LEFT JOIN `article_tag` item_at ON item_at.id = item_att.tag_id
		<if test="title != null and '' != title">
			AND item_a.title LIKE CONCAT(CONCAT("%", #{title}), "%")
		</if>
		<if test="articleTag != null and '' != articleTag">
			AND item_at.tag_name LIKE CONCAT(CONCAT("%", #{articleTag}), "%")
		</if>
		GROUP BY item_a.id
		ORDER BY
		<choose>
			<when test="properties != null and direction.trim() != ''">
				item_a.${properties} ${direction}
			</when>
			<otherwise>
				item_a.id DESC
			</otherwise>
		</choose>
		<choose>
			<when test="offset != null and pageLimit != null">
				LIMIT #{offset}, #{pageLimit}
			</when>
			<otherwise>
				LIMIT 0, 5
			</otherwise>
		</choose>
		) a
		LEFT JOIN `article_to_tag` att ON att.article_id = a.id
		LEFT JOIN `article_tag` at ON at.id = att.tag_id
		GROUP BY a.id,at.id
		ORDER BY
		<choose>
			<when test="properties != null and direction.trim() != ''">
				a.${properties} ${direction}
			</when>
			<otherwise>
				a.id DESC
			</otherwise>
		</choose>
	</select>

	<!-- 统计列表查询数量-3 -->
	<select id="countComplex" parameterType="java.util.Map" resultType="int">
		SELECT COUNT(DISTINCT(a.id))
		FROM article a
		LEFT JOIN `article_to_tag` att ON att.article_id = a.id
		LEFT JOIN `article_tag` at ON at.id = att.tag_id
		WHERE 1 = 1
		<if test="title != null and '' != title">
			AND a.title LIKE CONCAT(CONCAT("%", #{title}), "%")
		</if>
		<if test="articleTag != null and '' != articleTag">
			AND at.tag_name LIKE CONCAT(CONCAT("%", #{articleTag}), "%")
		</if>
	</select>

3.4 子查询主表不关联

java 类代码片段:

../demo-service/src/main/java/com/ljq/demo/springboot/service/impl/ArticleServiceImpl.java

	/**
	 * 查询列表-对比测试-4
	 *
	 * @param articleListParam
	 * @return
	 * @throws Exception
	 */
	@Override
	public ApiResult list4(ArticleListParam articleListParam) throws Exception {
		long start = System.currentTimeMillis();
		QueryUtil queryMap = new QueryUtil(BeanUtil.beanToMap(articleListParam, false, true));
		int countTotal = articleDao.countComplex4(queryMap);
		PageUtil pageUtil;
		if (countTotal > 0) {
			List<ArticleEntity> entityList = articleDao.queryListComplex4(queryMap);
			pageUtil = new PageUtil(entityList,countTotal, queryMap.getPageLimit(), queryMap.getCurrPage());
		} else {
			pageUtil = new PageUtil(null, countTotal, queryMap.getPageLimit(), queryMap.getCurrPage());
		}
		long end = System.currentTimeMillis();
		log.info("查询耗时: {}", (end - start));
		return ApiResult.success(pageUtil);
	}

Mapper 文件代码片段:

../demo-dao/src/main/resources/mapper/ArticleDao.xml

	<!-- 文章表结果集resultMap 2-->
	<resultMap type="com.ljq.demo.springboot.entity.ArticleEntity" id="articleMap2">
		<result property="id" column="id"/>
		<result property="title" column="title"/>
		<result property="content" column="content"/>
		<collection property="tagList" javaType="java.util.List" ofType="com.ljq.demo.springboot.entity.ArticleTagEntity" >
			<id property="id" column="at_id" />
			<result property="tagName" column="at_tag_name" />
		</collection>
	</resultMap>

   <!-- 文章表-基础字段 -->
	<sql id="article_base_field">
        a.`id`,
        a.`title`,
        a.`content`
	</sql>

	<!-- 文章表-列表字段 -->
	<sql id="article_list_field">
        <include refid="article_base_field" />
        ,
        at.`id` at_id,
        at.`tag_name` at_tag_name
	</sql>

	<!-- 统计查询数量-4 S -->
	<select id="countComplex4" parameterType="java.util.Map" resultType="int">
		SELECT COUNT(DISTINCT(a.id))
		FROM article a
		WHERE 1 = 1
		<if test="title != null and '' != title">
			AND a.title LIKE CONCAT(CONCAT("%", #{title}), "%")
		</if>
	</select>
	<!-- 统计查询数量-4 E -->

	<!-- 查询列表-4 S -->
	<select id="queryListComplex4" parameterType="java.util.Map" resultMap="articleMap2">
		SELECT
		    <include refid="article_list_field" />
		FROM (
		SELECT item_a.*
		FROM `article` item_a
		<if test="title != null and '' != title">
			AND item_a.title LIKE CONCAT(CONCAT("%", #{title}), "%")
		</if>
		ORDER BY
		<choose>
			<when test="properties != null and direction.trim() != ''">
				item_a.${properties} ${direction}
			</when>
			<otherwise>
				item_a.id DESC
			</otherwise>
		</choose>
		<choose>
			<when test="offset != null and pageLimit != null">
				LIMIT #{offset}, #{pageLimit}
			</when>
			<otherwise>
				LIMIT 0, 5
			</otherwise>
		</choose>
		) a
		LEFT JOIN `article_to_tag` att ON att.article_id = a.id
		LEFT JOIN `article_tag` at ON at.id = att.tag_id
		GROUP BY a.id,at.id
		ORDER BY
		<choose>
			<when test="properties != null and direction.trim() != ''">
				a.${properties} ${direction}
			</when>
			<otherwise>
				a.id DESC
			</otherwise>
		</choose>
	</select>
	<!-- 查询列表-4 E -->

​

4 测试结果对比

4.1 Mybatis PageHelper 主表关联查询

请求接口:

http://127.0.0.1:8088/api/article/list?currPage=100&direction=desc&pageLimit=10&properties=id

执行SQL:

2019-11-28 14:58:17 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.dao.article.ArticleDao.queryListPage_COUNTBaseJdbcLogger.java 159| ==>  Preparing: SELECT count(0) FROM (SELECT a.`id`, a.`title`, a.`content` FROM article a LEFT JOIN `article_to_tag` att ON att.article_id = a.id LEFT JOIN `article_tag` at ON at.id = att.tag_id WHERE 1 = 1 GROUP BY a.id) table_count 
2019-11-28 14:58:17 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.dao.article.ArticleDao.queryListPage_COUNTBaseJdbcLogger.java 159| ==> Parameters: 
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.dao.article.ArticleDao.queryListPage_COUNTBaseJdbcLogger.java 159| <==      Total: 1
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.dao.article.ArticleDao.queryListPageBaseJdbcLogger.java 159| ==>  Preparing: SELECT a.`id`, a.`title`, a.`content` FROM article a LEFT JOIN `article_to_tag` att ON att.article_id = a.id LEFT JOIN `article_tag` at ON at.id = att.tag_id WHERE 1 = 1 GROUP BY a.id order by id desc LIMIT ?, ? 
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.dao.article.ArticleDao.queryListPageBaseJdbcLogger.java 159| ==> Parameters: 990(Integer), 10(Integer)
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====>  Preparing: SELECT at.`id`, at.`tag_name` FROM `article_to_tag` att LEFT JOIN `article_tag` at ON at.id = att.tag_id LEFT JOIN `article` a ON a.id = att.article_id WHERE a.id = ? 
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====> Parameters: 999018(Long)
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| <====      Total: 2
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====>  Preparing: SELECT at.`id`, at.`tag_name` FROM `article_to_tag` att LEFT JOIN `article_tag` at ON at.id = att.tag_id LEFT JOIN `article` a ON a.id = att.article_id WHERE a.id = ? 
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====> Parameters: 999017(Long)
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| <====      Total: 2
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====>  Preparing: SELECT at.`id`, at.`tag_name` FROM `article_to_tag` att LEFT JOIN `article_tag` at ON at.id = att.tag_id LEFT JOIN `article` a ON a.id = att.article_id WHERE a.id = ? 
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====> Parameters: 999016(Long)
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| <====      Total: 2
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====>  Preparing: SELECT at.`id`, at.`tag_name` FROM `article_to_tag` att LEFT JOIN `article_tag` at ON at.id = att.tag_id LEFT JOIN `article` a ON a.id = att.article_id WHERE a.id = ? 
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====> Parameters: 999015(Long)
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| <====      Total: 2
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====>  Preparing: SELECT at.`id`, at.`tag_name` FROM `article_to_tag` att LEFT JOIN `article_tag` at ON at.id = att.tag_id LEFT JOIN `article` a ON a.id = att.article_id WHERE a.id = ? 
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====> Parameters: 999014(Long)
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| <====      Total: 2
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====>  Preparing: SELECT at.`id`, at.`tag_name` FROM `article_to_tag` att LEFT JOIN `article_tag` at ON at.id = att.tag_id LEFT JOIN `article` a ON a.id = att.article_id WHERE a.id = ? 
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====> Parameters: 999013(Long)
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| <====      Total: 2
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====>  Preparing: SELECT at.`id`, at.`tag_name` FROM `article_to_tag` att LEFT JOIN `article_tag` at ON at.id = att.tag_id LEFT JOIN `article` a ON a.id = att.article_id WHERE a.id = ? 
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====> Parameters: 999012(Long)
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| <====      Total: 2
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====>  Preparing: SELECT at.`id`, at.`tag_name` FROM `article_to_tag` att LEFT JOIN `article_tag` at ON at.id = att.tag_id LEFT JOIN `article` a ON a.id = att.article_id WHERE a.id = ? 
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====> Parameters: 999011(Long)
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| <====      Total: 2
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====>  Preparing: SELECT at.`id`, at.`tag_name` FROM `article_to_tag` att LEFT JOIN `article_tag` at ON at.id = att.tag_id LEFT JOIN `article` a ON a.id = att.article_id WHERE a.id = ? 
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====> Parameters: 999010(Long)
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| <====      Total: 2
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====>  Preparing: SELECT at.`id`, at.`tag_name` FROM `article_to_tag` att LEFT JOIN `article_tag` at ON at.id = att.tag_id LEFT JOIN `article` a ON a.id = att.article_id WHERE a.id = ? 
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====> Parameters: 999009(Long)
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| <====      Total: 2
2019-11-28 14:58:27 | DEBUG | http-nio-8088-exec-3 | c.l.d.s.dao.article.ArticleDao.queryListPageBaseJdbcLogger.java 159| <==      Total: 10
2019-11-28 14:58:27 | INFO  | http-nio-8088-exec-3 | c.l.d.springboot.service.impl.ArticleServiceImplArticleServiceImpl.java 49| 查询耗时: 10074

加上统计总条数的查询的话，共执行 N+2 条，N 为每页显示条数

耗时:

经过多次查询，平均耗时在 8-10 秒

4.2 Mybatis PageHelper 主表不关联查询

请求接口:

http://127.0.0.1:8088/api/article/list/2?currPage=200&direction=desc&pageLimit=10&properties=id

执行SQL:

2019-11-28 15:07:26 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.article.ArticleDao.queryListPage2_COUNTBaseJdbcLogger.java 159| ==>  Preparing: SELECT count(0) FROM (SELECT a.`id`, a.`title`, a.`content` FROM article a LEFT JOIN `article_to_tag` att ON att.article_id = a.id WHERE 1 = 1 GROUP BY a.id) table_count 
2019-11-28 15:07:26 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.article.ArticleDao.queryListPage2_COUNTBaseJdbcLogger.java 159| ==> Parameters: 
2019-11-28 15:07:33 | DEBUG | Eureka-PeerNodesUpdater | com.netflix.discovery.endpoint.EndpointUtilsEndpointUtils.java 198| The availability zone for the given region us-east-1 are [defaultZone]
2019-11-28 15:07:33 | WARN  | Eureka-PeerNodesUpdater | com.netflix.eureka.cluster.PeerEurekaNodesPeerEurekaNodes.java 156| The replica size seems to be empty. Check the route 53 DNS Registry
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.article.ArticleDao.queryListPage2_COUNTBaseJdbcLogger.java 159| <==      Total: 1
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.dao.article.ArticleDao.queryListPage2BaseJdbcLogger.java 159| ==>  Preparing: SELECT a.`id`, a.`title`, a.`content` FROM article a LEFT JOIN `article_to_tag` att ON att.article_id = a.id WHERE 1 = 1 GROUP BY a.id order by id desc LIMIT ?, ? 
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.dao.article.ArticleDao.queryListPage2BaseJdbcLogger.java 159| ==> Parameters: 1990(Integer), 10(Integer)
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====>  Preparing: SELECT at.`id`, at.`tag_name` FROM `article_to_tag` att LEFT JOIN `article_tag` at ON at.id = att.tag_id LEFT JOIN `article` a ON a.id = att.article_id WHERE a.id = ? 
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====> Parameters: 998018(Long)
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| <====      Total: 2
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====>  Preparing: SELECT at.`id`, at.`tag_name` FROM `article_to_tag` att LEFT JOIN `article_tag` at ON at.id = att.tag_id LEFT JOIN `article` a ON a.id = att.article_id WHERE a.id = ? 
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====> Parameters: 998017(Long)
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| <====      Total: 2
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====>  Preparing: SELECT at.`id`, at.`tag_name` FROM `article_to_tag` att LEFT JOIN `article_tag` at ON at.id = att.tag_id LEFT JOIN `article` a ON a.id = att.article_id WHERE a.id = ? 
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====> Parameters: 998016(Long)
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| <====      Total: 2
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====>  Preparing: SELECT at.`id`, at.`tag_name` FROM `article_to_tag` att LEFT JOIN `article_tag` at ON at.id = att.tag_id LEFT JOIN `article` a ON a.id = att.article_id WHERE a.id = ? 
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====> Parameters: 998015(Long)
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| <====      Total: 2
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====>  Preparing: SELECT at.`id`, at.`tag_name` FROM `article_to_tag` att LEFT JOIN `article_tag` at ON at.id = att.tag_id LEFT JOIN `article` a ON a.id = att.article_id WHERE a.id = ? 
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====> Parameters: 998014(Long)
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| <====      Total: 2
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====>  Preparing: SELECT at.`id`, at.`tag_name` FROM `article_to_tag` att LEFT JOIN `article_tag` at ON at.id = att.tag_id LEFT JOIN `article` a ON a.id = att.article_id WHERE a.id = ? 
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====> Parameters: 998013(Long)
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| <====      Total: 2
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====>  Preparing: SELECT at.`id`, at.`tag_name` FROM `article_to_tag` att LEFT JOIN `article_tag` at ON at.id = att.tag_id LEFT JOIN `article` a ON a.id = att.article_id WHERE a.id = ? 
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====> Parameters: 998012(Long)
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| <====      Total: 2
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====>  Preparing: SELECT at.`id`, at.`tag_name` FROM `article_to_tag` att LEFT JOIN `article_tag` at ON at.id = att.tag_id LEFT JOIN `article` a ON a.id = att.article_id WHERE a.id = ? 
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====> Parameters: 998011(Long)
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| <====      Total: 2
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====>  Preparing: SELECT at.`id`, at.`tag_name` FROM `article_to_tag` att LEFT JOIN `article_tag` at ON at.id = att.tag_id LEFT JOIN `article` a ON a.id = att.article_id WHERE a.id = ? 
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====> Parameters: 998010(Long)
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| <====      Total: 2
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====>  Preparing: SELECT at.`id`, at.`tag_name` FROM `article_to_tag` att LEFT JOIN `article_tag` at ON at.id = att.tag_id LEFT JOIN `article` a ON a.id = att.article_id WHERE a.id = ? 
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====> Parameters: 998009(Long)
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| <====      Total: 2
2019-11-28 15:07:33 | DEBUG | http-nio-8088-exec-6 | c.l.d.s.dao.article.ArticleDao.queryListPage2BaseJdbcLogger.java 159| <==      Total: 10
2019-11-28 15:07:33 | INFO  | http-nio-8088-exec-6 | c.l.d.springboot.service.impl.ArticleServiceImplArticleServiceImpl.java 68| 查询耗时: 7242

与 Mybatis PageHelper 主表关联查询一样，包括统计总条数的查询，共执行 N+2 条SQL，N 为每页显示条数

耗时:

经过多次测试，平均耗时在 6-8 秒区间

4.3 子查询主表关联查询

请求接口:

http://127.0.0.1:8088/api/article/list/3?currPage=300&direction=desc&pageLimit=10&properties=id

执行SQL：

2019-11-28 15:13:26 | DEBUG | http-nio-8088-exec-10 | c.l.d.s.dao.article.ArticleDao.countComplexBaseJdbcLogger.java 159| ==>  Preparing: SELECT COUNT(DISTINCT(a.id)) FROM article a LEFT JOIN `article_to_tag` att ON att.article_id = a.id LEFT JOIN `article_tag` at ON at.id = att.tag_id WHERE 1 = 1 
2019-11-28 15:13:26 | DEBUG | http-nio-8088-exec-10 | c.l.d.s.dao.article.ArticleDao.countComplexBaseJdbcLogger.java 159| ==> Parameters: 
2019-11-28 15:13:29 | DEBUG | http-nio-8088-exec-10 | c.l.d.s.dao.article.ArticleDao.countComplexBaseJdbcLogger.java 159| <==      Total: 1
2019-11-28 15:13:29 | DEBUG | http-nio-8088-exec-10 | c.l.d.s.dao.article.ArticleDao.queryListComplexBaseJdbcLogger.java 159| ==>  Preparing: SELECT a.`id`, a.`title`, a.`content` , at.`id` at_id, at.`tag_name` at_tag_name FROM ( SELECT item_a.* FROM `article` item_a LEFT JOIN `article_to_tag` item_att ON item_att.article_id = item_a.id LEFT JOIN `article_tag` item_at ON item_at.id = item_att.tag_id GROUP BY item_a.id ORDER BY item_a.id desc LIMIT 0, 5 ) a LEFT JOIN `article_to_tag` att ON att.article_id = a.id LEFT JOIN `article_tag` at ON at.id = att.tag_id GROUP BY a.id,at.id ORDER BY a.id desc 
2019-11-28 15:13:29 | DEBUG | http-nio-8088-exec-10 | c.l.d.s.dao.article.ArticleDao.queryListComplexBaseJdbcLogger.java 159| ==> Parameters: 
2019-11-28 15:13:29 | DEBUG | http-nio-8088-exec-10 | c.l.d.s.dao.article.ArticleDao.queryListComplexBaseJdbcLogger.java 159| <==      Total: 9
2019-11-28 15:13:29 | INFO  | http-nio-8088-exec-10 | c.l.d.springboot.service.impl.ArticleServiceImplArticleServiceImpl.java 92| 查询耗时: 3334

SQL 条数:

2 条，一条统计总条数，一天查询列表

耗时:

平均耗时在 3-5 秒区间

4.4 子查询主表不关联

请求接口:

http://127.0.0.1:8088/api/article/list/4?currPage=400&direction=desc&pageLimit=10&properties=id

执行SQL:

2019-11-28 15:17:14 | DEBUG | http-nio-8088-exec-7 | c.l.d.s.dao.article.ArticleDao.countComplex4BaseJdbcLogger.java 159| ==>  Preparing: SELECT COUNT(DISTINCT(a.id)) FROM article a WHERE 1 = 1 
2019-11-28 15:17:14 | DEBUG | http-nio-8088-exec-7 | c.l.d.s.dao.article.ArticleDao.countComplex4BaseJdbcLogger.java 159| ==> Parameters: 
2019-11-28 15:17:14 | DEBUG | http-nio-8088-exec-7 | c.l.d.s.dao.article.ArticleDao.countComplex4BaseJdbcLogger.java 159| <==      Total: 1
2019-11-28 15:17:14 | DEBUG | http-nio-8088-exec-7 | c.l.d.s.dao.article.ArticleDao.queryListComplex4BaseJdbcLogger.java 159| ==>  Preparing: SELECT a.`id`, a.`title`, a.`content` , at.`id` at_id, at.`tag_name` at_tag_name FROM ( SELECT item_a.* FROM `article` item_a ORDER BY item_a.id desc LIMIT 0, 5 ) a LEFT JOIN `article_to_tag` att ON att.article_id = a.id LEFT JOIN `article_tag` at ON at.id = att.tag_id GROUP BY a.id,at.id ORDER BY a.id desc 
2019-11-28 15:17:14 | DEBUG | http-nio-8088-exec-7 | c.l.d.s.dao.article.ArticleDao.queryListComplex4BaseJdbcLogger.java 159| ==> Parameters: 
2019-11-28 15:17:14 | DEBUG | http-nio-8088-exec-7 | c.l.d.s.dao.article.ArticleDao.queryListComplex4BaseJdbcLogger.java 159| <==      Total: 9
2019-11-28 15:17:14 | INFO  | http-nio-8088-exec-7 | c.l.d.springboot.service.impl.ArticleServiceImplArticleServiceImpl.java 116| 查询耗时: 834

SQL 条数:

2 条，一条统计总条数，一天查询列表

耗时:

平均耗时在 500ms - 3s 区间

​

5 测试结论

5.1 简洁性

Mybatis PageHelper 分页插件使用方便，可大大减少人工代码量；手动编写子查询代码较为复杂，难度较大，但是掌握这一知识后也可熟练使用

5.2 性能

使用 Mybatis PageHelper 耗时较长，手动编写子查询代码，耗时较短，Mybatis PageHelper 分页插件的查询时长几乎是子查询的两倍；在不涉及将关联表的字段作为查询条件的情况下，查询速度有显著提升(不建议将关联表的字段作为分页查询的条件,如果需要将关联信息都查询出来，可以使用搜索引擎)

总结:

少量数据(低于百万条)可使用 Mybatis PageHelper 分页插件，减轻编码工作量，大量数据(超过百万条)，可使用子查询的方式优化性能；最好不要将关联表的字段作为分页查询的条件；如需将主表和关联表中包含某一信息的数据都查出来，可以使用搜索引擎

​

6 提交记录

commit feb8bce5f201794919ae4c50fa3a83f7a3edc7b6
Author: flying9001 <[email protected]>
Date:   Thu Nov 28 10:56:19 2019 +0800

    代码-新增分页查询性能对比功能

版本回退命令:

git reset --soft feb8bce5f201794919ae4c50fa3a83f7a3edc7b6