สร้างตาราง MySQL ด้วย InnoDB Engine (Default และแนะนำ)

ใช้ ENGINE = InnoDB ตอน CREATE TABLE (InnoDB เป็น Default ตั้งแต่ MySQL 5.5/5.6+)

Syntax

CREATE TABLE table_name (
    col1 datatype,
    col2 datatype,
    ...
) ENGINE = InnoDB;

ตัวอย่าง

CREATE TABLE EmployeeRecords (
    EmpId INT,
    EmpName VARCHAR(100),
    EmpAge INT,
    EmpSalary FLOAT
) ENGINE = InnoDB;

ตรวจสอบ Engine

1. SHOW TABLE STATUS

SHOW TABLE STATUS FROM business LIKE 'EmployeeRecords';
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name            | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| employeerecords | InnoDB | 10      | Dynamic    | 0    | 0              | 16384       | 0               | 0            | 0         | NULL           | 2018-10-22 15:22:01 | NULL        | NULL       | utf8mb4_unicode_ci | NULL     |                |         |
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+

2. INFORMATION_SCHEMA

SELECT TABLE_NAME, ENGINE 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'business' AND TABLE_NAME = 'EmployeeRecords';

3. SHOW CREATE TABLE

SHOW CREATE TABLE EmployeeRecords;
-- Table: CREATE TABLE `EmployeeRecords` (...) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

InnoDB Features (ทำไมควรใช้)

Feature	Description
ACID Transactions	Commit, Rollback, Crash Recovery (Redo/Undo Log)
Row-level Locking	High Concurrency (Lock แค่ Row ที่ Update)
Foreign Keys	Referential Integrity Enforcement
Buffer Pool	Cache Data + Index ใน RAM (Configurable)
Clustered Index	Primary Key = Data Order (Fast PK Lookup)
Compression	Page/Table Compression (Barracuda File Format)
Online DDL	ALTER TABLE โดยไม่ Lock (บาง Operation)
Adaptive Hash Index	Auto Hash Index สำหรับ Hot Pages

InnoDB Configuration (my.cnf)

[mysqld]
# Buffer Pool: 70-80% RAM (Dedicated DB Server)
innodb_buffer_pool_size = 4G

# Log File Size: Large = Less Checkpoint Flush
innodb_log_file_size = 512M

# Flush at Commit (1 = ACID, 0/2 = Performance)
innodb_flush_log_at_trx_commit = 1

# File Per Table (Default On)
innodb_file_per_table = 1

# Page Size (Default 16KB)
innodb_page_size = 16384

Best Practice

✅ ใช้ InnoDB เป็น Default สำหรับทุกตารางใหม่
✅ ระบุ ENGINE=InnoDB ให้ชัดเจน (Explicit)
✅ Tuning innodb_buffer_pool_size ให้พอ RAM
✅ ใช้ ROW_FORMAT=DYNAMIC (Default 8.0+) สำหรับ Large Columns
✅ Monitor: SHOW ENGINE INNODB STATUS, INFORMATION_SCHEMA.INNODB_METRICS