บทความนี้จะแนะนำกลยุทธ์ของ MySQL ในการปรับปรุงประสิทธิภาพของการดำเนินการโหลดข้อมูล หลายครั้งที่คุณเกี่ยวข้องกับการปรับแบบสอบถาม SELECT ให้เหมาะสม เนื่องจากเป็นแบบสอบถามที่ใช้บ่อยที่สุด และการกำหนดวิธีการปรับให้เหมาะสมนั้นไม่ได้ตรงไปตรงมาเสมอไป การโหลดข้อมูลลงในฐานข้อมูลนั้นค่อนข้างตรงไปตรงมา หลายครั้งที่คุณเกี่ยวข้องกับการปรับแบบสอบถาม SELECT ให้เหมาะสม เนื่องจากเป็นแบบสอบถามที่ใช้บ่อยที่สุด และการกำหนดวิธีการปรับให้เหมาะสมนั้นไม่ได้ตรงไปตรงมาเสมอไป การโหลดข้อมูลลงในฐานข้อมูลนั้นค่อนข้างตรงไปตรงมา อย่างไรก็ตาม มีกลยุทธ์ที่สามารถใช้เพื่อปรับปรุงประสิทธิภาพของการดำเนินการโหลดข้อมูลได้ โดยมีหลักการพื้นฐานดังนี้
การโหลดจำนวนมากเร็วกว่าการโหลดแถวเดียว เนื่องจากไม่จำเป็นต้องล้างแคชดัชนีหลังจากโหลดแต่ละระเบียนแล้ว สามารถล้างได้หลังจากโหลดชุดระเบียนแล้ว
การโหลดตารางโดยไม่มีดัชนีจะเร็วกว่าการโหลดหลังจากดัชนี หากมีดัชนี ไม่เพียงแต่จะต้องเพิ่มบันทึกลงในไฟล์ข้อมูลเท่านั้น แต่แต่ละดัชนีจะต้องได้รับการแก้ไขเพื่อให้สะท้อนถึงการเพิ่มบันทึกใหม่ด้วย
คำสั่ง SQL ที่สั้นกว่าจะเร็วกว่าคำสั่ง SQL ที่ยาวกว่า เนื่องจากเกี่ยวข้องกับการวิเคราะห์น้อยกว่าในฝั่งเซิร์ฟเวอร์ และเนื่องจากคำสั่งเหล่านี้ส่งผ่านเครือข่ายจากไคลเอนต์ไปยังเซิร์ฟเวอร์ได้เร็วกว่า ปัจจัยบางอย่างเหล่านี้อาจดูเล็กน้อย (โดยเฉพาะปัจจัยสุดท้าย) แต่หากคุณโหลดข้อมูลจำนวนมาก แม้แต่ปัจจัยเล็กๆ ก็สามารถสร้างความแตกต่างอย่างมากให้กับผลลัพธ์ได้ เราสามารถใช้หลักการทั่วไปข้างต้นเพื่อให้ได้ข้อสรุปเชิงปฏิบัติหลายประการเกี่ยวกับวิธีโหลดข้อมูลที่เร็วที่สุด:
โหลดข้อมูล (ในทุกรูปแบบ) มีประสิทธิภาพมากกว่า INSERT เนื่องจากโหลดแถวเป็นชุด การรีเฟรชดัชนีจะน้อยกว่า และเซิร์ฟเวอร์จะต้องแยกวิเคราะห์และตีความคำสั่งเดียวแทนที่จะเป็นหลายคำสั่ง
โหลดข้อมูลมีประสิทธิภาพมากกว่าโหลดข้อมูลในเครื่อง ด้วย LOAD DATA ไฟล์จะต้องอยู่บนเซิร์ฟเวอร์และต้องมีสิทธิ์ FILE แต่เซิร์ฟเวอร์สามารถอ่านไฟล์ได้โดยตรงจากดิสก์ ด้วย LOAD DATA LOCAL ไคลเอนต์จะอ่านไฟล์และส่งผ่านเครือข่ายไปยังเซิร์ฟเวอร์ซึ่งช้า
หากคุณต้องใช้ INSERT คุณควรใช้แบบฟอร์มที่อนุญาตให้ระบุหลายแถวในคำสั่งเดียว เช่น:
ยิ่งคุณระบุแถวในคำสั่งได้มากเท่าไรก็ยิ่งดีเท่านั้น ซึ่งจะช่วยลดจำนวนคำสั่งที่ต้องการ และลดจำนวนการรีเฟรชดัชนี หากคุณใช้ mysqldump เพื่อสร้างไฟล์สำรองฐานข้อมูล คุณควรใช้ตัวเลือก --extensed-insert เพื่อให้ไฟล์ดัมพ์มีคำสั่ง INSERT หลายบรรทัด คุณยังสามารถใช้ --opt (การเพิ่มประสิทธิภาพ) ซึ่งเปิดใช้งานตัวเลือก --extensed-insert ในทางกลับกัน ควรหลีกเลี่ยงการใช้ตัวเลือก --complete-insert กับ mysqldump ตัวเลือกนี้จะทำให้คำสั่ง INSERT เป็นบรรทัดเดียว ใช้เวลาดำเนินการนานกว่า และต้องการการวิเคราะห์มากกว่าคำสั่งที่สร้างขึ้นโดยไม่มีตัวเลือก --complete-insert
ใช้โปรโตคอลไคลเอ็นต์/เซิร์ฟเวอร์แบบบีบอัดเพื่อลดการรับส่งข้อมูลเครือข่าย สำหรับไคลเอ็นต์ MySQL ส่วนใหญ่ สามารถระบุได้ด้วยตัวเลือกบรรทัดคำสั่ง --compress โดยทั่วไปจะใช้เฉพาะบนเครือข่ายที่ช้ากว่าเท่านั้น เนื่องจากการบีบอัดต้องใช้เวลาในการประมวลผลมาก
ให้ MySQL แทรกค่าเริ่มต้น อย่าระบุคอลัมน์ในคำสั่ง INSERT ที่จะกำหนดค่าเริ่มต้นในทางใดทางหนึ่ง โดยเฉลี่ยแล้วจะส่งผลให้คำสั่งสั้นลงและลดจำนวนอักขระที่ส่งผ่านเครือข่ายไปยังเซิร์ฟเวอร์ นอกจากนี้ ข้อความสั่งที่มีค่าน้อยกว่าจำเป็นต้องมีการวิเคราะห์และการเปลี่ยนแปลงโดยเซิร์ฟเวอร์น้อยลง
หากตารางได้รับการจัดทำดัชนี คุณสามารถใช้การแทรกจำนวนมาก (คำสั่ง LOAD DATA หรือคำสั่ง INSERT หลายแถว) เพื่อลดค่าใช้จ่ายของดัชนี วิธีนี้จะช่วยลดผลกระทบของการอัปเดตดัชนีให้เหลือน้อยที่สุด เนื่องจากดัชนีจะต้องได้รับการรีเฟรชเมื่อประมวลผลแถวทั้งหมดเท่านั้น แทนที่จะรีเฟรชหลังจากแต่ละแถว
หากคุณต้องการโหลดข้อมูลจำนวนมากลงในตารางใหม่ คุณควรสร้างตารางและโหลดเมื่อไม่ได้จัดทำดัชนี จากนั้นจึงสร้างดัชนีหลังจากโหลดข้อมูล ซึ่งจะเร็วกว่า การสร้างดัชนีครั้งเดียว (แทนที่จะแก้ไขครั้งเดียวต่อแถว) จะเร็วกว่า
หากคุณทิ้งหรือปิดใช้งานดัชนีก่อนที่จะโหลด การสร้างหรือเปิดใช้งานดัชนีใหม่หลังจากโหลดข้อมูลอาจทำให้การโหลดเร็วขึ้น หากคุณต้องการใช้กลยุทธ์การลบหรือปิดใช้งานสำหรับการโหลดข้อมูล อย่าลืมทำการทดลองเพื่อดูว่าคุ้มค่าหรือไม่ (หากคุณกำลังโหลดข้อมูลจำนวนเล็กน้อยลงในตารางขนาดใหญ่ การสร้างและการจัดทำดัชนีใหม่อาจใช้เวลานานกว่าการโหลด ข้อมูล) )
DROP INDEX และ CREATE INDEX สามารถใช้ในการดรอปและสร้างดัชนีใหม่ได้ อีกทางเลือกหนึ่งคือการปิดใช้งานและเปิดใช้งานดัชนีโดยใช้ myisamchk หรือ isamchk สิ่งนี้จำเป็นต้องมีบัญชีบนโฮสต์เซิร์ฟเวอร์ MySQL ที่มีสิทธิ์การเขียนไฟล์ตาราง หากต้องการปิดใช้งานดัชนีตาราง ให้ป้อนไดเร็กทอรีฐานข้อมูลที่เกี่ยวข้องและดำเนินการคำสั่งใดคำสั่งหนึ่งต่อไปนี้:
ใช้ myisamchk สำหรับตาราง MyISAM ที่มีไฟล์ดัชนีที่มีนามสกุล .MYI และใช้ isamchk สำหรับตาราง ISAM ที่มีไฟล์ดัชนีที่มีนามสกุล .ISM หลังจากโหลดข้อมูลลงในตารางแล้ว ให้เปิดใช้งานดัชนีดังต่อไปนี้:
หากคุณตัดสินใจที่จะใช้การปิดใช้งานและเปิดใช้งานดัชนี คุณควรใช้โปรโตคอลการล็อคการซ่อมแซมตารางที่อธิบายไว้ในบทที่ 13 เพื่อป้องกันไม่ให้เซิร์ฟเวอร์เปลี่ยนการล็อคในเวลาเดียวกัน (แม้ว่าตารางจะยังไม่ได้รับการซ่อมแซมในขณะนี้ แต่ก็มีการปรับเปลี่ยนเหมือนกับตาราง กระบวนการซ่อมแซมดังนั้นคุณต้องใช้โปรโตคอลการล็อคเดียวกัน)
หลักการโหลดข้อมูลที่อธิบายไว้ข้างต้นยังนำไปใช้กับการสืบค้นแบบคงที่ที่เกี่ยวข้องกับไคลเอนต์ที่จำเป็นต้องดำเนินการที่แตกต่างกัน ตัวอย่างเช่น โดยทั่วไปคุณต้องการหลีกเลี่ยงการเรียกใช้แบบสอบถาม SELECT ที่ยาวบนตารางที่อัปเดตบ่อยครั้ง การสืบค้น SELECT ที่รันมายาวนานสามารถสร้างความขัดแย้งได้มากและลดประสิทธิภาพของตัวเขียน วิธีแก้ปัญหาหนึ่งที่เป็นไปได้คือจัดเก็บบันทึกในตารางชั่วคราวก่อน จากนั้นจึงเพิ่มบันทึกลงในตารางหลักเป็นระยะๆ หากการเขียนเป็นการดำเนินการ INSERT เป็นหลัก นี่ไม่ใช่แนวทางที่เป็นไปได้หากจำเป็นต้องเข้าถึงบันทึกใหม่ทันที แต่วิธีนี้สามารถใช้ได้ตราบใดที่ไม่สามารถเข้าถึงได้ในช่วงเวลาสั้นๆ การใช้ตารางชั่วคราวมีประโยชน์สองประการ ขั้นแรก จะลดการโต้แย้งด้วยคำสั่งคิวรี SELECT บนตารางหลัก ดังนั้นจึงดำเนินการได้เร็วขึ้น ประการที่สอง เวลารวมในการโหลดบันทึกจากตารางชั่วคราวลงในตารางหลักจะน้อยกว่าเวลารวมในการโหลดบันทึกแยกกัน แคชดัชนีที่เกี่ยวข้องจะต้องได้รับการรีเฟรชเมื่อสิ้นสุดการโหลดแต่ละชุดเท่านั้น แทนที่จะรีเฟรชหลังจากแต่ละแถว โหลด การประยุกต์ใช้กลยุทธ์นี้คือการเข้าถึงฐานข้อมูล MySQL จากเว็บเพจของเว็บเซิร์ฟเวอร์ ในสถานการณ์สมมตินี้ อาจไม่มีระดับอำนาจที่สูงกว่าที่จะรับประกันการป้อนข้อมูลของเรกคอร์ดลงในตารางหลักทันที
หากข้อมูลไม่ตรงกับประเภทของบันทึกเดียวที่จะแทรกในกรณีที่ระบบปิดระบบ อีกกลยุทธ์หนึ่งในการลดการรีเฟรชดัชนีคือการใช้ตัวเลือกการสร้างตาราง DELAYED_KEY_WRITE สำหรับตาราง MyISAM (ซึ่งอาจเป็นไปได้หากใช้ MySQL สำหรับ งานป้อนข้อมูลบางส่วน) สิ่งนี้จะเกิดขึ้น) ตัวเลือกนี้ทำให้แคชดัชนีได้รับการรีเฟรชเป็นครั้งคราวเท่านั้น แทนที่จะรีเฟรชหลังจากการแทรกทุกครั้ง
หากคุณต้องการใช้ประโยชน์จากการรีเฟรชดัชนีล่าช้าทั่วทั้งเซิร์ฟเวอร์ เพียงเริ่ม mysqld ด้วยตัวเลือก --delayed-key-write ในสถานการณ์สมมตินี้ การเขียนบล็อกดัชนีจะล่าช้าจนกว่าบล็อกจะต้องถูกล้างเพื่อให้มีที่ว่างสำหรับค่าดัชนีอื่นๆ จนกว่าจะดำเนินการคำสั่งล้างตาราง หรือจนกว่าตารางดัชนีจะถูกปิด
-