数据库访问时,经常需要分页获取并获得总数,有两种方式:

1.  使用两条sql句子:

select * from table where … order by … limit …

select count(1) as cnt from table where …

2. 使用一条sql句子:

select SQL_CALC_FOUND_ROWS * from table where …

3. 不返回总数,而是是否还有下一页

从代码的整洁度而言,显然后者较优,但是性能角度考虑的话,则需要视情况而定,以下先分析方法1和2。

针对当前的一条sql句子做了测试,profile输出如下:

| 13 | 0.00911600 | select SQL_CALC_FOUND_ROWS SQL_NO_CACHE * FROM “table` AS `a` WHERE (a.appid = ’57’) AND (a.parentid != 0) ORDER BY `authtag` desc LIMIT 4 |
| 14 | 0.00234600 | select SQL_NO_CACHE * FROM `table` AS `a` WHERE (a.appid = ’57’) AND (a.parentid != 0) ORDER BY `authtag` desc LIMIT 4 |
| 15 | 0.00262500 | select SQL_NO_CACHE count(1) FROM `table` AS `a` WHERE (a.appid = ’57’) AND (a.parentid != 0) |

具体如下:

mysql> show profile for query 13;
+——————–+———-+
| Status | Duration |
+——————–+———-+
| (initialization) | 0.000072 |
| Opening tables | 0.000013 |
| System lock | 0.000005 |
| Table lock | 0.000009 |
| init | 0.000033 |
| optimizing | 0.000014 |
| statistics | 0.000018 |
| preparing | 0.000016 |
| executing | 0.000003 |
| Sorting result | 0.000005 |
| Sending data | 0.008898 |
| end | 0.00001 |
| query end | 0.000002 |
| freeing items | 0.00001 |
| closing tables | 0.000005 |
| logging slow query | 0.000003 |
+——————–+———-+
16 rows in set (0.00 sec)

mysql> show profile for query 14;
+——————–+———–+
| Status | Duration |
+——————–+———–+
| (initialization) | 0.0000749 |
| Opening tables | 0.000013 |
| System lock | 0.000004 |
| Table lock | 0.00001 |
| init | 0.000033 |
| optimizing | 0.000015 |
| statistics | 0.000017 |
| preparing | 0.000016 |
| executing | 0.000004 |
| Sorting result | 0.000005 |
| Sending data | 0.002134 |
| end | 0.000004 |
| query end | 0.000003 |
| freeing items | 0.000006 |
| closing tables | 0.000005 |
| logging slow query | 0.000002 |
+——————–+———–+
16 rows in set (0.00 sec)

mysql> show profile for query 15;
+——————–+———-+
| Status | Duration |
+——————–+———-+
| (initialization) | 0.000073 |
| Opening tables | 0.000014 |
| System lock | 0.000005 |
| Table lock | 0.000009 |
| init | 0.000028 |
| optimizing | 0.000016 |
| statistics | 0.000017 |
| preparing | 0.000015 |
| executing | 0.000006 |
| Sending data | 0.00242 |
| end | 0.000006 |
| query end | 0.000003 |
| freeing items | 0.000006 |
| closing tables | 0.000005 |
| logging slow query | 0.000002 |
+——————–+———-+
15 rows in set (0.00 sec)

原因呢?当使用SQL_CALC_FOUND_ROWS时,会获取全部符合标准的条目(即使该条目不在limit的范围内),这样就使两者的Sending data时间差别很大。

可以参考:

http://hi.baidu.com/thinkinginlamp/blog/item/df85b31c93cd148686d6b64f.html

http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

在分析SQL_CALC_FOUND_ROWS的时候,可以看到count操作是比较费时的,那有没有其他方法了呢?获取总数是为了分页,但是总页数真的那么重要吗?如果改成“下一页”的思路呢?

在淘宝开放平台的api里, 对于某些对于总数并不是很敏感且逻辑较为复杂的操作,可以使用use_has_next参数:

 use_has_next:是否启用has_next的分页方式,如果指定true,则返回的结果中不包含总记录数,但是会新增一个是否存在下一页的的字段,通过此种方式获取增量交易,效率在原有的基础上有80%的提升

当指定该参数时,不返回总条数,但是返回是否还有下一页的数据,通过这种方式可以获得性能方面较大的提升!那么怎样方便的实现呢?还是以上面的sql为例,原始sql为:

select SQL_NO_CACHE * FROM `table` AS `a` WHERE (a.appid = ’57’) AND (a.parentid != 0) ORDER BY `authtag` desc LIMIT 4 offset 0

这时,计算offset的方法不变,还是(page-1)*per_page,但是limit时,我们多取一个,即limit=per_page + 1。如果获取到的条目=per_page+1,则说明有下一页,否则就没有下一页了!

Leave a Reply