Quay lại tất cả hướng dẫn
Hiệu năng··Đọc trong 8 phút

Chiến lược đánh chỉ mục cơ sở dữ liệu để tăng tốc truy vấn SQL

Tìm hiểu cách thức hoạt động của chỉ mục cơ sở dữ liệu, khi nào nên sử dụng chúng và các lỗi thường gặp. Bao gồm B-tree, chỉ mục tổng hợp và chỉ mục bao phủ với các ví dụ SQL.

Ví dụ về chỉ mục phức hợp bên cạnh tra cứu B-tree theo đường dẫn từ 40 qua 60 đến khóa 50
Trong hướng dẫn này
  1. Chỉ mục cơ sở dữ liệu là gì?
  2. Khi nào bạn nên sử dụng chỉ mục (index)?
  3. Các loại chỉ mục
  4. Các sai lầm phổ biến khi lập chỉ mục
  5. Giám sát việc sử dụng chỉ mục
  6. Chỉ mục trong Thiết kế lược đồ và ERD
  7. Tài liệu tham khảo và đọc thêm

Các ví dụ SQL sử dụng PostgreSQL trừ khi một phương ngữ khác được chỉ định. Các đoạn mã ngắn giả định rằng các bảng được tham chiếu đã tồn tại. Hãy xác thực DDL trong cơ sở dữ liệu mục tiêu của bạn trước khi áp dụng nó.

Chỉ mục cơ sở dữ liệu là gì?

Một chỉ mục là một cấu trúc dữ liệu được duy trì bởi công cụ cơ sở dữ liệu, cung cấp đường dẫn tra cứu nhanh đến các hàng trong một bảng. Đối với các bộ lọc có tính chọn lọc, một chỉ mục có thể giảm số lượng hàng hoặc trang cần đọc. Trình tối ưu hóa vẫn có thể chọn quét bảng đối với các bảng nhỏ hoặc các truy vấn trả về nhiều hàng. Hãy đo lường kế hoạch và thời gian thay vì giả định một tốc độ tăng nhanh cố định.

Cách thức hoạt động của chỉ mục B-Tree

Loại chỉ mục phổ biến nhất trên PostgreSQL, MySQL, SQL Server và SQLite là B-tree (cây cân bằng). Hãy nghĩ về nó giống như một cuốn danh bạ điện thoại: thay vì đọc từng tên từ A đến Z, bạn mở đến phần gần đúng, thu hẹp xuống trang phù hợp và dừng lại ở mục nhập đó. Một B-tree hoạt động tương tự:

  • Cây được tổ chức thành nút. Mỗi nút nội bộ chứa các khóa đã sắp xếp và các con trỏ đến các nút con.
  • Một tra cứu bắt đầu tại gốc, so sánh giá trị tìm kiếm với các khóa nút và đi theo con trỏ chính xác xuống qua nhánh các nút.
  • Nó đạt đến một lá chứa các mục nhập khớp. Tùy thuộc vào công cụ và chỉ mục, các mục nhập xác định các hàng, chứa một khóa phân cụm, hoặc giữ chính dữ liệu hàng.
  • Cây vẫn được cân bằng, nhưng độ sâu và I/O của nó phụ thuộc vào độ rộng khóa, kích thước trang, số lượng hàng, bộ nhớ đệm và liệu truy vấn có tìm nạp các trang bảng hay không.
-- 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]';

Khi nào bạn nên sử dụng chỉ mục (index)?

Chỉ mục không miễn phí. Chúng tiêu tốn dung lượng đĩa và thêm chi phí cho mỗi INSERT, UPDATE, và DELETE bởi vì công cụ phải giữ cho chỉ mục đồng bộ với dữ liệu bảng. Hãy sử dụng chúng một cách chiến lược:

  • Các cột trong WHERE các mệnh đề. Một chỉ mục phù hợp có thể giúp ích cho một bộ lọc chọn lọc thường xuyên; lợi ích của nó phụ thuộc vào phân phối dữ liệu và kế hoạch đã chọn.
  • Các cột trong JOIN các điều kiện. Chỉ mục có thể hỗ trợ các phép nối khóa ngoại và cập nhật hoặc xóa cha; một phép nối lớn vẫn có thể ưu tiên quét bảng.
  • Các cột trong ORDER BY và GROUP BY. Một chỉ mục có thể cung cấp dữ liệu đã được sắp xếp trước, loại bỏ thao tác sắp xếp tốn kém.
  • Các cột có độ phân tán cao (high-cardinality). Các cột có nhiều giá trị riêng biệt (như email hoặc UUID) mang lại lợi ích nhiều hơn các cột có ít giá trị riêng biệt (như boolean is_active cờ).
  • Các bảng lớn. Một bảng với 100 hàng hiếm khi cần thêm chỉ mục. Một bảng với 10 triệu hàng gần như chắc chắn cần.

Theo quy tắc chung: bắt đầu mà không cần thêm chỉ mục, đo lường hiệu suất truy vấn với EXPLAIN, sau đó thêm các chỉ mục ở nơi dữ liệu chỉ dẫn cho bạn.

Các loại chỉ mục

Chỉ mục một cột

Dạng đơn giản nhất. Tạo một B-tree trên một cột. Lý tưởng cho các truy vấn lọc hoặc sắp xếp chỉ trên cột đó.

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

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

Chỉ mục tổng hợp (nhiều cột)

Một chỉ mục trên hai hoặc nhiều cột. Các bộ lọc đẳng thức trên các cột đứng đầu và một phạm vi trên cột tiếp theo thường tạo thành một đường dẫn truy cập B-tree hiệu quả. Các điều kiện khác vẫn có thể sử dụng quét hoặc bỏ qua quét; hỗ trợ và chi phí thay đổi tùy theo công cụ và phiên bản.

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';

Chọn thứ tự cột từ các bộ lọc đẳng thức, điều kiện phạm vi, sắp xếp và nhu cầu bao phủ. “Chọn lọc nhất trước” không phải là quy tắc phổ quát. PostgreSQL 18 có thể sử dụng quét bỏ qua B-tree cho các bộ lọc không dẫn đầu phù hợp.

Chỉ mục duy nhất

Thực thi ràng buộc tính duy nhất đồng thời cung cấp hiệu suất tra cứu dựa trên chỉ mục. Hầu hết các cơ sở dữ liệu tự động tạo một chỉ mục duy nhất khi bạn xác định một UNIQUE ràng buộc hoặc 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);

Chỉ mục một phần (đã lọc)

Chỉ lập chỉ mục các hàng thỏa mãn một WHERE điều kiện. Điều này giữ cho chỉ mục nhỏ và được nhắm mục tiêu cao.

-- 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';

Một chỉ mục một phần hoặc được lọc có thể giúp ích cho một tập hợp con nhỏ, thường xuyên được truy vấn. Vị ngữ truy vấn phải bao hàm bộ lọc. Cú pháp bộ lọc của SQL Server bị hạn chế hơn so với PostgreSQL; đừng sao chép các vị ngữ tùy ý giữa chúng.

Các loại chỉ mục khác

Kiểu chỉ mụcTốt nhất choHỗ trợ cơ sở dữ liệu
HashTra cứu bằng đẳng thức chính xác (=)PostgreSQL, MySQL (công cụ Memory)
GINTìm kiếm toàn văn, JSONB, mảngPostgreSQL
GiSTCác kiểu dữ liệu hình học và phạm viPostgreSQL
BRINCác bảng rất lớn với dữ liệu được sắp xếp tự nhiênPostgreSQL
Toàn vănTìm kiếm văn bản với xếp hạng mức độ liên quanMySQL, SQL Server

Các sai lầm phổ biến khi lập chỉ mục

Ngay cả những nhà phát triển có kinh nghiệm cũng rơi vào những cái bẫy này. Hãy tránh chúng để giữ cho cơ sở dữ liệu của bạn hoạt động tốt.

1. Lập chỉ mục quá mức

Việc thêm một chỉ mục trên mọi cột “đề phòng” sẽ lãng phí dung lượng đĩa và làm chậm quá trình ghi. Mỗi chỉ mục phải được cập nhật trên mọi thao tác chèn, cập nhật và xóa. Một bảng với 15 chỉ mục sẽ có thông lượng ghi chậm hơn đáng kể so với bảng có 3 chỉ mục được chọn kỹ lưỡng.

2. Lập chỉ mục các cột có độ phân giải thấp

Lập chỉ mục một kiểu boolean is_active cột chỉ chứa TRUE và FALSE có thể hữu ích khi các giá trị bị lệch cao hoặc chỉ mục bao gồm một truy vấn hẹp. Chỉ riêng tính chất tập hợp (cardinality) không quyết định liệu một chỉ mục có hữu ích hay không. Nếu bạn phải lọc trên các cột như vậy, hãy kết hợp chúng trong một chỉ mục tổng hợp với một cột có tính chất tập hợp cao hơn, hoặc sử dụng một chỉ mục một phần.

3. Bỏ qua các mẫu truy vấn

Một chỉ mục chỉ hữu ích nếu các truy vấn thực tế của bạn có thể tận dụng nó. Luôn kiểm tra các truy vấn chậm trong ứng dụng của bạn đầu tiên, sau đó thiết kế các chỉ mục nhắm mục tiêu vào các mẫu truy cập cụ thể đó.

-- 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. Quên lập chỉ mục khóa ngoại

Trong PostgreSQL, các cột khóa ngoại là không được đánh chỉ mục tự động (không giống như InnoDB của MySQL, vốn tạo chỉ mục trên các khóa ngoại theo mặc định). Một chỉ mục có thể hỗ trợ các phép nối có chọn lọc và kiểm tra tham chiếu, nhưng trình tối ưu hóa có thể chọn quét hoặc nối băm. Hãy kiểm tra xem có chỉ mục hiện có nào mà các cột dẫn đầu đã bao gồm khóa ngoại hay không trước khi thêm một chỉ mục khác.

-- 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. Không bao giờ xem xét các chỉ mục không sử dụng

Theo thời gian, mã ứng dụng thay đổi và các chỉ mục cũ có thể không còn phục vụ bất kỳ truy vấn nào. Các chỉ mục không sử dụng vẫn tiêu tốn dung lượng lưu trữ và làm chậm quá trình ghi mà không mang lại lợi ích đọc nào.

Giám sát việc sử dụng chỉ mục

Các cơ sở dữ liệu hiện đại cung cấp các khung nhìn hệ thống báo cáo tần suất sử dụng của từng chỉ mục. Việc xem xét thường xuyên các báo cáo này giúp bạn loại bỏ các chỉ mục không sử dụng và phát hiện các chỉ mục còn thiếu.

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';

Hãy coi số lần đọc bằng không là ứng viên để xem xét, không phải là sự cho phép để xóa chỉ mục. Kiểm tra thời điểm các bộ đếm được đặt lại, liệu công cụ đo lường có được bật hay không, và liệu các truy vấn theo mùa, ràng buộc hoặc kiểm tra khóa ngoại có cần nó không. Kiểm tra việc đề xuất xóa với một khối lượng công việc đại diện.

Chỉ mục trong Thiết kế lược đồ và ERD

Mặc dù ERD chủ yếu tập trung vào bảng, cột và mối quan hệ, nhưng chỉ mục là một phần quan trọng trong thiết kế lược đồ tổng thể. Khi lập kế hoạch cơ sở dữ liệu, hãy xem xét các chỉ mục cùng với định nghĩa bảng của bạn:

  • A khóa chính thông thường có một đường dẫn truy cập do công cụ quản lý. SQLite INTEGER PRIMARY KEY sử dụng rowid thay vì một chỉ mục duy nhất riêng biệt; hãy kiểm tra công cụ trước khi thêm một chỉ mục trùng lặp.
  • Xem lại các đường dẫn truy cập cho các khóa ngoại. PostgreSQL không tự động tạo chỉ mục ở phía bảng con, nhưng một chỉ mục tổng hợp hiện có có thể đã bao phủ mối quan hệ này.
  • Các cột với Các ràng buộc UNIQUE nhận một chỉ mục duy nhất tự động.
  • Các chỉ mục bổ sung cho các cột thường xuyên được truy vấn nên được ghi lại cùng với DDL lược đồ của bạn để chúng không bị lãng quên trong quá trình triển khai.
-- 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);

Trực quan hóa toàn bộ lược đồ của bạn—các bảng, mối quan hệ và các cột bạn dự định lập chỉ mục—bằng cách dán DDL của bạn vào Trình chỉnh sửa Erd Go. Việc nhìn thấy cấu trúc một cách trực quan thường làm lộ ra các cột cần lập chỉ mục (như các khóa ngoại thường xuyên được kết nối) mà bạn có thể bỏ qua khi đọc SQL thô.

Tài liệu tham khảo và đọc thêm

Các bài viết khác