MySQL-如何不用count获取到查询记录？

Question

一般分页的时候，我们需要以下两条sql语句
//取得全部记录数
select count(*) from tb;
//取得当前页的数据
select * from tb limit 1,10;
其中limit offset优化见
@如何优化LIMIT,OFFSET进行的分页？
我想问的是，上面两条sql语句能否优化，能否不用count统计出所有记录？

Answer 1

可以在覆盖索引上进行偏远，而不是在全局上进行偏移。可以将从覆盖索引上提取出来的数据和全行数据进行连接，然后取得需要的列。
select fid,desc from film order by title limit 5000,5
如果表非常大，可以写成这样，觉得会检索更少的数据，效率会更高
select fid,desc from film inner join (select fid from film order by title limit 5000,5) as lim using(fid)

Answer 2

mysql 有 FOUND_ROWS() 函数，手册中说明如下：

For a SELECT with a LIMIT clause, the number of rows that would be returned were there no LIMIT clause
A SELECT statement may include a LIMIT clause to restrict the number of rows the server returns to the client. In some cases, it is desirable to know how many rows the statement would have returned without the LIMIT, but without running the statement again. To obtain this row count, include a SQL_CALC_FOUND_ROWS option in the SELECT statement, and then invoke FOUND_ROWS() afterward。

翻译如下：

一条 SELECT语句可能包括一个 LIMIT 子句，用来限制服务器返回客户端的行数。在有些情况下，需要不用再次运行该语句而得知在没有LIMIT 时到底该语句返回了多少行。为了知道这个行数, 包括在SELECT 语句中选择 SQL_CALC_FOUND_ROWS ，随后调用 FOUND_ROWS()

例子：

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name

-> WHERE id > 100 LIMIT 10;

mysql> SELECT FOUND_ROWS();

我自己的测试结果：

使用 FOUND_ROWS()函数获得总数：