Database PostgreSQL

Tập đếm COUNT(*) trong PostgreSQL

TẬP ĐẾM COUNT(*) TRONG POSTGRESQL

 

Khi làm việc với cơ sở dữ liệu, việc muốn biết xem có bao nhiêu hàng trong một bảng có lẽ là chuyện quá đỗi quen thuộc. Nhưng ngoài cách sử dụng COUNT trong postgresql thì bạn còn biết cách nào khác không? Hay bạn có thực sự hiểu rõ được câu lệnh mà bạn đã sử dụng từ trước tới giờ? Và bạn muốn đếm số hàng chính xác hay bạn chỉ muốn ước lượng gần đúng thôi? Cuối tuần rảnh rỗi ngồi dịch cho bận bớt.

Thông tin chung:

  • PostgreSQL v12.0

Chuẩn bị DB để test:

-- Tạo 2tr số và chuỗi ngẫu nhiên
CREATE TABLE items AS
    SELECT 
        (random()*2000000)::integer AS n,
        md5(random()::text) AS s
    FROM
        generate_series(1,2000000);

-- Thông báo có sự thay đổi của bảng kích thước lớn
VACUUM ANALYZE;

CHÍNH XÁC ĐẾN TỪNG CHI TIẾT

Bắt đầu đếm…

Chúng ta bắt đầu với việc đếm chính xác có bao nhiêu hàng trong bảng items vừa tạo ở trên, tất nhiên là dùng COUNT(*) rồi. Sau đó tính thời gian thực thi.

SELECT COUNT(*) FROM public.items;
-- Response --
-- Total query runtime: 150ms

150ms cho 2 triệu hàng – vẫn được cho là quá chậm.

Vậy tại sao COUNT(*) lại chậm như vậy?

Đối với nhiều người thì sự chậm chạp trong việc đếm cho câu truy vấn SQL sau là dễ hiểu.

SELECT COUNT(*) FROM /* truy vấn phức tạp */

Theo sau là những truy vấn phức tạp thì PostgreSQL phải tính toán ra kết quả trước rồi mới tới bước đếm có bao nhiêu hàng. Chậm là đúng rồi. Nhưng nếu chỉ là FROM một bảng có kích thước lớn nào đó thì với nhiều người, điều đó thật khó hiểu.

Tuy nhiên, nếu nghĩ kĩ lại, thì điều trên vẫn đúng: PostgreSQL phải tính toán tập kết quả trước rồi sau đó đếm chúng. Bởi vì không có một biến ma thuật nào đó lưu trữ số lượng hàng của một bảng (giống như trong MySQL’s MyISAM), cách duy nhất để đếm là đếm từng hàng một.

Vì vậy COUNT(*) bình thường sẽ quét tuần tự từng hàng bảng nên khá là tốn thời gian.

Ủa rồi * trong COUNT(*) có phải là vấn đề?

Dấu * trong SELECT * FROM … được hiểu là tất cả các cột. Tương tự như vậy, nhiều người cho là sử dụng COUNT(*) sẽ không hiệu quả và nên sử dụng COUNT(id) hoặc COUNT(1) sẽ OK hơn.

Nhưng * trong COUNT(*) lại có một ý nghĩa khác.

Nó có nghĩa là hàng(row) và không được hiểu theo kiểu mở rộng (thực ra nó có nghĩa là không có đối số).

Sử dụng COUNT(1) hoặc COUNT(id) thực ra lại chậm hơn so với COUNT(*) vì nó phải kiểm tra nếu đối số có rỗng (IS NULL) hay không (COUNT sẽ bỏ qua các đối số NULL).

Thử với bảng items với 2 câu lệnh kia và được kết quả như thế này.

SELECT COUNT(s) FROM public.items; 
-- Response -- 
-- Total query runtime: 181ms

SELECT COUNT(1) FROM public.items;
-- Response --
-- Total query runtime: 163ms

Vậy không việc gì phải đổ thừa cho * cả.

Làm sao để tăng tốc độ COUNT trong postgresql

Sử dụng một chỉ mục cho việc quét (Index only scan)

Nếu mà chỉ cần quét số lượng nhỏ index để đếm số lượng hàng thay vì cả cái bảng thì tốt quá.

Nhưng mà việc này lại không hề đơn giản trong PostgreSQL bởi cơ chế kiểm soát đồng thời nhiều phiên bản (multi-version concurrency control strategy) của nó.

Có nghĩa là PostgreSQL lưu nhiều phiên bản của 1 hàng, như trong trường hợp cập nhật mới thì nó sẽ lưu cả phiên bản cũ trước khi cập nhật và phiên bản hiện tại sau khi cập nhật của hàng đó.

Vậy nếu dùng chỉ mục để đếm thì sẽ đếm dư ra các phiên bản không được hiển thị đối với người dùng, ví dụ những hàng đã bị xóa (deleted rows), phiên bản cũ của những hàng đã được cập nhật (old versions of updated rows) cũng như là những hàng được cập nhật mới hoặc chèn thêm mà chưa được thực thi (uncommitted inserts or new versions of updates).

Để giải quyết vấn đề này, PostgreSQL sử dụng visibility map (bản đồ hiển thị hay gì đó tự cảm nhận), một cấu trúc dữ liệu lưu trữ thông tin nếu tất cả các hàng trong 1 khối bảng có được hiển thị (visible) hay không với người dùng.

Nếu tất cả các khối bảng đều được hiển thị thì việc quét chỉ mục không cần thiết phải truy cập đến từng hàng mà xác nhận thử nó có visible hay không. Việc quét chỉ mục như vậy được gọi là “index only scan”, và nó thường nhanh hơn việc chỉ quét chỉ mục mà đếm số lượng hàng.

Bản đồ này (visibility map) được duy trì bởi VACUUM trong PostgreSQL, thường thì ở chế độ nền là autovacuum. Nếu autovacuum không theo kịp các hoạt động chèn xóa, hoặc autovacuum bị disabled thì index only scan sẽ không được sử dụng vì visibility map đó không đủ dữ liệu. Nên phải đảm bảo rằng autovacuum chạy đủ thường xuyên trên bảng nếu bạn muốn sử dụng số lượng nhỏ index để tăng tốc độ COUNT trong postgresql.

Sử dụng một bảng tổng hợp

Như đã nói ở trên thì PostgreSQL không có lưu trữ thông tin số hàng trong bảng.

Việc có một bảng tổng hợp số lượng hàng của các bảng cũng phải tốn chi phí cho mỗi thay đổi trong cơ sở dữ liệu nhưng có gì ngăn bạn lại khi tự tạo riêng cho mình một bảng tổng hợp nhỉ? Giả sử bạn muốn theo dõi số lượng hàng trong bản items. Bạn có thể thực hiện như sau:

START TRANSACTION;
 
CREATE TABLE items_count(c bigint);
 
CREATE FUNCTION items_count() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF TG_OP = 'INSERT' THEN
      UPDATE items_count SET c = c + 1;
 
      RETURN NEW;
   ELSIF TG_OP = 'DELETE' THEN
      UPDATE items_count SET c = c - 1;
 
      RETURN OLD;
   ELSE
      UPDATE items_count SET c = 0;
 
      RETURN NULL;
   END IF;
END;$$;
 
CREATE CONSTRAINT TRIGGER items_count_mod
   AFTER INSERT OR DELETE ON items
   DEFERRABLE INITIALLY DEFERRED
   FOR EACH ROW EXECUTE PROCEDURE items_count();
 
-- triggers phải được thực thi cho mỗi statement
CREATE TRIGGER items_count_trunc AFTER TRUNCATE ON items
   FOR EACH STATEMENT EXECUTE PROCEDURE items_count();
 
-- Khởi tạo bảng đếm cho bảng items
INSERT INTO items_count
   SELECT count(*) FROM items;
 
COMMIT;

Việc cập nhật sẽ được thực hiện ở mỗi một giao dịch nên sẽ không có dữ liệu bị thay đổi bởi các giao dịch đồng thời có thể bị mất vì vấn đề “race conditions” (Hiểu nhanh ở đây là nhiều giao dịch cùng muốn thay đổi cùng 1 hàng trong cơ sở dữ liệu cùng một lúc).

Điều này được đảm bảo vì CREATE TRIGGER sẽ khóa bảng trong chế độ SHARE ROW EXCLUSIVE – chế độ ngăn chặn tất cả các sự thay đổi đồng thời. Điểm tệ ở đây là tất cả thay đổi dữ liệu đồng thời đều phải đợi đến lúc câu lệnh SELECT COUNT(*) thực thi xong.

Cách này sau này sẽ giúp thực thi câu lệnh COUNT trong postgresql nhanh hơn, nhưng cái giá phải trả là làm chậm tất cả quá trình thay đổi dữ liệu trên bảng. Và cách này cũng không giúp gì trong trường hợp có nhiều truy vấn đằng sau FROM.

Sử dụng CONSTRAINT TRIGGER sẽ đảm bảo khóa trên mỗi hàng của items_count được giữ ngắn nhất có thể để cải thiện sự tương tranh. Mặc dù bảng đếm này sẽ nhận nhiều cập nhật, nhưng sẽ không bao giờ xảy ra sự bloating (khi quá nhiều giao dịch, update hay delete thì nó lưu old version của hàng, mà ko xóa hẳn hay cập xóa bản cũ, việc lưu trữ nhiều giao dịch quá tốn bộ nhớ, tham khảo thêm Table bloating) bởi những cập nhật này là HOT updates.

ĐANG LÀM BIẾNG, GẦN ĐÚNG LÀ ĐƯỢC RỒI

Bạn có thực sự cần COUNT trong postgresql?

Đôi khi giải pháp tốt nhất là tìm kiếm một sự thay thế.

Thường thì không cần phải đếm chính xác, đếm gần đúng là được rồi. Trong trường hợp đó bạn có thể dùng sự ước lượng trong PostgreSQL:

SELECT reltuples::bigint
FROM pg_catalog.pg_class
WHERE relname = 'items';

-- Response --
-- Total query runtime: 70ms

Giá trị này được cập nhật bởi cả autovacuum và autoanalyze, vì vậy nó không bao giờ sai hơn 10%. Bạn có thể giảm autovacuum_analyze_scale_factor cho bảng bạn cần tính để autoanalyze chạy thường xuyên hơn.

Ước lượng số lượng kết quả truy vấn

Từ đầu tới giờ là các cách để tăng tốc độ đếm hàng của một bảng. Nhưng đôi khi bạn vẫn muốn biết có bao nhiêu hàng một câu truy vấn SELECT sẽ trả về mà không thực sự chạy nó.

Rõ ràng cách duy nhất để có câu trả lời chính xác là thực hiện câu truy vấn đó nhưng nếu có một cách ước lượng đủ tốt thì có thể sử dụng trình tối ưu hóa của PostgreSQL để ước lượng.

Chúng ta có thể ước lượng bằng cách sử dụng EXPLAIN trong PostgreSQL.

Sau đây là câu lệnh thử nhẹ, bạn nào thích tìm hiểu thêm thì tự tìm theo từ khóa EXPLAIN nhé. Mệt rồi!!

EXPLAIN ANALYZE SELECT * FROM items;

-- Response --
Seq Scan on items  (cost=0.00..36667.00 rows=2000000 width=37) (actual time=0.161..290.345 rows=2000000 loops=1)
Planning Time: 0.065 ms
Execution Time: 332.389 ms

Bài viết tham khảo:

  1. PostgreSQL COUNT(*) made fast

bxz :*

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 *