นักพัฒนามักจะถามคำถามเดิมกับผมเสมอว่า: "API ของผมช้า จะเริ่มแก้ตรงไหนดี?" ปฏิกิริยาตอบโต้โดยสัญชาตญาณมักจะเป็นการอัปเกรดเซิร์ฟเวอร์หรือเพิ่ม RAM เป็นสองเท่า นั่นทำให้เสียเงินและแทบจะไม่ช่วยแก้ปัญหาที่ต้นเหตุเลย ในแอปพลิเคชัน Laravel ส่วนใหญ่ คอขวดมักจะอยู่ที่เลเยอร์ของฐานข้อมูล ไวยากรณ์ที่สวยงามของเฟรมเวิร์กทำให้เราลืมได้ง่ายว่าการเรียกใช้ Eloquent แต่ละครั้งจะกลายเป็น SQL ในท้ายที่สุด และ SQL นี่แหละที่มักจะเป็นจุดเริ่มต้นของปัญหา

ก่อนที่คุณจะแตะต้องการตั้งค่าเซิร์ฟเวอร์ ให้ตรวจสอบคิวรีของคุณอย่างเป็นระบบ

เริ่มต้นด้วยการวินิจฉัยที่ถูกต้อง

อย่าปรับแต่ง (optimize) แบบสุ่มสี่สุ่มห้า การเขียนคิวรีใหม่แบบสุ่มคือการเดา และการเดาจะทำให้คุณเสียเวลาไปหลายชั่วโมง

คุณต้องหาคำสั่งที่ใช้เวลาประมวลผลรวมมากที่สุด ตรวจสอบแอปพลิเคชันของคุณภายใต้ภาระงานจริง (real load) Laravel Telescope ช่วยให้คุณเห็นภาพรวมของทุกคิวรีที่ถูกรันระหว่างการร้องขอ (request) พร้อมข้อมูลเวลาที่ใช้ Laravel Debugbar จะแสดงข้อมูลเหล่านี้ในเบราว์เซอร์ของคุณระหว่างการพัฒนาในเครื่อง (local development) เพื่อให้คุณตรวจพบความผิดปกติได้ทันที เมื่อคุณต้องการตรวจจับปัญหาในระบบจริง (production) ให้เปิดใช้งาน MySQL Slow Query Log มันจะบันทึกคำสั่งที่ใช้เวลาเกินเกณฑ์ที่คุณกำหนดไว้ ซึ่งเหมาะมากสำหรับการค้นหาปัญหาที่ไม่ได้ปรากฏให้เห็นในชุดข้อมูลขนาดเล็ก หากคุณรันระบบที่มีขนาดใหญ่ขึ้น เครื่องมือ Application Performance Monitoring (APM) สามารถช่วยเชื่อมโยง HTTP endpoint ที่ทำงานช้าเข้ากับคำสั่งฐานข้อมูลที่เฉพาะเจาะจงได้

เมื่อคุณตรวจสอบข้อมูล ให้มองหาสองสิ่ง: เวลาที่ใช้ในการประมวลผลจริง (absolute execution time) และความถี่ในการเรียกใช้งาน (call frequency) คิวรีที่ใช้เวลา 40 มิลลิวินาทีอาจฟังดูไม่มีพิษมีภัย จนกว่าคุณจะพบว่ามันถูกรันถึง 2,000 ครั้งต่อนาที รายงานที่ใช้เวลา 3 วินาทีซึ่งรันเพียงชั่วโมงละครั้ง อาจมีความสำคัญน้อยกว่าการค้นหาข้อมูลที่ใช้เวลาครึ่งวินาทีแต่รันในทุกๆ หน้า แก้ไขปัญหาที่มีผลกระทบสูงก่อน

เลิกดึงข้อมูลทุกอย่างมาทั้งหมด

SELECT * นั้นสะดวก แต่มันก็มีราคาที่ต้องจ่าย (expensive) เมื่อคุณเขียน Model::all() หรือดึงชุดผลลัพธ์โดยไม่ระบุชื่อคอลัมน์ MySQL จะต้องดึงข้อมูลทุกฟิลด์สำหรับทุกแถวที่ตรงตามเงื่อนไข ซึ่งรวมถึงฟิลด์ข้อความขนาดใหญ่, JSON blobs และข้อมูลอื่นๆ ทั้งหมดที่อยู่ในตารางนั้น ชุดผลลัพธ์จะใหญ่ขึ้น การใช้หน่วยความจำจะสูงขึ้น และเวลาที่ใช้ในการทำ serialization ของการตอบกลับก็จะเพิ่มขึ้นด้วย

จงระบุให้ชัดเจน หาก controller ของคุณต้องการเพียงฟิลด์ id, name, และ email ก็ให้เรียกขอเฉพาะฟิลด์เหล่านั้น:

User::select('id', 'name', 'email')->get();

ใน query builder ก็ใช้หลักการเดียวกัน ข้อมูล (payload) ที่มีขนาดเล็กกว่าจะเคลื่อนที่ผ่านเครือข่ายได้เร็วกว่าและใช้ RAM บนเซิร์ฟเวอร์แอปพลิเคชันน้อยกว่า นี่คือหนึ่งในวิธีที่ประหยัดและได้ผลดีที่สุด แต่ก็มักจะถูกมองข้ามได้ง่ายเพราะ Laravel กำหนดให้ SELECT * เป็นพฤติกรรมเริ่มต้น

ให้ EXPLAIN เป็นตัวนำทางการแก้ไขของคุณ

อย่าทำการ refactor คิวรีที่ทำงานช้าโดยไม่ได้รัน EXPLAIN ก่อน ใน MySQL คีย์เวิร์ด EXPLAIN จะแสดงแผนการประมวลผลคิวรี (query execution plan) ซึ่งจะเผยให้เห็นว่า optimizer ตั้งใจจะค้นหาข้อมูลของคุณอย่างไร

ให้ความสำคัญกับคอลัมน์ type หากคุณเห็น ALL แสดงว่า MySQL กำลังทำ full table scan ซึ่งหมายความว่ามันกำลังอ่านทุกแถวเพื่อให้ตรงตามเงื่อนไขใน WHERE clause ของคุณ ดูที่คอลัมน์ key เพื่อดูว่า optimizer ได้ใช้ index บ้างหรือไม่ จากนั้นตรวจสอบคอลัมน์ Extra หากคุณพบ Using temporary หรือ Using filesort แสดงว่า MySQL กำลังสร้างตารางชั่วคราวหรือทำการเรียงลำดับในหน่วยความจำ เนื่องจากโครงสร้างปัจจุบันของคุณไม่สามารถตอบสนองคิวรีได้อย่างมีประสิทธิภาพ

รัน EXPLAIN ใน MySQL client ของคุณ หรือใช้เครื่องมือที่ช่วยจัดรูปแบบผลลัพธ์ให้ เมื่อคุณเห็นแผนการทำงานแล้ว คุณจะรู้ว่าปัญหาคือการขาด index, การ join ที่ไม่ดี หรือเงื่อนไข (predicate) ที่ engine ไม่สามารถ optimize ได้ ทำให้การเดาเป็นสิ่งที่ไม่จำเป็นอีกต่อไป

สร้าง Index อย่างมีจุดมุ่งหมาย

Index เป็นเครื่องมือที่ทรงพลังที่สุดในการเพิ่มความเร็วในการค้นหา แต่พวกมันจะทำงานได้ก็ต่อเมื่อสอดคล้องกับวิธีการที่คุณเขียนคิวรีเท่านั้น หากไม่มี index ที่เหมาะสม MySQL จะต้องสแกนทีละแถว ซึ่งอาจจะดูเหมือนไม่มีปัญหาในขั้นตอนการพัฒนาที่มีตารางเพียงหนึ่งพันแถว แต่จะพังทลายลงในระบบจริงที่มีตารางถึงสิบล้านแถว

เริ่มต้นด้วย single-column index สำหรับฟิลด์ที่ปรากฏบ่อยใน WHERE clause หากคุณมีการกรองด้วย status อยู่เสมอ ให้เพิ่ม index ใน status

เมื่อคิวรีมีการกรองหลายคอลัมน์พร้อมกัน ให้เปลี่ยนไปใช้ composite index ลำดับของคอลัมน์ภายใน index นั้นมีความสำคัญ เพราะ MySQL จะอ่าน composite index จากซ้ายไปขวา ซึ่งเรียกว่ากฎ leftmost prefix หากคิวรีของคุณค้นหาด้วย user_id แล้วเรียงลำดับด้วย created_at การสร้าง composite index บน (user_id, created_at) จะช่วยได้อย่างมาก หากสลับลำดับกัน optimizer อาจจะไม่ใช้ index สำหรับการกรองเลย

อย่าทำ index ในทุกคอลัมน์ เพราะแต่ละ index จะเพิ่ม overhead ในการ insert, update และ delete เนื่องจาก MySQL ต้องคอยรักษาโครงสร้างไว้ จงเพิ่ม index อย่างรอบคอบโดยอิงจากรูปแบบที่คุณพบระหว่างการวัดผล

หลีกเลี่ยงการใช้ฟังก์ชันกับคอลัมน์

ความผิดพลาดนี้จะทำให้ดัชนี (indexes) ถูกปิดใช้งานโดยที่คุณไม่รู้ตัว เมื่อคุณใช้ฟังก์ชันครอบคอลัมน์ภายใน WHERE clause, MySQL จะไม่สามารถ