记录一次PageHelper插件和SQL不规范导致的慢查询问题

一 问题描述

通过查看慢查询语句，发现类似如下的慢查询语句，t_a表数据量大概50多万，查询耗时大概是3秒。

SELECT COUNT(0)
FROM (
	SELECT a.id, a.bbbb, b.cccc, c.aaaa1, d.bbbbb2, d.ccccc3
	FROM t_a a
		LEFT JOIN t_b b ON a.aa_id = b.id
		LEFT JOIN t_c c ON b.bb_id = c.id
		LEFT JOIN t_d d ON c.cc_id = d.id
	WHERE 1 = 1
		AND a.aaa = 1
	GROUP BY a.id
) table_count

而该查询是通过PageHelper插件拦截生成的查询语句。
为何会出现count嵌套？

二 带着问题看源码

关键源码 com.github.pagehelper.parser.CountSqlParser 如下：

/**
 * 获取智能的countSql
 *
 * @param sql
 * @param name 列名，默认 0
 * @return
 */
public String getSmartCountSql(String sql, String name) {
    //解析SQL
    Statement stmt = null;
    //特殊sql不需要去掉order by时，使用注释前缀
    if(sql.indexOf(KEEP_ORDERBY) >= 0){
        return getSimpleCountSql(sql, name);
    }
    try {
        stmt = CCJSqlParserUtil.parse(sql);
    } catch (Throwable e) {
        //无法解析的用一般方法返回count语句
        return getSimpleCountSql(sql, name);
    }
    Select select = (Select) stmt;
    SelectBody selectBody = select.getSelectBody();
    try {
        //处理body-去order by
        processSelectBody(selectBody);
    } catch (Exception e) {
        //当 sql 包含 group by 时，不去除 order by
        return getSimpleCountSql(sql, name);
    }
    //处理with-去order by
    processWithItemsList(select.getWithItemsList());
    //处理为count查询
    sqlToCount(select, name);
    String result = select.toString();
    return result;
}

/**
 * 将sql转换为count查询
 *
 * @param select
 */
public void sqlToCount(Select select, String name) {
    SelectBody selectBody = select.getSelectBody();
    // 是否能简化count查询
    List<SelectItem> COUNT_ITEM = new ArrayList<SelectItem>();
    COUNT_ITEM.add(new SelectExpressionItem(new Column("count(" + name +")")));
    if (selectBody instanceof PlainSelect && isSimpleCount((PlainSelect) selectBody)) {
        ((PlainSelect) selectBody).setSelectItems(COUNT_ITEM);
    } else {
        PlainSelect plainSelect = new PlainSelect();
        SubSelect subSelect = new SubSelect();
        subSelect.setSelectBody(selectBody);
        subSelect.setAlias(TABLE_ALIAS);
        plainSelect.setFromItem(subSelect);
        plainSelect.setSelectItems(COUNT_ITEM);
        select.setSelectBody(plainSelect);
    }
}

/**
 * 是否可以用简单的count查询方式
 *
 * @param select
 * @return
 */
public boolean isSimpleCount(PlainSelect select) {
    //包含group by的时候不可以
    if (select.getGroupBy() != null) {
        return false;
    }
    //包含distinct的时候不可以
    if (select.getDistinct() != null) {
        return false;
    }
    for (SelectItem item : select.getSelectItems()) {
        //select列中包含参数的时候不可以，否则会引起参数个数错误
        if (item.toString().contains("?")) {
            return false;
        }
        //如果查询列中包含函数，也不可以，函数可能会聚合列
        if (item instanceof SelectExpressionItem) {
            Expression expression = ((SelectExpressionItem) item).getExpression();
            if (expression instanceof Function) {
                String name = ((Function) expression).getName();
                if (name != null) {
                    String NAME = name.toUpperCase();
                    if(skipFunctions.contains(NAME)){
                        //go on
                    } else if(falseFunctions.contains(NAME)){
                        return false;
                    } else {
                        for (String aggregateFunction : AGGREGATE_FUNCTIONS) {
                            if(NAME.startsWith(aggregateFunction)){
                                falseFunctions.add(NAME);
                                return false;
                            }
                        }
                        skipFunctions.add(NAME);
                    }
                }
            }
        }
    }
    return true;
}

其中是否可以使用简单的count查询isSimpleCount中可知，当SQL中包含group by、distinct，或者查询项包含参数、函数都不可以。
而我们的sql就是包含了group by，具体是什么原因让开发同学在这个分页查询的sql里加上了这个group by不太清楚，但是经过分析，其实这个group by完全是多余的，所以优化的做法是直接去掉了。

所以显而易见，就是因为有 group by，导致了嵌套，从而导致了count查询时间更长。如果去掉group by之后，PageHelper帮我们生成的countSQL如下：

SELECT count(0)
FROM t_a a
  LEFT JOIN t_b b ON a.aa_id = b.id
  LEFT JOIN t_c c ON b.bb_id = c.id
  LEFT JOIN t_d d ON c.cc_id = d.id
WHERE 1 = 1
  AND a.aaa = 1
GROUP BY a.id

效果
调整之后，我们的分页查询时间从3s降到了1.2s左右，但是查询时间还是太长，最终与产品沟通，是否可以仅显示最近一段时间的数据，沟通后产品的反馈是业务仅关心最近的数据，历史的数据一般仅做分析用，所以我们把时间范围又做了过滤，默认仅显示最近3个月的数据，可以修改时间范围，但是最宽仅支持3个月。最终查询时间直降到了300ms左右，勉强可以接受。

三 总结

对于我们所使用的一些插件框架，我们要尽可能去了解底层帮我们做了什么事情，可以帮我们规避很多使用不当的问题。
另外，该示例，即使没有这个嵌套，查询也不是很快，因为数据量过大，而且其中其实通过条件还支持了一些模糊查询，所以针对这种情况，我们一般要进一步去设计一些必须过滤的条件，比如时间范围必填，如一个月，控制数据只能查一个月范围内的，这样，数据量降下来了，查询效率才能得到解决。

当然如果说数据量过大，又有很多字段的模糊查询，那这时候可能要考虑需求是否合理，是否仅关心最近一段时间的数据即可，如果需求是合理的，那么就要考虑直接在数据库查是否合适的问题了，不合适那是否可以考虑ES等等来代替等等。

---