问题来自需求
因为我最近在升级Gorose-Pro框架,于是乎在编写Paginator模块(分页)的时候,遇到了一个问题
当count和groupby在一起使用的时候
原版的Total返回的数据是不正确的
Count+GroupBy的致命组合
gorose框架(原版)在使用Paginate输出全部数据的时候使用的是dba.Count()方法
正常情况下使用count方法是可以计算出数量的,但是在复杂条件下,例如Count+GroupBy同时使用的时候,读出来的数据是这样的
原版的Count方法好死不死的使用first来读取最终数据,这就导致生成的sql语句最后是以limit 1;结尾的
平时这样处理没什么问题,但是加入group后,如上图所示,着实很尴尬,如果使用limit1,那么最终paginate方法数据的total条数就会是3,明显不对
通常怎么解决Count+GroupBy的问题
一般通常使用如下方法解决这块问题
- Count法:使用select count(*) as count from (原来的SQL语句) as counts
- 先执行原来的语句,在执行这个语句从而取回之前语句的条数
- SQL_CALC_FOUND_ROWS法:select SQL_CALC_FOUND_ROWS (原来的语句去掉select部分)
- 接下来执行select FOUND_ROWS()取回条数
如上所见,如果使用第二个方法,对SQL的语句入侵极小,而且无需考虑select后的参数,因为它只是计算了下这条语句会返回的数据量,并没有其他的行为
调研
1.可行性
在功能的可行性上,通过测试,我确认如上两种方式都可以解决问题,因为count方法有创建临时表的行为,于是我考虑到性能问题,使用了:count(distinct group) as count的形式,让count值能保证打在复合索引上增强性能
2.性能
接下来就是性能问题了,于是……
这里使用explain语句来给大家看看,数据量在10000000条
mysql> explain SELECT SQL_CALC_FOUND_ROWS * FROM count_test WHERE b = 999 ORDER BY c LIMIT 5;
+----+-------------+------------+------+---------------+------+---------+-------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+-------+-------+-------------+
| 1 | SIMPLE | count_test | ref | bc | bc | 4 | const | 75327 | Using where |
+----+-------------+------------+------+---------------+------+---------+-------+-------+-------------+
1 row in set (0.00 sec)
mysql> explain SELECT SQL_NO_CACHE count(*) FROM count_test WHERE b = 666;
+----+-------------+------------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | count_test | ref | bc | bc | 4 | const | 5479 | Using index |
+----+-------------+------------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)
总结一下,一句话就是SQL_CALC_FOUND_ROWS把能扫的数据全扫了,于是乎性能就拉了
3.未来的支持与维护
一句话总结,从Mysql8.0.17版本后FOUND_ROWS()这方法将会被标记为Deprecated,所谓了避免未来可能出现突然不能用的情况,所以SQL_CALC_FOUND_ROWS这个方案可能确实值得商榷
Gorose-Pro数据库框架的方案选择
最终,我们选择count作为落地方案有以下几个原因
- Mysql未来将会对count(*)有更多的优化,性能更好
- 在没有缓存支持的情况下count的效率将更加稳定
- Count方法长远来看,支持及稳定性上有更强的优势
最后,GorosePro是什么?
GorsePro是基于Gorose改版的数据库框架,拥有如Thinkphp-Orm那样的操作体验,文档极其齐全,框架的目标是操作一定要简单,性能一定要好
与原作相比,GorosePro思考的更多的是在商业项目上的应用问题,例如大型项目开发为了降低耦合度而使用的NestedTransaction(事务嵌套),在大量统计方面使用的NestedCount(嵌套计数)
我们的目标是在大型项目中可以让GorosePro有更加稳定的发挥,从实际需求和案例中出发,不断更新完善Go语言框架
GorosePro数据库框架,对于BUG处理响应时间<12小时,所以你根本无需担心“无人维护”的问题,欢迎大家使用:https://github.com/tobycroft/gorose-pro