PostgreSQL - INDEXES

Chỉ mục là các bảng tra cứu đặc biệt mà công cụ tìm kiếm cơ sở dữ liệu có thể sử dụng để tăng tốc độ truy xuất dữ liệu. Nói một cách đơn giản, chỉ mục là một con trỏ đến dữ liệu trong một bảng. Một chỉ mục trong cơ sở dữ liệu rất giống với một chỉ mục ở phía sau một cuốn sách.

Ví dụ, nếu bạn muốn tham chiếu đến tất cả các trang trong một cuốn sách thảo luận về một chủ đề nào đó, trước tiên bạn phải tham khảo chỉ mục, mục lục này liệt kê tất cả các chủ đề theo thứ tự bảng chữ cái và sau đó tham chiếu đến một hoặc nhiều số trang cụ thể.

Một chỉ mục giúp tăng tốc các truy vấn SELECT và mệnh đề WHERE; tuy nhiên, nó làm chậm quá trình nhập dữ liệu, với các câu lệnh UPDATE và INSERT. Chỉ mục có thể được tạo hoặc giảm xuống mà không ảnh hưởng đến dữ liệu.

Tạo chỉ mục liên quan đến câu lệnh CREATE INDEX, cho phép bạn đặt tên cho chỉ mục, chỉ định bảng và cột hoặc các cột nào để lập chỉ mục và cho biết chỉ mục theo thứ tự tăng dần hay giảm dần.

Các chỉ mục cũng có thể là duy nhất, tương tự như ràng buộc UNIQUE, trong đó chỉ mục ngăn các mục nhập trùng lặp trong cột hoặc tổ hợp các cột mà trên đó có một chỉ mục.

Lệnh CREATE INDEX

Cú pháp cơ bản của CREATE INDEX như sau -

CREATE INDEX index_name ON table_name;

Các loại chỉ mục

PostgreSQL cung cấp một số kiểu chỉ mục: B-tree, Hash, GiST, SP-GiST và GIN. Mỗi loại Chỉ mục 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 chỉ mục B-tree, phù hợp với các tình huống phổ biến nhất.

Chỉ mục một cột

Chỉ mục một cột là chỉ mục được tạo chỉ dựa trên một cột của bảng. Cú pháp cơ bản như sau:

CREATE INDEX index_name
ON table_name (column_name);

Chỉ mục nhiều cột

Chỉ mục nhiều cột được xác định trên nhiều cột của bảng. Cú pháp cơ bản như sau:

CREATE INDEX index_name
ON table_name (column1_name, column2_name);

Cho dù tạo chỉ mục một cột hay chỉ mục nhiều cột, hãy xem xét (các) cột mà bạn có thể sử dụng rất thường xuyên trong mệnh đề WHERE của truy vấn làm điều kiện lọc.

Nếu chỉ có một cột được sử dụng, chỉ mục một cột nên là lựa chọn. Nếu có hai hoặc nhiều cột thường được sử dụng trong mệnh đề WHERE làm bộ lọc, chỉ mục đa cột sẽ là lựa chọn tốt nhất.

Chỉ mục duy nhất

Các chỉ mục duy nhất không chỉ được sử dụng cho hiệu suất mà còn cho tính toàn vẹn của dữ liệu. Chỉ mục duy nhất không cho phép chèn bất kỳ giá trị trùng lặp nào vào bảng. Cú pháp cơ bản như sau:

CREATE UNIQUE INDEX index_name
on table_name (column_name);

Chỉ mục một phần

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 của một bảng; tập hợp con được xác định bởi một biểu thức điều kiện (được gọi là vị từ của chỉ số bộ phận). Chỉ mục chỉ chứa các mục nhập cho những hàng bảng thỏa mãn vị từ. Cú pháp cơ bản như sau:

CREATE INDEX index_name
on table_name (conditional_expression);

Chỉ mục ngầm định

Chỉ mục ngầm là các chỉ mục được máy chủ cơ sở dữ liệu tự động tạo khi một đối tượng được tạo. Các chỉ mục được tạo tự động cho các ràng buộc khóa chính và các ràng buộc duy nhất.

Thí dụ

Sau đây là một ví dụ mà chúng ta sẽ tạo một chỉ mục trên bảng COMPANY cho cột lương -

# CREATE INDEX salary_index ON COMPANY (salary);

Bây giờ, hãy để chúng tôi liệt kê tất cả các chỉ số có sẵn trên bảng COMPANY bằng cách sử dụng \d company chỉ huy.

# \d company

Điều này sẽ tạo ra kết quả sau, trong đó company_pkey là một chỉ mục ngầm định, được tạo khi bảng được tạo.

Table "public.company"
 Column  |     Type      | Modifiers
---------+---------------+-----------
 id      | integer       | not null
 name    | text          | not null
 age     | integer       | not null
 address | character(50) |
 salary  | real          |
Indexes:
    "company_pkey" PRIMARY KEY, btree (id)
    "salary_index" btree (salary)

Bạn có thể liệt kê toàn bộ cơ sở dữ liệu chỉ mục bằng cách sử dụng \di lệnh -

Lệnh DROP INDEX

Một chỉ mục có thể bị loại bỏ bằng cách sử dụng PostgreSQL DROPchỉ huy. Cần cẩn thận khi giảm chỉ số vì hiệu suất có thể bị chậm lại hoặc được cải thiện.

Cú pháp cơ bản như sau:

DROP INDEX index_name;

Bạn có thể sử dụng câu lệnh sau để xóa chỉ mục đã tạo trước đó -

# DROP INDEX salary_index;

Khi nào thì nên tránh chỉ mục?

Mặc dù các chỉ mục nhằm mục đích nâng cao hiệu suất của cơ sở dữ liệu, nhưng đôi khi chúng nên được tránh. Các nguyên tắc sau chỉ ra khi nào việc sử dụng chỉ mục nên được xem xét lại:

Chỉ mục không nên được sử dụng trên các bảng nhỏ.
Các bảng có hoạt động cập nhật hoặc chèn hàng loạt lớn, thường xuyên.
Chỉ mục không được sử dụng trên các cột chứa nhiều giá trị NULL.
Các cột được thao tác thường xuyên sẽ không được lập chỉ mục.