在数据库查询场景中，分页是获取批量数据的常用方式，SQL的LIMIT子句是最基础的分页实现手段，理解其运行原理并针对性优化，能有效提升查询效率。
SQL LIMIT分页的基本原理
LIMIT子句的作用是从查询结果中截取指定范围的数据，基础语法为LIMIT offset, row_count，其中offset表示跳过的记录数，row_count表示返回的记录数。执行分页查询时，数据库会先执行完整的查询逻辑，扫描所有符合条件的记录，再跳过前offset条，返回后续的row_count条数据。
例如要查询用户表第3页的数据，每页10条，对应的SQL如下：
-- 查询用户表第3页，每页10条数据 SELECT id, username, age FROM user_table WHERE age > 18 ORDER BY id LIMIT 20, 10;
这条语句会先筛选出年龄大于18的用户，按照id排序后，跳过前20条记录，返回第21到30条共10条数据。
常规LIMIT分页的性能问题
当数据量较小时，LIMIT分页的性能表现良好，但随着offset值增大，性能会急剧下降。原因在于offset越大，数据库需要扫描和跳过的记录就越多，这些被跳过的记录依然会被数据库读取和判断，只是不会返回给客户端，造成大量无效的IO和CPU消耗。
比如当offset达到100万时，即使只需要返回10条数据，数据库也需要先扫描100万零10条符合条件的记录，再丢弃前100万条，效率极低。
分页查询性能优化方案
1. 基于索引覆盖的游标分页
如果分页查询的排序字段是唯一且有索引的，比如自增主键id，可以使用游标分页替代传统的offset分页。核心思路是记录上一页最后一条数据的排序字段值，查询时直接从这个值之后开始取数据，避免扫描前面的无效记录。
以上面的用户表查询为例，假设上一页最后一条数据的id是20，查询下一页的SQL可以写成：
-- 基于id游标查询下一页数据，每页10条 SELECT id, username, age FROM user_table WHERE age > 18 AND id > 20 ORDER BY id LIMIT 10;
这种方式不需要计算offset，数据库只需要扫描id大于20的10条记录即可，性能不受数据量增长影响。
2. 延迟关联优化大offset场景
如果必须使用offset分页，且查询的字段较多，可以使用延迟关联的方式优化。先通过索引查询出符合条件的主键id，再根据id关联原表获取完整数据，减少回表的开销。
-- 延迟关联优化分页查询
SELECT t.id, t.username, t.age
FROM user_table t
JOIN (
-- 先通过索引查询出需要的主键id
SELECT id FROM user_table
WHERE age > 18
ORDER BY id
LIMIT 100000, 10
) tmp ON t.id = tmp.id;
子查询中只查询主键id，利用索引覆盖的特性可以快速定位到需要的id，再关联原表获取其他字段，大幅减少扫描的数据量。
3. 业务层限制最大分页深度
很多业务场景中，用户很少会翻到非常靠后的页码，比如电商商品列表，用户通常只会查看前几页的数据。可以在业务层限制最大可查询的页码，比如最多只允许查询前100页，超过这个范围直接返回空数据或者提示用户缩小筛选条件，从业务层面避免大offset分页的场景。
4. 合理使用复合索引
分页查询的WHERE条件和ORDER BY字段需要匹配对应的索引，避免数据库进行全表扫描和临时排序。比如查询条件是age大于18，排序字段是id，可以建立(age, id)的复合索引，让查询和排序都可以利用索引完成，提升整体效率。
不同优化方案的选择建议
如果分页场景支持游标分页，优先选择基于索引的游标分页方案，性能最优；如果必须使用offset分页，且offset较大，优先使用延迟关联优化；同时结合业务特点限制最大分页深度，从源头减少性能问题的发生。实际落地时可以根据数据量、查询频率、业务需求综合选择最合适的方案。