ĐĂNG KÝ ĐỂ NHẬN THÔNG TIN MỚI NHẤT

Tất cả mọi người

Tầm quan trọng của thứ tự các cột xuất hiện trong Index trong PostgreSQL

Tháng Sáu 16, 2023Tháng Sáu 16, 2023 Trần Quốc Huy

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/

a. Đánh giá đối với câu lệnh chỉ tìm kiếm trên cột AMOUNT

Câu lệnh chi tiết

select * from payment where amount=2.99

Trường hợp 1: Đánh giá khi sử dụng Index theo trên 2 cột theo thứ tự (payment_id, amount)

Câu lệnh tạo Index như sau

create index idx_id_amount on payment(payment_id, amount);

Kiểm tra chiến lược thực thi của câu lệnh sau khi đã có INDEX

Seq Scan on payment as payment (cost=0..290.45 rows=3233 width=26) (actual=0.009..2.067 rows=3231 loops=1)
Filter: (amount = 2.99)
Rows Removed by Filter: 11365

Giải thích chiến lược thực thi:

Hệ thống phải sử dụng giải thuật Seq Scan: quét qua toàn bộ dữ liệu trong bảng mà không sử dụng Index.
Chi phí ước tính của câu lệnh này là COST =290.45

Trường hợp 2: Đánh giá khi sử dụng Index theo trên 2 cột theo thứ tự ( amount, payment_id)

Câu lệnh tạo Index như sau

create index idx_amount_id on payment(amount, payment_id)

Kiểm tra chiến lược thực thi của câu lệnh

Bitmap Heap Scan on payment as payment (cost=77.34..225.75 rows=3233 width=26) (actual=0.588..1.177 rows=3231 loops=1)
Recheck Cond: (amount = 2.99)
Heap Blocks: exact=108
         Bitmap Index Scan using idx_amount_id (cost=0..76.53 rows=3233 width=0) (actual=0.566..0.567 rows=3231 loops=1)
         Index Cond: (amount = 2.99)

Giải thích chiến lược thực thi:

Trong trường hợp này, hệ thống đã sử dụng được Index IDX_AMOUNT_ID để phục vụ cho câu lệnh SQL.
Chi phí ước tính của câu lệnh là COST=225.75

Tổng hợp kết quả: Có sự chênh lệch hiểu quả giữa 2 Index trong PostgreSQL

Index (payment_id, amount)

Index (amount, payment_id)

select * from payment where amount=2.99

Index không hiệu quả, hệ thống tự động bỏ qua Index trong trường hợp này.

Chi phí của câu lệnh thực hiện là COST =290.45

Index có hiệu quả, hệ thống sử dụng Index trong quá trình tìm kiếm

Chi phí của câu lệnh thực hiện là COST=225.75 (chỉ bằng ~77% so với trường hợp sử dụng Index trên (payment_id, amount đang xét ở bên trái)

b. Đánh giá đối với câu lệnh chỉ tìm kiếm trên cả 2 cột AMOUNT và PAYMENT_ID

Câu lệnh sẽ đánh giá hiệu năng

select * from payment where amount=2.99 and payment_id=17506

Trường hợp 1: Đánh giá khi sử dụng Index theo trên 2 cột theo thứ tự (payment_id, amount)

Chiến lược thực thi của câu lệnh

Index Scan using idx_id_amount on payment as payment (cost=0.29..8.3 rows=1 width=26) (actual=0.015..0.015 rows=1 loops=1)
Index Cond: ((payment_id = 17506) AND (amount = 2.99))

Giải thích

Câu lệnh đã sử dụng được index IDX_ID_AMOUNT
Chi phí của câu lệnh khi thực hiện là COST=8.3

Trường hợp 2: Đánh giá khi sử dụng Index theo trên 2 cột theo thứ tự (amount, payment_id)

Chiến lược thực thi của câu lệnh

Index Scan using idx_amount_id on payment as payment (cost=0.29..8.3 rows=1 width=26) (actual=0.024..0.025 rows=1 loops=1)
Index Cond: ((amount = 2.99) AND (payment_id = 17506))

Giải thích

Câu lệnh đã sử dụng được index idx_amount_id
Chi phí của câu lệnh khi thực hiện là COST=8.3

Tổng hợp kết quả: Trong trường hợp này, hiệu quả tương đương giữa 2 Index trong PostgreSQL

Index (payment_id, amount)

Index (amount, payment_id)

select * from payment where amount=2.99 and payment_id=17506

Index có hiệu quả, hệ thống sử dụng Index trong quá trình tìm kiếm

Chi phí của câu lệnh thực hiện là COST =8.3

Index có hiệu quả, hệ thống sử dụng Index trong quá trình tìm kiếm

Chi phí của câu lệnh thực hiện là COST =8.3

2.3. Ghi chú khi sử dung Index trong PostgreSQL

Bên trên chỉ là một ví dụ cụ thể để bạn hiểu được tầm quan trọng của thứ tự các cột khi sử dụng Index trong PostgreSQL.
Để sử dụng được Index trong PostgreSQL, cách tốt nhất là tiếp cận từ kiến trúc và hiểu được các chiến lược thực thi. Bạn không nên tự tổng kết các luật kiểu như:
- Nếu sử dụng Index trên 2 cột (A,B) mà câu lệnh chỉ tìm kiếm điều kiện WHERE B= thì sẽ không sử dụng được Index
- Kết luận như trên là HOÀN TOÀN SAI LẦM, vì cách thức hoạt động của chiến lược thực thi không phải theo các LUẬT kiểu như vậy.

3. Nếu bạn muốn xem Video Demo so sánh hiệu năng các trường hợp sử dụng Index trong PostgreSQL

Bạn có thể xem Video tại đây

4. Một số bài viết về tối ưu khác mà tôi từng chia sẻ

Thiết kế sai lầm cực lớn của DEV trong Cơ sở dữ liệu, mắc phải sai lầm này thì số lượng bản ghi nhỏ cũng có thể bị treo: xem bài viết tại đây
Cách tôi tối ưu cơ sở dữ liệu cải thiện 97% bằng một chấm nhỏ như thế nào: xem tại đây
Xem bài viết sau để tránh hiểu lầm kinh điểm khi so sánh hiệu năng SELECT 1 CỘT và SELECT : xem bài viết tại đây.

5. Nếu bạn muốn hiểu tường tận tất cả các kỹ năng tối ưu SQL tối ưu cơ sở dữ liệu

Khi bạn tham gia chương trình học “Từ điển tối ưu 100x hiệu năng” của Wecommit, bạn sẽ biết được toàn bộ những kỹ thuật tối ưu cơ sở dữ liệu mà chúng tôi đã và đang áp dụng cho rất nhiều dự án tại ngân hàng, chứng khoán, các công ty viễn thông, các hệ thống tại bệnh viện…

Không chỉ vậy, bạn còn được đồng hành và tư vấn HÀNG TUẦN, liên tục trong 1 năm, bạn sẽ cảm thấy vô cùng tự tin và khác biệt so với các đồng nghiệp của mình.

Hãy tìm hiểu chương trình tại đây:https://wecommit.com.vn/tu-dien-toi-uu-100x-hieu-nang/

6. Nếu ban muốn liên hệ với tôi

Tác giả của bài viết: Trần Quốc Huy – CEO & Founder Wecommit.

Follow tôi tại Facebook cá nhân: https://www.facebook.com/tran.q.huy.71/

Theo dõi các video về tối ưu SQL trên Youtube của tôi: https://www.youtube.com/@tranquochuywecommit

Views: 1776

Trả lời Hủy