Optimize SQL

Ví dụ sử dụng EXPLAIN ANALYZE và đọc dữ liệu plan

EXPLAIN ANALYZE SELECT * FROM ENGINEER WHERE first_name = ‘Dominique’ AND last_name LIKE ‘%a%’;

Chú ý cách đọc plan sẽ đi từ trong ra ngoài, không phải từ trên xuống dưới:

Step đầu tiên dễ dàng nhận thấy là index scan với điều kiện first_name = Dominique trước, lọc ra đc 58 rows.
Sau đó thực hiện filter với cả 2 điều kiện first_name = Dominique và last_name LIKE %a%, loại bỏ được 43 rows.
Kết quả thu được 15 rows với thời gian chưa đến 1 ms.

Giải thích thuật ngữ

+ Seq Scan: là quét tuần tự toàn bộ table
+ Bitmap Heap Scan: nghĩa là PostgreSQL đã tìm thấy một tập hợp con nhỏ các hàng để tìm nạp (ví dụ: từ một chỉ mục) và sẽ chỉ tìm nạp những hàng đó. Và như vậy trong các trường hợp mà dữ liệu lớn, hoặc PostgreSQL cho rằng là nó nên như thế, thì thao tác này sẽ nhanh hơn
+ Index Scan:
+ Index: Một Index là một cấu trúc on-disk liên quan tới 1 bảng hoặc view nhằm mục đích tăng tốc độ đọc dữ liệu. Một Index bao gồm các key được lấy từ các cộng trong table hoặc view. Những key này được lưu theo cấu trúc (B-tree) cho phép SQL Server có thể tìm thấy các row liên quan một cách nhanh chóng và hiệu quả nhất. Có 2 loại Index là Clustered Index và Non Clustered Index.
+ Index Cond:
+ B-Tree Index (Balanced Tree index) :
+ Bitmap Index:
+ Nested Loop: vòng lặp lồng nhau

Từ khóa

B-Tree index.
Bitmap index.
Hash index.

GIST.
SP-GIST.
GIN.
BRIN.

Seq Scan và Bitmap Heap Scan
Không gian tìm kiếm
Độ phức tạp
Giảm stress cho CPU

Vài chú ý khi sử dụng B-Tree index:

Không có tác dụng khi tìm kiếm text sử dụng điều kiện LIKE ‘%%’. Index nữa index mãi thì tốc độ tìm kiếm.. không thay đổi (chung thủy thật), tốc độ insert còn tăng lên.
Với composite index, các điều kiện cần match full value hoặc match mostleft column để đạt hiệu quả tối đa.

Hỏi và trả lời

Có nên đánh index cho các cột có ít giá trị (low cardinality), ví dụ như cột giới tính?
Câu trả lời của mình như sau:

Bản chất của việc indexing vẫn là seq scan (dựa vào BST) nhưng trên table index. Mục đích giảm không gian tìm kiếm từ table chính xuống table index, từ đó cải thiện tốc độ query.
Do đó, nếu chỉ indexing cho column gender thì không gian tìm kiếm giảm không đáng kể. Ví dụ gender có 2 giá trị, giảm từ N xuống N/2, complexity vẫn là O(N). Ngoài ra còn thời gian truy cập vào main table để fetch data. Nếu indexing vẫn có khả năng giảm thời gian tìm kiếm.
Cần dựa trên nhu cầu thực tế, có query nào dùng điều kiện với column đó không để tạo index kết hợp với index khác hoặc composite index.

# REF

https://2kvn.com/002-hieu-ve-index-de-tang-performance-voi-postgresql-p1-p5f32363837

PostgreSQL Performance Tuning Tutorial

https://wiki.postgresql.org/wiki/Performance_Optimization

https://www.postgresql.org/docs/8.2/sql-explain.html

https://thoughtbot.com/blog/reading-an-explain-analyze-query-plan

https://helpex.vn/question/su-khac-biet-giua-seq-scan-va-bitmap-heap-scan-trong-postgres-la-gi-6098e80d853bc933ca57859b

https://viblo.asia/p/su-khac-nhau-giua-sql-server-clustered-index-scan-va-index-seed-RQqKLOvN57z

https://2kvn.com/003-hieu-ve-index-de-tang-performance-voi-postgresql-p2-p5f32373030

Series

https://viblo.asia/s/performance-optimization-voi-postgresql-OVlYq8oal8W

Trần Huy

Xem tất cả bài viết của tác giả

Bài viết khác

Logging – Monitoring – CI/CD

Logging, Monitoring và CI/CD là gì? Vai trò trong phát triển Backend 1. Tổng quan Khi xây dựng một ứng dụng Backend, viết code và chạy được chương trình mới chỉ là bước đầu. Khi ứng dụng được đưa vào sử dụng, lập trình viên còn cần biết ứng dụng đang hoạt động như thế […]

Đọc thêm

Docker là gì?

Docker là gì? Hướng dẫn sử dụng Docker trong Backend với Go 1. Docker là gì? Docker là nền tảng hỗ trợ đóng gói và chạy ứng dụng trong các môi trường độc lập gọi là container. Thay vì chỉ chia sẻ mã nguồn, lập trình viên có thể mô tả môi trường chạy ứng […]

Đọc thêm

Logging / Monitoring / CI/CD

1. Tổng quan: Ba thành phần giải quyết ba vấn đề khác nhau Khi một Backend chạy trên production, việc code hoạt động đúng trên máy local chưa đủ. Hệ thống cần có khả năng ghi nhận sự kiện, phát hiện sự cố và triển khai phiên bản mới một cách an toàn. Ba thành […]

Đọc thêm

Unit & Integration Testing

1. Unit Testing và Integration Testing là gì? Trong quá trình phát triển phần mềm, viết code để triển khai chức năng mới chỉ là một phần của công việc. Lập trình viên còn cần kiểm tra xem chương trình có hoạt động đúng theo yêu cầu hay không, đồng thời bảo đảm những thay […]

Đọc thêm

Docker

1. Docker là gì? Docker là nền tảng giúp đóng gói và chạy ứng dụng trong các môi trường được gọi là container. Một ứng dụng Backend thường phụ thuộc vào nhiều thành phần: Ngôn ngữ lập trình và runtime như Go, PHP hoặc Node.js. Thư viện và package. Database như PostgreSQL hoặc MySQL. Cache […]

Đọc thêm

Message Broker

Message Broker là gì? Vai trò của Message Broker trong Backend 1. Message Broker là gì? Message Broker là phần mềm trung gian cho phép các thành phần trong hệ thống trao đổi thông điệp với nhau mà không cần giao tiếp trực tiếp. Nó tiếp nhận thông điệp từ bên gửi, sau đó định […]

Đọc thêm