การเพิ่มประสิทธิภาพอัตราการสุ่มตัวอย่างข้อมูล

6.1 บทนำ

ตอนนี้ขอย้ายจากทฤษฎีไปสู่การปฏิบัติ

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

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

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

ต่อไปนี้คือวิธี 5 อันดับแรกที่โปรแกรมเมอร์เพิ่มความเร็วในการสืบค้นฐานข้อมูล:

1. การเพิ่มดัชนีให้กับตารางในฐานข้อมูล
2. เขียนใหม่และเพิ่มประสิทธิภาพแบบสอบถาม
3. เปิดใช้งาน (และกำหนดค่า) การแคชบนฝั่งฐานข้อมูล
4. เปิดใช้งานการแคชในฝั่งไคลเอ็นต์
5. ดำเนินการ denormalization ฐานข้อมูล

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

6.2 ดัชนี

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

ในบทความนี้ฉันต้องการให้คำแนะนำที่เป็นประโยชน์เกี่ยวกับการใช้ MySQL

ฉันจะพูดทันที:

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

ปัญหาในการใช้งาน MySQL สามารถแบ่งออกได้เป็นสามกลุ่มดังต่อไปนี้ (ตามลำดับความสำคัญ):

1. การไม่ใช้หรือการใช้ดัชนีในทางที่ผิด
2. โครงสร้างฐานข้อมูลไม่ถูกต้อง
3. แบบสอบถาม SQL ไม่เหมาะสม \ ไม่ถูกต้อง

มาดูกันดีกว่าว่าแต่ละกลุ่มเหล่านี้เป็นอย่างไร

การใช้ดัชนี

การไม่ใช้หรือใช้ดัชนีในทางที่ผิดคือสิ่งที่ทำให้การค้นหาช้าลง สำหรับผู้ที่ไม่คุ้นเคยกับกลไกการทำงานของดัชนีหรือยังไม่ได้อ่านเกี่ยวกับเรื่องนี้ในคู่มือ เราขอแนะนำให้คุณอ่าน

เคล็ดลับสำหรับการใช้ดัชนี:

• คุณไม่จำเป็นต้องจัดทำดัชนีทุกอย่าง บ่อยครั้งโดยที่ไม่เข้าใจความหมาย ผู้คนเพียงแค่ทำดัชนีฟิลด์ทั้งหมดของตาราง ดัชนีเร่งการดึงข้อมูล แต่ทำให้การแทรกแถวและการอัปเดตช้าลง ดังนั้นการเลือกดัชนีแต่ละรายการจึงต้องมีความหมาย
• หนึ่งในพารามิเตอร์หลักที่กำหนดลักษณะของดัชนีคือการเลือก ซึ่งก็คือจำนวนขององค์ประกอบต่างๆ ในดัชนี มันไม่มีเหตุผลที่จะทำดัชนีฟิลด์ที่มีค่าที่เป็นไปได้สองหรือสามค่า จะมีประโยชน์เพียงเล็กน้อยจากดัชนีดังกล่าว
• การเลือกดัชนีควรเริ่มต้นด้วยการวิเคราะห์ข้อความค้นหาทั้งหมดเทียบกับตารางที่กำหนด บ่อยครั้งมาก หลังจากการวิเคราะห์ดังกล่าว แทนที่จะใช้ดัชนีสามหรือสี่ดัชนี คุณสามารถสร้างหนึ่งดัชนีผสมกันได้
• เมื่อใช้ดัชนีผสม ลำดับของฟิลด์ในดัชนีมีความสำคัญ
• อย่าลืมเกี่ยวกับการครอบคลุมดัชนี หากสามารถดึงข้อมูลทั้งหมดในแบบสอบถามจากดัชนีได้ MySQL จะไม่เข้าถึงตารางโดยตรง คำขอดังกล่าวจะดำเนินการอย่างรวดเร็ว ตัวอย่างเช่น สำหรับแบบสอบถามSELECT name FROM user WHERE login='test'ที่มีดัชนี (ชื่อเข้าสู่ระบบ) ไม่จำเป็นต้องเข้าถึงตาราง บางครั้งก็สมเหตุสมผลที่จะเพิ่มฟิลด์เพิ่มเติมให้กับดัชนีผสม ซึ่งจะทำให้ดัชนีครอบคลุมและเพิ่มความเร็วในการค้นหา
• สำหรับดัชนีแถว มักจะเพียงพอที่จะสร้างดัชนีเพียงบางส่วนของแถว สิ่งนี้สามารถลดขนาดดัชนีได้อย่างมาก
• หาก%เป็นจุดเริ่มต้นLIKE(SELECT * FROM table WHERE field LIKE '%test')ดัชนีจะไม่ถูกใช้
• ดัชนี FULLTEXTใช้กับ ไวยากรณ์ MATCH ... AGAINSTเท่านั้น

6.3 โครงสร้างฐานข้อมูล

ฐานข้อมูลที่ออกแบบมาอย่างดีเป็นกุญแจสำคัญในการทำงานกับฐานข้อมูลอย่างรวดเร็วและมีประสิทธิภาพ ในทางกลับกัน ฐานข้อมูลที่ออกแบบมาไม่ดีมักสร้างปัญหาให้นักพัฒนาปวดหัวอยู่เสมอ

เคล็ดลับการออกแบบฐานข้อมูล:

1. ใช้ชนิดข้อมูลที่เล็กที่สุดเท่าที่จะเป็นไปได้ ยิ่งชนิดข้อมูลมีขนาดใหญ่ ตารางก็ยิ่งต้องการการเข้าถึงดิสก์มากขึ้นเพื่อรับข้อมูล ใช้ขั้นตอนที่สะดวกมาก: SELECT * FROM table_name PROCEDURE ANALYSE();เพื่อกำหนดประเภทข้อมูลขั้นต่ำที่เป็นไปได้
2. สังเกตรูปแบบปกติในระหว่างขั้นตอนการออกแบบ บ่อยครั้งที่โปรแกรมเมอร์หันไปใช้การทำให้เป็นปกติในขั้นตอนนี้ อย่างไรก็ตาม ในกรณีส่วนใหญ่ในตอนเริ่มต้นโครงการ ยังห่างไกลจากความชัดเจนว่าจะส่งผลอย่างไร การปรับตารางให้เป็นปกตินั้นง่ายกว่าการต้องทนทุกข์ทรมานจากตารางที่ปรับค่าให้ต่ำกว่ามาตรฐาน และJOINบางครั้งก็ทำงานได้เร็วกว่าตารางดีนอร์มัลไลซ์ที่ไม่ถูกต้อง
3. อย่าใช้NULLคอลัมน์เว้นแต่คุณจะต้องการจริงๆ

6.4 แบบสอบถาม SQL

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

1. หลีกเลี่ยงการร้องขอในวง SQL เป็นภาษาของชุด และการเขียนแบบสอบถามไม่ควรใช้ในภาษาของฟังก์ชัน แต่เป็นภาษาของชุด
2. หลีกเลี่ยง*(เครื่องหมายดอกจัน) ในข้อความค้นหา อย่าลังเลที่จะระบุฟิลด์ที่คุณเลือก ซึ่งจะลดปริมาณข้อมูลที่ดึงและส่ง อย่าลืมเกี่ยวกับการครอบคลุมดัชนี แม้ว่าคุณจะเลือกเขตข้อมูลทั้งหมดในตารางแล้วก็ตาม จะเป็นการดีกว่าที่จะแสดงรายการเขตข้อมูลเหล่านั้น ประการแรกปรับปรุงความสามารถในการอ่านโค้ด เมื่อใช้เครื่องหมายดอกจัน เป็นไปไม่ได้ที่จะค้นหาว่าฟิลด์ใดอยู่ในตารางโดยไม่ดู ประการที่สองวันนี้ตารางของคุณมีห้า คอลัมน์ INT และหนึ่งเดือนต่อมาได้เพิ่ม TEXTและBLOBอีกหนึ่งรายการ และเครื่องหมายดอกจันยังคงอยู่เช่นเดิม
3. เมื่อแบ่งหน้า เพื่อรับจำนวนระเบียนทั้งหมด ใช้SQL_CALC_FOUND_ROWSและSELECT FOUND_ROWS();เมื่อใช้SQL_CALC_FOUND_ROWS MySQLแคชจำนวนแถวที่เลือก (ก่อนที่จะใช้ LIMIT) และเมื่อใช้SELECT FOUND_ROWS()จะคืนค่าแคชนี้เท่านั้นโดยไม่ต้องดำเนินการค้นหาใหม่
4. อย่าลืมว่ามีINSERTไวยากรณ์สำหรับการแทรกหลายรายการ ข้อความค้นหาหนึ่งรายการจะเรียงลำดับความสำคัญได้เร็วกว่าข้อความค้นหาหลายรายการในลูป
5. ใช้LIMITในที่ที่คุณไม่ต้องการข้อมูลทั้งหมด
6. ใช้INSERT… ON DUPLICATE KEY UPDATE…แทนและINSERTหรือUPDATEหลังการเลือก และมักใช้REPLACEแทน
7. อย่าลืมคุณสมบัติที่น่าทึ่งGROUP_CONCATนี้ สามารถช่วยตอบคำถามที่ซับซ้อนได้