当前位置：首页 > article >正文

Mysql深分页的解决方案

article 2025/3/25 7:00:37

在数据量非常大的情况下，深分页查询则变得很常见，深分页会导致MySQL需要扫描大量前面的数据，从而效率低下。例如，使用`LIMIT 100000, 10`时，MySQL需要扫描前100000条数据才能找到第10000页的数据。

在MySQL中解决深分页问题，可通过以下5种优化方案实现：

方案一：延迟关联 (Deferred Join)

原理：先通过子查询获取主键，再关联原表获取完整数据

通常我们直接查询分页较大的数据速率较慢，我们可以选择优先查询主键列，因为其可以通过索引查询且速度最快，然后根据获取的主键匹配对应的数据。

SELECT t.* 
FROM user t
INNER JOIN (
SELECT id 
FROM user 
ORDER BY sort_field 
LIMIT 100000, 10
) AS tmp ON t.id = tmp.id;

方案二：有序唯一键分页 (Cursor-based Pagination)

要求：表中存在有序唯一键（如自增ID）

这种方法的原理就是我们在进行范围查询后需要记录页尾的行号，当查询以行号开始的范围数据时直接根据行号匹配，避免了扫描前面的数据。

-- 假设已知上一页最后一条记录的id为12345
SELECT * 
FROM user 
WHERE id > 12345 
ORDER BY id 
LIMIT 10;

方案三：书签分页 (Bookmark Pagination)

原理：记录上一页最后一条数据的排序字段值

-- 假设按create_time排序，上一页最后记录的create_time为'2023-01-01 12:00:00'
SELECT * 
FROM user 
WHERE create_time > '2023-01-01 12:00:00' 
ORDER BY create_time 
LIMIT 10;

方案四：预估分页 (Approximate Pagination)

适用场景：允许误差的近似分页

适用于数据量极大的场景，即主键也不再进行分页查询，而是通过预估得到大致行号的范围，再通过主键匹配数据行（此方案可能会有误差，需要根据场景选择）

-- 先获取预估偏移量
SELECT COUNT(*) 
FROM user 
WHERE sort_field < {target_value};


-- 再使用延迟关联获取精确数据
SELECT t.* 
FROM user t
INNER JOIN (
SELECT id 
FROM user 
WHERE sort_field < {target_value} 
ORDER BY sort_field 
LIMIT 10
) AS tmp ON t.id = tmp.id;