✨字节二面✨MySQL深分页如何优化

大家好，我是半夏之沫 😁😁 一名金融科技领域的JAVA系统研发😊😊
我希望将自己工作和学习中的经验以最朴实，最严谨的方式分享给大家，共同进步👉💓👈
👉👉👉👉👉👉👉👉💓写作不易，期待大家的关注和点赞💓👈👈👈👈👈👈👈👈
👉👉👉👉👉👉👉👉💓关注微信公众号【技术探界】 💓👈👈👈👈👈👈👈👈

---

前言

前同事刚参加完字节的二面，向我反馈了一道MySQL 深分页的优化题目，起初我以为这只是一道很常规的深分页的题目，但是听完字节面试官的追问，才发现 水很深。

参考资料

正文

一. 题目说明

请优化如下SQL语句，其中tb表的b列存在索引。

SELECT * FROM tb WHERE tb.b = 5 LIMIT 30000,5;

前同事给出的答案如下。

SELECT * FROM tb WHERE id IN (SELECT id FROM tb WHERE tb.b = 5 LIMIT 30000,5);

此时面试官进行了两个问题追加。

1. 优化方案的SQL语句能真实执行吗；
2. 还有其它的SQL优化手段吗。

前同事一个没答上来，随即面试官又追加了一个问题。

1. 请说一下一开始给到你的SQL语句为什么会慢。

那么到这里，可以发现字节面试官其实真正的目的是想问为什么深分页会慢，这个问题你有仔细想过吗，如果问到你，你会怎么回答。

二. 深分页演示

创建如下一张表。

CREATE TABLE blog_post (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    uid VARCHAR(255) NOT NULL COMMENT '唯一标识',
    like_count INT NOT NULL DEFAULT '0' COMMENT '点赞统计',
    collect_count INT NOT NULL DEFAULT '0' COMMENT '收藏统计',
    recommend TINYINT(1) NOT NULL DEFAULT '0' COMMENT '推荐标识',
    KEY like_count_index(like_count)
);

向表中插入100W条数据，统计如下。

+----------+
| COUNT(*) |
+----------+
|  1100010 |
+----------+

执行如下一条深分页的查询SQL。

SELECT 
  * 
FROM
  blog_post
WHERE
  like_count > 0
LIMIT 500000,10;

耗时如下。

10 rows in set (1.34 sec)

很简单的一条查询语句结果耗时达到了1.34秒。

三. 深分页为什么慢

还是以如下这条语句来分析。

SELECT 
  * 
FROM
  blog_post
WHERE
  like_count > 0
LIMIT 500000,10;

上述这条SQL语句在执行时，MySQL的Server层和InnoDB存储引擎层存在如下这样的交互。

通过上图得到如下两个信息。

1. 存在大量回表操作。因为不满足覆盖索引，所以需要先通过二级索引获取到主键Id，然后回表从主键索引中获取到完整记录，而回表操作是随机IO，会存在大量耗时的情况；
2. limit 条件判断发生在 Server 层。Server层准备将数据返回给客户端时，才会去判断limit的条件。

现在就可以回答为什么深分页慢了，首先慢的深分页通常没有使用到覆盖索引，此时为了得到完整记录就需要回表，而回表是随机磁盘IO，速度慢消耗大，这就是深分页慢的 根本原因。其次深分页场景里limit的偏移量通常很大，并且limit的判断发生在Server层，这就导致存在大量无效的回表，即前50W条记录都是通过二级索引再回表到主键索引得到的，但实际这50W条件记录都不是被需要的，做的这50W次回表都是无意义的。

总结下来就是。

1. 没有使用覆盖索引存在大量回表操作；
2. limit 的判断发生在 Server 层导致大量回表操作都是无意义的。

四. 优化深分页

既然明确了深分页为什么慢，那么现在就可以很好的针对原因从SQL层面进行优化。

既然 根本原因 是存在大量无效回表，那么我们可以着手消灭掉回表操作或者减少回表操作。

首先可以将SQL优化如下。

SELECT 
  like_count 
FROM
  blog_post
WHERE
  like_count > 0
LIMIT 500000,10;

将查询字段修改为like_count，此时深分页就算再深，不用回表，速度也是十分可观的，此时查询耗时如下。

10 rows in set (0.13 sec)

速度提升十分可观，但假如一定要查询完整记录呢，此时可以将SQL优化如下。

SELECT 
  *
FROM
  blog_post
INNER JOIN
  (SELECT id FROM blog_post WHERE like_count > 0 LIMIT 500000,10) AS bpid
ON
  blog_post.id = bpid.id;

上述语句的查询耗时如下。

10 rows in set (0.17 sec)

速度提升也是十分可观，但其实无论哪种优化方式，从SQL层面入手的话，思路都是将回表操作进行了减少甚至消除。

总结

深分页其实就是使用limit时偏移量很大，而被偏移的数据其实都是不被需要的，如果在获取这些数据时耗时很高，那么这些很高的耗时是白白浪费的，那么什么情况下会出现获取这些数据耗时很高呢，最常见的就是没有使用到覆盖索引的情况，此时每一条被偏移的数据都要回表一次才能得到完整数据，耗时自然就很高了。

所以如果一定要深分页，最好的优化方案就是避免出现回表，也就是利用覆盖索引。

---

大家好，我是半夏之沫 😁😁 一名金融科技领域的JAVA系统研发😊😊
我希望将自己工作和学习中的经验以最朴实，最严谨的方式分享给大家，共同进步👉💓👈
👉👉👉👉👉👉👉👉💓写作不易，期待大家的关注和点赞💓👈👈👈👈👈👈👈👈
👉👉👉👉👉👉👉👉💓关注微信公众号【技术探界】 💓👈👈👈👈👈👈👈👈