正文一. 题目说明请优化如下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层面入手的话思路都是将回表操作进行了减少甚至消除。
