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

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

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

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. Bên cạnh bài viết về Index trong PostgreSQL, bạn có thể đọc thêm các nội dung khác về tối ưu

  • Thiết kế sai lầm cực lớn của DEV trong Cơ sở dữ liệu, mắc phải sai lầm này thì số lượng bản ghi nhỏ cũng có thể bị treo: xem bài viết tại đây
  • Cách tôi tối ưu cơ sở dữ liệu cải thiện 97% bằng một chấm nhỏ như thế nào: xem tạ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.
  • Tối ưu Index với cột có giá trị Null: Xem bài viết tại đây

5. Tôi có một chương trình có phí – Từ điển tối ưu 100x hiệu năng – chương trình này sẽ giúp bạn HOÀN TOÀN TỰ TIN và KHÁC BIỆT so với các đồng nghiệp về năng lực tối ưu

Trong chương trình này bạn sẽ nhận được rất nhiều các KIẾN THỨC, KINH NGHIỆM ĐỘC QUYỀN, có nhiều thứ các anh em DEV còn không biết về sự tồn tại. 

Tôi sẽ đồng hành 1 năm cùng anh em, để anh em hoàn toàn tự tin khi áp dụng các kiến thức vào dự án thực tế trên công ty.

Đây là một chương trình có phí, anh em có thể tham gia chương trình 01 năm ngay từ mức phí 8.000.000 VNĐ.

Các anh em có thể đăng ký trải nghiệm để hiểu rõ mọi thứ trong chương trình để tự đánh giá được chương trình có phù hợp với mục tiêu của bản thân hay không.

Buổi trải nghiệm này diễn ra qua Zoom Online, cách thức đăng ký tham gia trải nghiệm như sau:

  • Cách 1: Đăng ký trực tiếp trên Website wecommit.com.vn qua Form đăng ký
  • Cách 2: Inbox Zalo 0888549190

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

Tác giả: Trần Quốc Huy – Founder & CEO Wecommit.

Các bạn có thể liên hệ, thảo luận các kiến thức về tối ưu cùng tôi qua kênh FB cá nhân của tôi

Facebook: https://www.facebook.com/tranquochuy.toiuu/

Views: 2702





    Câu hỏi bảo mật

    Trả lời

    Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *