MYSQL - 分页查询执行时间问题

Question

我正在尝试使用 MySql 在 Coldfusion 中进行分页，以便一次检索 20 条记录。首先，我获取要检索的记录总数。

<CFQUERY DATASOURCE="#datasource#" USERNAME="#username#" PASSWORD="#password#" NAME="myCount" CACHEDWITHIN="#CreateTimeSpan(0,0,3,0)#">
   SELECT COUNT(*) as TotalCount
   FROM products
   WHERE products.field1 = <cfqueryparam value = "#myfield#" cfsqltype = "cf_sql_integer" maxLength = "2">
   AND MATCH(field2) AGAINST(<cfqueryparam value = "#TRIM(string)#" cfsqltype = "cf_sql_char" maxLength = "52"> IN BOOLEAN MODE)
</CFQUERY>      

然后我使用以下命令一次检索 20 条记录:

<CFQUERY DATASOURCE="#datasource#" USERNAME="#username#" PASSWORD="#password#" NAME="myQuery" result="tmpResult">
    SELECT products.field1, products.field2, products.field3, company.field1, company.field2
    FROM products JOIN company ON products.field1 = company.field1
    WHERE products.field2 = <cfqueryparam value = "#myfield#" cfsqltype = "cf_sql_integer" maxLength = "2">
    AND MATCH(field2) AGAINST(<cfqueryparam value = "#TRIM(string)#" cfsqltype = "cf_sql_char" maxLength = "52"> IN BOOLEAN MODE)
    ORDER by products.field3 DESC
    LIMIT <cfqueryparam value="#start#" cfsqltype="cf_sql_integer">, 20
</CFQUERY>

(注:每页#start#的值会增加20。)

一切似乎都运行良好。当我点击第一页时，执行时间(#tmpResult.ExecutionTime#)表明检索20条记录需要40(我假设是毫秒)。但是，如果我单击最后一页(可能是第 40,000 条记录)，则需要 500 毫秒才能检索最后 20 条记录。回过头来看，后面的页面似乎需要更长的时间才能检索。

为什么会出现这样的差异？检索相同数量的 20 条记录是否应该花费相同的时间，无论是从“前面”还是“后面”？我在这里错过了什么吗？提前致谢。

Answer 1

首先阅读并研究我的blog on why Pagination via Offset is bad 。请注意，它解释了一种使其更快的方法。

如果初始 COUNT(*) 可能很慢，请重新考虑 UI。请注意搜索引擎如何不再告诉您找到了多少项目，或者至少只给您一个大概的答案？从他们很久以前学到的教训中吸取教训。

要解决“为什么”的问题...LIMIT 100, 20 必须找到 120 行，跳过 100 行，然后交付 20 行。因此页面会变得越来越慢。

但情况变得更糟。如果查询太复杂，它必须找到所有匹配行，对所有它们进行排序，并且仅然后执行偏移和限制。在这种情况下，当行数较多时，即使是第一页也会很慢。