Mysql/ES深度分页优化

1.深度分页

LIMIT 偏移量分页

性能差

键集分页

原理：这种方法基于上一次查询的结果来确定下一次查询的起始点。假设表中有一个自增主键id和一个时间戳created_at，并且数据是按照created_at降序排列的。第一次查询可以是SELECT * FROM table_name WHERE created_at <= '2024 - 01 - 01' ORDER BY created_at DESC, id DESC LIMIT 10，下一次查询就可以根据上一次查询结果中的最小created_at和id值来构建查询条件，如SELECT * FROM table_name WHERE (created_at < '上一次最小created_at' OR (created_at = '上一次最小created_at' AND id < '上一次最小id')) ORDER BY created_at DESC, id DESC LIMIT 10。

局限性

• 这种方法要求数据有明确的排序规则，并且需要在应用层维护上一次查询的边界条件，对于动态排序的场景可能不太适用。
• 要确保排序的列组合能够唯一确定每一行数据，避免数据遗漏或重复查询
• 不支持随机选择某一页查询

子查询和临时表

原理：可以先在子查询中获取主键（或索引列）的值，然后将这些值连接到原表来获取完整的数据。例如，对于上面的products表，查询语句可以是SELECT p.* FROM products p JOIN (SELECT id FROM products WHERE name LIKE '%product%' ORDER BY price LIMIT 10000, 10) sub ON p.id = sub.id。这样，子查询只获取了主键id的值，减少了数据传输和处理量，然后通过连接获取完整的数据行。
注意点：这种方法在一定程度上可以提高性能，但如果子查询和连接操作复杂，也可能会引入新的性能问题。同时，要注意临时表的大小和内存使用情况，避免占用过多资源

https://cloud.tencent.com/developer/article/1884103

https://javaguide.cn/high-performance/deep-pagination-optimization.html#范围查询

https://segmentfault.com/a/1190000023912355

利用索引覆盖

• 原理：通过创建合适的索引，让查询仅通过索引就能获取所需的结果，而不需要访问数据表中的所有行。例如，如果经常按照某个字段（如 create_date ）进行排序和分页，可以为该字段创建索引。这样，在分页查询时，MySQL可以利用索引来快速定位到需要的记录，而不是全表扫描。
• 局限性：设计和维护索引需要一定的成本，过多的索引会占用存储空间，并且在数据插入、更新和删除时会影响性能。同时，并不是所有的查询都能通过索引覆盖来优化，需要根据具体的业务场景和查询模式来合理设计索引。