程式設計師必備 MySQL 開發實戰精要：引擎選型、索引設計、大批量寫入優化

一、儲存引擎選型：InnoDB vs MyISAM 實戰取捨

二、索引設計與使用：從原理到實戰

核心原則

1. WHERE/ORDER BY/GROUP BY/JOIN ON 欄位優先建索引，SELECT 列表欄位無需索引。
2. 高選擇性（基數大）優先：唯一索引 > 幾乎唯一 > 低基數（如性別、狀態碼）單列索引無效。
3. 短索引：VARCHAR(100) 前 10 字元唯一 → INDEX(name(10))，節省空間、加快比較。
4. 複合索引左前綴原則：INDEX(a,b,c) 等價於 INDEX(a) + INDEX(a,b) + INDEX(a,b,c)，最左列必須出現在查詢條件中才生效。
5. 拒絕過度索引：每新增索引，INSERT/UPDATE/DELETE 皆需維護，大表索引過多嚴重拖慢寫入、佔用磁碟。

實戰驗證：EXPLAIN 解析索引生效

-- 複合索引 idx_user (username, order, email)
EXPLAIN SELECT * FROM user WHERE username='leehui';
-- type=ref, key=idx_user, key_len=152 ✅ 用到最左列

EXPLAIN SELECT * FROM user WHERE `order`=1;
-- type=ALL, key=NULL ❌ 未用最左列，全表掃描

LIKE 查詢索引生效邊界

需模糊匹配中間/尾部 → 全文索引或 Elasticsearch。

索引使用狀況監控

SHOW STATUS LIKE 'Handler_read_key';     -- 高 = 索引命中好
SHOW STATUS LIKE 'Handler_read_rnd_next'; -- 高 = 全表掃描多，索引設計不當

三、大批量插入 SQL 優化

多值單條 INSERT（核心優化）

-- 慢：100 條單獨 INSERT = 100 次網路往返 + 解析 + 提交
INSERT INTO test VALUES ('a','b');
INSERT INTO test VALUES ('c','d');
...

-- 快：1 條多值 INSERT = 1 次網路往返 + 1 次解析 + 1 次提交
INSERT INTO test VALUES ('a','b'), ('c','d'), ('e','f'), ...;

實測：百萬行插入速度提升 10-50 倍。

INSERT DELAYED 非同步寫入（舊版特性，8.0 移除）

• 資料先入記憶體佇列，空閒時批量寫入，立即返回 OK。
• 限制：mysql_insert_id() 無效、REPLACE/ON DUPLICATE KEY 不支援、崩潰風險丟失資料、僅 MyISAM/Memory/Archive 支援。
• 現代替代：應用層批次累積 + 多值 INSERT、或異步寫入佇列（Kafka/RabbitMQ）+ 消費者批量入庫。

其他批量寫入技巧

• LOAD DATA INFILE：CSV/TSV 直接載入，速度最快（繞過 SQL 層），需 FILE 權限、local_infile=1。
• 關閉非必要索引/外鍵/觸發器 → 匯入 → 重建（大表重建索引比逐行維護快得多）。
• 調大 innodb_buffer_pool_size、innodb_log_file_size、innodb_flush_log_at_trx_commit=2（匯入期間犧牲 1 秒耐久性換速度）。

四、其他實戰雜談

MyISAM 表超過 4GB 無法存取解決

ALTER TABLE user MAX_ROWS=1000000000 AVG_ROW_LENGTH=15000;

告知優化器預估行數與平均行長，突破 4GB 檔案大小限制（32 位檔案指標上限）。InnoDB 表空間無此限制（64TB）。

大表 Schema 變更線上操作

• MySQL 5.6+ ALGORITHM=INPLACE, LOCK=NONE 線上 DDL（部份操作）。
• 大表加欄位/索引 → pt-online-schema-change (Percona Toolkit) / gh-ost (GitHub) 無鎖變更。

慢查詢分析標準流程

1. 開啟 slow_query_log=1, long_query_time=1 (或 0.5)。
2. pt-query-digest 分析 Top N 慢查詢。
3. EXPLAIN 確認執行計畫、索引使用。
4. 優化：加索引、改寫 SQL、調整優化器提示、分表分庫。
5. 驗證：上線前預發環境壓測對比。

總結：MySQL 開發黃金法則

1. 預設 InnoDB，特定場景才考慮其他引擎。
2. 索引按查詢設計，而非按表結構想像；定期審視 Handler_read_rnd_next。
3. 批量寫入用多值 INSERT，極大量用 LOAD DATA + 索引延後建立。
4. 嚴禁 SELECT *，明確列出欄位，利用覆蓋索引避免回表。
5. 參數化查詢（Prepared Statement）防注入、利用計畫快取。
6. 監控先行：Prometheus + Grafana 監控 QPS、延遲、鎖等待、緩衝池命中率。

1. 上一篇文章： InnoDB vs MyISAM 深度對比：選型關鍵差異與避坑指南
2. 下一篇文章： SQL 查詢緩慢 50 大成因深度解析與優化對策

MySQL 資料庫相關知識

MySQL VARCHAR 轉 DATE 類型實戰與 Oracle 對比指南

MySQL 方案：STR_TO_DATE 函數 將字符串按指

Apache ab 壓力測試工具完整使用指南：參數詳解與結果分析

ab (ApacheBench) 是 Apache 附帶的

MySQL SELECT 查詢語法全解：從基礎語法到無 FROM 子句的運算式評估

SELECT 語句核心語法 SELECT 是 SQL 最常用

MySQL REPLACE INTO 與 INSERT ... ON DUPLICATE KEY UPDATE 核心差異深度解析

兩者皆用於「主鍵/唯一鍵衝突時的寫入策略」，但底層行為差異巨