Tips for speeding up dump and import data in MySQL

เมื่อต้อง import ข้อมูล SQL จำนวนมหาศาลเข้าไปยัง MySQL server โดยปกติแล้วจะใช้เวลาในการ import ค่อนข้างนาน แต่ก็สามารถช่วยเร่ง speed การ import ให้ไวมากขึ้นกว่าเดิมได้โดยใช้ tip เล็กๆ น้อยๆ ดังต่อไปในในการ import ข้อมูล SQL จำนวนมหาศาล

ปิดการใช้งาน index ชั่วคราว (Disable index)
การปิดการใช้งาน index ในระหว่างการ import ข้อมูลจะช่วยเพิ่มความไวในการ import มากๆ และยังช่วยลดการทำงานของ MySQL server ด้วย ทำให้ระบบไม่ได้รับผลกระทบเมื่อระบบ online และจำเป็นต้องทำการ import ข้อมูลไปด้วย

ก่อนทำการ import ข้อมูลให้ทำการ Disable index ด้วยคำสั่ง
[shell]ALTER TABLE `table_name` DISABLE KEYS;[/shell]

หลังจาก import ข้อมูลเรียบร้อยแล้วให้ทำการ Enaable index ด้วยคำสั่ง
[shell]ALTER TABLE `table_name` ENABLE KEYS;[/shell]

* แทน table_name ด้วย ชื่อ table ที่มี index

Tip สำหรับ table ที่ใช้ MyISAM
ให้ใช้ INSERT DELAYED แทนการใช้ INSERT แบบทั่วไป ซึ่งจะช่วยให้ไม่ต้องรอการตอบกลับจากระบบเมื่อทำการ insert เสร็จแล้ว จึงช่วยเพิ่มความรวดเร็วในการ import ได้ในระดับหนึ่ง

ถ้าหากเป็น table ที่ใช้ InnoDB จำเป็นจะต้องรันคำสั่งเพิ่ม
ให้ปิดการ autocommit mode เพื่อป้องกันไม่ให้ log flush ไปยัง disk ทุกๆการ imsert ข้อมูล
ถ้าหากมี UNIQUE constraint ก็สามารถปิดการใช้งานชั่วคราวเพื่อช่วยเพิ่ม speed ในการ import ข้อมูล ระบบจะได้ไม่ต้องตรวจสอบข้อมูลในขณะที่ import ข้อมูล
ถ้าหากมี FOREIGN KEY constraints ในตาราง ให้ปิดการใช้งานชั่วคราวเพื่อช่วยเพิ่ม speed ในการ import ข้อมูล ระบบจะได้ไม่ต้องตรวจสอบ foreign key ในขณะที่ import ข้อมูล

ให้รันคำสั่งต่อไปนี้ก่อน import ข้อมูล
[shell]SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;[/shell]

หลังจาก import ข้อมูลเรียบร้อยแล้วให้รันคำสั่งดังนี้
[shell]SET UNIQUE_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;[/shell]