是否使用SQL_CALC_FOUND_ROWS

https://www.percona.com/blog/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

https://bugs.mysql.com/bug.php?id=18454

在优化SQL查询的过程中，我们经常遇到使用SQL_CALC_FOUND_ROWS的查询。很多人都认为使用SQL_CALC_FOUND_ROWS将会比使用2条SQL（1条查询数据，1条统计总数）更快。真实情况是这样的吗？

为了测试，我们首先创建一个简单的表：

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

并且插入测试数据：

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)");
}

首先，我们在WHERE子句中使用索引列B进行查询：

mysql> SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM count_test WHERE b = 555 ORDER BY c LIMIT 5;

使用SQL_CALC_FOUND_ROWS的结果是：如果没有缓冲，每个不同的b值将花费20-100秒，有缓存的话将花费2-5秒。这种差异可以通过查询所需的I/O来解释：MySQL将忽略LIMIT，而扫描所有10K的行。

我们来测试下分为2条SQL查询的结果：

mysql> SELECT SQL_NO_CACHE count(*) FROM count_test WHERE b = 666;

结果是：第一次运行为0.01-0.11秒，后面均为0.00-0.02秒。

现在，我们来看看COUNT需要花多久：

mysql> SELECT SQL_NO_CACHE count(*) FROM count_test WHERE b = 666;

结果是：0.00-0.04秒

我们可以看到，使用SELECT+COUNT（0.00-0.15秒）的执行时间远远小于比使用SQL_CALC_FOUND_ROWS（2-100秒）的执行时间。我们来看看EXPLAIN：

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

原因是：当计算结果的行数时，MySQL扫描了所有的结果，即使我们不需要（忽略LIMIT）。使用count(*)因为有索引，所以更快。

我们测试b有索引和没有索引（全表扫描）的情况，结果如下：

Full-scan:

使用SQL_CALC_FOUND_ROWS：7秒

使用2条SQL：7秒 + 7秒

Filesort:

使用SQL_CALC_FOUND_ROWS：1.8秒

使用2条SQL：1.8+0.05秒

结论就是：当查询语句的WHERE/ORDER子句有合适索引的时候，使用2条分开的SQL比使用SQL_CALC_FOUND_ROWS会快很多。