MySQL深分页优化方式_Mysql

mysql深分页优化

mysql中的深分页问题通常是指当我们通过limit语句查询数据，尤其是在翻到较后面的页码时，性能会急剧下降。

例如，查询第1000页的数据，每页10条，系统需要跳过前9990条数据，然后才能获取到所需的记录，这在大数据集上非常低效。

传统的深分页实现方法通常是使用offset和limit直接做分页查询：

select * from table
order by some_column
limit 9990, 10;

这会导致数据库扫描大量不需要的行然后抛弃它们，才能获取到真正需要的数据。

延迟关联的工作方式

延迟关联通过两步查询优化性能：

快速定位：首先仅在索引上运行快速查询，快速定位到需要的数据的位置。这个步骤不获取所有字段，只获取主键或者是用于排序的列。
精确获取：然后根据第一步查询获得的主键（或少数几个列），做第二步的查询以精确获取所有需要的数据字段。

示例：有 posts 表和 comments 表。

-- 查询有特定标签的文章的id
select post_id
into temporary table temp_post_ids
from posts
where tags like '%特定标签%';

-- 利用临时表数据进行关联查询
select p.*, c.*
from temp_post_ids t
join posts p on t.post_id = p.id
left join comments c on p.id = c.post_id;

为什么能提升性能

减少数据扫描量：第一步查询只在索引上运行，大大减少了数据的扫描量。因为索引通常比完整的数据行要小很多，而且数据库可以更有效地在索引上进行排序和分页操作。
减少io操作：只有在第二步查询中才会获取完整的数据行，这减少了数据库的io操作，尤其是当表中包含大量大型字段（如text, blob类型）时。
充分利用索引：通常，第一步的查询能够充分利用索引，使查询效率最大化。

最大id查询法

使用最大id查询法，我们利用了数据库中的id通常是自增（或至少是有序的）这一性质。

通过记录上一次查询返回的最后一条记录的id，下一次查询时，我们只需要选择id大于这个值的记录，这样避免了扫描和跳过前面所有的记录。

优点：

性能提升：这种方法减少了数据库的负载，尤其是对于大数据集。因为它只查询需要的数据，避免了大量的无用扫描。
可扩展性：随着数据量的增加，传统的offset方法性能降低，而最大id方法的性能下降不明显，适合大数据量的场景。
简单有效：实现简单，但能显著提高分页查询的性能。

缺点：

依赖有序的id：这个方法的有效性依赖于有序的id（比如自增id）。如果数据库表中没有一个有序的、单调递增的字段，这种方法就不适用。
不适合复杂排序需求：当查询需要基于其他字段进行排序时，这种方法可能就不再适用。比如，如果需要基于时间或者其他非递增字段进行分页，最大id方法就不能直接使用了。
数据删除或更新的处理：如果数据表中的记录会被删除，那么这可能会导致某些id被跳过，从而影响分页的连续性。同样，如果id是可更新的，那么这种方法也会遇到问题。
非等距分页：使用最大id进行分页时，如果数据表中存在大量的删除操作，导致id有较大的间隔，可能会出现每页数据量不一致的情况。虽然通常这不是一个大问题，但在某些应用场景中可能会影响用户体验。
首页数据动态变化：如果你的应用场景需要频繁展示数据的最新状态，使用最大id分页法可能会导致最新添加的记录不被即时显示。例如，当用户在浏览第二页时，如果首页有新数据添加，用户回到首页可能看不到这些新数据，因为查询的起始id已经改变。
不适用于随机访问：对于需要直接跳转到指定页面的场景（例如，用户直接跳转到第100页），最大id方法实现起来比较困难，因为你无法直接知道第100页开始的id是多少，除非你额外维护一个每页开始id的映射表。