Top 20+ MySQL Best Practices
Posted on Thursday, October 3rd, 2013 at 2:02 pmถอดความจาก http://net.tutsplus.com/tutorials/other/top-20-mysql-best-practices/ เอาคร่าว ๆ นะ รูปประกอบ ไปดูได้จากเว็บต้นทาง ขี้เกียจทำรูปใหม่ และไม่อยาก repost/hot link รูปเขา
1. เขียน query ที่ทำให้ query cache ทำงานได้ดี
query cache คือระบบหนึ่งของ MySQL ที่จะช่วยลดโหลดของการ query โดยถ้าเรา query ข้อมูลหนึ่งครั้ง MySQL จะเก็บผลการ query ไว้ ดังนั้นถ้าเรา query ซ้ำเดิม (เช่นจากผู้ใช้คนอื่น แต่ใช้ query เดียวกัน) MySQL ก็สามารถเอาผลการ query ส่งไปให้ได้เลย โดยไม่ต้องไปค้นข้อมูลใหม่ ซึ่งทำให้การ query ทำงานได้ไว และลดโหลดของ server ได้เยอะ
ทีนี้ การเขียน query ก็มีผลต่อการ cache เหมือนกัน ลองดูตัวอย่าง
// query cache does NOT work
$r = mysql_query(“SELECT username FROM user WHERE signup_date >= CURDATE()”);
// query cache works!
$today = date(“Y-m-d”); $r = mysql_query(“SELECT username FROM user WHERE signup_date >= ‘$today'”);
อันบน cache ไม่ทำงาน เพราะเราใช้ CURDATE() ซึ่งเป็น function ที่ทำงานในระบบฐานข้อมูล ซึ่งทำให้ MySQL ต้อง execute query นี้เสมอ ซึ่ง function ในลักษณะนี้ก็เช่น NOW(), RAND() เป็นต้น ดังนั้น ถ้าเป็นไปได้ ควรหลีกเลี่ยงการทำงานในลักษณะนี้ เช่นในบรรทัดล่าง เราสร้าง string ของวันที่ปัจจุบันขึ้นมาก่อน แล้วค่อยส่งไป query เป็นต้น ทำให้ฐานข้อมูลเอา query นี้ไปเทียบได้เลย ต้องไม่ต้องไปทำงานภายในก่อน
2. ใช้คำสั่ง EXPLAIN เพื่อวิเคราะห์ SELECT query
พอเขียน query เสร็จ ควรใช้คำสั่ง EXPLAIN เพื่อวิเคราะห์ดูว่า query นั้นทำงานดีไหม และมีปัญหาที่เกิดจากโครงสร้งาของตารางหรือไม่ โดยเฉพาะถ้าเป็น query ที่มีการ join เยอะ ๆ ลองดูตัวอย่างในรูปของเว็บต้นทาง จะเห็นว่า การใช้คำสั่ง EXPLAIN จะแสดงให้เห็นถึงการ query ที่ไม่บน table ที่ไม่ได้สร้าง index key ที่เหมาะสม จึงทำให้จำนวน rows ที่เกี่ยวข้องมีสูง (7883 rows) ซึ่งพอใส่ index เข้าไป จำนวน rows ที่เกี่ยวข้องก็ลดลงอย่างมาก ซึ่งแน่นอนว่า การ query ก็จะไวขึ้น
3. ใช้ LIMIT 1 เมื่อต้องการ query แค่ record เดียว
อันนี้คงไม่ต้องการอะไรมาก คือถ้าเราต้องการข้อมูลแค่ record เดียว เช่นจะดูว่า มี account นี้ในระบบไหม หรือว่า มีรหัสนักศึกษานี้ในระบบไหม ให้ใส่ LIMIT 1 เสมอ เพราะในการ query ที่มี LIMIT 1 ถ้า MySQL พบข้อมูลตัวแรกแล้ว(ซึ่งก็คงมีแค่ตัวเดียว) มันก็จะหยุดการ query แล้วให้ผลคืนทันที ซึ่งต่างจากการไม่ใส่ LIMIT 1 ที่ MySQL จะต้องเช็คไปจนสุดตาราง ถึงจะหยุดการทำงานได้
4. ใส่ index สำหรับทุก field ที่ต้องการค้นหา
หลักการง่าย ๆ คือ ถ้ามี field ไหนไปโผล่ใน WHERE clause ของ SELECT จงใส่ index ให้มันเสีย เพื่อให้มัน search ได้ไวขึ้น
แน่นอน วิธีการนี้ใช้ได้กับกรณี exact match เช่น WHERE lastname like ‘pruet’ หรือว่า partial match เช่น WHERE lastname like ‘pr%’ เท่านั้น ถ้าต้องการทำ full text match เช่น WHERE lastname like ‘%pr%’ การทำ index จะไม่ช่วยอะไร ต้องไปใช้ MySQL fulltext search แทน ดังนั้น พยายามหลีกเลี่ยงการทำ full text match ด้วย
5. ใช้ข้อมูลประเภทเดียวกันใน column ที่เอามา join กัน และให้ทำ index ทั้งสองฝั่งด้วย
การ join เป็นการทำงานที่มี cost สูงมาก เพราะจะต้องมีการเอาข้อมูลจากสอง table มาเทียบกัน ในฐานข้อมูลที่ออกแบบไม่ดี การ join สอง table หมายถึงการจำนวน query ที่เท่ากับ จำนวน record ของทั้งสอง table มาคูณกัน ซึ่งเป็นการทำงานที่ช้ามากแน่นอน
สิ่งที่ต้องทำเพื่อหลีกเลี่ยงกรณีนั้นคือ ใน column ที่จะเอามา join กัน ให้ทำ index เสมอ และทั้งสอง column จะต้องเป็น data type เดียวกันเท่านั้น เพราะถ้าเป็นคนละ data type (เช่น DECIMAL กับ INT) แล้ว MySQL จะใช้ index ในการ join ไม่ได้
6. อย่าใช้ ORDER BY RAND()
ถ้าเขียนโค้ดแบบนี้
$r = mysql_query(“SELECT username FROM user ORDER BY RAND() LIMIT 1”);
MySQL จะต้องเรียกคำสั่ง RAND() ในทุก record ก่อน แล้วจึงเอา record มาเรียงกัน แล้วจึงดึงออกมาแค่หนึ่ง record ซึ่งแน่นอนว่าทำงานช้ามาก
ควรทำแบบนี้ดีกว่า
$r = mysql_query(“SELECT count(*) FROM user”);
$d = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] – 1);
$r = mysql_query(“SELECT username FROM user LIMIT $rand, 1”);
โค้ดยาวขึ้น แต่ทำงานเร็วกว่าเยอะ เพราะทำงาน rand แค่ครั้งเดียว (แถมทำนอก MySQL ด้วย) แล้วจึงไป query ออกมาแค่ record เดียว
7. หลีกเลี่ยง SELECT *
ถ้าต้องการข้อมูลเพียงไม่กี่ field ให้ระบุไปเลยว่าต้องการอะไร อย่าใช้ SELECT * เพราะยิ่งข้อมูลที่เราสั่งให้ดึงมาน้อย ก็จะลดโหลดทั้งในส่วนของการเรียกข้อมูลจาก harddisk และการส่งข้อมูลผ่าน network โดยเฉพาะถ้าต้องการข้อมูลเยอะ ๆ
8. ทุก table ต้องมี field id
ทุก table ควรจะมี field ที่เป็น PRIMARY KEY, AUTO_INCREEMENT, UNSIGNED ที่กำหนดให้เป็น id ซึ่งควรจะใช้ในการเชื่อม table เข้าด้วยกัน เพราะว่า การ query table ด้วย field id ในลักษณะดังกล่าว จะทำได้เร็วกว่าการ query โดยใช้ field ที่เป็น VARCHAR มาก (ต่อให้ทำ primary key/index แล้วก็ตาม)
9. ถ้าเป็นไปได้ พยายามใช้ ENUM แทนที่ VARCHAR
column แบบ ENUM ทำงานได้ไว และใช้พื้นที่น้อย ดังนั้นถ้ามีข้อมูลที่รู้อยู่แล้วว่ามีอะไรบ้าง และจำกัด เช่น พวก status ต่าง ๆ ก็ควรจะใช้เป็น ENUM แล้วระบุไปเลยว่ามีอะไรบ้าง
ข้อดีอีกอย่างคือ ลดการพิมพ์ผิดในกรณีของ VARCHAR ได้ เพราะถ้าพิมพ์ผมาผิด ENUM จะโวยวายทันทีว่าไม่มีข้อมูลแบบนี้
10. ใช้คำสั่ง PROCEDURE ANALYSE() เพื่อวิเคราะห์โครงสร้างตาราง
คำสั่ง PROCEDURE ANALYSE() มีประโยชน์มากในการวิเคราะห์โครงสร้างตาราง “และข้อมูลที่อยู่ด้านใน” ว่าเหมาะสมไหม เช่น ถ้าเราระบุให้ primary key เป็น INT แต่จริง ๆ แล้ว มีข้อมูลไม่กี่ตัว มันก็จะแนะนำให้ใช้ MEDIUMINT แทน ตัวอย่างเช่น ตารางที่เก็บรายชื่อของคณะต่าง ๆ ในมหาวิทยาลัยเป็นต้น
ถ้าใช้ phpmyadmin อยู่ จะมีคำสั่ง Propose table restructure อยู่ ซึ่งมันก็เรียก PROCEDURE ANALYSE() นี่แหละมาทำงาน
11. ใช้ NOT NULL เสมอ
ถ้าในตารางไม่ได้ใช้ค่า NULL ทำอะไร ซึ่งส่วนใหญ่ก็จะเป็นอย่างนั้น ก็ให้ระบุในค่า column ว่าเป็น NOT NULL แล้วใช้ค่า default จริง ๆ ไปเลย เช่น 0 สำหรับ INT และ string ว่าง สำหรับพวกข้อความต่าง ๆ อย่างแรกคือ NULL column กินพื้นที่เยอะกว่าข้อมูลอื่น ๆ และเวลาเขียนโค้ดก็ต้องมานั่งเช็คอีกว่า มันจะ NULL ไหม
12. พยายามใช้ prepare ในการสร้าง query
โดยทั่วไป เราเขียน query ได้สองวิธี คือผ่าน prepare หรือว่า query เข้าไปตรง ๆ การใช้ prepare ข้อดีอย่างแรกคือ ช่วยป้องการ SQL injection ได้ เพราะมันจะทำการ filter ข้อมูลให้ก่อนหนึ่งชั้น ส่วนในแง่ของ performance นั้น ถ้าเกิดว่า query นั้นมีการใช้ซ้ำ ๆ หลาย ๆ ครั้ง เช่น การดึงข้อมูลมาสร้างเป็นตาราง การใช้ prepare จะช่วยลดโหลดของ MySQL ลงได้ เพราะ MySQL จะทำการวิเคราะห์ query นั้นแค่ครั้งเดียว
ข้อจำกัดของ prepare มีแค่อย่างเดียว คือ ใน MySQL version 5.0 ลงมา query ที่ใช้ผ่าน prepare จะไม่ถูก cache
13. ใช้ unbeffered query ในการ query ข้อมูลใหญ่ ๆ
ปกติ เวลาเรา query ข้อมูลจากฐานข้อมูล MySQL จะสร้าง buffer อันหนึ่งไว้เก็บ query result ทั้งหมด และเมื่อได้ result ทั้งหมดแล้ว ก็จะส่งข้อมูลใน buffer กลับมาให้ ซึ่งถ้าเรา query ข้อมูลขนาดใหญ่ จะส่งผลสองประการคือ 1. กินพื้นที่ memory ของ server 2. กว่าจะได้ข้อมูลกลับมา ก็ต้องรอให้ได้ข้อมูลครบก่อน ซึ่ง php ก็มีคำสั่ง mysql_unbuffered_query ให้ใช้ โดยจะไม่มีการรอให้ MySQL ค้นข้อมูลให้หมดก่อน แต่จะทยอยส่งมาให้เลย ด้วยวิธีการนี้ จะช่วยลดขนาด memory ที่ใช้ และเพิ่มความเร็วในการได้ข้อมูลแรก ๆ ด้วย
ข้อจำกัดคือ เราจะต้องอ่านข้อมูลทั้งหมด หรือสั่ง mysql_free_result ก่อนที่จะส่ง query ถัดไป และกลุ่มคำสั่งที่จัดการข้อมูลแบบทั้งก้อน เช่น mysql_num_rows และ mysql_data_seek จะใช้งานไม่ได้
14. เก็บ IP address เป็น UNSIGNED INT
ปกติ เรามักจะเก็บ IP address เป็น VARCHAR(15) ด้วยว่ามันมีจุดหลายจุด แต่จริง ๆ แล้ว เราสามารถเก็บมันเป็นตัวเลขแบบ UNSIGNED INT ด้วย ซึ่งกินพื้นที่น้อยกว่า index ได้ไวกว่า และเปรียบเทียบกันได้ไวกว่าด้วย วิธ๊การก็คือ สร้าง column ให้เป็น UNSIGNED INT แล้วใช้ function INET_ATON() และ INET_NTOA() ของ MySQL แปลงไป/แปลงกลับระหว่าง IP ADDRESS และ ตัวเลข เช่น ตัวอย่าง
$r = “UPDATE users SET ip = INET_ATON(‘{$_SERVER[‘REMOTE_ADDR’]}’) WHERE user_id = $user_id”;
ถ้าไม่อยากไปทำที่ระดับ MySQL ใน PHP ก็มีคำสั่ง ip2long() กับ long2ip() ให้ใช้
15. table แบบ Fixed-length ทำงานเร็วกว่า
table ที่ทุก column มีขนาดจำกัด (fixed-length) จะเรียกว่า static หรือ fixed-length table ซึ่งชนิด column ที่”ไม่ใช่” fixed-length คือ VARCHAR, TEXT และ BLOB. ดังนั้น ถ้าสามารถหลีกเลี่ยง column ทั้งสามชนิดนี้ได้ จะทำให้ MySQL ทำงานกับ table นั้นได้ไวกว่ามาก เพราะว่าเวลา MySQL จะไปอ่านข้อมูลใน record หนึ่ง ๆ มันจะสามารถคำนวณตำแหน่งได้ง่าย (เช่น ตำแหน่งที่ต้องการ x ความยาว record) ซึ่งถ้า table มันไม่ fixed MySQL ก็ต้องวิ่ง seek หาตำแหน่งเอา หรือว่าไปดูจาก primary key
ถ้าจำเป็นต้องมี column ที่ไม่ใช่ fixed-length อาจจะใช้เทคนิค Vertical Partitioning ในการแยก column ที่ fixed กับ ไม่ fixed ออกจากกัน ลองดูข้อถัดไป
16. Vertical Partitioning
เป็นเทคนิคการแบ่ง table ออกเป็นหลายส่วน โดยการกระจาย column ออกไปเพื่อผลทางประสิทธิภาพ สมมติว่าเรามี table หนึ่งที่มี column ดังต่อไปนี้ table1(id, login, fname, lname, address, last_login)
ตัวอย่างที่ 1: เช่นถ้าเรารู้ว่า บาง column ไม่ได้ถูกใช้บ่อย ๆ (เช่น ที่อยู่) ก็สามารถแย่งออกไปเป็นอีก table หนึ่ง เช่นแยกเป็น table1(id, login, fname, lname, last_login) และ table2(id, address) ซึ่งจะทำให้ table1 มีขนาดเล็กลงมาก และทำงานได้ไวขึ้น
ตัวอย่างที่ 2: ถ้าเรารู้ว่า บาง column มีการ update ถี่ ๆ ในขณะที่ column อื่นไม่มีการเปลี่ยนแปลง ก็อาจจะแยก column นั้นออกมา เพื่อให้ข้อมูลใน column อื่น ๆ คงอยู่ใน cache ได้ เช่นในตัวอย่าง last_login จะมีการ update ทุกครั้งที่ผู้ใช้ติดต่อ server ซึ่งการ update แต่ละครั้ง ก็จะทำให้ทั้ง record ถูก flush ออกไป เราจึงควรแบ่ง table ออกเป็น table1(id, login, fname, lname, address) และ t2(id, last_login) เพื่อให้การ update เกิดใน t2 เท่านั้น
ข้อควรระวังคือ พอแบ่งเป็น 2 table แล้ว ก็ไม่ควรให้มัน join กันบ่อย ๆ เพราะมันมีค่าใช้จ่ายสูง ถ้า column ที่ถูกแยกออกมา ต้องถูกใช้ร่วมกันบ่อย ๆ ก็ไม่ควรแยกตั้งแต่แรก
17. กระจาย DELETE กับ INSERT query ใหญ่ ๆ ออกมา
ถ้าเราจำเป็นต้องทำการ DELETE หรือ INSERT ข้อมูลจำนวนมาก ๆ บนเว็บที่กำลังทำงานอยู่ ก็ไม่ควรทำทีเดียวทั้งหมด เพราะมันจะไป lock table ของเรา แล้วทำให้ MySQL ไม่สามารถทำงานอื่น ๆ ได้ วิธีการคือ ค่อย ๆ กระจายทำทีละน้อย ๆ เช่น ลบทีละ 10,000 record แล้วหยุดไปสักพัก เพื่อให้ MySQL สามารถไปทำอย่างอื่น เช่น จัดการ SELECT query ที่คั่งค้าง ก่อนจะกลับมาลบต่อ ลองดูตัวอย่าง
while (1) {
mysql_query(“DELETE FROM logs WHERE log_date <= ‘2009-10-01’ LIMIT 10000”);
if (mysql_affected_rows() == 0) {
// done deleting
break;
}
// you can even pause a bit
usleep(50000);
}
จากตัวอย่าง เราลบไป 10,000 record แล้วก็หยุดพักไปครู่ใหญ่ โดยใช้คำสั่ง usleep ก่อนที่จะสั่งลบต่อ
18. column เล็ก ๆ ทำงานไวกว่า
สิ่งที่ทำให้ database ทำงานช้าสุดคือ harddisk ดังนั้น ถ้าเราสามารถลดปริมาณข้อมูลที่ต้องอ่านเขียนจาก harddisk ได้ ระบบเราก็จะทำงานเร็วขึ้น และประหยัดพื้นที่ harddisk มากขึ้น วิธีการหนึ่งก็คือ การใช้ชนิด column ให้เหมาะสม เช่น ถ้าเราคิดว่าจะมี record แค่ไม่กี่อัน ก็อย่าใช้ INT เป็น primary key แต่เปลี่ยนเป็น MEDIUMINT, SMALLINT หรือแม้แต่ TINYINT แทน เป็นต้น หรือว่าถ้าต้องการเก็บแค่วันที่ ก็ใช้ DATE แทนที่จะเป็น DATETIME เป็นต้น
19. ใช้ storage engine ให้ถูก
ใน MySQL มี storage engine หลักอยู่สองตัวคือ MyISAM กับ InnoDB ถ้าเอาง่าย ๆ ก็คือ MyISAM เหมาะกับโปรแกรมที่เน้นการ read เพราะว่ามันเป็น table-level locking เวลามีคนพยายาม insert/update/delete มันจะล๊อคทั้ง table ซึ่งทำให้ทำงานช้า ส่วน InnoDB สนับสนุน row-based locking ซึ่งทำให้การ insert/update/delete จะทำได้ไวกว่า MyISAM แต่ด้วยความซับซ้อนของมัน โดยทั่วไปสำหรับโปรแกรมเล็ก ๆ ที่เน้น SELECT (โดยเฉพาะ SELECT COUNT(*)) มันเลยจะทำงานกว่า MyISAM
20. พยายามใช้ ORM
ORM หรือ Object Relational Mapper นอกจากจะทำให้เขียนโปรแกรมง่าย เพราะว่าไม่ต้องไปยุ่งกับ SQL มากแล้ว ยังสามารถเพิ่มประสิทธิภาพของโปรแกรมได้ด้วย เพราะว่า ORM library โดยทั่วไป จะถูกเขียนให้มีประสิทธิภาพสูงอยู่แล้ว และมันยังทำ lazy loading ได้ด้วย นั่นคือ ถ้าเราสั่งเรียกข้อมูลมา แต่ไม่ได้ใช้ทันที ORM ก็จะไม่เรียกมากให้ทันทีเช่นกัน โดยจะเรียกมาเพื่อจะใช้
21. ระวังการใช้ Persistent Connection
โดยทั่วไป Persistent Connection จะช่วยลด overhead ในการเชื่อมต่อได้ เพราะว่าเราไม่ต้องทำการสร้างการเชื่อมต่อทุกครั้งที่ต้อง query ข้อมูล แต่เนื่องจาก connection มันไม่ถูกปิด ดังนั้นมีโอกาสที่ connection ของ MySQL จะเต็มเพราะว่า Apache มัน reuse connection ของ child process จนทำให้ connection ปิดไม่ได้นั่นเอง ตัวอย่างเช่น ถ้า MySQL ใช้ table-locking แล้ว php script ก่อนหน้านี้ ไม่ได้ปลดล๊อคก่อนที่จะปิดตัว script ตัวถัดมา จะถูก lock ทันที เพราะว่า ตราบใดที่ connection ยังไม่ถูกปิด MySQL ก็ต้องคง lock อันนั้นไว้อยู่
คำแนะนำคือ อย่าใช้