MySQL深度分页问题的三种解决方法_Mysql

前言

在 mysql 中解决深度分页问题的核心思路是减少扫描的数据量，尤其是避免通过 limit offset, size 导致的大范围数据扫描。以下是三种优化方法及其原理、适用场景和注意事项：

1. 子查询 + 覆盖索引（延迟关联）

原理

先通过覆盖索引（如二级索引 (name, id)）快速定位目标页的起始 id，再通过主键索引回表查询数据。
子查询只需扫描二级索引，体积小且有序，能高效跳过 offset 行，获取起始 id。
主查询通过 id >= [子查询结果] 直接定位数据，避免全表扫描。

示例 sql

select * from mianshiya 
where name = 'yupi' and id >= (
    select id from mianshiya 
    where name = 'yupi' 
    order by id limit 99999990, 1
)
order by id limit 10;

或使用 join 优化：

select * from mianshiya 
inner join (
    select id from mianshiya 
    where name = 'yupi' 
    order by id limit 99999990, 10
) as tmp on mianshiya.id = tmp.id;

关键点

必须创建联合索引 (name, id)，确保子查询直接利用索引有序性，避免临时排序（filesort）。
主查询的 name 条件可省略（若子查询结果 id 对应的 name 必为 'yupi'），但需权衡数据变更风险。

2. 记录最大 id（游标分页）

原理

每次分页返回当前页的最大 id，下页查询时通过 where id > max_id limit size 跳过已读数据。
仅扫描目标数据（size 行），时间复杂度稳定为 o(size)，性能极佳。

适用场景

连续分页（如“下一页”），不支持随机跳页。
数据按主键或有序字段分页（如 order by id）。

示例 sql

-- 第一页
select * from mianshiya where name = 'yupi' order by id limit 10;
-- 后续页（假设上一页最大 id 为 100）
select * from mianshiya where name = 'yupi' and id > 100 order by id limit 10;

注意事项

若数据删除或新增可能导致少量重复或遗漏，需业务容忍。
需前端配合传递 max_id，不可直接跳页。

3. elasticsearch 优化

原理

将数据同步到 elasticsearch，利用其分布式特性加速搜索。
通过 search_after 参数实现游标分页，类似记录 max_id 方法，避免深分页性能问题。

es 分页示例

{
  "query": { "match": { "name": "yupi" } },
  "sort": [{ "id": "asc" }],
  "size": 10,
  "search_after": [100]  -- 上一页最后一条记录的排序值
}

es 注意事项

默认限制 from + size <= 10000，深度分页需改用 search_after 或 scroll。
search_after 需基于唯一排序字段，且仅支持连续分页。

其他优化思路

业务限制：禁止深度跳页（如最多展示前 100 页）。
预计算分页位置：按时间或分段存储分页起始 id（如每 1000 页记录一个起始 id）。
分区表：按时间或范围分区，减少单次扫描数据量。

总结

方法	优点	缺点	适用场景
子查询 + 覆盖索引	支持跳页，通用性强	依赖联合索引设计	需频繁分页且允许跳页
记录最大 id	性能最优，复杂度低	仅支持连续分页	连续分页（如瀑布流）
elasticsearch	适合复杂搜索，分布式性能好	维护成本高，es 需额外学习	高并发搜索+分页场景

核心要点

索引设计是优化基础，确保覆盖索引和排序字段匹配。
权衡业务需求，优先选择游标分页（记录 max_id）或延迟关联（子查询）。
搜索引擎适用于复杂查询，但引入额外组件需评估成本。

扩展：mysql中 join、inner join、left join、right join区别

在 mysql 中，join 操作用于将多个表中的数据关联起来。以下是 inner join、left join、right join 的核心区别和用法总结，帮助你快速分清它们：

1. inner join（内连接）

作用：只返回两个表中完全匹配的行。
结果：仅包含两个表中都存在关联键值的行。
语法：

select * 
from 表a
inner join 表b on 表a.键 = 表b.键;

示例：
- 表a（员工表）和表b（部门表）通过 department_id 关联。
- 结果：只显示有部门的员工信息，无部门或无员工的数据会被过滤掉。

2. left join（左外连接）

作用：返回左表（left join 左侧的表）的所有行，即使右表没有匹配。
结果：左表所有数据 + 右表匹配的数据（无匹配时右表字段为 null）。
语法：

select * 
from 表a
left join 表b on 表a.键 = 表b.键;

示例：
- 表a（员工表）left join 表b（部门表）。
- 结果：显示所有员工信息，即使员工没有部门（部门字段为 null）。

3. right join（右外连接）

作用：返回右表（right join 右侧的表）的所有行，即使左表没有匹配。
结果：右表所有数据 + 左表匹配的数据（无匹配时左表字段为 null）。
语法：

select * 
from 表a
right join 表b on 表a.键 = 表b.键;

示例：
- 表a（员工表）right join 表b（部门表）。
- 结果：显示所有部门信息，即使部门没有员工（员工字段为 null）。

4. join（默认是 inner join）

说明：在 mysql 中，直接写 join 等价于 inner join。

select * 
from 表a
join 表b on 表a.键 = 表b.键; -- 等同于 inner join

对比总结

类型	行为	适用场景
inner join	仅返回两个表匹配的行	需要精确匹配的数据（如订单和商品）
left join	返回左表全部数据 + 右表匹配的数据（右表无匹配则为 `null`）	保留左表全部数据（如所有员工信息）
right join	返回右表全部数据 + 左表匹配的数据（左表无匹配则为 `null`）	保留右表全部数据（如所有部门信息）

关键注意事项

方向性：left join 和 right join 的方向取决于表的书写顺序。
- left join 以左表为主，right join 以右表为主。
过滤条件：
- 在 left join 中，若在 where 子句中对右表字段过滤（如 where 表b.键 is null），会筛选出仅存在于左表但右表无匹配的行。
性能：
- inner join 通常效率更高，因为它涉及的数据量更小。
- left/right join 可能因处理 null 值而略慢，尤其是在大表中。

示例演示

数据准备

-- 员工表（employees）
+-------------+-------+---------------+
| employee_id | name  | department_id |
+-------------+-------+---------------+
| 1           | 张三  | 101           |
| 2           | 李四  | 102           |
| 3           | 王五  | null          |
+-------------+-------+---------------+

-- 部门表（departments）
+---------------+-----------------+
| department_id | department_name |
+---------------+-----------------+
| 101           | 技术部          |
| 102           | 市场部          |
| 103           | 财务部          |
+---------------+-----------------+

查询结果对比

inner join（匹配数据）：

select * 
from employees
inner join departments 
  on employees.department_id = departments.department_id;

结果：

| 1 | 张三 | 101 | 101 | 技术部 |
| 2 | 李四 | 102 | 102 | 市场部 |

left join（保留所有员工）：

select * 
from employees
left join departments 
  on employees.department_id = departments.department_id;

结果：

| 1 | 张三 | 101 | 101 | 技术部 |
| 2 | 李四 | 102 | 102 | 市场部 |
| 3 | 王五 | null| null| null   | -- 员工无部门，右表字段为 null

right join（保留所有部门）：

select * 
from employees
right join departments 
  on employees.department_id = departments.department_id;

结果：

| 1 | 张三 | 101 | 101 | 技术部 |
| 2 | 李四 | 102 | 102 | 市场部 |
| null| null| null| 103 | 财务部 | -- 部门无员工，左表字段为 null

总结

inner join：精确匹配，适合需要严格关联的场景。
left join：保留左表全部数据，适合主从表查询（如“所有员工及其部门”）。
right join：保留右表全部数据，使用较少（通常用 left join 调换表顺序替代）。

以上就是mysql深度分页问题的三种解决方法的详细内容，更多关于mysql深度分页问题的资料请关注代码网其它相关文章！

MySQL深度分页问题的三种解决方法

2025年02月28日 • Mysql •我要评论

前言