กลับไปที่คู่มือทั้งหมด
ประสิทธิภาพ··อ่าน 8 นาที

กลยุทธ์การทำดัชนีฐานข้อมูลเพื่อให้คำสั่ง SQL เร็วขึ้น

เรียนรู้วิธีการทำงานของดัชนีฐานข้อมูล เมื่อใดควรใช้ และข้อควรระวังทั่วไป ครอบคลุมถึง B-tree, ดัชนีแบบผสม (composite) และดัชนีแบบครอบคลุม (covering indexes) พร้อมตัวอย่าง SQL

ตัวอย่างดัชนีแบบผสมข้างการค้นหา B-tree ตามเส้นทางจาก 40 ผ่าน 60 ไปยังคีย์ 50
ในคู่มือนี้
  1. ดัชนีฐานข้อมูลคืออะไร?
  2. เมื่อใดที่คุณควรใช้ดัชนี (Indexes)?
  3. ประเภทของดัชนี (Indexes)
  4. ข้อผิดพลาดทั่วไปในการทำดัชนี
  5. การตรวจสอบการใช้งานดัชนี
  6. ดัชนีในการออกแบบสคีมาและ ERD
  7. ข้อมูลอ้างอิงและการอ่านเพิ่มเติม

ตัวอย่าง SQL จะใช้ PostgreSQL เว้นแต่จะระบุภาษาอื่นไว้ ตัวอย่างสั้นๆ สมมติว่าตารางที่อ้างถึงมีอยู่แล้ว โปรดตรวจสอบความถูกต้องของ DDL ในฐานข้อมูลเป้าหมายของคุณก่อนนำไปใช้งาน

ดัชนีฐานข้อมูลคืออะไร?

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

ดัชนี B-Tree ทำงานอย่างไร

ประเภทดัชนีที่พบบ่อยที่สุดใน PostgreSQL, MySQL, SQL Server และ SQLite คือ B-tree (B-tree) ให้ลองนึกภาพเหมือนสมุดโทรศัพท์ แทนที่จะอ่านชื่อทุกชื่อตั้งแต่ A ถึง Z คุณจะเปิดไปยังส่วนที่ใกล้เคียงที่สุด จำกัดวงให้แคบลงจนถึงหน้าที่ถูกต้อง และไปถึงรายการนั้น B-tree ทำงานในลักษณะเดียวกัน:

  • แผนผังถูกจัดระเบียบเป็น โหนด (nodes). โหนดภายในแต่ละโหนดประกอบด้วยคีย์ที่เรียงลำดับและตัวชี้ไปยังโหนดลูก
  • การค้นหาเริ่มต้นที่ ราก, เปรียบเทียบค่าที่ค้นหากับคีย์ของโหนด และติดตามตัวชี้ที่ถูกต้องลงไปผ่าน สาขา โหนด
  • มันไปถึง leaf ที่มีรายการที่ตรงกัน ขึ้นอยู่กับเอนจินและดัชนี รายการจะระบุแถว ประกอบด้วยคีย์แบบคลัสเตอร์ หรือเก็บข้อมูลแถวไว้เอง
  • แผนผังจะยังคงสมดุล แต่ความลึกและ I/O ขึ้นอยู่กับความกว้างของคีย์ ขนาดหน้า จำนวนแถว การแคช และว่าคิวรีดึงหน้าตารางด้วยหรือไม่
-- Basic index on a single column
CREATE INDEX idx_employees_email ON employees (email);

-- The database now maintains a B-tree on the email column.
-- A selective filter is a candidate for an index lookup; check EXPLAIN:
SELECT * FROM employees WHERE email = '[email protected]';

เมื่อใดที่คุณควรใช้ดัชนี (Indexes)?

ดัชนีไม่ได้ฟรี มันใช้พื้นที่ดิสก์และเพิ่มภาระให้กับทุกๆ INSERT, UPDATE, และ DELETE เนื่องจากเอนจินต้องรักษาดัชนีให้ตรงกับข้อมูลในตารางเสมอ ควรใช้ดัชนีอย่างมีกลยุทธ์:

  • คอลัมน์ใน WHERE ประโยค ดัชนีที่เหมาะสมสามารถช่วยในการกรองแบบเลือกที่พบบ่อย ประโยชน์ของมันขึ้นอยู่กับการกระจายของข้อมูลและแผนที่เลือก
  • คอลัมน์ใน JOIN เงื่อนไข ดัชนีอาจช่วยในการเชื่อมต่อคีย์นอกและการอัปเดตหรือลบข้อมูลหลัก การเชื่อมต่อขนาดใหญ่อาจยังคงชอบการสแกนมากกว่า
  • คอลัมน์ใน ORDER BY และ GROUP BY. ดัชนีสามารถให้ข้อมูลที่เรียงลำดับไว้ล่วงหน้า ซึ่งช่วยลดการดำเนินการเรียงลำดับที่มีราคาแพง
  • คอลัมน์ที่มีความหลากหลายสูง (High-cardinality) คอลัมน์ที่มีค่าที่แตกต่างกันจำนวนมาก (เช่น อีเมลหรือ UUID) จะได้รับประโยชน์มากกว่าคอลัมน์ที่มีค่าที่แตกต่างกันน้อย (เช่น บูลีน) is_active แฟล็ก)
  • ตารางขนาดใหญ่ ตารางที่มี 100 แถวแทบไม่จำเป็นต้องมีดัชนีเพิ่มเติม ตารางที่มี 10 ล้านแถวจำเป็นต้องมีอย่างแน่นอน

ตามกฎทั่วไป: เริ่มต้นโดยไม่มีดัชนีเพิ่มเติม วัดประสิทธิภาพของคิวรีด้วย EXPLAIN, จากนั้นเพิ่มดัชนีในจุดที่ข้อมูลบอกคุณ

ประเภทของดัชนี (Indexes)

ดัชนีคอลัมน์เดียว

รูปแบบที่ง่ายที่สุด สร้าง B-tree บนหนึ่งคอลัมน์ เหมาะสำหรับคิวรีที่กรองหรือเรียงลำดับบนคอลัมน์นั้นเพียงอย่างเดียว

-- PostgreSQL / MySQL / SQLite
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- SQL Server
CREATE NONCLUSTERED INDEX idx_orders_customer ON orders (customer_id);

ดัชนีแบบคอมโพสิต (หลายคอลัมน์)

ดัชนีบนคอลัมน์ตั้งแต่สองคอลัมน์ขึ้นไป ตัวกรองความเท่าเทียมบนคอลัมน์นำและช่วงบนคอลัมน์ถัดไปมักจะสร้างเส้นทางการเข้าถึง B-tree ที่มีประสิทธิภาพ เงื่อนไขอื่นๆ อาจยังคงใช้การสแกนหรือการข้ามการสแกน (skip scan) การสนับสนุนและต้นทุนจะแตกต่างกันไปตามเอนจินและเวอร์ชัน

CREATE INDEX idx_orders_status_date
    ON orders (status, ordered_at);

-- Leading equality plus a date range is a candidate for this index:
SELECT * FROM orders
WHERE status = 'shipped' AND ordered_at > '2025-01-01';

-- Filtering on the leading column may also use this index:
SELECT * FROM orders WHERE status = 'pending';

-- A date-only filter may scan or use a skip scan; inspect the plan:
SELECT * FROM orders WHERE ordered_at > '2025-01-01';

เลือกลำดับคอลัมน์จากตัวกรองความเท่าเทียม เงื่อนไขช่วง การเรียงลำดับ และความต้องการความครอบคลุม “เลือกสิ่งที่คัดเลือกมากที่สุดก่อน” ไม่ใช่กฎสากล PostgreSQL 18 สามารถใช้ B-tree skip scans สำหรับตัวกรองที่ไม่ใช่ตัวนำที่เหมาะสมได้

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

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

CREATE UNIQUE INDEX idx_users_email ON users (email);

-- Enforces uniqueness; constraint metadata and eligible FK targets vary by engine:
-- ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);

ดัชนีบางส่วน (กรองแล้ว)

ดัชนีเฉพาะแถวที่ตรงตาม WHERE เงื่อนไข สิ่งนี้จะช่วยให้ดัชนีมีขนาดเล็กและกำหนดเป้าหมายได้แม่นยำ

-- PostgreSQL partial index
CREATE INDEX idx_orders_active
    ON orders (customer_id)
    WHERE status NOT IN ('delivered', 'cancelled');

-- SQL Server: a supported simple comparison filter
CREATE INDEX idx_orders_pending
    ON orders (customer_id)
    WHERE status = 'pending';

ดัชนีบางส่วนหรือแบบกรองสามารถช่วยในชุดข้อมูลย่อยขนาดเล็กที่มีการคิวรีบ่อยได้ เงื่อนไขคิวรีต้องระบุตัวกรองนั้น ไวยากรณ์ตัวกรองของ SQL Server มีข้อจำกัดมากกว่าของ PostgreSQL อย่าคัดลอกเงื่อนไขตามอำเภอใจระหว่างกัน

ประเภทดัชนีอื่นๆ

ประเภทดัชนีดีที่สุดสำหรับการรองรับฐานข้อมูล
Hashการค้นหาความเท่ากันแบบตรงตัว (=)PostgreSQL, MySQL (เอนจิน Memory)
GINการค้นหาแบบ Full-text, JSONB, อาร์เรย์PostgreSQL
GiSTประเภทข้อมูลเชิงเรขาคณิตและช่วงPostgreSQL
BRINตารางขนาดใหญ่มากที่มีข้อมูลที่เรียงลำดับตามธรรมชาติPostgreSQL
Full-Textการค้นหาข้อความพร้อมการจัดอันดับความเกี่ยวข้องMySQL, SQL Server

ข้อผิดพลาดทั่วไปในการทำดัชนี

แม้แต่นักพัฒนาที่มีประสบการณ์ก็ยังตกหลุมพรางเหล่านี้ หลีกเลี่ยงสิ่งเหล่านี้เพื่อให้ฐานข้อมูลของคุณทำงานได้ดี

1. การทำดัชนีมากเกินไป

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

2. การทำดัชนีคอลัมน์ที่มีความหลากหลายต่ำ

การทำดัชนีบูลีน is_active คอลัมน์ที่มีเพียง TRUE และ FALSE สามารถมีประโยชน์เมื่อค่ามีความเบี่ยงเบนสูงหรือดัชนีครอบคลุมคิวรีที่แคบ ความเป็นคาร์ดินัลลิตี้ (cardinality) เพียงอย่างเดียวไม่ได้เป็นตัวตัดสินว่าดัชนีจะมีประโยชน์หรือไม่ หากคุณจำเป็นต้องกรองข้อมูลในคอลัมน์ดังกล่าว ให้รวมคอลัมน์เหล่านั้นไว้ในดัชนีแบบผสมที่มีคอลัมน์ที่มีความเป็นคาร์ดินัลลิตี้สูงกว่า หรือใช้ดัชนีแบบบางส่วน (partial index)

3. การเพิกเฉยต่อรูปแบบคิวรี

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

-- Use EXPLAIN to see if your index is being used
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 7 AND status = 'shipped';

-- Look for "Index Scan" or "Index Only Scan" in the output.
-- Bitmap Index Scan is another index access path. Seq Scan can be appropriate.

4. การลืมทำดัชนีคีย์นอก

ใน PostgreSQL คอลัมน์คีย์นอก (foreign-key) คือ ไม่ ถูกจัดทำดัชนีโดยอัตโนมัติ (ไม่เหมือนกับ InnoDB ของ MySQL ซึ่งสร้างดัชนีบนคีย์นอกโดยค่าเริ่มต้น) ดัชนีสามารถช่วยในการจอยน์แบบเลือกเฉพาะและการตรวจสอบความสัมพันธ์ แต่ตัวปรับแต่งประสิทธิภาพ (optimizer) อาจเลือกใช้การสแกนหรือการจอยน์แบบแฮช (hash join) ให้ตรวจสอบดัชนีที่มีอยู่ซึ่งคอลัมน์นำหน้าครอบคลุมคีย์นอกอยู่แล้วก่อนที่จะเพิ่มดัชนีใหม่

-- Consider these only when equivalent access paths do not exist
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_order_items_order_id ON order_items (order_id);
CREATE INDEX idx_order_items_product_id ON order_items (product_id);

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

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

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

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

PostgreSQL

-- Find unused indexes (0 scans since last stats reset)
SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

MySQL

-- MySQL: no row fetches recorded since these counters were reset
SELECT
    object_schema,
    object_name AS table_name,
    index_name,
    count_fetch AS row_fetches
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_fetch = 0
  AND object_schema = 'your_database';

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

ดัชนีในการออกแบบสคีมาและ ERD

ในขณะที่ ERD เน้นที่ตาราง คอลัมน์ และความสัมพันธ์เป็นหลัก ดัชนี (Indexes) ก็เป็นส่วนสำคัญของการออกแบบสคีมาโดยรวม เมื่อวางแผนฐานข้อมูลของคุณ ให้พิจารณาดัชนีควบคู่ไปกับคำจำกัดความของตารางของคุณ:

  • A คีย์หลัก (primary key) โดยปกติจะมีเส้นทางการเข้าถึงที่จัดการโดยเอนจิน SQLite INTEGER PRIMARY KEY จะใช้ rowid แทนการใช้ดัชนีเฉพาะ (unique index) แยกต่างหาก; ตรวจสอบเอนจินก่อนที่จะเพิ่มดัชนีซ้ำ
  • ตรวจสอบเส้นทางการเข้าถึงสำหรับ คีย์นอก. PostgreSQL ไม่สร้างดัชนีฝั่งลูกโดยอัตโนมัติ แต่ดัชนีแบบคอมโพสิตที่มีอยู่อาจครอบคลุมความสัมพันธ์นั้นอยู่แล้ว
  • คอลัมน์ที่มี ข้อจำกัด UNIQUE ได้รับดัชนีเฉพาะอัตโนมัติ
  • ดัชนีเพิ่มเติมสำหรับคอลัมน์ที่ถูกคิวรีบ่อยควรได้รับการบันทึกไว้ควบคู่ไปกับ DDL ของสคีมาของคุณ เพื่อไม่ให้ลืมในระหว่างการปรับใช้
-- Include indexes in your schema DDL files for reproducibility
CREATE TABLE products (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(200) NOT NULL,
    category_id INT REFERENCES categories (id),
    price       NUMERIC(10, 2) NOT NULL,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_products_category ON products (category_id);
CREATE INDEX idx_products_price ON products (price);
CREATE INDEX idx_products_created ON products (created_at DESC);

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

ข้อมูลอ้างอิงและการอ่านเพิ่มเติม

โพสต์เพิ่มเติม