Trong bài viết này chúng ta sẽ cùng nhau bàn về các nguyên tắc sử dụng chỉ mục (index), tại sao bạn phải sử dụng chúng, khi nào, như thế nào như một lập trình viên chuyên nghiệp. Thực ra sử dụng index, không phải là điều gì quá sức khó khăn về mặt kỹ thuật, chúng ta hoàn toàn có thể có thể học về chúng bằng cách đọc trong các tài liệu chính thức hoặc các bài chia sẻ kiến thức.

Trong ví dụ này chúng ta sẽ áp dụng trên MySQL, bởi MySQL là một trong những hệ quản trị cơ sở dữ liệu được sử dụng phổ biến nhất.

Tại sao dùng index?

Để dễ hình dùng chúng ta cùng đến với ví dụ về một thư viện sách. Khi nhận được một yêu cầu tìm sách “Harry Potter”, thủ thư sẽ không tìm tất cả các cuốn sách có trong thư viện. Thay vào đó, anh ta sẽ đi đến các giá sách cho các tựa sách bắt đầu bằng ký tự “H” sau đó tìm đến các cuốn sách bắt đầu bằng “Ha” tiếp tục đến “Har” cho đến khi tìm được cuốn sách.

Tuy ví dụ này có vẻ đơn giản, nhưng nó mô tả được cách hoạt động chính của index. Cơ sở dữ liệu khi thực hiện tìm kiếm trên 3 dòng dữ liệu là chuyện rất đơn giản, nhưng nếu dữ liệu trong lưu trữ hiện tại lên đến 3 triệu dòng thì sao?  Index chính là một phương pháp tốt để giải quyết vấn đề tìm kiếm này.

Khi nào dùng Index?

Index thường xuyên được dùng trên các trường dữ liệu trong lệnh WHERE hoặc ORDER BY. Ví dụ:

SELECT * FROM products 
WHERE category_id = 7 and status= 'subscribe me pls' 
ORDER BY created_at

Tong ví dụ này, trường dữ liệu category_id, status và created_at có thể đặt index.

Làm thế nào để sử dụng index?

Từng trường hợp tìm kiếm sẽ áp dụng những cách dùng index khác nhau.

Ví dụ về truy vấn ở trên. DBMS sẽ tìm kiếm các sản phẩm theo các trường category_id và status, sau đó sắp xếp chúng theo trường create_at, đây là một trong những trường hợp tìm kiếm lớn và khá phức tạp. Vì vậy chúng ta phải tạo chỉ mục cho 3 trường.

Tạo index trong MySQL:

ALTER TABLE products
ADD INDEX category_idx (category_id, status, created_at)
USING BTREE;

Gợi ý đặt tên cho các index của bạn theo cú pháp trường dữ liệu _idx

Chúng ta thường sẽ có các câu truy vấn trên một bảng. Ví dụ trên là tìm kiếm theo danh mục và trạng thái, giả sử chúng ta có một tìm kiếm bổ sung khác:

Đây là một trường hợp tìm kiếm khác, vì thế yêu cầu thêm một index khác, bởi chúng ta phải tìm kiếm theo trường chưa được lập chỉ mục. Nếu bạn tìm kiếm theo một trường dữ liệu nào đó, mà trường dữ liệu này chưa được lập chỉ mục - hãy cân nhắc và lập chỉ mục cho nó!

Chúng ta có thể thêm trường create_at để sắp xếp nếu muốn.

Combined Index

Sử dụng một index đơn giản cho một trường là một điều bình thường, sử dụng combined index như các ví dụ ở trên - sử dụng chúng trong các trường hợp tìm kiếm phức tạp khác cũng như dùng trong sắp xếp thứ tự của các trường.

Truy vấn này sẽ hoạt động tốt hơn, bởi vì category_id và status là các trường đã được đánh index, trong đó category_id là trường đầu tiên và status là trường tiếp theo (danh sách hiện tại là: category_id, status, created_at). MySQL engine có thể tìm mọi thứ cần thiết theo category_id, sau đó sắp xếp theo status từ danh sách các index.

Chúng ta có thể hiểu rằng, để đi đến bản ghi dữ liệu chúng ta sẽ đi qua chỉ mục theo thứ tự là: trường đầu tiên -> trường thứ hai -> trường thứ ba ->… Cùng xem truy vấn tiếp theo, chúng ta sẽ thấy rằng truy vấn này bỏ qua trường thứ hai và tìm kiếm bằng trường đầu tiên và sắp xếp bằng trường thứ ba.

SELECT * FROM products
WHERE category_id = 7 ORDER BY created_at;

Index sẽ chỉ hoạt động một phần: nhanh tại WHERE và chậm tại ORDER BY, bởi vì chúng ta có category_id làm trường đầu tiên trong danh sách index, nhưng chúng tôi không có bất kỳ create_at nào sau category_id. Truy vấn gốc ban đầu với cả 3 trường sẽ hoạt động nhanh hơn nhiều so với truy vấn này, mặc dù chúng ta thực hiện tìm kiếm trên ít hơn 1 trường. Vì thế hãy cân nhắc khi chọn thứ tự cho các index.

EXPLAIN statement

Nếu chúng ta muốn kiểm tra các hoạt động của các index, muốn biết có nên dùng index hay không, trường dữ liệu nào nên dùng index hay kiểm tra tại sao truy vấn lại lâu, thì có thể sử dụng lệnh EXPLAIN.

EXPLAIN là một trong những tính năng để làm việc với index. Đơn giản là bạn chỉ cần đặt từ khóa EXPLAIN ngay trước câu lệnh SELECT.

EXPLAIN SELECT * FROM products
WHERE category_id = 7 and status = 'active'
ORDER BY created_at;

Kểm tra các trường type, could_keys, key, ref, Extra. Mục đích để xem liệu có nên sử dụng index cho trường category_idx hay không.

Kết quả khi không dùng index

Kết quả khi dùng index

Using where; Using filesort

Những chú thích này có nghĩa là MySQL đã sử dụng tệp tạm thời để sắp xếp, những từ này đồng nghĩa với rất chậm. Ngoài ra bạn có thể type = All, có nghĩa là duyệt qua tất cả các bản ghi trong bảng, thời gian truy vấn sẽ tăng theo kích thước bảng (số lượng bản ghi).

B-Tree vs Hash:

• B-Tree, nếu sử dụng câu lệnh > hoặc <hoặc ≥ hoặc ≤ hoặc LIKE bổ sung cho =, với thao tác trên phép = B-Tree hoạt động chậm hơn hash

• Hash nếu chỉ sử dụng = hoặc !=, hoạt động nhanh hơn so với B-Tree

Có đánh đổi gì cho việc tạo các index này không? 

Tất nhiên, việc đặt index để tăng tốc truy vấn thì chúng ta cần phải đánh đổi những thứ khác:

• Giảm tốc độ trong khi INSERT / UPDATE / DELETE  

• Tăng kích thước cơ sở dữ liệu, đôi khi lượng không gian lưu trữ index có thể nhiều hơn cả dữ liệu trong bảng.