MySQL 效能調優六大技巧：存儲引擎選擇、計數陷阱、EXPLAIN 實戰與索引策略

1. 存儲引擎選擇

• 需事務 → InnoDB（ACID）。
• 無事務、重讀輕寫 → MyISAM。
• 不要混用：事務中混用會導致部分提交、部分回滾，資料不一致。
• 技巧：選 MyISAM 時仍可單獨載入 InnoDB 插件，按需啟用事務表。

2. COUNT(*) 計數陷阱

InnoDB 支援 MVCC，COUNT(*) 只能返回近似值（有並行事務未提交/回滾）。生產環境避免直接用 COUNT(*) 做精確統計。

3. 大樣本反覆測試

查詢優化必須在上萬/百萬級真實資料量下測試，開發環境少量資料無法暴露全表掃描、鎖競爭等問題。

4. 避免全表掃描

最直接手段：建立合適索引。但索引非越多越好，寫入開銷與優化器誤判風險隨索引數增加。

5. 善用 EXPLAIN 診斷

實戰案例

CREATE TABLE awesome_pcq (
  emp_id INT(10) NOT NULL DEFAULT 0 PRIMARY KEY,
  full_name VARCHAR(100) NOT NULL,
  email_id VARCHAR(100) NOT NULL,
  password VARCHAR(50) NOT NULL,
  deleted TINYINT(4) NOT NULL
) ENGINE=InnoDB;

查詢：SELECT emp_id FROM awesome_pcq WHERE email_id='x' AND password='y' AND deleted=0

EXPLAIN 顯示 rows=100（全表掃描）。

加索引：ALTER TABLE awesome_pcq ADD INDEX LoginValidate (email_id);

再次 EXPLAIN → rows=1，僅掃描 1 行。MySQL 優化器會自動利用 WHERE 條件中有索引的欄位先過濾。

6. 索引設計原則

• 非唯一索引仍可能返回多行，但遠優於全表掃描。
• WHERE 條件欄位順序不影響索引使用（優化器自動選擇）。
• 避免過度索引：僅在高選擇性、高頻過濾/排序欄位建立。