Thanaoscar

Bài 3: Sử dụng Index hiệu quả

Thana Oscar's photo
Thana Oscar
·

6 min read

Index là một trong những công cụ mạnh mẽ nhất để tối ưu hóa hiệu suất của các truy vấn SQL. Khi được sử dụng đúng cách, index có thể giảm đáng kể thời gian thực thi của truy vấn và cải thiện hiệu suất tổng thể của hệ thống. Tuy nhiên, việc sử dụng index không đúng cách có thể dẫn đến các vấn đề về hiệu suất và tài nguyên. Bài viết này sẽ đi sâu vào khái niệm index, các loại index phổ biến, và cách sử dụng chúng một cách hiệu quả.

3.1. Index là gì và tại sao cần sử dụng?

3.1.1. Cơ chế hoạt động của index

  • Index là một cấu trúc dữ liệu giúp tăng tốc độ truy vấn bằng cách cho phép database engine tìm kiếm dữ liệu nhanh hơn mà không cần quét toàn bộ bảng (table scan).

  • Cơ chế hoạt động: Index hoạt động giống như một bảng tra cứu (lookup table). Khi bạn tạo index trên một cột, database engine sẽ lưu trữ các giá trị của cột đó cùng với con trỏ (pointer) đến các hàng tương ứng trong bảng. Khi truy vấn được thực thi, database engine sẽ sử dụng index để nhanh chóng định vị các hàng phù hợp với điều kiện truy vấn.

  • Ví dụ: Nếu bạn có index trên cột employee_id, truy vấn SELECT * FROM employees WHERE employee_id = 123; sẽ sử dụng index để tìm kiếm nhanh chóng thay vì quét toàn bộ bảng.

3.1.2. Lợi ích của index

  • Tăng tốc độ truy vấn: Index giúp giảm thời gian tìm kiếm dữ liệu, đặc biệt là với các bảng lớn.

  • Giảm tải cho hệ thống: Khi truy vấn sử dụng index, database engine cần ít tài nguyên hơn (CPU, I/O) so với việc quét toàn bộ bảng.

  • Hỗ trợ các phép toán phức tạp: Index có thể được sử dụng để tối ưu hóa các phép toán như JOIN, ORDER BY, và GROUP BY.

3.2. Các loại index phổ biến

Có nhiều loại index khác nhau, mỗi loại phù hợp với các tình huống cụ thể. Dưới đây là các loại index phổ biến nhất:

3.2.1. Single-column index

  • Định nghĩa: Index được tạo trên một cột duy nhất.

  • Ưu điểm: Đơn giản và hiệu quả cho các truy vấn có điều kiện trên một cột.

  • Ví dụ: CREATE INDEX idx_employee_id ON employees(employee_id);

3.2.2. Composite index

  • Định nghĩa: Index được tạo trên nhiều cột.

  • Ưu điểm: Hiệu quả cho các truy vấn có điều kiện trên nhiều cột.

  • Ví dụ: CREATE INDEX idx_employee_name_department ON employees(last_name, department);

3.2.3. Unique index

  • Định nghĩa: Index đảm bảo rằng các giá trị trong cột là duy nhất.

  • Ưu điểm: Ngăn chặn việc chèn các giá trị trùng lặp vào cột.

  • Ví dụ: CREATE UNIQUE INDEX idx_employee_email ON employees(email);

3.2.4. Full-text index

  • Định nghĩa: Index được sử dụng để tìm kiếm văn bản (text search) trong các cột chứa dữ liệu văn bản lớn.

  • Ưu điểm: Hỗ trợ tìm kiếm nhanh chóng trong các cột văn bản.

  • Ví dụ: CREATE FULLTEXT INDEX idx_employee_notes ON employees(notes);

3.2.5. Partial Index (PostgreSQL) / Filtered Index (SQL Server)

  • Định nghĩa: Index chỉ được tạo trên một phần của bảng, dựa trên một điều kiện cụ thể.

  • Ưu điểm: Tiết kiệm không gian lưu trữ và cải thiện hiệu suất cho các truy vấn cụ thể.

  • Ví dụ: CREATE INDEX idx_employee_active ON employees(employee_id) WHERE status = 'Active';

3.2.6. Function-based Index (Oracle)

  • Định nghĩa: Index được tạo dựa trên kết quả của một hàm áp dụng lên cột.

  • Ưu điểm: Hỗ trợ tối ưu hóa các truy vấn sử dụng hàm trong điều kiện.

  • Ví dụ: CREATE INDEX idx_employee_upper_name ON employees(UPPER(last_name));

3.3. Khi nào nên và không nên sử dụng index

3.3.1. Trường hợp cần index

  • Cột thường xuyên được sử dụng trong điều kiện WHERE: Nếu một cột thường xuyên xuất hiện trong điều kiện WHERE của các truy vấn, việc tạo index trên cột đó sẽ cải thiện hiệu suất.

  • Cột được sử dụng trong JOIN: Index trên các cột được sử dụng trong phép JOIN sẽ giúp tăng tốc độ thực thi.

  • Cột được sử dụng trong ORDER BY hoặc GROUP BY: Index trên các cột này sẽ giúp tối ưu hóa các phép toán sắp xếp và nhóm dữ liệu.

3.3.2. Trường hợp index gây chậm

  • Bảng nhỏ: Đối với các bảng có số lượng bản ghi nhỏ, việc sử dụng index có thể không mang lại lợi ích đáng kể và thậm chí có thể làm chậm truy vấn do chi phí duy trì index.

  • Cột có độ chọn lọc thấp: Nếu một cột có ít giá trị duy nhất (ví dụ: cột giới tính chỉ có hai giá trị "Nam" và "Nữ"), index trên cột này sẽ không hiệu quả.

  • Cập nhật dữ liệu thường xuyên: Khi dữ liệu trong bảng được cập nhật thường xuyên, việc duy trì index có thể gây tốn kém tài nguyên và làm chậm các thao tác INSERT, UPDATE, DELETE.

3.3.3. Đo lường hiệu quả index: Index Selectivity, Index Cardinality

  • Index Selectivity: Là tỷ lệ giữa số lượng giá trị duy nhất (unique values) và tổng số bản ghi trong bảng. Index có độ chọn lọc cao (high selectivity) thường hiệu quả hơn.

    • Công thức: Selectivity = (Number of Unique Values) / (Total Number of Rows)

    • Ví dụ: Nếu một bảng có 1000 bản ghi và cột employee_id có 1000 giá trị duy nhất, selectivity của index trên cột này là 1 (cao nhất).

  • Index Cardinality: Là số lượng giá trị duy nhất trong index. Cardinality càng cao, index càng hiệu quả.

    • Ví dụ: Một index trên cột email có cardinality cao vì mỗi email thường là duy nhất.

Kết luận

Index là một công cụ mạnh mẽ để tối ưu hóa hiệu suất của các truy vấn SQL, nhưng việc sử dụng index cần được cân nhắc kỹ lưỡng. Bằng cách hiểu rõ các loại index và khi nào nên sử dụng chúng, bạn có thể cải thiện đáng kể hiệu suất của hệ thống cơ sở dữ liệu. Trong các bài tiếp theo, chúng ta sẽ đi sâu vào các kỹ thuật tối ưu hóa cụ thể, chẳng hạn như tối ưu câu lệnh SELECT và JOIN.

Từ vựng chuyên môn (Glossary)

  • Index: Chỉ mục.

  • Single-column Index: Chỉ mục đơn cột.

  • Composite Index: Chỉ mục kết hợp.

  • Unique Index: Chỉ mục duy nhất.

  • Full-text Index: Chỉ mục toàn văn.

  • Partial Index: Chỉ mục một phần.

  • Filtered Index: Chỉ mục lọc.

  • Function-based Index: Chỉ mục dựa trên hàm.

  • Index Selectivity: Độ chọn lọc của chỉ mục.

  • Index Cardinality: Số lượng giá trị duy nhất trong chỉ mục.

  • Table Scan: Quét toàn bộ bảng.

  • Index Scan: Quét chỉ mục.

sql optimize