--- highlight: a11y-dark ---

你的名字-001

> 大家好，我是**半夏之沫** 😁😁 一名金融科技领域的**JAVA**系统研发😊😊
> 我希望将自己工作和学习中的经验以**最朴实**，**最严谨**的方式分享给大家，共同进步👉💓👈
> 👉👉👉👉👉👉👉👉💓**写作不易，期待大家的关注和点赞**💓👈👈👈👈👈👈👈👈
> 👉👉👉👉👉👉👉👉💓**关注微信公众号【技术探界】** 💓👈👈👈👈👈👈👈👈 *** ## 前言前同事刚参加完字节的二面，向我反馈了一道**MySQL**深分页的优化题目，起初我以为这只是一道很常规的深分页的题目，但是听完字节面试官的追问，才发现 ***`水很深`***。 [**参考资料**](https://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/) ## 正文 ### 一. 题目说明请优化如下**SQL**语句，其中**tb**表的**b**列存在索引。 ```sql SELECT * FROM tb WHERE tb.b = 5 LIMIT 30000,5; ``` 前同事给出的答案如下。 ```sql SELECT * FROM tb WHERE id IN (SELECT id FROM tb WHERE tb.b = 5 LIMIT 30000,5); ``` 此时面试官进行了两个问题追加。 1. *优化方案的* **SQL** *语句能真实执行吗*； 2. *还有其它的* **SQL** *优化手段吗*。前同事一个没答上来，随即面试官又追加了一个问题。 1. *请说一下一开始给到你的* **SQL** *语句为什么会慢*。那么到这里，可以发现字节面试官其实真正的目的是想问 ***`为什么深分页会慢`***，这个问题你有仔细想过吗，如果问到你，你会怎么回答。 ### 二. 深分页演示创建如下一张表。 ```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**条数据，统计如下。 ```txt +----------+ | COUNT(*) | +----------+ | 1100010 | +----------+ ``` 执行如下一条深分页的查询**SQL**。 ```sql SELECT * FROM blog_post WHERE like_count > 0 LIMIT 500000,10; ``` 耗时如下。 ```txt 10 rows in set (1.34 sec) ``` 很简单的一条查询语句结果耗时达到了**1.34**秒。 ### 三. 深分页为什么慢还是以如下这条语句来分析。 ```sql SELECT * FROM blog_post WHERE like_count > 0 LIMIT 500000,10; ``` 上述这条**SQL**语句在执行时，**MySQL**的**Server**层和**InnoDB**存储引擎层存在如下这样的交互。

MySQL-一条查询语句执行时Server层和引擎层的交互示意图.jpg

通过上图得到如下两个信息。 1. *存在大量回表操作*。因为不满足覆盖索引，所以需要先通过二级索引获取到主键**Id**，然后回表从主键索引中获取到完整记录，而回表操作是随机**IO**，会存在大量耗时的情况； 2. **limit** *条件判断发生在* **Server** *层*。**Server**层准备将数据返回给客户端时，才会去判断**limit**的条件。现在就可以回答为什么深分页慢了，首先慢的深分页通常没有使用到覆盖索引，此时为了得到完整记录就需要回表，而回表是随机磁盘**IO**，速度慢消耗大，这就是深分页慢的 ***`根本原因`***。其次深分页场景里**limit**的偏移量通常很大，并且**limit**的判断发生在**Server**层，这就导致存在大量无效的回表，即前**50W**条记录都是通过二级索引再回表到主键索引得到的，但实际这**50W**条件记录都不是被需要的，做的这**50W**次回表都是无意义的。总结下来就是。 1. *没有使用覆盖索引存在大量回表操作*； 2. **limit** *的判断发生在* **Server** *层导致大量回表操作都是无意义的*。 ### 三. 优化深分页既然明确了深分页为什么慢，那么现在就可以很好的针对原因从**SQL**层面进行优化。既然 ***`根本原因`*** 是存在大量无效回表，那么我们可以着手消灭掉回表操作或者减少回表操作。首先可以将**SQL**优化如下。 ```sql SELECT like_count FROM blog_post WHERE like_count > 0 LIMIT 500000,10; ``` 将查询字段修改为**like\_count**，此时深分页就算再深，不用回表，速度也是十分可观的，此时查询耗时如下。 ```txt 10 rows in set (0.13 sec) ``` 速度提升十分可观，但假如一定要查询完整记录呢，此时可以将**SQL**优化如下。 ```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; ``` 上述语句的查询耗时如下。 ```txt 10 rows in set (0.17 sec) ``` 速度提升也是十分可观，但其实无论哪种优化方式，从**SQL**层面入手的话，思路都是将回表操作进行了减少甚至消除。 ## 总结深分页其实就是使用**limit**时偏移量很大，而被偏移的数据其实都是不被需要的，如果在获取这些数据时耗时很高，那么这些很高的耗时是白白浪费的，那么什么情况下会出现获取这些数据耗时很高呢，最常见的就是 ***`没有使用到覆盖索引的情况`***，此时每一条被偏移的数据都要回表一次才能得到完整数据，耗时自然就很高了。所以如果一定要深分页，最好的优化方案就是避免出现回表，也就是利用 ***`覆盖索引`***。 *** > 大家好，我是**半夏之沫** 😁😁 一名金融科技领域的**JAVA**系统研发😊😊
> 我希望将自己工作和学习中的经验以**最朴实**，**最严谨**的方式分享给大家，共同进步👉💓👈
> 👉👉👉👉👉👉👉👉💓**写作不易，期待大家的关注和点赞**💓👈👈👈👈👈👈👈👈
> 👉👉👉👉👉👉👉👉💓**关注微信公众号【技术探界】** 💓👈👈👈👈👈👈👈👈

你的名字-002