萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 查詢優化五大核心方法:資料型別、字元集、COUNT、子查詢、UNION

1. 優化資料型別

  • 避免 NULL:NULL 需額外位元組標記、索引邏輯複雜、優化器難以統計。預設用 NOT NULL + 預設值 (0、空字串、'1970-01-01')。
  • 越小越好:TINYINT < SMALLINT < INT < BIGINT;CHAR 固定長、VARCHAR 可變長。但預留擴充空間,避免後續 ALTER TABLE 鎖表重建。

2. 小心字元集隱性轉換

  • Client/連線/資料表字元集不一致會觸發隱性 CONVERT(),消耗 CPU 且可能導致索引失效。
  • 統一全鏈路 utf8mb4,連線字串加 charset=utf8mb4。
  • UTF-8 為多位元組 (1-4 bytes),儲存空間比 latin1 大,規劃容量時需考量。

3. 優化 COUNT()

  • MyISAM:COUNT(*) 無 WHERE 時極快 (內部計數器)。
  • InnoDB:無計數器,需掃描聚集索引。COUNT(col) 若無 NULL 效能相近。
  • 複雜 WHERE:建立覆蓋索引 (含 WHERE+SELECT 欄位) 讓掃描只走索引。
  • 極大表統計:維護匯總表 (觸發器/定時任務/應用層更新),查詢匯總表 O(1)。

4. 優化子查詢 → JOIN

  • MySQL 優化器對 IN/EXISTS 子查詢優化較弱 (5.6+ 有改善但仍有限)。
  • 改寫為 JOIN,確保被驅動表 (右表) 關聯欄位有索引。
  • 驅動表 (左表) 通常全掃,結果集較小時效能最佳。

5. 優化 UNION

  • UNION = UNION ALL + 排序去重,大資料集排序極耗資源。
  • 確知無重複或可容忍重複 → UNION ALL,跳過排序,速度提升數倍。
  • 應用層邏輯去重 (如 INSERT IGNORE、ON DUPLICATE KEY) 同理。

總結口訣

型別小而不 Null、字元集全鏈路統一、COUNT 看引擎用覆蓋索引、子查詢改 JOIN 記得加索引、UNION 能 ALL 就 ALL。

copyright © 萬盛學電腦網 all rights reserved