Các chỉ mục (Indexes) trong cơ sở dữ liệu là các công cụ đặc biệt giúp tối ưu hóa việc truy xuất dữ liệu. Một cách dễ hiểu, chỉ mục là như một danh sách chỉ dẫn đến dữ liệu trong một bảng. Nó tăng cường tốc độ truy vấn SELECT và các điều kiện WHERE, ORDER và GROUP. Tuy nhiên, nó có thể làm chậm quá trình thêm mới hoặc cập nhật dữ liệu, như khi sử dụng các câu lệnh UPDATE hoặc INSERT, bởi vì chúng phải cập nhật các bản ghi trong bảng chỉ mục. Các chỉ mục có thể được tạo hoặc xóa mà không ảnh hưởng đến dữ liệu ban đầu.
Trong PostgreSQL, bạn có thể tạo một chỉ mục bằng cách sử dụng câu lệnh CREATE INDEX.
Tạo chỉ mục (Indexs) trong bảng CSDL
Cú pháp lệnh tạo chỉ mục (Index)
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ INCLUDE ( column_name [, ...] ) ]
[ WITH ( storage_parameter = value [, ... ] ) ]
[ TABLESPACE tablespace_name ]
[ WHERE predicate ]
VD: Đánh chỉ mục trên bảng Users, cho trường group_id, chúng ta sử dụng câu lệnh sql sau:
CREATE INDEX IDX_group_id ON users (group_id);
Kết quả:
Các loại chỉ mục (Index) trong PostgreSQL
PostgreSQL cung cấp một số loại Index như: B-tree, Hash, GiST, SP-GiST và GIN. Mỗi loại index sử dụng một thuật toán khác nhau phù hợp nhất với các loại truy vấn khác nhau. Theo mặc định, lệnh CREATE INDEX tạo các index B-tree.
Chỉ mục trên một trường dữ liệu (Single-Column Indexes)
Chỉ mục được tạo ra chỉ cho 1 trường (Column) của database
CREATE INDEX index_name ON table_name (column_name);
Chỉ mục trên nhiều trường dữ liệu (Multicolumn Indexes)
Chỉ mục được tạo ra cho nhiều trường (column) của database
CREATE INDEX index_name ON table_name (column_name_1, column_name_2, ...);
Chỉ mục duy nhất (Unique Indexes)
Ngoài việc tăng tốc độ truy xuất dữ liệu, nó còn đảm bảo tính toàn vẹn của dữ liệu bằng việc ngăn không cho chèn các dữ liệu trùng lặp vào bảng CSDL.
CREATE UNIQUE INDEX index_name on table_name (column_name);
Chỉ mục một phần (Partial Indexes)
Chỉ mục một phần là một chỉ mục được xây dựng trên một tập hợp con (subset) của bảng dữ liệu, tập hợp con được xác định bởi một biểu thức điều kiện (condition).
CREATE INDEX index_name on table_name (conditional_expression);
Chỉ mục ngầm (Implicit Indexes)
Chỉ mục ngầm sẽ tự động được tạo bởi máy chủ Cơ sở dữ liệu (Database Server) khi một đối tượng được tạo. Đối tượng ở đây có thể là một khóa chính (PRIMARY KEY) hoặc các trường được xác định là duy nhất (UNIQUE Columns). Trong quá trình này, máy chủ sẽ tự động thiết lập các ràng buộc khóa chính (PRIMARY KEY constraints) và ràng buộc duy nhất (UNIQUE constraints).
VD: Bảng groups có 1 Primary Key là group_id, PostgreSQL sẽ tự động tạo chỉ mục ngầm có tên là groups_pkey
Xóa chỉ mục (Indexs) trong bảng CSDL
Để xóa các index trong bảng CSLD của PostgreSQL chúng ta sử dụng lệnh: DROP INDEX . Việc xóa index không làm ảnh hưởng tới dữ liệu, nhưng có thể ảnh hưởng tới tốc độ truy xuất dữ liệu.
Cú pháp xóa index
DROP INDEX index_name;
VD: Xóa index vừa tạo trên bảng users
DROP INDEX IDX_group_id;
Một số lưu ý khi sử dụng Index
- Index không nên sử dụng ở các bảng có dữ liệu nhỏ
- Bảng thường xuyên có thêm và cập nhật dữ liệu
- Bảng có nhiều dữ liệu NULL
- Các trường thường xuyên thao tác
