MySQL 5.0 優化系列（四）：ORDER BY、LIMIT、全表掃描、DML 性能深度解析

前言

本文基於 MySQL 5.0 官方手冊優化章節，系統梳理 ORDER BY 索引優化、LIMIT 執行機制、全表掃描規避、INSERT/UPDATE/DELETE 加速策略 及其他進階優化技巧，為 SQL 調優提供實戰指南。

一、ORDER BY 如何利用索引避免 Filesort

✅ 可用索引的場景

-- 1. 索引順序完全匹配
SELECT * FROM t1 ORDER BY key_part1, key_part2;

-- 2. WHERE 固定前綴，ORDER BY 後綴
SELECT * FROM t1 WHERE key_part1 = const ORDER BY key_part2;

-- 3. DESC 方向一致
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;

核心原則：WHERE 條件中的常量等值匹配「佔用」了索引前綴，剩餘有序列可直接服務 ORDER BY。

❌ 無法用索引的場景（觸發 Filesort）

• ORDER BY 列來自不同索引：ORDER BY key1, key2
• 索引列不連續：WHERE key2=const ORDER BY key_part2（key_part1 缺失）
• 混合 ASC/DESC：ORDER BY key1 DESC, key2 ASC
• WHERE 與 ORDER BY 使用不同索引：WHERE key2=const ORDER BY key1
• 多表 JOIN 且 ORDER BY 非首表常量表列
• HASH/HEAP 表（無序存儲）

🔍 診斷手段

EXPLAIN SELECT ... ORDER BY ...;
-- Extra 欄顯示 "Using filesort" 即未用索引

⚙️ Filesort 兩代算法對比

🚀 加速 Filesort 參數調優

• sort_buffer_size：增大排序緩存（如 2M → 8M）
• read_rnd_buffer_size：增大隨機讀緩存
• tmpdir：指向高速磁盤/多盤輪詢（tmpdir=/disk1:/disk2:/disk3）

二、LIMIT 優化特性

• LIMIT n + ORDER BY：找到 n 行即停止排序，避免全表排序
• LIMIT n + DISTINCT：找到 n 個唯一值即停止掃描
• GROUP BY + LIMIT：按索引序讀取時，僅計算必要組
• LIMIT 0：快速獲取列類型/驗證語法，返回空集
• SQL_CALC_FOUND_ROWS：需掃描全量才能獲取總行數，慎用

三、規避全表掃描（type=ALL）

觸發條件

1. 極小表（<10 行且短行長）
2. 無可用索引
3. 常量覆蓋率高（優化器判斷掃描更快）
4. 低基數索引（匹配行數過多）

干預手段

-- 1. 更新統計信息
ANALYZE TABLE tbl_name;

-- 2. 強制使用索引
SELECT * FROM t1 FORCE INDEX (idx_col) WHERE col = ...;

-- 3. 調整優化器預期
SET max_seeks_for_key = 1000; -- 或啟動參數 --max-seeks-for-key=1000

四、INSERT/UPDATE/DELETE 加速策略

INSERT 耗時分解

連接(3) + 發送查詢(2) + 解析(2) + 插入記錄(1×行大小) + 更新索引(1×索引數) + 關閉(1)

加速技巧

1. 批量 VALUES：INSERT INTO t VALUES (1),(2),(3)... 比單條快數倍
2. LOAD DATA INFILE：比批量 INSERT 快 20 倍
3. 大量導入優化流程：

CREATE TABLE t ...;
FLUSH TABLES;
myisamchk --keys-used=0 -rq /path/to/t;  -- 刪除索引
LOAD DATA INFILE 'data.txt' INTO TABLE t;
myisamchk -r -q /path/to/t;              -- 重建索引（內存排序，極快）
FLUSH TABLES;
# MySQL 4.0+ 可用 ALTER TABLE t DISABLE/ENABLE KEYS 替代 myisamchk

4. 顯式鎖表：LOCK TABLES t WRITE; INSERT ...; UNLOCK TABLES; 減少索引刷盤次數
5. 事務表用 BEGIN/COMMIT 替代鎖表
6. 調大 key_buffer_size（MyISAM）

UPDATE 優化

• 同 SELECT 優化（索引、WHERE）
• 延遲批量更新 + 鎖表
• 動態行格式頻繁增長 → 定期 OPTIMIZE TABLE 整理碎片

DELETE 優化

• 刪除全表：TRUNCATE TABLE（DDL，無事務日志，極快）
• 增大 key_buffer_size 加速索引定位

五、其他進階優化點子

• 持久連接/線程池：減少握手開銷，調大 thread_cache_size
• EXPLAIN 常態化：所有上線 SQL 必過 EXPLAIN
• 讀寫分離：MyISAM 無刪除時支持並發插入
• 表重排序：ALTER TABLE ... ORDER BY col1, col2 物理順序匹配高頻查詢
• 哈希列替代多列索引：hash_col=MD5(CONCAT(col1,col2))
• 拆分表場景：動態轉固定長、頻繁全掃描但少取列
• 摘要表/物化視圖：統計查詢預計算
• 默認值利用：INSERT 時省略默認列
• BLOB 存儲非結構化數據：應用層序列化/反序列化
• 適度反范式：冗余列換查詢速度
• 存儲過程/UDF：邏輯下推
• 查詢緩存 + 批量寫入：鎖表合併刷盤
• INSERT DELAYED / LOW_PRIORITY / HIGH_PRIORITY：優先級控制
• 定期 OPTIMIZE TABLE：防碎片
• HEAP/MEMORY 表：熱點數據內存級訪問
• 圖片存文件系統，庫存路徑：Web Server 緩存更優
• 非關鍵高頻數據用 MEMORY 表：如 Session
• JOIN 列類型長度完全一致：避免隱式轉換
• 底層存儲引擎接口：MyISAM 直接訪問比 SQL 接口快 2-5 倍（單進程、本地、無鎖）
• 數值優於文本：存儲緊湊、無需解析
• 主從復制分流讀寫：Slave 掛 --skip-innodb --delay-key-write=ALL 提速
• DELAY_KEY_WRITE=1：MyISAM 索引延遲寫入（配合 --myisam-recover 保安全）

總結

MySQL 優化無銀彈，核心是 「用索引替代掃描、用順序替代隨機、用批量替代單條、用內存替代磁盤」。結合 EXPLAIN、SHOW PROFILE、Performance Schema 量化分析，針對具體業務場景組合參數調優與 SQL 重寫，方能獲得穩定收益。