基本語法

SELECT column_name(s)
FROM table_name
ORDER BY column_name [ASC|DESC];

• ASC（默認）：升序（1→9, a→z）
• DESC：降序（9→1, z→a）
• 不區分大小寫：ORDER BY = order by

多列排序

SELECT * FROM person
ORDER BY age ASC, lastname DESC;
-- 先按 age 升序，age 相同再按 lastname 降序

PHP + MySQL 完整示例

<?php
$conn = new mysqli("localhost", "user", "pass", "my_db");
if ($conn->connect_error) die("Connection failed: " . $conn->connect_error);

$sql = "SELECT FirstName, LastName, Age FROM person ORDER BY Age";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo $row["FirstName"] . " " . $row["LastName"] . " " . $row["Age"] . "<br>";
    }
} else {
    echo "0 results";
}
$conn->close();
?>

輸出：

Glenn Quagmire 33
Peter Griffin 35

索引優化關鍵點

場景	索引建議	說明
單列 ORDER BY	該列建立索引	避免 filesort
多列 ORDER BY a,b	聯合索引 (a,b)	順序必須一致，方向最好一致
WHERE + ORDER BY	聯合索引 (where_col, order_col)	先過濾後排序
ORDER BY col DESC	MySQL 8.0 支持降序索引	5.7 需檢查是否用到索引

EXPLAIN 關鍵列

• Using filesort：未用到索引排序，需優化
• Using index：覆蓋索引，最理想
• Using index; Using filesort：覆蓋索引但仍需排序

常見陷阱

1. ORDER BY RAND()：全表掃描，大表禁用（見 優化方案）
2. ORDER BY col LIMIT 100000, 10：深分頁性能差，改用 WHERE id > last_id ORDER BY id LIMIT 10
3. 字符集不一致導致隱式轉換，索引失效