[TOC] #### 1. 深度分页 --- MySQL 深度分页是后端开发中非常经典的性能瓶颈问题，也是面试中很可能问到的知识点 深度分页：通常把页码非常靠后，也就是 `offset` 很大的分页称为深度分页 + 当数据量较小时没问题，但当页数越来越大，就会产生深度分页问题 + 当使用传统的 `limit offset, size` 语句翻页到很靠后的位置时，查询速度会急剧下降，甚至导致数据库超时 为什么会出现深度分页问题 ？ 假设你有一条看似很简单的的 SQL，你可能以为 MySQL 会直接跳到第 1000000 行，然后取走后面的10条，但事实并非如此 ```sql select * from users limit 1000000,10; ``` MySQL 的实际执行流程是： 1. 全量扫描与排序：根据索引或文件排序，找到满足条件的前 1000010 条记录（扫描数量 = offset + size） 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` 条件来减少基础数据量 ##### 引入搜索引擎（Elasticsearch） 如果系统对复杂条件的深度分页和全文检索有极高的要求，建议将数据同步到 Elasticsearch。ES 在处理海量数据的分页和搜索方面，比 MySQL 更加专业和高效 #### 3. 总结和建议 --- 在日常开发中，优先推荐使用 “游标分页”；如果是内部后台管理系统且必须翻页，则采用 “延迟关联”；同时配合 “限制最大页数” 的业务兜底策略，基本可以解决 90% 以上的深度分页问题 | 方案 | 适用场景 | 优点 | 缺点 | | ------------ | ------------ | ------------ | ------------ | | 游标分页 | 无限滚动加载（瀑布流） | 性能极高且稳定 | 无法随机跳页 | | 延迟关联 | 后台管理系统、必须支持跳页 | 兼容性好，性能提升明显 | SQL 稍显复杂 | | 业务限制 | 常规 Web 列表展示 | 开发成功几乎为 0 | 牺牲部分用户体验（很少有人翻很多页） | #### 4. 面试高频问题 --- ##### 深度分页为什么会慢 ？ 深度分页慢的根本原因，是因为 MySQL 在处理大 `offset` 时，会进行大量的无效扫描和回表操作 当执行类似 `LIMIT 100000, 10` 这样的 SQL 时，很多人以为 MySQL 会直接跳到第 100000 行取数据。但实际上，MySQL 采用的是 “流式处理”： 1. 它会从索引树的第一条记录开始，逐行扫描并定位满足条件的数据 2. 每找到一条符合条件的记录，它就会拿着主键 ID 去聚簇索引中进行回表，读取完整的行数据 3. 然后它会把前 100000 条完整的数据丢弃，只返回最后的 10 条 + 这意味着数据库白白做了 100000 次毫无意义的回表 I/O 操作，导致查询性能断崖式下跌 ##### 如何优化深度分页问题 ？ 针对深度分页问题，实际开发中应根据具体的业务场景来选择不同的优化方案： + 下拉加载更多等连续翻页的场景：首选基于主键ID游标分页的方案，它的核心是放弃传统的 `offset` + 在后台管理系统中产品要求必须支持随机跳页：游标分页就不适用了。此时采用延迟关联法，核心是：先查 ID，再回表 + 从产品和业务层面进行妥协：限制最大只能查看前 100 页，因为绝大多数用户根本不会去翻看特别靠后的数据 拔高架构视野（展示技术广度）： 如果面对的是亿级海量数据，并且有极其复杂的全文检索和多维度排序需求，单纯依靠 MySQL 优化可能已经触及瓶颈。这时候可以考虑接入 Elasticsearch 这样的专业搜索引擎来接管分页和检索任务，利用 ES 强大的倒排索引和 search_after 机制来解决极端的深度分页问题。 ##### 面试官可能的追问与应对 延迟关联里的覆盖索引是什么意思 ？ 为什么要用它 ？ + 覆盖索引是指查询的字段刚好都在索引树上，不需要再去聚簇索引里回表拿完整的行数据 + 在延迟关联的子查询中使用覆盖索引，可以保证子查询本身极快，从而最大化整体优化的效果