InnoDB กับ MyISAM ต่างกันอย่างไร? ทดลองใช้งาน และทดสอบการล็อกตารางใน MySQL

เมื่อเราสร้างตารางใน MySQL ข้อมูลไม่ได้ถูกจัดเก็บด้วยวิธีเดียวกันทั้งหมด แต่ MySQL มี Storage Engine หรือกลไกที่รับผิดชอบการจัดเก็บ อ่าน แก้ไข ลบ และควบคุมการเข้าถึงข้อมูล บทความนี้จะเปรียบเทียบ Storage Engine ที่นักศึกษาควรรู้จัก 2 ชนิด ได้แก่ InnoDB และ MyISAM พร้อมทดลองการล็อกข้อมูลด้วยการเชื่อมต่อฐานข้อมูล 2 Session

ข้อควรระวัง: เหมาะสำหรับ MySQL 8.x และควรทดลองในฐานข้อมูลสำหรับการเรียนเท่านั้น

Storage Engine คืออะไร?

Storage Engine คือส่วนของ MySQL ที่จัดการข้อมูลภายในตาราง เช่น รูปแบบการจัดเก็บ การใช้ดัชนี การทำ Transaction การกู้คืนข้อมูล และการล็อกเมื่อมีผู้ใช้หลายคนเข้าถึงข้อมูลพร้อมกัน

ฐานข้อมูลเดียวกันสามารถมีหลายตารางที่ใช้ Storage Engine ต่างกันได้ เช่น ตาราง orders ใช้ InnoDB แต่ตาราง old_logs ใช้ MyISAM อย่างไรก็ตาม ระบบงานทั่วไปในปัจจุบันควรใช้ InnoDB เป็นตัวเลือกหลัก เพราะรองรับ Transaction, Foreign Key, การกู้คืนเมื่อระบบขัดข้อง และการทำงานพร้อมกันของผู้ใช้จำนวนมากได้ดีกว่า

เปรียบเทียบ InnoDB และ MyISAM

เกร็ดความรู้: คำว่า "InnoDB ล็อกระดับแถว" ไม่ได้หมายความว่าจะล็อกเพียงแถวเดียวทุกกรณี หากคำสั่งค้นหาไม่มีดัชนีที่เหมาะสม MySQL อาจต้องสแกนหลายแถว ทำให้ InnoDB ล็อกระเบียนที่ถูกสแกนจำนวนมากได้ ดังนั้นการออกแบบ PRIMARY KEY และ INDEX จึงสำคัญมาก

1. ตรวจสอบ Storage Engine ที่ใช้งานได้

เปิด MySQL Workbench, phpMyAdmin หรือ MySQL Command Line แล้วใช้คำสั่ง

SHOW ENGINES;

คอลัมน์ Support มีความหมายสำคัญดังนี้

• DEFAULT — Storage Engine เริ่มต้น
• YES — รองรับและสามารถใช้งานได้
• NO — ไม่รองรับในเซิร์ฟเวอร์นี้

ตรวจสอบ Storage Engine เริ่มต้นด้วยคำสั่ง

SELECT @@default_storage_engine;

หาก SHOW ENGINES แสดงว่า MyISAM เป็น YES ก็สามารถระบุ ENGINE=MyISAM ตอนสร้างตารางได้ ไม่ต้องเปิดโมดูลเพิ่ม ส่วนเซิร์ฟเวอร์บางระบบอาจปิดหรือไม่รองรับ Engine บางชนิด ผู้ใช้ทั่วไปไม่ควรแก้ไฟล์ตั้งค่าเซิร์ฟเวอร์เอง

2. สร้างฐานข้อมูลสำหรับทดลอง

CREATE DATABASE IF NOT EXISTS engine_lab
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE engine_lab;

3. สร้างตาราง InnoDB และ MyISAM

ตารางทั้งสองจะมีโครงสร้างและข้อมูลเหมือนกัน เพื่อให้เห็นว่าผลต่างเกิดจาก Storage Engine

CREATE TABLE accounts_innodb (
    id INT PRIMARY KEY,
    owner_name VARCHAR(100) NOT NULL,
    balance DECIMAL(10,2) NOT NULL DEFAULT 0
) ENGINE=InnoDB;

CREATE TABLE accounts_myisam (
    id INT PRIMARY KEY,
    owner_name VARCHAR(100) NOT NULL,
    balance DECIMAL(10,2) NOT NULL DEFAULT 0
) ENGINE=MyISAM;

เพิ่มข้อมูลตัวอย่าง

INSERT INTO accounts_innodb (id, owner_name, balance) VALUES
(1, 'สมชาย', 1000.00),
(2, 'สมหญิง', 2000.00);

INSERT INTO accounts_myisam (id, owner_name, balance) VALUES
(1, 'สมชาย', 1000.00),
(2, 'สมหญิง', 2000.00);

ตรวจสอบ Engine ของแต่ละตาราง

SHOW TABLE STATUS
FROM engine_lab
WHERE Name IN ('accounts_innodb', 'accounts_myisam');

หรือดูคำสั่งที่ใช้สร้างตาราง

SHOW CREATE TABLE accounts_innodb;
SHOW CREATE TABLE accounts_myisam;

4. การเปลี่ยน Storage Engine ของตาราง

เปลี่ยนตาราง MyISAM เป็น InnoDB ได้ด้วยคำสั่ง

ALTER TABLE accounts_myisam ENGINE=InnoDB;

เปลี่ยนกลับเป็น MyISAM ได้ด้วยคำสั่ง

ALTER TABLE accounts_myisam ENGINE=MyISAM;

คำเตือน: ควรสำรองข้อมูลและทดสอบก่อนใช้กับระบบจริง เพราะการแปลงตารางอาจใช้เวลานาน ใช้พื้นที่เพิ่ม และอาจติดข้อจำกัดของ Foreign Key หรือคุณสมบัติที่ Engine ปลายทางไม่รองรับ

5. ทดลอง Transaction: InnoDB กับ MyISAM

5.1 ทดลองกับ InnoDB

START TRANSACTION;

UPDATE accounts_innodb
SET balance = balance - 200
WHERE id = 1;

SELECT * FROM accounts_innodb;

ROLLBACK;

SELECT * FROM accounts_innodb;

หลัง ROLLBACK ยอดเงินของ id = 1 จะกลับเป็น 1,000 บาท เพราะ InnoDB รองรับ Transaction

5.2 ทดลองกับ MyISAM

START TRANSACTION;

UPDATE accounts_myisam
SET balance = balance - 200
WHERE id = 1;

ROLLBACK;

SELECT * FROM accounts_myisam;

ยอดเงินจะไม่ย้อนกลับ เพราะ MyISAM ไม่รองรับ Transaction อย่างแท้จริง คำสั่ง ROLLBACK จึงไม่สามารถยกเลิกการแก้ไขนั้นได้

คืนค่าข้อมูลก่อนทดลองขั้นต่อไป

UPDATE accounts_myisam SET balance = 1000 WHERE id = 1;
UPDATE accounts_myisam SET balance = 2000 WHERE id = 2;

6. ทดสอบการล็อกของ InnoDB ด้วย 2 Session

ให้เปิดหน้าต่าง SQL สองหน้าต่างที่เชื่อมต่อมายัง MySQL Server เดียวกัน เรียกว่า Session A และ Session B (ห้ามทดสอบโดยรันทุกคำสั่งต่อกันในหน้าต่างเดียว)

Session A: ล็อกแถวที่ id = 1

USE engine_lab;
SET autocommit = 0;
START TRANSACTION;

UPDATE accounts_innodb
SET balance = balance + 100
WHERE id = 1;

-- ยังไม่ต้อง COMMIT หรือ ROLLBACK

Session B: แก้ไขคนละแถว

USE engine_lab;
SET autocommit = 0;
START TRANSACTION;

UPDATE accounts_innodb
SET balance = balance + 50
WHERE id = 2;

COMMIT;

คำสั่งนี้ควรทำงานสำเร็จทันที เพราะ Session A ล็อก id = 1 แต่ Session B แก้ไข id = 2 แสดงถึงการทำงานแบบ Row-level Lock

Session B: ทดลองแก้ไขแถวเดียวกัน

START TRANSACTION;

UPDATE accounts_innodb
SET balance = balance + 50
WHERE id = 1;

คำสั่งจะรอ เพราะแถว id = 1 ถูก Session A ล็อกอยู่ หากรอนานเกินค่าที่กำหนด อาจพบข้อความ Lock wait timeout exceeded

กลับไปที่ Session A แล้วปล่อยล็อก

COMMIT;

จากนั้นคำสั่งใน Session B จะทำงานต่อ ให้จบ Transaction ด้วย

COMMIT;

7. ทดสอบการล็อกของ MyISAM ด้วย 2 Session

Session A: ล็อกตาราง MyISAM

USE engine_lab;

LOCK TABLES accounts_myisam WRITE;

UPDATE accounts_myisam
SET balance = balance + 100
WHERE id = 1;

-- ยังไม่ต้อง UNLOCK TABLES

Session B: ทดลองแก้ไขคนละแถว

USE engine_lab;

UPDATE accounts_myisam
SET balance = balance + 50
WHERE id = 2;

แม้ Session B จะแก้ไข id = 2 ซึ่งเป็นคนละแถวกับ Session A แต่คำสั่งยังต้องรอ เพราะ Session A ถือ Write Lock ของ ทั้งตาราง

กลับไปที่ Session A แล้วปล่อยล็อก

UNLOCK TABLES;

8. สรุปผลการทดลอง

9. คำสั่งตรวจสอบเมื่อเกิดการรอล็อก

ดู Process ที่กำลังทำงาน

SHOW FULL PROCESSLIST;

ดูสถานะ InnoDB และข้อมูล Deadlock ล่าสุด

SHOW ENGINE INNODB STATUS;

10. ข้อผิดพลาดที่พบบ่อย

• รันทุกคำสั่งในหน้าต่างเดียว: จะไม่เห็นการรอล็อก ให้ตรวจสอบด้วย SELECT CONNECTION_ID(); เพื่อยืนยันว่าอยู่คนละ Session
• ลืมปิด Transaction: Transaction ที่ค้างอยู่จะถือ Lock ค้างไว้ ต้องจบด้วย COMMIT; หรือ ROLLBACK; เสมอ
• เข้าใจว่า SELECT ธรรมดาจะถูกบล็อก: InnoDB ใช้ MVCC ดังนั้น SELECT ปกติจะอ่าน Snapshot ได้โดยไม่ต้องรอ หากต้องการอ่านแบบล็อกเพื่อแก้ไข ต้องใช้ FOR UPDATE

11. คำถามท้ายบท

1. Storage Engine ทำหน้าที่อะไร?
2. เพราะเหตุใด InnoDB จึงเหมาะกับระบบขายสินค้าหรือระบบธนาคารมากกว่า MyISAM?
3. Row-level Lock และ Table-level Lock ต่างกันอย่างไร?
4. เพราะเหตุใด ROLLBACK จึงยกเลิกการแก้ไขในตาราง MyISAM ไม่ได้?
5. ถ้า InnoDB ไม่มี Index ที่เหมาะสม การล็อกอาจส่งผลอย่างไร?
6. เพราะเหตุใดการทดลองการล็อกจึงต้องใช้ 2 Session?

12. งานปฏิบัติสำหรับนักศึกษา

ให้นักศึกษาสร้างฐานข้อมูลและทดลองตามบทความ แล้วส่งหลักฐานดังนี้

1. ภาพผลลัพธ์ SHOW ENGINES
2. ภาพ SHOW TABLE STATUS ที่เห็นตาราง InnoDB และ MyISAM
3. ภาพผลทดลอง ROLLBACK ของทั้งสอง Engine
4. ภาพ Session B ขณะรอแถวเดียวกันใน InnoDB
5. ภาพ Session B ขณะรอทั้งตารางใน MyISAM
6. สรุปผลด้วยภาษาของตนเอง 5–8 บรรทัด

เอกสารอ้างอิง

• MySQL 8.4 Reference Manual: InnoDB Locking and Transaction Model
• MySQL 8.4 Reference Manual: Locks Set by Different SQL Statements in InnoDB
• MySQL 8.4 Reference Manual: Internal Locking Methods