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 ลดลง และยังเปลืองหน่วยความจำด้วย