mysql 分页获取总条数_MySQL查询分页,同时获取总记录数

Mysql分页查询获取totalCount大幅提升性能的办法总结

MySQL查询分页,通常在MySQL中获取记录总数都会使用SELECT COUNT(*) FROM tblName之类的语句

这类语句的缺点是:记录集还需要单独的查询来获取,相当于查询两次,推荐如下写法:

SELECT   SQL_CALC_FOUND_ROWS fldName1, fldName2 FROM tblName WHERE fldName3 = 1 LIMIT 10, OFFSET 20;

SELECT FOUND_ROWS();

虽然有两条SQL语句,但实际上只执行了一次数据库查询

做分页查询中,一般情况下需要两个sql,查当前页数据 和 查记录总条数;但后者的查询结果变化有时候并不大,而且count还占用了很大一部分的查询时间;主要是想用一种省时简便的方法查询符合条件的记录总数,

查询数据使用的sql为:

SELECT SUBSTRING_INDEX(`url`,'/',3) AS host,COUNT(*) AS count FROM `tab` WHERE `type`=4 GROUP BY host HAVING(count >= 5) ORDER BY count desc LIMIT 0,10

以下是网上查到的一些尝试过的方法(不过后来都感觉不太合适,所以,亮点在最后):

方法一: 一般情况下可以使用DISTINCT来查询总数

select count(DISTINCT SUBSTRING_INDEX(`url`,'/',3)) as c from tab where type = 4

但是 查询数据中的sql 有 having 子句,这样得到的总数是没有经过条件筛选的。这个结果是错误的。

方法二: 通过 SQL_CALC_FOUND_ROWS 选项忽略 LIMIT 子句,然后通过FOUND_ROWS()获得查询总数,那么sql改为:

SELECT SQL_CALC_FOUND_ROWS SUBSTRING_INDEX(`url`,'/',3) AS host,COUNT(*) AS count FROM `tab` WHERE `type`=4 GROUP BY host HAVING(count >= 5) ORDER BY count desc LIMIT 0,10

再通过 select FOUND_ROWS(); 获得总数

这样获得的总数没问题,但是由于分页程序需要先获得符合条件的总数,才能生成 page_list ,以及验证offset,和总页数等信息,所以不能先查询数据再得总数。

方法三:和上边的方法类似,只是第一次使用sql获得总数

先:

SELECT SUBSTRING_INDEX(`url`,'/',3) AS host,COUNT(*) AS count FROM `tab` WHERE `type`=4 GROUP BY host HAVING(count >= 5)

然后:

select FOUND_ROWS();

最后:

SELECT SUBSTRING_INDEX(`url`,'/',3) AS host,COUNT(*) AS count FROM `tab` WHERE `type`=4 GROUP BY host HAVING(count >= 5) ORDER BY count desc LIMIT 0,10

这个没有问题,也可以避免方法二中的问题,但是会返回全部的符合条件的数据,并且返回的数据没有任何作用,只是查询一次总数,所以也不可取。

方法四:使用子查询

select count(*) as count from (select SUBSTRING_INDEX(url,'/',3) as host,count(*) as c from tab where type=4 group by host having(c >= 5)) as temp

这个基本满足了需要,但是效率不是很高,如果子集很大的话,性能上是个问题。

以上4种方法,是网上查到的,但感觉都不是特别好或特别通用;后来经多方努力查找和学习,选用了自己写一套智能生产count查询语句的方案;

该方案采用了第三方包jsqlparser来解析sql结构并智能拼接count查询语句;

以我现在使用的java语言mybatis框架为示例:

框架中分页查询的count语句是这样产生的:

[java] view plain copy

String count_sql = dialect.getCountString(sql);

mybatis分页插件paginator中,mysql方言是这样实现的:

[html] view plain copy

/**

* 将sql转换为总记录数SQL

* @param sql SQL语句

* @return 总记录数的sql

*/

public String getCountString(String sql){

return "select count(1) from (" + sql + ") tmp_count";

}

当我看到这段源码的时候,有种想骂娘的感觉,mybatis官方提供的这种count写法,性能真不敢恭维!

于是乎亲自动手覆盖了如下方法:

[java] view plain copy

/**

* 优化父类的getCountString性能

*/

public String getCountString(String sql) {

try {

boolean queryCacheable = queryCachedFlag.get() != null && queryCachedFlag.get();

queryCachedFlag.remove();// 使用一次清理一次

return MySqlSmartCountUtil.getSmartCountSql(sql, queryCacheable);

} catch (JSQLParserException e) {

e.printStackTrace();

} catch (Exception e) {

e.printStackTrace();

}

return "select count(*) from (" + sql + ") tmp_count";

}

MySqlSmartCountUtil就是今天介绍的大神,是用jsqlparser写的智能生产count语句的工具类,采用了mysql查询缓存和获取count语句静态缓存的策略,大大提升了只能生产count语句的时间,和count查询的时间;源码分享给大家:

[java] view plain copy

public class MySqlSmartCountUtil {

// countSql缓存

private static HashMap countSqlCache = new HashMap();

private static HashMap queryCacheableCountSqlCache = new HashMap();

private static final List countItem = new ArrayList();

private static final List sqlCachedCountItem = new ArrayList();

static {

countItem.add(new SelectExpressionItem(new Column("count(*) as totalX")));

sqlCachedCountItem.add(new SelectExpressionItem(new Column("sql_cache count(*) as totalX")));

}

private static void cacheSmartCountSql(String srcSql, String countSql, boolean queryCacheable) {

if (queryCacheable)

queryCacheableCountSqlCache.put(srcSql, countSql);

else

countSqlCache.put(srcSql, countSql);

}

private static List getCountItem(boolean queryCacheable) {

return queryCacheable ? sqlCachedCountItem : countItem;

}

private static void smartCountPlainSelect(PlainSelect plainSelect, boolean queryCacheable) throws JSQLParserException{

// 去掉orderby

OrderByUtil.removeOrderBy(plainSelect);

// 判断是否包含group by

if(GMUtil.isEmpty(plainSelect.getGroupByColumnReferences())){

plainSelect.setSelectItems(getCountItem(queryCacheable));

} else {

throw new JSQLParserException("不支持智能count的sql格式: GROUP BY ");

}

}

public static String getSmartCountSql(String srcSql, boolean queryCacheable) throws JSQLParserException {

// 直接从缓存中取

if(!queryCacheable && countSqlCache.containsKey(srcSql))

return countSqlCache.get(srcSql);

if(queryCacheable && queryCacheableCountSqlCache.containsKey(srcSql))

return queryCacheableCountSqlCache.get(srcSql);

Statement stmt = CCJSqlParserUtil.parse(srcSql);

Select select = (Select) stmt;

SelectBody selectBody = select.getSelectBody();

if (selectBody instanceof PlainSelect) {

PlainSelect plainSelect = ((PlainSelect) selectBody);

smartCountPlainSelect(plainSelect, queryCacheable);

} else if (selectBody instanceof SetOperationList) {

SetOperationList setOperationList = (SetOperationList) selectBody;

boolean isUnion = false;

for (SetOperation o : setOperationList.getOperations()) {

isUnion = (o.toString().contains("UNION"));

if (!isUnion)

break;

}

// union all 语句的智能count

if(isUnion){

for (PlainSelect ps : setOperationList.getPlainSelects()) {

smartCountPlainSelect(ps, false);// TODO 强制不允许缓存

}

String resultSql = "select sum(totalX) from (" + select.toString() + ") as t ";

cacheSmartCountSql(srcSql, resultSql, false);// TODO 强制不允许缓存

return resultSql;

} else {

throw new JSQLParserException("不支持智能count的sql格式");

}

} else {

throw new JSQLParserException("不支持智能count的sql格式");

}

cacheSmartCountSql(srcSql, select.toString(), queryCacheable);

return select.toString();

}

}

目前该工具类可以支持简单的select查询,group by查询,union查询,更为复杂的查询还没有测试过,不过即使你的sql很复杂,最悲催的结局就是工具类抛出异常,方言类中会使用paginator古老的count语句为你服务!

  • 1
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值