MySQL 資料庫優化全方位總結：從設計到 SQL 調優

MySQL 資料庫優化全方位總結：從設計到 SQL 調優

資料庫效能優化是系統架構中至關重要的一環。本文從資料庫設計規範、SQL 語句優化、參數配置、硬體資源四大維度，系統梳理 MySQL 優化的核心知識體系，並深入解析 EXPLAIN 執行計畫分析、索引策略、存儲引擎選擇等實戰技巧。

一、優化考量的四大標準

1. 資料庫設計規範（三範式與逆範式）

• 第一範式（1NF）：屬性原子性，不可再分解，關聯式資料庫天然滿足
• 第二範式（2NF）：記錄唯一性，需建立主鍵確保實體唯一識別
• 第三範式（3NF）：消除欄位冗餘，透過外鍵關聯避免衍生欄位
• 逆範式策略：適度冗餘提升查詢效能，冗餘欄位宜放置於資料量較小的表中，避免空間浪費

2. SQL 語句優化

核心在於減少全表掃描、善用索引、避免不必要的排序與臨時表。

3. 參數配置調優

重點調整 innodb_buffer_pool_size、query_cache_size、sort_buffer_size 等快取參數，根據記憶體大小合理分配。

4. 硬體與作業系統

SSD 硬碟、充足記憶體、適當的 CPU 核心數，以及 Linux 核心參數調優（如 vm.swappiness、net.core.somaxconn）。

二、SQL 優化標準流程

1. 掌握執行狀態：SHOW STATUS LIKE 'Com%' 查看各類 SQL 執行頻率；SHOW GLOBAL STATUS LIKE 'Slow_queries' 監控慢查詢
2. 開啟慢查詢日誌：SET GLOBAL slow_query_log=ON;、SET GLOBAL long_query_time=2; 記錄超過 2 秒的查詢
3. 定位問題 SQL：透過慢查詢日誌或 pt-query-digest 分析工具鎖定低效語句
4. EXPLAIN 深度分析：預測執行計畫，關注 type、key、rows、Extra 等關鍵欄位
5. 制定優化方案：加索引、改寫 SQL、調整表結構、分表分庫等

三、SQL 語句分類

• DDL（定義）：CREATE、ALTER、DROP
• DML（操作）：INSERT、DELETE、UPDATE
• DQL（查詢）：SELECT
• DTL（事務）：COMMIT、ROLLBACK、SAVEPOINT
• DCL（控制）：GRANT、REVOKE

四、存儲引擎選擇：MyISAM vs InnoDB

MyISAM 需定期執行 OPTIMIZE TABLE 整理碎片；InnoDB 5.6+ 支援獨立表空間（innodb_file_per_table=ON）。

五、索引優化核心技巧

索引類型

• 主鍵索引（Primary Key）
• 唯一索引（UNIQUE）
• 普通索引（INDEX）
• 全文索引（FULLTEXT，僅 MyISAM）
• 複合索引（多欄組合）

建立與刪除索引

-- 建立
CREATE INDEX idx_name ON table_name (col1, col2);
ALTER TABLE table_name ADD INDEX idx_name (col1);

-- 刪除
DROP INDEX idx_name ON table_name;
ALTER TABLE table_name DROP INDEX idx_name;

索引失效常見場景（必須避開）

1. OR 條件未全覆蓋索引
2. 複合索引未遵循「最左前綴」原則
3. LIKE '%keyword' 以萬用字元開頭
4. 字串欄位查詢未加引號導致隱含轉型
5. MySQL 評估全表掃描比索引更快（資料量小或選擇性低）

監控索引使用情況

SHOW STATUS LIKE 'Handler_read%';
-- Handler_read_key 越高越好（索引命中）
-- Handler_read_rnd_next 越高越差（全表掃描）

六、EXPLAIN 執行計畫深度解讀

EXPLAIN SELECT * FROM emp WHERE empno=2000\G;

關鍵欄位含義

• type：連接類型，效能由好到差：system > const > eq_ref > ref > range > index > ALL
• possible_keys：可能用到的索引
• key：實際使用的索引
• key_len：索引長度，越短越好
• rows：預估掃描行數，越少越好
• Extra：額外資訊
  • Using index：覆蓋索引，極佳
  • Using where：WHERE 過濾
  • Using filesort：需外部排序，需優化
  • Using temporary：建立臨時表，需優化

七、常見 SQL 優化實戰案例

1. 大批量插入資料

MyISAM：

ALTER TABLE tbl DISABLE KEYS;
LOAD DATA INFILE ...;
ALTER TABLE tbl ENABLE KEYS;

InnoDB：

-- 1. 按主鍵排序資料
-- 2. 關閉唯一檢查與自動提交
SET UNIQUE_CHECKS=0;
SET AUTOCOMMIT=0;
-- 3. 批次提交
COMMIT;

2. GROUP BY 優化

SELECT * FROM dept GROUP BY ename ORDER BY NULL; -- 禁止隱含排序

3. OR 條件索引優化

每個 OR 條件欄位皆需有索引，否則全表掃描。

4. 數值型別選擇

高精度場景使用 DECIMAL 避免浮點數誤差。

5. 日期型別選擇

TIMESTAMP (4 bytes) vs DATETIME (8 bytes)，依業務需求選擇最小儲存型別。

總結

MySQL 優化是系統工程，需從設計規範、SQL 品質、索引策略、參數調優、硬體資源多維度協同。建立監控體系，持續追蹤慢查詢，養成 EXPLAIN 先行的開發習慣，才能讓資料庫持續高效穩定運行。