mysql中calculate_mysql中关于SQL_CALC_FOUND_ROWS的使用与否
最近在代码中发现了这个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) ，省了一个方法，那么性能如何呢？
mysql> CREATE TABLE`count_test` (-> `a` int(10) NOT NULLauto_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)
}
我这里先是导入了大约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.01sec)
mysql> SELECT SQL_NO_CACHE count(*) FROM count_test WHERE b = 555 ORDER BY c LIMIT 1;+----------+
| count(*) |
+----------+
| 1247 |
+----------+
1 row in set (0.00sec)
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.14sec)
mysql> SELECT SQL_NO_CACHE count(*) FROM count_test WHERE b = 555 ORDER BY c LIMIT 1;+----------+
| count(*) |
+----------+
| 11247 |
+----------+
1 row in set (0.01sec)
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.00sec)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.00sec)
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)
原博只是比对了后两条语句，没有进行第一条语句的比较，直接得出了结论，但是如果加上第一条语句的话，还是无法解释上述的查询的时间差距，原博得出这个结论是片面的。
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 senttothe client.
SQL_CALC_FOUND_ROWSand FOUND_ROWS() can be useful in situations when you want to restrict the number of rowsthat 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 ofa search result. Using FOUND_ROWS() enables you to determine how many other pages are needed for the rest of theresult.
按照手册说法，SQL_CALC_FOUND_ROWS 和 FOUND_ROWS()是被推荐使用的，至少比再查询一次快。就像原博评论里说的，我们没有理由不相信官方手册啊！