คู่มือ MySQL EXPLAIN: วิเคราะห์และเพิ่มประสิทธิภาพคิวรีฐานข้อมูลของคุณ

By 佐川 直弘 | Naohiro Sagawa (運営者)

※記事内に広告を含みます。Amazonアソシエイトとして適格販売により収入を得ています。　

1. ภาพรวมของ MySQL EXPLAIN

คำสั่ง EXPLAIN ของ MySQL เป็นเครื่องมือสำคัญสำหรับการวิเคราะห์แผนการดำเนินการของคิวรี และให้คำแนะนำในการเพิ่มประสิทธิภาพ โดยเฉพาะอย่างยิ่งในสภาพแวดล้อมฐานข้อมูลขนาดใหญ่ การเพิ่มประสิทธิภาพของคิวรีมีผลอย่างมากต่อประสิทธิภาพโดยรวม

EXPLAIN คืออะไร?

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

ความสำคัญของ EXPLAIN

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

2. วิธีการใช้งาน MySQL EXPLAIN เบื้องต้น

ในส่วนนี้ จะอธิบายวิธีการใช้งานคำสั่ง EXPLAIN เบื้องต้น และวิธีการตีความเนื้อหาในผลลัพธ์

วิธีการใช้งาน EXPLAIN เบื้องต้น

EXPLAIN ใช้โดยวางไว้หน้าคิวรีที่ต้องการตรวจสอบ ตัวอย่างเช่น:

EXPLAIN SELECT * FROM users WHERE age > 30;

คำสั่งนี้จะแสดงแผนการดำเนินการของคิวรี และสามารถตรวจสอบสถานะการใช้งานอินเด็กซ์หรือการสแกนตารางได้

การตีความเนื้อหาในผลลัพธ์ของ EXPLAIN

ผลลัพธ์ประกอบด้วยคอลัมน์ดังต่อไปนี้:

 
• id: ตัวระบุที่กำหนดให้กับแต่ละส่วนในคิวรี

 

• select_type: ประเภทของคิวรี (เช่น Simple, Subquery)

 

• table: ชื่อของตารางที่ใช้งาน

 

• type: วิธีการเข้าถึงตาราง (เช่น ALL, index, range)

 

• possible_keys: อินเด็กซ์ที่สามารถใช้ได้กับคิวรี

 

• key: อินเด็กซ์ที่ถูกใช้งานจริง

 

• rows: จำนวนแถวที่คาดว่าจะถูกสแกน

 

• Extra: ข้อมูลเพิ่มเติม (เช่น Using index, Using temporary)

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

3. การเพิ่มประสิทธิภาพคิวรีโดยใช้ EXPLAIN

จะอธิบายว่าสามารถเพิ่มประสิทธิภาพคิวรีได้อย่างไรโดยใช้ EXPLAIN

การใช้งานอินเด็กซ์อย่างเหมาะสม

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

EXPLAIN SELECT * FROM orders USE INDEX (order_date_idx) WHERE order_date > '2024-01-01';

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

ลดการสแกนแถวให้น้อยที่สุด

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

4. คุณสมบัติขั้นสูงของ EXPLAIN

EXPLAIN มีคุณสมบัติขั้นสูงสำหรับการวิเคราะห์แผนการดำเนินการของคิวรีในรายละเอียดมากขึ้น

การเลือกรูปแบบเอาต์พุต

EXPLAIN มีรูปแบบเอาต์พุตดังต่อไปนี้:

 
• Traditional: รูปแบบตารางดีฟอลต์

 

• JSON: รูปแบบ JSON ที่มีข้อมูลโดยละเอียด (ตั้งแต่ MySQL 5.7 ขึ้นไป)

 

• Tree: แสดงโครงสร้างการดำเนินการของคิวรีในรูปแบบต้นไม้ (ตั้งแต่ MySQL 8.0.16 ขึ้นไป)

ตัวอย่างเช่น การระบุเอาต์พุตในรูปแบบ JSON สามารถทำได้ดังนี้:

EXPLAIN FORMAT = JSON SELECT * FROM users WHERE age > 30;

สิ่งนี้ช่วยให้สามารถวิเคราะห์รายละเอียดของแผนการดำเนินการคิวรีได้อย่างลึกซึ้ง

การวิเคราะห์คิวรีแบบเรียลไทม์

การใช้ EXPLAIN FOR CONNECTION ช่วยให้สามารถรับแผนการดำเนินการของคิวรีที่กำลังทำงานอยู่ได้แบบเรียลไทม์ สิ่งนี้ช่วยให้สามารถประเมินภาระที่คิวรีเฉพาะกำลังสร้างต่อฐานข้อมูลได้แบบเรียลไทม์

5. ตัวอย่างการใช้งานจริง

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

การวิเคราะห์คิวรีแบบง่าย

ขั้นแรก ให้ใช้ EXPLAIN กับคิวรีอย่างง่าย

EXPLAIN SELECT * FROM employees WHERE department = 'Sales';

จากผลลัพธ์นี้ จะตรวจสอบว่ามีการใช้งานอินเด็กซ์อย่างเหมาะสมหรือไม่ หรือมีการสแกนทั้งตาราง

การเพิ่มประสิทธิภาพคิวรีที่ซับซ้อน

วิเคราะห์แผนการดำเนินการของคิวรีที่เชื่อมโยงหลายตาราง

EXPLAIN SELECT e.name, d.name FROM employees e INNER JOIN departments d ON e.department_id = d.id WHERE e.salary > 50000;

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

การแสดงผลแผนการดำเนินการแบบภาพ

แสดงผลแผนการดำเนินการของคิวรีในรูปแบบต้นไม้

EXPLAIN FORMAT = tree SELECT * FROM employees WHERE department = 'Sales';

การวิเคราะห์ด้วยภาพในรูปแบบต้นไม้มีประโยชน์อย่างมากสำหรับการเพิ่มประสิทธิภาพคิวรีที่ซับซ้อน

6. แนวทางปฏิบัติที่ดีที่สุดสำหรับ EXPLAIN

จะแนะนำแนวทางปฏิบัติที่ดีที่สุดสำหรับการใช้ EXPLAIN อย่างมีประสิทธิภาพ

การดำเนินการคิวรีซ้ำๆ

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

การใช้งานร่วมกับ SHOW STATUS

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

7. ปัญหาและความเข้าใจผิดที่พบบ่อย

จะอธิบายข้อควรระวังในการใช้ EXPLAIN และความเข้าใจผิดที่พบบ่อย

ความแตกต่างระหว่างค่าประมาณของ EXPLAIN กับผลลัพธ์จริง

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

การเชื่ออินเด็กซ์มากเกินไปและผลกระทบ

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

8. สรุป

ในบทความนี้ ได้อธิบายเกี่ยวกับการวิเคราะห์และเพิ่มประสิทธิภาพคิวรีโดยใช้คำสั่ง EXPLAIN ของ MySQL

สรุปประเด็นสำคัญ

 
• การใช้งานเบื้องต้น: ใช้ EXPLAIN เพื่อตรวจสอบแผนการดำเนินการของคิวรี และประเมินสถานะการใช้งานอินเด็กซ์และวิธีการเข้าถึงตาราง

 

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

 

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

ขั้นตอนต่อไปในการเพิ่มประสิทธิภาพคิวรี

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

สุดท้ายนี้

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