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

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行，因此selects非常快，虽然插入速度较慢，但与selects相比很少见，所以这是可以的)

这是一种糟糕的方法吗?有什么好的替代方法吗？

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

这应该会快得多，因为排序可能会将结果限制在一次传递中。