在 Postgres 世界中,索引對於數據庫存儲(稱為“堆”)的高效導航至關重要。 Postgres 不支持它的集群,並且 MVCC 體系結構導致您最終得到相同元組的多個版本。 因此,能夠創建和維護高效的索引以支持應用程序非常重要。
這裡有一些優化和改進索引使用的技巧。
注意:下面顯示的查詢適用於未修改的
使用覆蓋索引
讓我們看一下為非活動用戶提取電子郵件地址的請求。 桌子 customer
有一列 active
,查詢很簡單:
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)
查詢調用全表掃描序列 customer
. 讓我們在列上創建索引 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)
它有所幫助,隨後的掃描變成了“index scan
”。 這意味著 Postgres 將掃描索引“idx_cust1
",然後繼續搜索表堆,讀取其他列的值(本例中的列 email
) 查詢需要的。
PostgreSQL 11 引入了覆蓋索引。 它們允許您在索引本身中包含一個或多個附加列——它們的值存儲在索引數據存儲中。
如果我們利用這個特性並在索引中添加電子郵件值,那麼 Postgres 就不需要在表堆中搜索該值。 email
. 讓我們看看這是否可行:
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
' 告訴我們查詢現在只需要一個索引,這有助於避免所有磁盤 I/O 讀取表堆。
覆蓋索引目前僅適用於 B 樹。 但是,在這種情況下,維護工作量會更高。
使用部分索引
部分索引僅索引表中行的子集。 這節省了索引的大小並使掃描速度更快。
假設我們想要獲取我們在加利福尼亞的客戶的電子郵件地址列表。 請求將是這樣的:
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)
普通索引會給我們什麼:
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)
掃描 address
已被索引掃描取代 idx_address1
然後掃描堆 address
.
由於這是一個頻繁的查詢,需要優化,我們可以使用部分索引,它只索引地址在該區的那些行 ‘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)
現在查詢只讀 idx_address2
並且不碰桌子 address
.
使用多值索引
一些要索引的列可能不包含標量數據類型。 列類型如 jsonb
, arrays
и tsvector
包含複合值或多個值。 如果需要索引此類列,通常必須搜索這些列中的所有單個值。
讓我們試著找到所有包含不成功鏡頭剪輯的電影的標題。 桌子 film
有一個名為 special_features
. 如果電影具有此“特殊屬性”,則該列包含作為文本數組的元素 Behind The Scenes
. 要搜索所有此類電影,我們需要選擇所有帶有“幕後花絮”的行 任何 數組值 special_features
:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
嵌套運算符 @>
檢查右側是否是左側的子集。
要求計劃:
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)
它要求以 67 的成本進行完整的堆掃描。
讓我們看看常規 B 樹索引是否對我們有幫助:
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)
甚至沒有考慮索引。 B 樹索引不知道索引值中單個元素的存在。
我們需要一個 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)
GIN 索引支持將單個值映射到索引複合值,從而使查詢計劃成本減半。
擺脫重複索引
索引隨時間累積,有時新索引可能包含與先前索引之一相同的定義。 您可以使用目錄視圖來獲取人類可讀的索引 SQL 定義。 pg_indexes
. 您還可以輕鬆找到相同的定義:
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)
超集索引
最終可能會出現許多索引,其中一個索引索引其他索引的列的超集。 這可能是合意的,也可能不是合意的——超集可能會導致僅索引掃描,這很好,但它可能會佔用太多空間,或者不再使用超集旨在優化的查詢。
如果您需要自動定義此類索引,您可以從 pg_catalog
.
未使用的索引
隨著使用數據庫的應用程序的發展,它們使用的查詢也在不斷發展。 之前添加的索引可能不再被任何查詢使用。 每次掃描一個索引,都會被統計管理器標記,在系統目錄視圖中 pg_stat_user_indexes
你可以看到價值 idx_scan
,這是一個累積計數器。 在一段時間內(比如說一個月)跟踪這個值,可以很好地了解哪些索引沒有被使用並且可以被刪除。
這是獲取架構中所有索引的當前掃描計數的查詢 ‘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)
用更少的鎖重建索引
索引經常需要重建,例如當它們變得臃腫時,重建可以加快掃描速度。 索引也可能被破壞。 更改索引參數可能還需要重建它。
啟用並行索引創建
在 PostgreSQL 11 中,創建 B-Tree 索引是並發的。 為了加快創建過程,可以使用多個並行工作器。 但是,請確保正確設置了這些配置選項:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;
默認值太小了。 理想情況下,這些數字應隨著處理器核心數量的增加而增加。 閱讀更多
後台索引創建
您可以使用該選項在後台創建索引 CONCURRENTLY
命令 CREATE INDEX
:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEX
此索引創建過程與通常的過程不同,因為它不需要鎖定表,因此不會阻止寫操作。 另一方面,它需要更多的時間和消耗更多的資源。
Postgres 提供了很大的靈活性來創建索引和解決任何特殊情況的方法,以及在應用程序爆炸式增長的情況下管理數據庫的方法。 我們希望這些技巧能幫助您快速查詢並讓您的數據庫做好擴展準備。
來源: www.habr.com