ความเสี่ยงของการตั้งค่า max_seeks_for_key สูงเกินไป

MAX_SEEKS_FOR_KEY แสดงถึงจำนวนคีย์สูงสุดที่สันนิษฐานว่าแสวงหาเครื่องมือเพิ่มประสิทธิภาพคาดว่าเมื่อค้นหาแถวตามคีย์ดัชนี โดยค่าเริ่มต้นค่านี้สูงมาก (ในระบบ 64 บิตโดยทั่วไปจะถูกตั้งค่าเป็น 18,446,744,073,709,551,615) ซึ่งหมายความว่าเครื่องมือเพิ่มประสิทธิภาพจะไม่ จำกัด ตัวเองโดยสมมติว่าการค้นหาคีย์ที่มีราคาแพงโดยดัชนีสามารถเป็นอย่างไร

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

1. แผนการสืบค้นที่ไม่ดี:
ความเสี่ยงที่สำคัญที่สุดของ MAX_SEEKS_FOR_KEY สูงคือการเพิ่มประสิทธิภาพการสืบค้นอาจเลือกการสแกนตารางเต็มแทนการสแกนดัชนีที่มีประสิทธิภาพมากขึ้น การสแกนตารางเต็มอ่านแต่ละแถวในตารางตามลำดับซึ่งอาจส่งผลให้ค่าใช้จ่าย I/O สูงขึ้นหากตารางมีขนาดใหญ่และแบบสอบถามต้องการเฉพาะแถวย่อยของแถว สิ่งนี้นำไปสู่การดำเนินการค้นหาช้าลงเมื่อเทียบกับการใช้การสแกนดัชนีที่เหมาะสม

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

3. ประสิทธิภาพที่ไม่ดีบนโต๊ะขนาดใหญ่:
เนื่องจากการสแกนเต็มรูปแบบจำเป็นต้องอ่านตารางทั้งหมดการตั้งค่า max_seeks_for_key ผลกระทบที่สูงเกินไปประสิทธิภาพในเชิงลบสำหรับตารางขนาดใหญ่ที่มีหลายแถว แบบสอบถามที่ควรใช้ประโยชน์จากดัชนีในการกรองแถวอย่างรวดเร็วแทนที่จะได้รับช่วงเวลาการสแกนที่ยาวนาน

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

5. ความล้มเหลวในการใช้ประโยชน์จากดัชนี cardinality ได้อย่างมีประสิทธิภาพ:
ดัชนี cardinality (การประมาณค่าที่แตกต่างกันในดัชนี) เป็นกุญแจสำคัญในการตัดสินใจของ MySQL Optimizer หาก MAX_SEEKS_FOR_KEY ตั้งค่าสูงเกินไป Optimizer จะลงโทษการสแกนดัชนีโดยสมมติว่าพวกเขาอาจต้องการการค้นหาคีย์ที่มีราคาแพงกว่าที่พวกเขาทำจริง ๆ แม้ว่าดัชนีที่มีความสามารถสูงควรใช้อย่างมีเหตุผล ดังนั้นการเพิ่มประสิทธิภาพตามดัชนีจะถูกมองข้ามหรือละเว้น

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

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

สรุปความเสี่ยง: การตั้งค่า MAX_SEEKS_FOR_KEY สูงเกินไปทำให้ MySQL เพิ่มประสิทธิภาพเพื่อสนับสนุนการสแกนตารางเต็มแทนการสแกนดัชนีซึ่งนำไปสู่การดำเนินการที่ช้าลงเพิ่ม CPU และ I/O โหลดที่ไม่มีประสิทธิภาพ

ความเสี่ยงเหล่านี้เน้นว่าทำไม DBAs จำนวนมากเลือกที่จะลด MAX_SEEKS_FOR_KEY ให้อยู่ในระดับปานกลางมากขึ้น (เช่น 1,000 หรือคล้ายกัน) การลดค่าจะกระตุ้นให้ Optimizer ต้องการการใช้ดัชนีมากกว่าการสแกนเต็มรูปแบบเมื่อดัชนี cardinality สูงกว่าเกณฑ์นั้นมักจะปรับปรุงความเร็วในการสืบค้นและลดการใช้ทรัพยากรสำหรับเวิร์กโหลดทั่วไป