优化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. 没有使用覆盖索引

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优化如下。