SQL unleashed: 17 วิธีในการเร่งความเร็วการสืบค้น SQL ของคุณ

นักพัฒนา SQL ในทุกแพลตฟอร์มกำลังดิ้นรนดูเหมือนว่าจะติดอยู่ในDO WHILEวงจรที่ทำให้พวกเขาทำผิดซ้ำแล้วซ้ำเล่า นั่นเป็นเพราะฟิลด์ฐานข้อมูลยังค่อนข้างไม่สมบูรณ์ แน่นอนว่าผู้ขายกำลังก้าวไปข้างหน้า แต่พวกเขายังคงต่อสู้กับปัญหาใหญ่ ๆ การทำงานร่วมกันการจัดการทรัพยากรการจัดการพื้นที่และความเร็วยังคงสร้างความเสียหายให้กับนักพัฒนา SQL ไม่ว่าพวกเขาจะเข้ารหัสบน SQL Server, Oracle, DB2, Sybase, MySQL หรือแพลตฟอร์มเชิงสัมพันธ์อื่น ๆ

ปัญหาส่วนหนึ่งคือไม่มีสัญลักษณ์แสดงหัวข้อย่อยวิเศษและสำหรับแนวทางปฏิบัติที่ดีที่สุดเกือบทุกข้อฉันสามารถแสดงข้อยกเว้นให้คุณได้อย่างน้อยหนึ่งข้อ โดยปกตินักพัฒนาซอฟต์แวร์จะค้นหาวิธีการที่ตนเองชื่นชอบแม้ว่าโดยปกติแล้วจะไม่รวมโครงสร้างใด ๆ สำหรับประสิทธิภาพหรือการทำงานพร้อมกันและไม่ต้องกังวลกับการสำรวจตัวเลือกอื่น ๆ นั่นอาจเป็นอาการของการขาดการศึกษาหรือนักพัฒนาอยู่ใกล้กระบวนการมากเกินไปที่จะรับรู้เมื่อพวกเขาทำอะไรผิดพลาด แบบสอบถามอาจทำงานได้ดีกับชุดข้อมูลทดสอบในเครื่อง แต่ล้มเหลวอย่างน่าอนาถในระบบการผลิต

ฉันไม่คาดหวังว่านักพัฒนา SQL จะเป็นผู้ดูแลระบบ แต่พวกเขาต้องคำนึงถึงปัญหาการผลิตเมื่อเขียนโค้ด หากพวกเขาไม่ทำในระหว่างการพัฒนาครั้งแรก DBA จะทำให้พวกเขากลับไปทำในภายหลังและผู้ใช้จะต้องทนทุกข์ทรมานในระหว่างนี้

มีเหตุผลที่เรากล่าวว่าการปรับฐานข้อมูลเป็นทั้งศิลปะและวิทยาศาสตร์ เป็นเพราะมีกฎที่ยากและรวดเร็วเพียงไม่กี่กฎที่บังคับใช้ทั่วกระดาน ปัญหาที่คุณแก้ไขในระบบหนึ่งไม่ได้เป็นปัญหาในอีกระบบหนึ่งและในทางกลับกัน ไม่มีคำตอบที่ถูกต้องในการปรับแต่งข้อความค้นหา แต่นั่นไม่ได้หมายความว่าคุณควรยอมแพ้

มีหลักการที่ดีบางประการที่คุณสามารถปฏิบัติตามที่ควรให้ผลลัพธ์ในชุดค่าผสมเดียวหรืออย่างอื่น ฉันได้สรุปไว้ในรายการสิ่งที่ควรทำและไม่ควรทำของ SQL ที่มักถูกมองข้ามหรือยากที่จะมองเห็น เทคนิคเหล่านี้ควรให้ข้อมูลเชิงลึกมากขึ้นเกี่ยวกับความคิดของ DBA ของคุณรวมถึงความสามารถในการเริ่มคิดกระบวนการในรูปแบบที่มุ่งเน้นการผลิต

1. อย่าใช้UPDATEแทนCASE

ปัญหานี้เป็นเรื่องปกติมากและแม้ว่าจะไม่ยากที่จะสังเกตเห็น แต่นักพัฒนาหลายคนมักมองข้ามเพราะการใช้UPDATEมีลักษณะเป็นธรรมชาติ

ยกตัวอย่างสถานการณ์นี้: คุณกำลังแทรกข้อมูลลงในตารางชั่วคราวและต้องการให้แสดงค่าบางค่าหากมีค่าอื่นอยู่ บางทีคุณอาจดึงออกจากโต๊ะลูกค้าและคุณต้องการให้ใครก็ตามที่มีคำสั่งซื้อมากกว่า 100,000 ดอลลาร์ถูกระบุว่าเป็น "ที่ต้องการ" ดังนั้นคุณจึงแทรกข้อมูลลงในตารางและเรียกใช้UPDATEคำสั่งเพื่อตั้งค่าคอลัมน์ CustomerRank เป็น "Preferred" สำหรับใครก็ตามที่มีคำสั่งซื้อมากกว่า $ 100,000 ปัญหาคือUPDATEคำสั่งถูกบันทึกซึ่งหมายความว่าจะต้องเขียนสองครั้งสำหรับทุกๆการเขียนลงในตาราง วิธีแก้ไขปัญหานี้คือการใช้CASEคำสั่งแบบอินไลน์ในแบบสอบถาม SQL เอง การดำเนินการนี้จะทดสอบทุกแถวสำหรับเงื่อนไขจำนวนคำสั่งซื้อและตั้งค่าป้ายกำกับ "ที่ต้องการ" ก่อนที่จะเขียนลงในตาราง การเพิ่มประสิทธิภาพอาจทำให้ส่ายได้

2. อย่าใช้รหัสซ้ำสุ่มสี่สุ่มห้า

ปัญหานี้ยังพบบ่อยมาก การคัดลอกรหัสของคนอื่นทำได้ง่ายมากเพราะคุณรู้ว่ามันดึงข้อมูลที่คุณต้องการ ปัญหาคือบ่อยครั้งที่มันดึงข้อมูลมากกว่าที่คุณต้องการและนักพัฒนาแทบไม่ต้องกังวลกับการตัดทอนข้อมูลดังนั้นพวกเขาจึงได้รับข้อมูลจำนวนมาก สิ่งนี้มักจะมาในรูปแบบของการรวมภายนอกเพิ่มเติมหรือเงื่อนไขพิเศษในWHEREข้อ คุณจะได้รับประสิทธิภาพที่เพิ่มขึ้นอย่างมากหากคุณตัดต่อโค้ดที่ใช้ซ้ำตามความต้องการของคุณ

3. ดึงเฉพาะจำนวนคอลัมน์ที่คุณต้องการ

ปัญหานี้คล้ายกับฉบับที่ 2 แต่เป็นปัญหาเฉพาะในคอลัมน์ การเขียนโค้ดข้อความค้นหาทั้งหมดของคุณนั้นง่ายเกินไปSELECT *แทนที่จะแสดงรายการทีละคอลัมน์ ปัญหาอีกครั้งคือดึงข้อมูลมากกว่าที่คุณต้องการ ฉันเคยเห็นข้อผิดพลาดนี้หลายสิบครั้ง นักพัฒนาทำการSELECT *สืบค้นกับตารางที่มี 120 คอลัมน์และแถวนับล้าน แต่จะใช้เพียงสามถึงห้าแถวเท่านั้น ณ จุดนั้นคุณกำลังประมวลผลข้อมูลมากกว่าที่คุณต้องการมันน่าแปลกใจที่การสืบค้นกลับมาทั้งหมด คุณไม่เพียง แต่ประมวลผลข้อมูลมากกว่าที่คุณต้องการ แต่คุณยังนำทรัพยากรออกไปจากกระบวนการอื่น ๆ อีกด้วย

4. อย่าจุ่มสองครั้ง

นี่เป็นอีกสิ่งหนึ่งที่ฉันเคยเห็นมากกว่าที่ควรจะมี: ขั้นตอนการจัดเก็บถูกเขียนขึ้นเพื่อดึงข้อมูลจากตารางที่มีหลายร้อยล้านแถว ผู้พัฒนาต้องการลูกค้าที่อาศัยอยู่ในแคลิฟอร์เนียและมีรายได้มากกว่า 40,000 ดอลลาร์ ดังนั้นเขาจึงสอบถามลูกค้าที่อาศัยอยู่ในแคลิฟอร์เนียและนำผลลัพธ์ไปไว้ในตารางชั่วคราว จากนั้นเขาก็ค้นหาลูกค้าที่มีรายได้สูงกว่า 40,000 ดอลลาร์และนำผลลัพธ์เหล่านั้นไปไว้ในตารางชั่วคราว ในที่สุดเขาก็เข้าร่วมทั้งสองโต๊ะเพื่อรับผลิตภัณฑ์ขั้นสุดท้าย

คุณล้อเล่นฉัน? สิ่งนี้ควรทำในแบบสอบถามเดียว แต่คุณจุ่มโต๊ะขนาดใหญ่เป็นสองเท่า อย่าเป็นคนปัญญาอ่อน: ค้นหาตารางขนาดใหญ่เพียงครั้งเดียวเมื่อทำได้คุณจะพบว่าขั้นตอนของคุณทำงานได้ดีเพียงใด

สถานการณ์ที่แตกต่างกันเล็กน้อยคือเมื่อส่วนย่อยของตารางขนาดใหญ่จำเป็นต้องใช้หลายขั้นตอนในกระบวนการซึ่งทำให้ตารางขนาดใหญ่ถูกสอบถามในแต่ละครั้ง หลีกเลี่ยงปัญหานี้โดยการค้นหาชุดย่อยและเก็บไว้ที่อื่นจากนั้นชี้ขั้นตอนต่อมาไปยังชุดข้อมูลขนาดเล็กของคุณ

6. ทำข้อมูลก่อนขั้นตอน

นี่เป็นหนึ่งในหัวข้อโปรดของฉันเพราะเป็นเทคนิคเก่า ๆ ที่มักถูกมองข้าม หากคุณมีรายงานหรือโพรซีเดอร์ (หรือดีกว่านั้นก็คือชุดของพวกเขา) ที่จะทำการเชื่อมต่อที่คล้ายกันกับตารางขนาดใหญ่อาจเป็นประโยชน์สำหรับคุณในการจัดเตรียมข้อมูลล่วงหน้าโดยการเข้าร่วมตารางล่วงหน้าและคงอยู่ต่อไป ลงในตาราง ขณะนี้รายงานสามารถทำงานกับตารางที่จัดเตรียมไว้ล่วงหน้าและหลีกเลี่ยงการรวมขนาดใหญ่ได้

คุณไม่สามารถใช้เทคนิคนี้ได้เสมอไป แต่เมื่อทำได้คุณจะพบว่าเป็นวิธีที่ยอดเยี่ยมในการประหยัดทรัพยากรเซิร์ฟเวอร์

โปรดทราบว่านักพัฒนาจำนวนมากหลีกเลี่ยงปัญหาการเข้าร่วมนี้โดยมุ่งเน้นไปที่การสืบค้นและสร้างมุมมองอย่างเดียวรอบ ๆ การรวมเพื่อที่พวกเขาจะได้ไม่ต้องพิมพ์เงื่อนไขการเข้าร่วมซ้ำแล้วซ้ำอีก แต่ปัญหาในแนวทางนี้คือการสืบค้นยังคงทำงานสำหรับทุกรายงานที่ต้องการ ด้วยการจัดเตรียมข้อมูลล่วงหน้าคุณเรียกใช้การเข้าร่วมเพียงครั้งเดียว (เช่น 10 นาทีก่อนรายงาน) และคนอื่น ๆ ก็หลีกเลี่ยงการเข้าร่วมครั้งใหญ่ ฉันไม่สามารถบอกคุณได้ว่าฉันชอบเทคนิคนี้มากแค่ไหน ในสภาพแวดล้อมส่วนใหญ่มีตารางยอดนิยมที่เข้าร่วมตลอดเวลาดังนั้นจึงไม่มีเหตุผลว่าทำไมจึงไม่สามารถจัดฉากล่วงหน้าได้

7. ทำการลบและอัปเดตเป็นชุด ๆ

นี่เป็นอีกหนึ่งเทคนิคง่ายๆที่ถูกมองข้ามไปมาก การลบหรืออัปเดตข้อมูลจำนวนมากจากตารางขนาดใหญ่อาจเป็นฝันร้ายหากคุณทำไม่ถูกต้อง ปัญหาคือทั้งสองคำสั่งเหล่านี้ทำงานเป็นธุรกรรมเดียวและหากคุณจำเป็นต้องฆ่าพวกเขาหรือหากมีบางอย่างเกิดขึ้นกับระบบในขณะที่พวกเขากำลังทำงานระบบจะต้องย้อนกลับธุรกรรมทั้งหมด อาจใช้เวลานานมาก การดำเนินการเหล่านี้ยังสามารถปิดกั้นการทำธุรกรรมอื่น ๆ ในช่วงระยะเวลาของพวกเขาซึ่งเป็นปัญหาคอขวดของระบบ

วิธีแก้ปัญหาคือทำการลบหรืออัปเดตเป็นกลุ่มเล็ก ๆ วิธีนี้ช่วยแก้ปัญหาของคุณได้สองวิธี ขั้นแรกหากธุรกรรมถูกฆ่าไม่ว่าด้วยเหตุผลใดก็ตามมีเพียงไม่กี่แถวที่จะย้อนกลับฐานข้อมูลจึงกลับมาออนไลน์ได้เร็วกว่ามาก ประการที่สองในขณะที่แบตช์ที่เล็กกว่ากำลังส่งไปยังดิสก์คนอื่น ๆ สามารถแอบเข้ามาและทำงานบางอย่างได้ดังนั้นการทำงานพร้อมกันจึงเพิ่มขึ้นอย่างมาก

ตามบรรทัดเหล่านี้นักพัฒนาหลายคนติดอยู่ในหัวว่าการดำเนินการลบและอัปเดตเหล่านี้จะต้องเสร็จสิ้นในวันเดียวกัน นั่นไม่ใช่ความจริงเสมอไปโดยเฉพาะอย่างยิ่งหากคุณกำลังเก็บถาวร คุณสามารถยืดการดำเนินการนั้นออกไปได้นานเท่าที่คุณต้องการและชุดงานที่มีขนาดเล็กจะช่วยให้บรรลุเป้าหมายนั้นได้ หากคุณสามารถดำเนินการที่เข้มข้นเหล่านี้ได้นานขึ้นให้ใช้เวลาเพิ่มขึ้นและอย่าทำให้ระบบของคุณพัง

8. ใช้ตารางอุณหภูมิเพื่อปรับปรุงประสิทธิภาพของเคอร์เซอร์

ฉันหวังว่าตอนนี้เราทุกคนคงรู้แล้วว่าควรอยู่ห่างจากเคอร์เซอร์ถ้าทำได้ดีที่สุด เคอร์เซอร์ไม่เพียง แต่ประสบปัญหาด้านความเร็วซึ่งอาจเป็นปัญหากับการดำเนินการหลายอย่าง แต่ยังทำให้การดำเนินการของคุณบล็อกการดำเนินการอื่น ๆ เป็นเวลานานเกินความจำเป็น สิ่งนี้ช่วยลดการทำงานพร้อมกันในระบบของคุณได้อย่างมาก

อย่างไรก็ตามคุณไม่สามารถหลีกเลี่ยงการใช้เคอร์เซอร์ได้เสมอไปและเมื่อเกิดช่วงเวลาดังกล่าวขึ้นคุณอาจสามารถหลีกเลี่ยงปัญหาประสิทธิภาพการทำงานที่เกิดจากเคอร์เซอร์ได้โดยการใช้เคอร์เซอร์กับตารางชั่วคราวแทน ยกตัวอย่างเช่นใช้เคอร์เซอร์ที่ผ่านตารางและอัปเดตคอลัมน์สองสามคอลัมน์ตามผลการเปรียบเทียบ แทนที่จะทำการเปรียบเทียบกับตารางสดคุณอาจสามารถใส่ข้อมูลนั้นลงในตารางชั่วคราวและทำการเปรียบเทียบกับตารางนั้นแทน จากนั้นคุณมีUPDATEคำสั่งเดียวกับตารางถ่ายทอดสดที่มีขนาดเล็กกว่ามากและล็อคไว้เพียงช่วงเวลาสั้น ๆ

การตัดการแก้ไขข้อมูลเช่นนี้สามารถเพิ่มการทำงานพร้อมกันได้อย่างมาก ฉันจะจบด้วยการบอกว่าคุณแทบไม่ต้องใช้เคอร์เซอร์เลย มักจะมีวิธีแก้ปัญหาตามชุด คุณต้องเรียนรู้ที่จะเห็นมัน

9. อย่าซ้อนมุมมอง

มุมมองสามารถทำได้สะดวก แต่คุณต้องระมัดระวังในการใช้งาน แม้ว่ามุมมองจะช่วยบดบังการสืบค้นข้อมูลจำนวนมากจากผู้ใช้และเพื่อสร้างมาตรฐานการเข้าถึงข้อมูล แต่คุณสามารถพบว่าตัวเองอยู่ในสถานการณ์ที่คุณมีมุมมองที่เรียกการดูที่เรียกการดูที่เรียกดู สิ่งนี้เรียกว่าการดูแบบซ้อนกันและอาจทำให้เกิดปัญหาด้านประสิทธิภาพที่รุนแรงโดยเฉพาะใน 2 วิธี:

  • ขั้นแรกคุณมีแนวโน้มที่จะมีข้อมูลกลับมามากกว่าที่คุณต้องการ
  • ประการที่สองเครื่องมือเพิ่มประสิทธิภาพการสืบค้นจะยกเลิกและส่งคืนแผนการสืบค้นที่ไม่ถูกต้อง

ครั้งหนึ่งฉันเคยมีลูกค้าที่ชอบดูแบบซ้อน ลูกค้ามีมุมมองเดียวที่ใช้สำหรับเกือบทุกอย่างเนื่องจากมีการรวมที่สำคัญสองรายการ ปัญหาคือมุมมองส่งคืนคอลัมน์ที่มีเอกสาร 2MB อยู่ในนั้น เอกสารบางฉบับมีขนาดใหญ่กว่า ไคลเอ็นต์กำลังผลักดันอย่างน้อย 2MB พิเศษข้ามเครือข่ายสำหรับทุกแถวเดียวในเกือบทุกข้อความค้นหาที่รัน โดยปกติแล้วประสิทธิภาพของการสืบค้นเป็นสิ่งที่เลวร้าย

และไม่มีคำค้นหาใดที่ใช้คอลัมน์นั้นจริง! แน่นอนว่าคอลัมน์นี้ถูกฝังไว้ลึกเจ็ดมุมมองดังนั้นแม้จะพบว่ามันยาก เมื่อฉันลบคอลัมน์เอกสารออกจากมุมมองเวลาสำหรับการสืบค้นข้อมูลที่ใหญ่ที่สุดจะเปลี่ยนจาก 2.5 ชั่วโมงเป็น 10 นาที ในที่สุดเมื่อฉันคลี่คลายมุมมองที่ซ้อนกันซึ่งมีการรวมและคอลัมน์ที่ไม่จำเป็นหลายรายการและเขียนแบบสอบถามธรรมดาเวลาสำหรับการสืบค้นเดียวกันนั้นลดลงเหลือเพียงวินาที