การอภิปรายล่าสุดเกี่ยวกับการปรับปรุงประสิทธิภาพ SQL query ได้จุดประกายการถกเถียงในหมู่นักพัฒนาฐานข้อมูลเรื่องต้นทุนประสิทธิภาพของ OR clauses และวิธีแก้ปัญหาที่เป็นไปได้ การสนทนามุ่งเน้นไปที่ตัวอย่างจริงที่แสดงให้เห็นว่า OR queries สามารถช้ากว่าทางเลือกที่ใช้ AND อย่างมีนัยสำคัญ ซึ่งนำไปสู่การอภิปรายในวงกว้างเกี่ยวกับรูปแบบการออกแบบ schema และกลยุทธ์การปรับปรุงประสิทธิภาพ query
ปัญหาประสิทธิภาพหลัก
ตัวอย่างเดิมแสดงให้เห็นความแตกต่างของประสิทธิภาพที่โดดเด่นใน PostgreSQL query ที่ใช้ OR เพื่อค้นหาแอปพลิเคชันที่ผู้ใช้เป็นทั้งผู้ส่งหรือผู้ตรวจสอบใช้เวลามากกว่า 100 มิลลิวินาทีกับข้อมูลหนึ่งล้านเรคอร์ด อย่างไรก็ตาม การเขียน logic เดียวกันใหม่โดยใช้ query แยกที่ใช้ AND ลดเวลาการทำงานลงเหลือน้อยกว่า 1 มิลลิวินาที ซึ่งเป็นการปรับปรุงประสิทธิภาพมากกว่า 100 เท่า
ความแตกต่างอย่างมากนี้เกิดขึ้นแม้ว่าจะมี index ที่เหมาะสมอยู่ในคอลัมน์ที่กรองแล้วก็ตาม ปัญหาเกิดจากวิธีที่ database query planner จัดการกับ OR operations ซึ่งมักต้องการการรวม index lookups แยกกันหรือการทำ full table scans ซึ่งทั้งสองวิธีมีต้นทุนการคำนวณสูงเมื่อเทียบกับการเข้าถึง index โดยตรง
การเปรียบเทียบประสิทธิภาพ:
- OR Query: เวลาในการประมวลผลมากกว่า 100ms
- AND Query Alternative: เวลาในการประมวลผลน้อยกว่า 1ms
- การปรับปรุงประสิทธิภาพ: เร็วขึ้นมากกว่า 100 เท่า
- สภาพแวดล้อมการทดสอบ: แอปพลิเคชัน 1,000,000 รายการ, ผู้ใช้ 1,000 คน, PostgreSQL
มุมมองของชุมชนเกี่ยวกับการปรับปรุงประสิทธิภาพ Query
ผู้เชี่ยวชาญฐานข้อมูลในการอภิปรายเน้นข้อพิจารณาสำคัญหลายประการ บางคนโต้แย้งว่าแม้การปรับปรุงประสิทธิภาพจะมีคุณค่า แต่ไม่ควรมาแลกกับความชัดเจนของโค้ดและความสามารถในการบำรุงรักษา OR query เดิมแสดงเจตนาของนักพัฒนาได้ดีกว่าและสื่อสารกับโปรแกรมเมอร์ในอนาคตที่ต้องเข้าใจโค้ดได้ชัดเจนกว่า
คนอื่นๆ ชี้ให้เห็นว่า query optimizer สมัยใหม่กำลังซับซ้อนมากขึ้น มีการพัฒนาอย่างต่อเนื่องใน PostgreSQL และระบบฐานข้อมูลอื่นๆ เพื่อปรับปรุง query ประเภทนี้โดยอัตโนมัติ ซึ่งอาจทำให้การเขียนใหม่ด้วยตนเองไม่จำเป็นในเวอร์ชันในอนาคต
รูปแบบ Extension Table
วิธีแก้ปัญหายอดนิยมที่กล่าวถึงเกี่ยวข้องกับการปรับโครงสร้าง database schemas โดยใช้สิ่งที่นักพัฒนาเรียกว่า extension pattern แทนที่จะมีหลาย foreign key columns ในตารางเดียวกัน วิธีนี้สร้างตาราง junction แยกที่สร้างความสัมพันธ์ได้อย่างมีประสิทธิภาพมากกว่า
สำหรับตัวอย่างแอปพลิเคชัน นี่หมายถึงการสร้างตาราง application_user ที่เชื่อมโยงผู้ใช้กับแอปพลิเคชันด้วย type indicator (submitter หรือ reviewer) การออกแบบนี้ช่วยให้ query สามารถติดตามเส้นทางเชิงเส้นผ่าน indexes แทนที่จะต้องการ merge operations ที่ซับซ้อน
ผมชอบ extension pattern มากจริงๆ อยากให้ตารางในบริษัทของผมใช้มันมากกว่านี้
ตัวอย่าง Schema ของ Extension Pattern:
-- โครงสร้างเดิมที่มีปัญหา
create table application (
application_id int8 not null,
submitter_id int8 not null,
reviewer_id int8 not null
);
-- วิธีแก้ไขด้วย extension pattern
create table application_user (
user_id int8 not null,
application_id int8 not null,
user_type enum ('submitter', 'reviewer') not null
);
ผลกระทบในวงกว้างต่อการออกแบบฐานข้อมูล
การอภิปรายเผยให้เห็นว่าการตัดสินใจออกแบบ schema มีผลกระทบกว้างไกลเกินกว่าประสิทธิภาพ query ธรรมดา นักพัฒนาสังเกตว่า extension pattern ยังช่วยลดความซับซ้อนในการรวมเข้ากับระบบค้นหาอย่าง Elasticsearch และลดความจำเป็นในกลยุทธ์ denormalization ที่ซับซ้อน
อย่างไรก็ตาม ผู้เชี่ยวชาญฐานข้อมูลที่มีประสบการณ์เตือนไม่ให้นำเทคนิคการปรับปรุงประสิทธิภาพเหล่านี้ไปใช้ทั่วไปมากเกินไป ประสิทธิผลของวิธีการต่างๆ ขึ้นอยู่กับระบบฐานข้อมูลเฉพาะ การกระจายข้อมูล และรูปแบบ query เป็นอย่างมาก สิ่งที่ทำงานได้ดีกับ PostgreSQL อาจไม่สามารถนำไปใช้กับ database engine อื่นได้ และวิธีแก้ปัญหาที่ช่วยในกรณีง่ายๆ อาจกลายเป็นเรื่องยุ่งยากกับ multi-table joins ที่ซับซ้อน
การสนทนายังสัมผัสถึงความท้าทายพื้นฐานของการปรับปรุงประสิทธิภาพ query: ระบบฐานข้อมูลต้องตัดสินใจการทำงานโดยไม่มีความรู้ที่สมบูรณ์เกี่ยวกับขนาดของ result set ทำให้ยากที่จะเลือกกลยุทธ์ที่เหมาะสมที่สุดโดยอัตโนมัติ
คำแนะนำเชิงปฏิบัติ
สำหรับนักพัฒนาที่เผชิญปัญหาประสิทธิภาพคล้ายกัน ชุมชนแนะนำหลายวิธี ประการแรก การเข้าใจ execution plans เป็นสิ่งสำคัญสำหรับการวินิจฉัยปัญหาประสิทธิภาพ ระบบฐานข้อมูลต่างๆ มีเครื่องมือในการแสดงภาพว่า queries ทำงานอย่างไร ช่วยระบุจุดคอขวด
ประการที่สอง การเลือกระหว่างเทคนิคการปรับปรุงประสิทธิภาพ OR และการปรับโครงสร้าง schema ควรพิจารณา use case เฉพาะ สำหรับแอปพลิเคชันที่ต้อง query ข้ามหลายประเภทความสัมพันธ์บ่อยๆ extension pattern ให้ประโยชน์ที่ชัดเจน สำหรับกรณีง่ายๆ หรือระบบที่การเปลี่ยนแปลง schema ทำได้ยาก การเขียน query ใหม่อาจเป็นทางปฏิบัติมากกว่า
การอภิปรายเน้นย้ำว่าการออกแบบฐานข้อมูลที่มีประสิทธิผลต้องการความเข้าใจในรูปแบบการเข้าถึง workload ของการอ่านเทียบกับการเขียน และปัญหาการแข่งขันที่อาจเกิดขึ้น ปัจจัยเหล่านี้มักสำคัญกว่าการปฏิบัติตามกฎการปรับปรุงประสิทธิภาพทั่วไป
อ้างอิง: A SQL Heuristic: ORs Are Expensive