Giới thiệu về Index trong SQL

Giới thiệu về Index trong SQL

1. Index là gì ?

Nhiều website, ứng dụng sau một thời gian hoạt động thì tốc độ truy cập giảm xuống rất thấp. Nguyên nhân rất có thể đến từ Database của các bạn. Khi đó, dữ liệu sau một thời gian dài đã được lưu trữ với khối lượng lớn dẫn đến việc các câu truy vấn bị chậm gây ảnh hưởng đến hoạt động của website.

Vậy có cách nào để khắc phục vấn đề này? Đó chính là sử dụng index cho database của bạn. Nói dễ hiểu, index giống như mục lục trong một cuốn sách. Bạn không phải dò tìm từng trang đến vị trí muốn đọc, bạn chỉ cần tra cứu mục lục để biết trang chứa nội dung mình mong muốn ở đâu rồi nhanh chóng đến trang đó.

Index trong database là một bảng đặc biệt như là các con trỏ tới các hàng của bảng dữ liệu, giúp các câu truy vấn SELECT nhanh chóng xác định được các hàng thỏa mãn điều kiện trong mệnh đề WHERE, cũng như trong mệnh đề ORDER BY, GROUP BY.

Mặc dù ta có thể tạo index cho tất cả các cột của bảng để tăng tốc độ truy vấn câu SELECT tuy nhiên sẽ gây mất thời gian cho database xác định sử dụng index nào để truy vấn. Nó tương tự như việc nếu ta viết mục lục cho một cuốn sách quá chi tiết cụ thể thì ngay việc đọc nội dung mục lục để tìm ra trang cần đọc cũng rất mất công. Vì vậy, cần cân nhắc cẩn thận cần đánh index cho cột nào nhé.

2. Các loại index trong MYSQL

Mysql cung cấp các loại index sau:

• BTREE index: kiểu index thường gặp nhất trong MySQL.
• RTREE index - spatial index: chỉ có trong Storage Engine MyISAM, thường sử dụng trong các hệ thống GIS - hệ thống 
• HASH index:  có trong Storage Engine MEMMORY, NDB.
• FULLTEXT index - có trong Storage Engine MyISAM, Innodb.

Trong  các loại index nêu trên, thường sử dụng nhất là BTREE index và HASH index. Do đó, chúng ta tập chung làm rõ về 2 loại index này.

a. BTREE index

BTREE index có các đặc trưng sau:

• BTREE tổ chức dữ liệu dạng cây cân bằng, bao gồm root, leaf. Giá trị của các node được tổ chức tăng dần từ trái qua phải. Khi truy vấn dữ liệu thì việc tìm kiếm trong BTREE là 1 quá trình đệ quy, bắt đầu từ root và tìm kiếm tới branch và leaf, đến khi tìm được tất cả dữ liệu - thỏa mãn với điều kiện truy vấn thì mới dùng lại.
• BTREE index được sử dụng cho những column trong bảng cần tìm kiếm trong một khoảng, thường là với các toán tử =, >, >=, <, <=, BETWEEN và LIKE.
• Dữ liệu trong column càng phân biệt (high cardinality) thì index càng tốt. Có thể sử dụng để tối tư hóa toán tử ORDER BY.
• Độ phức tạp thuật toán O(log n)

Tổ chức dữ liệu của BTREE index

b. HASH index

HASH index có các đặc trưng sau: 

• Dữ liệu của HASH index được tổ chức dưới dạng key-value.
• Chỉ nên sử dụng cho các toán tử =, <>. Không nên sử dụng cho các toán tử trong một khoảng giá trị.
• Không thể sử dụng để tối ưu hóa toán tử ORDER BY.
• Toàn bộ giá trị của key đều được sử dụng để tìm kiếm, không giống BTREE index có thể chỉ sử dụng một phần giá trị của node để tìm kiếm.
• Độ phức tạp thuật toán O(1). Tốc độ của HASH index nhanh hơn so với BTREE.

Tổ chức dữ liệu của HASH index

3. Cách tạo index

Cấu trúc câu lệnh tạo index:


CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,...) [index_option] [algorithm_option | lock_option] ... index_col_name: col_name [(length)] [ASC | DESC] index_option: KEY_BLOCK_SIZE [=] value | index_type | WITH PARSER parser_name | COMMENT 'string' index_type: USING {BTREE | HASH} algorithm_option: ALGORITHM [=] {DEFAULT|INPLACE|COPY} lock_option: LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}


Trong đó:

• Khi UNIQUE được sử dụng thì trong bảng không tồn tại hai dòng có cùng giá trị trong cột được đánh index. 
• FULLTEXT, SPATIAL index được sử dụng để tạo FULLTEXT index và SPATIAL index.
• index_option: KEY_BLOCK_SIZE : kích thước (byte) được sửu dụng cho cho các key block.  WITH PARSER parser_name được sử dụng cho FULLTEXT index.
• index_type: BTREE hoặc HASH.
• algorithm_option và lock_option được sử dụng để thay đổi các ảnh hưởng đến phương pháp sao lưu bảng trong quá trình đồng thời cùng có tác động đọc và ghi dữ liệu vào bảng. Nên để mặc định các option này nếu các bạn không thực sự hiểu về nó( mình cũng thế :) ).

4. Khi nào sử dụng index ? 

Index giúp tăng tốc độ tìm kiếm dữ liệu xong làm giảm các thao tác thêm mới và cập nhật. Do đó, nếu không cần thiết thì không nên sử dụng index. 

Khi tạo index cần chú ý một số điểm sau:

• Trong các bảng có dữ liệu ít không càn thiết tạo index vì sẽ làm chậm việc thêm mới, cập nhật dữ liệu.
• Bảng thường xuyên thực hiện thêm mới, cập nhật mà ít tìm kiếm cũng không nên tạo index.
• Trong cột mà phần lớn giá trị NULL không nên tạo index.
• Với các cột thường xuyên bị thay đổi không nên tạo index.

Trên đây mình đã giới thiệu các khái niệm cơ bản khi các bạn sử dụng index. Sau đây, mình sẽ có loạt bài hướng dẫn tối ưu các câu truy vấn sử dụng index trong thời gian tới.