MySQL 效能優化全攻略：從插入到查詢的實戰技巧

前言

MySQL 憑藉體積輕巧、速度極快且開源免費的特性，成為廣大中小型專案的首選資料庫。然而，隨著資料量增長，如何壓榨效能成為開發者必修的課題。本文將系統梳理 MySQL 中最頻繁的兩大操作——插入（INSERT）與查詢（SELECT）的優化策略，助你輕鬆應對高併發場景。

一、 插入優化：十大黃金法則

1. 善用 LOAD DATA INFILE 批量導入

對於大量文字檔資料，LOAD DATA INFILE 的速度比逐條 INSERT 快 20 倍以上。範例：

LOAD DATA LOCAL INFILE 'C:/data/news.txt' INTO TABLE news
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\r\n'
(content, date, ip, author, subject);

2. 合併多行 INSERT，減少網路交互

單條 SQL 插入多行資料，可大幅降低連線開銷：

INSERT INTO food VALUES
(NULL, 'EE果凍', 'EE果凍廠', 1.5, '2007', 2, '北京'),
(NULL, 'FF咖啡', 'FF咖啡廠', 20, '2002', 5, '天津'),
(NULL, 'GG奶糖', 'GG奶糖廠', 14, '2003', 3, '廣東');

3. 優先選擇 VARCHAR 而非 CHAR

VARCHAR 按實際長度儲存，CHAR 會補齊空白。實測無論是否建立索引，CHAR 插入效率顯著低於 VARCHAR。

4. 精準控制欄位長度

越小的資料型別佔用空間越少，磁碟 I/O 與記憶體打包效率越高。但需預留業務擴展空間，避免日後修改表結構的高昂成本。

5. 定義 NOT NULL + DEFAULT

NULL 需額外的判斷邏輯與特殊索引處理。將欄位設為 NOT NULL 並給予預設值（如 0 或空字串），能簡化引擎運算。

6. 權衡事務與效能

InnoDB 支援事務，MyISAM 不支援。啟用事務會帶來額外開銷，但能保證資料一致性。若業務允許（如日誌、快照表），可考慮使用 MyISAM 或關閉事務以換取極致寫入速度。

7. 大量匯入前禁用索引

插入時需維護索引排序。可先執行 ALTER TABLE tbl DISABLE KEYS;，資料載入完畢後再 ENABLE KEYS 重建，效能提升明顯（僅限 MyISAM）。

8. 關閉唯一性檢查

執行 SET UNIQUE_CHECKS=0; 暫停唯一約束檢查，匯入完成後恢復 SET UNIQUE_CHECKS=1;。

9. 鎖表插入，減少索引緩衝刷新

使用 LOCK TABLES tbl WRITE; 獨佔鎖定，所有插入完成後一次性刷新索引緩衝區至磁碟。若已合併為單條 INSERT，則無需額外鎖表。

10. 啟用並行插入

MyISAM 支援 concurrent_insert 參數（預設 1）。設為 2 允許在表尾並行插入，即使中間有刪除空洞也能利用。

11. 延遲插入 INSERT DELAYED

客戶端無需等待插入完成時適用。伺服器立即返回，資料排隊寫入，多客戶端插入會合併為同一塊寫入，大幅提升吞吐量。

二、 查詢優化：八大核心策略

1. 優化資料型別基礎

查詢優化同樣遵循「拒絕 NULL、選用小型別」原則，減少 I/O 與 CPU 運算量。

2. 以 JOIN 取代子查詢

MySQL 優化器對 JOIN 成熟度遠高於子查詢。確保被關聯表的關聯欄位有索引，驅動表通常會全表掃描，屬嵌套迴圈演算法一環。

3. 索引：雙面刃，用好才是王道

索引能加速關聯與檢索，但寫入會產生維護成本。原則：僅在頻繁搜尋、關聯、排序的欄位建立索引。

4. 每張表必備 INT UNSIGNED AUTO_INCREMENT 主鍵

叢集索引、分區、主從同步等核心機制高度依賴主鍵。整數自增主鍵能保證寫入順序，減少頁分裂。

5. 善用 EXPLAIN 解析執行計畫

在 SELECT 前加 EXPLAIN，可洞悉索引使用情況、掃描行數、排序方式等，快速定位瓶頸。

6. 杜絕 SELECT *

只取需要的欄位，減少網路傳輸與記憶體佔用，尤其在資料庫與應用分離架構下效果顯著。

7. 關鍵系統參數調優

• key_buffer_size：索引緩衝區，建議設為實體記憶體 25% 左右。
• read_buffer_size：全表掃描緩衝，適當增大可加速順序讀取。
• table_open_cache：配合 max_connections 設定，避免頻繁開關表。
• long_query_time：慢查詢閾值，配合慢查詢日誌發現低效 SQL。

8. 明確需求時加 LIMIT 1

已知只需一筆資料時，LIMIT 1 讓引擎命中即停，避免繼續掃描。

結語

MySQL 優化博大精深，以上僅是管中窺豹。實戰中需結合具體業務、資料分佈、硬體資源持續觀測、調整、驗證。願諸君在優化之路上越走越遠！