ระบบฐานข้อมูลเป็นแกนหลักของระบบสารสนเทศเพื่อการจัดการ การประมวลผลธุรกรรมออนไลน์บนฐานข้อมูล (OLTP) และการประมวลผลการวิเคราะห์ออนไลน์ (OLAP) เป็นหนึ่งในแอปพลิเคชันคอมพิวเตอร์ที่สำคัญที่สุดในธนาคาร องค์กร รัฐบาล และแผนกอื่นๆ จากตัวอย่างการใช้งานและรวมกับทฤษฎีฐานข้อมูล บทความนี้จะแนะนำการประยุกต์ใช้เทคโนโลยีการปรับให้เหมาะสมแบบสอบถามในระบบจริง เมื่อพิจารณาจากตัวอย่างแอปพลิเคชันของระบบส่วนใหญ่ การดำเนินการสืบค้นถือเป็นสัดส่วนที่ใหญ่ที่สุดของการดำเนินการฐานข้อมูลต่างๆ และคำสั่ง SELECT ซึ่งใช้การดำเนินการสืบค้นนั้นเป็นคำสั่งที่แพงที่สุดในบรรดาคำสั่ง SQL ตัวอย่างเช่น หากปริมาณข้อมูลสะสมจนถึงระดับหนึ่ง เช่น ข้อมูลตารางฐานข้อมูลบัญชีธนาคารที่สะสมเป็นล้านหรือหลายสิบล้านบันทึก การสแกนตารางแบบเต็มมักจะใช้เวลาหลายสิบนาทีหรือหลายชั่วโมง หากคุณใช้กลยุทธ์การสืบค้นที่ดีกว่าการสแกนตารางแบบเต็ม คุณสามารถลดเวลาการสืบค้นลงเหลือเพียงไม่กี่นาที ซึ่งแสดงให้เห็นถึงความสำคัญของเทคโนโลยีการปรับให้เหมาะสมของการสืบค้น
ในระหว่างการดำเนินโครงการแอปพลิเคชัน ผู้เขียนพบว่าเมื่อพัฒนาแอปพลิเคชันฐานข้อมูลโดยใช้เครื่องมือพัฒนาฐานข้อมูลส่วนหน้า (เช่น PowerBuilder, Delphi เป็นต้น) โปรแกรมเมอร์จำนวนมากมุ่งเน้นไปที่ความสวยงามของอินเทอร์เฟซผู้ใช้เท่านั้นและไม่ต้องจ่ายเงิน การใส่ใจในประสิทธิภาพของคำสั่งแบบสอบถามส่งผลให้ระบบแอปพลิเคชันที่พัฒนาขึ้นไม่มีประสิทธิภาพและทำให้สิ้นเปลืองทรัพยากรอย่างร้ายแรง ดังนั้นวิธีการออกแบบคำสั่งแบบสอบถามที่มีประสิทธิภาพและสมเหตุสมผลจึงมีความสำคัญมาก จากตัวอย่างการใช้งานและรวมกับทฤษฎีฐานข้อมูล บทความนี้จะแนะนำการประยุกต์ใช้เทคโนโลยีการปรับให้เหมาะสมแบบสอบถามในระบบจริง
วิเคราะห์ปัญหา
โปรแกรมเมอร์หลายคนเชื่อว่าการเพิ่มประสิทธิภาพแบบสอบถามเป็นงานของ DBMS (ระบบการจัดการฐานข้อมูล) และแทบไม่เกี่ยวข้องกับคำสั่ง SQL ที่เขียนโดยโปรแกรมเมอร์ นี่เป็นสิ่งที่ผิด แผนการสืบค้นที่ดีมักจะสามารถปรับปรุงประสิทธิภาพของโปรแกรมได้หลายสิบครั้ง แผนการสืบค้นคือชุดของคำสั่ง SQL ที่ผู้ใช้ส่งเข้ามา และแผนการสืบค้นคือชุดของคำสั่งที่สร้างขึ้นหลังการปรับให้เหมาะสม กระบวนการของแผนการสืบค้นการประมวลผล DBMS มีดังนี้: หลังจากเสร็จสิ้นการตรวจสอบคำศัพท์และไวยากรณ์ของคำสั่งการสืบค้นแล้ว คำสั่งจะถูกส่งไปยังเครื่องมือเพิ่มประสิทธิภาพการสืบค้น DBMS หลังจากที่เครื่องมือเพิ่มประสิทธิภาพเสร็จสิ้นการเพิ่มประสิทธิภาพพีชคณิตและการเพิ่มประสิทธิภาพเส้นทางการเข้าถึงแล้ว โมดูลที่คอมไพล์แล้วจะประมวลผล คำสั่งและสร้างแผนแบบสอบถาม จากนั้นส่งไปยังระบบเพื่อประมวลผลและดำเนินการในเวลาที่เหมาะสม และสุดท้ายก็ส่งคืนผลการดำเนินการให้กับผู้ใช้ ในผลิตภัณฑ์ฐานข้อมูลจริงเวอร์ชันสูง (เช่น Oracle, Sybase เป็นต้น) จะใช้วิธีการปรับให้เหมาะสมตามต้นทุน การปรับให้เหมาะสมนี้สามารถประมาณต้นทุนของแผนการสืบค้นที่แตกต่างกันตามข้อมูลที่ได้รับจากตารางพจนานุกรมของระบบ จากนั้นเลือก การวางแผนที่ดีขึ้น แม้ว่าผลิตภัณฑ์ฐานข้อมูลในปัจจุบันจะดีขึ้นเรื่อยๆ ในการปรับให้เหมาะสมของแบบสอบถาม แต่คำสั่ง SQL ที่ผู้ใช้ส่งมานั้นเป็นพื้นฐานสำหรับการเพิ่มประสิทธิภาพของระบบ เป็นเรื่องยากที่จะจินตนาการว่าแผนการสืบค้นที่ไม่ดีแต่เดิมจะมีประสิทธิภาพหลังจากการเพิ่มประสิทธิภาพของระบบ ดังนั้น คุณภาพของ ข้อความที่ผู้ใช้เขียนเป็นสิ่งสำคัญ เราจะไม่หารือเกี่ยวกับการปรับให้เหมาะสมแบบสอบถามที่ดำเนินการโดยระบบในตอนนี้ ต่อไปนี้จะเน้นที่โซลูชันเพื่อปรับปรุงแผนการสืบค้นของผู้ใช้
แก้ปัญหา
ข้อมูลต่อไปนี้ใช้ระบบฐานข้อมูลเชิงสัมพันธ์ Informix เป็นตัวอย่างในการแนะนำวิธีการปรับปรุงแผนการสืบค้นของผู้ใช้
1. การใช้ดัชนีอย่างสมเหตุสมผล
Index เป็นโครงสร้างข้อมูลที่สำคัญในฐานข้อมูล และวัตถุประสงค์พื้นฐานของดัชนีคือเพื่อปรับปรุงประสิทธิภาพการสืบค้น ผลิตภัณฑ์ฐานข้อมูลส่วนใหญ่ใช้โครงสร้างดัชนี ISAM ที่เสนอครั้งแรกโดย IBM การใช้ดัชนีจะต้องมีความเหมาะสมและมีหลักการใช้ดังนี้
●สร้างดัชนีบนคอลัมน์ที่เชื่อมต่อบ่อยครั้งแต่ไม่ได้ถูกกำหนดให้เป็นคีย์นอก ในขณะที่เครื่องมือเพิ่มประสิทธิภาพจะสร้างดัชนีสำหรับฟิลด์ที่เชื่อมต่อไม่บ่อยโดยอัตโนมัติ
● สร้างดัชนีบนคอลัมน์ที่มีการจัดเรียงหรือจัดกลุ่มบ่อยครั้ง (นั่นคือ จัดกลุ่มตามหรือเรียงลำดับตามการดำเนินการ)
●สร้างการค้นหาในคอลัมน์ที่มีค่าต่างๆ มากมายซึ่งมักใช้ในนิพจน์แบบมีเงื่อนไข อย่าสร้างดัชนีในคอลัมน์ที่มีค่าต่างกันเพียงเล็กน้อย ตัวอย่างเช่น มีเพียงสองค่าที่แตกต่างกันในคอลัมน์ "เพศ" ของตารางพนักงาน คือ "ชาย" และ "หญิง" ดังนั้นจึงไม่จำเป็นต้องสร้างดัชนี หากคุณสร้างดัชนี ไม่เพียงแต่จะไม่ปรับปรุงประสิทธิภาพการสืบค้นเท่านั้น แต่ยังจะลดความเร็วในการอัปเดตลงอย่างมากอีกด้วย
●หากมีหลายคอลัมน์ที่จะจัดเรียง คุณสามารถสร้างดัชนีผสมในคอลัมน์เหล่านี้ได้
●ใช้เครื่องมือของระบบ ตัวอย่างเช่น ฐานข้อมูล Informix มีเครื่องมือ tbcheck ที่สามารถตรวจสอบดัชนีที่น่าสงสัยได้ ในเซิร์ฟเวอร์ฐานข้อมูลบางแห่ง ดัชนีอาจไม่ถูกต้องหรือประสิทธิภาพการอ่านอาจลดลงเนื่องจากการดำเนินการบ่อยครั้ง หากการสืบค้นที่ใช้ดัชนีช้าลงโดยไม่มีเหตุผลที่ชัดเจน คุณสามารถลองใช้เครื่องมือ tbcheck เพื่อตรวจสอบความสมบูรณ์ของดัชนี และซ่อมแซมหากจำเป็น นอกจากนี้ เมื่อตารางฐานข้อมูลอัปเดตข้อมูลจำนวนมาก การลบและสร้างดัชนีใหม่จะช่วยเพิ่มความเร็วในการสืบค้นได้
2. หลีกเลี่ยงหรือทำให้การเรียงลำดับง่ายขึ้น
การเรียงลำดับตารางขนาดใหญ่ซ้ำๆ ควรทำให้ง่ายขึ้นหรือหลีกเลี่ยงการเรียงลำดับ เครื่องมือเพิ่มประสิทธิภาพจะหลีกเลี่ยงขั้นตอนการเรียงลำดับเมื่อสามารถใช้ดัชนีเพื่อสร้างเอาต์พุตตามลำดับที่เหมาะสมโดยอัตโนมัติ ต่อไปนี้เป็นปัจจัยที่มีอิทธิพลบางประการ:
●ดัชนีไม่รวมหนึ่งหรือหลายคอลัมน์ที่จะจัดเรียง
●ลำดับของคอลัมน์ในกลุ่มตามหรือเรียงลำดับตามข้อแตกต่างจากลำดับของดัชนี
●คอลัมน์ที่จัดเรียงมาจากตารางที่ต่างกัน
เพื่อหลีกเลี่ยงการเรียงลำดับที่ไม่จำเป็น จำเป็นต้องเพิ่มดัชนีอย่างถูกต้องและรวมตารางฐานข้อมูลอย่างสมเหตุสมผล (แม้ว่าบางครั้งอาจส่งผลต่อการทำให้ตารางเป็นมาตรฐาน แต่การปรับปรุงประสิทธิภาพก็คุ้มค่า) หากไม่สามารถหลีกเลี่ยงการเรียงลำดับได้ คุณควรพยายามทำให้การเรียงลำดับง่ายขึ้น เช่น ลดช่วงของคอลัมน์สำหรับการเรียงลำดับให้แคบลง เป็นต้น
3. ขจัดการเข้าถึงข้อมูลแถวตารางขนาดใหญ่ตามลำดับ
ในแบบสอบถามแบบซ้อน การเข้าถึงตารางตามลำดับอาจมีผลกระทบร้ายแรงต่อประสิทธิภาพของแบบสอบถาม ตัวอย่างเช่น การใช้กลยุทธ์การเข้าถึงตามลำดับ หากการสืบค้นที่มีระดับซ้อนกันสามระดับการสืบค้น 1,000 แถวในแต่ละระดับ การสืบค้นนี้จะสืบค้นข้อมูล 1 พันล้านแถว วิธีหลักในการหลีกเลี่ยงปัญหานี้คือการจัดทำดัชนีคอลัมน์ที่รวมเข้าด้วยกัน ตัวอย่างเช่น สองตาราง: ตารางนักเรียน (หมายเลขนักเรียน, ชื่อ, อายุ...) และตารางการเลือกหลักสูตร (หมายเลขนักเรียน, หมายเลขหลักสูตร, เกรด) หากจะเชื่อมต่อสองตารางเข้าด้วยกัน จะต้องสร้างดัชนีในช่องการเชื่อมต่อ "หมายเลขนักเรียน"
คุณยังสามารถใช้สหภาพเพื่อหลีกเลี่ยงการเข้าถึงตามลำดับได้ แม้ว่าจะมีดัชนีอยู่ในคอลัมน์ตรวจสอบทั้งหมด แต่รูปแบบบางรูปแบบของคำสั่งย่อยบังคับให้เครื่องมือเพิ่มประสิทธิภาพใช้การเข้าถึงตามลำดับ แบบสอบถามต่อไปนี้จะบังคับให้ดำเนินการตามลำดับในตารางคำสั่งซื้อ: SELECT * FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008
แม้ว่าจะมีดัชนีใน customer_num และ order_num แต่เครื่องมือเพิ่มประสิทธิภาพยังคงใช้เส้นทางการเข้าถึงตามลำดับเพื่อสแกนทั้งตารางในคำสั่งข้างต้น เนื่องจากคำสั่งนี้ดึงข้อมูลคอลเลกชันแถวที่แยกจากกัน จึงควรเปลี่ยนเป็นคำสั่งต่อไปนี้:
SELECT * จากคำสั่งซื้อ โดยที่ customer_num=104 และ order_num>1001
ยูเนี่ยน
SELECT * จากคำสั่งซื้อ โดยที่ order_num=1008
ซึ่งช่วยให้สามารถใช้เส้นทางดัชนีเพื่อประมวลผลแบบสอบถามได้
4. หลีกเลี่ยงแบบสอบถามย่อยที่สัมพันธ์กัน
ถ้าป้ายชื่อคอลัมน์ปรากฏในทั้งแบบสอบถามหลักและแบบสอบถามในส่วนคำสั่ง Where มีแนวโน้มว่าแบบสอบถามย่อยจะต้องได้รับการสอบถามอีกครั้งเมื่อค่าคอลัมน์ในแบบสอบถามหลักมีการเปลี่ยนแปลง ระดับการสืบค้นที่ซ้อนกันมากขึ้น ประสิทธิภาพจะลดลง ดังนั้นควรหลีกเลี่ยงแบบสอบถามย่อยให้มากที่สุด หากไม่สามารถหลีกเลี่ยงแบบสอบถามย่อยได้ ให้กรองแถวในแบบสอบถามย่อยให้ได้มากที่สุด
5. หลีกเลี่ยงนิพจน์ทั่วไปที่ยาก
คำหลัก MATCHES และ LIKE รองรับการจับคู่ไวด์การ์ด ซึ่งในทางเทคนิคเรียกว่านิพจน์ทั่วไป แต่การจับคู่ประเภทนี้ใช้เวลานานเป็นพิเศษ ตัวอย่างเช่น: SELECT * FROM ลูกค้า WHERE รหัสไปรษณีย์ LIKE “98_ _ _”
แม้ว่าดัชนีจะถูกสร้างขึ้นในช่องรหัสไปรษณีย์ แต่การสแกนตามลำดับยังคงใช้อยู่ในกรณีนี้ หากคุณเปลี่ยนคำสั่งเป็น SELECT * FROM customer WHERE zipcode > "98000" ดัชนีจะถูกนำมาใช้เพื่อสืบค้นเมื่อดำเนินการสืบค้น ซึ่งจะช่วยปรับปรุงความเร็วได้อย่างมากอย่างเห็นได้ชัด
นอกจากนี้ ให้หลีกเลี่ยงสตริงย่อยที่ไม่เริ่มต้น ตัวอย่างเช่น คำสั่ง: SELECT * FROM customer WHERE zipcode[2, 3]>"80" ใช้สตริงย่อยที่ไม่ได้เริ่มต้นในส่วนคำสั่ง Where ดังนั้นคำสั่งนี้จึงไม่ใช้ดัชนี
6. ใช้ตารางชั่วคราวเพื่อเพิ่มความเร็วในการสืบค้น
การเรียงลำดับชุดย่อยของตารางและการสร้างตารางชั่วคราวอาจทำให้การค้นหาเร็วขึ้นในบางครั้ง ช่วยหลีกเลี่ยงการเรียงลำดับหลายรายการและทำให้การทำงานของเครื่องมือเพิ่มประสิทธิภาพง่ายขึ้น ตัวอย่างเช่น: SELECT cust.name, rcVBles.balance,...คอลัมน์อื่นๆ
เลือก cust.name,rcVBles.balance,...คอลัมน์อื่นๆ
จาก cust, rcvbles
โดยที่ cust.customer_id = rcvlbes.customer_id
และ rcvblls.balance>0
และ cust.รหัสไปรษณีย์>"98000"
สั่งซื้อตาม cust.name
หากต้องดำเนินการค้นหานี้หลายครั้งแทนที่จะดำเนินการเพียงครั้งเดียว คุณสามารถค้นหาลูกค้าที่ยังไม่ได้ชำระเงินทั้งหมดในไฟล์ชั่วคราวและจัดเรียงตามชื่อลูกค้า: SELECT cust.name, rcvbles.balance,...คอลัมน์อื่นๆ
เลือก cust.name,rcvbles.balance,...คอลัมน์อื่นๆ
จาก cust, rcvbles
โดยที่ cust.customer_id = rcvlbes.customer_id
และ rcvblls.balance>0
สั่งซื้อตาม cust.name
เข้าสู่ TEMP cust_with_balance
จากนั้นสอบถามในตารางชั่วคราวด้วยวิธีต่อไปนี้: SELECT * FROM cust_with_balance
รหัสไปรษณีย์>"98000"
ในตารางชั่วคราวมีแถวน้อยกว่าในตารางหลัก และลำดับทางกายภาพเป็นลำดับที่จำเป็น ซึ่งจะลด I/O ของดิสก์ ดังนั้นเวิร์กโหลดการสืบค้นจึงสามารถลดลงได้อย่างมาก
หมายเหตุ: หลังจากสร้างตารางชั่วคราวแล้ว จะไม่สะท้อนถึงการแก้ไขตารางหลัก เมื่อมีการแก้ไขข้อมูลในตารางหลักบ่อยครั้ง โปรดระวังอย่าให้ข้อมูลสูญหาย
7. ใช้การเรียงลำดับเพื่อแทนที่การเข้าถึงแบบไม่ต่อเนื่อง
การเข้าถึงดิสก์แบบไม่ต่อเนื่องเป็นการดำเนินการที่ช้าที่สุด และแสดงโดยการเคลื่อนไปมาของแขนเข้าถึงดิสก์ คำสั่ง SQL ซ่อนสถานการณ์นี้ ทำให้เป็นเรื่องง่ายสำหรับเราในการเขียนคำสั่งที่ต้องเข้าถึงเพจที่ไม่เรียงลำดับจำนวนมากเมื่อเขียนแอปพลิเคชัน บางครั้ง การใช้ความสามารถในการเรียงลำดับของฐานข้อมูลแทนการเข้าถึงแบบไม่ต่อเนื่องสามารถปรับปรุงการสืบค้นได้
-