MySQL สารบัญ: วิธีสร้าง เพิ่ม และลบข้อมูล

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

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

การสร้างดัชนีมีทั้งข้อดีและข้อเสีย — ทุกครั้งที่มีการแทรกหรืออัปเดตข้อมูล ดัชนีจะต้องได้รับการดูแลรักษาอยู่เสมอ ดังนั้นการเพิ่มดัชนีมากเกินไปในตารางที่มีการเขียนข้อมูลบ่อยอาจส่งผลเสียต่อประสิทธิภาพโดยรวมได้ โดยทั่วไปแล้ว ควรสร้างดัชนีให้กับคอลัมน์ที่ปรากฏในส่วน WHERE, JOIN และ ORDER BY ของตารางที่มีการอ่านข้อมูลบ่อยกว่าการเขียนข้อมูล

ทำไมต้องใช้ดัชนี?

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

โดยทั่วไปแล้ว การตอบสนองที่ช้าเกิดจากการจัดเก็บข้อมูลตามลำดับทางกายภาพบนดิสก์ หากไม่มีดัชนี MySQL ต้องสแกนทุกแถวเพื่อหาแถวที่ตรงกับเงื่อนไข — เรียกว่า “การสแกนตารางทั้งหมด” ดัชนีช่วยให้ MySQL ข้ามไปยังแถวที่ตรงกันโดยตรง ซึ่งจะเปลี่ยนแผนการค้นหาจาก O(n) เป็นประมาณ O(log n) สำหรับการค้นหาใน B-tree

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

สามารถกำหนดดัชนีได้ในสองที่:

1. ในขณะที่สร้างตาราง
2. หลังจากที่ตารางนั้นมีอยู่แล้ว

ตัวอย่าง: สร้างดัชนีแบบอินไลน์ด้วยคำสั่ง CREATE TABLE

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

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;

เรียกใช้สคริปต์ใน MySQL โต๊ะทำงานติดกับ myflixdb ฐานข้อมูล

รีเฟรช myflixdb เพื่อดูสิ่งใหม่ members_indexed โต๊ะ. ที่ full_names คอลัมน์ปรากฏอยู่ใต้ ดัชนี ปม

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

เพิ่มดัชนีหลังจากตารางมีอยู่แล้ว

คุณมักจะพบว่าตารางที่มีอยู่แล้วนั้นต้องการดัชนี — การค้นหาข้อมูลช้าลง และแผนการดำเนินการ EXPLAIN แสดงให้เห็นว่ามีการสแกนตารางทั้งหมดในคอลัมน์ที่ปรากฏในส่วน WHERE CREATE INDEX คำสั่งนี้จะเพิ่มดัชนีโดยไม่ต้องสร้างตารางใหม่

CREATE INDEX `id_index` ON `table_name` (`column_name`);

ตัวอย่างที่เป็นรูปธรรม — เร่งความเร็วในการค้นหาบน title คอลัมน์ของ movies ตาราง:

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

ทุกคำค้นหาที่กรองตาม movies.title ขณะนี้ได้รับการสนับสนุนโดยดัชนีใหม่แล้ว การค้นหาที่กรองตามคอลัมน์อื่นๆ ยังคงสแกนตารางเว้นแต่ว่าคอลัมน์เหล่านั้นจะมีดัชนีของตัวเอง

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

แสดงรายการดัชนีในตาราง

ใช้ SHOW INDEXES เพื่อดูดัชนีทั้งหมดที่กำหนดไว้ในตาราง

SHOW INDEXES FROM `table_name`;

ตัวอย่าง — แสดงรายการดัชนีบน movies ตาราง:

SHOW INDEXES FROM `movies`;

รันคำสั่งใน MySQL ม้านั่งทำงานของช่างเครื่อง กับ myflixdb เพื่อดูดัชนีที่มีอยู่และคอลัมน์ที่ครอบคลุม

หมายเหตุ คีย์หลักและคีย์รองจะถูกจัดทำดัชนีโดยอัตโนมัติโดย MySQLแต่ละดัชนีจะมีชื่อเฉพาะและระบุคอลัมน์ที่ครอบคลุมอยู่

ไวยากรณ์: Drop Index

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

DROP INDEX `index_id` ON `table_name`;

ตัวอย่างที่เป็นรูปธรรม — ทิ้ง full_names ดัชนีจาก members_indexed:

DROP INDEX `full_names` ON `members_indexed`;

ประเภทของ MySQL ดัชนี

MySQL รองรับดัชนีหลายประเภท ซึ่งแต่ละประเภทเหมาะสมกับปริมาณงานที่แตกต่างกัน

ประเภท	จุดมุ่งหมาย
คีย์หลัก	รหัสระบุแถวที่ไม่ซ้ำกัน; จัดกลุ่มร่วมกับข้อมูลตารางใน InnoDB
ที่ไม่ซ้ำกัน	ช่วยเสริมสร้างเอกลักษณ์เฉพาะตัวไปพร้อมๆ กับการทำหน้าที่เป็นดัชนี
ดัชนี (บีทรี)	ดัชนีรองเริ่มต้นที่ใช้สำหรับการค้นหาช่วงและค้นหาแบบเท่ากัน
ข้อความเต็ม	ปรับให้เหมาะสมสำหรับการค้นหาข้อความในภาษาธรรมชาติด้วยฟังก์ชัน MATCH … AGAINST
เชิงพื้นที่	ดัชนี R-tree สำหรับประเภทข้อมูล GIS เช่น จุด (POINT) และรูปหลายเหลี่ยม (POLYGON)
กัญชา	การค้นหาค่าเท่ากันในเวลาคงที่; ใช้โดยกลไกการจัดเก็บข้อมูล MEMORY
คอมโพสิต (หลายคอลัมน์)	รวมหลายคอลัมน์เข้าไว้ในดัชนีเดียว โดยยึดหลักการจัดลำดับตามตัวอักษรซ้ายสุด

แนวทางปฏิบัติที่ดีที่สุดสำหรับ MySQL ดัชนี

นิสัยด้านล่างนี้จะช่วยให้ดัชนีมีประโยชน์และป้องกันไม่ให้กลายเป็นภาระที่ไร้ประโยชน์

• ดัชนีสำหรับรูปแบบการค้นหา ไม่ใช่ชื่อคอลัมน์: เพิ่มดัชนีที่ตรงกับเงื่อนไข WHERE, JOIN และ ORDER BY จริงๆ ไม่ใช่ "ทุกคอลัมน์ที่ฟังดูสำคัญ"
• ดูลำดับดัชนีรวม: คอลัมน์นำหน้าต้องปรากฏในคำสั่งค้นหาเพื่อให้สามารถใช้ดัชนีได้
• หลีกเลี่ยงการสร้างดัชนีซ้ำซ้อน: คำนำหน้าหลักของดัชนีแบบผสมนั้นครอบคลุมการค้นหาแบบคอลัมน์เดียวตามคำนำหน้านั้นอยู่แล้ว
• ตรวจสอบด้วย EXPLAIN: ตรวจสอบให้แน่ใจว่าโปรแกรมวางแผนได้เลือกดัชนีใหม่แล้ว
• ลบดัชนีที่ไม่ได้ใช้งาน: ใช้ sys.schema_unused_indexes in MySQL 5.7+ เพื่อค้นหาดัชนีที่ไม่มีอะไรอ่านได้
• ประเภทข้อมูลที่ตรงกัน: หากเงื่อนไข WHERE เปรียบเทียบคอลัมน์ประเภท VARCHAR กับตัวเลข จะไม่สามารถใช้ดัชนีได้เนื่องจากการแปลงประเภทโดยปริยาย

คำถามที่พบบ่อย