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

Bài viết khác

Backend xử lý với dữ liệu người dùng tải lên như thế nào?

Khi người dùng tải lên dữ liệu (file, hình ảnh, video, v.v.), backend thường xử lý dữ liệu này qua các bước sau: Nhận dữ liệu  Qua API: Backend nhận dữ liệu từ HTTP request (thường là POST hoặc PUT). Ví dụ: sử dụng multipart/form-data cho file. Xác thực dữ liệu Kiểm tra định dạng: […]

Đọc thêm

MinIO

MinIO là gì? MinIO là một giải pháp lưu trữ đối tượng (object storage) mã nguồn mở, tương thích với API của Amazon S3. Nó được thiết kế để lưu trữ và quản lý dữ liệu phi cấu trúc như hình ảnh, video, tài liệu, và các tệp tin lớn khác. Dung lượng của 1 object […]

Đọc thêm

Relational Database Design Principle

1-1 1-n n-n   https://nexwebsites.com/database/database-design/  

Đọc thêm

Laravel

Laravel là gì? Laravel là một framework PHP mã nguồn mở miễn phí được phát hành lần đầu tiên vào năm 2011. Taylor Otwell đã tạo ra Laravel nhằm giúp các Developer xây dựng các ứng dụng web bằng cách sử dụng mẫu kiến ​​trúc Model-View-Controller (MVC). Mô hình Model-View-Controller (MVC) trong Laravel: 1. Model […]

Đọc thêm

PHP

PHP là gì? PHP là từ viết tắt của thuật ngữ Personal Home Page và hiện nay đang có tên Hypertext Preprocessor. Đây là một dạng mã lệnh hoặc một chuỗi ngôn ngữ kịch bản. Trong đó, ngôn ngữ PHP chủ yếu được phát triển để dành cho những ứng dụng nằm trên máy chủ. Mỗi […]

Đọc thêm

Build for global scale: AFK scale cube and basic rule to build an application for global scale

REF https://akfpartners.com/growth-blog/scale-cube

Đọc thêm