请尽量避免使用count(*)

在分页查询的时候，有的人总是习惯用select count(*)获得总的记录条数，实际上这不是一个高效的做法，因为，之前获得数据的时候已经查询过一次了，select count(*)相当于统一个语句查询了两次，对数据库的开销自然就大了，我们应当使用数据库自带的API，或者系统变量来完成这个工作，下面以mysql为例，加以说明：

 

比如说有段sql需要取出一张表的前10行，同时又需要取出符合条件的总数。这在某些翻页操作中很常见

sqlA: SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id> 100 LIMIT 10 ;

 

在上一查询之后，你只需要用FOUND_ROWS()就能获得查询总数，这个数目是抛掉了LIMIT之后的结果数:

sqlB: SELECT FOUND_ROWS( ) ;

 

其中第一个sql里面的SQL_CALC_FOUND_ROWS 不可省略，它表示需要取得结果数，也是后面使用FOUND_ROWS()函数的铺垫。

 

以下两点要注意：

 

当然以上两个语句需要在同一个transaction 里面完成（在JDBC里面就是同一个statment）。

 

sqlA和sqlB之间不能有其他的select语句，否则和可能结果总是返回1。

 

以下是mysql refman的翻译：

 

FOUND_ROWS()

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();

 

The second SELECT returns a number indicating how many rows the first SELECT would have returned had it been written without the LIMIT clause.

 

第二个SELECT将返回第一条SELECT如果没有LIMIT时返回的行数。

 

 

In the absence of the SQL_CALC_FOUND_ROWS option in the most recent SELECT statement, FOUND_ROWS() returns the number of rows in the result set returned by that statement.

 

如果在前一条语句中没有使用SQL_CALC_FOUND_ROWS选项， FOUND_ROWS()将返回前一条语句实际返回的行数。

 

 

The row count available through FOUND_ROWS() is transient and not intended to be available past the statement following the SELECT SQL_CALC_FOUND_ROWS statement. If you need to refer to the value later, save it:

 

FOUND_ROWS()得到的数字是临时的，执行下一条语句就会失效。如果想要这个数字，就要将它保存下来：

 

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM ... ;




mysql> SET @rows = FOUND_ROWS();

 

If you are using SELECT SQL_CALC_FOUND_ROWS , MySQL must calculate how many rows are in the full result set. However, this is faster than running the query again without LIMIT , because the result set need not be sent to the client.

 

如果使用 SELECT SQL_CALC_FOUND_ROWS ，MySQL必须计算所有结果集的行数。尽管这样，总比再执行一次不使用LIMIT的查询要快多了吧，因为那样结果集要返回客户端滴。（阿冬注：应该不单是没有将结果集返回的原因，还有原因可能是比如LIKE之类比较费劲的SQL不需要再去劳累一次。 ）（突然想起来MYSQL5.1是有中文文档的，在12.9.3节。）

 

 

SQL_CALC_FOUND_ROWS and FOUND_ROWS() can be useful in situations when you want to restrict the number of rows that a query returns, but also determine the number of rows in the full result set without running the query again. An example is a Web script that presents a paged display containing links to the pages that show other sections of a search result. Using FOUND_ROWS() allows you to determine how many other pages are needed for the rest of the result.

The use of SQL_CALC_FOUND_ROWS and FOUND_ROWS() is more complex for UNION statements than for simple SELECT statements, because LIMIT may occur at multiple places in a UNION . It may be applied to individual SELECT statements in the UNION , or global to the UNION result as a whole.

The intent of SQL_CALC_FOUND_ROWS for UNION is that it should return the row count that would be returned without a global LIMIT . The conditions for use of SQL_CALC_FOUND_ROWS with UNION are:

 

• The SQL_CALC_FOUND_ROWS keyword must appear in the first SELECT of the UNION .
• The value of FOUND_ROWS() is exact only if UNION ALL is used. If UNION without ALL is used, duplicate removal occurs and the value of FOUND_ROWS() is only approximate.
• If no LIMIT is present in the UNION , SQL_CALC_FOUND_ROWS is ignored and returns the number of rows in the temporary table that is created to process the UNION .

翻译引用自：http://www.ahdong.com/blog/?p=142