MySQL 分頁查詢詳解
MySQL 中分頁查詢的核心是 LIMIT 子句，用於限制查詢結果返回的行數，適用於海量數據場景下的分批加載（如列表分頁、滾動加載）。以下是完整的用法、優化方案和注意事項：
一、基礎語法（LIMIT 子句）
1. 語法格式
-- 方式1：LIMIT 偏移量, 每頁條數（偏移量從0開始）
SELECT 字段列表 FROM 表名 [WHERE 條件] [ORDER BY 排序字段] LIMIT 偏移量, 每頁條數;
-- 方式2：LIMIT 每頁條數 OFFSET 偏移量（與方式1等價，更易讀）
SELECT 字段列表 FROM 表名 [WHERE 條件] [ORDER BY 排序字段] LIMIT 每頁條數 OFFSET 偏移量;
2. 核心參數説明
- 偏移量：表示從第幾條數據開始查詢（默認從 0 開始，即第一條數據偏移量為 0）。
- 每頁條數：表示本次查詢返回的最大行數。
3. 基礎示例
假設表 user 有 100 條數據，需實現“每頁 10 條”的分頁：
-- 第1頁（偏移量0，取前10條）
SELECT id, name, age FROM user ORDER BY id LIMIT 0, 10;
-- 等價於
SELECT id, name, age FROM user ORDER BY id LIMIT 10 OFFSET 0;
-- 第2頁（偏移量10，從第11條開始取10條）
SELECT id, name, age FROM user ORDER BY id LIMIT 10, 10;
-- 第n頁通用公式：偏移量 = (n-1) * 每頁條數
-- 第5頁（每頁10條）：(5-1)*10=40
SELECT id, name, age FROM user ORDER BY id LIMIT 40, 10;
4. 簡化寫法（僅取前 N 條）
如果只需取前 N 條數據（無需偏移），可省略偏移量：
-- 取前5條數據（等價於 LIMIT 0, 5）
SELECT id, name FROM user LIMIT 5;
二、分頁查詢的關鍵注意事項
1. 必須搭配 ORDER BY，避免數據重複/缺失
問題：如果不加 ORDER BY，MySQL 會隨機返回數據，導致分頁時同一數據出現在不同頁，或部分數據漏查。 解決：強制按唯一字段排序（如主鍵 id、唯一索引字段），確保結果順序穩定。
-- 錯誤：無 ORDER BY，分頁結果不穩定
SELECT * FROM user LIMIT 10, 10;
-- 正確：按主鍵 id 排序（唯一且有序）
SELECT * FROM user ORDER BY id LIMIT 10, 10;
2. 處理邊界情況（不足一頁時）
當最後一頁數據不足“每頁條數”時，LIMIT 會自動返回剩餘所有數據，無需額外處理：
-- 若總數據105條，第11頁（偏移量100），僅返回5條
SELECT * FROM user ORDER BY id LIMIT 100, 10;
3. 避免 OFFSET 過大導致性能問題
問題場景
當分頁頁碼較大時（如第 1000 頁，LIMIT 9990, 10），MySQL 會先掃描前 9990 條數據並丟棄，僅返回後 10 條，效率極低（掃描行數多）。
優化方案（適用於有連續唯一索引的場景）
利用 索引過濾 替代 OFFSET，直接定位到分頁起點，減少掃描行數：
-- 傳統低效寫法（OFFSET 9990，掃描前9990條）
SELECT id, name FROM user ORDER BY id LIMIT 9990, 10;
-- 優化寫法（利用主鍵索引，直接定位到 id > 9990 的數據）
SELECT id, name FROM user WHERE id > 9990 ORDER BY id LIMIT 10;
適用條件：排序字段是唯一索引（如主鍵、唯一鍵），且分頁時能記錄上一頁的最後一個值（如 last_id=9990）。
三、複雜場景分頁（多條件+關聯查詢）
1. 帶 WHERE 條件的分頁
-- 查詢年齡 > 18 的用户，第2頁（每頁10條）
SELECT id, name, age FROM user
WHERE age > 18
ORDER BY id
LIMIT 10, 10;
2. 多表關聯分頁（JOIN 場景）
核心：先確定主表的分頁數據，再關聯其他表（避免關聯後的數據量膨脹導致分頁錯誤）。
-- 主表 user，關聯表 order，查詢用户及訂單信息（第2頁）
SELECT u.id, u.name, o.order_no
FROM user u
LEFT JOIN `order` o ON u.id = o.user_id
WHERE u.age > 18
ORDER BY u.id
LIMIT 10, 10;
-- 更高效的寫法（先分頁主表，再關聯）
SELECT u.id, u.name, o.order_no
FROM (
SELECT id, name FROM user WHERE age > 18 ORDER BY id LIMIT 10, 10
) u
LEFT JOIN `order` o ON u.id = o.user_id;
四、MySQL 8.0+ 新特性：FETCH NEXT（標準SQL語法）
MySQL 8.0 支持標準 SQL 的 OFFSET ... FETCH NEXT 語法，功能與 LIMIT 一致，可讀性更強：
-- 第2頁（偏移量10，取10條）
SELECT id, name FROM user
ORDER BY id
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
-- 等價於 LIMIT 10, 10
五、分頁查詢優化總結
| 場景 | 優化方案 | 適用條件 |
|---|---|---|
| 小頁碼分頁（<100頁） | 直接使用 LIMIT 偏移量, 每頁條數 |
數據量小，無需複雜優化 |
| 大頁碼分頁（>100頁） | 利用唯一索引過濾（WHERE id > last_id） |
排序字段是唯一索引，可記錄上頁尾值 |
| 關聯查詢分頁 | 先分頁主表，再關聯其他表 | 多表JOIN，避免數據量膨脹 |
| 統計總頁數 | 避免 COUNT(*)（用緩存/近似值），或延遲查詢 |
總數據量極大時，優先返回分頁數據 |
統計總頁數的技巧（避免低效 COUNT(*)）
分頁時通常需要顯示“總頁數”，但 COUNT(*) 在大表中效率低，可優化：
-- 低效：全表掃描統計總條數
SELECT COUNT(*) FROM user WHERE age > 18;
-- 優化1：使用近似值（MySQL 8.0+，誤差極小）
SELECT TABLE_ROWS FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '數據庫名' AND TABLE_NAME = 'user';
-- 優化2：緩存總條數（如Redis），定時更新
-- 優化3：延遲查詢總頁數（先返回分頁數據，再異步統計總條數）
六、常見錯誤避坑
- 偏移量計算錯誤：偏移量從 0 開始，第 n 頁的偏移量是
(n-1)*每頁條數（而非n*每頁條數）。 - 無 ORDER BY 導致分頁混亂：必須按唯一字段排序，確保結果順序穩定。
- 關聯查詢分頁順序錯誤：先關聯後分頁會導致數據重複（如一對多關聯），應先分頁主表再關聯。
- 大頁碼使用 OFFSET：OFFSET 過大時掃描行數多，優先用索引過濾替代。
