程式設計師必讀：MySQL 資料庫開發核心常識全解析

MySQL 是開發者最常用的資料庫之一。本文匯總 MySQL 開發中的核心常識，涵蓋儲存引擎選型、索引設計原則、大批量插入優化等關鍵主題，助你構建高效穩健的資料庫應用。

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

針對 MySQL 5.x 版本，重點比較兩大主流引擎：

MyISAM

• 特點：不支援交易、無外鍵約束；讀取速度快、批量插入效能高。
• 適用場景：以 SELECT、INSERT 為主、無交易需求的業務（如日誌、報表統計）。
• 風險提示：高頻批量寫入、大數據量下易發生表損壞，需謹慎評估。

InnoDB

• 特點：支援交易（ACID）、行級鎖、外鍵；相對 MyISAM 寫入效率稍低，索引與資料佔用更多磁碟空間。
• 適用場景：核心業務數據、需交易保證、高併發讀寫場景。
• 建議：避免用於極高訪問量的純讀取場景。

二、索引設計與使用的黃金法則

無索引的大表查詢效能極差。合理設計索引需遵循以下原則：

1. 索引建在 WHERE、JOIN、ORDER BY 涉及的欄位，而非 SELECT 列表欄位。
2. 優先選擇高選擇性（基數大）欄位。如性別只有男/女，建索引無助於過濾；主鍵、唯一鍵是最佳索引候選。
3. 使用前綴索引縮短長度。如 CHAR(100) 前 10 字符已具唯一性，定義索引長度為 10，可加速查詢並節省空間。
4. 善用聯合索引的最左前綴特性。聯合索引 (a,b,c) 等價於建立 (a)、(a,b)、(a,b,c) 多個索引，查詢只要用到最左列即可命中，優於建立多個單列索引。
5. 切忌過度索引。索引越多，INSERT/UPDATE/DELETE 越慢，且消耗大量磁碟空間，需在讀寫平衡中取捨。

實戰案例：聯合索引最左前綴驗證

表 user 有聯合索引 user(username, `order`, email)。
EXPLAIN SELECT * FROM user WHERE username='leehui' → type=ref，索引生效。
EXPLAIN SELECT * FROM user WHERE pwd='123' → type=ALL，全表掃描，索引失效（未用到最左列）。

LIKE 查詢與索引的關係

• WHERE username LIKE 'lee%' → type=range，索引生效（通配符不在首位）。
• WHERE username LIKE '%lee' → type=ALL，索引失效（通配符在首位）。

監控索引使用情況

執行 SHOW STATUS LIKE 'Handler_read_key'。若值很高說明索引生效；若 Handler_read_rnd_next 偏高，表示查詢低效、索引設計不合理。

三、大批量插入 SQL 優化技巧

高併發大量插入會導致負載飆升。優化核心在於減少網路往返與語句解析次數：

-- 低效：多條單獨 INSERT
INSERT INTO test VALUES ('aa','bb');
INSERT INTO test VALUES ('cc','dd');

-- 高效：單條多值 INSERT
INSERT INTO test VALUES ('aa','bb'), ('cc','dd'), ('ee','ff');

實戰中，百萬級併發插入可改用 INSERT DELAYED：數據先入記憶體隊列，資料庫閒時寫入，立即返回成功。注意：無法使用 LAST_INSERT_ID()，極重要數據不建議用此語句，防數據丟失。

四、其他實用技巧

MyISAM 表突破 4GB 限制

MyISAM 預設最大 4GB。單表超限會無法訪問。解決：

ALTER TABLE user MAX_ROWS=1000000000 AVG_ROW_LENGTH=15000;

InnoDB 理論支援 6TB，無此限制。