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

墨蓝 2022-05-28 03:53 966阅读 0赞

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

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

查询数据使用的sql为：

1.  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来查询总数

1.  **select** count(DISTINCT SUBSTRING\_INDEX(\`url\`,'/',3)) **as** c **from** tab **where** type = 4

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

方法二： 通过 SQL\_CALC\_FOUND\_ROWS 选项忽略 LIMIT 子句，然后通过FOUND\_ROWS()获得查询总数，那么sql改为：

1.  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获得总数

先：

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

然后：

1.  **select** FOUND\_ROWS();

最后：

1.  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

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

方法四：使用子查询

1.  **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][view plain]

1.  String count\_sql = dialect.getCountString(sql);

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

\[html\]  [view plain][]  [copy][view plain]

1.  /\*\*  
2.       \* 将sql转换为总记录数SQL  
3.       \* @param sql SQL语句  
4.       \* @return 总记录数的sql  
5.       \*/  
6.      public String getCountString(String sql)\{  
7.          return "select count(1) from (" + sql + ") tmp\_count";  
8.      \}

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

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

\[java\]  [view plain][]  [copy][view plain]

1.  /\*\* 
2.   \* 优化父类的getCountString性能 
3.   \*/  
4.  public String getCountString(String sql) \{  
5.      try \{  
6.          boolean queryCacheable = queryCachedFlag.get() != null && queryCachedFlag.get();  
7.          queryCachedFlag.remove();// 使用一次清理一次  
8.          return MySqlSmartCountUtil.getSmartCountSql(sql, queryCacheable);  
9.      \} catch (JSQLParserException e) \{  
10.         e.printStackTrace();  
11.     \} catch (Exception e) \{  
12.         e.printStackTrace();  
13.     \}  
14.     return "select count(\*) from (" + sql + ") tmp\_count";  
15. \}

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

\[java\]  [view plain][]  [copy][view plain]

1.  public class MySqlSmartCountUtil \{  
2.        
3.      // countSql缓存  
4.      private static HashMap<String, String> countSqlCache = new HashMap<String, String>();  
5.      private static HashMap<String, String> queryCacheableCountSqlCache = new HashMap<String, String>();  
6.    
7.      private static final List<SelectItem> countItem = new ArrayList<SelectItem>();  
8.      private static final List<SelectItem> sqlCachedCountItem = new ArrayList<SelectItem>();  
9.      static \{  
10.         countItem.add(new SelectExpressionItem(new Column("count(\*) as totalX")));  
11.         sqlCachedCountItem.add(new SelectExpressionItem(new Column("sql\_cache count(\*) as totalX")));  
12.     \}  
13.       
14.     private static void cacheSmartCountSql(String srcSql, String countSql, boolean queryCacheable) \{  
15.         if (queryCacheable)  
16.             queryCacheableCountSqlCache.put(srcSql, countSql);  
17.         else  
18.             countSqlCache.put(srcSql, countSql);  
19.     \}  
20.       
21.     private static List<SelectItem> getCountItem(boolean queryCacheable) \{  
22.         return queryCacheable ? sqlCachedCountItem : countItem;  
23.     \}  
24.       
25.     private static void smartCountPlainSelect(PlainSelect plainSelect, boolean queryCacheable) throws JSQLParserException\{  
26.           
27.         // 去掉orderby  
28.         OrderByUtil.removeOrderBy(plainSelect);  
29.   
30.         // 判断是否包含group by  
31.         if(GMUtil.isEmpty(plainSelect.getGroupByColumnReferences()))\{  
32.             plainSelect.setSelectItems(getCountItem(queryCacheable));  
33.         \} else \{  
34.             throw new JSQLParserException("不支持智能count的sql格式: GROUP BY ");  
35.         \}  
36.     \}  
37.       
38.     public static String getSmartCountSql(String srcSql, boolean queryCacheable) throws JSQLParserException \{  
39.           
40.         // 直接从缓存中取  
41.         if(!queryCacheable && countSqlCache.containsKey(srcSql))  
42.             return countSqlCache.get(srcSql);  
43.         if(queryCacheable && queryCacheableCountSqlCache.containsKey(srcSql))  
44.             return queryCacheableCountSqlCache.get(srcSql);  
45.   
46.         Statement stmt = CCJSqlParserUtil.parse(srcSql);  
47.         Select select = (Select) stmt;  
48.         SelectBody selectBody = select.getSelectBody();  
49.   
50.         if (selectBody instanceof PlainSelect) \{  
51.             PlainSelect plainSelect = ((PlainSelect) selectBody);  
52.             smartCountPlainSelect(plainSelect, queryCacheable);  
53.               
54.         \} else if (selectBody instanceof SetOperationList) \{  
55.             SetOperationList setOperationList = (SetOperationList) selectBody;  
56.             boolean isUnion = false;  
57.             for (SetOperation o : setOperationList.getOperations()) \{  
58.                 isUnion = (o.toString().contains("UNION"));  
59.                 if (!isUnion)  
60.                     break;  
61.             \}  
62.             // union all 语句的智能count  
63.             if(isUnion)\{  
64.                 for (PlainSelect ps : setOperationList.getPlainSelects()) \{  
65.                     smartCountPlainSelect(ps, false);// TODO 强制不允许缓存  
66.                 \}  
67.                 String resultSql = "select sum(totalX) from (" + select.toString() + ") as t ";  
68.                 cacheSmartCountSql(srcSql, resultSql, false);// TODO 强制不允许缓存  
69.                 return resultSql;  
70.             \} else \{  
71.                 throw new JSQLParserException("不支持智能count的sql格式");  
72.             \}  
73.         \} else \{  
74.             throw new JSQLParserException("不支持智能count的sql格式");  
75.         \}  
76.           
77.         cacheSmartCountSql(srcSql, select.toString(), queryCacheable);  
78.   
79.           
80.         return select.toString();  
81.     \}  
82.       
83.   
84. \}

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

附：关于mysql查询缓存，可以参考博文：http://orangeholic.iteye.com/blog/1701117

[view plain]: https://blog.csdn.net/yinyuehepijiu/article/details/50420569#