MySQL大表分页查询过慢，求有效优化方案

问题背景 #

现有InnoDB引擎的Playlists表结构如下：

create table if not exists Playlists(
    UserId bigint unsigned not null,
    Title varchar(50) not null,
    IsPublic bool not null,
    primary key (UserId, Title),
    foreign key (UserId) references Users(Id)
    on delete cascade
    on update cascade
);

表数据量超100万且持续增长，执行以下分页查询时耗时随Offset增大而显著增加：

select Title from Playlists where IsPublic = true order by Title limit @myLimit offset @myOffset

单独创建IsPublic字段索引未获得明显性能提升：

create index index_playlists_ispublic on Playlists(IsPublic);

可行优化方案 #

1. 创建联合覆盖索引 #

单独的IsPublic索引性能差的核心原因：该字段基数极低（仅true/false两个值），查询会返回大量数据，MySQL使用索引后仍需回表获取数据，再执行排序操作，开销极大。

应创建**(IsPublic, Title)联合覆盖索引**，该索引同时满足三个核心需求：

• 直接过滤IsPublic = true的条件
• 索引本身按Title有序，无需额外执行排序操作
• 索引包含查询所需的Title字段，无需回表（覆盖索引特性）

创建索引的SQL：

CREATE INDEX idx_ispublic_title ON Playlists(IsPublic, Title);

可通过EXPLAIN验证索引是否生效：

EXPLAIN SELECT Title FROM Playlists WHERE IsPublic = true ORDER BY Title LIMIT @myLimit OFFSET @myOffset;

若执行计划中type为ref或range，key为idx_ispublic_title，Extra包含Using index，则说明索引已正确使用。

2. 替换大Offset分页为游标分页 #

当Offset值很大时（如超过10万），MySQL需要扫描并跳过大量前置数据，导致耗时剧增。此时可改用游标分页，以上一页查询结果的最后一条Title作为下一页的起始条件，直接定位数据位置。

示例：

• 第一页查询：

SELECT Title FROM Playlists WHERE IsPublic = true ORDER BY Title LIMIT @myLimit;

• 假设上一页最后一条Title为"Summer Hits"，下一页查询：

SELECT Title FROM Playlists WHERE IsPublic = true AND Title > 'Summer Hits' ORDER BY Title LIMIT @myLimit;

这种方式无需扫描前置数据，查询效率稳定，但仅支持顺序翻页，无法直接跳转到指定页码。

3. 数据拆分（备选） #

若未来数据量持续增长至千万级以上，可考虑按IsPublic字段拆分表，将公开歌单和私有歌单分别存储到两张表中。此方案能减少单表数据量，提升查询效率，但会增加数据维护的复杂度（如IsPublic字段变更时需迁移数据），仅在其他优化方案无法满足需求时考虑。

内容的提问来源于stack exchange，提问作者Jule