Vài tháng trước - công cộng đến PostgreSQL.
Bạn đã sử dụng nó hơn 6000 lần nhưng có một tính năng tiện dụng có thể chưa được chú ý là manh mối cấu trúc, trông giống như thế này:
Hãy lắng nghe họ và những yêu cầu của bạn sẽ “trở nên suôn sẻ và mượt mà”. 🙂
Nhưng nghiêm túc mà nói, nhiều tình huống khiến yêu cầu bị chậm và ngốn tài nguyên mang tính điển hình và có thể được nhận biết qua cấu trúc, số liệu của kế hoạch.
Trong trường hợp này, mỗi nhà phát triển riêng lẻ không phải tự mình tìm kiếm tùy chọn tối ưu hóa mà chỉ dựa vào kinh nghiệm của mình - chúng tôi có thể cho anh ấy biết điều gì đang xảy ra ở đây, lý do có thể là gì và cách tiếp cận giải pháp. Đó là những gì chúng tôi đã làm.
Chúng ta hãy xem xét kỹ hơn những trường hợp này - cách chúng được xác định và chúng đưa đến những khuyến nghị gì.
Để đắm mình vào chủ đề tốt hơn, trước tiên bạn có thể nghe khối tương ứng từ , và chỉ sau đó chuyển sang phân tích chi tiết từng ví dụ:
#1: chỉ mục “phân loại dưới”
Khi phát sinh
Hiển thị hóa đơn mới nhất cho khách hàng "LLC Kolokolchik".
Làm thế nào để xác định
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
Khuyến nghị
Chỉ mục được sử dụng mở rộng với các trường sắp xếp.
Ví dụ:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key
SELECT
*
FROM
tbl
WHERE
fk_cli = 1 -- отбор по конкретной связи
ORDER BY
pk DESC -- хотим всего одну "последнюю" запись
LIMIT 1;
Bạn có thể nhận thấy ngay rằng hơn 100 bản ghi đã bị trừ khỏi chỉ mục, sau đó tất cả đều được sắp xếp và chỉ còn lại một bản ghi.
Sửa chữa:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Ngay cả trên một mẫu nguyên thủy như vậy - Nhanh hơn 8.5 lần và số lần đọc ít hơn 33 lần. Bạn càng có nhiều “sự thật” cho mỗi giá trị thì hiệu quả càng rõ ràng fk.
Tôi lưu ý rằng chỉ mục như vậy sẽ hoạt động như một chỉ mục “tiền tố” không tệ hơn trước đối với các truy vấn khác có fk, sắp xếp theo đâu pk không có và không có (bạn có thể đọc thêm về điều này ). Bao gồm, nó sẽ cung cấp bình thường hỗ trợ khóa ngoại rõ ràng trên lĩnh vực này.
#2: giao điểm chỉ mục (BitmapAnd)
Khi phát sinh
Hiển thị tất cả các thỏa thuận dành cho khách hàng “LLC Kolokolchik”, được ký kết thay mặt cho “NAO Buttercup”.
Làm thế nào để xác định
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanKhuyến nghị
sáng tạo chỉ số tổng hợp theo các trường từ cả hai trường gốc hoặc mở rộng một trong các trường hiện có bằng các trường từ trường thứ hai.
Ví dụ:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 100)::integer fk_org -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_org); -- индекс для foreign key
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key
SELECT
*
FROM
tbl
WHERE
(fk_org, fk_cli) = (1, 999); -- отбор по конкретной паре
Sửa chữa:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Lợi ích ở đây nhỏ hơn vì bản thân Bitmap Heap Scan khá hiệu quả. Nhưng dù sao Nhanh hơn 7 lần và số lần đọc ít hơn 2.5 lần.
#3: Hợp nhất các chỉ mục (BitmapOr)
Khi phát sinh
Hiển thị 20 yêu cầu cũ nhất đầu tiên của “chúng tôi” hoặc chưa được chỉ định để xử lý, ưu tiên yêu cầu của bạn.
Làm thế nào để xác định
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanKhuyến nghị
Để sử dụng UNION [TẤT CẢ] để kết hợp các truy vấn con cho từng khối OR của điều kiện.
Ví dụ:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, CASE
WHEN random() < 1::real/16 THEN NULL -- с вероятностью 1:16 запись "ничья"
ELSE (random() * 100)::integer -- 100 разных внешних ключей
END fk_own;
CREATE INDEX ON tbl(fk_own, pk); -- индекс с "вроде как подходящей" сортировкой
SELECT
*
FROM
tbl
WHERE
fk_own = 1 OR -- свои
fk_own IS NULL -- ... или "ничьи"
ORDER BY
pk
, (fk_own = 1) DESC -- сначала "свои"
LIMIT 20;
Sửa chữa:
(
SELECT
*
FROM
tbl
WHERE
fk_own = 1 -- сначала "свои" 20
ORDER BY
pk
LIMIT 20
)
UNION ALL
(
SELECT
*
FROM
tbl
WHERE
fk_own IS NULL -- потом "ничьи" 20
ORDER BY
pk
LIMIT 20
)
LIMIT 20; -- но всего - 20, больше и не надо
Chúng tôi đã lợi dụng thực tế là tất cả 20 bản ghi cần thiết đều được nhận ngay lập tức trong khối đầu tiên, do đó, khối thứ hai, với Bitmap Heap Scan “đắt tiền” hơn, thậm chí còn không được thực thi - cuối cùng Nhanh hơn 22 lần, đọc ít hơn 44 lần!
Một câu chuyện chi tiết hơn về phương pháp tối ưu hóa này trên các ví dụ cụ thể có thể được đọc trong bài viết и .
Phiên bản tổng quát lựa chọn theo thứ tự dựa trên một số phím (và không chỉ cặp const/NULL) được thảo luận trong bài viết .
#4: Chúng ta đọc rất nhiều thứ không cần thiết
Khi phát sinh
Theo quy định, nó phát sinh khi bạn muốn “đính kèm bộ lọc khác” vào một yêu cầu đã có sẵn.
“Và bạn không có cái giống nhau, nhưng với các nút ngọc trai? " phim "Cánh tay kim cương"
Ví dụ: sửa đổi tác vụ ở trên, hiển thị 20 yêu cầu “quan trọng” cũ nhất đầu tiên để xử lý, bất kể mục đích của chúng là gì.
Làm thế nào để xác định
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
Khuyến nghị
Tạo [thêm] chuyên biệt lập chỉ mục với điều kiện WHERE hoặc bao gồm các trường bổ sung trong chỉ mục.
Nếu điều kiện bộ lọc là "tĩnh" cho mục đích của bạn - nghĩa là không hàm ý mở rộng danh sách các giá trị trong tương lai - tốt hơn là sử dụng chỉ mục WHERE. Các trạng thái boolean/enum khác nhau rất phù hợp với loại này.
Nếu điều kiện lọc có thể mang những ý nghĩa khác nhau, thì tốt hơn là bạn nên mở rộng chỉ mục với các trường này - như trong trường hợp với BitmapAnd ở trên.
Ví dụ:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, CASE
WHEN random() < 1::real/16 THEN NULL
ELSE (random() * 100)::integer -- 100 разных внешних ключей
END fk_own
, (random() < 1::real/50) critical; -- 1:50, что заявка "критичная"
CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);
SELECT
*
FROM
tbl
WHERE
critical
ORDER BY
pk
LIMIT 20;
Sửa chữa:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Như bạn có thể thấy, tính năng lọc đã hoàn toàn biến mất khỏi kế hoạch và yêu cầu đã trở thành nhanh gấp 5 lần.
#5: bảng thưa thớt
Khi phát sinh
Nhiều nỗ lực khác nhau nhằm tạo hàng đợi xử lý tác vụ của riêng bạn, khi một số lượng lớn các cập nhật/xóa bản ghi trên bảng dẫn đến tình trạng một số lượng lớn bản ghi "chết".
Làm thế nào để xác định
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Khuyến nghị
Thực hiện thủ công thường xuyên CHÂN KHÔNG [ĐẦY ĐỦ] hoặc đạt được sự đào tạo thường xuyên đầy đủ bằng cách tinh chỉnh các tham số của nó, bao gồm .
Trong hầu hết các trường hợp, những vấn đề như vậy là do thành phần truy vấn kém khi gọi từ logic nghiệp vụ như những vấn đề được thảo luận trong .
Nhưng bạn cần hiểu rằng ngay cả VACUUM FULL không phải lúc nào cũng có ích. Đối với những trường hợp như vậy, bạn nên làm quen với thuật toán trong bài viết .
#6: Đọc từ “giữa” mục lục
Khi phát sinh
Có vẻ như chúng tôi đã đọc một chút và mọi thứ đều được lập chỉ mục và chúng tôi không lọc ra bất kỳ ai quá mức - nhưng chúng tôi vẫn đọc được nhiều trang hơn đáng kể so với mong muốn.
Làm thế nào để xác định
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Khuyến nghị
Hãy xem xét kỹ cấu trúc của chỉ mục được sử dụng và các trường khóa được chỉ định trong truy vấn - rất có thể một phần của chỉ mục không được thiết lập. Rất có thể bạn sẽ phải tạo một chỉ mục tương tự nhưng không có các trường tiền tố hoặc .
Ví dụ:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 100)::integer fk_org -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_org, fk_cli); -- все почти как в #2
-- только вот отдельный индекс по fk_cli мы уже посчитали лишним и удалили
SELECT
*
FROM
tbl
WHERE
fk_cli = 999 -- а fk_org не задано, хотя стоит в индексе раньше
LIMIT 20;
Mọi thứ có vẻ ổn, ngay cả theo chỉ mục, nhưng có phần đáng ngờ - đối với mỗi bản ghi trong số 20 bản ghi được đọc, chúng tôi phải trừ 4 trang dữ liệu, 32KB mỗi bản ghi - điều đó có đậm không? Và tên chỉ mục tbl_fk_org_fk_cli_idx đáng suy nghĩ.
Sửa chữa:
CREATE INDEX ON tbl(fk_cli);
Đột nhiên - Nhanh hơn 10 lần và đọc ít hơn 4 lần!
Bạn có thể xem các ví dụ khác về các tình huống sử dụng chỉ mục không hiệu quả trong bài viết .
#7: CTE × CTE
Khi phát sinh
Trong yêu cầu đạt điểm CTE “béo” từ các bảng khác nhau và sau đó quyết định thực hiện việc đó giữa chúng JOIN.
Trường hợp này phù hợp với các phiên bản dưới v12 hoặc các yêu cầu có WITH MATERIALIZED.
Làm thế nào để xác định
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
Khuyến nghị
Phân tích cẩn thận yêu cầu - và ? Nếu có thì áp dụng "từ điển" trong hstore/json theo mô hình được mô tả ở .
#8: trao đổi sang đĩa (viết tạm thời)
Khi phát sinh
Việc xử lý một lần (sắp xếp hoặc duy nhất) một số lượng lớn bản ghi không phù hợp với bộ nhớ được phân bổ cho việc này.
Làm thế nào để xác định
-> *
&& temp written > 0Khuyến nghị
Nếu dung lượng bộ nhớ được sử dụng bởi thao tác không vượt quá nhiều giá trị được chỉ định của tham số , đáng để sửa lại. Bạn có thể ngay trong config cho mọi người, hoặc bạn có thể thông qua SET [LOCAL] cho một yêu cầu/giao dịch cụ thể.
Ví dụ:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Sửa chữa:
SET work_mem = '128MB'; -- перед выполнением запроса
Vì những lý do hiển nhiên, nếu chỉ sử dụng bộ nhớ chứ không sử dụng đĩa thì truy vấn sẽ được thực thi nhanh hơn nhiều. Đồng thời, một phần tải trọng từ ổ cứng HDD cũng được loại bỏ.
Nhưng bạn cần hiểu rằng không phải lúc nào bạn cũng có thể phân bổ rất nhiều bộ nhớ - đơn giản là sẽ không đủ cho tất cả mọi người.
#9: số liệu thống kê không liên quan
Khi phát sinh
Họ đổ rất nhiều thứ vào cơ sở dữ liệu cùng một lúc nhưng không có thời gian để xua đuổi nó ANALYZE.
Làm thế nào để xác định
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10Khuyến nghị
Thực hiện nó ANALYZE.
Tình huống này được mô tả chi tiết hơn trong .
#10: “có gì đó không ổn”
Khi phát sinh
Phải chờ khóa do yêu cầu cạnh tranh áp đặt hoặc không có đủ tài nguyên phần cứng CPU/trình giám sát ảo.
Làm thế nào để xác định
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
Khuyến nghị
Sử dụng bên ngoài hệ thống giám sát máy chủ để chặn hoặc tiêu thụ tài nguyên bất thường. Chúng tôi đã nói về phiên bản tổ chức quy trình này cho hàng trăm máy chủ и .
Nguồn: www.habr.com