[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` 条件来减少基础数据量