MySQL 三大開發優化核心：存儲引擎選型、索引設計、大批量插入優化

前言

系統瓶頸多在資料庫端。少量資料/低併發無需技巧，但資料量達標後，每個設計細節都影響效能。

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

選型建議

• 以 SELECT、INSERT 為主、無事務需求 → MyISAM
• 關鍵業務、需事務、高併發寫入 → InnoDB
• 實測提醒：MyISAM 高頻批量插入大數據量時易損壞表

二、索引設計與使用五大原則

1. 建在 WHERE/ORDER BY 欄位，SELECT 列建索引無效
2. 高選擇性優先：唯一索引最佳；低基數欄位（如性別）建索引無效
3. 前綴索引：CHAR(100) 前 10 字元唯一 → INDEX idx_col (col(10))，加快查詢、省空間
4. 聯合索引左前綴特性：INDEX (a,b,c) 等效於 (a)、(a,b)、(a,b,c) 三個索引，查詢必須包含最左列才會用到
5. 避免過度索引：索引越多 INSERT 越慢、佔空間大，大數據量下代價巨大

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

SHOW INDEX FROM user;
-- 聯合索引 user(username, `order`, email)

EXPLAIN SELECT * FROM user WHERE username='leehui';  -- 用到索引
EXPLAIN SELECT * FROM user WHERE `order`=1;          -- 未用到（非最左）

LIKE 查詢索引生效規則

EXPLAIN SELECT * FROM user WHERE username LIKE 'lee%';  -- 索引生效 (range)
EXPLAIN SELECT * FROM user WHERE username LIKE '%lee';  -- 索引失效 (ALL)

只有 % 不在首位 時索引才可能生效。

索引使用監控

SHOW STATUS LIKE 'Handler_read_key';     -- 高 = 索引生效
SHOW STATUS LIKE 'Handler_read_rnd_next'; -- 高 = 全表掃描，索引失效

三、大批量插入 SQL 優化

1. 合併 VALUES 減少網路往返

-- 慢：N 條 INSERT
INSERT INTO test VALUES (a,b);
INSERT INTO test VALUES (c,d);

-- 快：單條多值
INSERT INTO test VALUES (a,b), (c,d), (e,f);

2. INSERT DELAYED 非阻塞寫入

• 資料先入記憶體佇列，資料庫空閒時寫入，立即返回成功
• 注意：mysql_insert_id() 無效（尚未真實寫入）；關鍵資料勿用（掉電丟失風險）

附錄：MyISAM 表突破 4GB 限制

ALTER TABLE user MAX_ROWS=1000000000 AVG_ROW_LENGTH=15000;

預設 4GB，修改後支援 ~208TB。