เทคนิคการตรวจสอบประสิทธิภาพของ Query และการเลือกใช้วิธีที่เหมาะสม
การตรวจสอบความเร็วในการทำงานของเว็บไซต์เป็นงานอย่างหนึ่งที่เราควรทำอย่างสม่ำเสมอในทุกขั้นตอนของการออกแบบ เพราะยิ่งเว็บเราสามารถทำงานได้มีประสิทธิภาพมากเพียงใด ค่าใช้จ่ายในระยะยาวก็จะลดลงเป็นเงาตามตัว
ฐานข้อมูลก็เช่นกัน เราก็จำเป็นต้องตรวจสอบประสิทธิภาพของ Query ที่เราเขียนด้วย เพราะถ้าเราเขียน Query ได้ดีเท่าใด เว็บเราก็ทำงานได้เร็วขึ้นเป็นเงาตามตัวด้วย การรู้จัก Query ที่เราใช้ การเข้าใจกลไกการทำงานของ Query จะทำให้เราสามารถเลือกใช้ Query ที่ดีที่สุด ให้เหมาะสมกับงาน
เครื่องมือที่เราจะใช้ในการตรวจสอบประสิทธิภาพของ Query มีหลายตัวด้วยกัน ตั้งแต่การรันคำสั่ง SQL บน Command line หรือ บน phpMyAdmin หรือบนเครื่องมือจัดการฐานข้อมูลอื่นใด ก็มักจะมีความสามารถนี้ติดมาด้วย สามารถเลือกใช้ที่ถนัดได้เลย
วิธีการดูประสิทธิภาพของ Query แบบพื้นฐานที่สุดคือการดูเวลาประมาวลผลรวม ยกตัวอย่างเปรียบเทียบคำสั่ง Sql สองชุดที่ทำงานคล้ายๆกัน
วิธีที่ 1
วิธีที่ 2
ถ้าดูการประมวลผลรวม จากรูป ดูตรงคำว่า Time ซึ่งบอกเราว่าทั้ง 2 วิธีใช้เวลาประมวลผลลรวม .001ms เท่าๆกัน ซึ่งถ้าดูเผินๆจะเห็นว่า ทั้งสองวิธีมีประสิทธิภาพเท่ากัน สามารถใช้อันไหนก็ได้
กรณีที่สอง หากฐานข้อมูลมีขนาดใหญ่ขึ้น (เกือบสองล้านแถว) เวลาที่ใช้ในการประมวลผลเปลี่ยนไปตามรายละเอียดด้านล่าง
และ
จะเริ่มเห็นความแตกต่างระหว่าง 2 Query ซึ่ง Query แรกจะช้ากว่า ประมาณ 30ms
ถ้าดูจากข้อมูลด้านบนจะเห็นว่า วิธีที่ 2 จะดีกว่าในระยะยาว แต่ในการเขียนโปรแกรมไม่ได้มีเพียงการ Query ข้อมูลเท่านั้น การนำข้อมูลที่ได้ไปแสดงผลเพื่อให้คนสามารถเข้าใจได้ง่ายๆเป็นเรื่องถัดมาที่เราต้องคำนึงถึงด้วย ก่อนอื่นเรามาดูผลลัพท์ที่ได้ของทั้งสองวิธีกันก่อน
จะเห็นนะครับ ว่า นอกจากปัจจัยความเร็วในการทำงานของ SQL แล้ว เรายังต้องคำนึงถึงความยุ่งยากในการจัดการแสดงผลให้อ่านได้รู้เรื่อง และ เวลาที่ต้องใช้ในการดำเนินการเพิ่มเติมอีกด้วย
ทีนี้มาดูลึกลงไปอีกหน่อย
.png)
จากสองรูปด้านบนนี้ คำสั่ง Profile ของ Query มันบอกเราว่า เวลาทั้งหมดกว่า 99% หมดไปกับการส่งข้อมูลกลับ นั่นเป็นสาเหตุว่า ทำไมเราจึงไม่ควรใช้ SELECT * ใน Query เราควรเลือกเฉพาะคอลัมน์ที่ต้องการใช้งานเท่านั้น เช่น SELECT `column1`, `column2`, .... เพราะ ปริมาณข้อมูลที่ส่งกลับยิ่งเยอะ Query ก็จะใช้เวลารวมในการทำงานเพิ่มขึ้น
รายละเอียดที่ได้จาก Profile ยังบอกเราได้อีกว่า ในแบบที่ 2 มีการใช้หน่วยความจำและการประมวลผลมากกว่าแบบแรก เนื่องจากมีการใช้การเรียงลำดับในคำสั่ง (Creating sort index) และมีการใช้ตารางชั่วคราว (Creating tmp table) ซึ่งขั้นตอนเหล่านี้อาจจะทำให้ประสิทธิภาพโดยรวมของระบบลดลง ส่วนการใช้คำสั่ง IF ในแบบแรก ก็อาจจะทำให้ประสิทธิภาพของคำสั่ง ลดลงได้เช่นกัน (ในตัวอย่างไม่สามารถบ่งบอกความแตกต่างในการใช้คำสั่งนี้ได้เนื่องจากคำสั่งไม่ได้ซับซ้อน แต่ถึงอย่างไรก็ตามการใช้ฟังก์ชั่นต่างๆใน Query ก็มีผลต่อประสิทธิภาพของคำสั่งอย่างแน่นอนอยู่แล้ว ถ้าเป็นไปได้ก็ควรนำมาประมวลผลนอกฐานข้อมูล)
นอกจากนี้ การพิจารณา Query ที่เหมาะสม อาจต้องพิจารณาถึงผลลัพท์ที่เป็นไปได้ร่วมด้วย ยกตัวอย่างจาก Query ตามด้านบนจะเห็นว่า เมื่อจำนวนข้อมูลมีน้อยๆ เวลาที่ใช้ไปสำหรับแต่ละวิธีแทบจะไม่แตกต่าง แต่หากข้อมูลมีปริมาณมากขึ้น เวลาในการทำงานจะเริ่มมีผล อันเนื่องมาจาก กระบวนการทำงานทั้งหมดเวลาส่วนใหญ่หมดไปกับการส่งข้อมูลกลับ ซึ่งวิธีแรก มีแนวโน้มที่จะมีปริมาณข้อมูลส่งกลับที่มากกว่า เนื่องจากมันจะส่งข้อมูลในเดือนที่เป็น 0 กลับมาด้วย แต่ในทางกลับกัน หากข้อมูลมีการกระจายตัว เช่นมีข้อมูลทุกเดือนเหมือนๆกัน ขนาดข้อมูลที่ใช้ในการส่งกลับก็จะมีขนาดใกล้เคียงกัน ส่งผลให้เวลาในการประมวลผมรวมของทั้งสองแบบไม่ต่างกันนัก ซึ่งหากต้องนำไปรวมกับการจัดรูปแบบการแสดงผล วิธีที่สอง อาจใช้เวลารวมมากกว่าก็ได้
อีกวิธีที่จะช่วยเราทำความเข้าใจในการประมวลผล Sql ตลอดจนการบอกถึงต้นตอของปัญหาที่ทำให้ Query ช้าลงด้วยการ Explain คำสั่ง SQL (อธิบายคำสั่ง Sql)
ซึ่งจากรูปอาจจะไม่บ่งบอกข้อมูลอะไรมากนัก มันบอกเราแค่เพียงว่า คำสั่งทำงานกับข้อมูลประมาณเกือบสองล้านแถวเท่าๆกัน และไม่มีการใช้ index และในวิธีที่ 2 มีการใช้การเรียงลำดับและตารางชัวคราวเพิ่มเติมมาเท่านั้น ซึ่งหากเป็นคำสั่งที่ซับซ้อนกว่านี้ มันจะบอกอะไรต่อมิอะไรเราได้มากกว่านี้
หมายเหตุ
คุณสามารถดู Profile บนเครื่องมือจัดการฐานข้อมูลที่ใช้ ด้วยการใส่คำสั่ง SHOW PROFILE; ต่อท้าย Query (Query ที่จะทดสอบ ต้องปิดด้วย ; เพื่อบอกจุดสิ้นสุดของคำสั่ง)
ซึ่งเครื่องมือแต่ละตัวอาจมีวิธีการจัดการแสดงผลคำสั่ง Profile แตกต่างกันเล็กน้อย เช่นถ้าเป็นบน phpMyAdmin ให้ขีดถูกตัวเลือก Profiling ก่อนการรันคำสั่ง SQL
ส่วนการดู Explain บน phpMyAdmin สามารถทำได้โดยใส่คำสั่ง Explain ไว้ข้างหน้า Query
ฐานข้อมูลก็เช่นกัน เราก็จำเป็นต้องตรวจสอบประสิทธิภาพของ Query ที่เราเขียนด้วย เพราะถ้าเราเขียน Query ได้ดีเท่าใด เว็บเราก็ทำงานได้เร็วขึ้นเป็นเงาตามตัวด้วย การรู้จัก Query ที่เราใช้ การเข้าใจกลไกการทำงานของ Query จะทำให้เราสามารถเลือกใช้ Query ที่ดีที่สุด ให้เหมาะสมกับงาน
เครื่องมือที่เราจะใช้ในการตรวจสอบประสิทธิภาพของ Query มีหลายตัวด้วยกัน ตั้งแต่การรันคำสั่ง SQL บน Command line หรือ บน phpMyAdmin หรือบนเครื่องมือจัดการฐานข้อมูลอื่นใด ก็มักจะมีความสามารถนี้ติดมาด้วย สามารถเลือกใช้ที่ถนัดได้เลย
วิธีการดูประสิทธิภาพของ Query แบบพื้นฐานที่สุดคือการดูเวลาประมาวลผลรวม ยกตัวอย่างเปรียบเทียบคำสั่ง Sql สองชุดที่ทำงานคล้ายๆกัน
วิธีที่ 1
SELECT
YEAR(`last_update`) AS `year`,
SUM(IF(MONTH(`last_update`)=1,`total`,NULL)) AS `1`,
SUM(IF(MONTH(`last_update`)=2,`total`,NULL)) AS `2`,
SUM(IF(MONTH(`last_update`)=3,`total`,NULL)) AS `3`,
SUM(IF(MONTH(`last_update`)=4,`total`,NULL)) AS `4`,
SUM(IF(MONTH(`last_update`)=5,`total`,NULL)) AS `5`,
SUM(IF(MONTH(`last_update`)=6,`total`,NULL)) AS `6`,
SUM(IF(MONTH(`last_update`)=7,`total`,NULL)) AS `7`,
SUM(IF(MONTH(`last_update`)=8,`total`,NULL)) AS `8`,
SUM(IF(MONTH(`last_update`)=9,`total`,NULL)) AS `9`,
SUM(IF(MONTH(`last_update`)=10,`total`,NULL)) AS `10`,
SUM(IF(MONTH(`last_update`)=11,`total`,NULL)) AS `11`,
SUM(IF(MONTH(`last_update`)=12,`total`,NULL)) AS `12`
FROM `gcms_orders`
GROUP BY YEAR(`last_update`)
Affected rows: 0
Time: 0.001msวิธีที่ 2
SELECT YEAR(`last_update`),MONTH(`last_update`),SUM(`total`)
FROM `gcms_orders`
GROUP BY YEAR(`last_update`),MONTH(`last_update`)
Affected rows: 0
Time: 0.001msถ้าดูการประมวลผลรวม จากรูป ดูตรงคำว่า Time ซึ่งบอกเราว่าทั้ง 2 วิธีใช้เวลาประมวลผลลรวม .001ms เท่าๆกัน ซึ่งถ้าดูเผินๆจะเห็นว่า ทั้งสองวิธีมีประสิทธิภาพเท่ากัน สามารถใช้อันไหนก็ได้
กรณีที่สอง หากฐานข้อมูลมีขนาดใหญ่ขึ้น (เกือบสองล้านแถว) เวลาที่ใช้ในการประมวลผลเปลี่ยนไปตามรายละเอียดด้านล่าง
SELECT
YEAR(`create_date`) AS `year`,
SUM(IF(MONTH(`create_date`)=1,`bids`,NULL)) AS `1`,
.................
FROM `bid_pramool_auction`
WHERE YEAR(`create_date`)=1983
Affected rows: 0
Time: 0.316msและ
SELECT YEAR(`create_date`),MONTH(`create_date`),SUM(`bids`)
FROM bid_pramool_auction
WHERE YEAR(`create_date`)=1983
GROUP BY YEAR(`create_date`),MONTH(`create_date`)
Affected rows: 0
Time: 0.289msจะเริ่มเห็นความแตกต่างระหว่าง 2 Query ซึ่ง Query แรกจะช้ากว่า ประมาณ 30ms
ถ้าดูจากข้อมูลด้านบนจะเห็นว่า วิธีที่ 2 จะดีกว่าในระยะยาว แต่ในการเขียนโปรแกรมไม่ได้มีเพียงการ Query ข้อมูลเท่านั้น การนำข้อมูลที่ได้ไปแสดงผลเพื่อให้คนสามารถเข้าใจได้ง่ายๆเป็นเรื่องถัดมาที่เราต้องคำนึงถึงด้วย ก่อนอื่นเรามาดูผลลัพท์ที่ได้ของทั้งสองวิธีกันก่อน
จะเห็นนะครับ ว่า นอกจากปัจจัยความเร็วในการทำงานของ SQL แล้ว เรายังต้องคำนึงถึงความยุ่งยากในการจัดการแสดงผลให้อ่านได้รู้เรื่อง และ เวลาที่ต้องใช้ในการดำเนินการเพิ่มเติมอีกด้วย
ทีนี้มาดูลึกลงไปอีกหน่อย
จากสองรูปด้านบนนี้ คำสั่ง Profile ของ Query มันบอกเราว่า เวลาทั้งหมดกว่า 99% หมดไปกับการส่งข้อมูลกลับ นั่นเป็นสาเหตุว่า ทำไมเราจึงไม่ควรใช้ SELECT * ใน Query เราควรเลือกเฉพาะคอลัมน์ที่ต้องการใช้งานเท่านั้น เช่น SELECT `column1`, `column2`, .... เพราะ ปริมาณข้อมูลที่ส่งกลับยิ่งเยอะ Query ก็จะใช้เวลารวมในการทำงานเพิ่มขึ้น
รายละเอียดที่ได้จาก Profile ยังบอกเราได้อีกว่า ในแบบที่ 2 มีการใช้หน่วยความจำและการประมวลผลมากกว่าแบบแรก เนื่องจากมีการใช้การเรียงลำดับในคำสั่ง (Creating sort index) และมีการใช้ตารางชั่วคราว (Creating tmp table) ซึ่งขั้นตอนเหล่านี้อาจจะทำให้ประสิทธิภาพโดยรวมของระบบลดลง ส่วนการใช้คำสั่ง IF ในแบบแรก ก็อาจจะทำให้ประสิทธิภาพของคำสั่ง ลดลงได้เช่นกัน (ในตัวอย่างไม่สามารถบ่งบอกความแตกต่างในการใช้คำสั่งนี้ได้เนื่องจากคำสั่งไม่ได้ซับซ้อน แต่ถึงอย่างไรก็ตามการใช้ฟังก์ชั่นต่างๆใน Query ก็มีผลต่อประสิทธิภาพของคำสั่งอย่างแน่นอนอยู่แล้ว ถ้าเป็นไปได้ก็ควรนำมาประมวลผลนอกฐานข้อมูล)
นอกจากนี้ การพิจารณา Query ที่เหมาะสม อาจต้องพิจารณาถึงผลลัพท์ที่เป็นไปได้ร่วมด้วย ยกตัวอย่างจาก Query ตามด้านบนจะเห็นว่า เมื่อจำนวนข้อมูลมีน้อยๆ เวลาที่ใช้ไปสำหรับแต่ละวิธีแทบจะไม่แตกต่าง แต่หากข้อมูลมีปริมาณมากขึ้น เวลาในการทำงานจะเริ่มมีผล อันเนื่องมาจาก กระบวนการทำงานทั้งหมดเวลาส่วนใหญ่หมดไปกับการส่งข้อมูลกลับ ซึ่งวิธีแรก มีแนวโน้มที่จะมีปริมาณข้อมูลส่งกลับที่มากกว่า เนื่องจากมันจะส่งข้อมูลในเดือนที่เป็น 0 กลับมาด้วย แต่ในทางกลับกัน หากข้อมูลมีการกระจายตัว เช่นมีข้อมูลทุกเดือนเหมือนๆกัน ขนาดข้อมูลที่ใช้ในการส่งกลับก็จะมีขนาดใกล้เคียงกัน ส่งผลให้เวลาในการประมวลผมรวมของทั้งสองแบบไม่ต่างกันนัก ซึ่งหากต้องนำไปรวมกับการจัดรูปแบบการแสดงผล วิธีที่สอง อาจใช้เวลารวมมากกว่าก็ได้
อีกวิธีที่จะช่วยเราทำความเข้าใจในการประมวลผล Sql ตลอดจนการบอกถึงต้นตอของปัญหาที่ทำให้ Query ช้าลงด้วยการ Explain คำสั่ง SQL (อธิบายคำสั่ง Sql)
ซึ่งจากรูปอาจจะไม่บ่งบอกข้อมูลอะไรมากนัก มันบอกเราแค่เพียงว่า คำสั่งทำงานกับข้อมูลประมาณเกือบสองล้านแถวเท่าๆกัน และไม่มีการใช้ index และในวิธีที่ 2 มีการใช้การเรียงลำดับและตารางชัวคราวเพิ่มเติมมาเท่านั้น ซึ่งหากเป็นคำสั่งที่ซับซ้อนกว่านี้ มันจะบอกอะไรต่อมิอะไรเราได้มากกว่านี้
หมายเหตุ
คุณสามารถดู Profile บนเครื่องมือจัดการฐานข้อมูลที่ใช้ ด้วยการใส่คำสั่ง SHOW PROFILE; ต่อท้าย Query (Query ที่จะทดสอบ ต้องปิดด้วย ; เพื่อบอกจุดสิ้นสุดของคำสั่ง)
SELECT
YEAR(`create_date`) AS `year`,
SUM(IF(MONTH(`create_date`)=1,`bids`,NULL)) AS `1`,
.......
FROM `bid_pramool_auction`
WHERE YEAR(`create_date`)=1983;
SHOW PROFILE;ซึ่งเครื่องมือแต่ละตัวอาจมีวิธีการจัดการแสดงผลคำสั่ง Profile แตกต่างกันเล็กน้อย เช่นถ้าเป็นบน phpMyAdmin ให้ขีดถูกตัวเลือก Profiling ก่อนการรันคำสั่ง SQL
ส่วนการดู Explain บน phpMyAdmin สามารถทำได้โดยใส่คำสั่ง Explain ไว้ข้างหน้า Query
EXPLAIN SELECT
YEAR(`create_date`) AS `year`,
SUM(IF(MONTH(`create_date`)=1,`bids`,NULL)) AS `1`,
.....
FROM `bid_pramool_auction`
WHERE YEAR(`create_date`)=1983