MySQL排序與分頁：ORDER BY 與 LIMIT 深度解析

MySQL排序與分頁：ORDER BY 與 LIMIT 深度解析

ORDER BY與LIMIT是構建分頁、排行榜、最新列表等功能的核心組合。本文詳細講解語法、參數陷阱、性能優化及實戰技巧。

一、ORDER BY 排序詳解

1. 基本語法

SELECT column_list
FROM table_name
[WHERE condition]
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

• ASC：升序（默認），DESC：降序
• 支持多列排序：先按第一列排，相同值再按第二列排
• 可對選查列、未選列、表達式、別名、列位置編號排序

2. 實戰示例

-- 按ID降序（最新優先）
SELECT uid, username FROM user ORDER BY uid DESC;

-- 按注冊時間降序，取最新10名用戶
SELECT username FROM user ORDER BY regdate DESC LIMIT 10;

-- 多列排序：按部門升序，部門內按薪資降序
SELECT dept, name, salary FROM employee ORDER BY dept ASC, salary DESC;

二、LIMIT 分頁詳解

1. 語法形式

-- 形式一：指定偏移量和行數
LIMIT offset, row_count

-- 形式二：僅指定行數（偏移量默認0）
LIMIT row_count

• offset：起始行偏移量（從0開始）
• row_count：返回最大行數
• 兩者必須為非負整數

2. 分頁邏輯映射

3. 關鍵陷阱

• 不支持負數：LIMIT 9, -1會報錯，無法實現「從第10條到結尾」
• 大偏移量性能災難：LIMIT 1000000, 10需掃描前100萬行再丟棄，極其低效
• 無ORDER BY不確定性：無顯式排序時，結果順序不可預期（受存儲引擎、索引、並發影響）

三、性能優化核心策略

1. 索引優化排序

• 為ORDER BY列建立索引，避免filesort
• 複合索引列順序需匹配ORDER BY列順序及排序方向
• WHERE與ORDER BY列組合可用聯合索引

2. 深分頁優化方案

方案A：延遲關聯（適合InnoDB主鍵聚簇索引）

-- 1. 只查主鍵ID（覆蓋索引，極快）
SELECT id FROM huge_table ORDER BY id LIMIT 1000000, 10;

-- 2. 應用層拿到ID列表，再回表查詳情
SELECT * FROM huge_table WHERE id IN (id1, id2, ...);

方案B：游標/記憶最大值（適合遞增ID、時間戳）

-- 首頁
SELECT * FROM posts WHERE id > 0 ORDER BY id LIMIT 20;
-- 記錄最後一條ID = 100
-- 下一頁
SELECT * FROM posts WHERE id > 100 ORDER BY id LIMIT 20;

避免OFFSET，利用索引範圍查詢，性能穩定。

方案C：覆蓋索引+子查詢

SELECT * FROM huge_table 
WHERE id >= (SELECT id FROM huge_table ORDER BY id LIMIT 1000000, 1)
ORDER BY id LIMIT 10;

3. 單行查詢優化

當確定結果唯一（如登錄驗證），加LIMIT 1：

SELECT * FROM user WHERE username='admin' AND password='xxx' LIMIT 1;

命中第一條即停止掃描，顯著提升效率。

四、綜合實戰：高性能分頁查詢模板

<?php
$page  = max(1, intval($_GET['page']));
$size  = 20;
$offset = ($page - 1) * $size;

// 1. 總數查詢（可緩存、異步、近似值）
$total = $pdo->query("SELECT COUNT(*) FROM articles WHERE status=1")->fetchColumn();

// 2. 數據查詢（延遲關聯優化）
$ids = $pdo->query("
    SELECT id FROM articles 
    WHERE status=1 
    ORDER BY created_at DESC 
    LIMIT $offset, $size
")->fetchAll(PDO::FETCH_COLUMN);

if ($ids) {
    $placeholders = implode(',', array_fill(0, count($ids), '?'));
    $stmt = $pdo->prepare("
        SELECT id, title, summary, created_at 
        FROM articles 
        WHERE id IN ($placeholders) 
        ORDER BY created_at DESC
    ");
    $stmt->execute($ids);
    $articles = $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// 3. 輸出分頁信息
$totalPages = ceil($total / $size);
// ... 渲染分頁導航
?>

五、總結速查表

掌握ORDER BY與LIMIT的底層機制與優化模式，是構建流暢用戶體驗、降低數據庫負載的關鍵技能。