INDEX trong SQL: Cách tạo/xóa và lưu ý với các truy vấn có INDEX – Vantien’s blog

Posted on by adminChuyên mục:Kiến thức Internet

Lời mở đầu

Chào các bạn, ở trong bài viết trước : INDEX trong SQL : Tổng quan, khái niệm, chính sách hoạt động giải trí mình đã nói về một vài những trình làng tổng quan về INDEX trong SQL như thể :

  • Cơ chế hoạt động của INDEX
  • Các loại INDEX

Nếu như các bạn chưa đọc bài viết trước thì hãy click vào link bên trên để có thể hiểu được cơ bản về nó nhé. Trong bài viết này mình sẽ hướng dẫn các bạn cách tạo/xóa INDEX trong bảng và một vài lưu ý để sử dụng INDEX trong SQL nhé.

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):

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 ) :

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ì giờ đây bạn truy vấn rất nhiều đến tài liệu của cột username chính thế cho nên bạn muốn đánh INDEX cho nó để giúp truy vấn nhanh hơn :

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 = ‘[email protected]’). 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:

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

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 = ‘[email protected]’. 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 trọn vẹn hoàn toàn có thể xóa bằng cách sử dụng :

DROP INDEX index_name ON table_name;

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

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:

DROP INDEX idx_username ON users;

Hoặc

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 nỗ lực tránh sử dụng các toán tử sau trong mệnh đề WHERE như thể : “ 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 tác động đến vậ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ụ :

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

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

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 hoàn toàn có thể viết lại bằng cách tích hợp UNION ALL để tăng vận tốc truy vấn. Ví dụ bạn đã đánh index riêng cho username và email :

SELECT username, email
FROM users
WHERE username = 'TienHihi' OR email = '[email protected]';

Câu truy vấn sau cùng mục tiêu nhưng có vận tốc nhanh hơn :

SELECT username, email
FROM users
WHERE username = 'TienHihi';

UNION ALL

SELECT username, email
FROM users
WHERE email = '[email protected]';

Nguồn : vantien.net
Tham khảo tại :

admin