เคล็ดลับ:
1. จะตรวจจับข้อความที่ไม่มีประสิทธิภาพได้อย่างไร?
ภายใต้ MySQL โดยการตั้งค่า --log-slow-queries=[ชื่อไฟล์] ในพารามิเตอร์เริ่มต้น คุณสามารถบันทึกคำสั่ง SQL ที่เวลาในการดำเนินการเกิน long_query_time (ค่าเริ่มต้นคือ 10 วินาที) ในไฟล์บันทึกที่ระบุ คุณยังสามารถแก้ไขเวลาสืบค้นแบบยาวในไฟล์การกำหนดค่าเริ่มต้นได้ เช่น:
# ตั้งเวลาสืบค้นแบบยาวเป็น 8 วินาที
long_query_time=8
2. จะสืบค้นดัชนีของตารางได้อย่างไร?
คุณสามารถใช้คำสั่ง SHOW INDEX ได้ เช่น:
SHOW INDEX FROM [ชื่อตาราง]
3. จะสอบถามการใช้ดัชนีของคำสั่งบางคำสั่งได้อย่างไร?
คุณสามารถใช้คำสั่ง EXPLAIN เพื่อดูการใช้ดัชนีของคำสั่ง SELECT บางอย่างได้ หากเป็นคำสั่ง UPDATE หรือ DELETE จะต้องแปลงเป็นคำสั่ง SELECT ก่อน
4. จะส่งออกเนื้อหาของเอ็นจิ้น INNODB ไปยังไฟล์บันทึกข้อผิดพลาดได้อย่างไร
เราสามารถใช้คำสั่ง SHOW INNODB STATUS เพื่อดูข้อมูลที่เป็นประโยชน์มากมายเกี่ยวกับเอ็นจิ้น INNODB เช่น กระบวนการปัจจุบัน ธุรกรรม ข้อผิดพลาดของคีย์ต่างประเทศ การหยุดชะงัก ปัญหาและสถิติอื่นๆ จะทำให้ข้อมูลนี้ถูกบันทึกลงในไฟล์บันทึกได้อย่างไร? ตราบใดที่คุณสร้างตาราง innodb_monitor โดยใช้คำสั่งต่อไปนี้ MySQL จะเขียนระบบลงในไฟล์บันทึกข้อผิดพลาดทุกๆ 15 วินาที:
CREATE
TABLE innodb_monitor (a INT) ENGINE=INNODB;
เพียงแค่ลบตารางนี้สามารถเป็น:
DROP TABLE innodb_monitor;
5. จะลบไฟล์บันทึกขนาดใหญ่เป็นประจำได้อย่างไร?
เพียงตั้งเวลาหมดอายุของบันทึกในไฟล์การกำหนดค่าเริ่มต้น:
expir_logs_days=10
หมายเหตุ:
1. มุ่งเน้นไปที่ดัชนี
ต่อไปนี้ใช้ตาราง TSK_TASK เป็นตัวอย่างเพื่อแสดงกระบวนการเพิ่มประสิทธิภาพคำสั่ง SQL ตาราง TSK_TASK ใช้เพื่อบันทึกงานการตรวจสอบระบบ ฟิลด์และดัชนีที่เกี่ยวข้องมีดังนี้:
ID: คีย์หลัก;
MON_TIME: เวลาในการตรวจสอบ;
STATUS_ID: สถานะคีย์ต่างประเทศที่สร้างขึ้นด้วย SYS_HIER_INFO.ID
หมายเหตุ: MySQL จะสร้างดัชนีสำหรับคีย์ต่างประเทศโดยอัตโนมัติ ในระหว่างกระบวนการเพิ่มประสิทธิภาพนี้ พบว่าดัชนีคีย์ต่างประเทศที่สร้างขึ้นโดยอัตโนมัติเหล่านี้จะทำให้เกิดการรบกวนประสิทธิภาพของคำสั่ง SQL โดยไม่จำเป็น
อันดับแรก เราพบในไฟล์บันทึกว่าการดำเนินการของคำสั่งต่อไปนี้ค่อนข้างช้า มากกว่า 10 วินาที:
# Query_time: 18 Lock_time: 0 Rows_sent: 295 Rows_examined: 88143
เลือก * จาก TSK_TASK WHERE STATUS_ID = 1064 และ MON_TIME >= ' 2007-11 -22' และ MON_TIME < '2007-11-23';
ปรากฎว่าจำเป็นต้องค้นหา 295 ระเบียนที่ตรงตามเงื่อนไขจาก 88143 ระเบียน ซึ่งแน่นอนว่าช้า ใช้คำสั่ง EXPLAIN อย่างรวดเร็วเพื่อตรวจสอบการใช้ดัชนี:
+----+-------------+-----------+------+- ---------
|. id |. select_type |. คีย์ที่เป็นไปได้
| ----------+-+-----
|. 1 |. TSK_TASK |. อ้างอิง |
. +-------------+----------+-+--------- --
จะเห็นได้ว่าตรงนั้น มีดัชนีให้เลือกสองแบบ: FK_task_status_id_TO_SYS_HIER_INFO, TSK_TASK_KEY_MON_TIME และดัชนีคีย์ต่างประเทศบน STATUS_ID จะถูกใช้เมื่อมีการดำเนินการคำสั่งในที่สุด
มาดูดัชนีของตาราง TSK_TASK กันอีกครั้ง:
+-----+-------------------------- -- --------
|. ตาราง |. Key_name |
. -- ---------------
|. TSK_TASK |. รหัส
|
---
+---------- -----------------------------ภายใต้ Oracle หรือฐานข้อมูลเชิงสัมพันธ์อื่นๆ เงื่อนไข WHERE ลำดับของฟิลด์ในดัชนีมีบทบาทสำคัญในการเลือก ดัชนี มาปรับลำดับฟิลด์ ใส่ STATUS_ID ต่อท้าย และ EXPLAIN อีกครั้ง:
EXPLAIN เลือก * จาก TSK_TASK WHERE MON_TIME >= '2007-11-22' และ MON_TIME < '2007-11-23' และ STATUS_ID = 1064
; ไม่มีผลกระทบ MySQL ยังคงใช้ดัชนีคีย์ต่างประเทศ STATUS_ID ที่สร้างโดยระบบ
หลังจากการวิเคราะห์อย่างรอบคอบ ดูเหมือนว่าแอตทริบิวต์ Cardinality (นั่นคือจำนวนค่าที่ไม่ซ้ำในดัชนี) มีบทบาทสำคัญอย่างยิ่งในการเลือกดัชนี MySQL จะเลือกดัชนีที่มีค่าไม่ซ้ำกันจำนวนน้อยกว่า ในดัชนีเป็นดัชนีของคำสั่งทั้งหมด
สำหรับคำสั่งนี้ หากคุณใช้ FK_task_status_id_TO_SYS_HIER_INFO เป็นดัชนีและตาราง TSK_TASK จัดเก็บข้อมูลเป็นเวลาหลายวัน จำนวนบันทึกที่สแกนจะมีขนาดใหญ่และความเร็วจะช้าลง มีโซลูชันการปรับให้เหมาะสมหลายวิธี:
หากมีงานไม่มากในหนึ่งวัน เราจะลบดัชนี FK_task_status_id_TO_SYS_HIER_INFO จากนั้น MySQL จะใช้ดัชนี TSK_TASK_KEY_MON_TIME จากนั้นสแกนบันทึกด้วย STATUS_ID 1064 ในข้อมูลของวันนั้น ซึ่งไม่ช้า ;
หากมีงานจำนวนมากในหนึ่งวัน เราจำเป็นต้องลบดัชนี FK_task_status_id_TO_SYS_HIER_INFO และ TSK_TASK_KEY_MON_TIME จากนั้นสร้างดัชนีร่วม STATUS_ID, MON_TIME ซึ่งจะมีประสิทธิภาพมากอย่างแน่นอน
ดังนั้นจึงขอแนะนำว่าอย่าใช้คีย์นอกสำหรับตารางที่มีเรคคอร์ดจำนวนมาก เพื่อหลีกเลี่ยงการลดประสิทธิภาพการทำงานลงอย่างรุนแรง
2. พยายามควบคุมจำนวนบันทึกในแต่ละตาราง
เมื่อจำนวนบันทึกในตารางมีขนาดใหญ่ การจัดการและการบำรุงรักษาจะยุ่งยากมาก เช่น การบำรุงรักษาดัชนีจะใช้เวลานานซึ่งจะทำให้เกิดการหยุดชะงักอย่างมาก การทำงานปกติของระบบรบกวน
สำหรับตารางที่มีปริมาณข้อมูลเพิ่มขึ้นอย่างต่อเนื่องเมื่อเวลาผ่านไป เราสามารถแยกแยะข้อมูลแบบเรียลไทม์และข้อมูลประวัติตามเวลาได้ เราสามารถใช้โปรแกรมบริการพื้นหลังเพื่อย้ายข้อมูลในตารางแบบเรียลไทม์ไปยังตารางประวัติเป็นประจำ เพื่อควบคุม จำนวนบันทึกในตารางเรียลไทม์และปรับปรุงประสิทธิภาพการสืบค้นและประสิทธิภาพการดำเนินงาน แต่โปรดทราบว่าระยะเวลาในการเคลื่อนไหวแต่ละครั้งควรสั้นเพียงพอเพื่อไม่ให้กระทบต่อการเขียนข้อมูลของโปรแกรมปกติ หากใช้เวลานานเกินไปอาจทำให้เกิดปัญหาการหยุดชะงักได้
3. กลยุทธ์การแฮชข้อมูล (พาร์ติชัน):
เมื่อจำนวนลูกค้าถึงระดับหนึ่ง ฐานข้อมูลเดียวจะไม่สามารถรองรับการเข้าถึงพร้อมกันที่สูงขึ้นได้ ในขณะนี้ คุณสามารถพิจารณาแฮช (การแบ่งพาร์ติชัน) ข้อมูลลูกค้าออกเป็นหลายฐานข้อมูลได้ แบ่งปันภาระ ปรับปรุงประสิทธิภาพและประสิทธิภาพของระบบโดยรวม