MySQL 索引與提交頻率對 InnoDB 寫入速度影響實測：自增主鍵提升 3%，批次提交快 5 倍

實測索引策略與提交頻率對 InnoDB 批量寫入（30 萬行）的影響，得出明確量化結論指導實務優化。

測試環境

• 表結構：id INT AUTO_INCREMENT PRIMARY KEY, c1 INT, c2 INT, c3 TIMESTAMP, c4 VARCHAR(200)
• 存儲過程 insert_mytab(rownum, commitrate) 控制提交頻率。
• 每次測試前重啟實例，冷啟動狀態。

結論一：索引對寫入速度影響

建議：InnoDB 表必須有自增主鍵，順序寫入減少頁分裂，提升寫入效能；二級索引按查詢需求最小化建立。

結論二：提交頻率影響（自增主鍵表，30 萬行）

核心解讀

• 批次提交極其關鍵：每行提交比一次性提交慢 5.5 倍，主要開銷在 Redo Log 刷盤（innodb_flush_log_at_trx_commit=1 預設每提交 fsync）。
• mysqldump --extended-insert 產生批次 INSERT，比單行 INSERT 快 5 倍以上，同理。

場景化建議

測試存儲過程核心碼

CREATE PROCEDURE insert_mytab(IN rownum INT, IN commitrate INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET AUTOCOMMIT = 0;
    WHILE i < rownum DO
        INSERT INTO mytab(c1,c2,c3,c4) VALUES(
            FLOOR(RAND()*rownum), FLOOR(RAND()*rownum), NOW(), REPEAT(CHAR(ROUND(RAND()*255)),200)
        );
        SET i = i+1;
        IF commitrate > 0 AND i % commitrate = 0 THEN
            COMMIT;
        END IF;
    END WHILE;
    COMMIT;  -- 確保最後提交
END;

此實測數據為容量規劃與架構決策提供量化依據。

1. 上一篇文章： MySQL SAVEPOINT 識別字元陷阱：含科學記號 'e' 導致語法錯誤的 Bug 與解法
2. 下一篇文章： 提升 MySQL InnoDB BLOB/TEXT 列存儲效率：Off-page 機制解析與五大優化策略

MySQL 資料庫相關知識

MySQL儲存引擎選擇指南：八大引擎特性對比與適用場景

插件式儲存架構優勢 MySQL提供多種儲存引擎，針對不同使用

MySQL 動態行轉列實戰：存儲過程 + PREPARE 實現課程成績橫表輸出

以「學生-課程-成績」三表為例，展示如何用 GROUP_CO

Windows 7/8 連線本地 MySQL 極慢 (1 秒延遲) 根因與解決：IPv6 localhost 陷阱

本地程式連線 localhost 耗時 ~1 秒，改用 12

SQL 去重完全指南：DISTINCT vs ROW_NUMBER() 實戰對比

場景準備：UserInfo 示例表 IDNameAgeCi