Mysql 中的深分页问题
前言
实现分页需求时，通常都会使用 limit 来实现，但是当 offset 特别大的时候，查询速度会非常的慢。文章将会详细的介绍深分页为什么会导致性能问题以及如何解决这个问题。
环境
CREATE TABLE movies
(
id INT PRIMARY KEY,
title VARCHAR(255),
vote_average DECIMAL(4, 3),
vote_count INT,
status VARCHAR(50),
release_date DATE,
revenue BIGINT,
runtime INT,
adult BOOLEAN,
backdrop_path VARCHAR(255),
budget BIGINT,
homepage VARCHAR(255),
imdb_id VARCHAR(20),
original_language VARCHAR(10),
original_title VARCHAR(255),
overview TEXT,
popularity DECIMAL(8, 3),
poster_path VARCHAR(255),
tagline VARCHAR(255),
production_companies TEXT,
production_countries TEXT,
spoken_languages TEXT,
keywords TEXT,
release_year INT,
Director VARCHAR(100),
AverageRating DECIMAL(3, 1),
Poster_Link VARCHAR(255),
Certificate VARCHAR(20),
IMDB_Rating DECIMAL(3, 1),
Meta_score INT,
Star1 VARCHAR(100),
Star2 VARCHAR(100),
Star3 VARCHAR(100),
Star4 VARCHAR(100),
Writer VARCHAR(255),
Director_of_Photography VARCHAR(255),
Producers TEXT,
Music_Composer VARCHAR(255),
genres_list TEXT,
Cast_list TEXT,
overview_sentiment DECIMAL(5, 4),
all_combined_keywords TEXT
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4;
-- 在 release_date 上创建索引
create index idx_release_date on movies (release_date);
数据量
MySQL root@docker.local.com:test> select count(*) from movies;
+----------+
| count(*) |
+----------+
| 1071607 |
+----------+
1 row in set
Time: 1.120s
慢 SQL
在这种环境下，看下深分页查询需要多长的时间
test> select *
from movies
where release_date > '1799-01-01'
order by release_date, id
limit 800000, 10
[2025-10-09 13:21:13] 10 rows retrieved starting from 1 in 8 s 456 ms (execution: 8 s 411 ms, fetching: 45 ms)
上述 SQL 花了 8.456s 才执行完毕，已经是非常非常慢的 SQL 了， 在生产环境下绝对是灾难级别的问题。看下执行计划:
MySQL root@docker.local.com:test> explain
-> select *
-> from movies
-> where release_date > '1799-01-01'
-> order by release_date, id
-> limit 800000, 10;
+----+-------------+--------+------------+------+------------------+--------+---------+--------+---------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+--------+---------+--------+---------+----------+-----------------------------+
| 1 | SIMPLE | movies | <null> | ALL | idx_release_date | <null> | <null> | <null> | 1005233 | 50.0 | Using where; Using filesort |
+----+-------------+--------+------------+------+------------------+--------+---------+--------+---------+----------+-----------------------------+
可以看到，根本没有使用索引，直接进行的全表扫描，同时还用到了文件排序。有读者可能提出: 会不会排序导致了问题呢? 我们看下不使用排序会花费多长的时间:
test> select *
from movies
where release_date > '1799-01-01'
limit 800000, 10
[2025-10-09 13:38:09] 10 rows retrieved starting from 1 in 8 s 365 ms (execution: 8 s 316 ms, fetching: 49 ms)
上述 SQL 依然使用了 8.365s, 并没有比使用了 order by 的 SQL 快多少，依然是灾难级别的 SQL。看下执行计划:
MySQL root@docker.local.com:test> explain
-> select *
-> from movies
-> where release_date > '1799-01-01'
-> limit 800000, 10;
+----+-------------+--------+------------+-------+------------------+------------------+---------+--------+--------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+------------------+------------------+---------+--------+--------+----------+----------------------------------+
| 1 | SIMPLE | movies | <null> | range | idx_release_date | idx_release_date | 4 | <null> | 502616 | 100.0 | Using index condition; Using MRR |
+----+-------------+--------+------------+-------+------------------+------------------+---------+--------+--------+----------+----------------------------------+
去除掉排序后，使用了 idx_release_date 索引，但是速度还是非常的慢，这是为什么呢?
SQL 执行过程
在 Mysql 中，SQL 的执行过程如下:
- 通过 where 进行过滤，找出结果集
- SQL 中有
limit N, M，从结果集的第一行开始遍历，直到第 N 行，抛弃前面的 N - 1 行，然后取出后面的 M 行。
读者可能会说: MySQL 为什么不直接从第 N 行开始，而是从第 1 行开始?
主要是 MySQL 没法像数组那样直接根据下标访问数据。MySQL 的数据存放在一个个数据页中，每个数据页存放的行数是不确定的，因此 MySQL 只能够从第一行开始遍历。
深分页慢 SQL 优化
深分页导致慢 SQL 的原因: 遍历一个个的数据页，直到找到所需要的数据。这个过程涉及很多次的 I/O，I/O 操作相对于 CPU 来说是非常缓慢的。
那么该如何进行优化呢?
首先需要明确: MySQL 的分页操作只能够从第一行开始查找。我们没有办法改变 MySQL 的这个行为，我们能够做的是: 减少 MySQL 遍历的数据页，从而减少 I/O 的次数。
一个普通的索引，里面存放的数据是索引列以及主键。而完整的数据行则存放了非常多的字段。因此相同大小的数据页，索引的行数远大于完整的数据行。举个例子:
- 表有 1GB 数据，每行 1 KB， 也就是 100 万条数据
- 索引 16MB 数据，每行 16 B
假设一个数据页的大小是 64KB，一个数据页可以存放 64 条完整的数据行，而索引可以存放 4000 条. $ 4000 \div 64 = 62.5 $。也就是说遍历索引的速度至少是遍历完整数据行的速度的 62.5 倍。并且遍历索引 I/O 次数更少，真实的速度肯定比 62.5 倍更快。
数据行的大小与索引行的大小差距越大，遍历索引相对于遍历完整数据行的速度就越快。
因此优化深分页的目标就是将分页的操作转移到索引中，让 MySQL 遍历索引而不是遍历完整数据行，怎么办呢? 答案就是: 覆盖索引(covering index) + 延迟关联。
- 覆盖索引可以避免回表，只在索引树中查询所需要的字段
- 延迟关联指的是延迟对列的访问，也就是说不直接获取所需要的列。
- 通过覆盖索引查询到的列与外层查询进行匹配，获取所需要的数据。
慢 SQL
select *
from movies
where release_date > '1799-01-01'
order by release_date, id
limit 800000, 10;
慢 SQL 执行时间
test> select *
from movies
where release_date > '1799-01-01'
order by release_date, id
limit 800000, 10
[2025-10-09 13:21:13] 10 rows retrieved starting from 1 in 8 s 456 ms (execution: 8 s 411 ms, fetching: 45 ms)
执行了 8.456s
慢 SQL 优化后:
select a.*
from movies as a
inner join (select id
from movies
where release_date > '1799-01-01'
order by release_date, id
limit 800000, 10) as tmp on a.id = tmp.id;
优化后的执行时间:
test> select a.*
from movies as a
inner join (select id
from movies
where release_date > '1799-01-01'
order by release_date, id
limit 800000, 10) as tmp on a.id = tmp.id
[2025-10-09 15:01:20] 10 rows retrieved starting from 1 in 317 ms (execution: 290 ms, fetching: 27 ms)
只花费了 317ms