SQL分页查询详解：从基础语法到最佳实践
个人名片
🎓作者简介：java领域优质创作者
🌐个人主页：码农阿豪
📞工作室：新空间代码工作室（提供各种软件服务)
💌个人邮箱：[2435024119@qq.com]
📱个人微信：15279484656
🌐个人导航网站：www.forff.top
💡座右铭：总有人要赢。为什么不能是我呢？
- 专栏导航：
码农阿豪系列专栏导航
面试专栏：收集了java相关高频面试题，面试实战总结🍻🎉🖥️
Spring5系列专栏：整理了Spring5重要知识点与实战演练，有案例可直接使用🚀🔧💻
Redis专栏：Redis从零到一学习分享，经验总结，案例实战💐📝💡
全栈系列专栏：海纳百川有容乃大，可能你想要的东西里面都有🤸🌱🚀
目录
SQL分页查询详解：从基础语法到最佳实践
引言
在数据库查询中，分页（Pagination） 是一项基本且关键的技术，特别是在Web应用、数据分析和大规模数据查询场景中。合理的分页查询可以显著提升性能，减少不必要的数据传输，并优化用户体验。
本文将从 SQL分页的基础语法 讲起，逐步深入探讨 不同数据库的分页实现方式，并给出 最佳实践建议，帮助开发者高效、安全地实现分页功能。
1. 为什么需要分页？
1.1 分页的作用
- 减少数据传输：避免一次性加载海量数据，降低网络和内存开销。
- 提升查询性能：数据库只需返回部分数据，减少I/O和计算压力。
- 改善用户体验：前端展示更友好，避免长列表导致页面卡顿。
1.2 典型应用场景
- 电商网站的商品列表
- 社交媒体的动态流
- 数据分析报表的分批加载
2. SQL分页基础语法
2.1 MySQL/MariaDB/PostgreSQL的分页方式
最常见的分页方式是使用 LIMIT 子句，有两种写法：
（1）LIMIT offset, count
SELECT * FROM users
ORDER BY id
LIMIT 10, 20; -- 跳过前10条，返回接下来的20条
（2）LIMIT count OFFSET offset（更清晰）
SELECT * FROM users
ORDER BY id
LIMIT 20 OFFSET 10; -- 同上，但可读性更好
2.2 使用变量动态分页
在实际开发中，分页参数通常是动态传入的（如前端传递 page 和 pageSize）。例如，在 MyBatis 或 JDBC 中，可以这样写：
SELECT * FROM products
ORDER BY create_time DESC
LIMIT #{offset}, #{pageSize};
其中：
offset = (page - 1) * pageSize（如果page从 1 开始计数）pageSize是每页记录数
3. 不同数据库的分页实现
不同数据库对分页的支持略有不同，以下是几种主流数据库的分页语法对比。
3.1 MySQL / MariaDB / PostgreSQL / SQLite
-- 方式1
SELECT * FROM table LIMIT 10, 20;
-- 方式2（推荐）
SELECT * FROM table LIMIT 20 OFFSET 10;
3.2 SQL Server（2012+）
SQL Server 使用 OFFSET-FETCH 语法：
SELECT * FROM table
ORDER BY id
OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY;
3.3 Oracle（12c+）
Oracle 12c 开始支持 OFFSET-FETCH：
SELECT * FROM table
ORDER BY id
OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY;
3.4 旧版Oracle（使用ROWNUM）
-- 第一页（1-20条）
SELECT * FROM (
SELECT t.*, ROWNUM rn FROM (
SELECT * FROM table ORDER BY id
) t WHERE ROWNUM <= 20
) WHERE rn > 0;
-- 第二页（21-40条）
SELECT * FROM (
SELECT t.*, ROWNUM rn FROM (
SELECT * FROM table ORDER BY id
) t WHERE ROWNUM <= 40
) WHERE rn > 20;
4. 分页查询的最佳实践
4.1 始终结合 ORDER BY 使用
分页查询必须指定排序规则，否则数据可能随机返回，导致分页混乱：
-- ✅ 正确
SELECT * FROM users ORDER BY id LIMIT 10, 20;
-- ❌ 错误（数据可能不一致）
SELECT * FROM users LIMIT 10, 20;
4.2 避免大偏移量（Deep Pagination）
当 offset 很大时（如 LIMIT 100000, 20），数据库仍然需要扫描前 100000 条记录，性能极差。
优化方案：
（1）使用 WHERE + 索引列
SELECT * FROM users
WHERE id > 100000 -- 假设id是自增主键
ORDER BY id
LIMIT 20;
（2）使用 JOIN 优化
SELECT t.* FROM users t
JOIN (SELECT id FROM users ORDER BY id LIMIT 100000, 20) tmp
ON t.id = tmp.id;
4.3 前端分页 vs 后端分页
| 方案 | 优点 | 缺点 |
|---|---|---|
| 前端分页（一次性加载所有数据） | 减少HTTP请求 | 数据量大时内存占用高 |
| 后端分页（每次请求部分数据） | 节省带宽，适合大数据 | 需要多次请求 |
推荐：
- 数据量小（<1000条） → 前端分页
- 数据量大（>1000条） → 后端分页
5. 常见问题及解决方案
5.1 如何计算总页数？
通常需要先查询总记录数：
SELECT COUNT(*) FROM users;
然后在代码中计算：
int totalPages = (totalRecords + pageSize - 1) / pageSize;
5.2 分页参数安全
避免SQL注入，应使用 参数化查询（PreparedStatement）：
// Java（JDBC）
String sql = "SELECT * FROM users LIMIT ?, ?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setInt(1, offset);
stmt.setInt(2, pageSize);
5.3 分页偏移量超出范围
如果 offset 超过总记录数，应返回空列表，而不是报错。
6. 总结
| 关键点 | 说明 |
|---|---|
| 基础语法 | LIMIT offset, count 或 LIMIT count OFFSET offset |
| 数据库差异 | MySQL/PostgreSQL 用 LIMIT，SQL Server/Oracle 用 OFFSET-FETCH |
| 优化大偏移量 | 使用 WHERE 或 JOIN 减少扫描行数 |
| 排序关键 | 必须搭配 ORDER BY，否则分页可能混乱 |
| 安全分页 | 使用参数化查询，避免SQL注入 |
最佳实践推荐：
- 使用
LIMIT #{pageSize} OFFSET #{offset}语法（更清晰）。 - 避免
LIMIT 100000, 20这样的深分页，改用WHERE id > last_id。 - 结合缓存（如Redis）存储热点分页数据，提升性能。
7. 进一步思考
- 无限滚动（Infinite Scroll） vs 传统分页：哪种更适合你的业务？
- 游标分页（Cursor Pagination）：适用于实时数据流（如Twitter、Facebook）。
- 分布式数据库分页：在分库分表环境下如何高效分页？
希望本文能帮助你掌握SQL分页的核心技术，并在实际项目中灵活运用！ �
浙公网安备 33010602011771号