[TOC] #### 1. 深度分页 --- MySQL 深度分页是后端开发中非常经典的性能瓶颈问题。 当使用传统的 `limit offset, size` 语句翻页到很靠后的位置时，查询速度会急剧下降，甚至导致数据库超时。 为什么会出现深度分页问题 ？ 假设你有一条看似很简单的的 SQL，你可能以为 MySQL 会直接跳到第 1000000 行，然后取走后面的10条，但事实并非如此 ```sql select * from users limit 1000000,10; ``` MySQL 的实际执行流程是： 1. 全量扫描与排序：根据索引或文件排序，找到满足条件的前 1000010 条记录 2. 逐行读取并丢弃：从第一条开始，一条一条的读取数据，把前 1000000 条全部丢弃（这就是 offset 带来的巨大开销） 3. 返回结果：只返回最后的那 10 条数据 随着 `offset` 的增大，MySQL 需要扫描的行数呈线性增长，产生了大量的无用的 I/O 和回表操作，性能自然断崖式下跌 #### 2. 优化方案 --- ##### 游标分页（性能最优） 这是目前互联网大厂（如抖音、微博信息流）最常用的方案。 它的核心思想是放弃 `offset`，利用上一页最后一条数据的唯一标识作为 “游标”，直接定位下一页的起点 传统写法（慢）： ```sql SELECT * FROM goods WHERE category = 1 ORDER BY id DESC LIMIT 100000, 20; ``` 游标分页（快）： + 前端传递上一页最后一条记录的 `id`（假设为 100000） ```sql SELECT * FROM goods WHERE category = 1 AND id > 100000 ORDER BY id DESC LIMIT 20; ``` 游标分页的优缺点： + 优势：无论翻到多深，MySQL 都能通过索引直接定位，每次只扫描需要的 20 条数据，性能极其稳定 + 局限：不支持随机跳转到某一页（比如直接点第 500 页），只适合下拉加载、加载更多以及无限滚动的场景 ##### 延迟关联（兼容跳页） 如果你的业务必须支持后台管理系统的 “页码跳转”，可以使用延迟关联（覆盖索引 + 子查询 + 关联查询） + 核心思路是：先在覆盖索引中查出需要的 ID，再通过 ID 回表获取完整数据 ```sql SELECT g.* FROM goods g INNER JOIN ( SELECT id FROM goods WHERE category = 1 ORDER BY id DESC LIMIT 100000,20 ) AS temp ON g.id = temp.id ORDER BY id DESC; ``` 延迟关联原理：子查询 `select id ...` 只需要走索引树，不需要进行耗时的 “回表” 操作去拿整行数据，等确定了 20 个 ID 后，再拿着这 20个 ID 去主键索引里回表，大大减少了 I/O 开销 为什么这样查询会更快呢 ？ + 因为 `select id` 是覆盖索引，直接在索引树中就能拿到 id，不会去回表，查询数据就快 + 拿到这 20 个 id 后再去回表，只需要回表 10 条，而不需要回表 100000 条，所以查询会更快 适用场景： + 业务必须支持页面跳转 + 根据非主键字段（create_time）进行深度分页查询，无法直接用 id 进行定位 ##### 业务妥协法（最简单直接） 很多时候，技术问题可以通过产品逻辑解决 + 限制最大页数：像百度、谷歌搜索或京东商品列表一样，通常只允许用户翻到前 100 页 + 缩小时间范围：强制要求用户选择时间范围（如 “最近三个月”），通过增加 `where` 条件来减少基础数据量