如何优化MySQL中使用OFFSET的百万级数据分页查询

问题背景 #

单表ipdetails中country='US'的数据量超80万条，执行以下分页查询时耗时超20秒：

SELECT * FROM ipdetails WHERE country= 'US' LIMIT 1000 OFFSET 800000;

已为country字段创建单独索引，但大偏移量导致性能瓶颈。

优化方案 #

1. 改用主键游标分页 #

OFFSET 800000会强制数据库扫描前80万条数据并丢弃，这是性能低下的核心原因。通过联合索引+主键范围查询可以直接定位到分页起始位置：

• 首先创建country与id的联合索引：

  CREATE INDEX idx_country_id ON ipdetails(country, id);
  

• 获取上一页最后一条数据的id（首次查询可通过偏移量获取一次）：

  SELECT id FROM ipdetails WHERE country='US' ORDER BY id LIMIT 1 OFFSET 800000;
  

• 用该id作为游标，查询下一页数据：

  SELECT * FROM ipdetails WHERE country='US' AND id > [上一页最后id] ORDER BY id LIMIT 1000;
  

后续分页只需传递上一页的最后id，无需再使用大偏移量，查询效率会大幅提升。

2. 使用覆盖索引减少回表 #

如果不需要查询所有字段，只保留业务必需的列，同时创建包含这些列的覆盖索引，避免数据库回表查询：

• 假设只需要id, ip_to, ip_from字段，创建覆盖索引：

  CREATE INDEX idx_country_ip_fields ON ipdetails(country, id, ip_to, ip_from);
  

• 查询语句改为：

  SELECT id, ip_to, ip_from FROM ipdetails WHERE country='US' AND id > [上一页最后id] ORDER BY id LIMIT 1000;
  

此时数据库直接从索引中读取数据，无需访问主表，性能进一步优化。

3. 预存分页起始位置（可选） #

如果分页逻辑固定，可提前计算各分页段的起始id，存入临时表或缓存中。后续分页直接使用预存的id进行范围查询，彻底避免偏移量计算。

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