MySQL 表設計優化與索引（七）：避免磁碟臨時表策略、TEXT/BLOB 限制與 ORDER BY SUBSTRING 優化技巧

磁碟臨時表效能殺手

MySQL 查詢優化器在處理 GROUP BY、ORDER BY、DISTINCT、UNION、子查詢、JOIN 等操作時，可能建立內部臨時表。預設優先嘗試使用 Memory 引擎（記憶體臨時表），但以下情況會強制降級為 MyISAM 磁碟臨時表，導致效能劇烈下降：

• 查詢包含 TEXT、BLOB、GEOMETRY、JSON（舊版）欄位
• VARCHAR/VARBINARY 超過閾值（max_heap_table_size/tmp_table_size）
• GROUP BY/ORDER BY 欄位來自不同表（JOIN 後）
• 使用 DISTINCT 配合 ORDER BY 且欄位不一致
• UNION / UNION ALL 涉及大欄位

Memory 引擎限制根因

• Memory 引擎不支援可變長度行，將 VARCHAR 轉為定長 CHAR、TEXT/BLOB 無法儲存
• 行大小超過 max_heap_table_size（預設 16MB）或 tmp_table_size（預設 16MB，取較小者）即轉磁碟
• 磁碟臨時表使用 MyISAM，無交易、表鎖、IO 密集，效能差數個數量級

核心優化策略：ORDER BY SUBSTRING 技巧

當無法避免查詢 TEXT/BLOB 欄位，但需對其排序/分組時，可將長字串截取為定長前綴，使查詢符合 Memory 引擎限制：

-- 原查詢：強制磁碟臨時表
SELECT id, content FROM articles ORDER BY content LIMIT 10;

-- 優化：截取前 50 字元排序（假設前綴區分度足夠）
SELECT id, content FROM articles ORDER BY SUBSTRING(content, 1, 50) LIMIT 10;

-- 進階：覆蓋索引配合
ALTER TABLE articles ADD INDEX idx_content_prefix (content(50));
SELECT id, content FROM articles ORDER BY content(50) LIMIT 10;

關鍵參數與驗證

EXPLAIN 診斷磁碟臨時表

EXPLAIN SELECT id, content FROM articles ORDER BY content LIMIT 10\G

# 關鍵輸出：
# Extra: Using temporary; Using filesort
# 含 "Using temporary" → 使用隱式臨時表
# 若同時有 "Using filesort" → 需額外排序（通常伴隨臨時表）

完整避免磁碟臨時表檢查清單

1. 欄位類型審查：SHOW CREATE TABLE 確認無 TEXT/BLOB 參與排序/分組/去重
2. 長度評估：VARCHAR(N) 實際平均長度 × 行數 < tmp_table_size
3. 索引支撐：ORDER BY/GROUP BY 欄位有索引 → 可避免臨時表（索引本身有序）
4. SQL 改寫：
   • UNION → UNION ALL + 應用層去重
   • 子查詢 → JOIN 或 EXISTS
   • DISTINCT + ORDER BY 分離
5. 參數調大：tmp_table_size/max_heap_table_size 設 64M~256M（視記憶體）
6. 升級引擎：MySQL 8.0.16+ 使用 TempTable 引擎支援可變長度，大幅降低落盤率
7. MariaDB 優勢：ColumnStore/Spider 引擎、MEMORY 引擎改進，對臨時表處理更優

實戰案例：電子郵件內容搜尋排序

-- 問題：content 為 MEDIUMTEXT，ORDER BY content 強制磁碟臨時表
-- 方案 1：只排序主旨（VARCHAR）
SELECT id, subject, content FROM emails ORDER BY subject LIMIT 20;

-- 方案 2：截取內容前 100 字排序
SELECT id, subject, content FROM emails ORDER BY SUBSTRING(content, 1, 100) LIMIT 20;

-- 方案 3：全文搜尋引擎（Elasticsearch/Meilisearch）替代
-- 方案 4：生成摘要欄位 summary VARCHAR(200) 定期同步，排序用 summary

磁碟臨時表是查詢效能的隱形殺手。通過欄位類型審查、長度控制、索引支撐、SQL 改寫、參數調優、引擎升級多維度組合拳，可將絕大多數查詢控制在記憶體臨時表範圍內。MySQL 8.0 TempTable 引擎的引入，從架構層面緩解了此痛點，升級是長期最佳解法。

1. 上一篇文章： MySQL 表設計優化與索引（六）：MVCC 多版本並發控制核心機制、InnoDB 實現細節與隔離級別影響
2. 下一篇文章： MySQL 表設計優化與索引（九）：日期時間類型選型指南、DATETIME vs TIMESTAMP 深度對比與高精度儲存方案

MySQL 資料庫相關知識

JMeter 連接 MySQL 資料庫壓測圖文教程：JDBC 配置與 SQL 取樣器

前言：JMeter 做資料庫壓測，JDBC Request

MySQL 服務啟動報錯 1067 根因排查：端口佔用與進程衝突實戰

錯誤現象 Windows could not start t

MySQL 入門系列：客戶端程式 4 — 執行期動態取得連線參數

從硬編碼到執行期參數 客戶端程式 3 雖具模組化優勢，但連線

Debian/Ubuntu 系統 MySQL 8.0 徹底解決中文亂碼終極配置指南

現象確認：為什麼會亂碼？ STATUS; ... Serv