Tối ưu hóa các hàm phân tích cho dữ liệu lớn: indexing và partitioning

Khi dữ liệu nhiều lên (kiểu như tin nhắn deadline trong chat công ty ấy), các truy vấn select và xử lý bắt đầu chậm dần. Đây là mấy lý do chính:

1. Không có index. Khi PostgreSQL phải quét hết cả bảng để thực hiện truy vấn (cái này gọi là "Seq Scan" — quét tuần tự), truy vấn sẽ tốn kha khá thời gian luôn.
2. Truy vấn SQL không tối ưu. Nếu truy vấn viết mà không để ý tối ưu, thì dù có index bạn vẫn gặp khó khăn khi chạy thực tế. Ví dụ, quên dùng điều kiện quan trọng trong WHERE? Chuẩn bị tinh thần chờ lâu nhé.
3. Dữ liệu quá nhiều trong một bảng. Ví dụ, bạn thử phân tích doanh số của tất cả các năm cùng lúc, thì index cũng chưa chắc cứu được đâu.

Nhưng đừng lo, mình có hai cách xịn đã được kiểm chứng: Indexing và Partitioning.

Dùng index để tăng tốc truy vấn

Đây là ví dụ đơn giản tạo index:

CREATE INDEX idx_sales_date ON sales(transaction_date);

• Ở đây idx_sales_date — là tên index (bạn đặt tên gì cũng được, nhưng nên đặt cho dễ hiểu tí).
• ON sales(transaction_date) — chỉ rõ index tạo cho bảng nào, cột nào.

Index này cực kỳ hữu ích nếu bạn hay filter truy vấn theo trường transaction_date.

Ví dụ truy vấn sẽ hưởng lợi từ index này:

SELECT *
FROM sales
WHERE transaction_date BETWEEN '2023-01-01' AND '2023-12-31';

Index cho khóa tổng hợp

Nếu truy vấn của bạn hay dùng kết hợp nhiều trường, ví dụ region và product_id, hãy nghĩ đến việc tạo index tổng hợp:

CREATE INDEX idx_sales_region_product ON sales(region, product_id);

Bây giờ các truy vấn kiểu này chạy nhanh hơn hẳn:

SELECT *
FROM sales
WHERE region = 'North America' AND product_id = 42;

Dùng unique index

Unique index không chỉ giúp tìm kiếm nhanh mà còn đảm bảo giá trị trong cột là duy nhất. Ví dụ:

CREATE UNIQUE INDEX idx_unique_customer_email ON customers(email);

Bây giờ bạn không thể lỡ tạo hai khách hàng cùng email nữa đâu.

Index cho các hàm phân tích

Một số hàm phân tích dữ liệu như SUM, COUNT hay AVG có thể dùng index để tính nhanh hơn. Ví dụ:

CREATE INDEX idx_sales_amount ON sales(amount);

Truy vấn:

SELECT SUM(amount)
FROM sales 
WHERE transaction_date >= '2023-01-01';

sẽ chạy nhanh hơn nhờ index.

Partitioning bảng cho dữ liệu lớn

Partitioning bảng — là quá trình chia một bảng lớn thành các phần logic nhỏ hơn, gọi là partition. Ví dụ, bạn có thể chia bảng sales thành partition theo năm: sales_2021, sales_2022 v.v.

Bạn nghĩ cái này khó à? Thực ra PostgreSQL làm cái này dễ hơn bạn tưởng nhiều.

Các kiểu partitioning

1. Partition theo range (Range Partitioning). Dữ liệu chia theo khoảng, ví dụ theo ngày tháng.
2. Partition theo list (List Partitioning). Dữ liệu chia theo giá trị cụ thể, ví dụ theo vùng miền.
3. Partition theo hash (Hash Partitioning). Dùng hàm hash để chia dữ liệu (ít khi dùng thủ công).

Tạo bảng partition

Cùng tạo bảng sales partition theo năm nhé.

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    transaction_date DATE NOT NULL,
    amount NUMERIC,
    region TEXT
) PARTITION BY RANGE (transaction_date);

Bây giờ tạo partition cho từng năm:

CREATE TABLE sales_2021 PARTITION OF sales
FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');

CREATE TABLE sales_2022 PARTITION OF sales
FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

Các truy vấn filter theo ngày sẽ tự động chỉ làm việc với partition cần thiết. Bạn có thể kiểm tra bằng lệnh EXPLAIN.

Ví dụ với partitioning

Đây là truy vấn tính tổng doanh số chỉ cho năm 2021:

SELECT SUM(amount)
FROM sales
WHERE transaction_date BETWEEN '2021-01-01' AND '2021-12-31';

Nhìn đấy, PostgreSQL chỉ làm việc với partition sales_2021 thôi, không quét cả bảng đâu.

Ví dụ: tối ưu hóa tính metric theo vùng miền

Giả sử bạn muốn tính tổng doanh số theo vùng miền. Không có index và partition thì chờ dài cổ luôn. Đầu tiên tạo index cho cột region:

CREATE INDEX idx_sales_region ON sales(region);

Truy vấn của bạn:

SELECT region, SUM(amount)
FROM sales
GROUP BY region;

Bây giờ xử lý nhanh hơn nhờ index.

Ví dụ: partitioning dữ liệu thời gian

Với dữ liệu thời gian như giao dịch hay log, hãy tạo partition theo tháng. Ví dụ:

CREATE TABLE sales_monthly PARTITION BY RANGE (transaction_date);

CREATE TABLE sales_jan_2023 PARTITION OF sales_monthly
FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

Truy vấn:

SELECT SUM(amount)
FROM sales_monthly
WHERE transaction_date >= '2023-01-01' AND transaction_date < '2023-02-01';

sẽ chạy nhanh hơn vì PostgreSQL chỉ đọc partition sales_jan_2023.

Ví dụ: kết hợp indexing và partitioning

Bạn có thể kết hợp indexing và partitioning để đạt hiệu năng tối đa. Ví dụ, bạn tạo index trong từng partition. Như này nè:

CREATE INDEX idx_sales_amount_jan_2023 ON sales_jan_2023(amount);

Làm sao tránh lỗi phổ biến

Nhiều lỗi hiệu năng là do dùng index và partitioning không đúng cách. Ví dụ:

• Có quá nhiều index sẽ làm chậm thao tác insert.
• Partition nên thiết kế sao cho đều nhau; partition quá nhỏ hoặc quá to đều làm giảm hiệu năng.
• Quên phân tích hiệu năng (EXPLAIN ANALYZE) trước khi tối ưu — giống như sửa xe mà không mở nắp capo ấy.

Luôn kiểm tra xem tối ưu hóa của bạn có thực sự tăng tốc không, và đừng ngại thử nghiệm nhé.