home

หน้าแรก
 Computer >> บทช่วยสอนคอมพิวเตอร์ >  >> การเขียนโปรแกรม >> MySQL

การสร้าง Index หลายคอลัมน์ (Composite Index) ใน MySQL

Composite Index (หรือ Multi-column Index) คือ Index ที่สร้างจากหลายคอลัมน์ร่วมกัน ช่วยให้ Query ที่กรอง/เรียงหลายคอลัมน์พร้อมกันทำงานได้เร็วขึ้น ใน MySQL สร้างได้ด้วยคีย์เวิร์ด KEY หรือ INDEX

ตัวอย่างการใช้งาน

สร้างตารางพร้อม Composite Index 3 คอลัมน์:

mysql> CREATE TABLE DemoTable1437 (
    -> StudentId INT,
    -> StudentName VARCHAR(20),
    -> StudentMarks INT,
    -> StudentAge INT,
    -> KEY (StudentId, StudentMarks, StudentAge)
    -> );

ตรวจสอบโครงสร้างตาราง:

mysql> DESC DemoTable1437;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| StudentId    | int(11)     | YES  | MUL | NULL    |       |
| StudentName  | varchar(20) | YES  |     | NULL    |       |
| StudentMarks | int(11)     | YES  |     | NULL    |       |
| StudentAge   | int(11)     | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

คอลัมน์ StudentId แสดง Key = MUL แสดงว่าเป็นส่วนหนึ่งของ Index

หลักการทำงานของ Composite Index (Leftmost Prefix Rule)

Index (A, B, C) สามารถใช้ได้กับ Query ที่มี WHERE:

  • A
  • A, B
  • A, B, C
  • A, C (ใช้ได้แค่ A ส่วน C ไม่ได้)

ไม่สามารถใช้ได้: B เพียงอย่างเดียว, B,C, C เพียงอย่างเดียว

คำสั่งสร้าง Index แบบแยกต่างหาก

-- สร้างตารางก่อน
CREATE TABLE DemoTable1437 (...);

-- เพิ่ม Index ภายหลัง
CREATE INDEX idx_student_marks_age ON DemoTable1437 (StudentId, StudentMarks, StudentAge);

-- หรือใช้ ALTER TABLE
ALTER TABLE DemoTable1437 ADD INDEX idx_student_marks_age (StudentId, StudentMarks, StudentAge);

การดู Index ที่มีอยู่

mysql> SHOW INDEX FROM DemoTable1437;

แนวทางปฏิบัติที่ดี

  • วางคอลัมน์ที่มี Cardinality สูง (ค่าที่หลากหลายมาก) ไว้หน้าสุด
  • คอลัมน์ที่ใช้ใน WHERE บ่อยสุดอยู่ซ้ายสุด
  • คอลัมน์ที่ใช้ใน ORDER BY ต่อท้าย
  • หลีกเลี่ยง Index เยอะเกินไป (แต่ละ Index ทำให้ INSERT/UPDATE/DELETE ช้าลง)
  • ใช้ EXPLAIN ตรวจสอบว่า Index ถูกใช้จริงหรือไม่

กรณีที่ไม่ควรสร้าง Composite Index

  • คอลัมน์ที่ Update บ่อยมาก
  • คอลัมน์ที่ Cardinality ต่ำมาก (เช่น Boolean, Status 2-3 ค่า)
  • Query ที่ใช้คอลัมน์ไม่ตามลำดับซ้าย-ขวา