Mysql深分页

引言：

在 MySQL 中，深分页问题通常出现在需要从大型数据集（如百万级或以上）中提取特定页面的数据时，尤其使用 LIMIT m, n 且 m 很大时。数据库需要扫描并丢弃前 m 行，性能随偏移量增加而显著下降。例如 SELECT * FROM orders ORDER BY order_date DESC LIMIT 100000, 10 在数据量大时可能耗时数秒，严重影响用户体验。

使用索引的 ORDER BY：

对于深分页查询，可以尝试通过 ORDER BY 使用索引来加速。例如，假设查询为 SELECT id, m_id, name, identity_no, address, create_time, modify_time FROM t1 ORDER BY create_time DESC LIMIT 100000, 10，如果 create_time 有索引，MySQL 可能使用该索引进行排序和范围扫描。
create_time 前提是需要为排序字段建立索引，切记：如果分页过深可能即使你加上了索引但最终的执行计划还是没有走索引，因此可以使用force index(create_index) 来强制其走索引（在数据量为300w，limit 100000，10 如果不走create索引时间为6s，如果走索引时间为1s，如果数据量更加可能对比效果不明显）。
总结：对于深分页，偏移量大时仍需扫描大量索引行，性能提升有限。用户观察到即使加索引，执行计划有时仍不走索引，可能因优化器判断全表扫描更高效。

“last_” 条件查询（键集分页）：

此方法适合连续翻页场景，如社交媒体的无限滚动。通过记录上一次查询的最后一行（如 last_id 和 last_create_time），下次查询从该点开始。例如：

SELECT id, m_id, name, identity_no, address, create_time, modify_time
FROM t2
WHERE id > #{last_id} AND create_time > #{last_create_time}
ORDER BY create_time DESC
LIMIT 0, 20;

需要字段（如 id 和 create_time）具有连续性，通常 id 为自增主键。

子查询联表方法（延迟关联）：

# 在300w数据量的情况下查询时间为10s  方法一
select id,m_id, name, identity_no, address, create_time, modify_time
from t1
order by create_time desc
limit 1000000, 20;

# 在300w的数据量下查询时间为0.6s   方法二
select id,m_id, name, identity_no, address, create_time, modify_time
from t1
JOIN ( SELECT id FROM t1 ORDER BY create_time desc LIMIT 1000000, 20 ) t2 USING ( id );

对于该sql的变形有很多例如  [把条件转移到主键索引树]
SELECT id, m_id, name, identity_no, address, create_time, modify_time
FROM t1
WHERE id >= (SELECT id FROM t1 ORDER BY create_time DESC LIMIT 1000000, 1)
ORDER BY create_time DESC
LIMIT 10;

其实通过查看执行计划可以发现方法二走了Using index 意思就是覆盖索引方法二虽然看起来相较于方法一变得复杂了许多但是其子查询直接可以在二级索引中查到所有字段而不需要进行回表查询这个是性能提升的关键，然后通过查询得到id再通过关联id来进行二次查询，这次查询也不需要回表，因此速度很快。哪怕create_time没有添加索引，方法二也比方法一快一倍，因为方法二的子查询由于对create_tiem进行了排序因此需要使用一个临时文件进行存储满足结果的字段然后进行排序，方法二中的字段只有id和create_time，字段较少因此排序所需时间要少很多，到最后就会发现方法二的速度还是很快。

深分页问题的解决思路：

可以将深分页的解决方案总结为减少回表次数和减少临时表大小。上述的方案也都是围绕着这个核心来开展的。