เร่งความเร็ว INSERT หลายแถวใน MySQL ด้วย Transaction

หลักการ

การ insert หลายแถวพร้อมกันใน Transaction เดียว เร็วกว่ามาก เพราะลดการ flush disk ทุกแถว (InnoDB flush ทุก commit)

รูปแบบคำสั่ง

START TRANSACTION;
INSERT INTO table (col1, col2) VALUES 
  (val1, val2), (val3, val4), ..., (valN, valM);
COMMIT;

ตัวอย่างครบถ้วน

1. สร้างตาราง

mysql> create table insertDemo 
    -> ( 
    -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    -> StudentName varchar(20), 
    -> StudentAge int 
    -> ); 
Query OK, 0 rows affected (0.72 sec)

2. Insert หลายแถวใน Transaction เดียว

mysql> START TRANSACTION; 
Query OK, 0 rows affected (0.00 sec)

mysql> insert into insertDemo(StudentName,StudentAge) values
    -> ('John',21),('Carol',22),('Bob',21),('David',24),
    -> ('Maxwell',25),('Mike',22); 
Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0

mysql> COMMIT; 
Query OK, 0 rows affected (0.14 sec)

3. ตรวจสอบผลลัพธ์

mysql> select * from insertDemo;

+-----------+-------------+------------+
| StudentId | StudentName | StudentAge |
+-----------+-------------+------------+
| 1         | John        | 21         |
| 2         | Carol       | 22         |
| 3         | Bob         | 21         |
| 4         | David       | 24         |
| 5         | Maxwell     | 25         |
| 6         | Mike        | 22         |
+-----------+-------------+------------+

เทคนิคเพิ่มเติมสำหรับ Bulk Insert

1. ใช้ LOAD DATA INFILE (เร็วที่สุด)

LOAD DATA INFILE '/path/file.csv' 
INTO TABLE table_name 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
IGNORE 1 LINES;

2. ปิด Index ชั่วคราว (MyISAM)

ALTER TABLE table_name DISABLE KEYS;
-- bulk insert
ALTER TABLE table_name ENABLE KEYS;

3. เพิ่ม innodb_flush_log_at_trx_commit = 2 (ชั่วคราว)

ใน my.cnf: ลดการ flush ทุก transaction → เร็วขึ้น แต่อาจสูญเสียข้อมูล 1 วินาทีถ้า crash

4. ใช้ Prepared Statement (สำหรับ application)

PREPARE stmt FROM 'INSERT INTO t (a,b) VALUES (?,?)';
SET @a = 1, @b = 2; EXECUTE stmt;
SET @a = 3, @b = 4; EXECUTE stmt;
-- ...
DEALLOCATE PREPARE stmt;

Benchmark แนวประมาณ

สรุป: ใช้ multi-row INSERT ใน Transaction เดียวเป็นวิธีง่ายที่สุด เร็วขึ้น 10-50 เท่า