INDEX trong SQL

Posted by

INDEX trong SQL là gì?

Nói dễ hiểu thì bạn hay tưởng tượng bạn có một quyển bách khoa toàn thư chứa thông tin về mọi thứ trên đời. Một ngày đẹp trời nào đó bạn bị “say nắng” ai đó và muốn tìm hiểu về cách tán gán/trai. Bạn sẽ làm thế nào nếu không có mục lục. Tất nhiên là bạn sẽ phải tìm qua từng trang một đến khi nào tìm được toàn bộ những thông tin về “bí kíp tán gái/trai” đúng không? Còn nếu có mục lục thì mọi thứ sẽ dễ dàng hơn. Chỉ cần dở mục lục ra là bạn sẽ tìm và đọc được ngay bí kíp rồi. Nhưng để mà tạo ra mục lục cho quyển sách khổng lồ này thì người viết cũng cần mất nhiều công sức hơn và chúng ta cũng cần tốn nhiều trang giấy để viết mục lục lên đó.

Ở đây mình muốn nói rằng là:

  • “cách tán gán/trai” -> từ khóa cần truy vấn
  • Quyển sách -> Database
  • Mục lục -> INDEX
  • Người viết sách -> Công cụ ghi
  • Bạn -> Công cụ truy vấn

Đấy là cách giải thích kiểu “amateur”, còn sau đây là giải thích theo kiểu “professional” nhé:

Theo VietJack thì:

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 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.

Cơ chế hoạt động

Khi bạn thực hiện truy vấn đến cơ sở dữ liệu. Database Engine sẽ thực hiện việc quét qua toàn bộ tất cả các bản ghi trong bảng đến tìm những bản ghi phù hợp với điều kiện. Những bảng có ít dữ liệu và dữ liệu không phức tạp thì ổn. Tuy nhiên nếu dữ liệu lớn và phức tạp thì việc quét này sẽ rất chậm và tốn rất nhiều tài nguyên của hệ thống.

Khi bạn đánh chỉ mục cho bảng. Database Engine sẽ tìm đến chỗ lưu chỉ mục để có thể so sánh và lấy ra vị trí chính xác của hàng dữ liệu phù hợp với điều kiện truy vấn. Qua đó việc đánh INDEX sẽ giúp việc truy vấn dữ liệu trở lên nhanh hơn và tốn ít tài nguyên hơn so với việc quét toàn bộ bản ghi trong bảng.

Các loại INDEX trong SQL

Trong SQL có 2 loại INDEX đó là:

  1. Clustered Index
    • Đây là dạng INDEX mà dữ liệu của bảng sẽ được sắp xếp một cách vật lý theo thứ tự. Chính vì vậy mà trong một bảng chỉ có duy nhất một Clustered Index.
    • Dạng Index này sẽ lưu trực tiếp vào trong bảng theo trường được đánh INDEX.
    • Thường được tạo ra khi bảng có Primary Key. Nếu không có Primary Key, nó sẽ được tạo khi một trường được định nghĩa là UNIQUE (nếu chưa tồn tại Clustered Index trước đó).
  2. Non-clustered Index
    • Một non-clustered index sẽ được lưu ra một nơi hoàn toàn tách biệt so với bảng cơ sở.
    • Nó sẽ không sắp xếp dữ liệu khi lưu.
    • Một non-clustered index sẽ chứa giá trị của chỉ mục cùng với một con trỏ trực tiếp đến vị trí của hàng dữ liệu trong bảng.

Cách tạo INDEX

Để tạo INDEX trong SQL, các bạn sử dụng câu query sau (giá trị của trường được phép trùng nhau không ràng buộc):

?
1
2
CREATE INDEX index_name
ON table_name (column1, column2, ...);

Để tạo unique index (giá trị của trường là duy nhất, không được phép trùng nhau):

?
1
2
CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

VÍ DỤ TẠO INDEX

Ví dụ bạn đang có table users với các cột:

  • id (Primary Key, Auto Increament)
  • username
  • email
  • password

Vì cột id là khóa chính nên nó mặc định sẽ được đánh index dạng unique. Chính vì vậy nếu bạn truy vấn tìm kiếm bản ghi thông qua id thường rất nhanh là vậy.

Bởi vì bây giờ bạn truy vấn rất nhiều đến dữ liệu của cột username chính vì vậy bạn muốn đánh INDEX cho nó để giúp truy vấn nhanh hơn:

?
1
2
CREATE INDEX idx_username
ON users (username);

Truy vấn đến username và email rất nhiều (và thường 2 trường này luôn được tìm kiếm song song trong câu query, kiểu như: WHERE username = ‘VanTien’ AND email = ‘tienhuhihaha@vantien.net’). Lúc này bạn có thể tạo 1 index tổ hợp (compound index) cho 2 cột này bằng cách:

?
1
2
CREATE INDEX idx_username_email
ON users (username, email);

Lưu ý: Bạn tạo INDEX như trên và việc tạo 2 INDEX cho 2 cột như dưới đây nó sẽ khác nhau đấy nhé.

?
1
2
3
4
CREATE INDEX idx_username
ON users (username);
CREATE INDEX idx_email
ON users (email);

Việc bạn tạo index dạng compound sẽ tốt hơn cho việc truy vấn kết 2 cột lại với nhau đấy nhé. Ví dụ như bạn cần tìm user có cả 2 điều kiện là: username = ‘TienDepTrai’ AND email = ‘TienDepTrai@vantien.net’. Thì dạng compound sẽ cần duyệt qua ít bản ghi hơn là so với dạng đơn lẻ bên trên. Chi tiết bạn có thể xem tại đây nhé: A mini-lesson in “compound indexes”.

Xóa INDEX

Nếu bạn không muốn sử dụng INDEX nữa thì bạn hoàn toàn có thể xóa bằng cách sử dụng:

?
1
DROP INDEX index_name ON table_name;

Trong MySQL bạn sẽ có thêm 1 cách nữa là:

?
1
2
ALTER TABLE table_name
DROP INDEX index_name;

VÍ DỤ XÓA INDEX:

Vẫn là bảng users bên trên và giả định là bạn đã tạo 1 index là idx_username nhé. Bây giờ bạn không muốn sử dụng INDEX cho nó nữa thì bạn có thể chạy:

?
1
DROP INDEX idx_username ON users;

Hoặc

1
2
ALTER TABLE idx_username
DROP INDEX users;

Một vài lưu ý khi sử dụng INDEX

– Bạn hãy cố gắng tránh sử dụng các toán tử sau trong mệnh đề WHERE như là: “IS NULL”, “<>”, “!=”, “!>”, “!<“, “NOT”, “NOT EXISTS”, “NOT IN”, “NOT LIKE”. Vì những toán tử này nó không sử dụng đặc tính index mà thay vì thế nó sẽ dò tìm toàn bảng gây ảnh hưởng đến tốc độ của câu truy vấn.

Khá hay ho là khi bạn dùng “LIKE ‘%abc’” thì nó cũng không sử dụng index sẽ scan qua toàn bộ bảng còn nếu bạn dùng “LIKE ‘abc%’” thì… nó lại dùng đến INDEX.

– Bạn không nên sử dụng các function built-in trực tiếp cho các cột vì nó cũng sẽ không sử dụng index. Ví dụ:

?
1
2
SELECT * FROM users
WHERE UPPER(username) = 'TIEN';

Ví dụ sau có dùng index:

?
1
2
SELECT * FROM users
WHERE username = UPPER('tien');

– Trong các câu truy vấn có nhiều OR với nhiều cột bạn có thể viết lại bằng cách kết hợp UNION ALL để tăng tốc độ truy vấn. Ví dụ bạn đã đánh index riêng cho username và email:

?
1
2
3
SELECT username, email
FROM users
WHERE username = 'TienHihi' OR email = 'TienDepTrai@vantien.net';

Câu truy vấn sau cùng mục đích nhưng có tốc độ nhanh hơn:

?
1
2
3
4
5
6
7
8
9
SELECT username, email
FROM users
WHERE username = 'TienHihi';
UNION ALL
SELECT username, email
FROM users
WHERE email = 'TienHihi@vantien.net';

Sưu tầm: vantien.net

Leave a Reply

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *