MySQL 優化總結：插入與查詢兩大核心場景 20 條實戰策略

MySQL 優化聚焦最高頻兩大操作：插入 (寫入) 與 查詢 (讀取)。本文彙總 20 條經驗驗證策略，含大量導入、批次寫入、索引控制、事務鎖、分頁優化、配置調優等，可直接落地執行。

一、插入優化 11 條

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);

2. 批次 INSERT 多值： 單條語句多組 VALUES，減少網路往返與解析開銷。

   INSERT INTO food VALUES
   (NULL,'A','FactoryA',1.5,'2007',2,'Beijing'),
   (NULL,'B','FactoryB',20,'2002',5,'Tianjin'),
   (NULL,'C','FactoryC',14,'2003',3,'Guangdong');

3. 優先 VARCHAR 而非 CHAR： 變長儲存實際長度，CHAR 補空格浪費空間與 IO，實測 VARCHAR 插入更快。
4. 控制欄位長度： 足夠用多大定多大，INT 固定 4 Bytes，VARCHAR(20) 夠用別定 255，但預留業務擴展空間。
5. NOT NULL + DEFAULT： NULL 需額外 bitmap、索引不存 NULL、應用判空麻煩。預設 0/空串/特殊值。
6. 非核心表禁用事務 (MyISAM/InnoDB 關閉事務)： 理論上無事務快，但犧牲 ACID。ENGINE=MyISAM 或 SET autocommit=1 單條自動提交。核心業務嚴禁。
7. 大量導入前禁用索引： ALTER TABLE t DISABLE KEYS; (僅 MyISAM 有效，InnoDB 忽略)。導入後 ENABLE KEYS 重建。
8. 禁用唯一性檢查： SET UNIQUE_CHECKS=0; 導入 → SET UNIQUE_CHECKS=1; 重開。InnoDB 有效，大幅減少唯一索引檢查開銷。
9. 鎖表批次插入： LOCK TABLES t WRITE; INSERT ...; UNLOCK TABLES; 索引緩衝區僅刷盤一次。單條大 INSERT 無需顯式鎖。
10. 啟用並行插入： concurrent_insert=2 (MyISAM)，表尾並行插入即使有刪除空洞。
11. 延遲插入： INSERT DELAYED (MyISAM/5.6+ 廢棄)，客戶端立即返回，伺服器排隊寫入。適用日誌/統計類非即時業務。

二、查詢優化 9 條

1. 優化資料類型： 同插入原則，小類型、NOT NULL、數值優於字串。
2. JOIN 代替子查詢： 優化器處理 JOIN 更成熟，確保被驅動表關聯欄位有索引，驅動表通常全表掃描 (Nested Loop)。
3. 索引策略： 高頻 WHERE/JOIN/ORDER BY/GROUP BY 建索引、組合索引遵循最左前綴、覆蓋索引消除回表、低基數不建、定期 OPTIMIZE TABLE 整理碎片。
4. 每表必有主鍵 (INT UNSIGNED AUTO_INCREMENT)： InnoDB 聚簇索引即主鍵，二級索引存主鍵值，主鍵設計直接影響所有索引大小與查找效率。
5. EXPLAIN 分析慢查詢： 關注 type、key、rows、Extra，定位全表掃描、臨時表、排序、索引未命中。
6. 禁止 SELECT *： 只取必要欄位，減少網傳、利用覆蓋索引、結構變更不影響應用。
7. 關鍵配置調優：

   參數	建議	監控指標
   key_buffer_size	記憶體 25% (MyISAM)	Key_reads/Key_read_requests
   innodb_buffer_pool_size	記憶體 60-80%	Innodb_buffer_pool_reads/Requests
   read_buffer_size	256K-4M	全表掃描耗時
   table_open_cache	max_connections * N	Opened_tables/Uptime
   long_query_time	1-2 秒	慢查詢日誌分析

8. LIMIT 1 優化單行查詢： 找到一條即停止，避免繼續掃描。SELECT * FROM t WHERE status=1 LIMIT 1
9. 分頁優化兩大模式 (見開發規範 24 條)：
   • 模式一 (小結果/複雜條件)：直接 LIMIT
   • 模式二 (大結果/有覆蓋索引)：子查詢取 ID + JOIN 回表，每頁耗時恆定

三、實戰檢查清單 (上線前必驗)

1. 慢查詢日誌開啟 (long_query_time=1)，每日分析 Top 10
2. 核心表 EXPLAIN 無 type=ALL/index、無 Using temporary/filesort 3. 寫入峰值 Innodb_row_lock_waits < 10/秒、Innodb_row_lock_time_avg < 10ms 4. 緩存命中率 Qcache_hits/(Qcache_hits+Com_select) > 90% (若開啟 Query Cache) 5. 磁碟 IOPS 使用率 < 70%，留擴容餘量 6. 主從延遲 < 1 秒 (關鍵業務 < 100ms)

優化無銀彈，唯有「測量 → 定位 → 調整 → 驗證」閉環。插入優化重在減少交互與索引維護開銷；查詢優化重在索引設計與執行計劃分析。將 20 條策略內化為 Code Review 標準與架構決策依據，效能提升自然水到渠成。