php - MySQL+PHP : How to paginate data from complex query with ORDER BY on user-selected column

Question

我有一个表，目前约有 1500 行，预计会随着时间的推移而增长(不能说多少，但仍然如此)，该网站是只读的，允许用户通过使用某些表单进行复杂的查询，那么搜索查询是完全 URL 编码的，因为它是一个公共(public)数据库。重要的是要知道用户可以选择必须按哪些列数据排序。

我不担心放置一些索引并减慢插入和更新速度(只是管理员偶尔执行)，因为它基本上是繁重的阅读，但我需要分页结果，因为一些流行的查询可以返回 900 多个结果，这在客户端占用了太多的空间和 RAM(结果被进一步处理以创建一个相当丰富的

HTML 元素，每个结果都有一个 ，顺便说一句)。

• 我知道 OFFSET {$m} LIMIT {$n} 的使用，但希望避免使用
• 我知道这个的用途

查询

SELECT *
FROM table
WHERE {$filters} AND id > {$last_id}
ORDER BY id ASC
LIMIT {$results_per_page}

这就是我想要使用的，但这要求行仅按其 ID 排序!

• 我提出了(我认为是)与自定义排序结果非常相似的查询，并允许高效分页。

查询:

SELECT *
FROM table
WHERE {$filters} AND {$column_id} > {$last_column_id}
ORDER BY {$column} ASC
LIMIT {$results_per_page}

但不幸的是，这需要有一个 {$last_column_id} 值才能在页面之间传递!

我知道索引(尤其是唯一索引)基本上是自动更新的基于整数的列，它们通过列的值(无论是整数、varchar 等)对表进行“排名”，但我真的不知道如何让 MySQL 返回该查询所需的 $last_column_id!

我唯一能想到的就是在用户可以对结果进行排序的每个“XYZ”列旁边放置一个额外的“XYZ_id”整数列，然后通过一些脚本定期更新值，但这是唯一的方法吗？这行得通？请帮忙。

最佳答案

(评论太多，无法容纳在“评论”中。)

查询 I/O 是否受限？还是CPU限制？看起来仅仅 1500 行就会导致 CPU 限制并且速度足够快。

您使用什么引擎？多少内存？ key_buffer_size和innodb_buffer_pool_size的设置是什么？

让我们看看显示创建表。如果表充满了大的BLOB或TEXT字段，我们需要对查询进行编码，以避免获取这些庞大的字段却因为OFFSET<而将它们丢弃。提示:获取 LIMIT ID，然后返回表以获取庞大的列。

提高效率的唯一方法:

SELECT ...
    WHERE x = ...
    ORDER BY y
    LIMIT 100,20

是有INDEX(x,y)。但即便如此，也得跨过100多 block 牛田。

您暗示有许多可能的 WHERE 和 ORDER BY 子句？这是否意味着添加足够的索引来覆盖所有情况可能是不切实际的？

“记住你离开的地方”比使用 OFFSET 好得多，所以尝试这样做。这避免了已经讨论过的 OFFSET 问题。

不要使用WHERE (a,b) > (x,y)；该结构过去没有得到很好的优化。 (也许 5.7 已经修复了这个问题，但我不知道。)

My blog on OFFSET讨论你的问题。 (但是，它可能对您的具体情况有帮助，也可能没有帮助。)

关于php - MySQL+PHP : How to paginate data from complex query with ORDER BY on user-selected column，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/37682597/

Answer 1

(评论太多，无法容纳在“评论”中。)

查询 I/O 是否受限？还是CPU限制？看起来仅仅 1500 行就会导致 CPU 限制并且速度足够快。

您使用什么引擎？多少内存？ key_buffer_size和innodb_buffer_pool_size的设置是什么？

让我们看看显示创建表。如果表充满了大的BLOB或TEXT字段，我们需要对查询进行编码，以避免获取这些庞大的字段却因为OFFSET<而将它们丢弃。提示:获取 LIMIT ID，然后返回表以获取庞大的列。

提高效率的唯一方法:

SELECT ...
    WHERE x = ...
    ORDER BY y
    LIMIT 100,20

是有INDEX(x,y)。但即便如此，也得跨过100多 block 牛田。

您暗示有许多可能的 WHERE 和 ORDER BY 子句？这是否意味着添加足够的索引来覆盖所有情况可能是不切实际的？

“记住你离开的地方”比使用 OFFSET 好得多，所以尝试这样做。这避免了已经讨论过的 OFFSET 问题。

不要使用WHERE (a,b) > (x,y)；该结构过去没有得到很好的优化。 (也许 5.7 已经修复了这个问题，但我不知道。)

My blog on OFFSET讨论你的问题。 (但是，它可能对您的具体情况有帮助，也可能没有帮助。)