หมดยุคแล้วที่คุณไม่ต้องกังวลกับการเพิ่มประสิทธิภาพฐานข้อมูลอีกต่อไป เวลาไม่หยุดนิ่ง ผู้ประกอบการด้านเทคโนโลยีหน้าใหม่ทุกคนต้องการสร้าง Facebook รุ่นใหม่ ในขณะเดียวกันก็พยายามรวบรวมข้อมูลทั้งหมดที่พวกเขาสามารถทำได้ ธุรกิจต่างๆ ต้องการข้อมูลนี้เพื่อฝึกฝนโมเดลที่ช่วยให้พวกเขาสร้างรายได้ได้ดียิ่งขึ้น ในสภาวะเช่นนี้ โปรแกรมเมอร์จำเป็นต้องสร้าง API ที่ช่วยให้พวกเขาทำงานกับข้อมูลจำนวนมหาศาลได้อย่างรวดเร็วและเชื่อถือได้
หากคุณได้ออกแบบแอปพลิเคชันหรือแบ็กเอนด์ฐานข้อมูลมาเป็นระยะเวลาหนึ่งแล้ว คุณอาจเคยเขียนโค้ดเพื่อเรียกใช้คิวรีแบบแบ่งหน้า ตัวอย่างเช่นเช่นนี้:
SELECT * FROM table_name LIMIT 10 OFFSET 40
วิธีที่มันเป็น?
แต่ถ้าคุณทำการแบ่งหน้าแบบนี้ ฉันเสียใจที่ต้องบอกว่าคุณไม่ได้ทำอย่างมีประสิทธิภาพมากที่สุด
คุณต้องการที่จะคัดค้านฉัน? . , и พวกเขากำลังใช้เทคนิคที่ฉันอยากพูดถึงในวันนี้อยู่แล้ว
ระบุชื่อนักพัฒนาแบ็กเอนด์อย่างน้อยหนึ่งรายที่ไม่เคยใช้ OFFSET и LIMIT เพื่อดำเนินการค้นหาแบบแบ่งหน้า ใน MVP (Minimum Viable Product) และในโปรเจ็กต์ที่ใช้ข้อมูลจำนวนน้อย วิธีนี้ค่อนข้างใช้ได้ มัน "ใช้งานได้" เพื่อที่จะพูด
แต่ถ้าคุณต้องการสร้างระบบที่เชื่อถือได้และมีประสิทธิภาพตั้งแต่เริ่มต้น คุณควรดูแลล่วงหน้าเกี่ยวกับประสิทธิภาพของการสืบค้นฐานข้อมูลที่ใช้ในระบบดังกล่าว
วันนี้เราจะพูดถึงปัญหาในการใช้งานทั่วไป (แย่เกินไป) ของเอ็นจิ้นการสืบค้นแบบแบ่งหน้า และวิธีการบรรลุประสิทธิภาพสูงเมื่อดำเนินการค้นหาดังกล่าว
เกิดอะไรขึ้นกับ OFFSET และ LIMIT
ดังที่ได้กล่าวไปแล้วว่า OFFSET и LIMIT ทำงานได้ดีในโครงการที่ไม่จำเป็นต้องทำงานกับข้อมูลจำนวนมาก
ปัญหาเกิดขึ้นเมื่อฐานข้อมูลขยายจนมีขนาดไม่พอดีกับหน่วยความจำของเซิร์ฟเวอร์อีกต่อไป อย่างไรก็ตาม เมื่อทำงานกับฐานข้อมูลนี้ คุณจำเป็นต้องใช้แบบสอบถามแบบแบ่งหน้า
เพื่อให้ปัญหานี้แสดงออกมา จะต้องมีสถานการณ์ที่ DBMS หันไปใช้การดำเนินการสแกนตารางแบบเต็มที่ไม่มีประสิทธิภาพในการสืบค้นแบบแบ่งหน้าแต่ละรายการ (ในขณะที่การดำเนินการแทรกและการลบอาจเกิดขึ้น และเราไม่ต้องการข้อมูลที่ล้าสมัย!)
“การสแกนตารางแบบเต็ม” (หรือ “การสแกนตารางตามลำดับ”, การสแกนตามลำดับ) คืออะไร นี่คือการดำเนินการในระหว่างที่ DBMS อ่านแต่ละแถวของตารางตามลำดับ นั่นคือข้อมูลที่มีอยู่ในนั้น และตรวจสอบการปฏิบัติตามเงื่อนไขที่กำหนด การสแกนตารางประเภทนี้ถือว่าช้าที่สุด ความจริงก็คือเมื่อมีการดำเนินการ จะมีการดำเนินการอินพุต/เอาท์พุตจำนวนมากที่เกี่ยวข้องกับระบบย่อยของดิสก์ของเซิร์ฟเวอร์ สถานการณ์แย่ลงเนื่องจากเวลาแฝงที่เกี่ยวข้องกับการทำงานกับข้อมูลที่จัดเก็บไว้ในดิสก์ และความจริงที่ว่าการถ่ายโอนข้อมูลจากดิสก์ไปยังหน่วยความจำนั้นเป็นการดำเนินการที่ต้องใช้ทรัพยากรมาก
ตัวอย่างเช่น คุณมีบันทึกผู้ใช้ 100000000 ราย และคุณเรียกใช้แบบสอบถามด้วยโครงสร้าง OFFSET 50000000. ซึ่งหมายความว่า DBMS จะต้องโหลดบันทึกเหล่านี้ทั้งหมด (และเราไม่ต้องการมันด้วยซ้ำ!) เก็บไว้ในหน่วยความจำและหลังจากนั้นให้พูด 20 ผลลัพธ์ที่รายงานใน LIMIT.
สมมติว่าอาจมีลักษณะดังนี้: "เลือกแถวตั้งแต่ 50000 ถึง 50020 จาก 100000" นั่นคือระบบจะต้องโหลด 50000 แถวก่อนจึงจะทำการสืบค้นให้เสร็จสิ้น คุณเห็นไหมว่าเธอจะต้องทำงานที่ไม่จำเป็นมากแค่ไหน?
หากคุณไม่เชื่อฉัน ลองดูตัวอย่างที่ฉันสร้างขึ้นโดยใช้คุณสมบัติต่างๆ .
ตัวอย่างที่ db-fiddle.com
ด้านซ้ายมือในสนาม Schema SQLมีโค้ดที่แทรก 100000 แถวในฐานข้อมูล และทางด้านขวา ในช่อง Query SQLจะแสดงข้อความค้นหาสองรายการ อันแรกช้ามีลักษณะดังนี้:
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
และอย่างที่สองซึ่งเป็นวิธีแก้ไขปัญหาเดียวกันที่มีประสิทธิภาพก็เป็นดังนี้:
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
เพื่อตอบสนองคำขอเหล่านี้ เพียงคลิกที่ปุ่ม Run ที่ด้านบนของหน้า เมื่อทำเช่นนี้ เราจะเปรียบเทียบข้อมูลเกี่ยวกับเวลาดำเนินการแบบสอบถาม ปรากฎว่าการดำเนินการค้นหาที่ไม่มีประสิทธิภาพจะใช้เวลานานกว่าการดำเนินการค้นหาครั้งที่สองอย่างน้อย 30 เท่า (เวลานี้แตกต่างกันไปในแต่ละรัน ตัวอย่างเช่น ระบบอาจรายงานว่าแบบสอบถามแรกใช้เวลา 37 ms ในการดำเนินการให้เสร็จสิ้น แต่การดำเนินการของ วินาที - 1 มิลลิวินาที)
และหากมีข้อมูลเพิ่มเติมทุกอย่างจะดูแย่ลงไปอีก (เพื่อให้มั่นใจในสิ่งนี้ลองดูที่ฉัน จำนวน 10 ล้านแถว)
สิ่งที่เราเพิ่งพูดคุยกันควรให้ข้อมูลเชิงลึกเกี่ยวกับวิธีการประมวลผลการสืบค้นฐานข้อมูลจริง ๆ
โปรดทราบว่ายิ่งมูลค่าสูงขึ้น OFFSET — ยิ่งคำขอใช้เวลานานเท่าใดจึงจะเสร็จสมบูรณ์
ฉันควรใช้อะไรแทนการรวมกันของ OFFSET และ LIMIT
แทนที่จะรวมกัน OFFSET и LIMIT ควรใช้โครงสร้างที่สร้างขึ้นตามรูปแบบต่อไปนี้:
SELECT * FROM table_name WHERE id > 10 LIMIT 20
นี่คือการดำเนินการค้นหาโดยใช้การแบ่งหน้าตามเคอร์เซอร์
แทนที่จะจัดเก็บสิ่งปัจจุบันไว้ในเครื่อง OFFSET и LIMIT และส่งไปพร้อมกับคำขอแต่ละครั้ง คุณจะต้องจัดเก็บคีย์หลักที่ได้รับครั้งล่าสุด (โดยปกติจะเป็นดังนี้ ID) และ LIMITด้วยเหตุนี้ จะได้รับข้อความค้นหาที่คล้ายกับที่กล่าวข้างต้น
ทำไม ประเด็นก็คือโดยการระบุตัวระบุของแถวสุดท้ายที่อ่านอย่างชัดเจน คุณจะบอก DBMS ของคุณได้ว่าต้องเริ่มค้นหาข้อมูลที่จำเป็นที่ใด นอกจากนี้ การค้นหาจะดำเนินการอย่างมีประสิทธิภาพด้วยการใช้กุญแจ โดยระบบจะไม่ต้องถูกรบกวนด้วยสายที่อยู่นอกช่วงที่กำหนด
ลองมาดูการเปรียบเทียบประสิทธิภาพต่อไปนี้ของข้อความค้นหาต่างๆ นี่เป็นคำถามที่ไม่มีประสิทธิภาพ
คำขอช้า
และนี่คือเวอร์ชันที่ปรับให้เหมาะสมของคำขอนี้
ขอด่วน
แบบสอบถามทั้งสองส่งคืนข้อมูลในปริมาณเท่ากันทุกประการ แต่อันแรกใช้เวลา 12,80 วินาทีจึงจะเสร็จ และอันที่สองใช้เวลา 0,01 วินาที คุณรู้สึกถึงความแตกต่างหรือไม่?
ปัญหาที่เป็นไปได้
เพื่อให้วิธีการสืบค้นที่นำเสนอทำงานได้อย่างมีประสิทธิภาพ ตารางจะต้องมีคอลัมน์ (หรือคอลัมน์) ที่มีดัชนีตามลำดับที่ไม่ซ้ำกัน เช่น ตัวระบุจำนวนเต็ม ในบางกรณี สิ่งนี้อาจกำหนดความสำเร็จของการใช้แบบสอบถามดังกล่าวเพื่อเพิ่มความเร็วในการทำงานกับฐานข้อมูล
โดยปกติแล้ว เมื่อสร้างแบบสอบถาม คุณจะต้องคำนึงถึงสถาปัตยกรรมเฉพาะของตาราง และเลือกกลไกเหล่านั้นที่จะทำงานได้ดีที่สุดบนตารางที่มีอยู่ ตัวอย่างเช่น หากคุณต้องการทำงานกับคิวรีที่มีข้อมูลที่เกี่ยวข้องจำนวนมาก คุณอาจพบว่าน่าสนใจ บทความ.
หากเราประสบปัญหาคีย์หลักหายไป เช่น หากเรามีตารางที่มีความสัมพันธ์แบบกลุ่มต่อกลุ่ม วิธีดั้งเดิมของการใช้ OFFSET и LIMITรับประกันว่าเหมาะกับเรา แต่การใช้งานอาจส่งผลให้เกิดการสืบค้นที่ช้า ในกรณีเช่นนี้ ฉันขอแนะนำให้ใช้คีย์หลักแบบเพิ่มค่าอัตโนมัติ แม้ว่าจะจำเป็นสำหรับจัดการกับการสืบค้นแบบแบ่งหน้าเท่านั้นก็ตาม
หากคุณสนใจในหัวข้อนี้ - , и - วัสดุที่มีประโยชน์หลายอย่าง
ผลของการ
ข้อสรุปหลักที่เราสามารถสรุปได้คือ ไม่ว่าเราจะพูดถึงฐานข้อมูลขนาดใดก็ตาม จำเป็นต้องวิเคราะห์ความเร็วของการดำเนินการสืบค้นเสมอ ทุกวันนี้ ความสามารถในการปรับขนาดของโซลูชันมีความสำคัญอย่างยิ่ง และหากทุกอย่างได้รับการออกแบบอย่างถูกต้องตั้งแต่เริ่มต้นการทำงานบนระบบใดระบบหนึ่ง ในอนาคต สิ่งนี้สามารถช่วยนักพัฒนาจากปัญหาต่างๆ มากมายได้
คุณจะวิเคราะห์และเพิ่มประสิทธิภาพการสืบค้นฐานข้อมูลได้อย่างไร
ที่มา: will.com