正文
一. 题目说明
请优化如下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);此时面试官进行了两个问题追加。
- 优化方案的SQL语句能真实执行吗；
- 还有其它的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条数据，统计如下。
+----------+ | 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存储引擎层存在如下这样的交互。
通过上图得到如下两个信息。
- 存在大量回表操作。因为不满足覆盖索引，所以需要先通过二级索引获取到主键Id，然后回表从主键索引中获取到完整记录，而回表操作是随机IO，会存在大量耗时的情况；
- limit条件判断发生在Server层。Server层准备将数据返回给客户端时，才会去判断limit的条件。
现在就可以回答为什么深分页慢了，首先慢的深分页通常没有使用到覆盖索引，此时为了得到完整记录就需要回表，而回表是随机磁盘IO，速度慢消耗大，这就是深分页慢的根本原因。其次深分页场景里limit的偏移量通常很大，并且limit的判断发生在Server层，这就导致存在大量无效的回表，即前50W条记录都是通过二级索引再回表到主键索引得到的，但实际这50W条件记录都不是被需要的，做的这50W次回表都是无意义的。
总结下来就是。
- 没有使用覆盖索引存在大量回表操作；
- 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层面入手的话，思路都是将回表操作进行了减少甚至消除。
