没有双重查询的 MySQL 分页？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/818567/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL pagination without double-querying?
提问by atp
I was wondering if there was a way to get the number of results from a MySQL query, and at the same time limit the results.
我想知道是否有办法从 MySQL 查询中获取结果数量，同时限制结果。
The way pagination works (as I understand it), first I do something like
分页的工作方式（据我所知），首先我做类似的事情
query = SELECT COUNT(*) FROM `table` WHERE `some_condition`
After I get the num_rows(query), I have the number of results. But then to actually limit my results, I have to do a second query like:
在我得到 num_rows(query) 之后，我得到了结果的数量。但是为了实际限制我的结果，我必须执行第二个查询，例如：
query2 = SELECT COUNT(*) FROM `table` WHERE `some_condition` LIMIT 0, 10
My question: Is there anyway to both retrieve the total number of results that would be given, AND limit the results returned in a single query? Or any more efficient way of doing this. Thanks!
我的问题：无论如何都可以检索将给出的结果总数，并限制在单个查询中返回的结果？或者任何更有效的方式来做到这一点。谢谢！
采纳答案by staticsan
No, that's how many applications that want to paginate have to do it. It's reliable and bullet-proof, albeit it makes the query twice. But you can cache the count for a few seconds and that will help a lot.
不，这就是需要分页的应用程序的数量。它可靠且防弹，尽管它进行了两次查询。但是您可以将计数缓存几秒钟，这将有很大帮助。
The other way is to use SQL_CALC_FOUND_ROWSclause and then call SELECT FOUND_ROWS(). apart from the fact you have to put the FOUND_ROWS()call afterwards, there is a problem with this: There is a bug in MySQLthat this tickles that affects ORDER BYqueries making it much slower on large tables than the naive approach of two queries.
另一种方法是使用SQL_CALC_FOUND_ROWS子句然后调用SELECT FOUND_ROWS(). 除了您必须在FOUND_ROWS()之后调用这一事实之外，还有一个问题：MySQL中有一个错误，它会影响ORDER BY查询，使其在大型表上的速度比两个查询的幼稚方法慢得多。
回答by Derrick
I almost never do two queries.
我几乎从不做两个查询。
Simply return one more row than is needed, only display 10 on the page, and if there are more than are displayed, display a "Next" button.
只需返回比需要多的行，页面上只显示10行，如果显示多于，则显示“下一步”按钮。
SELECT x, y, z FROM `table` WHERE `some_condition` LIMIT 0, 11
// iterate through and display 10 rows.
// if there were 11 rows, display a "Next" button.
Your query should return in an order of most relevant first. Chances are, most people aren't going to care about going to page 236 out of 412.
您的查询应按最相关的顺序返回。很有可能，大多数人不会关心去 412 页中的 236 页。
When you do a google search, and your results aren't on the first page, you likely go to page two, not nine.
当您进行谷歌搜索时，您的结果不在第一页上，您可能会转到第二页，而不是第九页。
回答by thomasrutter
Another approach to avoiding double-querying is to fetch all the rows for the current page using a LIMIT clause first, then only do a second COUNT(*) query if the maximum number of rows were retrieved.
避免双重查询的另一种方法是首先使用 LIMIT 子句获取当前页面的所有行，如果检索到最大行数，则仅执行第二次 COUNT(*) 查询。
In many applications, the most likely outcome will be that all of the results fit on one page, and having to do pagination is the exception rather than the norm. In these cases, the first query will not retrieve the maximum number of results.
在许多应用程序中，最有可能的结果是所有结果都适合一页，并且必须进行分页是例外而不是常态。在这些情况下，第一个查询将不会检索到最大数量的结果。
For example, answers on a stackoverflow question rarely spill onto a second page. Comments on an answer rarely spill over the limit of 5 or so required to show them all.
例如，stackoverflow 问题的答案很少会溢出到第二页。对答案的评论很少会超过显示所有评论所需的 5 个左右的限制。
So in these applications you can simply just do a query with a LIMIT first, and then as long as that limit is not reached, you know exactly how many rows there are without the need to do a second COUNT(*) query - which should cover the majority of situations.
因此，在这些应用程序中，您可以简单地先使用 LIMIT 进行查询，然后只要未达到该限制，您就可以确切地知道有多少行，而无需进行第二次 COUNT(*) 查询 - 这应该涵盖大部分情况。
回答by thomasrutter
In most situations it is much faster and less resource intensive to do it in two separate queries than to do it in one, even though that seems counter-intuitive.
在大多数情况下，在两个单独的查询中执行此操作比在一个查询中执行要快得多且资源占用更少，即使这似乎违反直觉。
If you use SQL_CALC_FOUND_ROWS, then for large tables it makes your query much slower, significantly slower even than executing two queries, the first with a COUNT(*) and the second with a LIMIT. The reason for this is that SQL_CALC_FOUND_ROWS causes the LIMIT clause to be applied afterfetching the rows instead of before, so it fetches the entire row for all possible results before applying the limits. This can't be satisfied by an index because it actually fetches the data.
如果您使用 SQL_CALC_FOUND_ROWS，那么对于大型表，它会使您的查询慢得多，甚至比执行两个查询慢得多，第一个查询是 COUNT(*)，第二个是 LIMIT。这样做的原因是 SQL_CALC_FOUND_ROWS 导致在获取行之后而不是之前应用 LIMIT 子句，因此它在应用限制之前为所有可能的结果获取整行。索引无法满足这一点，因为它实际上是在获取数据。
If you take the two queries approach, the first one only fetching COUNT(*) and not actually fetching and actual data, this can be satisfied much more quickly because it can usually use indexes and doesn't have to fetch the actual row data for every row it looks at. Then, the second query only needs to look at the first $offset+$limit rows and then return.
如果您采用两种查询方法，第一个只获取 COUNT(*) 而不是实际获取和实际数据，这可以更快地满足，因为它通常可以使用索引并且不必为实际获取行数据它查看的每一行。然后，第二个查询只需要查看前 $offset+$limit 行然后返回。
This post from the MySQL performance blog explains this further:
MySQL 性能博客中的这篇文章进一步解释了这一点：
http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/
http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/
For more information on optimising pagination, check this postand this post.
回答by Kama
My answer may be late, but you can skip the second query (with the limit) and just filter the info through your back end script. In PHP for instance, you could do something like:
我的回答可能晚了，但您可以跳过第二个查询（有限制），只需通过后端脚本过滤信息。例如，在 PHP 中，您可以执行以下操作：
if($queryResult > 0) {
$counter = 0;
foreach($queryResult AS $result) {
if($counter >= $startAt AND $counter < $numOfRows) {
//do what you want here
}
$counter++;
}
}
But of course, when you have thousands of records to consider, it becomes inefficient very fast. Pre-calculated count maybe a good idea to look into.
但是当然，当您要考虑数千条记录时，它会很快变得效率低下。预先计算的计数可能是一个好主意。
Here's a good read on the subject: http://www.percona.com/ppc2009/PPC2009_mysql_pagination.pdf
这是有关该主题的好读物：http: //www.percona.com/ppc2009/PPC2009_mysql_pagination.pdf
回答by Cris McLaughlin
query = SELECT col, col2, (SELECT COUNT(*) FROM `table`) AS total FROM `table` WHERE `some_condition` LIMIT 0, 10
回答by Philip Rollins
You can reuse most of the query in a subquery and set it to an identifier. For example a movie query that finds movies containing the letter 's' ordering by runtime would look like this on my site.
您可以在子查询中重用大部分查询并将其设置为标识符。例如，查找包含按运行时排序的字母 's' 的电影的电影查询在我的网站上看起来像这样。
SELECT Movie.*, (
SELECT Count(1) FROM Movie
INNER JOIN MovieGenre
ON MovieGenre.MovieId = Movie.Id AND MovieGenre.GenreId = 11
WHERE Title LIKE '%s%'
) AS Count FROM Movie
INNER JOIN MovieGenre
ON MovieGenre.MovieId = Movie.Id AND MovieGenre.GenreId = 11
WHERE Title LIKE '%s%' LIMIT 8;
Do note that I'm not a database expert, and am hoping someone will be able to optimize that a bit better. As it stands running it straight from the SQL command line interface they both take ~0.02 seconds on my laptop.
请注意，我不是数据库专家，我希望有人能够更好地优化它。由于它直接从 SQL 命令行界面运行，它们在我的笔记本电脑上都需要大约 0.02 秒。
回答by John
SELECT *
FROM table
WHERE some_condition
ORDER BY RAND()
LIMIT 0, 10
