在学习 SQL 调优时很多人都会遇到一个经典问题深分页为什么慢比如这条 SQLSELECTid,order_no,total_amount,create_timeFROMt_orderORDERBYcreate_timeDESC,idDESCLIMIT90000,20;它的意思是跳过前 90000 条数据再取 20 条数据。看起来只是取 20 条为什么会慢核心原因是MySQL 不能直接“瞬移”到第 90001 条它需要先扫描、排序或计数前面的 90000 条数据然后把它们丢掉最后再返回 20 条。所以深分页慢不是慢在返回 20 条而是慢在前面大量被跳过的数据也需要被处理。一、LIMIT 两个参数是什么意思MySQL 里LIMIT有两种常见写法。第一种LIMIT20;表示取前 20 条。它等价于LIMIT0,20;第二种LIMIT90000,20;第一个参数是 offset表示跳过多少条。第二个参数是 count表示取多少条。所以LIMIT90000,20;不是取 90000 条而是跳过前 90000 条再取 20 条。二、深分页为什么慢假设有一张订单表CREATETABLEt_order(idBIGINTPRIMARYKEY,order_noVARCHAR(64)NOTNULL,total_amountDECIMAL(10,2)NOTNULL,create_timeDATETIMENOTNULL)ENGINEInnoDB;现在执行SELECTid,order_no,total_amount,create_timeFROMt_orderORDERBYcreate_timeDESC,idDESCLIMIT90000,20;这条 SQL 的逻辑是1. 按 create_time DESC, id DESC 排序 2. 跳过前 90000 条 3. 返回后面的 20 条如果没有合适索引MySQL 可能需要1. 扫描大量数据 2. 对数据按照 create_time 和 id 排序 3. 排序后跳过前 90000 条 4. 返回第 90001 到第 90020 条用伪代码理解ListOrdertempnewArrayList();for(Orderrow:t_order所有数据){temp.add(row);}temp.sort(按照 create_timeDESC,idDESC);returntemp.subList(90000,90020);这时候很慢因为 MySQL 不仅要筛选数据还要排序还要丢弃大量无用数据。如果EXPLAIN看到类似type: ALL key: NULL rows: 1000000 Extra: Using filesort说明它可能在全表扫描并且还要额外排序。其中type ALL全表扫描 key NULL没有用上索引 Using filesortMySQL 需要额外排序没有直接利用索引顺序三、有索引时深分页还会慢吗假设我们创建索引CREATEINDEXidx_order_create_time_idONt_order(create_timeDESC,idDESC);这个索引的顺序刚好和 SQL 的排序一致ORDERBYcreate_timeDESC,idDESC那么 MySQL 可以沿着索引顺序扫描第 1 条 第 2 条 第 3 条 ... 第 90000 条跳过 第 90001 条返回 ... 第 90020 条返回这时候它可能不需要filesort因为索引本身已经排好序了。但是问题仍然存在即使有索引LIMIT 90000, 20仍然要从索引开头扫描并跳过前 90000 条。所以有索引以后深分页可能从全表扫描 排序 跳过大量数据优化成索引顺序扫描 跳过大量数据虽然已经快了一些但依然要扫描很多无用数据。这就是深分页的本质问题offset 越大前面被跳过的数据越多。四、游标分页是什么游标分页也叫 seek pagination。它的思想是不要每次都从头开始数而是记录上一页最后一条数据的位置下一页直接从这个位置后面继续查。比如第一页SELECTid,order_no,total_amount,create_timeFROMt_orderORDERBYcreate_timeDESC,idDESCLIMIT20;假设第一页最后一条是create_time 2025-01-01 12:00:00 id 888888那么下一页就不要写LIMIT20,20;而是写SELECTid,order_no,total_amount,create_timeFROMt_orderWHEREcreate_time2025-01-01 12:00:00OR(create_time2025-01-01 12:00:00ANDid888888)ORDERBYcreate_timeDESC,idDESCLIMIT20;这条 SQL 的意思是找排在上一页最后一条后面的数据然后取 20 条。五、为什么这里是小于不是大于因为排序是ORDERBYcreate_timeDESC,idDESC也就是倒序。倒序规则下create_time 越大越靠前 create_time 相同id 越大越靠前。假设数据顺序是create_time id 2025-01-05 10:00:00 999999 2025-01-04 09:00:00 900000 2025-01-01 12:00:00 888888 ← 第一页最后一条 2025-01-01 12:00:00 888887 2025-01-01 12:00:00 888886 2024-12-31 20:00:00 777777下一页应该从888887开始。所以条件应该是WHEREcreate_time2025-01-01 12:00:00OR(create_time2025-01-01 12:00:00ANDid888888)意思是1. 找 create_time 更小的数据 2. 如果 create_time 相同就找 id 更小的数据。如果是升序排序ORDERBYcreate_timeASC,idASC那么下一页就应该用WHEREcreate_time#{lastCreateTime}OR(create_time#{lastCreateTime} AND id #{lastId})所以规律是DESC 倒序分页下一页用 ASC 升序分页下一页用 更准确地说游标分页的条件要和 ORDER BY 的排序方向保持一致。六、游标分页为什么更快游标分页快的前提是必须有合适的索引。比如CREATEINDEXidx_order_create_time_idONt_order(create_timeDESC,idDESC);这个索引的顺序就是create_time 倒序 create_time 相同再按 id 倒序。索引里的数据大概是create_time id 2025-01-05 10:00:00 999999 2025-01-04 09:00:00 900000 2025-01-01 12:00:00 888888 2025-01-01 12:00:00 888887 2025-01-01 12:00:00 888886 2024-12-31 20:00:00 777777当执行SELECTid,order_no,total_amount,create_timeFROMt_orderWHEREcreate_time2025-01-01 12:00:00OR(create_time2025-01-01 12:00:00ANDid888888)ORDERBYcreate_timeDESC,idDESCLIMIT20;MySQL 可以利用索引快速定位到上一页最后一条附近然后沿着索引继续向后扫描扫够 20 条就停止。它不需要从第一页重新数到第 90000 条。所以深分页和游标分页的区别是深分页 从头开始扫跳过前 offset 条再取 count 条。 游标分页 从上一页最后一条的位置继续往后扫扫够 count 条就停止。这就是游标分页快的原因。七、如果没有索引游标分页还快吗不一定。这是非常关键的一点。如果没有这个索引CREATEINDEXidx_order_create_time_idONt_order(create_timeDESC,idDESC);那么游标分页 SQLSELECTid,order_no,total_amount,create_timeFROMt_orderWHEREcreate_time2025-01-01 12:00:00OR(create_time2025-01-01 12:00:00ANDid888888)ORDERBYcreate_timeDESC,idDESCLIMIT20;仍然可能变成1. 扫描全表 2. 判断 WHERE 条件 3. 把符合条件的数据拿出来 4. 按 create_time DESC, id DESC 排序 5. 取前 20 条伪代码类似ListOrdertempnewArrayList();for(Orderrow:t_order所有数据){if(row.createTime2025-01-0112:00:00||(row.createTime2025-01-0112:00:00row.id888888)){temp.add(row);}}temp.sort(按照 create_timeDESC,idDESC);returntemp前20条;所以必须强调游标分页不是因为多写了 WHERE 就快而是因为这个 WHERE 条件能够配合索引变成索引范围扫描。如果没有索引它仍然可能全表扫描。八、B 树索引到底怎么存储要理解游标分页为什么能“从某个位置继续往后扫”就要理解 MySQL InnoDB 的 B 树索引结构。InnoDB 的索引底层主要是 B 树。B 树不是二叉树不是一个节点只有左孩子和右孩子。B 树是一种多叉平衡搜索树。它的特点是1. 一个节点里可以存很多索引值 2. 一个节点可以指向很多个子节点 3. 非叶子节点只负责导航 4. 叶子节点存真正的数据或主键值 5. 叶子节点之间通过链表连接适合范围查询。九、B 树的节点是什么在 InnoDB 里可以把 B 树的一个节点理解成一个数据页。默认情况下一个页大小通常是 16KB。一个页里不是只存一个值而是可以存很多索引记录。比如一个非叶子节点可能长这样[100 | 300 | 600 | 900]这些值都是排好序的。它们像目录一样把数据范围分成很多段小于 100 的去第 1 个子节点找 100 到 299 的去第 2 个子节点找 300 到 599 的去第 3 个子节点找 600 到 899 的去第 4 个子节点找 大于等于 900 的去第 5 个子节点找。所以 B 树不是左子树 / 右子树而是第 1 个子节点 / 第 2 个子节点 / 第 3 个子节点 / 第 4 个子节点 / ...它是多叉树。十、非叶子节点存什么非叶子节点存的是索引值 指向下一层页的指针也就是说非叶子节点本质上是目录页。比如主键索引PRIMARY KEY(id)的非叶子节点可能存300 - page_10 600 - page_20 900 - page_30它的作用是告诉 MySQL你要找 id 520应该去 page_20 继续找。非叶子节点不存完整行数据。如果非叶子节点也存完整行会导致每个节点能放的数据变少树变高磁盘 IO 次数变多查询性能反而下降。所以 B 树的设计思想是非叶子节点尽量小只负责指路叶子节点才保存真正的数据。十一、叶子节点存什么InnoDB 里要区分两类索引1. 主键索引也叫聚簇索引 2. 二级索引也叫普通索引、辅助索引。1. 主键索引的叶子节点如果表有主键PRIMARYKEY(id)那么 InnoDB 会按照id建一棵主键 B 树。主键索引的叶子节点存的是完整的一整行数据比如id 100 - 这一行订单完整数据 id 200 - 这一行订单完整数据 id 300 - 这一行订单完整数据完整行数据包括id order_no user_id shop_id total_amount create_time ...所以 InnoDB 的表数据本身就是存放在主键索引的叶子节点上的。这就是聚簇索引。2. 二级索引的叶子节点比如创建普通索引CREATEINDEXidx_order_create_time_idONt_order(create_timeDESC,idDESC);它会额外建立一棵 B 树。这棵树按照create_time DESC id DESC排序。二级索引的叶子节点一般存的是索引字段值 主键值在这个例子里索引字段本身就是create_time id其中id又是主键。如果创建的是CREATEINDEXidx_order_user_idONt_order(user_id);那么二级索引叶子节点存的就是user_id 主键 id为什么二级索引要存主键 id因为如果查询需要整行数据MySQL 可以先通过二级索引找到主键 id再拿主键 id 回到主键索引里查完整行。这个过程叫回表十二、用 create_time id 索引举例创建索引CREATEINDEXidx_order_create_time_idONt_order(create_timeDESC,idDESC);假设有这些数据id create_time 100 2025-01-05 10:00:00 200 2025-01-04 09:00:00 300 2025-01-01 12:00:00 400 2025-01-01 12:00:00 500 2024-12-31 20:00:00因为索引是create_timeDESC,idDESC所以二级索引叶子节点里的顺序大概是create_time id 2025-01-05 10:00:00 100 2025-01-04 09:00:00 200 2025-01-01 12:00:00 400 2025-01-01 12:00:00 300 2024-12-31 20:00:00 500注意create_time 相同的时候id 大的排前面。因为id DESC。这棵 B 树的非叶子节点存的是类似索引边界值 子页指针叶子节点存的是create_time id叶子节点之间还有链表连接[2025-01-05,100] - [2025-01-04,200] - [2025-01-01,400] - [2025-01-01,300] - [2024-12-31,500]所以当执行ORDERBYcreate_timeDESC,idDESCLIMIT20;MySQL 可以直接沿着这个索引顺序扫描不需要额外排序。当执行游标分页WHEREcreate_time2025-01-01 12:00:00OR(create_time2025-01-01 12:00:00ANDid888888)ORDERBYcreate_timeDESC,idDESCLIMIT20;MySQL 可以利用 B 树定位到对应位置附近然后沿着叶子节点链表继续扫描取够 20 条就停止。这就是游标分页快的底层原因。十三、什么是索引值索引值就是你创建索引时指定字段的值。比如CREATEINDEXidx_user_phoneONt_user(phone);这个索引的索引值就是phone 字段的值比如13000010001 13000010002 13000010003如果是联合索引CREATEINDEXidx_order_create_time_idONt_order(create_timeDESC,idDESC);它的索引值就是(create_time, id)比如(2025-01-05 10:00:00, 100) (2025-01-04 09:00:00, 200) (2025-01-01 12:00:00, 400)联合索引的排序规则是先按第一个字段排 第一个字段相同再按第二个字段排 第二个字段相同再按第三个字段排 依次类推。这就是最左前缀原则的底层基础。十四、为什么 B 树适合范围查询B 树的叶子节点之间是有序链表。比如[1, 2, 3] - [4, 5, 6] - [7, 8, 9] - [10, 11, 12]如果查WHEREidBETWEEN4AND10MySQL 可以1. 先通过非叶子节点快速定位到 id 4 附近 2. 然后沿着叶子节点链表继续往后扫 3. 扫到 id 10 停止。这就非常适合范围查询。游标分页也是类似思想先定位到上一页最后一条附近 再沿着叶子节点链表继续扫描 扫够 LIMIT 20 就停。十五、深分页与游标分页的最终对比深分页SELECTid,order_no,total_amount,create_timeFROMt_orderORDERBYcreate_timeDESC,idDESCLIMIT90000,20;执行特点从头开始扫描 跳过前 90000 条 再返回 20 条。即使有索引也要从索引开头数过 90000 条。游标分页SELECTid,order_no,total_amount,create_timeFROMt_orderWHEREcreate_time2025-01-01 12:00:00OR(create_time2025-01-01 12:00:00ANDid888888)ORDERBYcreate_timeDESC,idDESCLIMIT20;执行特点从上一页最后一条后面继续扫描 取够 20 条就停止。前提是有索引CREATEINDEXidx_order_create_time_idONt_order(create_timeDESC,idDESC);否则它仍然可能全表扫描。十六、面试可以这样回答如果面试官问深分页为什么慢怎么优化可以这样回答深分页慢的原因是 offset 太大。比如LIMIT 90000, 20MySQL 需要先找到前 90020 条数据然后丢弃前 90000 条只返回 20 条。前面被丢弃的数据也要扫描、排序或计数所以 offset 越大越慢。优化方式可以使用游标分页也就是记录上一页最后一条数据的排序字段和主键比如create_time和id下一页通过WHERE create_time lastCreateTime OR (create_time lastCreateTime AND id lastId)继续往后查。但游标分页快的前提是要有和排序字段一致的联合索引比如(create_time DESC, id DESC)。如果没有索引游标分页也可能全表扫描并不会真正快。它快的底层原因是 B 树索引本身已经按照create_time和id排好序MySQL 可以通过索引快速定位到上一页最后一条附近然后沿着叶子节点链表继续扫描扫够LIMIT 20就停止。如果面试官继续问B 树内部怎么存可以这样回答B 树不是二叉树而是多叉平衡搜索树。一个节点里可以存很多有序的索引值并指向多个子节点。在 InnoDB 中一个 B 树节点可以理解成一个数据页。非叶子节点存的是索引值和子页指针主要负责导航叶子节点存真正的数据。主键索引的叶子节点存完整行数据所以主键索引也叫聚簇索引。二级索引的叶子节点存索引字段值和主键值如果查询需要其他字段就要通过主键值回到主键索引中查完整行这个过程叫回表。B 树的叶子节点之间有链表所以非常适合范围查询和游标分页这类场景。十七、总结深分页慢的本质offset 太大前面被跳过的数据也要被处理。游标分页快的本质记录上一页最后一条的位置下一页从这个位置继续查。但是必须强调游标分页快不是因为多写了 WHERE 条件 而是因为 WHERE 条件可以配合索引变成索引范围扫描。B 树索引的本质非叶子节点存索引值和指针负责导航 叶子节点存真实索引记录 主键索引叶子节点存整行数据 二级索引叶子节点存索引字段值和主键值 叶子节点之间有链表适合范围扫描。所以 SQL 调优时不能只背“加索引”而要理解索引本身就是一棵按照字段值排好序的 B 树。如果 SQL 的 WHERE、ORDER BY、LIMIT 能利用这棵树的顺序就能少扫描、少排序、少回表从而提升性能。