php - 在mysql中的一个查询中选择行以及总计数

Question

假设我有一个表 t 并且表 t 有 15000 个条目

假设查询

SELECT * FROM t WHERE t.nid <1000

返回 1000 行

但后来我只想要前 10 行，所以我做了 LIMIT

SELECT * FROM t WHERE t.nid <1000 LIMIT 10

是否可以构造一个查询，除了返回上面LIMIT子句的10行信息外，还返回满足WHERE子句中设置的条件的行的总计数，因此除了返回上面的 10 行，它也返回 1000，因为总共有 1000 行满足 WHERE 子句......并且都在一个查询中返回

Answer 1

首选方案

首先，found_rows() 函数不可移植(它是一个 MySQL 扩展)并且将被删除。正如用户@Zveddochka 指出的那样，它已在 MySQL 8.0.17 中被弃用。

但更重要的是，事实证明，如果您使用适当的索引，那么运行两个查询实际上会更快。 SQL_CALC_FOUND_ROWS 指令是通过会产生额外恢复成本的“虚拟扫描”实现的。当查询未被索引时，此成本将与 COUNT() 相同，因此运行两个查询将成本加倍 - 即，使用 SQL_CALC_FOUND_ROWS 将使事情运行速度提高 50%。

但是当查询被正确索引时会发生什么？ The guys at Percona checked it out 。事实证明，不仅 COUNT() 非常快，因为它只访问元数据和索引，而且没有 SQL_CALC_FOUND_ROWS 的查询速度更快，因为它不会产生任何附加费用; 两个查询合并的成本小于增强的单个查询的成本:

Results with SQL_CALC_FOUND_ROWS are following: for each b value ittakes 20-100 sec to execute uncached and 2-5 sec after warmup. Suchdifference could be explained by the I/O which required for this query– mysql accesses all 10k rows this query could produce without LIMITclause.

The results are following: it takes 0.01-0.11 sec to run this queryfirst time and 0.00-0.02 sec for all consecutive runs.

So, as we can see, total time for SELECT+COUNT (0.00-0.15 sec) is muchless than execution time for original query (2-100 sec). Let’s take alook at EXPLAINs...

那么，怎么办？

// Run two queries ensuring they satisfy exactly the same conditions

$field1 = "Field1, Field2, blah blah blah";
$field2 = "COUNT(*) AS rows";

$where  = "Field5 = 'X' AND Field6 = 'Y' AND blah blah";

$cntQuery = "SELECT {$field2} FROM {$joins} WHERE {$where}";
$rowQuery = "SELECT {$field1} FROM {$joins} WHERE {$where} LIMIT {$limit}";

现在第一个查询返回计数，第二个查询返回实际数据。

旧答案(仅对非索引表有用)

不要这样做。如果您发现答案的这一部分比上面的部分更适合您，这几乎可以肯定是您的设置中其他方面不是最佳的信号 - 很可能您没有正确使用索引，或者您需要更新您的MySQL 服务器，或运行数据库分析/优化以更新基数统计信息。

可以，但我认为这会成为性能 killer 。

您最好的选择是使用 SQL_CALC_FOUND_ROWS MySQL 扩展并发出第二个查询以使用 FOUND_ROWS() 恢复全部行数。

 SELECT SQL_CALC_FOUND_ROWS * FROM t WHERE t.nid <1000 LIMIT 10;
 SELECT FOUND_ROWS();

参见例如 http://www.arraystudio.com/as-workshop/mysql-get-total-number-of-rows-when-using-limit.html

或者您可以简单地运行不带 LIMIT 子句的完整查询，并且只检索前十行。然后你可以根据需要使用一个查询，也可以通过 mysql_num_rows() 获取行数。这并不理想，但对于大多数查询来说也不是那么灾难性的。

不过，如果最后执行此操作，请非常小心地关闭查询并释放其资源:我发现检索不到完整结果集并忘记释放 rs 句柄是 one outstanding cause of "metadata locking" .