Index และการจัดลำดับของ คอลัมน์ ใน Index บนฐานข้อมูล MySQL

เคยสงสัยกันหรือไม่ว่า Index มีผลอย่างไรกับฐานข้อมูลบ้าง และ การจัดลำดับของ Index แบบต่างๆให้ผลแตกต่างกันอย่างไร

ลองดูข้อมูลตัวอย่างนี้ ตารางนี้มีข้อมูลทั้งหมด 40 แถว และเป็นข้อมูล department ทั้งหมด 4 รายการ (ในรูปไม่ได้แสดงทั้งหมดนะครับ เพราะมันจะยาวไป)
ในกรณีที่ไม่มี Index เมื่อเรา Query ข้อมูลตามนี้
SELECT * FROM app_category WHERE type='department'

อธิบายง่ายๆว่าเอาข้อมูลที่มี type เท่ากับ department ออกมา ผลการ Query จะเป็นไปตามตารางด้านล่าง
หมายเหตุ คำสั่ง EXPLAIN ใช้สำหรับสั่งให้ MySQL อธิบายวิธีการ Query ออกมา โดยไม่ได้ต้องการผลลัพท์
ดูตรงลูกศรชี้นะครับ มันบอกว่าคำสั่งข้างต้นกระทำกับข้อมูลทั้งตาราง (บอกไปแต่แรกแล้วว่าตารางนี้มีข้อมูลทั้งหมด 40 แถว) นั่นหมายความว่าในกรณีที่ไม่มี Index การหาข้อมูลเพียง 4 แถวจะต้องไปหาจากทั้งตาราง ลองคิดดูว่าถ้าตารางนี้มีสักล้านแถว จะเกิดอะไรขึ้น

ต่อมาเรามาลองใส่ Index ให้กับตารางสัก 2 คอลัมน์ดู ตามรูปเลย คือใส่ให้กับ type และ category_id
ลอง Query ดูใหม่ ผลลัพท์คือ การค้นหามีการใช้ Index เกิดขึ้นแล้ว ทำให้มันไปค้นหาเอาเฉพาะข้อมูลที่ตรงตาม Index ซึ่งมีเพียง 4 แถวเท่านั้น (ต่อให้มีเป็นล้านแถวก็สบายมากละทีนี้)
ลองดูอีกสักตัวอย่าง ตอนนี้เราจะค้นหาข้อมูลที่มี Index อีกตัวคือ category_id ด้วย ผลลัพท์ก็จะเป็นไปตามตาราง คือมันยังคงทำงานกับข้อมูลทั้งหมด 4 แถว แต่ตามเงื่อนไขนี้ ผลลัพท์ที่จะได้มีเพียง 1 แถวเท่านั้น
ลองเปลี่ยนมาทำ Index อีกแบบดู โดยให้ทั้งสองคอลัมน์เป็น Index รายการเดียวกันดังรูป
ลอง Query เดิมอีกครั้ง ผลลัพท์ลดลงเหลือเพียง 1 แถวเท่านั้น เร็วสุดๆ
ลองกลับมาค้นหาเฉพาะรายการที่ type เท่ากับ department จะเห็นว่า Index ยังคงทำงานอยู่ที่ 4 แถวเช่นเดิม
ลองเปลี่ยนมาค้นหาที่ Index ตัวที่ 2 (category_id) จะเห็นว่ามีการกลับไป Query ทั้งตารางเหมือนไม่มี Index เลย
สรุป การมี Index จะช่วยให้การค้นหาข้อมูลทำได้เร็วขึ้นถ้าการค้นหานั้นๆ กระทำกับคอลัมน์ ที่เป็น Index อย่างถูกต้อง
  • การทำ Index ให้กับแต่ละคอลัมน์แยกกัน ช่วยให้การค้นหาเฉพาะคอลัมน์ใดคอลัมน์หนึ่งที่เป็น Index สามารถใช้ประโยชน์จาก Index ได้อย่างเต็มที่ 
  • ในกรณีที่ Index แยกกัน และมีการค้นหาข้อมูลจากคอลัมน์ที่เป็น Index มากกว่า 1 ตอลัมน์ จะยังคงใช้งาน Index ได้อยู่ แต่เป็นการใช้งาน Index จากทุกคอลัมน์รวมกัน
  • ในกรณีที่มีการรวมคอลัมน์ทั้งหมดเป็น Index รายการเดียวกัน การค้นหาจากทุกคอลัมน์ที่เป็น Index พร้อมกันจะให้ประสิทธิภาพสูงที่สุด
  • เรายังสามารถใช้งาน Index กับคอลัมน์ลำดับแรกสุดได้ในกรณีที่มีการค้นหาเฉพาะคอลัมน์รายการแรก แต่การค้นหาในคอลัมน์หลัง Index จะไม่มีการทำงาน
การออกแบบ Index ของตารางที่เหมาะสม คือให้ดูจากการ Query ว่าปกติแล้วเราจะ Query ข้อมูลแบบใดมากกว่ากัน เราก็เลือกให้เหมาะสม จริงๆแล้วเราสามารถทำทั้งสองกรณีร่วมกันได้ แต่จะทำให้ประสิทธิภาพในการ Insert Delete และ Update ลดลง และยังเปลืองหน่วยความจำด้วย
ผู้เขียน goragod โพสต์เมื่อ 11 ก.ย. 2566 เปิดดู 2,343 ป้ายกำกับ MySQL
^