MySQL 索引優化實戰：ORDER BY LIMIT 場景深度解析

MySQL 索引優化實戰：ORDER BY LIMIT 場景深度解析

ORDER BY ... LIMIT M 是 SQL 優化常見場景，核心原理是利用索引有序性，優化器沿著索引順序掃描，掃到滿足條件的 M 行即停止。但實務中常見未善用此特性導致效能不佳，以下透過兩個實際案例說明。

案例一：單表排序分頁優化

問題 SQL

SELECT * FROM test_order_desc 
WHERE END_TIME > NOW() 
ORDER BY GMT_CREATE DESC, count_num DESC 
LIMIT 12, 12;

執行時間：0.49 秒，掃描 113,549 行，出現 Using filesort。

執行計劃分析

EXPLAIN 结果：
type=range, key=ind_hot_endtime, rows=113549, Extra=Using where; Using filesort

現有索引 ind_hot_endtime(end_time, count_num) 可用於過濾，但無法支援 ORDER BY gmt_create, count_num，導致大量資料需在記憶體/磁碟排序。

優化策略：新增符合排序順序的索引

ALTER TABLE test_order_desc 
ADD INDEX ind_gmt_create(gmt_create, count_num);

優化後效果

• 執行時間：**< 0.001 秒（毫秒級）**
• 執行計劃：type=index, key=ind_gmt_create, rows=48, Extra=Using where
• 優化器選擇新索引掃描，避免排序，預估掃描 48 行即取得結果

案例二：多表 JOIN 複雜排序優化

問題 SQL

SELECT b.*, a.*, k.* 
FROM instance b 
LEFT JOIN image a ON b.image_id = a.image_id
LEFT JOIN key_pair k ON b.key_pair_id = k.key_pair_id
LEFT JOIN region_alias r_a ON r_a.region_no = b.region_no
WHERE b.STATUS IN (1,8) 
  AND b.user_id = 21 
  AND r_a.big_region_no = 'region_xx'
ORDER BY b.instance_no ASC 
LIMIT 37300, 50;

執行時間：**1.28 秒**

執行計劃問題

• 連接順序：b → r_a → a → k
• 驅動表 b 掃描 49,212 行
• STATUS IN (1,8) 導致 instance_no 索引失效，產生臨時表排序

優化：新增符合排序需求的索引

ALTER TABLE instance 
ADD INDEX ind_user_id(user_id, instance_no);

強制使用新索引

EXPLAIN SELECT ... 
FROM instance b FORCE INDEX (ind_user_id) 
LEFT JOIN ... 
ORDER BY b.instance_no ASC LIMIT 37300, 50;

結果對比

總結與限制

• 適用場景：查詢列選擇性低、需大量排序、配合 LIMIT 取少量資料
• 限制：優化器可能不選擇預期索引（考量過濾性與 LIMIT 長度）；選擇性高時排序成本低，反而不需此技巧
• 核心心法：索引設計要同時滿足 WHERE 過濾與 ORDER BY 排序，配合 LIMIT 早期終止掃描