Chỉ mục (Index) là bảng tra cứu đặc biệt mà Database Search Engine có thể sử dụng để tăng nhanh thời gian và hiệu suất thu thập dữ liệu. Hiểu đơn giản, một chỉ mục là một con trỏ tới dữ liệu trong một bảng. Một chỉ mục trong một Database là tương tự như một chỉ mục trong Mục lục của cuốn sách.

Ví dụ, nếu bạn muốn tham chiếu tất cả các trang trong một cuốn sách về một chủ đề nào đó, đầu tiên bạn nghĩ ngay đến mục lục của nó, mà liệt kê tất cả các chương, chủ đề theo thứ tự và sau đó được tham chiếu tới một hoặc nhiều trang cụ thể.

Một chỉ mục giúp tăng tốc các truy vấn SELECT và các mệnh đề WHERE, nhưng nó làm chậm việc dữ liệu nhập vào, với các lệnh UPDATE và INSERT. Các chỉ mục có thể được tạo hoặc xóa mà không ảnh hưởng tới dữ liệu.

Tạo một chỉ mục bởi sử dụng lệnh CREATE INDEX, mà cho phép bạn đặt tên cho chỉ mục, xác định bảng và cột hoặc các cột nào để lập chỉ mục, và để chỉ dẫn chỉ mục là theo thứ tự tăng dần hoặc giảm dần.

Các chỉ mục cũng có thể là duy nhất, tương tự như ràng buộc UNIQUE, trong đó chỉ mục ngăn ngừa các bản sao của các bản ghi trong cột hoặc việc tổ hợp của các cột có chỉ mục.

Lệnh CREATE INDEX trong SQL

Cú pháp cơ bản của lệnh CREATE INDEX trong SQL là như sau:

CREATE INDEX ten_chi_muc ON ten_bang;

Single-Column Index trong SQL

Chỉ mục cho một cột đơn là một chỉ mục được tạo dựa trên chỉ một cột trong bảng. Cú pháp cơ bản là:

CREATE INDEX ten_chi_muc
ON ten_bang (ten_cot);

Unique index trong SQL

Unique index được sử dụng không chỉ để tăng hiệu suất, mà còn cho mục đích toàn vẹn dữ liệu. Một Unique index không cho phép bất kỳ bản sao giá trị nào được chèn vào trong bảng. Cú pháp cơ bản là:

CREATE UNIQUE INDEX ten_chi_muc
on ten_bang (ten_cot);

Composite Index trong SQL

Composite Index là một chỉ mục cho hai hoặc nhiều cột trong một bảng. Cú pháp cơ bản của nó như sau:

CREATE INDEX ten_chi_muc
on ten_bang (cot1, cot2);

Việc tạo một Single-column index hoặc một Composite index tùy thuộc vào sự cân nhắc của bạn về các cột bạn có thể sử dụng thường xuyên trong mệnh đề WHERE của một truy vấn như là các điều kiện lọc.

Nếu chỉ có một cột được sử dụng, thì lựa chọn tốt nhất là Single-column index. Nếu có hai hoặc nhiều cột được sử dụng thường xuyên trong mệnh đề WHERE như là các bộ lọc, thì dạng chỉ mục Composite index là lựa chọn tối ưu.

Implicit Index trong SQL

Implicit Index (có thể hiểu là chỉ mục ngầm định) là chỉ mục mà được tạo tự động bởi Database Server khi một đối tượng được tạo. Các chỉ mục được tạo tự động cho các ràng buộc Primary key và các ràng buộc Unique. 2 dạng này sẽ được đánh là sử dụng dạng Btree.

Lệnh DROP INDEX trong SQL

Một chỉ mục có thể bị xóa bởi sử dụng lệnh DROP INDEX trong SQL. Bạn nên cẩn thận trong khi xóa một chỉ mục, bởi vì khi đó hiệu suất có thể chậm hơn hoặc không được cải thiện.

Cú pháp cơ bản của lệnh DROP INDEX là như sau:

DROP INDEX ten_chi_muc;

Bạn có thể tìm hiểu về Ràng buộc INDEX trong SQL để theo dõi các ví dụ về chỉ mục.

Khi nào nên tránh sử dụng chỉ mục trong SQL?

Mặc dù các chỉ mục nhằm mục đích để nâng cao hiệu suất của Database, nhưng đôi khi, bạn nên tránh dùng chúng. Dưới đây là một số điểm bạn cần xem xét để quyết định có nên sử dụng chỉ mục hay không:

• Các chỉ mục không nên được sử dụng trong các bảng nhỏ.

• Bảng mà thường xuyên có các hoạt động update, insert.

• Các chỉ mục không nên được sử dụng trên các cột mà chứa một số lượng lớn giá trị NULL.

• Không nên dùng chỉ mục trên các cột mà thường xuyên bị sửa đổi.

Các loại Index

Trong MySQL, có bốn loại chỉ mục (index) chính dựa trên các cấu trúc dữ liệu và thuật toán khác nhau: BTREE, HASH, RTREE, và FULLTEXT. Dưới đây là mô tả chi tiết về từng loại chỉ mục:

1. BTREE Index

• Mô tả: BTREE là loại chỉ mục mặc định trong MySQL và được sử dụng rộng rãi trong các bảng InnoDB và MyISAM. BTREE (Balanced Tree) là một cấu trúc cây cân bằng, cho phép tìm kiếm, chèn, xóa và duyệt dữ liệu một cách hiệu quả.
• Đặc điểm:
  • Thích hợp cho các truy vấn tìm kiếm, sắp xếp và phạm vi (range queries).
  • Hiệu quả cho các phép toán =, >, >=, <, <=, và BETWEEN.
• Sử dụng:

  CREATE TABLE employees (
      id INT AUTO_INCREMENT,
      name VARCHAR(100),
      email VARCHAR(100),
      PRIMARY KEY (id),
      INDEX name_index (name)
  ) ENGINE=InnoDB;

2. HASH Index

• Mô tả: HASH index sử dụng hàm băm để ánh xạ các giá trị khóa đến các vị trí chỉ mục. Loại chỉ mục này chỉ có sẵn trong các bảng sử dụng công cụ lưu trữ Memory (HEAP).
• Đặc điểm:
  • Tối ưu cho các phép toán so sánh bằng (=).
  • Không hỗ trợ các truy vấn phạm vi (range queries).
  • Thích hợp cho các bảng nhỏ hoặc các bảng thường xuyên truy cập bằng khóa chính xác.
• Sử dụng:

  CREATE TABLE employees (
      id INT AUTO_INCREMENT,
      name VARCHAR(100),
      email VARCHAR(100),
      PRIMARY KEY (id),
      INDEX name_index (name) USING HASH
  ) ENGINE=Memory;

3. RTREE Index

• Mô tả: RTREE là loại chỉ mục đặc biệt được sử dụng cho dữ liệu không gian (spatial data) trong các bảng MyISAM. RTREE (R-Tree) là cấu trúc cây được thiết kế để hỗ trợ các truy vấn không gian.
• Đặc điểm:
  • Tối ưu cho các phép toán không gian như giao nhau (intersect), chứa (contains), và lân cận (proximity).
  • Thích hợp cho các cột kiểu không gian như GEOMETRY, POINT, LINESTRING, và POLYGON.
• Sử dụng:

  CREATE TABLE locations (
      id INT AUTO_INCREMENT,
      name VARCHAR(100),
      location GEOMETRY NOT NULL,
      PRIMARY KEY (id),
      SPATIAL INDEX(location)
  ) ENGINE=MyISAM;

4. FULLTEXT Index

• Mô tả: FULLTEXT index được sử dụng để tìm kiếm văn bản toàn văn (full-text search) trong các cột văn bản như CHAR, VARCHAR, hoặc TEXT. Chỉ có sẵn trong các bảng sử dụng các công cụ lưu trữ như InnoDB và MyISAM.
• Đặc điểm:
  • Tối ưu cho việc tìm kiếm văn bản.
  • Hỗ trợ các chức năng như MATCH và AGAINST để tìm kiếm văn bản.
  • Tìm kiếm dựa trên các từ và cụm từ trong văn bản.
• Sử dụng:

  CREATE TABLE articles (
      id INT AUTO_INCREMENT,
      title VARCHAR(200),
      body TEXT,
      PRIMARY KEY (id),
      FULLTEXT (title, body)
  ) ENGINE=InnoDB;
  
  -- Tìm kiếm văn bản
  SELECT * FROM articles
  WHERE MATCH (title, body) AGAINST ('search term');

Tóm tắt

• BTREE: Loại chỉ mục mặc định, hiệu quả cho các phép toán tìm kiếm, sắp xếp và phạm vi.
• HASH: Chỉ mục băm, tối ưu cho các phép toán so sánh bằng, không hỗ trợ truy vấn phạm vi.
• RTREE: Chỉ mục không gian, tối ưu cho các truy vấn không gian, chỉ có trong MyISAM.
• FULLTEXT: Chỉ mục tìm kiếm văn bản toàn văn, tối ưu cho việc tìm kiếm văn bản, có trong InnoDB và MyISAM.

Việc chọn loại chỉ mục phù hợp phụ thuộc vào loại dữ liệu và kiểu truy vấn mà bạn cần tối ưu hóa trong ứng dụng của mình.