索引對 MySQL 插入效能的實測量化分析：主鍵型別、索引類型、資料分布三大影響因子

實驗環境

• 引擎：MyISAM (避免 InnoDB 變數干擾)
• 表結構：id (PK/INDEX) + name VARCHAR(255)
• 資料量：100 萬行，INSERT 迴圈
• 指標：總耗時 (秒)

實測結果總表

深度解析

1. 整數主鍵 ≈ 1.15×，可接受

• 自增整數 → 尾部追加、頁分裂極少、B+Tree 高度穩定
• 最佳實踐：業務無關自增 BIGINT/INT 做主鍵

2. 整數二級索引 ≈ 1.5×，權衡查詢需求

• 每次 INSERT 需同步更新二級索引 B+Tree
• 若查詢頻率 ≫ 寫入，值得；否則考慮延遲索引/分區

3. 字串主鍵/索引 ≈ 4×，嚴禁濫用

• 字串比較 CPU 成本高、長度不固定導致頁分裂頻繁、索引體積大
• UUID、MD5、隨機字串做 PK → 插入效能崩潰、聚簇索引碎片化
• 必須用字串查詢 → 建立 INT 自增 PK + UNIQUE INDEX (字串欄位)

4. 進階實驗：資料分布對字串索引的影響

-- 原實驗：name = CONCAT('test', id)  → 字串前綴相同、後綴遞增
-- 修改：name = CONCAT(id, 'test')     → 字串前綴遞增、區分度極高
-- 結果：218s → 160s (降 27%)

結論：字串索引插入效能高度依賴 前綴區分度。高基數前綴 → B+Tree 插入路徑分散 → 頁分裂減少。

生產指導原則

1. 主鍵：自增整數 (BIGINT/INT)，業務鍵加 UNIQUE INDEX
2. 二級索引：按查詢頻率建立，寫入熱表審慎評估
3. 字串欄位索引：前綴索引 INDEX (col(10))、或生成欄位 col_hash INT AS (CRC32(col)) + 索引
4. 大量導入：先 Drop Index → 導入 → Rebuild Index，避免逐行維護
5. 監控：Handler_write、Innodb_pages_written、Innodb_buffer_pool_pages_dirty

結語

索引是雙面刃：查詢加速 ≫ 寫入成本 是核心權衡。量化實測證明：整數自增主鍵成本最低 (1.15×)、字串主鍵成本最高 (4×)。架構設計階段即鎖定「整數 PK + 業務鍵 UNIQUE」模式，寫入效能與查詢靈活性雙贏。