因为PageHelper对于MySQL的分页逻辑采用的是SQL后面追加limit子句的方式，这样在小数据量情况下是没有问题的。但是对于大数据量的时候，比如limit 100000, 10，MySQL的运作机理是查出100010条数据，再抛弃掉前100000条，留下剩余10条数据。所以执行效率并不高。
对于MySQL分页SQL的优化我在之前总结过《MySQL查询优化》，无非就是写个自关联，先走内部的主键索引/覆盖索引，这个效率很高。因为每页的数据一般都是10条，所以查出来的数据在和外部做关联的时候即使没有走索引，也不会慢。
正好在网上也没有搜到相关的自定义实现，所以我就在想能不能自己重写PageHelper中的分页逻辑。在看了看PageHelper源码中相关的实现后发现，PageHelper的分页逻辑是写在了AbstractHelperDialect类中，而不同的数据库分页实现是通过继承实现抽象方法的方式来实现的。对于MySQL的分页就是MySqlDialect类，典型的模板方法模式。
再来查看MySqlDialect类的实现：
可以看到就是重写了父类的两个抽象方法，processPageParameter方法是用来处理分页参数的，这个方法不需要管。而下面的getPageSql方法才是用来拼接分页SQL的，可以看到就是简单追加limit子句的方式。看到这里其实就能想到自己再写一个Dialect类，继承MySqlDialect类，覆写其中的getPageSql方法即可实现想要的自定义分页逻辑：
public class MyMySqlDialect extends MySqlDialect {
@Override
public String getPageSql(String sql, Page page, CacheKey pageKey) {
//实现自定义分页逻辑
}
}写完了自定义的分页逻辑后，要想用到它需要做下面的配置：
pagehelper.helperDialect=com.hys.pagehelper.config.MyMySqlDialect因为暂时没找到相关的抓取SQL中各个部分的源码（比如表名、字段名等等），只看到了分页的参数，所以我决定自己来实现。我是用正则表达式来实现的（正则大法好），我之前也写过讲解正则表达式的文章《Java中的正则表达式概述》，感兴趣的可以查看。同时我之前也写过一个抓取出不同数据库SQL中的源表和目标表的解析器HSP，也是用正则表达式来实现的。
下面演示一下我重写的分页逻辑的执行效果。首先是XML中的SQL：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD//EN Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hys.pagehelper.dao.UserDAO">
<sql id="allColumns">
id,
name,
sex,
address
</sql>
<select id="list" resultType="com.hys.pagehelper.entity.UserDO">
SELECT
<include refid="allColumns"/>
FROM user
</select>
</mapper>
可以看到就是需要对user表进行分页处理。使用的话跟原生的PageHelper类的使用是没有区别的，manager层：
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.hys.pagehelper.dao.UserDAO;
import com.hys.pagehelper.entity.Pager;
import com.hys.pagehelper.entity.UserDO;
import com.hys.pagehelper.util.PageHelperUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
/**
* 用户Manager
*
* @author Robert Hou
* @since 2020年11月28日 10:59
**/
@Component
public class UserManager {
@Autowired
private UserDAO userDAO;
public Pager<UserDO> list(int pageNum, int pageSize) {
PageHelper.startPage(pageNum, pageSize);
Page<UserDO> list = userDAO.list();
return PageHelperUtils.pageTransform(list);
}
}主要看一下重写的自定义分页实现的代码：
import com.github.pagehelper.Page;
import com.github.pagehelper.dialect.helper.MySqlDialect;
import com.hys.pagehelper.exception.ParseException;
import com.hys.pagehelper.util.PageHelperUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.BooleanUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.cache.CacheKey;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
/**
* 自定义MySQL分页逻辑
*
* @author Robert Hou
* @since 2020年11月27日 17:45
**/
@Slf4j
public class MyMySqlDialect extends MySqlDialect {
private static final Pattern PATTERN = Pattern.compile("SELECT\\s*([\\s|\\S]*?)\\s*?((FROM\\s*[0-9a-zA-Z_`]*)\\s*[\\s|\\S]*)", Pattern.CASE_INSENSITIVE);
private static final Pattern CONTAINS_JOIN_PATTERN = Pattern.compile("[\\s|\\S]*JOIN[\\s|\\S]*", Pattern.CASE_INSENSITIVE);
private static final Pattern CONTAINS_DISTINCT_PATTERN = Pattern.compile("\\s+DISTINCT\\s+", Pattern.CASE_INSENSITIVE);
private static final Pattern CONTAINS_ALIAS_PATTERN = Pattern.compile("\\s*(\\S+)(\\s+AS\\s+\\S+)?\\s*", Pattern.CASE_INSENSITIVE);
private static final Pattern CONTAINS_GROUP_BY_PATTERN = Pattern.compile("[\\s|\\S]*GROUP\\s+BY[\\s|\\S]*", Pattern.CASE_INSENSITIVE);
private static final Pattern CONTAINS_ORDER_BY_PATTERN = Pattern.compile("[\\s|\\S]*ORDER\\s+BY[\\s|\\S]*", Pattern.CASE_INSENSITIVE);
@Override
public String getPageSql(String sql, Page page, CacheKey pageKey) {
Matcher containsJoinMatcher = CONTAINS_JOIN_PATTERN.matcher(sql);
if (containsJoinMatcher.find() || BooleanUtils.isTrue(PageHelperUtils.getIsRelegated())) {
//多表分页逻辑没实现，用默认的SQL后面追加LIMIT子句的方式（对于不是JOIN方式来进行表连接的SQL（比如笛卡尔积），执行可能会报错。这个时候需要手动将降级选项置为true）
log.info("使用了多表联查的SQL、或是手动将降级选项置为true的SQL，不会进行优化，而是转而使用默认的SQL后面追加LIMIT子句的方式");
return invokeSuperMethod(sql, page, pageKey);
}
Matcher containsGroupByMatcher = CONTAINS_GROUP_BY_PATTERN.matcher(sql);
Matcher containsOrderByMatcher = CONTAINS_ORDER_BY_PATTERN.matcher(sql);
if (containsGroupByMatcher.find() || containsOrderByMatcher.find()) {
//todo GROUP_BY和ORDER_BY语句没有适配，暂时先降级
return invokeSuperMethod(sql, page, pageKey);
}
log.info("\n原始SQL：\n{}", sql);
List<String> keyNames = PageHelperUtils.getKeyNames();
if (CollectionUtils.isEmpty(keyNames)) {
//没有添加@KeyNamesStrategy注解，也将表主键名设置为”id“
PageHelperUtils.setKeyNames(new String[]{"id"});
keyNames = PageHelperUtils.getKeyNames();
}
String fromTable = null;
String fields = null;
String afterClause = null;
boolean isSucceeded = false;
Matcher m = PATTERN.matcher(sql);
boolean isDistinctContains = false;
if (m.find()) {
isSucceeded = true;
//SELECT后面FROM前面的查找字段
fields = m.group(1);
if (isOnlyContainsPrimaryKey(fields)) {
//如果字段中只包含主键的话，则不需要优化（因为要查的字段都在B+树上，不需要回表进行查询）。改用默认的SQL后面追加LIMIT子句的方式
log.info("要查询的字段中只包含主键，不需要优化，转而使用默认的SQL后面追加LIMIT子句的方式");
return invokeSuperMethod(sql, page, pageKey);
}
if (fields != null) {
//查看SQL中是否含有DISTINCT
Matcher containsDistinctMatcher = CONTAINS_DISTINCT_PATTERN.matcher(sql);
if (containsDistinctMatcher.find()) {
isDistinctContains = true;
}
StringBuilder stringBuilder = new StringBuilder();
for (String keyName : keyNames) {
String regex = "[\\s]*[^\\S]+((`)?" + keyName + "(`)?)[^\\S]+[\\s]*";
Pattern containsPattern = Pattern.compile(regex, Pattern.CASE_INSENSITIVE);
String[] fieldArray = fields.split(",");
List<String> fieldList = new ArrayList<>(fieldArray.length);
for (String field : fieldArray) {
stringBuilder.delete(0, stringBuilder.length());
field = stringBuilder.append(" ").append(field).append(" ").toString();
Matcher matcher = containsPattern.matcher(field);
if (matcher.find()) {
String keyNameAndBackQuoteIfContains = matcher.group(1).toUpperCase();
//只替换第一个是为了解决表主键起别名的情况
fieldList.add(field.toUpperCase().replaceFirst(keyNameAndBackQuoteIfContains, "pageHelperAlias1." + keyNameAndBackQuoteIfContains));
} else {
fieldList.add(field);
}
}
if (!fieldList.isEmpty()) {
fields = String.join(",", fieldList);
}
}
}
//FROM+后面的子句
afterClause = m.group(2);
if (page.getStartRow() == 0) {
afterClause = afterClause + "\n LIMIT ? ";
} else {
afterClause = afterClause + "\n LIMIT ?, ? ";
}
//FROM+表名
fromTable = m.group(3);
}
if (!isSucceeded) {
throw new ParseException("解析失败！需要排查SQL！");
}
String returnSql = "SELECT " + fields + " " + fromTable + " pageHelperAlias1 \n" +
" INNER JOIN ( SELECT " + (isDistinctContains ? "DISTINCT " : "") + getKeyNames(keyNames) + " " + afterClause + " ) pageHelperAlias2"
+ joinKeyNames(keyNames);
log.info("\n拼接后的分页SQL：\n{}", returnSql);
PageHelperUtils.remove();
return returnSql;
}
private String invokeSuperMethod(String sql, Page<?> page, CacheKey pageKey) {
PageHelperUtils.remove();
return super.getPageSql(sql, page, pageKey);
}
/**
* KeyNames转换成String格式（逗号拼接）
*/
private String getKeyNames(List<String> keyNames) {
if (CollectionUtils.isEmpty(keyNames)) {
return null;
}
StringBuilder stringBuilder = new StringBuilder();
for (String keyName : keyNames) {
stringBuilder.append(keyName.trim()).append(", ");
}
stringBuilder.deleteCharAt(stringBuilder.length() - 2);
return stringBuilder.toString();
}
/**
* KeyNames转换成SQL JOIN的关联格式
*/
private String joinKeyNames(List<String> keyNames) {
if (CollectionUtils.isEmpty(keyNames)) {
return null;
}
StringBuilder stringBuilder = new StringBuilder(" ON ");
for (int i = 0; i < keyNames.size(); i++) {
keyNames.set(i, keyNames.get(i).trim());
stringBuilder.append("pageHelperAlias1.").append(keyNames.get(i)).append(" = pageHelperAlias2.").append(keyNames.get(i));
if (i != keyNames.size() - 1) {
stringBuilder.append(" AND ");
}
}
return stringBuilder.toString();
}
/**
* 判断字段中是否只包含主键
*/
private boolean isOnlyContainsPrimaryKey(String fields) {
if (StringUtils.isBlank(fields)) {
return false;
}
List<String> keyNames = PageHelperUtils.getKeyNames();
if (CollectionUtils.isEmpty(keyNames)) {
return false;
}
//考虑keyNames有可能有重复主键名的情况 e.g.@KeyNamesStrategy(keyNames = {"order_id", "order_id"})
List<String> uniqueKeyNames = unique(keyNames);
String[] fieldArray = fields.split(",");
List<String> uniqueFieldArray = unique(fieldArray);
int length = 0;
for (String field : uniqueFieldArray) {
if (!uniqueKeyNames.contains(field)) {
return false;
}
length++;
}
return length == uniqueKeyNames.size();
}
private String removeAliasAndBackQuoteIfContains(String field) {
if (StringUtils.isBlank(field)) {
return StringUtils.EMPTY;
}
Matcher matcher = CONTAINS_ALIAS_PATTERN.matcher(field);
if (matcher.find()) {
String keyNameAndBackQuoteIfContains = matcher.group(1);
if (keyNameAndBackQuoteIfContains.startsWith("`") && keyNameAndBackQuoteIfContains.endsWith("`")) {
return keyNameAndBackQuoteIfContains.substring(1, keyNameAndBackQuoteIfContains.length() - 1);
}
return keyNameAndBackQuoteIfContains;
}
return field;
}
private List<String> unique(List<String> list) {
if (CollectionUtils.isEmpty(list)) {
return Collections.emptyList();
}
return list.stream().map(this::removeAliasAndBackQuoteIfContains).distinct().collect(Collectors.toList());
}
private List<String> unique(String[] array) {
if (ArrayUtils.isEmpty(array)) {
return Collections.emptyList();
}
List<String> list = Arrays.stream(array).collect(Collectors.toList());
return unique(list);
}
}
下面演示一下执行效果：
原始SQL：
SELECT
id,
name,
sex,
address
FROM user
拼接后的分页SQL：
SELECT pageHelperAlias1.id,
name,
sex,
address FROM user pageHelperAlias1
INNER JOIN ( SELECT id FROM user
LIMIT ?, ? ) pageHelperAlias2 ON pageHelperAlias1.id = pageHelperAlias2.id
==> Preparing: SELECT pageHelperAlias1.id, name, sex, address FROM user pageHelperAlias1 INNER JOIN ( SELECT id FROM user LIMIT ?, ? ) pageHelperAlias2 ON pageHelperAlias1.id = pageHelperAlias2.id
==> Parameters: 10(Long), 10(Integer)
<== Total: 3上面是后台打印的日志，因为我这里的user表的主键是id字段，所以可以看到是能成功分页出来的。而默认的PageHelper类的MySQL分页是简单通过后面追加limit子句的方式来实现的。
当然我这里的假设是表的主键名一定是“id”。而如果表的主键名不是“id”的话，比如“order_id”，我这里也做了支持。我使用了Spring AOP（自定义注解和环绕通知），只需要在manager层上加上我封装的自定义注解@KeyNamesStrategy就行了：
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.hys.pagehelper.annotation.KeyNamesStrategy;
import com.hys.pagehelper.dao.UserDAO;
import com.hys.pagehelper.entity.Pager;
import com.hys.pagehelper.entity.UserDO;
import com.hys.pagehelper.util.PageHelperUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
/**
* 用户Manager
*
* @author Robert Hou
* @since 2020年11月28日 10:59
**/
@Component
public class UserManager {
@Autowired
private UserDAO userDAO;
@KeyNamesStrategy(keyNames = "name")
public Pager<UserDO> list(int pageNum, int pageSize) {
PageHelper.startPage(pageNum, pageSize);
Page<UserDO> list = userDAO.list();
return PageHelperUtils.pageTransform(list);
}
}我这里是假设user表的主键名是“name”，而不再是“id”，再来看一下后台打印的日志：
原始SQL：
SELECT
id,
name,
sex,
address
FROM user
拼接后的分页SQL：
SELECT id,
pageHelperAlias1.name,
sex,
address FROM user pageHelperAlias1
INNER JOIN ( SELECT name FROM user
LIMIT ?, ? ) pageHelperAlias2 ON pageHelperAlias1.name = pageHelperAlias2.name
==> Preparing: SELECT id, pageHelperAlias1.name, sex, address FROM user pageHelperAlias1 INNER JOIN ( SELECT name FROM user LIMIT ?, ? ) pageHelperAlias2 ON pageHelperAlias1.name = pageHelperAlias2.name
==> Parameters: 10(Long), 10(Integer)
<== Total: 3可以看到也是能成功分页出来的。哪怕是联合主键，比如user表中的“id”和“name”两个字段联合做主键，也可以这么写：
@KeyNamesStrategy(keyNames = {"id", "name"})再来看一下后台打印的日志：
原始SQL：
SELECT
id,
name,
sex,
address
FROM user
拼接后的分页SQL：
SELECT pageHelperAlias1.id,
pageHelperAlias1.name,
sex,
address FROM user pageHelperAlias1
INNER JOIN ( SELECT id, name FROM user
LIMIT ?, ? ) pageHelperAlias2 ON pageHelperAlias1.id = pageHelperAlias2.id AND pageHelperAlias1.name = pageHelperAlias2.name
==> Preparing: SELECT pageHelperAlias1.id, pageHelperAlias1.name, sex, address FROM user pageHelperAlias1 INNER JOIN ( SELECT id, name FROM user LIMIT ?, ? ) pageHelperAlias2 ON pageHelperAlias1.id = pageHelperAlias2.id AND pageHelperAlias1.name = pageHelperAlias2.name
==> Parameters: 10(Long), 10(Integer)
<== Total: 3可以看到也是能成功分页出来的。相关的代码我已经上传到GitHub上了，地址：https://github.com/ACoolMonkey/PageHelper
介于测试的数据量不大，可能会有bug。同时目前仅支持MySQL单表的分页操作，对于多表连接的分页需求，我这里暂时是没有自定义实现的，会退化成默认的追加limit子句的方式来实现（如果一定要实现，是否可以考虑使用视图View来实现？）。而且对于笛卡尔积那种没有写“JOIN”直接进行连接的SQL，我这里执行可能会报错，这点等以后有时间了再去研究吧，其实对于笛卡尔积来说都可以把SQL改为“JOIN”的方式。如果不想改动的话，我这里也提供一个降级选项：isRelegated。默认值为false，如果设置为true的话，这条SQL就会完全走默认的PageHelper分页的逻辑，而不会走我自定义的分页逻辑（性能要低一些，但是起码能保证不出错）：
@KeyNamesStrategy(isRelegated = true)原创不易，未得准许，请勿转载，翻版必究