โลกของ MySQL และ MariaDB

วิธีตรวจสอบดัชนีใน MySQL | คำสั่ง SQL และการจัดการอย่างมีประสิทธิภาพ

การปรับแต่งประสิทธิภาพของ MySQL
目次

1. ดัชนี MySQL คืออะไร: กุญแจสู่การเพิ่มประสิทธิภาพฐานข้อมูล

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

บทบาทและประเภทของดัชนี

ดัชนีของ MySQL มีประเภทต่อไปนี้
  • PRIMARY（คีย์หลัก）：เป็นคีย์ที่เป็นเอกลักษณ์ที่อนุญาตให้มีเพียงหนึ่งค่าในแต่ละตาราง ใช้เป็นตัวระบุหลักของตาราง
  • UNIQUE ดัชนี：ดัชนีที่รักษาความเป็นเอกลักษณ์ ป้องกันไม่ให้ค่าซ้ำถูกแทรกลงในคอลัมน์ที่ระบุ
  • ดัชนีทั่วไป：ดัชนีที่ไม่มีข้อจำกัดความเป็นเอกลักษณ์ ใช้เพื่อเพิ่มประสิทธิภาพการค้นหาของคอลัมน์เฉพาะ
ดังนั้น ดัชนีช่วยเพิ่มประสิทธิภาพการค้นหาและการดำเนินการข้อมูลในตาราง โดยเฉพาะอย่างยิ่งในชุดข้อมูลขนาดใหญ่ที่เป็นส่วนสำคัญอย่างยิ่ง อย่างไรก็ตาม หากมีดัชนีมากเกินไป การทำ INSERT หรือ UPDATE จะช้าลง ดังนั้นจึงสำคัญที่จะจัดการดัชนีตามความจำเป็น

2. วิธีพื้นฐานในการตรวจสอบดัชนีใน MySQL

ใน MySQL เราใช้คำสั่ง SHOW INDEX เพื่อตรวจสอบดัชนีที่สร้างไว้ คำสั่งนี้เป็นคำสั่ง SQL ที่ง่ายและจะแสดงข้อมูลดัชนีภายในตารางที่ระบุ ต่อไปนี้จะแสดงขั้นตอนโดยละเอียด

โครงสร้างพื้นฐานของ SHOW INDEX และเนื้อหาที่แสดง

SHOW INDEX FROM ชื่อ_ตาราง;

คำอธิบายเนื้อหาที่แสดง

เมื่อรันคำสั่งนี้ จะมีข้อมูลดังต่อไปนี้แสดงออกมา
  • Table：ชื่อของตารางที่มีดัชนี
  • Non_unique：แสดงว่าดัชนีเป็นเอกลักษณ์ (0) หรืออนุญาตให้ซ้ำได้ (1)
  • Key_name：ชื่อของดัชนี
  • Column_name：ชื่อคอลัมน์ที่ดัชนีถูกนำไปใช้
  • Cardinality：การประมาณค่าที่เป็นเอกลักษณ์ที่บันทึกในดัชนี ใช้เป็นตัวชี้วัดประสิทธิภาพการค้นหา
โดยการใช้ข้อมูลนี้ คุณสามารถมองเห็นสถานะดัชนีภายในตารางและการจัดวางดัชนีบนแต่ละคอลัมน์ได้อย่างชัดเจน นอกจากนี้ยังสามารถใช้เงื่อนไข WHERE เพื่อกรองผลลัพธ์ที่ต้องการได้

3. การตรวจสอบดัชนีในตาราง INFORMATION_SCHEMA.STATISTICS

นอกจากคำสั่ง SHOW INDEX แล้ว MySQL ยังมีวิธีตรวจสอบดัชนีโดยอ้างอิงตาราง INFORMATION_SCHEMA.STATISTICS วิธีนี้สะดวกสำหรับการแสดงรายการดัชนีทั้งหมดของฐานข้อมูลและสามารถดึงข้อมูลที่ละเอียดขึ้นได้。

คิวรีพื้นฐานของ INFORMATION_SCHEMA.STATISTICS

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, INDEX_NAME 
FROM INFORMATION_SCHEMA.STATISTICS 
WHERE TABLE_SCHEMA = 'ชื่อฐานข้อมูล';

รายละเอียดผลลัพธ์ของคิวรี

  • TABLE_SCHEMA: ชื่อฐานข้อมูลที่ดัชนีเป็นส่วนหนึ่งของ
  • TABLE_NAME: ชื่อของตารางที่ดัชนีอยู่
  • COLUMN_NAME: ชื่อคอลัมน์ที่ดัชนีถูกนำไปใช้
  • INDEX_NAME: ชื่อของดัชนี
โดยใช้วิธีนี้ คุณสามารถตรวจสอบข้อมูลดัชนีที่ครอบคลุมหลายตารางหรือหลายฐานข้อมูลได้ในรูปแบบรายการ โดยเฉพาะอย่างยิ่งเป็นวิธีที่มีประโยชน์เมื่อจัดการดัชนีของฐานข้อมูลทั้งหมด。

4. วิธีการเพิ่มและลบดัชนีและผลกระทบของมัน

วิธีการเพิ่มดัชนี

ดัชนีสามารถเพิ่มได้ภายหลังตามความต้องการโดยใช้คำสั่งต่อไปนี้เพื่อสร้างดัชนีบนคอลัมน์ที่ระบุ
CREATE INDEX ชื่อดัชนี ON ชื่อเทเบิล(ชื่อคอลัมน์);
เช่น หากต้องการเพิ่มดัชนีให้คอลัมน์ email ของตาราง users ให้ทำตามดังนี้：
CREATE INDEX idx_email ON users(email);

วิธีการลบดัชนี

ดัชนีที่ไม่จำเป็นสามารถลบออกเพื่อเพิ่มประสิทธิภาพของการทำงาน INSERT และ UPDATE ได้ การลบใช้คำสั่ง DROP INDEX
DROP INDEX ชื่อดัชนี ON ชื่อเทเบิล;
ตัวอย่างของดัชนีที่ไม่จำเป็น ได้แก่ ดัชนีที่ตั้งบนคอลัมน์ที่ไม่ได้ใช้ในเงื่อนไขการค้นหา (WHERE) การลบดัชนีสามารถทำให้ความเร็วในการแทรกและอัปเดตข้อมูลดีขึ้น

5. ตรวจสอบประสิทธิภาพของดัชนีด้วยคำสั่ง EXPLAIN

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

วิธีการใช้คำสั่ง EXPLAIN อย่างพื้นฐาน

EXPLAIN SELECT * FROM ชื่อเทเบิล WHERE ชื่อคอลัมน์ = 'เงื่อนไข';
เมื่อใช้คำสั่งนี้ คุณสามารถตรวจสอบได้ว่าดัชนีถูกใช้หรือมีการสแกนทั้งหมดของแถว ผลลัพธ์จะประกอบด้วยรายการต่อไปนี้:
  • type：ประเภทของคิวรี (ALL หมายถึงสแกนทั้งหมด, INDEX หมายถึงใช้ดัชนี)
  • possible_keys：รายการดัชนีที่สามารถใช้กับคิวรีได้
  • key：ชื่อดัชนีที่ถูกใช้จริง
  • rows：จำนวนแถวที่คาดว่าจะสแกน
โดยอิงจากข้อมูลเหล่านี้ คุณสามารถวิเคราะห์ประสิทธิภาพของดัชนีและตัดสินใจว่าจำเป็นต้องปรับปรุงประสิทธิภาพการค้นหาหรือไม่

6. สรุป

การจัดการดัชนีอย่างเหมาะสมเป็นสิ่งจำเป็นสำหรับการเพิ่มประสิทธิภาพของฐานข้อมูล MySQL โดยเฉพาะอย่างยิ่งในตารางที่จัดการข้อมูลจำนวนมาก การตั้งค่าดัชนีบนคอลัมน์ที่ใช้ในเงื่อนไข WHERE หรือ JOIN จะทำให้ประสิทธิภาพการค้นหาดีขึ้นอย่างมาก อย่างไรก็ตาม หากมีดัชนีมากเกินไป การแทรกและอัปเดตข้อมูลจะช้าลง ดังนั้นการรักษาสมดุลที่เหมาะสมจึงสำคัญ เมื่อเข้าใจกระบวนการเพิ่ม ตรวจสอบ ลบดัชนี และตรวจสอบประสิทธิภาพ การปรับแต่งฐานข้อมูลจะทำได้ง่ายขึ้นและประสิทธิภาพของระบบโดยรวมก็จะดีขึ้น