วิธีโปรไฟล์แบบสอบถาม SQL เพื่อประสิทธิภาพที่ดีขึ้น

ที่ Servebolt เรามีชีวิตและหายใจ ด้วยประสิทธิภาพ

ประสิทธิภาพของฐานข้อมูลก็ไม่มีข้อยกเว้น

การดำเนินการค้นหาที่ไม่มีประสิทธิภาพหลังจากที่ผู้ เยี่ยม ชมเว็บไซต์คลิกลิงก์จะ ทำให้ประสบการณ์ของผู้ใช้ลดลงอย่างมากพวกเขาจะต้องรอให้คิวรีที่ช้าทำงานเต็มระยะเวลา ซึ่งอาจใช้เวลาหลายวินาที ก่อนที่การดำเนินการอื่นๆ จะเกิดขึ้น เช่น การแสดงหน้าเว็บ เวลารอนี้ไม่เพียงแต่รวมเวลาที่จำเป็นสำหรับการเรียกใช้คิวรีเท่านั้น แต่ยังรวมถึงเวลาเพิ่มเติมที่จำเป็นสำหรับการประมวลผลก่อนและหลังการประมวลผลด้วย ด้วยเหตุนี้ ข้อความค้นหาที่ออกแบบมาไม่ดีอาจทำให้ประสิทธิภาพโดยรวมของเว็บไซต์ช้าลงอย่างมาก ส่งผลให้ผู้ใช้รู้สึกหงุดหงิด

Time to First Byte (TTFB) เป็นวิธีวัดว่าใช้เวลานานเท่าใดในการรับข้อมูลไบต์แรกหลังจากที่ผู้ใช้ส่งคำขอไปยังเว็บไซต์นอกจากนี้ยังเป็นเมตริกสำคัญที่เครื่องมือค้นหาใช้ในการประเมินไซต์อีกด้วย เมื่อมีการทริกเกอร์ข้อความค้นหาที่ช้า จะส่งผลเสียต่อ TTFB ยิ่งการสืบค้นที่ช้าใช้เวลานานเท่าใด TTFB ก็จะยิ่งสูงขึ้น ส่งผลให้ประสิทธิภาพโดยรวมของเว็บไซต์ช้าลงและประสบการณ์ผู้ใช้ที่น่าพอใจน้อยลง

ในคู่มือนี้ เราจะแนะนำวิธีการทำโปรไฟล์การสืบค้น SQL ซึ่งเป็นส่วนสำคัญในการรักษาประสิทธิภาพของเว็บแอปพลิเคชันที่อาศัยการตอบกลับของฐานข้อมูล นี่เป็นกระบวนการที่กำหนดรากฐานเพื่อให้สามารถเริ่มทำงานเพื่อเพิ่มประสิทธิภาพการค้นหาเหล่านี้เพื่อปรับปรุงประสิทธิภาพของพวกเขา

ทำความเข้าใจเกี่ยวกับการทำโปรไฟล์แบบสอบถาม SQL

เมื่อคุณพัฒนาเว็บแอปพลิเคชันและเริ่มทำงานในระดับที่ใหญ่ขึ้น การสืบค้น SQL ที่เคยทำงานได้อย่างราบรื่นอาจทำให้เกิดปัญหาด้านประสิทธิภาพได้ โดยทั่วไปแล้ว มีแนวโน้มว่าจะมีข้อความค้นหาจำนวนมากขึ้นเรื่อยๆ ที่ทำงานเทียบกับข้อมูลจำนวนที่เพิ่มขึ้นพร้อมกับจำนวนคำขอต่อวินาทีที่เพิ่มขึ้น และเมื่อประสิทธิภาพลดลง ประสบการณ์ที่ผู้ใช้ของคุณได้รับเมื่อโต้ตอบกับไซต์ ซอฟต์แวร์ หรือบริการของคุณก็เช่นกัน

การทำโปรไฟล์แบบสอบถามเป็นวิธีการวิเคราะห์การสืบค้นฐานข้อมูล ประเมินประสิทธิภาพ และระบุปัญหาที่อาจเกิดขึ้น

ด้วยการวิเคราะห์และระบุข้อความค้นหาที่มีปัญหาเหล่านี้ คุณสามารถทำการปรับปรุงเฉพาะที่สามารถสร้างความแตกต่างที่วัดได้ให้กับประสิทธิภาพของฐานข้อมูล ในทางกลับกัน สิ่งนี้จะช่วยให้สามารถปรับขยายได้ดีขึ้นในอนาคต เช่นเดียวกับความพึงพอใจโดยรวมของลูกค้า เนื่องจากแอปและไซต์จะตอบสนองได้ดีขึ้น

MariaDB (และ MySQL) มีเครื่องมือและเทคนิคมากมายสำหรับการทำโปรไฟล์แบบสอบถาม ซึ่งเราจะกล่าวถึงในบทความนี้ เมื่อ ระบุ ข้อความค้นหาที่ช้าแล้ว ขั้นตอนต่อไปคือ การปรับให้เหมาะสม กระบวนการนี้รวมถึงการระบุสาเหตุของปัญหา และทำการเปลี่ยนแปลงโครงสร้างของแบบสอบถามเพื่อปรับปรุงประสิทธิภาพ

วิธีโปรไฟล์แบบสอบถาม SQL (7 วิธี)

เรามาเริ่มด้วยการแจกแจงเครื่องมือและเทคนิคต่างๆ ที่มีให้เพื่อระบุข้อความค้นหาที่ช้าและไม่มีประสิทธิภาพ เพื่อให้คุณรู้ว่าควรมุ่งเน้นความพยายามในการปรับปรุงที่ใด:

1 – คำสั่งEXPLAIN EXTENDED

หนึ่งในเครื่องมือที่สามารถใช้ในการวิเคราะห์แบบสอบถาม SQL ของคุณคือ คำสั่งอธิบาย

โดยการเรียกใช้คำสั่งอธิบายในแบบสอบถาม คุณสามารถดูวิธีการดำเนินการแบบสอบถาม รวมถึงดัชนีที่ใช้ และจำนวนแถวที่มีการตรวจสอบ

EXPLAIN SELECT * FROM orders

JOIN customers ON orders.customer_id = customers.customer_id

WHERE customers.name = 'John Smith';

เมื่อคุณเรียกใช้ คำสั่ง EXPLAINในแบบสอบถาม จะส่งกลับชุดผลลัพธ์ที่มีหลายคอลัมน์ รวมถึง:

• id: ตัวระบุเฉพาะของแบบสอบถามในแผนการดำเนินการ
• select_type: ประเภทของข้อความค้นหา เช่น SIMPLE หรือ SUBQUERY
• ตาราง: ตารางที่กำลังสอบถาม
• ประเภท: ประเภทการรวมที่ใช้ เช่น JOIN หรือ INDEX
• possible_keys: ดัชนีที่ MariaDB หรือ MySQL สามารถใช้ในการประมวลผลแบบสอบถาม
• คีย์: ดัชนีที่ MariaDB หรือ MySQL ใช้จริงในการประมวลผลแบบสอบถาม
• key_len: ความยาวของคีย์ที่ใช้
• แถว: จำนวนแถวที่ MariaDB หรือ MySQL ประมาณการจะได้รับการตรวจสอบสำหรับแบบสอบถาม

พิเศษ: ข้อมูลนี้มีข้อมูลเพิ่มเติมเกี่ยวกับคิวรี เช่น มีการสแกนตารางแบบเต็มหรือไม่ หรือใช้ตารางชั่วคราวหรือไม่

โดยการวิเคราะห์เอาต์พุตของ คำสั่งEXPLAINโดยทั่วไป คุณจะสามารถระบุคอขวดของประสิทธิภาพที่อาจเกิดขึ้นได้ เช่น การจัดทำดัชนีที่ไม่ดี ประเภทการรวมที่ไม่เหมาะสม หรือแถวที่ตรวจสอบจำนวนมาก

ตัวอย่างเช่น หากคอลัมน์ประเภทแสดง "ทั้งหมด" แทนที่จะเป็น "ดัชนี" แสดงว่าข้อความค้นหากำลังทำการสแกนตารางแบบเต็ม ซึ่งจะส่งผลให้ประสิทธิภาพการทำงานช้าลงอย่างแน่นอน หากคอลัมน์หลักเป็น NULL แสดงว่า MySQL ไม่ได้ใช้ดัชนีใดๆ ซึ่งจะทำงานช้าเช่นกัน หากคอลัมน์แถวมีค่าสูง แสดงว่ามีการตรวจสอบหลายแถว ส่งผลให้ประสิทธิภาพลดลงไปอีก

เราชอบใช้ รูปแบบ อธิบายเพิ่มเติม เพื่อช่วยให้ข้อมูลเพิ่มเติม

หมายเหตุ: แม้ว่าสิ่งนี้จะเลิกใช้แล้วใน MySQL แต่ยังคงมีอยู่ใน MariaDB

เมื่อใช้ตัวเลือก EXTENDED คุณจะสามารถดูข้อมูลที่เป็นประโยชน์ เช่น จำนวนแถวที่ตรวจสอบ จำนวนแถวที่ส่งคืน ข้อมูลเกี่ยวกับประเภทของ JOIN ที่ใช้ ลำดับของตารางที่สแกน ดัชนีที่ใช้ และระยะเวลา แบบสอบถามได้รับการดำเนินการ

นี่คือลักษณะของการใช้คำสั่ง EXPLAIN EXTENDED:

EXPLAIN EXTENDED SELECT * FROM your_table WHERE column_name = 'value';

ในตัวอย่างนี้ คำสั่ง EXPLAIN จะแสดงรายการขั้นตอนที่ฐานข้อมูลจะต้องดำเนินการเพื่อดำเนินการค้นหา ตลอดจนรายการทรัพยากรที่จะใช้

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

ตัวอย่างเช่น การใช้คำสั่ง EXPLAIN EXTENDED สามารถช่วยระบุความจำเป็นในการเพิ่มดัชนี ปรับเงื่อนไข JOIN ให้เหมาะสม และจำกัดจำนวนแถวทั้งหมดที่แบบสอบถามส่งคืน

นอกจากนี้ คุณควรตรวจสอบให้แน่ใจว่าคุณได้ปิดใช้งานการแคชข้อความค้นหาเมื่อดำเนินการทดสอบและเพิ่มประสิทธิภาพนี้ เพื่อให้แน่ใจว่าคุณได้รับผลลัพธ์ที่ถูกต้อง ในการทำเช่นนั้น ให้รันคำสั่งนี้ก่อนเมื่อคุณเชื่อมต่อไคลเอนต์ของคุณ

SET SESSION query_cache_type=0;

เมื่อคุณทำการเปลี่ยนแปลงเหล่านี้กับข้อความค้นหาของคุณแล้ว ให้ทดสอบประสิทธิภาพอีกครั้งเพื่อระบุว่ามีการปรับปรุงสำเร็จมากน้อยเพียงใด (ถ้ามี) โปรดจำไว้ว่าเช่นเดียวกับการทำโปรไฟล์และการเพิ่มประสิทธิภาพของแบบสอบถาม กระบวนการจะวนซ้ำ – คาดว่าจะใช้คำสั่ง EXPLAIN EXTENDED ตามด้วยการทดสอบประสิทธิภาพหลายครั้ง

2 – คำสั่งอธิบายวิเคราะห์

คำสั่งนี้ใช้เพื่อวิเคราะห์แผนการดำเนินการของคิวรีและส่งคืนเมตริกประสิทธิภาพ เช่น เวลาจริงที่คิวรีใช้ในการดำเนินการ และจำนวนแถวที่ตรวจสอบจริง ด้วยการวิเคราะห์ผลลัพธ์ของคำสั่ง EXPLAIN ANALYZE คุณสามารถระบุปัญหาคอขวดที่อาจเกิดขึ้นในการดำเนินการของแบบสอบถาม เช่น การขาดดัชนีหรือแถวจำนวนมากที่ต้องตรวจสอบ

3 – บันทึกการสืบค้นช้า

นี่เป็นคุณสมบัติในตัวของ MariaDB (และ MySQL) ที่บันทึกการสืบค้นทั้งหมดที่ใช้เวลานานกว่าระยะเวลาหนึ่งในการดำเนินการ บันทึกการสืบค้นที่ช้าสามารถกำหนดค่าให้บันทึกการสืบค้นที่ใช้เวลานานกว่าเกณฑ์ที่กำหนด เช่น หนึ่งวินาที

ที่ Servebolt บันทึกการสืบค้นที่ช้าจะบันทึกการสืบค้นทั้งหมดที่ใช้เวลานานกว่า 1 วินาทีในการดำเนินการ เนื่องจากข้อความค้นหาส่วนใหญ่ควรดำเนินการในเสี้ยววินาที ในบริบทของเว็บแอปพลิเคชัน เช่น ไซต์ที่ใช้ WordPress การโหลดหน้าเดียวต้องใช้การสืบค้นฐานข้อมูลระหว่าง 10 ถึง 100 รายการ ซึ่งทั้งหมดนี้ต้องดำเนินการตามลำดับก่อนจึงจะสามารถคอมไพล์หน้าเป็น HTML และส่งกลับไปยังผู้ใช้ได้

การกำหนดค่าปัจจุบันของ Servebolt Cloud เก็บบันทึกการสืบค้นที่ช้าบนเซิร์ฟเวอร์บันทึกส่วนกลาง หากมีความจำเป็น คุณสามารถติดต่อกับทีมสนับสนุนของเรา และเราจะกรองไฟล์สำหรับบันทึกที่เกี่ยวข้องและให้ผลลัพธ์แก่คุณ

ในสภาพแวดล้อมของคุณเอง คุณสามารถเปิดใช้งานบันทึกการสืบค้นที่ช้าได้โดยเพิ่มบรรทัดต่อไปนี้ในไฟล์กำหนดค่า MariaDB หรือ MySQL (my.cnf หรือ my.ini):

log_slow_queries = /path/to/slow.log

long_query_time = 1

4 – แผนภาพอธิบาย

แผนการอธิบายด้วยภาพ แสดงการแสดงกราฟิก ของเอาต์พุตคำสั่ง EXPLAIN ทำให้เข้าใจการดำเนินการของคิวรีได้ง่ายขึ้นและตรวจพบปัญหาด้านประสิทธิภาพ

หมายเหตุ: Visual Explain Plans มีประโยชน์เมื่อคุณอยู่ในขั้นตอนของการพัฒนาเว็บแอปพลิเคชัน

แทนที่จะเป็นเอาต์พุตข้อความธรรมดา จะแสดงการดำเนินการคิวรีใน โครงสร้างแบบต้นไม้ โดยแต่ละโหนดจะแทนตาราง ดัชนี หรือการดำเนินการ และการเชื่อมต่อระหว่างโหนดเหล่านี้แสดงถึงลำดับของการดำเนินการ

เครื่องมือต่างๆ เช่น MySQL Workbench และ EXPLAIN Analyzerสามารถสร้างแผนการอธิบายด้วยภาพ และนำเสนออินเทอร์เฟซแบบโต้ตอบสำหรับการนำทางแผนการดำเนินการและตรวจสอบการดำเนินการแต่ละอย่างโดยละเอียด

ตัวอย่างเช่น ใน MySQL Workbench การสร้างแผนอธิบายด้วยภาพทำได้ง่ายเพียงแค่ดำเนินการค้นหาและคลิกปุ่ม "อธิบายแผน " บนแท็บผลลัพธ์สิ่งนี้นำเสนอการแสดงแบบกราฟิกของแผนดำเนินการคิวรี พร้อมด้วยข้อมูลโดยละเอียดเกี่ยวกับการดำเนินการแต่ละรายการ การดำเนินการนี้ทำให้คุณสามารถระบุปัญหาด้านประสิทธิภาพ จากนั้นปรับการสืบค้นให้เหมาะสมตามต้องการ

5 – ตัวปรับแต่ง MySQL

MySQL Tuner เป็นสคริปต์ที่ตรวจสอบประสิทธิภาพและการกำหนดค่าของเซิร์ฟเวอร์ฐานข้อมูล และให้คำแนะนำสำหรับการปรับปรุง โดยจะให้ข้อมูลสรุปของสถานะเซิร์ฟเวอร์ปัจจุบัน รวมถึงข้อมูล เช่น จำนวนการสืบค้นทั้งหมด จำนวนการสืบค้นที่ช้า และการใช้พูลบัฟเฟอร์ปัจจุบัน

นอกจากนี้ยังสามารถใช้เพื่อตรวจสอบการตั้งค่าอื่นๆ เช่น เวอร์ชันของฐานข้อมูล เอ็นจิ้นการจัดเก็บที่ใช้งานอยู่ และการกำหนดค่าแคชของแบบสอบถาม และให้คำแนะนำสำหรับการปรับการตั้งค่าเหล่านี้ให้เหมาะสมตามปริมาณงานปัจจุบัน

หนึ่งในข้อแตกต่างที่สำคัญกับเครื่องมืออื่น ๆ คือเครื่องมือบรรทัดคำสั่งที่สามารถเรียกใช้ได้ทั้งบนเซิร์ฟเวอร์เองหรือจากระยะไกล ทำให้กระบวนการตรวจสอบและเพิ่มประสิทธิภาพฐานข้อมูลเป็นไปโดยอัตโนมัติได้ง่าย

หมายเหตุ: หากเว็บแอปพลิเคชันของคุณ (และฐานข้อมูล) โฮสต์อยู่ใน Servebolt Cloud แล้ว – นี่คือสิ่งที่ทีมของเราเชี่ยวชาญและสามารถทำได้ดีกว่าคำแนะนำใดๆ ที่เครื่องมือจะสามารถให้ได้

6 – แบบสอบถามตัวสร้างโปรไฟล์

มีผู้สร้างโปรไฟล์แบบสอบถามของบริษัทอื่นที่สามารถใช้เพื่อจัดทำโปรไฟล์ แบบสอบถาม SQL เช่น MariaDB Enterprise Query Analyzer , Dataedo และ Percona Toolkit ผู้สร้างโปรไฟล์แบบสอบถามบุคคลที่สามสามารถให้คุณสมบัติและการทำงานเพิ่มเติมเมื่อเปรียบเทียบกับเครื่องมือในตัวที่มีอยู่ใน MariaDB (หรือ MySQL)

หมายเหตุ: Query Profilers มีประโยชน์เมื่อคุณอยู่ในขั้นตอนการพัฒนาเว็บแอปพลิเคชัน

ตัวอย่างเช่น พวกเขาอาจให้ข้อมูลรายละเอียดเพิ่มเติมเกี่ยวกับประสิทธิภาพการค้นหา เช่น เวลาดำเนินการและล็อคเวลารอ และสามารถให้การแสดงภาพข้อมูลในรูปแบบที่ไม่สามารถทำได้ด้วยเครื่องมือในตัว

หากเครื่องมือในตัวเพียงพอสำหรับความต้องการของคุณ ก็ไม่จำเป็นต้องใช้ตัวสร้างโปรไฟล์การค้นหาของบุคคลที่สาม อย่างไรก็ตาม หากคุณต้องการข้อมูลที่ละเอียดมากขึ้นหรือคุณลักษณะขั้นสูง คุณอาจพิจารณาใช้ผู้สร้างโปรไฟล์จากภายนอก

7 – การทำโปรไฟล์ด้วยเครื่องมือตรวจสอบ

นอกจากนี้ยังมีเครื่องมือตรวจสอบจำนวนมาก เช่น Prometheus, Grafana และ Nagios ที่สามารถใช้ในการค้นหาโปรไฟล์และตรวจสอบประสิทธิภาพของฐานข้อมูลของคุณ

Prometheus เป็นระบบตรวจสอบที่มีประสิทธิภาพที่สามารถรวบรวม จัดเก็บ และสืบค้นข้อมูลเมตริก ช่วยให้คุณได้รับข้อมูลเชิงลึกอันมีค่าแบบเรียลไทม์มันผสานรวมกับ MariaDB (และ MySQL) เพื่อจัดเก็บตัวชี้วัดที่รวบรวมและมาพร้อมกับ Grafana เพื่อการแสดงภาพที่มีประสิทธิภาพ

Grafana เป็นเครื่องมือวิเคราะห์โอเพ่นซอร์สอันทรงพลังที่สามารถใช้ตรวจสอบและแสดงข้อมูลที่รวบรวมจาก Prometheusการตั้งค่าแดชบอร์ดและการแจ้งเตือนที่กำหนดเองช่วยให้คุณติดตามประสิทธิภาพของฐานข้อมูลได้แบบเรียลไทม์

Nagios ช่วยให้คุณติดตามสถานะของฐานข้อมูลของคุณได้ตลอดเวลาสามารถตั้งค่าให้ตรวจสอบทรัพยากรหลัก เช่น CPU, RAM และพื้นที่ดิสก์ ในขณะเดียวกันก็ติดตามบริการอื่น ๆ และอุปกรณ์เครือข่าย เนื่องจากกำหนดค่าได้สูง จึงเป็นเครื่องมือที่ยอดเยี่ยมสำหรับการตรวจสอบการสืบค้นฐานข้อมูลเชิงรุก

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

เทคนิคการเพิ่มประสิทธิภาพการค้นหาทั่วไป

มีเทคนิคการเพิ่มประสิทธิภาพการสืบค้นทั่วไปหลายประการที่สามารถใช้เพื่อปรับปรุงประสิทธิภาพของการสืบค้น SQL:

1 – การจัดทำดัชนี

ดัชนีเป็นวิธีเพิ่มความเร็วในการสืบค้นข้อมูล โดยเฉพาะที่ใช้ตัวกรอง(WHERE)การใช้ดัชนีส่งผลให้โครงสร้างข้อมูลในเครื่องมือฐานข้อมูลของคุณ (MariaDB หรือ MySQL) อยู่นอกตารางที่ระบุ และชี้ไปที่ข้อมูลที่คุณพยายามค้นหา เราจะไม่ลงรายละเอียดมากเกินไปในโพสต์นี้ เนื่องจากการใช้ดัชนีเพื่อปรับปรุงการสืบค้นฐานข้อมูลจะต้องมีบทความของตัวเอง ซึ่งเราวางแผนจะกล่าวถึงในอนาคต

ตัวอย่างเช่น พิจารณาตารางขนาดใหญ่ที่เรียกว่า "คำสั่งซื้อ" ที่มีข้อมูลหลายล้านแถว รวมถึงข้อมูลต่างๆ เช่น รหัสคำสั่งซื้อ รหัสลูกค้า และวันที่สั่งซื้อ หากมีการดำเนินการสืบค้นเพื่อดึงคำสั่งซื้อทั้งหมดที่ทำโดยลูกค้าเฉพาะรายโดยไม่มีดัชนีในคอลัมน์ ID ลูกค้า MariaDB จะต้องสแกนทั้งตารางเพื่อค้นหาข้อมูลที่เกี่ยวข้อง การดำเนินการนี้อาจใช้เวลาและทรัพยากรมาก โดยเฉพาะอย่างยิ่งสำหรับตารางขนาดใหญ่

กล่าวอย่างกว้างๆ เมื่อใดก็ตามที่คุณมั่นใจว่าคุณจะเรียกใช้การค้นหาเฉพาะเจาะจงซ้ำๆ และอ่านเรื่องประสิทธิภาพ การสร้างดัชนี (หรือมากกว่าหนึ่งดัชนี) อาจเป็นแนวทางที่เหมาะสมในการเพิ่มความเร็วการค้นหานั้น

ในบริบทของ WordPress สิ่งนี้เป็นเรื่องปกติมาก ปลั๊กอินจำนวนมากสร้างขึ้นโดยนักพัฒนาที่ (ไม่สะดวก) ใช้ตารางทั่วไปที่ใช้ร่วมกันโดยไม่ต้องใช้ดัชนี เป็นผลให้เป็นพื้นที่ที่มักมีการเพิ่มประสิทธิภาพที่สำคัญมาก

หากต้องการดูดัชนีใด ๆ ที่มีอยู่ในตารางใดตารางหนึ่ง

คุณสามารถดูดัชนีใดๆ ที่มีอยู่ในตารางเฉพาะได้โดยใช้ SHOW INDEX FROM เช่นในตัวอย่างด้านล่างสำหรับ ตาราง wp_postmeta:

MariaDB [db_name] > SHOW INDEX FROM wp_postmeta;

ในสถานการณ์ หนึ่ง เราเพิ่งสร้างสองดัชนีสำหรับตาราง wp_postmeta:sb_postid_metakey และ sb_postid_metakey_metaval

ดัชนีเหล่านี้ถูกเพิ่มโดยพิจารณาจากการสืบค้นที่ช้าที่สุด และพบว่าทั้งหมดมีความคล้ายคลึงกันโดยลักษณะของคำสั่ง SELECT ที่กรองโดยใช้ WHERE นอกเหนือจากเงื่อนไขการเปรียบเทียบจำนวนมาก (AND/OR) เมื่อเห็นสิ่งนี้ ฉันตรวจสอบดัชนีปัจจุบันสำหรับตารางที่ใช้และเรียกใช้ EXPLAIN EXTENDED ในแบบสอบถามเพื่อตรวจสอบแนวทางของฉันเพิ่มเติม

แบบสอบถามส่วนใหญ่ใช้งานได้และใช้ ตาราง wp_postmeta โดยใช้ JOINตามลำดับที่เกิดขึ้น การเพิ่มดัชนีเหล่านี้จะช่วยให้ MariaDB (หรือ MySQL) ได้รับการตอบสนองจากดัชนีแทนที่จะสแกนทั้งตารางที่มีแถวทั้งหมด

CREATE INDEX sb_postid_metakey ON wp_postmeta (post_id, meta_key);

CREATE INDEX sb_postid_metakey_metaval ON wp_postmeta (post_id, meta_key, meta_value);

นี่คือการรวมกันของ "การค้นหาสิ่งต่างๆ" โดยใช้เครื่องมือที่คุณมี (ตามที่ระบุไว้ด้านบน) รวมถึงความรู้เกี่ยวกับประเภทข้อมูลและเนื้อหาของฐานข้อมูล สิ่งนี้ไม่ได้ผลเสมอไป แม้ว่าจะเป็นเช่นนั้น ก็ไม่ได้ส่งผลให้ประสิทธิภาพดีขึ้น 500% เสมอไป การมีดัชนีขนาดใหญ่อาจจบลงได้ช้ากว่าการสแกนแถวทั้งหมด ดังนั้น แบบสอบถามจึงต้องได้รับการทดสอบก่อนและหลังการใช้ดัชนีเพื่อให้แน่ใจ

หมายเหตุ: เมื่อพยายามทดสอบความเร็วดัชนี คุณจะต้องปิดใช้งานการแคชแบบสอบถามสำหรับเซสชัน โดยใช้:

SET SESSION query_cache_type=0;

ในกรณีนี้ ก่อนที่จะใช้ดัชนี การสืบค้นใช้เวลา 10.437 วินาทีในการดำเนินการ และหลังจากสร้างดัชนีทั้งสองแล้ว การสืบค้นเดียวกันก็ใช้เวลา [# วินาที]

2 – ลดการเข้าถึงข้อมูล

ลดการเข้าถึงข้อมูล เช่น ลดจำนวนแถวและคอลัมน์ที่จะเข้าถึงเพื่อดำเนินการค้นหาซึ่งสามารถทำได้โดยการกรองข้อมูลที่ดึงมาจากคิวรี ใช้ดัชนี และแบ่งพาร์ติชันตารางขนาดใหญ่ แม้ว่าจะไม่ใช่สิ่งที่คนส่วนใหญ่ต้องการ (หรือสามารถทำได้) แต่ก็เป็นประเด็นสำคัญที่ต้องคำนึงถึงเมื่อออกแบบการสืบค้นฐานข้อมูลตั้งแต่เริ่มต้น

ตัวอย่างเช่น หากการสืบค้นฐานข้อมูลกำลังค้นหาข้อมูลเกี่ยวกับผู้ใช้เพื่อจุดประสงค์ในการเข้าสู่ระบบ การสืบค้นควรเป็น LIMIT 1 เนื่องจากไม่ควรมีข้อมูลของผู้ใช้มากกว่าหนึ่งคนอย่างชัดเจน

หมายเหตุ: สิ่งนี้เกี่ยวข้องกับการออกแบบฐานข้อมูลมากกว่าการปรับให้เหมาะสมแม้ว่าการรักษาประสิทธิภาพจะมีความสำคัญ แต่ความพยายามนี้มีความเกี่ยวข้องกับนักพัฒนาปลั๊กอิน (ในบริบทของ WordPress) มากกว่าสำหรับผู้ใช้ปลายทางส่วนใหญ่

โปรดจำไว้ว่าก่อนที่จะทดสอบความเร็วหลังจากทำการเปลี่ยนแปลงใดๆ ในการเข้าถึงข้อมูล คุณควรตรวจสอบให้แน่ใจว่าคุณได้ปิดใช้งานการแคชแบบสอบถามด้วยการเรียกใช้คำสั่งต่อไปนี้:

SET SESSION query_cache_type=0;

3 – การใช้การแบ่งพาร์ติชันข้อมูล

การแบ่งพาร์ติชันข้อมูลออกเป็นชิ้นเล็กๆ จะทำให้ฐานข้อมูลมีประสิทธิภาพมากขึ้นและใช้เวลาจัดการน้อยลง กลยุทธ์นี้สามารถช่วยลดระยะเวลาที่ใช้ในกระบวนการบำรุงรักษา เช่น การสำรองข้อมูลและการอัปเดต รวมทั้งจำกัดจำนวนข้อมูลที่จำเป็นต้องจัดการ โดยรวมแล้วจะช่วยปรับปรุงประสิทธิภาพและเพิ่มประสิทธิภาพการใช้ทรัพยากร

เมื่อต้องการแบ่งพาร์ติชันข้อมูลในฐานข้อมูล คุณสามารถทำตามขั้นตอนเหล่านี้:

1. เมื่อเลือกตารางที่จะแบ่งพาร์ติชัน ตรวจสอบให้แน่ใจว่าได้เลือกตารางที่เก็บข้อมูลจำนวนมากและจะได้รับประโยชน์จากการแบ่งพาร์ติชัน สิ่งนี้จะช่วยเพิ่มประสิทธิภาพระบบของคุณและปรับปรุงประสิทธิภาพการค้นหา
2. การเลือกวิธีการแบ่งพาร์ติชันที่ถูกต้องสำหรับฐานข้อมูลของคุณเป็นสิ่งสำคัญ คุณสามารถเลือกจากช่วง รายการ แฮช หรือการแบ่งคีย์ โดยขึ้นอยู่กับโครงสร้างของข้อมูลและการสืบค้นที่คุณวางแผนจะดำเนินการ ตรวจสอบให้แน่ใจว่าคุณเลือกสิ่งที่เหมาะสมกับความต้องการของคุณมากที่สุดเพื่อประสิทธิภาพและผลลัพธ์ที่ดีที่สุด
   
   1. การแบ่งพาร์ติชันช่วง เป็นตัวเลือกที่เหมาะสมที่สุดเมื่อคุณมีข้อมูลที่สามารถแบ่งออกเป็นบางช่วงได้ตัวอย่างเช่น หากคุณมีตารางที่มีข้อมูลหลายปี คุณสามารถสร้างพาร์ติชันช่วงเพื่อจัดระเบียบได้ดีขึ้น อาจขึ้นอยู่กับวันที่หรือค่าตัวเลขของคอลัมน์ที่เป็นปัญหา
   2. การแบ่งพาร์ติชันรายการ เป็นเทคนิคที่มีประสิทธิภาพในการจัดการข้อมูลที่สามารถแยกออกเป็นกลุ่มต่างๆ ตามพารามิเตอร์เฉพาะได้อย่างง่ายดายตัวอย่างเช่น คุณมีตารางที่มีข้อมูลพนักงานที่จัดหมวดหมู่ตามแผนก สิ่งนี้จำเป็นต้องใช้การแบ่งพาร์ติชันรายการ
   3. การแบ่งพาร์ติชันแฮช เป็นกลยุทธ์ที่มีประสิทธิภาพสำหรับการจัดเรียงข้อมูลเป็นกลุ่มขนาดเท่าๆ กัน โดยยึดตามค่าแฮชของคอลัมน์เฉพาะซึ่งช่วยให้สามารถกระจายข้อมูลได้ทั่วถึงในหลายพาร์ติชัน ทำให้เป็นตัวเลือกที่ยอดเยี่ยมสำหรับการกระจายข้อมูลอย่างมีประสิทธิภาพ
   4. การแบ่งคีย์ นั้นคล้ายกับการแบ่งพาร์ติชันแฮช แต่ข้อแตกต่างที่สำคัญคือการใช้ค่าคอลัมน์เฉพาะเป็นพื้นฐานในการแบ่งข้อมูลออกเป็นกลุ่มต่างๆทำให้เป็นตัวเลือกที่เหมาะสำหรับชุดข้อมูลที่สามารถแบ่งออกเป็นกลุ่มต่างๆ ตามตัวระบุเฉพาะหรือคีย์ธรรมชาติ
3. ด้วยการสร้างตารางที่แบ่งพาร์ติชัน คุณสามารถแบ่งตารางต้นฉบับออกเป็นตารางที่เล็กลงได้อย่างมีประสิทธิภาพ ซึ่งทำได้โดยการเพิ่มส่วนคำสั่งการแบ่งพาร์ติชันในคำสั่ง CREATE TABLE ซึ่งคุณระบุวิธีการและเงื่อนไขที่ต้องการสำหรับการแบ่งส่วน การทำเช่นนี้สามารถช่วยปรับปรุงประสิทธิภาพการค้นหา และยังทำให้การจัดการข้อมูลมีประสิทธิภาพมากขึ้นด้วย
4. คุณสามารถคัดลอกข้อมูลจากตารางเดิมไปยังตารางที่เพิ่งแบ่งพาร์ติชันได้อย่างรวดเร็วโดยใช้คำสั่ง INSERT INTO… SELECT สิ่งนี้จะเติมตารางพาร์ติชันของคุณด้วยข้อมูลที่เกี่ยวข้องทั้งหมดได้อย่างง่ายดาย
5. แอปพลิเคชันต้องได้รับการกำหนดค่าใหม่เพื่อใช้ประโยชน์จากตารางที่แบ่งพาร์ติชัน สิ่งนี้จะแทนที่ตารางเดิมและทำให้แอปพลิเคชันของคุณมีประสิทธิภาพมากขึ้น
6. ก่อนเรียกใช้การทดสอบใดๆ เพื่อประเมินการปรับปรุงประสิทธิภาพที่เป็นไปได้ จำเป็นต้องปิดใช้งานการแคชแบบสอบถามก่อนโดยเรียกใช้คำสั่ง: SET SESSION query_cache_type=0;
7. เพื่อให้แน่ใจว่าตารางที่แบ่งพาร์ติชันของคุณทำงานได้อย่างราบรื่น สิ่งสำคัญคือต้องติดตามดูประสิทธิภาพอย่างใกล้ชิด หากคุณสังเกตเห็นปัญหาใดๆ การปรับเงื่อนไขการแบ่งพาร์ติชันหรือเปลี่ยนไปใช้วิธีอื่นอาจช่วยได้ การตรวจสอบพาร์ติชันของคุณเป็นประจำจะช่วยให้คุณเพิ่มศักยภาพได้สูงสุด

หมายเหตุสำคัญเกี่ยวกับการอัปเกรดสคริปต์และการแบ่งตาราง

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

ตัวอย่างเช่น ถ้าสคริปต์ถูกสร้างขึ้นเพื่อเพิ่มคอลัมน์ใหม่ลงในตารางที่แบ่งพาร์ติชัน สคริปต์นั้นอาจแก้ไขเพียงพาร์ติชันเดียว สร้างความไม่สอดคล้องกันและปัญหาภายในข้อมูล ในทำนองเดียวกัน ถ้าสคริปต์การอัปเกรดถูกสร้างขึ้นเพื่อเพิ่มดัชนีลงในตารางที่แบ่งพาร์ติชัน สคริปต์จะสามารถสร้างดัชนีบนพาร์ติชันเดียวเท่านั้น ส่งผลให้ประสิทธิภาพการทำงานช้าลงและผลลัพธ์ไม่สอดคล้องกัน

เพื่อหลีกเลี่ยงปัญหาดังกล่าว ต้องออกแบบสคริปต์การอัพเกรดให้พิจารณาตารางที่แบ่งพาร์ติชัน ซึ่งอาจเกี่ยวข้องกับการเรียกใช้สคริปต์ในแต่ละพาร์ติชันแยกกัน หรือแก้ไขสคริปต์เพื่อทำงานกับตารางที่แบ่งพาร์ติชัน สิ่งสำคัญคือต้องทำการทดสอบอย่างละเอียดเพื่อให้แน่ใจว่ากระบวนการอัปเกรดไม่ก่อให้เกิดปัญหาที่ไม่คาดคิดหรือการสูญหายของข้อมูล

4 – เรดดิส

สำหรับลูกค้าของ Servebolt Redis เป็นส่วนเสริม (แบบชำระเงิน) ที่สามารถช่วยในการเพิ่มประสิทธิภาพการค้นหา

Redis (บางครั้งเรียกว่าเซิร์ฟเวอร์พจนานุกรมระยะไกล) เป็นโซลูชันโอเพ่นซอร์สที่เก็บข้อมูลในหน่วยความจำและสามารถใช้สำหรับการแคช ฐานข้อมูล หรือแม้แต่เป็นตัวกลางส่งข้อความ สามารถรวมเข้ากับฐานข้อมูลเพื่อปรับปรุงประสิทธิภาพ โดยทำหน้าที่เป็นตัวกลางที่มีประสิทธิภาพระหว่างแอปพลิเคชันและฐานข้อมูล

ทำงานเพื่อปรับปรุงประสิทธิภาพและเวลาตอบสนองของแอปพลิเคชันโดยการลดภาระในฐานข้อมูล สิ่งนี้ทำได้โดยการจัดเก็บข้อมูลที่ใช้บ่อยใน Redis แทนฐานข้อมูลสำหรับทุกคำขอ ซึ่งช่วยประหยัดเวลาได้มาก

ด้วยการกำหนดค่าปลั๊กอินอย่างถูกต้อง Redis สามารถใช้กับฐานข้อมูลเพื่อเพิ่มประสิทธิภาพการดำเนินการค้นหา เมื่อไม่มีข้อมูลที่จำเป็นใน Redis แอปพลิเคชันจะดึงข้อมูลจากฐานข้อมูลและจัดเก็บไว้ใน Redis เพื่อใช้ในอนาคต ทำให้การดึงข้อมูลเร็วขึ้นและมีประสิทธิภาพมากขึ้น

เมื่อใช้วิธีการนี้ แอปพลิเคชันจะได้รับประโยชน์จากการเข้าถึง Redis ในหน่วยความจำอย่างรวดเร็ว และยังจัดเก็บและเข้าถึงข้อมูลจากฐานข้อมูลได้ตามต้องการ

โปรดจำไว้ว่าหากคุณใช้งาน Redis เป็นครั้งแรก คุณจะต้องปิดใช้งานการแคชแบบสอบถามก่อนที่จะเรียกใช้การทดสอบประสิทธิภาพใดๆ ในการทำเช่นนี้ ใช้คำสั่ง:

SET SESSION query_cache_type=0;

บทสรุป

ระบบนิเวศของ MariaDB และ MySQL มีเครื่องมือและวิธีการมากมายที่ช่วยให้ค้นพบคอขวดในการดำเนินการสืบค้นฐานข้อมูลได้ง่ายขึ้น ช่วยให้คุณปรับปรุงประสิทธิภาพของเว็บแอปพลิเคชันของคุณได้

การชะลอตัวมีแนวโน้มที่จะเกิดขึ้นตลอดอายุการใช้งานของแอปพลิเคชันใดๆ การพยายามหลีกเลี่ยงสิ่งเหล่านี้เป็นสิ่งที่ดี แต่ท้ายที่สุดแล้ว คุณจำเป็นต้องรู้ว่าจะต้องดูที่ใดเมื่อคุณเริ่มวินิจฉัยปัญหาด้านประสิทธิภาพ ขึ้นอยู่กับขนาดและลักษณะของฐานข้อมูลที่คุณเรียกใช้ นี่เป็นกระบวนการซ้ำๆ ที่ต้องมีการตรวจสอบ แก้ไขปัญหา และปรับปรุงอย่างต่อเนื่องเพื่อให้ฐานข้อมูลของคุณทำงานด้วยมาตรฐานระดับสูง