Bài viết này sẽ giúp bạn hiểu rõ được sự ảnh hưởng của thứ tự các cột xuất hiện trong Index của PostgreSQL.

1. Giới thiệu về Index trong PostgreSQL và cách tạo Index

Index là một đối tượng phổ biến trong Cơ sở dữ liệu, và gần như anh em lập trình nào muốn tối ưu câu lệnh cũng đều phải biết về Index.

Mục tiêu chính của Index là giúp câu lệnh SQL có thể tìm kiếm theo các điều kiện lọc (ví dụ: trong mệnh đề WHERE) một cách nhanh chóng hơn, thay vì phải thực hiện duyệt toàn bộ các dữ liệu của bảng để tìm được bản ghi thỏa mãn điều kiện.

Nhiều anh em lập trình khi mới học chỉ biết tới việc tạo Index trên 1 cột trong bảng, và tôi cũng đã trực tiếp tối ưu rất nhiều dự án sử dụng quá nhiều Index kiểu “mổ cò” như vậy.

Trên thực tế, chúng ta có thể sử dụng Index trên đồng thời nhiều cột, và việc này có thể mang lại hiệu quả cực kỳ lớn nếu chúng ta tạo “chuẩn xác”.

Cú pháp để tạo Index trên nhiều cột trong PostgreSQL như sau

CREATE INDEX index_name ON table_name (column1, column2, column3, ..., column n);

Trong đó indexname là tên index, tablename là tên của bảng, và column1, column2, column3, … column n là các cột được sử dụng trong index.

Ví dụ: Nếu tao muốn tạo Index tên là idx_firstname_salary  trên đồng thời 2 cột (first_name, salary) của bảng employees thì sử dụng lệnh sau

create index idx_firstname_salary on employees(first_name, salary)

Khi sử dụng Index loại này, chúng ta cần đặc biệt lưu ý tới thú tự của các cột xuất hiện trong lệnh tạo Index. Điều này có ảnh hưởng rất lớn đến hiệu quả mà Index mang lại cho dự án của anh em.

2. Tầm quan trọng về thứ tự của các cột trong Index trong PostgreSQL

Khi sử dụng Index trên nhiều cột, anh em cần lưu ý rằng thứ tự đánh Index khác nhau sẽ dẫn tới Index khác nhau. Ví dụ:

• Index trên cột (first_name, salary) và Index trên cột (salary,first_name) là 2 Index độc lập và hoàn toàn khác nhau.

Tôi sẽ thực hiện demo để anh em thấy được mọi thứ chi tiết hơn

2.1. Môi trường thực hiện Demo

Cơ sở dữ liệu: PostgreSQL 15.2

Bảng thực hiện Demo: PAYMENT

Cấu trúc của bảng như sau:

CREATE TABLE public.payment
(
payment_id integer NOT NULL DEFAULT nextval('payment_payment_id_seq'::regclass),
customer_id smallint NOT NULL,
staff_id smallint NOT NULL,
rental_id integer NOT NULL,
amount numeric(5,2) NOT NULL,
payment_date timestamp without time zone NOT NULL,
CONSTRAINT payment_pkey PRIMARY KEY (payment_id),
CONSTRAINT payment_customer_id_fkey FOREIGN KEY (customer_id)
REFERENCES public.customer (customer_id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE RESTRICT,
CONSTRAINT payment_rental_id_fkey FOREIGN KEY (rental_id)
REFERENCES public.rental (rental_id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE SET NULL,
CONSTRAINT payment_staff_id_fkey FOREIGN KEY (staff_id)
REFERENCES public.staff (staff_id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE RESTRICT
) TABLESPACE pg_default;

Để cho dễ hình dung, các bạn chỉ cần tập trung vào thông tin các CỘT có trong bảng PAYMENT, tạm thời bỏ qua các ràng buộc Foreign Key sang các bảng khác.

Bảng PAYMENT có 6 cột: payment_id, customer_id, staff_id, rental_id, amount và payment_date

2.2. Thực hiện đánh giá hiệu năng các Index trong PostgreSQL

Chiến lược đánh giá hiệu năng:

• Sẽ thực hiện đánh giá chiến lược thực thi của cùng 1 câu lệnh SQL đối với 2 trường hợp sử dụng các thứ tự cột xuất hiện tại Index trong PostgreSQL khác nhau.
• Đánh giá dựa trên tiêu chí hệ thống có sử dụng được Index hay không và COST của câu lệnh khi đó ra sao

Trong trường hợp bạn chưa từng biết về chiến lược thực thi của câu lệnh SQL, bạn có thể đọc một số bài viết khác của tôi tại đây

• Chiến lược thực thi của câu lệnh SQL: https://wecommit.com.vn/sql-execution-plan-trong-toi-uu-sql/