การเพิ่มประสิทธิภาพการทำงานของแบบสอบถาม MySQL

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

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

1. ปรับปรุงความเร็วการสืบค้นผ่านการบัฟเฟอร์การสืบค้น

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

โชคดีที่ MySQL มีฟังก์ชันการบัฟเฟอร์แบบสอบถามให้เรา (การบัฟเฟอร์แบบสอบถามสามารถใช้ได้ใน MySQL 4.0.1 ขึ้นไปเท่านั้น) เราสามารถปรับปรุงประสิทธิภาพการสืบค้นได้ในระดับหนึ่งผ่านการแคชการสืบค้น

เราสามารถตั้งค่าบัฟเฟอร์แบบสอบถามผ่านไฟล์ my.ini ในไดเร็กทอรีการติดตั้ง MySQL การตั้งค่านั้นง่ายมาก เพียงตั้งค่า query_cache_type เป็น 1 หลังจากตั้งค่าแอตทริบิวต์นี้ ก่อนที่จะดำเนินการคำสั่ง SELECT ใด ๆ MySQL จะตรวจสอบในบัฟเฟอร์ว่ามีการดำเนินการคำสั่ง SELECT เดียวกันหรือไม่ หากเป็นเช่นนั้น และผลการดำเนินการยังไม่หมดอายุ ผลลัพธ์ของการสืบค้นจะถูกส่งกลับไปยังไคลเอนต์โดยตรง แต่เมื่อเขียนคำสั่ง SQL โปรดทราบว่าบัฟเฟอร์การสืบค้นของ MySQL คำนึงถึงตัวพิมพ์เล็กและตัวพิมพ์ใหญ่ คำสั่ง SELECT สองคำสั่งต่อไปนี้มีดังนี้: SELECT * จาก TABLE1

เลือก * จากตารางที่ 1

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

แม้ว่าบางครั้งการไม่ตั้งค่าบัฟเฟอร์การสืบค้นอาจทำให้ประสิทธิภาพการทำงานลดลง แต่ก็มีคำสั่ง SQL บางคำสั่งที่จำเป็นต้องสืบค้นข้อมูลแบบเรียลไทม์ หรือไม่ได้ใช้บ่อย (อาจดำเนินการวันละครั้งหรือสองครั้ง) สิ่งนี้จำเป็นต้องปิดการบัฟเฟอร์ แน่นอน คุณสามารถปิดแคชการสืบค้นได้โดยการตั้งค่า query_cache_type แต่การดำเนินการนี้จะปิดแคชการสืบค้นอย่างถาวร MySQL 5.0 ให้วิธีการปิดแคชแบบสอบถามชั่วคราว: SELECT SQL_NO_CACHE field1, field2 FROM TABLE1

เนื่องจากคำสั่ง SQL ข้างต้นใช้ SQL_NO_CACHE ไม่ว่าคำสั่ง SQL นี้จะถูกดำเนินการก่อนหน้านี้หรือไม่ เซิร์ฟเวอร์จะไม่ค้นหาในบัฟเฟอร์และจะดำเนินการทุกครั้ง

นอกจากนี้เรายังสามารถตั้งค่า query_cache_type ใน my.ini เป็น 2 เพื่อให้แคชแบบสอบถามจะถูกใช้หลังจากใช้ SQL_CACHE เท่านั้น เลือก SQL_CALHE * จากตาราง 1

2. การเพิ่มประสิทธิภาพการสืบค้นอัตโนมัติของ MySQL

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

-

ID INT (10) ไม่ได้ลงนามไม่เป็นโมฆะ AUTO_INCREMENT

ชื่อ VARCHAR (100) ไม่เป็นโมฆะ

ราคาลอยตัวไม่เป็นโมฆะ

SALE_COUNT INT ไม่เป็นโมฆะ

วันที่ SALE_DATE ไม่เป็นโมฆะ

คีย์หลัก (ID)，

ดัชนี (ชื่อ)

ดัชนี (SALE_DATE)

-

สมมติว่ามีข้อมูลหลายล้านชิ้นเก็บไว้ในตารางนี้ และเราต้องการสืบค้นราคาเฉลี่ยของผลิตภัณฑ์หมายเลข 1000 ในปี 2547 และ 2548 เราสามารถเขียนคำสั่ง SQL ต่อไปนี้: SELECT AVG(PRICE) FROM SALES

โดยที่ ID = 1,000 และวันที่ขายระหว่าง '2004-01-01' และ '31-12-2005';

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

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

3. การเรียงลำดับตามดัชนี

จุดอ่อนประการหนึ่งของ MySQL คือการเรียงลำดับ แม้ว่า MySQL จะสามารถสืบค้นบันทึกได้ประมาณ 15,000 รายการใน 1 วินาที แต่ MySQL สามารถใช้ดัชนีได้มากที่สุดเพียง 1 รายการเท่านั้นเมื่อทำการสืบค้น ดังนั้น หากเงื่อนไข WHERE ครอบครองดัชนีอยู่แล้ว ดัชนีจะไม่ถูกนำมาใช้ในการเรียงลำดับ ซึ่งจะลดความเร็วของการสืบค้นลงอย่างมาก เราสามารถดูคำสั่ง SQL ต่อไปนี้: SELECT * FROM SALES WHERE NAME = “name” ORDER BY SALE_DATE DESC;

มีการใช้ดัชนีในช่อง NAME ในส่วนคำสั่ง WHERE ของ SQL ข้างต้น ดังนั้นดัชนีจะไม่ถูกนำมาใช้อีกต่อไปเมื่อเรียงลำดับ SALE_DATE เพื่อที่จะแก้ไขปัญหานี้ เราสามารถสร้างดัชนีคอมโพสิตบนตาราง SALES: ALTER TABLE SALES DROP NAME NAME, ADD INDEX (NAME, SALE_DATE)

ด้วยวิธีนี้ ความเร็วจะดีขึ้นอย่างมากเมื่อใช้คำสั่ง SELECT ด้านบนเพื่อสืบค้น แต่ระวังเมื่อใช้วิธีนี้ ตรวจสอบให้แน่ใจว่าไม่มีฟิลด์การเรียงลำดับในส่วนคำสั่ง WHERE ในตัวอย่างข้างต้น คุณไม่สามารถใช้ SALE_DATE เพื่อสืบค้นได้ มิฉะนั้น แม้ว่าการเรียงลำดับจะเร็วกว่า แต่ก็ไม่มีดัชนีแยกต่างหากในฟิลด์ SALE_DATE ดังนั้นแบบสอบถามจะช้าลง

4. การตรวจจับคำค้นหาที่ไม่สามารถเข้าถึงได้

เมื่อดำเนินการคำสั่ง SQL คุณจะต้องพบกับเงื่อนไขบางประการที่ต้องเป็นเท็จอย่างหลีกเลี่ยงไม่ได้ สิ่งที่เรียกว่าเงื่อนไข must-false คือ ไม่ว่าข้อมูลในตารางจะเปลี่ยนแปลงไปอย่างไร เงื่อนไขนี้ก็จะเป็นเท็จ เช่น ค่า WHERE < 100 และ ค่า > 200 เราไม่สามารถหาจำนวนที่น้อยกว่า 100 และมากกว่า 200 ได้

หากคุณพบเงื่อนไขการสืบค้นดังกล่าว ก็ไม่จำเป็นต้องดำเนินการคำสั่ง SQL ดังกล่าว โชคดีที่ MySQL สามารถตรวจจับสถานการณ์นี้ได้โดยอัตโนมัติ ตัวอย่างเช่น เราสามารถดูคำสั่ง SQL ต่อไปนี้: SELECT * FROM SALES WHERE NAME = “name1” AND NAME = “name2”

ข้อความค้นหาข้างต้นค้นหาบันทึกที่มี NAME เท่ากับทั้ง name1 และ name2 แน่นอนว่านี่เป็นแบบสอบถามที่ไม่สามารถเข้าถึงได้ และเงื่อนไข WHERE ต้องเป็นเท็จ ก่อนที่ MySQL จะดำเนินการคำสั่ง SQL ก่อนอื่นจะทำการวิเคราะห์ว่าเงื่อนไข WHERE เป็นแบบสอบถามที่ไม่สามารถเข้าถึงได้หรือไม่ หากเป็นเช่นนั้น คำสั่ง SQL จะไม่ถูกดำเนินการอีกต่อไป เพื่อตรวจสอบสิ่งนี้ ก่อนอื่นเราใช้ EXPLAIN เพื่อทดสอบ SQL ต่อไปนี้: EXPLAIN SELECT * FROM SALES WHERE NAME = "name1"

แบบสอบถามข้างต้นเป็นแบบสอบถามปกติ เราจะเห็นว่ารายการตารางในข้อมูลการดำเนินการที่ส่งคืนโดย EXPLAIN คือการขาย นี่แสดงให้เห็นว่า MySQL ดำเนินการขาย ดูข้อความต่อไปนี้อีกครั้ง: อธิบาย SELECT * จากการขาย โดยที่ NAME = “name1” และ NAME = “name2”

เราจะเห็นว่ารายการในตารางว่างเปล่า ซึ่งหมายความว่า MySQL ไม่ได้ดำเนินการบนตาราง SALES

5. ใช้การเลือกแบบสอบถามต่างๆ เพื่อปรับปรุงประสิทธิภาพ

นอกเหนือจากการใช้งานคำสั่ง SELECT ตามปกติแล้ว MySQL ยังมอบตัวเลือกมากมายให้กับเราซึ่งสามารถปรับปรุงประสิทธิภาพการสืบค้นได้ ตามที่กล่าวไว้ข้างต้น SQL_NO_CACHE และ SQL_CACHE ซึ่งใช้ในการควบคุมการบัฟเฟอร์แบบสอบถามเป็นสองตัวเลือก ในส่วนนี้ ฉันจะแนะนำตัวเลือกการสืบค้นที่ใช้กันทั่วไปบางส่วน

1. STRAIGHT_JOIN: บังคับลำดับการเชื่อมต่อ

เมื่อเราเชื่อมต่อตารางตั้งแต่สองตารางขึ้นไปสำหรับการสืบค้น เราไม่จำเป็นต้องสนใจว่าตารางใดที่ MySQL จะเชื่อมต่อก่อนและตารางใดที่จะเชื่อมต่อเป็นอันดับสุดท้าย ทั้งหมดนี้ถูกกำหนดโดยลำดับการเชื่อมต่อที่กำหนดโดย MySQL ผ่านชุดการคำนวณและการประเมินภายใน ในคำสั่ง SQL ต่อไปนี้ TABLE1 และ TABLE2 ไม่จำเป็นต้องเชื่อมต่อถึงกัน: SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1, TABLE2 WHERE …

หากนักพัฒนาจำเป็นต้องแทรกแซงตามลำดับการเชื่อมต่อด้วยตนเอง พวกเขาต้องใช้คีย์เวิร์ด STRAIGHT_JOIN เช่นคำสั่ง SQL ต่อไปนี้: SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …

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

2. แทรกแซงการใช้ดัชนีและปรับปรุงประสิทธิภาพ

การใช้ดัชนีได้ถูกกล่าวถึงข้างต้น ภายใต้สถานการณ์ปกติ MySQL จะตัดสินใจว่าจะใช้ดัชนีหรือไม่ และจะใช้ดัชนีใดในการสืบค้น แต่ในบางกรณีพิเศษ เราต้องการให้ MySQL ใช้ดัชนีเพียงตัวเดียวหรือสองสามตัว หรือเราไม่ต้องการใช้ดัชนีบางตัว สิ่งนี้ต้องใช้ตัวเลือกการสืบค้นของ MySQL เพื่อควบคุมดัชนี

จำกัดขอบเขตการใช้ดัชนี

บางครั้งเราสร้างดัชนีจำนวนมากในตารางข้อมูล เมื่อ MySQL เลือกดัชนี ดัชนีเหล่านี้จะได้รับการพิจารณาทั้งหมด แต่บางครั้งเราต้องการให้ MySQL พิจารณาดัชนีเพียงไม่กี่รายการแทนดัชนีทั้งหมด ซึ่งต้องใช้ USE INDEX เพื่อตั้งค่าคำสั่งสืบค้น เลือก * จากตารางที่ 1 ใช้ดัชนี (FIELD1, FIELD2) …

ดังที่เห็นได้จากคำสั่ง SQL ข้างต้น ไม่ว่าจะสร้างดัชนีจำนวนเท่าใดในตารางที่ 1 MySQL จะพิจารณาเฉพาะดัชนีที่สร้างบน FIELD1 และ FIELD2 เมื่อเลือกดัชนีเท่านั้น

จำกัดช่วงของดัชนีที่ไม่ได้ใช้

หากเรามีดัชนีจำนวนมากที่ต้องพิจารณาและมีดัชนีที่ไม่ได้ใช้เพียงไม่กี่รายการ เราสามารถใช้ IGNORE INDEX สำหรับการเลือกแบบย้อนกลับได้ ในตัวอย่างข้างต้น ดัชนีที่พิจารณาจะถูกเลือก ในขณะที่การใช้ IGNORE INDEX คือการเลือกดัชนีที่ไม่ได้รับการพิจารณา เลือก * จากตารางที่ 1 ละเว้นดัชนี (FIELD1, FIELD2) …

ในคำสั่ง SQL ข้างต้น จะไม่มีการใช้เฉพาะดัชนีบน FIELD1 และ FIELD2 ในตาราง TABLE1

บังคับให้ใช้ดัชนี

สองตัวอย่างข้างต้นมีตัวเลือกให้กับ MySQL ซึ่งหมายความว่า MySQL ไม่จำเป็นต้องใช้ดัชนีเหล่านี้ บางครั้งเราหวังว่า MySQL จะต้องใช้ดัชนีบางตัว (เนื่องจาก MySQL สามารถใช้ดัชนีเดียวเท่านั้นในการสืบค้น จึงบังคับให้ MySQL ใช้ดัชนีเดียวเท่านั้น) ต้องใช้ FORCE INDEX เพื่อทำหน้าที่นี้ให้สมบูรณ์ เลือก * จากตารางที่ 1 ดัชนีแรง (FIELD1) …

คำสั่ง SQL ข้างต้นใช้เฉพาะดัชนีที่สร้างบน FIELD1 เท่านั้น ไม่ใช่ดัชนีในฟิลด์อื่นๆ

3. ใช้ตารางชั่วคราวเพื่อปรับปรุงประสิทธิภาพการสืบค้น

เมื่อมีข้อมูลจำนวนมากในชุดผลลัพธ์ของการสืบค้นของเรา เราสามารถบังคับให้ชุดผลลัพธ์เป็นตารางชั่วคราวได้โดยใช้ตัวเลือก SQL_BUFFER_RESULT เพื่อให้สามารถปลดล็อกตาราง MySQL ได้อย่างรวดเร็ว (เพื่อให้คำสั่ง SQL อื่นสามารถสืบค้นสิ่งเหล่านี้ได้ บันทึก) ) และสามารถให้บริการชุดบันทึกขนาดใหญ่แก่ลูกค้าได้เป็นระยะเวลานาน เลือก SQL_BUFFER_RESULT * จากตาราง 1 โดยที่ ...

คล้ายกับตัวเลือก SQL_BUFFER_RESULT โดยทั่วไปจะมีการใช้ตัวเลือกนี้สำหรับการจัดกลุ่มหรือคำหลัก DISTINCT เลือก SQL_BUFFER_RESULT FIELD1, COUNT(*) จากกลุ่ม TABLE1 ตาม FIELD1

6. บทสรุป

นอกจากนี้ยังมี "หลักการ 20/80" ในการเขียนโปรแกรมนั่นคือ 20% ของโค้ดใช้เวลา 80% เช่นเดียวกับการพัฒนาแอปพลิเคชันฐานข้อมูล การเพิ่มประสิทธิภาพแอปพลิเคชันฐานข้อมูลมุ่งเน้นไปที่ประสิทธิภาพการดำเนินการของ SQL จุดเน้นของการเพิ่มประสิทธิภาพการสืบค้นข้อมูลคือการทำให้เซิร์ฟเวอร์ฐานข้อมูลอ่านข้อมูลจากดิสก์น้อยลงและอ่านหน้าตามลำดับแทนที่จะเป็นแบบไม่เรียงลำดับ