# 是否使用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
```

并且插入测试数据：

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

首先，我们在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:**  <br>

使用SQL\_CALC\_FOUND\_ROWS：1.8秒

使用2条SQL：1.8+0.05秒

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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://john-mao.gitbook.io/my-handbook/mysql/shi-fou-shi-yong-sql-calc-found-rows.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.