วิธีตรวจสอบและปรับแต่งดัชนี MySQL: คู่มือฉบับสมบูรณ์ด้วย SHOW INDEX, EXPLAIN, และการปรับประสิทธิภาพ

1. Introduction

MySQL เป็นระบบฐานข้อมูลเชิงสัมพันธ์ที่ใช้กันอย่างแพร่หลายในหลายแอปพลิเคชันเว็บและระบบจัดการข้อมูล เพื่อเพิ่มความเร็วในการดึงข้อมูล มีกลไกที่เรียกว่า “ดัชนี” (index) อย่างไรก็ตาม หากไม่ได้รับการจัดการอย่างเหมาะสม ดัชนีอาจทำให้ประสิทธิภาพลดลงได้

Why Is Checking Indexes Important?

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

• Indexes are not properly created → ทำให้การค้นหาช้า
• Unnecessary indexes exist → ทำให้การอัปเดตและแทรกข้อมูลช้าลง
• Uncertainty about which indexes are being used → ทำให้ยากต่อการตัดสินใจว่าจะลบดัชนีที่ไม่ได้ใช้หรือไม่

What You Will Learn in This Article

• กลไกพื้นฐานของดัชนี MySQL
• วิธีตรวจสอบดัชนีปัจจุบัน (โดยใช้คำสั่ง SQL)
• วิธีจัดการและปรับแต่งดัชนี
• เทคนิคการวิเคราะห์การใช้ดัชนี

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

2. What Is a MySQL Index?

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

Basic Concept of Indexes

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

Types of MySQL Indexes

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

1. PRIMARY KEY (Primary Key Index)

• สามารถตั้งค่าได้เพียงครั้งเดียวต่อหนึ่งตาราง
• รับประกันความเป็นเอกลักษณ์ของแถวในตาราง
• ทำหน้าที่เป็นดัชนีแบบคลัสเตอร์

1. UNIQUE Index

• ทำให้ค่าที่อยู่ในคอลัมน์ที่กำหนดไม่มีการซ้ำกัน
• อนุญาตค่า NULL (สามารถมีหลายค่า NULL ได้)

1. INDEX (General Index)

• ใช้เพื่อเร่งความเร็วการค้นหา
• อนุญาตให้มีข้อมูลซ้ำได้

1. FULLTEXT Index (for text search)

• ปรับปรุงการค้นหาข้อความ
• ใช้ร่วมกับไวยากรณ์ MATCH ... AGAINST

1. SPATIAL Index (for geographic data)

• ออกแบบมาสำหรับข้อมูลเชิงพื้นที่ (GIS)

Advantages and Disadvantages of Indexes

Advantages

• ปรับปรุงความเร็วในการค้นหา query
• เพิ่มประสิทธิภาพของการทำ JOIN และเงื่อนไข WHERE
• ทำให้การดึงข้อมูลเฉพาะส่วนเป็นไปอย่างมีประสิทธิภาพ

Disadvantages

• ดัชนีจำนวนมากทำให้การ INSERT, UPDATE, และ DELETE ช้าลง
• ใช้พื้นที่ดิสก์เพิ่มขึ้น
• ดัชนีที่ออกแบบไม่ดีอาจทำให้ประสิทธิภาพลดลง

3. How to Check MySQL Indexes

เพื่อจัดการดัชนี MySQL อย่างเหมาะสม จำเป็นต้องตรวจสอบว่าตารางมีดัชนีใดบ้างในปัจจุบัน ในส่วนนี้เราจะอธิบายวิธีตรวจสอบดัชนีโดยใช้ คำสั่ง SHOW INDEX, INFORMATION_SCHEMA.STATISTICS, และคำสั่ง mysqlshow

SHOW INDEX Command (Basic Method)

ใน MySQL คุณสามารถใช้คำสั่ง SHOW INDEX เพื่อดึงรายการดัชนีที่กำหนดบนตารางเฉพาะ คำสั่งนี้ช่วยให้คุณตรวจสอบรายละเอียดเช่น ชื่อดัชนี, คอลัมน์ที่ดัชนีครอบคลุม, และว่ามีข้อจำกัดแบบ unique หรือไม่

Basic Syntax

SHOW INDEX FROM table_name;

Example

ตัวอย่างเช่น เพื่อตรวจสอบดัชนีที่กำหนดบนตาราง users ให้รัน SQL ดังต่อไปนี้:

SHOW INDEX FROM users;

Example Output

Using INFORMATION_SCHEMA.STATISTICS (Get Detailed Info)

โดยใช้ตารางระบบของ MySQL INFORMATION_SCHEMA.STATISTICS คุณสามารถดึงข้อมูลเดียวกับ SHOW INDEX พร้อมความยืดหยุ่นที่มากขึ้น

Check Indexes for a Specific Table

SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, NON_UNIQUE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'users';

ดึงดัชนีทั่วทั้งฐานข้อมูล

SELECT TABLE_NAME, COLUMN_NAME, INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database_name';

คำสั่ง mysqlshow (ตรวจสอบจาก CLI)

คุณยังสามารถดึงข้อมูลดัชนีโดยใช้เครื่องมือบรรทัดคำสั่งของ MySQL ได้ ซึ่งเป็นประโยชน์อย่างยิ่งเมื่อ ทำงานผ่าน SSH บนเซิร์ฟเวอร์ MySQL.

วิธีการรันคำสั่ง

mysqlshow -u username -p password database_name table_name

ตัวอย่าง

mysqlshow -u root -p my_database users

ควรทำอย่างไรหากไม่มีดัชนี

หากไม่มีดัชนีใดแสดงหลังจากรัน SHOW INDEX หรือสอบถาม INFORMATION_SCHEMA.STATISTICS ตารางอาจไม่มีดัชนีที่เหมาะสม ในกรณีนั้นคุณสามารถปรับปรุงประสิทธิภาพการค้นหาโดยการสร้างดัชนีตามความต้องการ.

สร้างดัชนีใหม่

CREATE INDEX idx_column ON users (email);

ตั้งค่า Primary Key (PRIMARY KEY)

ALTER TABLE users ADD PRIMARY KEY (id);

ลบดัชนีที่ไม่จำเป็น

ALTER TABLE users DROP INDEX idx_column;

4. วิธีตรวจสอบการใช้ดัชนี

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

การวิเคราะห์ Query ด้วย EXPLAIN

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

ไวยากรณ์พื้นฐาน

EXPLAIN SELECT * FROM table_name WHERE condition;

ตัวอย่าง

เช่น การค้นหาในตาราง users โดยใช้คอลัมน์ email เป็นเงื่อนไข:

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

ผลลัพธ์ตัวอย่าง

ประเด็นสำคัญ

• หาก type = ALL , คำสั่ง query กำลังทำ การสแกนตารางทั้งหมด ดังนั้นคุณอาจต้องการดัชนี.
• หากชื่อดัชนีปรากฏใน key , ดัชนีนั้นกำลังถูกใช้.
• หากค่าของ rows มากเกินไป อาจจำเป็นต้องทำการปรับแต่ง query.

การใช้ Performance Schema

ด้วย performance_schema ของ MySQL คุณสามารถวิเคราะห์อย่างละเอียดว่าดัชนีใดกำลังถูกใช้และความถี่ในการใช้ระหว่างการดำเนินการ query.

รับสถิติการดำเนินการ Query

SELECT * FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%SELECT%';

ตรวจสอบการใช้ดัชนีสำหรับตารางเฉพาะ

SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR, SUM_TIMER_WAIT
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database_name' AND OBJECT_NAME = 'users';

ควรทำอย่างไรหากดัชนีไม่ได้ถูกใช้

1. ตรวจสอบ Query

หากดัชนีไม่ได้ถูกใช้ โครงสร้างของ query อาจเป็นสาเหตุ ตัวอย่างเช่น รูปแบบต่อไปนี้อาจทำให้ดัชนีไม่ทำงาน.

❌ ตัวอย่างที่ไม่ถูกต้อง (ฟังก์ชันทำให้ดัชนีไม่ทำงาน)

SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

→ เนื่องจาก LOWER(email) ดัชนีบน email อาจถูกละเลย.

✅ ตัวอย่างที่แก้ไข (หลีกเลี่ยงการใช้ฟังก์ชัน)

SELECT * FROM users WHERE email = 'test@example.com';

2. สร้างดัชนีใหม่

หากดัชนีที่มีอยู่ทำงานไม่ถูกต้อง การลบและสร้างใหม่อาจช่วยปรับปรุงประสิทธิภาพได้บางครั้ง.

ALTER TABLE users DROP INDEX idx_email;
CREATE INDEX idx_email ON users(email);

3. อัปเดตสถิติ

หากสถิติของตารางล้าสมัย MySQL อาจไม่ใช้ดัชนีอย่างเหมาะสม คุณสามารถรีเฟรชสถิติด้วยคำสั่งต่อไปนี้:

ANALYZE TABLE users;

5. การจัดการดัชนี

MySQL indexes are essential for improving data retrieval performance. However, if not properly managed, they can degrade overall database performance. In this section, we explain in detail how to create, delete, and identify unnecessary indexes.

การสร้างดัชนี

การสร้างดัชนีที่เหมาะสมสามารถเร่งความเร็วในการค้นหาข้อมูลได้อย่างมาก ใน MySQL คุณสามารถเพิ่มดัชนีโดยใช้ CREATE INDEX หรือ ALTER TABLE。

ไวยากรณ์พื้นฐาน

CREATE INDEX index_name ON table_name(column_name);

ตัวอย่าง

เพื่อเพิ่มดัชนีให้กับคอลัมน์ email ในตาราง users:

CREATE INDEX idx_email ON users(email);

ดัชนีหลายคอลัมน์ (Composite Index)

CREATE INDEX idx_name_email ON users(last_name, first_name, email);

ดัชนีไม่ซ้ำกัน (Unique Index)

CREATE UNIQUE INDEX idx_unique_email ON users(email);

การตั้งค่าคีย์หลัก (PRIMARY KEY)

ALTER TABLE users ADD PRIMARY KEY (id);

การลบดัชนี

การลบดัชนีที่ไม่จำเป็นช่วยลดภาระของฐานข้อมูล

ไวยากรณ์พื้นฐาน

ALTER TABLE table_name DROP INDEX index_name;

ตัวอย่าง

ตัวอย่างเช่น เพื่อลบดัชนีชื่อ idx_email:

ALTER TABLE users DROP INDEX idx_email;

การระบุและลบดัชนีที่ไม่จำเป็น

ตรวจสอบดัชนีที่ไม่ได้ใช้งาน

SELECT * FROM sys.schema_unused_indexes;

ตรวจสอบสถานะตาราง (ผลกระทบของดัชนี)

SHOW TABLE STATUS LIKE 'users';

ลบดัชนีที่ไม่จำเป็น

ALTER TABLE users DROP INDEX idx_unused;

หลังจากการลบ แนะนำให้อัปเดตสถิติโดยใช้ ANALYZE TABLE。

ANALYZE TABLE users;

6. การปรับปรุงดัชนี (การปรับปรุงประสิทธิภาพ)

การจัดการดัชนีอย่างเหมาะสมสามารถปรับปรุงประสิทธิภาพการสอบถาม MySQL ได้อย่างมาก อย่างไรก็ตาม การสร้างดัชนีเพียงอย่างเดียวไม่เพียงพอ — ต้องมีการออกแบบ จัดการ และตรวจสอบอย่างเหมาะสม เพื่อรักษาประสิทธิภาพที่เหมาะสม

การออกแบบดัชนีที่เหมาะสม

ดัชนีที่ออกแบบมาดีสามารถปรับปรุงความเร็วในการค้นหาใน MySQL ได้อย่างมาก

กรณีที่ควรใช้ดัชนี

กรณีที่ไม่ควรใช้ดัชนี

การใช้ Slow Query Log

Slow Query Log ช่วยให้คุณสามารถระบุการสอบถามที่ใช้เวลานานและวิเคราะห์ว่าดัชนีใดที่ไม่ได้ถูกนำไปใช้

เปิดใช้งาน Slow Query Log

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- Log queries taking longer than 2 seconds

ตรวจสอบ Slow Query Log

SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

วิเคราะห์การสอบถามที่ช้า

EXPLAIN SELECT * FROM users WHERE last_login > '2024-01-01';

ตัวอย่างการนำดัชนีไปใช้

CREATE INDEX idx_last_login ON users(last_login);

การอัปเดตสถิติ (ANALYZE & OPTIMIZE)

ANALYZE TABLE (อัปเดตสถิติ)

ANALYZE TABLE users;

OPTIMIZE TABLE (การจัดเรียงข้อมูลใหม่)

OPTIMIZE TABLE users;

7. คำถามที่พบบ่อยเกี่ยวกับดัชนี (FAQ)

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

การเพิ่มดัชนีมากขึ้นจะทำให้ความเร็วในการค้นหาดีขึ้นหรือไม่?

ก. ไม่จำเป็นเสมอไป

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

• เพิ่มภาระในการเขียน (INSERT, UPDATE, DELETE)
• การใช้ดัชนีขึ้นอยู่กับการสอบถาม
• ดัชนีที่ไม่จำเป็นใช้หน่วยความจำ

คอลัมน์ใดที่ควรมีดัชนี?

ก. มีประสิทธิภาพในการนำดัชนีไปใช้กับประเภทคอลัมน์ต่อไปนี้:

ดัชนีถูกสร้างขึ้นอัตโนมัติหรือไม่?

ก. ดัชนีบางตัวถูกสร้างขึ้นอัตโนมัติ แต่ตัวอื่น ๆ ต้องเพิ่มด้วยตนเอง

ดัชนีที่สร้างโดยอัตโนมัติ

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY, -- PRIMARY KEY index
  email VARCHAR(255) UNIQUE -- Index automatically created by UNIQUE constraint
);

ดัชนีที่สร้างด้วยตนเอง

CREATE INDEX idx_email ON users(email);

ฉันควรเลือกใช้ดัชนี B-Tree, Hash หรือ FULLTEXT อย่างไร?

ฉันจะตรวจสอบขนาดของดัชนีได้อย่างไร?

SHOW TABLE STATUS LIKE 'users';

ฉันจะตรวจสอบว่าดัชนีกำลังถูกใช้หรือไม่ได้อย่างไร?

EXPLAIN SELECT * FROM users WHERE email = 'example@example.com';

ควรลบดัชนีเมื่อใด?

ลบดัชนีที่ไม่จำเป็น

ALTER TABLE users DROP INDEX idx_unused;

8. สรุป

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

ประเด็นสำคัญ

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

• ดัชนีเป็นกลไกที่ช่วยเร่งการดึงข้อมูล.
• มีหลายประเภท รวมถึง ดัชนี B-Tree, Hash, และ FULLTEXT .
• มีประสิทธิภาพสูงสุดเมื่อใช้กับคอลัมน์ที่อยู่ในเงื่อนไข WHERE, JOIN, และ ORDER BY.

วิธีตรวจสอบดัชนี MySQL

• ใช้คำสั่ง SHOW INDEX เพื่อดูรายการดัชนี.
• ใช้ INFORMATION_SCHEMA.STATISTICS เพื่อดึงข้อมูลรายละเอียด.

วิธีตรวจสอบการใช้ดัชนี

• ใช้ EXPLAIN เพื่อดูว่าคำสั่ง query ใช้ดัชนีใด.
• ใช้ Performance Schema เพื่อวิเคราะห์ความถี่การใช้ดัชนี.

การจัดการดัชนี

• ใช้ CREATE INDEX เพื่อสร้างดัชนีบนคอลัมน์ที่เหมาะสม.
• ใช้ ALTER TABLE DROP INDEX เพื่อลบดัชนีที่ไม่จำเป็น.
• ตรวจสอบขนาดดัชนีด้วย SHOW TABLE STATUS และทำการปรับแต่งตามต้องการ.

การปรับแต่งดัชนี (การปรับปรุงประสิทธิภาพ)

• สร้างดัชนีบนคอลัมน์ WHERE, JOIN, และ ORDER BY ที่ใช้บ่อย.
• ใช้ Slow Query Log เพื่อระบุและปรับแต่งคิวรีที่ช้า.
• อัปเดตสถิติด้วย ANALYZE TABLE และ OPTIMIZE TABLE.

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

1. ระบุคอขวดของคิวรีก่อนสร้างดัชนี.
2. เลือกดัชนีที่เหมาะสม.

• ใช้ดัชนีแบบคอลัมน์เดียวและดัชนีแบบคอมโพสิตอย่างเหมาะสม.
• ใช้ UNIQUE INDEX เมื่อจำเป็นต้องมีข้อจำกัดความเป็นเอกลักษณ์.

1. ลบดัชนีที่ไม่จำเป็นอย่างสม่ำเสมอ.

• ระบุดัชนีที่ไม่ได้ใช้โดยใช้ SHOW INDEX และ schema_unused_indexes.

1. อัปเดตสถิติอย่างสม่ำเสมอ.

• อัปเดตสถิติด้วย ANALYZE TABLE.
• รัน OPTIMIZE TABLE เพื่อแก้ไขการกระจายข้อมูลที่เกิดจากการลบและอัปเดต.

ขั้นตอนต่อไป

✅ รายการตรวจสอบเชิงปฏิบัติ
✅ คุณได้ตรวจสอบดัชนีปัจจุบันด้วย SHOW INDEX หรือยัง?
✅ คุณได้เปิดใช้งาน Slow Query Log และระบุคิวรีที่ช้าแล้วหรือยัง?
✅ คุณได้วิเคราะห์แผนการทำงานของคิวรีด้วย EXPLAIN หรือยัง?
✅ คุณได้ลบดัชนีที่ไม่จำเป็นและสร้างดัชนีที่เหมาะสมแล้วหรือยัง?
✅ คุณได้อัปเดตสถิติด้วย ANALYZE TABLE หรือยัง?

สรุปสุดท้าย

• การจัดการดัชนี MySQL อย่างเหมาะสมช่วยเพิ่มประสิทธิภาพการค้นหาอย่างมาก.
• ใช้ Slow Query Log และ EXPLAIN เพื่อวิเคราะห์ประสิทธิภาพของดัชนีและปรับแต่งตามนั้น.
• รักษาประสิทธิภาพของฐานข้อมูลโดยอัปเดตสถิติและปรับแต่งตารางอย่างสม่ำเสมอ.

นี่คือการสรุปคู่มือเต็มรูปแบบการจัดการดัชนี MySQL.

ใช้ความรู้นี้เพื่อสร้างระบบฐานข้อมูลที่เร็วและมีประสิทธิภาพมากขึ้น 💡🚀