mysql SQL_CALC_FOUND_ROWS 特性: 一条 sql 语句同时查出总数及分页结果

展示分页列表是一个常见的开发需求, 需要查询出总数及分页数据.

传统分页查询做法

传统上, 这个一般是通过两条 sql 去实现. 先是查询总数, 比如这样:

select count(*) from programmer where age >= 35;

然后再查分页结果:

select * from programmer where age >= 35 limit 0, 10;

如果是简单的查询还好, 但对于一些复杂的涉及很多条件的查询, 往往需要重复那些条件.

注: 在 mybatis 中, 你可以把公共的条件抽取出来做成一个可复用模块, 不过这样一来结构就相对复杂了, 也不是那么直观.

那么, 是否有方式可以避免上述麻烦, 一条语句就可以查出总数及分页结果呢? 那就要用到 mysql 里的 SQL_CALC_FOUND_ROWS 特性了.

SQL_CALC_FOUND_ROWS 特性

SQL_CALC_FOUND_ROWS 特性, 简单的讲, 就是可以让你在 limit 的分页查询中, 也能一起查到总数, 对于上述的代码示例, 具体是这样去操作的:

select SQL_CALC_FOUND_ROWS * from programmer where age >= 35 limit 0, 10;

基本上, 就还是前述的分页查询, 但 select 后面增加了 SQL_CALC_FOUND_ROWS, 之后, 要取得这个总数, 再紧接着发一条这样的查询即可:

SELECT FOUND_ROWS();

说到这里, 有人可能会说, 那还不是得发两条? 不过这里的第二个查询仅仅是取出第一次那条查询的总数而已, 只是一个取结果的操作而已, 你取不取, 结果都已经在那里了, 这个取的操作不会再去查数据库, 是一个很快的操作.

而且我们可以看得, 这是一种特殊形式, 不再包含前述那些条件, 甚至连表名这些都省略了. 所以这里其实也有一个需要特别注意的事项: SELECT FOUND_ROWS() 需要紧接着前述分页查询去执行, 不然它取得的结果可能就不对了.

事实上, 不论前面的查询是否有 SQL_CALC_FOUND_ROWS, 你都可以执行 SELECT FOUND_ROWS(), 总之它就是返回它所紧跟的前一个查询的结果数而已.

limit offset 偏移量很大时的推荐做法

最后, 如果 limit 的偏移量很大时, 如果你直接查 select * , 你可能会发现 mysql 的查询还是非常慢, 比如这样的一个大偏移量的查询:

select SQL_CALC_FOUND_ROWS * from programmer where age >= 35 limit 10000, 3;

注: 这个跟 SQL_CALC_FOUND_ROWS 特性无关, 无论你是否启用这一特性, 大的偏移量下它都可能很慢, 属于 mysql limit 实现本身的问题.

这时比较好的做法是先查出 id, 像这样:

select SQL_CALC_FOUND_ROWS id from programmer where age >= 35 limit 10000, 3;

只查 id 的话, 通过索引就能拿到 id 字段的值, 这时即便偏移量很大也不是问题了. 之后还是通过 select FOUND_ROWS() 拿到总数, 而最终的分页结果再发一条 in (ids) 的查询即可得到:

select * from programmer where id in (3, 5, 6)

这里具体的 id 值就是前述 limit 查询查到的结果.

因为分页一般展示的数据都是有限的, 这里用 in 的方式来查询也不会有太大问题.

关于利用 mysql SQL_CALC_FOUND_ROWS 特性, 在一条 sql 语句里同时查出总数及分页结果的介绍就到这里.