Database query optimizers สัญญาว่าจะเลือกแผนการดำเนินงานที่เร็วที่สุดสำหรับ query ของคุณโดยอัตโนมัติ แต่การทดสอบใหม่เผยให้เห็นว่าระบบเหล่านี้มักจะทำงานได้ไม่ดีเท่าที่ควร แม้ว่าระบบเหล่านี้จะทำงานได้ดีในสถานการณ์ง่ายๆ แต่มักจะมีปัญหากับรูปแบบข้อมูลในโลกแห่งความเป็นจริง ส่งผลให้เกิดปัญหาด้านประสิทธิภาพที่อาจทำให้นักพัฒนาต้องประหลาดใจ
ความเป็นจริงเบื้องหลัง Query Optimization
การทดสอบประสิทธิภาพล่าสุดในการกระจายข้อมูลแบบต่างๆ แสดงให้เห็นว่า query optimizers เลือกแผนการทำงานที่ไม่เหมาะสมอย่างสม่ำเสมอ การทดสอบนี้เกี่ยวข้องกับ SELECT queries แบบง่ายที่มีเงื่อนไข range แต่แม้แต่การดำเนินงานพื้นฐานเหล่านี้ก็เผยให้เห็นช่องว่างที่สำคัญระหว่างสิ่งที่ optimizers เลือกกับสิ่งที่ทำงานได้เร็วที่สุดจริงๆ ปัญหานี้จะรุนแรงมากขึ้นเมื่อมี queries ที่ซับซ้อนซึ่งเกี่ยวข้องกับหลายตารางและ joins
ปัญหาหลักอยู่ที่วิธีการตัดสินใจของ optimizers พวกมันอาศัยสรุปทางสถิติของข้อมูลและโมเดลต้นทุนที่เรียบง่ายเพื่อประเมินประสิทธิภาพ อย่างไรก็ตาม สถิติเหล่านี้เป็นเพียงการแสดงที่ไม่สมบูรณ์ของรูปแบบข้อมูลจริง ฐานข้อมูลในโลกแห่งความเป็นจริงมีความสัมพันธ์ การจัดกลุ่ม และรูปแบบการกระจายที่ไม่สามารถจับได้อย่างเต็มที่ในสรุปทางสถิติที่กะทัดรัด
รูปแบบประสิทธิภาพของแผนการค้นหา:
- ข้อมูลแบบสม่ำเสมอ: เลือกใช้ index scans สำหรับการคัดเลือกที่ 1-5% แต่ bitmap scans ให้ประสิทธิภาพที่ดีกว่าจริงๆ
- ข้อมูลแบบวัฏจักร: ตัววางแผนยังคงใช้ index scans แม้ในการคัดเลือกที่มีความเฉพาะเจาะจงต่ำ ซึ่ง bitmap จะเหมาะสมกว่า
- ข้อมูลแบบเชิงเส้น: เฉพาะชุดข้อมูลที่เป็นเชิงเส้นอย่างสมบูรณ์เท่านั้นที่สามารถเลือกแผนที่เหมาะสมอย่างสม่ำเสมอ
- ผลกระทบของ cache ที่อุ่น: ความแตกต่างด้านประสิทธิภาพระหว่างวิธีการสแกนจะหายไปส่วนใหญ่เมื่อมีการแคชข้อมูล
 |
|---|
| การแสดงภาพความถี่ที่ตัวปรับแต่งคิวรีเลือกแผนการดำเนินงานที่เหมาะสมที่สุด เน้นช่องว่างด้านประสิทธิภาพในการดำเนินคิวรี |
ความไม่พอใจของชุมชนเกี่ยวกับความคาดเดาได้ของแผน
ผู้เชี่ยวชาญด้านฐานข้อมูลเริ่มออกมาวิจารณ์เรื่องความไม่สามารถคาดเดาได้ของ optimizer มากขึ้น บางคนโต้แย้งว่า query planners สมัยใหม่ฉลาดเกินไปจนเป็นปัญหาต่อตัวเอง สร้างสถานการณ์ที่การเปลี่ยนแปลงเล็กน้อยในข้อมูลหรือโครงสร้าง query สามารถเปลี่ยนแปลงประสิทธิภาพอย่างมาก ความไม่สามารถคาดเดาได้นี้กลายเป็นปัญหาโดยเฉพาะในสภาพแวดล้อม production ที่ประสิทธิภาพที่สม่ำเสมอสำคัญกว่าการปรับปรุงเชิงทฤษฎี
ชุมชน PostgreSQL เผชิญกับความท้าทายเป็นพิเศษเนื่องจากฐานข้อมูลไม่อนุญาตให้ผู้ใช้บังคับแผนการดำเนินงานหรือ indexes เฉพาะ แม้ว่าปรัชญาการออกแบบนี้จะมุ่งหวังที่จะปฏิบัติต่อแผนที่ไม่เหมาะสมเป็น bugs ที่ต้องแก้ไข แต่ก็ทำให้นักพัฒนามีตัวเลือกจำกัดเมื่อ optimizer ตัดสินใจผิด Extensions อย่าง pg_hint_plan ให้ความช่วยเหลือบ้าง แต่ไม่ได้เป็นส่วนหนึ่งของระบบหลัก
มันกำลังซับซ้อนเกินไป คาดเดาไม่ได้เกินไป ทุก query และตัวแปรกลายเป็นเรื่องน่าประหลาดใจใน production
ข้อจำกัดของการควบคุมแผน PostgreSQL:
- ไม่มีความสามารถดั้งเดิมในการบังคับใช้ดัชนีเฉพาะหรือแผนการดำเนินการ
- แฟล็กระดับเซสชันให้การควบคุมแบบหยาบแต่อาจส่งผลกระทบต่อคิวรีอื่นๆ
- CTE แบบ Materialized ช่วยสร้างอุปสรรคการเพิ่มประสิทธิภาพแต่อาจป้องกันการเพิ่มประสิทธิภาพที่ต้องการ
- ส่วนขยาย pg_hint_plan จากบุคคลที่สามให้ความสามารถในการบังคับใช้แผน
- RDS Aurora PostgreSQL มีรายงานว่าสนับสนุนแผนคิวรีแบบจัดการ
ความซับซ้อนของฮาร์ดแวร์และสภาพแวดล้อม
สภาพแวดล้อมคอมพิวเตอร์สมัยใหม่เพิ่มความซับซ้อนอีกชั้นหนึ่งให้กับ query optimization โมเดลต้นทุนที่พัฒนาสำหรับระบบ on-premise แบบดั้งเดิมอาจไม่สะท้อนลักษณะประสิทธิภาพของระบบ cloud storage, NVMe arrays หรือสภาพแวดล้อม containerized ได้อย่างแม่นยำ คุณสมบัติอย่าง prefetching ซึ่งสามารถปรับปรุงประสิทธิภาพได้อย่างมากสำหรับรูปแบบการเข้าถึงบางอย่าง ไม่ได้ถูกนำมาพิจารณาอย่างเหมาะสมในการคำนวณต้นทุนของ optimizer เสมอไป
การทดสอบแสดงให้เห็นว่า bitmap scans มีประสิทธิภาพดีกว่า index scans อย่างสม่ำเสมอเนื่องจากความสามารถ prefetching แต่ optimizers มักเลือกวิธี index scan ที่ช้ากว่า การปรับปรุงในระดับฮาร์ดแวร์เหล่านี้สามารถเปลี่ยนแปลงภูมิทัศน์ประสิทธิภาพได้อย่างสมบูรณ์ในแบบที่โมเดลต้นทุนแบบดั้งเดิมไม่สามารถจับได้
ข้อมูลจำเพาะของสภาพแวดล้อมการทดสอบ:
- ฮาร์ดแวร์: โปรเซสเซอร์ AMD Ryzen 9300X พร้อมระบบจัดเก็บข้อมูล NVMe RAID
- สภาวะแคช: การทดสอบแบบ cold cache (ไม่มีข้อมูลใน page cache หรือ shared buffers)
- ประเภทของ query: คำสั่ง SELECT แบบง่ายที่มีเงื่อนไข WHERE แบบ single range
- การกระจายข้อมูลที่ทดสอบ: แบบสม่ำเสมอ, แบบวัฏจักรที่มี fuzz, แบบเชิงเส้นที่มีระดับ fuzz ที่แตกต่างกัน
แนวทางทางเลือกและวิธีแก้ปัญหา
นักพัฒนาบางคนสนับสนุนแนวทาง query ที่สามารถคาดเดาได้มากขึ้น โดยเฉพาะสำหรับ queries ที่สร้างโดยแอปพลิเคชันที่ความสามารถในการคาดเดาได้สำคัญกว่าการปรับปรุงเชิงทฤษฎี เทคนิคอย่างการใช้ Common Table Expressions (CTEs) เป็นอุปสรรคการปรับปรุง การปรับ session-level planner settings หรือการปรับโครงสร้าง queries สามารถให้การควบคุมแผนการดำเนินงานได้มากขึ้น
ระบบฐานข้อมูลระดับสูงเสนอ adaptive query processing ซึ่งอนุญาตให้ engine ปรับวิธีการของมันระหว่างการดำเนินงานตามข้อมูลประสิทธิภาพจริง อย่างไรก็ตาม คุณสมบัติเหล่านี้ไม่มีให้ใช้ทั่วไปและเพิ่มความซับซ้อนของตัวเองในการจัดการฐานข้อมูล
ความท้าทายพื้นฐานยังคงอยู่: query optimization เป็นปัญหาการบีบอัดที่สูญเสียข้อมูลโดยพื้นฐาน Optimizers ต้องตัดสินใจอย่างรวดเร็วตามข้อมูลที่จำกัด และแผนที่สมบูรณ์แบบสำหรับทุกสถานการณ์ที่เป็นไปได้อาจเป็นไปไม่ได้ทางคณิตศาสตร์ที่จะบรรลุภายในข้อจำกัดของเวลาและทรัพยากรที่สมเหตุสมผล
อ้างอิง: How often is the query plan optimal?