Mysql SQL_CALC_FOUND_ROWS 和分页
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4765901/
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 SQL_CALC_FOUND_ROWS and pagination
提问by John
So I have a table that has a little over 5 million rows. When I use SQL_CALC_FOUND_ROWS the query just hangs forever. When I take it out the query executes within a second withe LIMIT ,25. My question is for pagination reasons is there an alternative to getting the number of total rows?
所以我有一个有超过 500 万行的表。当我使用 SQL_CALC_FOUND_ROWS 时，查询会永远挂起。当我取出它时，查询会在 LIMIT 25 秒内执行。我的问题是出于分页的原因，是否有获取总行数的替代方法？
采纳答案by Marc B
SQL_CALC_FOUND_ROWS forces MySQL to scan for ALL matching rows, even if they'd never get fetched. Internally it amounts to the same query being executed without the LIMIT clause.
SQL_CALC_FOUND_ROWS 强制 MySQL 扫描所有匹配的行，即使它们永远不会被获取。在内部，它相当于在没有 LIMIT 子句的情况下执行相同的查询。
If the filtering you're doing via WHERE isn't too crazy, you could calculate and cache various types of filters to save the full-scan load imposed by calc_found_rows. Basically run a "select count(*) from ... where ...." for most possible where clauses.
如果您通过 WHERE 进行的过滤不是太疯狂，您可以计算和缓存各种类型的过滤器，以节省 calc_found_rows 施加的全扫描负载。对于大多数可能的 where 子句，基本上运行“select count(*) from ... where ....”。
Otherwise, you could go Google-style and just spit out some page numbers that occasionally have no relation whatsoever with reality (You know, you see "Goooooooooooogle", get to page 3, and suddenly run out of results).
否则，您可以使用 Google 风格，只是吐出一些偶尔与现实无关的页码（您知道，您看到“Goooooooooooogle”，进入第 3 页，结果突然用完）。
回答by Surat
Detailed talk about implementing Google-style pagination using MySQL
回答by Tadas Sasnauskas
You should choose between COUNT(*) AND SQL_CALC_FOUND_ROWS depending on situation. If your query search criteria uses rows that are in index - use COUNT(*). In this case Mysql will "read" from indexes only without touching actual data in the table while SQL_CALC_FOUND_ROWS method will load rows from disk what can be expensive and time consuming on massive tables.
您应该根据情况在 COUNT(*) 和 SQL_CALC_FOUND_ROWS 之间进行选择。如果您的查询搜索条件使用索引中的行 - 使用 COUNT(*)。在这种情况下，Mysql 将仅从索引“读取”而不触及表中的实际数据，而 SQL_CALC_FOUND_ROWS 方法将从磁盘加载行，这在大量表上可能既昂贵又耗时。
More information on this topic in this article @mysqlperformanceblog.
本文@mysqlperformanceblog 中有关此主题的更多信息。
