mysql - 存储行数和行数以加快分页速度是个坏主意吗？

Question

我的网站有超过 20.000.000 个条目，条目有类别 (FK) 和标签 (M2M)。至于查询，甚至像 SELECT id FROM table ORDER BY id LIMIT 1000000, 10 MySQL 需要扫描 1000010 行，但这真的慢得令人无法接受(而且 pks、索引、连接等没有太大帮助在这里，仍然是 1000010 行)。所以我试图通过像这样的触发器存储行数和行号来加快分页:

DELIMITER //
CREATE TRIGGER @trigger_name
AFTER INSERT
ON entry_table FOR EACH ROW
BEGIN
    UPDATE category_table SET row_count = (@rc := row_count + 1)
    WHERE id = NEW.category_id;
    NEW.row_number_in_category = @rc;
END //

然后我可以简单地:

SELECT * 
FROM entry_table 
WHERE row_number_in_category > 10 
ORDER BY row_number_in_category 
LIMIT 10

(现在只扫描了 10 行，因此选择速度非常快，虽然插入速度较慢，但​​与选择相比很少见，所以没关系)

这是一种不好的方法吗？有什么好的替代方法吗？

Answer 1

虽然我喜欢问题中的解决方案。如果 entry_table 中的数据发生更改，可能会出现一些问题 - 可能随着时间的推移被删除或分配给不同的类别。

它还限制了数据排序的方式，该方法假定数据仅按插入顺序排序。覆盖多种排序方法需要额外的触发器和摘要数据。

分页的另一种方法是传入您排序/分页所依据的字段的偏移量，而不是传递给 limit 参数的偏移量。

取而代之的是:

SELECT id FROM table ORDER BY id LIMIT 1000000, 10

执行此操作 - 假设在这种情况下查看的最后一个结果的 ID 为 1000000。

SELECT id FROM table WHERE id > 1000000 ORDER BY id LIMIT 0, 10

通过跟踪分页的偏移量，这可以传递给后续的数据查询，并避免数据库对永远不会成为最终结果一部分的行进行排序。

如果您真的只想要 2000 万行中的 10 行，您可以进一步猜测接下来的 10 行匹配行将出现在接下来的 1000 个总体结果中。如果不是这种情况，也许可以通过某种逻辑以更大的余量重复查询。

SELECT id FROM table WHERE id BETWEEN 1000000 AND 1001000 ORDER BY id LIMIT 0, 10

这应该明显更快，因为排序可能会限制一次通过的结果。