ในฐานะผู้ดูแลระบบฐานข้อมูล เราจำเป็นต้องหมกมุ่นอยู่กับเครื่องมือและวิธีการในการเพิ่มประสิทธิภาพฐานข้อมูล
ใน PostgreSQL เราสามารถเข้าถึงคำสั่ง EXPLAIN ANALYZE ที่ช่วยให้เราสามารถวิเคราะห์แผนการดำเนินการและประสิทธิภาพของการสืบค้นฐานข้อมูลที่กำหนดได้ คำสั่งส่งคืนข้อมูลโดยละเอียดเกี่ยวกับวิธีที่เอ็นจินฐานข้อมูลประมวลผลเคียวรี ซึ่งรวมถึงลำดับของการดำเนินการที่ดำเนินการ ค่าใช้จ่ายในการค้นหาโดยประมาณ ระยะเวลาดำเนินการ และอื่นๆ
จากนั้นเราสามารถใช้ข้อมูลนี้เพื่อระบุการสืบค้นฐานข้อมูล ตลอดจนระบุและแก้ไขปัญหาคอขวดของประสิทธิภาพที่อาจเกิดขึ้น
บทช่วยสอนนี้กล่าวถึงวิธีใช้คำสั่ง EXPLAIN ANALYZE ใน PostgreSQL เพื่อดูและเพิ่มประสิทธิภาพการสืบค้น
PostgreSQL อธิบายวิเคราะห์
คำสั่งค่อนข้างตรงไปตรงมา ขั้นแรก เราต้องใส่คำสั่ง EXPLAIN ANALYZE ไว้ข้างหน้าคำค้นหาที่เราต้องการวิเคราะห์
ไวยากรณ์คำสั่งมีดังนี้:
อธิบายวิเคราะห์เมื่อคุณรันคำสั่ง PostgreSQL จะส่งคืนเอาต์พุตโดยละเอียดเกี่ยวกับแบบสอบถามที่ให้มา
ทำความเข้าใจกับ EXPLAIN ANALYZE Query Output
ตามที่กล่าวไว้ เมื่อเราเรียกใช้คำสั่ง EXPLAIN ANALYZE แล้ว PostgreSQL จะสร้างรายงานโดยละเอียดเกี่ยวกับแผนการสืบค้นข้อมูลและสถิติการดำเนินการ
ผลลัพธ์ประกอบด้วยชุดของคอลัมน์ที่มีข้อมูลที่เป็นประโยชน์ คอลัมน์ผลลัพธ์จะแสดงตามความหมายที่เกี่ยวข้อง:
แผนแบบสอบถาม – คอลัมน์นี้แสดงแผนการดำเนินการของแบบสอบถามที่ระบุ แผนการดำเนินการอ้างอิงถึงลำดับของการดำเนินการที่กลไกจัดการฐานข้อมูลดำเนินการเพื่อให้คิวรีเสร็จสมบูรณ์
วางแผน – คอลัมน์ที่สองคือคอลัมน์แผน ประกอบด้วยการแสดงข้อความของแต่ละการดำเนินการหรือขั้นตอนในแผนการดำเนินการ อีกครั้ง การดำเนินการแต่ละรายการจะถูกเยื้องเพื่อระบุลำดับชั้นของการดำเนินการ
ค่าใช้จ่ายทั้งหมด – คอลัมน์ต้นทุนรวมแสดงต้นทุนรวมโดยประมาณของแบบสอบถาม ต้นทุนอ้างอิงการวัดสัมพัทธ์ที่ตัววางแผนการสืบค้นฐานข้อมูลใช้เพื่อกำหนดแผนการดำเนินการที่เหมาะสมที่สุด
แถวจริง – คอลัมน์นี้แสดงจำนวนแถวที่แน่นอนซึ่งได้รับการประมวลผลในแต่ละขั้นตอนในการดำเนินการค้นหา
เวลาจริง – คอลัมน์นี้แสดงเวลาจริงที่ใช้โดยการดำเนินการแต่ละรายการ ซึ่งรวมถึงเวลาดำเนินการของการดำเนินการและเวลาที่ใช้ทรัพยากร
เวลาวางแผน – คอลัมน์นี้แสดงเวลาที่ตัววางแผนแบบสอบถามใช้ในการสร้างแผนการดำเนินการ ซึ่งรวมถึงเวลารวมของการเพิ่มประสิทธิภาพการค้นหาและการสร้างแผน
เวลาดำเนินการ – คอลัมน์นี้แสดงเวลาทั้งหมดในการดำเนินการค้นหา ซึ่งรวมถึงเวลาที่ใช้ในการวางแผนและเวลาในการดำเนินการค้นหา
PostgreSQL อธิบายตัวอย่างการวิเคราะห์
ให้เราดูตัวอย่างพื้นฐานของการใช้คำสั่ง EXPLAIN ANALYZE
ตัวอย่างที่ 1: เลือกคำสั่ง
ให้เราใช้คำสั่ง EXPLAIN ANALYZE เพื่อแสดงการทำงานของคำสั่ง Select อย่างง่ายใน PostgreSQL
เมื่อเราเรียกใช้คำสั่งก่อนหน้า เราควรได้ผลลัพธ์ดังนี้:
แผนแบบสอบถาม-------------------------------------------------- -----------------
Seq Scan on wp_users (ต้นทุน=0.00..10.38 แถว=10 ความกว้าง=2256) (เวลาจริง=0.009..0.010 แถว=7 ลูป=1)
ตัวกรอง: (รหัส > 3)
แถวที่ถูกลบโดยตัวกรอง: 3
เวลาวางแผน: 0.995 ms
เวลาดำเนินการ: 0.021 ms
(5 แถว)
ในกรณีนี้ เราจะเห็นว่าส่วนแผนแบบสอบถามระบุว่าแบบสอบถามทำการสแกนตามลำดับในตาราง wp_users เส้นตัวกรองหมายถึงเงื่อนไขที่ใช้ในการกรองแถวผลลัพธ์
จากนั้นเราจะเห็น “แถวที่ถูกลบโดยตัวกรอง” ซึ่งแสดงจำนวนแถวที่ถูกกำจัดโดยเงื่อนไขตัวกรอง
สุดท้าย เวลาดำเนินการจะแสดงเวลาดำเนินการทั้งหมดของคิวรี ในกรณีนี้ การสืบค้นจะใช้เวลา 0.021 มิลลิวินาที
ตัวอย่างที่ 2: การวิเคราะห์การเข้าร่วม
ให้เราใช้แบบสอบถามที่ซับซ้อนมากขึ้นที่เกี่ยวข้องกับการรวม SQL สำหรับสิ่งนี้ เราใช้ฐานข้อมูลตัวอย่าง Pagila คุณสามารถดาวน์โหลดและติดตั้งฐานข้อมูลตัวอย่างในเครื่องของคุณเพื่อการสาธิต
เราสามารถเรียกใช้การเข้าร่วมอย่างง่ายได้ดังที่แสดงต่อไปนี้:
อธิบายวิเคราะห์ SELECT f.title, c.nameจากภาพยนตร์เรื่อง ฉ
เข้าร่วม film_category fc บน f.film_id = fc.film_id
เข้าร่วมหมวดหมู่ c ON fc.category_id = c.category_id;
เมื่อเราเรียกใช้แบบสอบถามที่กำหนด เราควรเห็นผลลัพธ์ดังนี้:
ให้เราสำรวจแผนการสืบค้นต่อไปนี้:
- Nested Loop – สิ่งนี้บ่งชี้ว่าการรวมใช้กลยุทธ์การรวมลูปที่ซ้อนกัน
- Hash Join – การดำเนินการนี้รวม film_category และตารางภาพยนตร์โดยใช้อัลกอริทึม Hash join การดำเนินการนี้มีค่าใช้จ่าย 77.50 และประมาณ 1,000 แถว อย่างไรก็ตาม เวลาจริงที่ใช้สำหรับการดำเนินการนี้คือ 0.254 ถึง 0.439 มิลลิวินาที และดึงข้อมูล 1,000 แถว
- Hash Cond – สิ่งนี้บ่งชี้ว่าเงื่อนไขการรวมใช้การรวมแฮชเพื่อจับคู่คอลัมน์ film_id และคอลัมน์ film_category ในตารางภาพยนตร์
- Seq Scan on film_category – การดำเนินการนี้ทำการสแกนตามลำดับในตาราง film_category ด้วยต้นทุน 16.00 และประมาณ 1,000 แถว เวลาจริงที่ใช้สำหรับการดำเนินการนี้คือ 0.008 ถึง 0.056 มิลลิวินาที และดึงข้อมูล 1,000 แถว
- Seq Scan on film – คิวรี่ทำการสแกนตามลำดับบนตารางฟิล์มด้วยค่าใช้จ่ายและแถวโดยประมาณและที่เกิดขึ้นจริงในการดำเนินการนี้
- Memoize – การดำเนินการนี้แคชผลลัพธ์ของการรวมระหว่าง film_category และตารางฟิล์มเพื่อใช้ในภายหลัง
- คีย์แคช – บ่งชี้ว่าคีย์แคชที่ใช้สำหรับการท่องจำนั้นอิงจากคอลัมน์ category_id จาก film_category
- โหมดแคช – สิ่งนี้บ่งชี้ว่าแบบสอบถามใช้โหมดแคชแบบลอจิคัล
- Hit, Misses, Evictions, Overflows – สามบรรทัดแสดงสถิติเกี่ยวกับแคช จำนวน Hit, Misses, Evictions และ Overflows ระหว่างการดำเนินการ บล็อกนี้ยังรวมถึงการใช้หน่วยความจำระหว่างการดำเนินการค้นหา
- การสแกนดัชนีโดยใช้ category_pkey – แสดงการทำงานที่ทำการสแกนดัชนีในตารางหมวดหมู่โดยใช้ดัชนีคีย์หลัก
- Index Cond – แสดงว่าการสแกนดัชนีเป็นไปตามเงื่อนไขที่ตรงกับคอลัมน์ category_id ในตารางหมวดหมู่
- เวลาวางแผน – บรรทัดนี้แสดงเวลาที่ใช้ในการวางแผนคิวรีซึ่งเท่ากับ 3.005 มิลลิวินาที
- เวลาดำเนินการ – สุดท้าย บรรทัดนี้แสดงเวลาดำเนินการทั้งหมดของแบบสอบถามซึ่งเท่ากับ 0.745 มิลลิวินาที
คุณมีมัน! ข้อมูลโดยละเอียดเกี่ยวกับการดำเนินการเข้าร่วมอย่างง่ายใน PostgreSQL
บทสรุป
คุณค้นพบพลังและการใช้งานคำสั่ง EXPLAIN ANALYZE ใน PostgreSQL คำสั่ง EXPLAIN ANALYZE เป็นเครื่องมือที่มีประสิทธิภาพสำหรับการวิเคราะห์แบบสอบถามและการเพิ่มประสิทธิภาพ ใช้เครื่องมือนี้เพื่อสร้างแบบสอบถามที่มีประสิทธิภาพและใช้ทรัพยากรน้อยลง