大表Offset分页查询优化：800万Player数据多表关联查询慢求助
2026-6-19
问题背景
我有三张表：country、team、player，表结构示例如下：
表结构示例
Country表
id name 1 x 2 x 3 x
Team表
id name country_id 1 x 1 2 x 1 3 x 1
Player表
id name country_id team_id 1 x 1 1 2 x 1 1 3 x 1 1
其中country和team表数据量极少，player表存有800万条数据。执行以下多表关联Offset分页查询时耗时超30秒：
慢查询语句
select c.id as country_id, c.name as country_name, t.id as team_id, t.name as team_name, p.id as player_id, p.name as player_name, p.created_at from player p join country c on c.id = p.country_id join team t on t.id = p.team_id where c.id = 1 order by p.created_at DESC limit 10, 10;
Player表现有索引
+--------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +--------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | player | 0 | PRIMARY | 1 | id | A | 557608 | NULL | NULL | | BTREE | | | YES | NULL | | player | 1 | FKb21w76q5ho5gx5270qg5docnt | 1 | country_id | A | 3 | NULL | NULL | | BTREE | | | YES | NULL | | player | 1 | FKdvd6ljes11r44igawmpm1mc5s | 1 | team_id | A | 3 | NULL | NULL | | BTREE | | | YES | NULL | | player | 1 | idx_player_created | 1 | created_at | D | 195396 | NULL | NULL | | BTREE | | | YES | NULL | +--------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
执行计划
EXPLAIN结果
+----+-------------+-------+------------+-------+-----------------------------+-----------------------------+---------+-----------------+------+----------+----------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+-----------------------------+-----------------------------+---------+-----------------+------+----------+----------------------------------------------+ | 1 | SIMPLE | c | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 2 | 100.00 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | p | NULL | ref | FKb21w76q5ho5gx5270qg5docnt | FKb21w76q5ho5gx5270qg5docnt | 8 | query_test.c.id | 2972 | 100.00 | NULL | +----+-------------+-------+------------+-------+-----------------------------+-----------------------------+---------+-----------------+------+----------+----------------------------------------------+
EXPLAIN ANALYZE结果
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Limit/Offset: 1/10 row(s) (actual time=34787..34787 rows=1 loops=1) -> Sort: p.id DESC, limit input to 11 row(s) per chunk (actual time=34787..34787 rows=11 loops=1) -> Stream results (cost=7929 rows=5944) (actual time=0.442..34548 rows=1.13e+6 loops=1) -> Nested loop inner join (cost=7929 rows=5944) (actual time=0.433..33974 rows=1.13e+6 loops=1) -> Nested loop inner join (cost=5848 rows=5944) (actual time=0.426..33200 rows=1.13e+6 loops=1) -> Filter: (c.id in (1,3)) (cost=0.91 rows=2) (actual time=0.0292..0.0476 rows=2 loops=1) -> Index range scan on c using PRIMARY over (id = 1) OR (id = 3) (cost=0.91 rows=2) (actual time=0.0273..0.0439 rows=2 loops=1) -> Index lookup on p using FKb21w76q5ho5gx5270qg5docnt (country_id=c.id) (cost=2775 rows=2972) (actual time=0.282..16570 rows=564564 loops=2) -> Single-row index lookup on t using PRIMARY (id=p.team_id) (cost=0.25 rows=1) (actual time=520e-6..544e-6 rows=1 loops=1.13e+6) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
移除ORDER BY后查询仅耗时0.01秒。我了解游标分页方案，但希望在保留Offset分页的前提下，获取更优的查询方案，请问是否可行？
建表语句
Player表
CREATE TABLE `player` ( `id` bigint NOT NULL AUTO_INCREMENT, `created_at` datetime(6) NOT NULL, `updated_at` datetime(6) NOT NULL, `name` varchar(255) DEFAULT NULL, `phone_number` varchar(255) DEFAULT NULL, `country_id` bigint NOT NULL, `team_id` bigint NOT NULL, PRIMARY KEY (`id`), KEY `FKdvd6ljes11r44igawmpm1mc5s` (`team_id`), KEY `idx_player_created` (`created_at` DESC), KEY `FKb21w76q5ho5gx5270qg5docnt` (`country_id`), CONSTRAINT `FKb21w76q5ho5gx5270qg5docnt` FOREIGN KEY (`country_id`) REFERENCES `country` (`id`), CONSTRAINT `FKdvd6ljes11r44igawmpm1mc5s` FOREIGN KEY (`team_id`) REFERENCES `team` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=8088248 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Country表
CREATE TABLE `country` ( `id` bigint NOT NULL AUTO_INCREMENT, `created_at` datetime(6) NOT NULL, `updated_at` datetime(6) NOT NULL, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Team表
CREATE TABLE `team` ( `id` bigint NOT NULL AUTO_INCREMENT, `created_at` datetime(6) NOT NULL, `updated_at` datetime(6) NOT NULL, `name` varchar(255) DEFAULT NULL, `country_id` bigint NOT NULL, PRIMARY KEY (`id`), KEY `FKqv6wvrq3qclb3gvo92gg2y6q7` (`country_id`), CONSTRAINT `FKqv6wvrq3qclb3gvo92gg2y6q7` FOREIGN KEY (`country_id`) REFERENCES `country` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
解决方案
可行，核心是优化排序和分页的效率，避免全量数据排序，以下是具体方案：
1. 创建联合索引（核心优化）
当前查询瓶颈：按country_id筛选后，需对大量player数据排序才能分页。现有索引无法同时满足过滤和排序需求，需创建联合索引：
CREATE INDEX idx_country_created ON player (country_id, created_at DESC);
该索引可直接定位country_id=1的所有数据，且已按created_at降序排列，无需额外排序。
若想进一步避免回表，可创建覆盖索引（包含查询所需的所有player字段）：
CREATE INDEX idx_country_created_covering ON player (country_id, created_at DESC, id, team_id, name);
查询时可直接从索引获取所需字段，无需访问主表。
2. 调整查询逻辑：先分页再关联
利用country和team数据量极小的特点，先从player表分页获取目标数据，再关联其他表，避免关联后排序分页：
select c.id as country_id, c.name as country_name, t.id as team_id, t.name as team_name, p.id as player_id, p.name as player_name, p.created_at from (select id, name, team_id, created_at from player where country_id = 1 order by created_at DESC limit 10, 10) p join country c on c.id = 1 join team t on t.id = p.team_id;
内层查询利用联合索引快速获取分页数据，外层关联小表几乎无开销。
3. 强制指定最优索引
若MySQL未自动选择新创建的联合索引，可在查询中强制指定：
select c.id as country_id, c.name as country_name, t.id as team_id, t.name as team_name, p.id as player_id, p.name as player_name, p.created_at from player p force index(idx_country_created) join country c on c.id = p.country_id join team t on t.id = p.team_id where c.id = 1 order by p.created_at DESC limit 10, 10;
效果验证
创建联合索引后，执行计划应显示使用idx_country_created索引，且Extra字段无Using filesort或Using temporary，查询耗时可降至毫秒级。
内容的提问来源于stack exchange，提问作者geon
相关产品推荐
相关产品推荐
