MySQL 索引優化實戰：ORDER BY ... LIMIT 掃描優化兩大案例

核心原理

利用索引有序性，優化器沿索引順序掃描，掃滿 LIMIT 行即停止，避免全表排序（Using filesort）。

案例一：單表篩選 + 排序分頁

原始狀況

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

• 索引 ind_hot_endtime(end_time, count_num)
• 篩選後 113,549 行需排序，耗時 0.49 秒

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

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

• 優化器選擇新索引，type=INDEX，rows=48（估算掃 48 行即得 12 行）
• 耗時：0.00 秒（毫秒級）

案例二：多表 LEFT 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 = 'regeion_xx'
ORDER BY b.instance_no ASC LIMIT 37300, 50;

• 原索引 idx_uid_stat_inid(user_id, status, instance_no)
• STATUS IN (1,8) 導致無法用 instance_no 排序，觸發 filesort
• 耗時 1.28 秒

優化：新增支援排序的索引 + 強制使用

ALTER TABLE instance ADD INDEX ind_user_id(user_id, instance_no);
-- 需 FORCE INDEX(ind_user_id) 才會用上

• 掃描行數 49,212 → 54,580（估算值）
• 耗時：0.49 秒（提升 62%）

總結與避坑指南

關鍵判斷：篩選選擇性高 → 排序成本低 → filesort 可接受；選擇性低 → 索引掃描 + LIMIT 極優。