個人中心
退出
利用 PostgreSQL 部分索引提高查詢語句的性能
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學，十多年數據庫管理與開發(fā)經驗，目前在一家全球性的金融公司從事數據庫架構設計。CSDN學院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目錄
大家好，我是只談技術不剪發(fā)的 Tony 老師。
在 PostgreSQL 數據庫中，部分索引（partial index）是指對表中滿足特定條件的數據行進行索引。由于它不需要對全部數據進行索引，因此索引會更小，在特定場景下通過部分索引查找數據時性能會更好。本文就給大家介紹一下 PostgreSQL 中的部分索引功能。
如果覺得文章有用，歡迎評論??、點贊??、推薦??
PostgreSQL 在創(chuàng)建索引時可以通過一個 WHERE 子句指定需要索引的數據行，從而創(chuàng)建一個部分索引。例如，對于以下訂單表 orders：
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
status TEXT
);
INSERT INTO orders (id, customer_id, status)
SELECT
i,
(random()*10000)::INT,
CASE (random() * 100)::int
WHEN 0 THEN 'pending'
WHEN 1 THEN 'shipped'
ELSE 'completed'
END
FROM generate_series(1, 1000000) i;
該表中總共有 1000000 個訂單，通常絕大部的訂單都處于完成狀態(tài)。一般情況下，我們只需要針對某個用戶未完成的訂單進行查詢跟蹤，因此可以創(chuàng)建一個基于用戶編號和狀態(tài)的部分索引：
CREATE INDEX full_idx ON orders (customer_id, status);
然后使用 EXPLAIN ANALYZE 命令查看 SELECT 語句的執(zhí)行計劃：
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 5678
AND status != 'completed';
QUERY PLAN |
---------------------------------------------------------------------------------------------------------------------|
Bitmap Heap Scan on orders (cost=5.18..369.08 rows=1 width=17) (actual time=33.661..34.040 rows=1 loops=1) |
Recheck Cond: (customer_id = 5678) |
Filter: (status <> 'completed'::text) |
Rows Removed by Filter: 109 |
Heap Blocks: exact=109 |
-> Bitmap Index Scan on full_idx (cost=0.00..5.17 rows=100 width=0) (actual time=33.526..33.526 rows=110 loops=1)|
Index Cond: (customer_id = 5678) |
Planning Time: 1.252 ms |
Execution Time: 34.180 ms |
輸出結果顯示利用索引 full_idx 掃描了 110 行，然后通過 status 過濾掉了 109 行，而不是直接通過索引掃描出所需的數據。
此時，我們可以查看一下索引 full_idx 占用的空間大小：
select pg_size_pretty(pg_table_size('full_idx'));
pg_size_pretty|
--------------|
30 MB |
接下來我們再創(chuàng)建一個部分索引，只包含未完成的訂單數據，從而減少索引的數據量：
CREATE INDEX partial_idx ON orders (customer_id)
WHERE status != 'completed';
索引 partial_idx 中只有 customer_id 字段，不需要 status 字段。然后再次執(zhí)行相同的 EXPLAIN ANALYZE 語句，查看執(zhí)行計劃：
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 5678
AND status != 'completed';
QUERY PLAN |
-------------------------------------------------------------------------------------------------------------------|
Index Scan using partial_idx on orders (cost=0.29..8.30 rows=1 width=17) (actual time=0.246..0.249 rows=1 loops=1)|
Index Cond: (customer_id = 5678) |
Planning Time: 0.397 ms |
Execution Time: 0.295 ms |
輸出結果顯示 PostgreSQL 執(zhí)行計劃選擇了索引 partial_idx，而不是 full_idx；因為這樣性能更好，只需要掃描 1 行記錄就可以得到結果。
同樣可以查看一下索引 partial_idx 占用的空間大?。?br>
select pg_size_pretty(pg_table_size('partial_idx'));
pg_size_pretty|
--------------|
352 kB |
索引只有 352 KB，而不是 30 MB，因為絕大多數訂單都處于完成狀態(tài)。
另外，部分索引還可以用于實現(xiàn)其他的功能。例如，我們可以將索引 partial_idx 定義為唯一索引，從而實現(xiàn)每個用戶只能存在一個未完成訂單的約束。
DROP INDEX partial_idx;
TRUNCATE TABLE orders;
CREATE UNIQUE INDEX partial_idx ON orders (customer_id)
WHERE status != 'completed';
INSERT INTO orders(id, customer_id, status) VALUES (1, 1, 'pending');
INSERT INTO orders(id, customer_id, status) VALUES (2, 1, 'pending');
SQL 錯誤 [23505]: 錯誤: 重復鍵違反唯一約束"partial_idx"
詳細：鍵值"(customer_id)=(1)" 已經存在
用戶必須完成一個訂單之后才能繼續(xù)生成新的訂單。
??更多關于 PostgreSQL 索引和優(yōu)化的內容，可以參考這篇文章。
分類導航