哪个更快？SELECT SQL_CALC_FOUND_ROWS FROM `table`，还是SELECT COUNT(*)？

Question

当您限制SQL查询返回的行数时，通常用于分页，有两种方法可以确定记录的总数:

方法1

在原始的SELECT语句中包含SQL_CALC_FOUND_ROWS选项，然后通过运行SELECT FOUND_ROWS()来获取总行数：

SELECT SQL_CALC_FOUND_ROWS * FROM table WHERE id > 100 LIMIT 10;
SELECT FOUND_ROWS();  

方法2

正常运行查询，然后通过运行SELECT COUNT(*)获取总行数。

SELECT * FROM table WHERE id > 100 LIMIT 10;
SELECT COUNT(*) FROM table WHERE id > 100;  

哪种方法是最好/最快的？

- Jrgns

Answer 1

这要看情况。请参考MySQL性能博客上的文章：使用SQL_CALC_FOUND_ROWS还是不用SQL_CALC_FOUND_ROWS？

简单概括一下：Peter说这要看你的索引和其他因素。该文章中的许多评论似乎表明，使用 SQL_CALC_FOUND_ROWS 比运行两个查询几乎总是要慢 - 有时甚至慢10倍。

- Nathan

---

我可以确认这一点——我刚刚在一个有 168,000 行的数据库上更新了一个包含 4 个连接的查询。仅选择前 100 行并使用 SQL_CALC_FOUND_ROWS 花费了超过 20 秒；而使用单独的 COUNT(*) 查询不到 5 秒钟（对于计数和结果查询均是如此）。 - Sam Dufel

非常有趣的发现。由于MySQL文档明确建议使用SQL_CALC_FOUND_ROWS可以更快，我想知道在什么情况下（如果有的话）它实际上确实更快！ - svidgen

虽然这是一个老话题，但还是对此感兴趣的人！我刚完成了对INNODB的检查，从10次检查中可以看出，它是26（2个查询）对9.2（1个查询）。查询语句如下：SELECT SQL_CALC_FOUND_ROWS tblA.*, tblB.id AS 'b_id', tblB.city AS 'b_city', tblC.id AS 'c_id', tblC.type AS 'c_type', tblD.id AS 'd_id', tblD.extype AS 'd_extype', tblY.id AS 'y_id', tblY.ydt AS y_ydt FROM tblA, tblB, tblC, tblD, tblY WHERE tblA.b = tblC.id AND tblA.c = tblB.id AND tblA.d = tblD.id AND tblA.y = tblY.id - Al Po

我刚刚运行了这个实验，使用SQLC_CALC_FOUND_ROWS比使用两个查询更快。现在我的主表只有65000条记录，还有几百条记录的两个连接表，但是无论是否使用SQLC_CALC_FOUND_ROWS，主查询都只需要0.18秒，而当我运行第二个包含COUNT(id)的查询时，它单独要花费0.25秒。 - transilvlad

除了可能的性能问题之外，还要考虑到FOUND_ROWS()在MySQL 8.0.17中已经被弃用。另请参见@madhur-bhaiya的答案。 - arueckauer

Answer 2

MySQL 从8.0.17版本开始逐步弃用 SQL_CALC_FOUND_ROWS 功能。

因此，最好始终考虑使用 LIMIT 执行查询，然后再使用不带 LIMIT 的 COUNT(*) 执行第二个查询，以确定是否存在其他行。

来自文档：

从 MySQL 8.0.17 开始，SQL_CALC_FOUND_ROWS 查询修饰符和相应的 FOUND_ROWS() 函数已被弃用，并将在未来的 MySQL 版本中删除。

COUNT(*) 受到某些优化。SQL_CALC_FOUND_ROWS 导致一些优化被禁用。

请改用这些查询：

SELECT * FROM tbl_name WHERE id > 100 LIMIT 10;
SELECT COUNT(*) WHERE id > 100;

此外，SQL_CALC_FOUND_ROWS通常存在更多问题，如MySQL WL# 12615所解释的那样：

SQL_CALC_FOUND_ROWS存在许多问题。首先，它很慢。 通常情况下，对于相同的查询，使用LIMIT然后单独选择SELECT COUNT()会更便宜，因为COUNT()可以利用不能在搜索整个结果集时进行的优化（例如，对于COUNT(*)，可以跳过filesort，而对于CALC_FOUND_ROWS，我们必须禁用一些filesort优化以确保正确的结果）。

更重要的是，在许多情况下，它具有非常不清楚的语义。特别是，当一个查询具有多个查询块（例如，使用UNION）时，根本无法在生成有效查询的同时计算出“将会被”的行数。随着迭代执行器朝这些类型的查询前进，试图保留相同的语义确实很困难。此外，如果查询中有多个LIMIT（例如，对于派生表），则不一定清楚SQL_CALC_FOUND_ROWS应参考哪一个。因此，这些非平凡的查询在迭代器执行器中必然会与它们之前的语义不同。

最后，大多数SQL_CALC_FOUND_ROWS似乎有用的用例应该通过限制/偏移量之外的其他机制来解决。例如，电话簿应该按字母进行分页（在UX和索引使用方面都是如此），而不是按记录编号进行分页。讨论越来越多地被日期排序的无限滚动替代（再次允许索引使用），而不是按帖子编号分页等。

- Madhur Bhaiya

---

如何将这两个SELECT语句作为一个原子操作执行？ 如果在SELECT COUNT(*)查询之前有人插入一行，会怎样？谢谢。 - Dom

@Dom 如果你使用MySQL8+，你可以使用窗口函数在单个查询中运行这两个查询；但是这不是最优解决方案，因为索引将无法正确使用。另一个选择是用 LOCK TABLES <tablename> 和 UNLOCK TABLES 包围这两个查询。第三个选项（我认为最好的）是重新考虑分页。请阅读：https://mariadb.com/kb/en/library/pagination-optimization/ - Madhur Bhaiya

与MySQL相比，MariaDB尚未弃用此功能。 - Johnson_145

Answer 3

在选择“最佳”方法时，与速度相比，更重要的考虑因素可能是代码的可维护性和正确性。如果是这样的话，SQL_CALC_FOUND_ROWS 更可取，因为您只需要维护一个查询。使用单个查询完全排除了主查询和计数查询之间存在微妙差异的可能性，这可能导致 COUNT 不准确。

- Jeff Clemens

---

这取决于你的设置。如果你正在使用某种ORM或查询构建器，那么很容易为两个查询使用相同的where条件，交换选择字段为计数，并取消限制。你不应该两次编写相同的条件。 - mpen

我想指出的是，与使用一个专有的MySQL特性相比，我更喜欢使用两个简单、相当标准、易于理解的SQL查询来维护代码。需要注意的是，这个特性在新版的MySQL中已经被废弃了。 - thomasrutter

Answer 4

根据以下文章：https://www.percona.com/blog/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/，如果你的where子句上有一个索引（在您的情况下是id），那么最好不要使用SQL_CALC_FOUND_ROWS而使用两个查询，但如果您在where子句中没有索引（在您的情况下是id），那么使用SQL_CALC_FOUND_ROWS更有效。

- patapouf_ai

Answer 5

个人认为，查询速度慢的原因可能是由于数据库中存在大量数据。

SELECT * FROM count_test WHERE b = 666 ORDER BY c LIMIT 5;
SELECT count(*) FROM count_test WHERE b = 666;

使用SQL_CALC_FOUND_ROWS比直接计算要慢。

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

必须视为一个特殊情况。

事实上，它取决于WHERE子句的选择性与等效于ORDER+LIMIT的隐式选择性的比较。

正如Arvids在评论中所说(http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/#comment-1174394)，EXPLAIN是否使用临时表，应该是知道SCFR是否更快的好基础。

但是，正如我所添加的(http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/#comment-8166482)，结果确实非常依赖于具体情况。对于特定的分页器，你可能会得出“对于前三页，使用两个查询；对于后续页面，使用SCFR”的结论！

- Pierre-Olivier Vares

Answer 6

删除一些不必要的SQL语句然后使用COUNT(*)会比使用SQL_CALC_FOUND_ROWS更快。例如：

SELECT Person.Id, Person.Name, Job.Description, Card.Number
FROM Person
JOIN Job ON Job.Id = Person.Job_Id
LEFT JOIN Card ON Card.Person_Id = Person.Id
WHERE Job.Name = 'WEB Developer'
ORDER BY Person.Name

然后不计算不必要的部分：

SELECT COUNT(*)
FROM Person
JOIN Job ON Job.Id = Person.Job_Id
WHERE Job.Name = 'WEB Developer'

- Jessé Catrinck

Answer 7

您还可以选择其他基准测试选项：

1.) 窗口函数可以直接返回实际大小（在MariaDB中进行了测试）：

SELECT 
  `mytable`.*,
  COUNT(*) OVER() AS `total_count`
FROM `mytable`
ORDER BY `mycol`
LIMIT 10, 20

2.) 大多数情况下，用户并不需要知道表格的精确尺寸，一个近似值通常已经足够。

SELECT `TABLE_ROWS` AS `rows_approx`
FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE `TABLE_SCHEMA` = DATABASE()
  AND `TABLE_TYPE` = "BASE TABLE"
  AND `TABLE_NAME` = ?

- Code4R7

Answer 8

一个关于拥有 2,000,000 行数据表格和以下查询的简单示例：

select fieldname 
from table_add 
where 
descryption_per like '%marihuana%' 
or addiction_per like '%alkohol%';

每次查询都是全表扫描，所以需要时间 x 2。我的意思是 "select count(*) from .....

- Ja Loc