Trong thế giới Postgres, các chỉ mục rất quan trọng để điều hướng hiệu quả việc lưu trữ cơ sở dữ liệu (được gọi là heap). Postgres không hỗ trợ phân cụm cho nó và kiến trúc MVCC khiến bạn có nhiều phiên bản của cùng một bộ dữ liệu. Vì vậy, điều rất quan trọng là có thể tạo và duy trì các chỉ mục hiệu quả để hỗ trợ các ứng dụng.
Dưới đây là một số mẹo để tối ưu hóa và cải thiện việc sử dụng các chỉ mục.
Lưu ý: các truy vấn được hiển thị bên dưới hoạt động trên phiên bản chưa sửa đổi .
Sử dụng các chỉ mục bao phủ
Hãy xem xét yêu cầu trích xuất địa chỉ email cho người dùng không hoạt động. Bàn customer có một cột activevà truy vấn rất đơn giản:
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
QUERY PLAN
-----------------------------------------------------------
Seq Scan on customer (cost=0.00..16.49 rows=15 width=32)
Filter: (active = 0)
(2 rows)
Truy vấn gọi trình tự quét toàn bộ bảng customer. Hãy tạo một chỉ mục trên một cột active:
pagila=# CREATE INDEX idx_cust1 ON customer(active);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
QUERY PLAN
-----------------------------------------------------------------------------
Index Scan using idx_cust1 on customer (cost=0.28..12.29 rows=15 width=32)
Index Cond: (active = 0)
(2 rows)
Đã trợ giúp, lần quét tiếp theo đã chuyển thành "index scan". Điều này có nghĩa là Postgres sẽ thu thập dữ liệu chỉ mục "idx_cust1", rồi tiếp tục tìm kiếm trong heap bảng để đọc giá trị của các cột khác (trong trường hợp này là cột email) mà yêu cầu cần.
Các chỉ mục bao gồm được giới thiệu trong PostgreSQL 11. Chúng cho phép bạn bao gồm một hoặc nhiều cột bổ sung trong chính chỉ mục - giá trị của chúng được lưu trữ trong kho lưu trữ dữ liệu chỉ mục.
Nếu chúng tôi tận dụng tính năng này và thêm giá trị email bên trong chỉ mục, thì Postgres sẽ không cần phải tìm kiếm giá trị trong bảng heap. email. Hãy xem nếu điều này sẽ làm việc:
pagila=# CREATE INDEX idx_cust2 ON customer(active) INCLUDE (email);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
QUERY PLAN
----------------------------------------------------------------------------------
Index Only Scan using idx_cust2 on customer (cost=0.28..12.29 rows=15 width=32)
Index Cond: (active = 0)
(2 rows)
«Index Only Scan" cho chúng tôi biết rằng truy vấn hiện chỉ cần chỉ mục, điều này giúp tránh việc tất cả I/O của đĩa phải đọc vùng nhớ heap của bảng.
Các chỉ mục bao phủ hiện chỉ khả dụng cho cây B. Tuy nhiên, trong trường hợp này, nỗ lực bảo trì sẽ cao hơn.
Sử dụng chỉ mục một phần
Chỉ mục một phần chỉ lập chỉ mục một tập hợp con của các hàng trong bảng. Điều này tiết kiệm kích thước của các chỉ mục và làm cho quá trình quét nhanh hơn.
Giả sử chúng ta muốn lấy danh sách địa chỉ email của khách hàng ở California. Yêu cầu sẽ như thế này:
SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
which has a query plan that involves scanning both the tables that are joined:
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
QUERY PLAN
----------------------------------------------------------------------
Hash Join (cost=15.65..32.22 rows=9 width=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan on customer c (cost=0.00..14.99 rows=599 width=34)
-> Hash (cost=15.54..15.54 rows=9 width=4)
-> Seq Scan on address a (cost=0.00..15.54 rows=9 width=4)
Filter: (district = 'California'::text)
(6 rows)Những chỉ mục thông thường sẽ cung cấp cho chúng ta:
pagila=# CREATE INDEX idx_address1 ON address(district);
CREATE INDEX
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
QUERY PLAN
---------------------------------------------------------------------------------------
Hash Join (cost=12.98..29.55 rows=9 width=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan on customer c (cost=0.00..14.99 rows=599 width=34)
-> Hash (cost=12.87..12.87 rows=9 width=4)
-> Bitmap Heap Scan on address a (cost=4.34..12.87 rows=9 width=4)
Recheck Cond: (district = 'California'::text)
-> Bitmap Index Scan on idx_address1 (cost=0.00..4.34 rows=9 width=0)
Index Cond: (district = 'California'::text)
(8 rows)
Quét address đã được thay thế bằng quét chỉ mục idx_address1và sau đó quét đống address.
Vì đây là truy vấn thường xuyên và cần được tối ưu hóa nên chúng tôi có thể sử dụng chỉ mục một phần, chỉ mục này chỉ lập chỉ mục cho những hàng có địa chỉ mà quận ‘California’:
pagila=# CREATE INDEX idx_address2 ON address(address_id) WHERE district='California';
CREATE INDEX
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
QUERY PLAN
------------------------------------------------------------------------------------------------
Hash Join (cost=12.38..28.96 rows=9 width=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan on customer c (cost=0.00..14.99 rows=599 width=34)
-> Hash (cost=12.27..12.27 rows=9 width=4)
-> Index Only Scan using idx_address2 on address a (cost=0.14..12.27 rows=9 width=4)
(5 rows)
Bây giờ yêu cầu chỉ đọc idx_address2 và không chạm vào bàn address.
Sử dụng chỉ mục đa giá trị
Một số cột được lập chỉ mục có thể không chứa kiểu dữ liệu vô hướng. các loại cột như jsonb, arrays и tsvector chứa hỗn hợp hoặc nhiều giá trị. Nếu bạn cần lập chỉ mục các cột như vậy, bạn thường phải tìm kiếm trong tất cả các giá trị riêng lẻ trong các cột đó.
Chúng ta hãy thử tìm tiêu đề của tất cả các bộ phim có đoạn cắt từ những cảnh quay không thành công. Bàn film có một cột văn bản tên là special_features. Nếu một bộ phim có "thuộc tính đặc biệt" này thì cột sẽ chứa một phần tử ở dạng mảng văn bản Behind The Scenes. Để tìm kiếm tất cả các phim như vậy, chúng ta cần chọn tất cả các hàng có “Hậu trường” tại bất kỳ giá trị mảng special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Toán tử lồng nhau @> kiểm tra xem vế phải có phải là tập con của vế trái hay không.
Kế hoạch yêu cầu:
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on film (cost=0.00..67.50 rows=5 width=15)
Filter: (special_features @> '{"Behind The Scenes"}'::text[])
(2 rows)Yêu cầu quét toàn bộ vùng heap với chi phí là 67.
Hãy xem liệu chỉ mục cây B thông thường có giúp chúng tôi không:
pagila=# CREATE INDEX idx_film1 ON film(special_features);
CREATE INDEX
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on film (cost=0.00..67.50 rows=5 width=15)
Filter: (special_features @> '{"Behind The Scenes"}'::text[])
(2 rows)Chỉ số thậm chí không được xem xét. Chỉ mục cây B không nhận thức được sự tồn tại của các phần tử riêng lẻ trong các giá trị được lập chỉ mục.
Chúng tôi cần một chỉ số GIN.
pagila=# CREATE INDEX idx_film2 ON film USING GIN(special_features);
CREATE INDEX
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
QUERY PLAN
---------------------------------------------------------------------------
Bitmap Heap Scan on film (cost=8.04..23.58 rows=5 width=15)
Recheck Cond: (special_features @> '{"Behind The Scenes"}'::text[])
-> Bitmap Index Scan on idx_film2 (cost=0.00..8.04 rows=5 width=0)
Index Cond: (special_features @> '{"Behind The Scenes"}'::text[])
(4 rows)Chỉ mục GIN hỗ trợ ánh xạ các giá trị đơn so với các giá trị tổng hợp được lập chỉ mục, dẫn đến chi phí kế hoạch truy vấn giảm hơn một nửa.
Loại bỏ các chỉ mục trùng lặp
Các chỉ mục tích lũy theo thời gian và đôi khi một chỉ mục mới có thể chứa định nghĩa giống như một trong những chỉ mục trước đó. Bạn có thể sử dụng chế độ xem danh mục để có được các định nghĩa chỉ mục SQL mà con người có thể đọc được pg_indexes. Bạn cũng có thể dễ dàng tìm thấy các định nghĩa tương tự:
SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
FROM pg_indexes
GROUP BY defn
HAVING count(*) > 1;
And here’s the result when run on the stock pagila database:
pagila=# SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
pagila-# FROM pg_indexes
pagila-# GROUP BY defn
pagila-# HAVING count(*) > 1;
indexes | defn
------------------------------------------------------------------------+------------------------------------------------------------------
{payment_p2017_01_customer_id_idx,idx_fk_payment_p2017_01_customer_id} | CREATE INDEX ON public.payment_p2017_01 USING btree (customer_id
{payment_p2017_02_customer_id_idx,idx_fk_payment_p2017_02_customer_id} | CREATE INDEX ON public.payment_p2017_02 USING btree (customer_id
{payment_p2017_03_customer_id_idx,idx_fk_payment_p2017_03_customer_id} | CREATE INDEX ON public.payment_p2017_03 USING btree (customer_id
{idx_fk_payment_p2017_04_customer_id,payment_p2017_04_customer_id_idx} | CREATE INDEX ON public.payment_p2017_04 USING btree (customer_id
{payment_p2017_05_customer_id_idx,idx_fk_payment_p2017_05_customer_id} | CREATE INDEX ON public.payment_p2017_05 USING btree (customer_id
{idx_fk_payment_p2017_06_customer_id,payment_p2017_06_customer_id_idx} | CREATE INDEX ON public.payment_p2017_06 USING btree (customer_id
(6 rows)
Chỉ mục siêu tập hợp
Có thể xảy ra trường hợp bạn tích lũy nhiều chỉ mục, một trong số đó lập chỉ mục cho một tập hợp siêu cột mà các chỉ mục khác lập chỉ mục. Điều này có thể được mong muốn hoặc không - một superset có thể dẫn đến việc quét chỉ chỉ mục, điều này tốt nhưng nó có thể chiếm quá nhiều dung lượng hoặc truy vấn mà superset dự định tối ưu hóa không còn được sử dụng.
Nếu bạn cần tự động định nghĩa các chỉ mục như vậy, bạn có thể bắt đầu với từ cái bàn pg_catalog.
Các chỉ mục không được sử dụng
Khi các ứng dụng sử dụng cơ sở dữ liệu phát triển thì các truy vấn mà chúng sử dụng cũng vậy. Các chỉ mục đã thêm trước đó có thể không còn được sử dụng bởi bất kỳ truy vấn nào. Mỗi lần quét một chỉ mục, nó sẽ được người quản lý thống kê ghi lại và trong chế độ xem danh mục hệ thống pg_stat_user_indexes bạn có thể thấy giá trị idx_scan, đó là một bộ đếm tích lũy. Theo dõi giá trị này trong một khoảng thời gian (chẳng hạn như một tháng) sẽ cho biết chỉ mục nào không được sử dụng và có thể bị loại bỏ.
Đây là một truy vấn để lấy số lần quét hiện tại của tất cả các chỉ mục trong lược đồ ‘public’:
SELECT relname, indexrelname, idx_scan
FROM pg_catalog.pg_stat_user_indexes
WHERE schemaname = 'public';
with output like this:
pagila=# SELECT relname, indexrelname, idx_scan
pagila-# FROM pg_catalog.pg_stat_user_indexes
pagila-# WHERE schemaname = 'public'
pagila-# LIMIT 10;
relname | indexrelname | idx_scan
---------------+--------------------+----------
customer | customer_pkey | 32093
actor | actor_pkey | 5462
address | address_pkey | 660
category | category_pkey | 1000
city | city_pkey | 609
country | country_pkey | 604
film_actor | film_actor_pkey | 0
film_category | film_category_pkey | 0
film | film_pkey | 11043
inventory | inventory_pkey | 16048
(10 rows)Xây dựng lại các chỉ mục với ít khóa hơn
Các chỉ mục thường cần được xây dựng lại, chẳng hạn như khi chúng trở nên cồng kềnh và việc xây dựng lại có thể tăng tốc quá trình quét. Ngoài ra các chỉ mục có thể bị hỏng. Thay đổi các tham số chỉ mục cũng có thể yêu cầu xây dựng lại nó.
Cho phép tạo chỉ mục song song
Trong PostgreSQL 11, việc tạo chỉ mục B-Tree là đồng thời. Để tăng tốc quá trình tạo, có thể sử dụng một số công nhân song song. Tuy nhiên, hãy đảm bảo các tùy chọn cấu hình này được đặt chính xác:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Các giá trị mặc định quá nhỏ. Lý tưởng nhất là những con số này sẽ tăng cùng với số lượng lõi của bộ xử lý. Đọc thêm trong .
Tạo chỉ mục nền
Bạn có thể tạo một chỉ mục trong nền bằng cách sử dụng tùy chọn CONCURRENTLY đội CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXQuy trình tạo chỉ mục này khác với quy trình thông thường ở chỗ nó không yêu cầu khóa bảng và do đó không chặn các thao tác ghi. Mặt khác, nó mất nhiều thời gian hơn và tiêu tốn nhiều tài nguyên hơn.
Postgres cung cấp rất nhiều tính linh hoạt để tạo chỉ mục và cách giải quyết bất kỳ trường hợp đặc biệt nào, cũng như cách quản lý cơ sở dữ liệu trong trường hợp ứng dụng của bạn phát triển bùng nổ. Chúng tôi hy vọng những mẹo này sẽ giúp bạn truy vấn nhanh chóng và cơ sở dữ liệu của bạn sẵn sàng mở rộng.
Nguồn: www.habr.com