個(gè)人中心
退出
利用 PostgreSQL 部分索引提高查詢語(yǔ)句的性能
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫(kù)管理與開(kāi)發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫(kù)架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目錄
大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。
在 PostgreSQL 數(shù)據(jù)庫(kù)中，部分索引（partial index）是指對(duì)表中滿足特定條件的數(shù)據(jù)行進(jìn)行索引。由于它不需要對(duì)全部數(shù)據(jù)進(jìn)行索引，因此索引會(huì)更小，在特定場(chǎng)景下通過(guò)部分索引查找數(shù)據(jù)時(shí)性能會(huì)更好。本文就給大家介紹一下 PostgreSQL 中的部分索引功能。
如果覺(jué)得文章有用，歡迎評(píng)論??、點(diǎn)贊??、推薦??
PostgreSQL 在創(chuàng)建索引時(shí)可以通過(guò)一個(gè) WHERE 子句指定需要索引的數(shù)據(jù)行，從而創(chuàng)建一個(gè)部分索引。例如，對(duì)于以下訂單表 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 個(gè)訂單，通常絕大部的訂單都處于完成狀態(tài)。一般情況下，我們只需要針對(duì)某個(gè)用戶未完成的訂單進(jìn)行查詢跟蹤，因此可以創(chuàng)建一個(gè)基于用戶編號(hào)和狀態(tài)的部分索引：
CREATE INDEX full_idx ON orders (customer_id, status);
然后使用 EXPLAIN ANALYZE 命令查看 SELECT 語(yǔ)句的執(zhí)行計(jì)劃：
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 |
輸出結(jié)果顯示利用索引 full_idx 掃描了 110 行，然后通過(guò) status 過(guò)濾掉了 109 行，而不是直接通過(guò)索引掃描出所需的數(shù)據(jù)。
此時(shí)，我們可以查看一下索引 full_idx 占用的空間大?。?br>
select pg_size_pretty(pg_table_size('full_idx'));
pg_size_pretty|
--------------|
30 MB |
接下來(lái)我們?cè)賱?chuàng)建一個(gè)部分索引，只包含未完成的訂單數(shù)據(jù)，從而減少索引的數(shù)據(jù)量：
CREATE INDEX partial_idx ON orders (customer_id)
WHERE status != 'completed';
索引 partial_idx 中只有 customer_id 字段，不需要 status 字段。然后再次執(zhí)行相同的 EXPLAIN ANALYZE 語(yǔ)句，查看執(zhí)行計(jì)劃：
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 |
輸出結(jié)果顯示 PostgreSQL 執(zhí)行計(jì)劃選擇了索引 partial_idx，而不是 full_idx；因?yàn)檫@樣性能更好，只需要掃描 1 行記錄就可以得到結(jié)果。
同樣可以查看一下索引 partial_idx 占用的空間大?。?br>
select pg_size_pretty(pg_table_size('partial_idx'));
pg_size_pretty|
--------------|
352 kB |
索引只有 352 KB，而不是 30 MB，因?yàn)榻^大多數(shù)訂單都處于完成狀態(tài)。
另外，部分索引還可以用于實(shí)現(xiàn)其他的功能。例如，我們可以將索引 partial_idx 定義為唯一索引，從而實(shí)現(xiàn)每個(gè)用戶只能存在一個(gè)未完成訂單的約束。
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 錯(cuò)誤 [23505]: 錯(cuò)誤: 重復(fù)鍵違反唯一約束"partial_idx"
詳細(xì)：鍵值"(customer_id)=(1)" 已經(jīng)存在
用戶必須完成一個(gè)訂單之后才能繼續(xù)生成新的訂單。
??更多關(guān)于 PostgreSQL 索引和優(yōu)化的內(nèi)容，可以參考這篇文章。
分類導(dǎo)航