ร่างไว้ก่อนนานมากๆ แล้ว เนื้อหายังไม่เสร็จดี แต่เอามาลงให้ก่อน เดี๋ยวจะหาว่าไม่อัพเดทเว็บไซต์เลย เอาแค่ engine หลักๆ ก่อนละกัน ส่วนอันอื่นๆ ค่อยลงทีหลังเนอะ
ถ้าถามถึงระบบฐานข้อมูลฟรีสำหรับเว็บไซต์ที่นิยมในบ้านเราตอนนี้ คำตอบที่ได้ก็น่าจะเป็น MySQL อย่างแน่นอน ถึงแม้จะมีระบบอื่นๆ อย่าง PostgreSQL แต่ปัญหาก็คือหา Server ยากพอสมควร ส่วน SQLite ก็ไม่เหมาะกับงานใหญ่ๆ แต่ก็กำลังมาแรงในระบบมือถือ
คงปฏิเสธไม่ได้อีกว่า MySQL นี้เป็นระบบฐานข้อมูลแรกที่ทุกๆ คนเรียนรู้คู่กับภาษา PHP แน่นอน เพราะหนังสือส่วนใหญ่จะสอนคู่กันเลย มีแยกเป็นบทท้ายๆ ในหนังสือส่วนใหญ่แน่นอน แต่สิ่งที่หนังสือไม่ได้สอน (หนังสือภาษาไทย) คือ รูปแบบของตารางแต่ละชนิด (stroage engine) ว่ามีแบบไหนบ้าง แล้วต่างกันอย่างไร หรือควรจะใช้ตารางข้อมูลแบบไหนในระบบของเรา
ใน MySQL มีรูปแบบตารางข้อมูลย่อยอยู่อีกหลายชนิดให้เราเลือกใช้ให้เหมาะสมกับงานของเรา รูปแบบหลักๆ มีดังนี้
– MyISAM
– InnoDB
– Memory
– Blackhole
– Archive
– Merge
– CSV
วิธีการดูว่า MySQL ที่เราติดตั้งไปสนับสนุนตารางข้อมูลรูปแบบใดบ้าง ทำได้โดยใช้คำสั่ง
SHOW ENGINES/G
ผลลัพธ์ที่ได้ก็จะเป็นแบบนี่
mysql> show engines\g
+——————–+———+—————————————————————-+————–+——+————+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+——————–+———+—————————————————————-+————–+——+————+
| FEDERATED | NO | Federated MySQL storage engine| NULL | NULL | NULL |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
+——————–+———+—————————————————————-+————–+——+————+
9 rows in set (0.00 sec)
mysql>
*** ค่าเริ่มต้นของรูปแบบตารางข้อมูลใน MySQL คือ MyISAM ***
การสร้างตารางโดยกำหนดรูปแบบตารางข้อมูล ทำได้โดยเพิ่มคำสั่ง engine=[ชนิดตาราง] ตัวอย่างตามนี้
CREATE TABLE `admin` (
`token` char(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT ”,
`username` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
`passwd` char(40) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`token`),
UNIQUE KEY `username` (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
(เป็นตัวอย่างตารางที่ใช้งานจริง แต่ตัดบางฟิลด์ออกไป)
ถ้าหากไม่กำหนด ENGINE=MyISAM ตารางที่สร้างขึ้นจะถูกกำหนดเป็นค่าเริ่มต้นของ MySQL
ก่อนที่จะอธิบายรูปแบบตารางแต่ละชนิดขออธิบายชนิดคำสั่งของ MySQL ก่อน คำสั่งใน MySQL จะมีอยู่สองแบบใหญ่ๆ คือ
1. Data Definition Language (DDL) เป็นคำสั่งที่ใช้จัดการกับตาราง ได้แก่ create, alter, drop, truncate, comment, rename เป็นต้น
2. Data Manipulation Language (DML) เป็นคำสั่งที่ใช้จัดการข้อมูลในตาราง ได้แก่ select, insert, update, delete, marge, replace, call, lock เป็นต้น
รูปแบบตารางชนิดต่างๆ ใน MySQL เท่าที่ผมรู้และใช้งานมาก็มีตามนี้
1. MyISAM
เป็นรูปแบบตารางมาตรฐานของ MySQL ถูกกำหนดให้เป็นรูปแบบเริ่มต้นตั้งแต่ MySQL 5.1 (ใน MySQL 4 จะเป็น InnoDB) พัฒนามาจาก ISAM เริ่มมีมาตั้งแต่ MySQL 3
คุณสมบัติ
– ไม่มีการทำแทรนเซคชัน (transection)
– เขียนข้อมูลได้เร็ว (insert)
– ใช้ B-Tree ในการทำดรรชนี (index)
– ใช้ตัวอักษรทำดรรชนีได้ (index หรือ unique)
ไฟล์ข้อมูล จะมีอยู่สามไฟล์
– [ชื่อตาราง].frm กำหนดรูปแบบ (format) ของตาราง
– [ชื่อตาราง].MYD เก็บข้อมูลทั้งหมด (MYData)
– [ชื่อตาราง].MYI เก็บดรรชนี (index) ของตาราง (MYIndex)
ทดลองสร้างตารางโดยใช้คำสั่ง
CREATE TABLE `myisam`(
`id` int
)ENGINE=MYISAM;
ใช้เมื่อไหร่?
MyISAM ออกแบบมาให้ใช้กับระบบที่มีการอ่านเขียนข้อมูลมากๆ ตัวอย่างเช่น webboard
2. InnoDB
เป็นฐานข้อมูลอีกชนิดที่ได้รับความนิยม ใช้สำหรับงานที่ต้องการความถูกต้องของข้อมูลสูงมากๆ จุดแข็งของฐานข้อมูลชนิดนี้คือการทำแทรนเซคชัน
คุณสมบัติ
– ทำแทรนเซตชันได้
– ใช้ ACID ได้ (ACID มาจากคำว่า Atomicity Consistency Isolation Durability อ่านได้ที่ http://dev.mysql.com/doc/refman/5.6/en/mysql-acid.html)
– สนับสนุน MVCC (Multi Version Concurrency Control) (อ่านได้ที่ http://en.wikipedia.org/wiki/Multiversion_concurrency_control)
– ล็อกข้อมูลได้ในระดับแถว (row) (MyISAM ล็อกเป็น table)
– สนับสนุนคีย์รอง (Foreign Key)
– คีย์หลักถูกทำดรรชนี (index) เป็นกลุ่ม
ข้อจำกัด
– ใช้ตัวอักษรทำดรรชนีไม่ได้
– ใช้เนื้อที่ในการเก็บข้อมูลเยอะกว่า MyISAM ประมาณ 2-3 เท่า
ไฟล์ข้อมูล จะมีสองไฟล์หลัก สองไฟล์รอง สำหรับทำแทรนเซคชัน
– [ชื่อตาราง].frm เก็บโครงสร้างของตาราง (format)
– ibdata1 เก็บข้อมูลของตาราง
ไฟล์ที่ทำแทรนเซคชัน
– ib_logfile0
– ib_logfile1
ทดลองสร้างตารางโดยใช้คำสั่ง
create table `innodb`(
`id` int
)engine=innodb;
ใช้เมื่อไหร่
ใช้กับงานที่ต้องการความถูกต้องของข้อมูลมากๆ เช่น ระบบ ATM ที่ต้องมีการจบกระบวนการทั้งหมดก่อน ถึงจะเขียนข้อมูล (commit) ลงไป ถ้าหากมีความผิดพลาดในจุดใดจุดหนึ่ง สามารถย้อนข้อมูลกลับ (rollback) ได้
3. Memory
ข้อมูลแบบ Memory หรือ Heap ใช้วิธีการเก็บลงไปในหน่วยความจำเลย
คุณสมบัติ
– ความเร็วสูงมาก เนื่องจากเก็บข้อมูลลงในหน่วยความจำตรงๆ เลย
– ทำแทรนเซคชันไม่ได้
– ใช้ B-Tree ทำดรรชนี
ข้อจำกัด
– ข้อมูลจะหายไปทั้งหมดเมื่อมีการรีเซ็ต MySQL หรือรีบูทเครื่อง
– ไม่สนับสนุนตารางชนิด text หรือ blob
– ขนาดของข้อมูลแต่ละแถวตายตัว
– ขนาดของตารางถูกจำกัดโดยหน่วยความจำของระบบ
ไฟล์ข้อมูล จะมีอยู่ไฟล์เดียวเท่านั้น
– [ชื่อตาราง].frm เก็บแค่รูปแบบของตารางเท่านั้น ไม่มีการเขียนข้อมูลจริงๆ
ใช้งานเมื่อไหร่
เนื่องจากข้อมูลทั้งหมดจะถูกเขียนลงหน่วยความจำโดยตรงทำให้มีความเร็วสูงมาก เหมาะสำหรับนำมาทำแคช (cache)
create table `memory`(
`id` int
)engine=memory;
4. Archive
เป็นฐานข้อมูลที่ออกแบบมาเพื่อเขียนอย่างเดียว รองรับการเก็บข้อมูลจำนวนมากๆ ได้ เหมาะสำหรับทำล็อก (log)
คุณสมบัติ
– มีความเร็วสูงมาก
– มีการล็อก (lock) ข้อมูลทุกแถว
ข้อจำกัด
– ทำแทรนเซคชันไม่ได้
– เขียนข้อมูล (insert) ได้อย่างเดียว ไม่สามารถแก้ไข (update) หรือลบ (delete) ได้
– ดรรชนีต้องเป็นตัวเลข (int) เท่านั้น ใช้ตัวอักษรไม่ได้
ไฟล์ที่เก็บข้อมูล
– [ชื่อตาราง].frm เก็บโครงสร้างตาราง
– [ชื่อตาราง].ARZ เก็บข้อมูลจริงๆ
– [ชื่อตาราง].ARM เก็บ meta ของตาราง
– [ชื่อตาราง].ARN เก็บข้อมูลชั่วคราว (temporary) ในระหว่างที่ตารางกำลังทำ Optimize
ใช้เมื่อไหร่
เนื่องจากสามารถเขียนข้อมูลได้อย่างเดียว ไม่สามารถลบหรือแก้ไขได้ ทำให้เหมาะกับการเขียน log มากๆ
MySQL ต่ำกว่า 5.5 ไม่สนับสนุน auto_increment ใน Archive นะ
5. Blackhole
เป็นรูปแบบตารางที่สามารถใช้ DML (ดูด้านบนได้) ในการจัดการคำสั่งได้เหมือนตารางทั่วๆ ไป แต่จะไม่สามารถดึงข้อมูล (select) กลับมาได้ เพราะไม่มีการเก็บข้อมูลไว้จริงๆ
คุณสมบัติ
– เร็วมากๆ
– ไม่ต้องการเนื้อที่สำหรับเก็บข้อมูล เพราะไม่ได้เขียนข้อมูลจริงๆ
– ใช้สำหรับวัดความเร็วของระบบเท่านั้น (Benchmark)
ข้อจำกัด
– ไม่สนับสนุนแทรนเซคชัน
– ไม่มีการเก็บข้อมูลจริงๆ
ใช้งานเมื่อไหร่
ใช้สำหรับวัดความเร็วของระบบหรือความเร็วของตารางข้อมูลที่ออกแบบไว้เท่านั้น (เคยใช้แค่นี้จริงๆ)