MySQL บทช่วยสอนดัชนี – สร้าง เพิ่ม และวาง

โดย: มาร์คัสอัลเลน มาร์คัสอัลเลน
Hours วันที่อัพเดท

ดัชนีคืออะไร?

ดัชนีใน MySQL จัดเรียงข้อมูลตามลำดับที่เป็นระเบียบ สร้างขึ้นในคอลัมน์ที่จะใช้กรองข้อมูล คิดว่าดัชนีเป็นรายการที่เรียงลำดับตามตัวอักษร การค้นหาชื่อที่เรียงลำดับตามตัวอักษรจะง่ายกว่าการค้นหาชื่อที่ไม่ได้เรียงลำดับ

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

อะไรใช้ดัชนี?

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

MySQL ดัชนี

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

ไวยากรณ์: สร้างดัชนี

ดัชนีสามารถกำหนดได้ 2 วิธี

  1. ในช่วงเวลาของการสร้างตาราง
  2. หลังจากสร้างตารางแล้ว

ตัวอย่าง:

สำหรับ myflixdb ของเรา เราคาดว่าจะมีการค้นหาฐานข้อมูลชื่อเต็มเป็นจำนวนมาก

เราจะเพิ่มคอลัมน์ “full_names” ลงในดัชนีในตารางใหม่ “members_indexed”

สคริปต์ที่แสดงด้านล่างช่วยให้เราบรรลุเป้าหมายนั้น

CREATE TABLE `members_indexed` (
  `membership_number` int(11) NOT NULL AUTO_INCREMENT,
  `full_names` varchar(150) DEFAULT NULL,
  `gender` varchar(6) DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `physical_address` varchar(255) DEFAULT NULL,
  `postal_address` varchar(255) DEFAULT NULL,
  `contact_number` varchar(75) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`membership_number`),INDEX(full_names)
) ENGINE=InnoDB;

ดำเนินการสคริปต์ SQL ข้างต้นใน MySQL โต๊ะทำงานกับ “myflixdb”

MySQL สร้างดัชนี

การรีเฟรช myflixdb จะแสดงตารางที่สร้างขึ้นใหม่ชื่อ members_indexed

"บันทึก" ตาราง members_indexed มี "full_names" ในโหนดดัชนี

เมื่อฐานสมาชิกขยายและจำนวนเร็กคอร์ดเพิ่มขึ้น คำค้นหาในตาราง members_indexed ที่ใช้ส่วนคำสั่ง WHERE และ ORDER BY จะเร็วขึ้นมากเมื่อเปรียบเทียบกับคำค้นหาที่ทำในตารางสมาชิกโดยไม่มีการกำหนดดัชนี

เพิ่มไวยากรณ์พื้นฐานของดัชนี

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

เราสามารถใช้ Query ต่อไปนี้เพื่อเพิ่มดัชนี

CREATE INDEX id_index ON table_name(column_name);

ลองสมมติว่าคำค้นหาในตารางภาพยนตร์นั้นช้ามาก และเราต้องการใช้ดัชนีใน "ชื่อภาพยนตร์" เพื่อเพิ่มความเร็วของคำค้นหา เราสามารถใช้สคริปต์ต่อไปนี้เพื่อให้บรรลุผลดังกล่าว

CREATE INDEX `title_index` ON `movies`(`title`);

การดำเนินการค้นหาข้างต้นจะสร้างดัชนีในช่องชื่อเรื่องในตารางภาพยนตร์

ซึ่งหมายความว่าคำค้นหาทั้งหมดบนตารางภาพยนตร์ที่ใช้ "ชื่อเรื่อง" จะเร็วขึ้น

คำค้นหาในช่องอื่นๆ ในตารางภาพยนตร์จะยังคงช้าลงเมื่อเทียบกับคำค้นหาที่อิงตามช่องที่จัดทำดัชนีไว้

หมายเหตุ คุณสามารถสร้างดัชนีบนหลายคอลัมน์ได้หากจำเป็น ขึ้นอยู่กับฟิลด์ที่คุณต้องการใช้สำหรับเครื่องมือค้นหาฐานข้อมูลของคุณ

หากคุณต้องการดูดัชนีที่กำหนดไว้ในตารางใดตารางหนึ่ง คุณสามารถใช้สคริปต์ต่อไปนี้เพื่อดำเนินการดังกล่าวได้

SHOW INDEXES FROM table_name;

ตอนนี้เรามาดูดัชนีทั้งหมดที่กำหนดไว้ในตารางภาพยนตร์ใน myflixdb กัน

SHOW INDEXES FROM `movies`;

ดำเนินการสคริปต์ข้างต้นใน MySQL ปรับแต่ง เทียบกับ myflixdb ให้ผลลัพธ์กับดัชนีที่สร้างขึ้น

หมายเหตุ คีย์หลักและคีย์ต่างประเทศบนโต๊ะได้รับการจัดทำดัชนีแล้ว MySQL- แต่ละดัชนีมีชื่อเฉพาะของตัวเองและคอลัมน์ที่ถูกกำหนดไว้ก็จะแสดงเช่นกัน

ไวยากรณ์: วางดัชนี

คำสั่ง drop ใช้เพื่อลบดัชนีที่กำหนดไว้แล้วในตาราง

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

DROP INDEX `index_id` ON `table_name`;

ตอนนี้เรามาดูตัวอย่างในทางปฏิบัติกัน

DROP INDEX ` full_names` ON `members_indexed`;

การดำเนินการคำสั่งข้างต้นจะปล่อยดัชนีด้วย id ` full_names ` จากตาราง members_indexed

สรุป

  • ดัชนีมีประสิทธิภาพมากเมื่อต้องปรับปรุงประสิทธิภาพอย่างมาก MySQL คำค้นหา
  • คุณสามารถกำหนดดัชนีได้เมื่อสร้างตารางหรือเพิ่มในภายหลังหลังจากสร้างตารางแล้ว
  • คุณสามารถกำหนดดัชนีได้มากกว่าหนึ่งคอลัมน์ในตาราง
  • SHOW INDEX FROM table_name ใช้เพื่อแสดงดัชนีที่กำหนดบนตาราง
  • คำสั่ง DROP ใช้เพื่อลบดัชนีที่กำหนดไว้ในตารางที่กำหนด

ที่คุณอาจชอบ: