mysql中关于SQL_CALC_FOUND_ROWS的使用与否

时间：2022-08-27 20:26:36

最近在代码中发现了这个mysql关键字 SQL_CALC_FOUND_ROWS

代码中是这么写的：

$dbProxy = self::getDBProxy();
$sql = "SELECT SQL_CALC_FOUND_ROWS * FROM rl_item_img_relation WHERE img_id=$id  limit 0,100 ";
$ret = $dbProxy->rs2array($sql);
$count = $dbProxy->rs2foundrows();

用处自然是无需在写一个count(img_id) ，省了一个方法，那么性能如何呢？

这里找到老外的一篇文章（http://www.percona.com/blog/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/），自己动手试下，眼见为实嘛！

mysql> CREATE TABLE `count_test` (
    ->   `a` int(10) NOT NULL auto_increment,
    ->   `b` int(10) NOT NULL,
    ->   `c` int(10) NOT NULL,
    ->   `d` varchar(32) NOT NULL,
    ->   PRIMARY KEY  (`a`),
    ->   KEY `bc` (`b`,`c`)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

 

<?php
mysql_connect("127.0.0.1", "root");
mysql_select_db("test");
for ($i = 0; $i < 10000000; $i++) {
    $b = $i % 1000;
    mysql_query("INSERT INTO count_test SET b=$b, c=ROUND(RAND()*10), d=MD5($i)");
}

我这里先是导入了大约124万，下面来查询下：

 

mysql> SELECT SQL_NO_CACHE * FROM count_test WHERE b = 555 ORDER BY c LIMIT 1;
+-------+-----+---+----------------------------------+
| a     | b   | c | d                                |
+-------+-----+---+----------------------------------+
| 18556 | 555 | 0 | 07895306ffe62e559d2cff903c91e66b |
+-------+-----+---+----------------------------------+
1 row in set (0.01 sec)

mysql> SELECT SQL_NO_CACHE count(*) FROM count_test WHERE b = 555 ORDER BY c LIMIT 1;
+----------+
| count(*) |
+----------+
|     1247 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT SQL_NO_CACHE   SQL_CALC_FOUND_ROWS  * FROM count_test WHERE b = 555 ORDER BY c LIMIT 1;
+-------+-----+---+----------------------------------+
| a     | b   | c | d                                |
+-------+-----+---+----------------------------------+
| 18556 | 555 | 0 | 07895306ffe62e559d2cff903c91e66b |
+-------+-----+---+----------------------------------+
1 row in set (0.01 sec)

 

两条分开的语句和合并一起的貌似没有什么差距。继续导入数据到千万级数据看下：

mysql> SELECT SQL_NO_CACHE   SQL_CALC_FOUND_ROWS  * FROM count_test WHERE b = 555 ORDER BY c LIMIT 1;
+-------+-----+---+----------------------------------+
| a     | b   | c | d                                |
+-------+-----+---+----------------------------------+
| 18556 | 555 | 0 | 07895306ffe62e559d2cff903c91e66b |
+-------+-----+---+----------------------------------+
1 row in set (45.14 sec)

mysql> SELECT SQL_NO_CACHE count(*) FROM count_test WHERE b = 555 ORDER BY c LIMIT 1;
+----------+
| count(*) |
+----------+
|    11247 |
+----------+
1 row in set (0.01 sec)

mysql> SELECT SQL_NO_CACHE * FROM count_test WHERE b = 555 ORDER BY c LIMIT 1;
+-------+-----+---+----------------------------------+
| a     | b   | c | d                                |
+-------+-----+---+----------------------------------+
| 18556 | 555 | 0 | 07895306ffe62e559d2cff903c91e66b |
+-------+-----+---+----------------------------------+
1 row in set (0.00 sec)

差距出来了，为什么？

 

看看sql语句的explain

mysql> explain SELECT SQL_NO_CACHE   SQL_CALC_FOUND_ROWS  * FROM count_test WHERE b = 555 ORDER BY c LIMIT 1;
+----+-------------+------------+------+---------------+------+---------+-------+-------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref   | rows  | Extra       |
+----+-------------+------------+------+---------------+------+---------+-------+-------+-------------+
|  1 | SIMPLE      | count_test | ref  | bc            | bc   | 4       | const | 11431 | Using where |
+----+-------------+------------+------+---------------+------+---------+-------+-------+-------------+
1 row in set (0.00 sec)

mysql> explain   SELECT SQL_NO_CACHE count(*) FROM count_test WHERE b = 555 ORDER BY c LIMIT 1;
+----+-------------+------------+------+---------------+------+---------+-------+-------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref   | rows  | Extra       |
+----+-------------+------------+------+---------------+------+---------+-------+-------+-------------+
|  1 | SIMPLE      | count_test | ref  | bc            | bc   | 4       | const | 11431 | Using index |
+----+-------------+------------+------+---------------+------+---------+-------+-------+-------------+
1 row in set (0.00 sec)

mysql> explain SELECT SQL_NO_CACHE * FROM count_test WHERE b = 555 ORDER BY c LIMIT 1;
+----+-------------+------------+------+---------------+------+---------+-------+-------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref   | rows  | Extra       |
+----+-------------+------------+------+---------------+------+---------+-------+-------+-------------+
|  1 | SIMPLE      | count_test | ref  | bc            | bc   | 4       | const | 11431 | Using where |
+----+-------------+------------+------+---------------+------+---------+-------+-------+-------------+
1 row in set (0.00 sec)

 

原博只是比对了后两条语句，没有进行第一条语句的比较，直接得出了结论，但是如果加上第一条语句的话，还是无法解释上述的查询的时间差距，原博得出这个结论是片面的。

根据mysql 官方手册（http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_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.

   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() enables you to determine how many other pages are needed for the rest of the 
result.

按照手册说法，SQL_CALC_FOUND_ROWS 和 FOUND_ROWS()是被推荐使用的，至少比再查询一次快。就像原博评论里说的，我们没有理由不相信官方手册啊！

标签：

相关文章