MySQL 憑藉體積輕量、速度快、開源免費等特性，成為中小型專案首選資料庫。但隨著資料量增長，插入與查詢這兩大最高頻操作的效能瓶頸逐漸浮現。本文系統梳理生產環境驗證過的優化策略，助你快速定位並解決效能痛點。

一、插入優化：十大核心策略

1. 批量載入：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);

適用於海量日誌、ETL 初始化等場景；需確保 local_infile=1 且檔案路徑對 MySQL 服務可讀。

2. 多值 INSERT 合併執行

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

單條 INSERT 需反覆建立連接、解析、執行、提交；多值版本僅一次網路往返，效能提升數倍。

3. 優先選用 VARCHAR 而非 CHAR

CHAR 固定長度補空格，佔用更多磁碟與記憶體，導致 I/O 放大；實測無論是否建立索引，VARCHAR 插入效率均顯著優於 CHAR。

4. 控制欄位長度，「剛好夠用」原則

越小的資料型別，磁碟讀取與記憶體打包效率越高；但預留擴充空間，避免日後業務變更導致 ALTER TABLE 鎖表風險。

5. NOT NULL + DEFAULT 取代 NULL

NULL 需額外位元圖標記、特殊索引邏輯與判斷碼，增加 CPU 開銷。設定 NOT NULL DEFAULT 0 或 '' 可簡化儲存與查詢邏輯。

6. 視業務一致性要求，權衡事務引擎

CREATE TABLE t (...) ENGINE=MyISAM;  -- 無事務，插入極快
CREATE TABLE t (...) ENGINE=InnoDB;  -- 支援事務，犧牲部分速度換取 ACID

純日誌、報表類資料可考慮 MyISAM；核心業務務必用 InnoDB。

7. 大量匯入前禁用索引與唯一檢查

ALTER TABLE t DISABLE KEYS;   -- 僅 MyISAM 有效
SET UNIQUE_CHECKS=0;
-- 執行大量 INSERT
SET UNIQUE_CHECKS=1;
ALTER TABLE t ENABLE KEYS;

InnoDB 不支援 DISABLE KEYS，可改用 SET FOREIGN_KEY_CHECKS=0 並按主鍵順序匯入以減少頁分裂。

8. 顯式鎖表減少索引緩衝刷新

LOCK TABLES t WRITE;
INSERT ...;  -- 批次插入
UNLOCK TABLES;

若已使用多值 INSERT，通常無需額外鎖表。

9. 啟用 MyISAM 並行插入

設定 concurrent_insert=2 允許尾部並行插入，即使中間有刪除空洞也不阻塞。

10. 延遲插入 INSERT DELAYED（MyISAM 專用）

客戶端立即返回，伺服器排隊寫入，適合非即時一致性場景（如日誌收集），並能將多客戶端寫入合併為單一塊，大幅降低 I/O。

二、查詢優化：七大核心原則

1. 資料型別極簡化

查詢與插入同理：避免 NULL、選用最小足夠型別、VARCHAR 替代 CHAR。

2. 連接查詢優於子查詢

MySQL 優化器對 JOIN 成熟度高於子查詢；確保被驅動表關聯欄位有索引，驅動表通常走全表掃描（Nested Loop）。

3. 索引策略：精準建立、定期清理

• 高頻 WHERE、JOIN、ORDER BY 欄位建索引。
• 複合索引遵循「最左前綴」與「區分度高者前置」。
• 定期 ANALYZE TABLE 更新統計資訊，清理未用索引。

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

InnoDB 聚簇索引即主鍵，有序自增主鍵可避免頁分裂，支援叢集、分區等高階架構。

5. 善用 EXPLAIN 定位瓶頸

關注 type（目標 ref/range 以上）、key、rows、Extra（避免 Using filesort/Using temporary）。

6. 杜絕 SELECT *

只取所需欄位：減少網路傳輸、降低記憶體佔用、利用覆蓋索引避免回表。

7. 關鍵參數調優（以 8GB 記憶體為例）

參數	建議值	說明
key_buffer_size	2G (25% RAM)	MyISAM 索引緩衝，InnoDB 用 innodb_buffer_pool_size
read_buffer_size	16M	全表掃描緩衝，過大反增 CPU
table_open_cache	4000	配合 max_connections，避免頻繁開關表
long_query_time	1	慢查詢閾值（秒），配合 slow_query_log 定位問題 SQL

8. 單行查詢加 LIMIT 1

命中第一筆即停止掃描，避免全表走訪。

結語

優化無終點，唯有「監控→分析→調整→驗證」的閉環實踐。建議搭配 pt-query-digest、sys schema、Grafana + Prometheus 等工具建立可觀測體系，讓每一次調優都有據可依。