Các chỉ mục (Indexs) là các bảng tra cứu đặc biệt mà công cụ tìm kiếm dữ liệu (database search engine) sử dụng để tăng tốc độ truy xuất dữ liệu. Nói một cách đơn giản, một index là một con trỏ tới dữ liệu trong một bảng. Bạn có thể hiểu một index trong database rất giống với phụ lục của một cuốn sách.
Một chỉ mục giúp tăng tốc các truy vấn SELECT và các mệnh đề điều kiện WHERE, ORDER, GROUP. Tuy nhiên, nó làm chậm quá trình nhập dữ liệu, khi sử dụng các câu lệnh UPDATE và INSERT. Vì chúng phải bổ sung thêm các bản ghi vào các bảng chứa index. Các chỉ mục có thể được tạo hoặc loại bỏ đi mà không ảnh hưởng đến dữ liệu.
Để tạo một chỉ mục (Index) trong PostgreSQL chúng ta 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ẽ được tự động tạo bởi các máy chủ CSDL (Database Server) khi một đối tượng được tạo ra. Đối tượng ở đây là khóa chính (PRIMARY KEY) hoặc các trường được định nghĩa là duy nhất (UNIQUE Columns). Nó sẽ tự động tạo 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