PostgreSQL資料庫多列複合索引的欄位順序選擇原理
標籤
PostgreSQL , 多列索引 , 複合索引 , 驅動列 , 順序 , 等值查詢 , 範圍掃描 , 離散值 , 連續值 , 單列索引 , bitmap index scan
背景
當需要建立多列複合索引時，應該使用什麼樣的順序呢？
多列複合索引的組織結構與單列欄位索引結構類似，只是需要按索引內表示式指定的順序編排。
例如
create index idx on tbl using btree (udf(c1) desc, c2 , c3 desc nulls last);
那麼會按定義的順序編排。
舉個例子
postgres=# create unlogged table tab1 (id int, c1 int, c2 int);
CREATE TABLE
postgres=# insert into tab1 select id, random()*9, 1 from generate_series(1,1000000) t(id);
INSERT 0 1000000
postgres=# insert into tab1 select id, random()*9, 3 from generate_series(1,1000000) t(id);
INSERT 0 1000000
postgres=# insert into tab1 values (1,1,2);
INSERT 0 1
postgres=# insert into tab1 select id, 1, 3 from generate_series(1,1000000) t(id);
INSERT 0 1000000
postgres=# insert into tab1 select id, 1, 1 from generate_series(1,1000000) t(id);
INSERT 0 1000000
c1=1, c2=2的記錄只有一條
1、搜尋c1=1, c2=2，只需要掃描4個BLOCK
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tab1 where c1=1 and c2=2;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Index Scan using idx_tab1 on public.tab1 (cost=0.43..2.38 rows=1 width=12) (actual time=0.017..0.018 rows=1 loops=1)
Output: id, c1, c2
Index Cond: ((tab1.c1 = 1) AND (tab1.c2 = 2))
Buffers: shared hit=4 （4個BLOCK，包括 root page, branch page, leaf page, HEAP PAGE）
Planning time: 0.214 ms
Execution time: 0.042 ms
(6 rows)
2、搜尋其他的，需要掃描很多BLOCK。
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tab1 where c1=1 and c2=3;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_tab1 on public.tab1 (cost=0.43..46108.77 rows=1109400 width=12) (actual time=0.026..237.712 rows=1111519 loops=1)
Output: id, c1, c2
Index Cond: ((tab1.c1 = 1) AND (tab1.c2 = 3))
Buffers: shared hit=22593 read=303 (包括heap page)
Planning time: 0.089 ms
Execution time: 328.249 ms
(6 rows)
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tab1 where c1=1 and c2=1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_tab1 on public.tab1 (cost=0.43..46108.77 rows=1109400 width=12) (actual time=0.022..238.399 rows=1110527 loops=1)
Output: id, c1, c2
Index Cond: ((tab1.c1 = 1) AND (tab1.c2 = 1))
Buffers: shared hit=22582 read=299 (包括heap page)
Planning time: 0.094 ms
Execution time: 329.331 ms
(6 rows)
那麼如何知道資料庫是快速定位到c1=1, c2=2的記錄的呢？
可以使用pageinspect來看一看索引內部的結構
postgres=# create extension pageinspect ;
CREATE EXTENSION
檢視索引內部結構，看看如何通過複合索引快速定位一條記錄
首先要檢視索引的第一個PAGE，即metapage，它會告訴你這個索引有幾層，ROOT PAGE在哪裡
postgres=# SELECT * FROM bt_metap(`idx_tab1`);
magic | version | root | level | fastroot | fastlevel
--------+---------+------+-------+----------+-----------
340322 | 2 | 290 | 2 | 290 | 2
(1 row)
表示這個索引除去ROOT節點有2層，ROOT節點是290號資料塊。
檢視根頁
postgres=# SELECT * FROM bt_page_items(`idx_tab1`, 290);
itemoffset | ctid | itemlen | nulls | vars | data
------------+-----------+---------+-------+------+-------------------------
1 | (3,1) | 8 | f | f |
2 | (289,1) | 16 | f | f | 00 00 00 00 03 00 00 00
3 | (12341,1) | 16 | f | f | 01 00 00 00 01 00 00 00
4 | (12124,1) | 16 | f | f | 01 00 00 00 01 00 00 00
5 | (11907,1) | 16 | f | f | 01 00 00 00 01 00 00 00
6 | (11690,1) | 16 | f | f | 01 00 00 00 01 00 00 00
7 | (11473,1) | 16 | f | f | 01 00 00 00 01 00 00 00
8 | (11256,1) | 16 | f | f | 01 00 00 00 01 00 00 00
9 | (11039,1) | 16 | f | f | 01 00 00 00 01 00 00 00
10 | (10822,1) | 16 | f | f | 01 00 00 00 01 00 00 00
11 | (10605,1) | 16 | f | f | 01 00 00 00 01 00 00 00
12 | (10388,1) | 16 | f | f | 01 00 00 00 01 00 00 00
13 | (10171,1) | 16 | f | f | 01 00 00 00 01 00 00 00
14 | (9954,1) | 16 | f | f | 01 00 00 00 01 00 00 00
15 | (9737,1) | 16 | f | f | 01 00 00 00 01 00 00 00
16 | (9520,1) | 16 | f | f | 01 00 00 00 01 00 00 00
17 | (9303,1) | 16 | f | f | 01 00 00 00 01 00 00 00
18 | (9086,1) | 16 | f | f | 01 00 00 00 01 00 00 00
19 | (575,1) | 16 | f | f | 01 00 00 00 01 00 00 00
20 | (8866,1) | 16 | f | f | 01 00 00 00 03 00 00 00
21 | (8649,1) | 16 | f | f | 01 00 00 00 03 00 00 00
22 | (8432,1) | 16 | f | f | 01 00 00 00 03 00 00 00
23 | (8215,1) | 16 | f | f | 01 00 00 00 03 00 00 00
24 | (7998,1) | 16 | f | f | 01 00 00 00 03 00 00 00
25 | (7781,1) | 16 | f | f | 01 00 00 00 03 00 00 00
26 | (7564,1) | 16 | f | f | 01 00 00 00 03 00 00 00
27 | (7347,1) | 16 | f | f | 01 00 00 00 03 00 00 00
28 | (7130,1) | 16 | f | f | 01 00 00 00 03 00 00 00
29 | (6913,1) | 16 | f | f | 01 00 00 00 03 00 00 00
30 | (6696,1) | 16 | f | f | 01 00 00 00 03 00 00 00
31 | (6479,1) | 16 | f | f | 01 00 00 00 03 00 00 00
32 | (6262,1) | 16 | f | f | 01 00 00 00 03 00 00 00
33 | (6045,1) | 16 | f | f | 01 00 00 00 03 00 00 00
34 | (5828,1) | 16 | f | f | 01 00 00 00 03 00 00 00
35 | (5611,1) | 16 | f | f | 01 00 00 00 03 00 00 00
36 | (860,1) | 16 | f | f | 01 00 00 00 03 00 00 00
37 | (1145,1) | 16 | f | f | 02 00 00 00 01 00 00 00
38 | (1430,1) | 16 | f | f | 02 00 00 00 03 00 00 00
39 | (1715,1) | 16 | f | f | 03 00 00 00 01 00 00 00
40 | (2000,1) | 16 | f | f | 03 00 00 00 03 00 00 00
41 | (2285,1) | 16 | f | f | 04 00 00 00 01 00 00 00
42 | (2570,1) | 16 | f | f | 04 00 00 00 03 00 00 00
43 | (2855,1) | 16 | f | f | 05 00 00 00 01 00 00 00
44 | (3140,1) | 16 | f | f | 05 00 00 00 03 00 00 00
45 | (3425,1) | 16 | f | f | 06 00 00 00 01 00 00 00
46 | (3710,1) | 16 | f | f | 06 00 00 00 03 00 00 00
47 | (3995,1) | 16 | f | f | 07 00 00 00 01 00 00 00
48 | (4280,1) | 16 | f | f | 07 00 00 00 03 00 00 00
49 | (4565,1) | 16 | f | f | 07 00 00 00 03 00 00 00
50 | (4850,1) | 16 | f | f | 08 00 00 00 01 00 00 00
51 | (5135,1) | 16 | f | f | 08 00 00 00 03 00 00 00
52 | (5420,1) | 16 | f | f | 09 00 00 00 03 00 00 00
(52 rows)
索引的非leaf節點，data表示這個PAGE的最小邊界值，最左邊的頁沒有最小值
如何快速找到c1=1 and c2=2，通過以上資訊，可以知道1,2在575號資料塊中。
19 | (575,1) | 16 | f | f | 01 00 00 00 01 00 00 00
20 | (8866,1) | 16 | f | f | 01 00 00 00 03 00 00 00
繼續檢視575號索引頁的內容。這個頁是第一層（不是最後一層），分支節點
第一條表示與當前頁右邊的相鄰頁，data是它的最小值。第二條表示當前頁左邊的相鄰頁，data為空。
postgres=# SELECT * FROM bt_page_items(`idx_tab1`, 575);
itemoffset | ctid | itemlen | nulls | vars | data
------------+----------+---------+-------+------+-------------------------
1 | (8712,1) | 16 | f | f | 01 00 00 00 03 00 00 00
2 | (572,1) | 8 | f | f |
3 | (573,1) | 16 | f | f | 01 00 00 00 01 00 00 00
4 | (574,1) | 16 | f | f | 01 00 00 00 01 00 00 00
5 | (576,1) | 16 | f | f | 01 00 00 00 01 00 00 00
6 | (577,1) | 16 | f | f | 01 00 00 00 01 00 00 00
7 | (578,1) | 16 | f | f | 01 00 00 00 01 00 00 00
8 | (579,1) | 16 | f | f | 01 00 00 00 01 00 00 00
9 | (580,1) | 16 | f | f | 01 00 00 00 01 00 00 00
10 | (581,1) | 16 | f | f | 01 00 00 00 01 00 00 00
11 | (582,1) | 16 | f | f | 01 00 00 00 01 00 00 00
12 | (583,1) | 16 | f | f | 01 00 00 00 01 00 00 00
13 | (584,1) | 16 | f | f | 01 00 00 00 01 00 00 00
14 | (585,1) | 16 | f | f | 01 00 00 00 01 00 00 00
15 | (586,1) | 16 | f | f | 01 00 00 00 01 00 00 00
16 | (587,1) | 16 | f | f | 01 00 00 00 01 00 00 00
17 | (588,1) | 16 | f | f | 01 00 00 00 01 00 00 00
18 | (589,1) | 16 | f | f | 01 00 00 00 01 00 00 00
19 | (590,1) | 16 | f | f | 01 00 00 00 01 00 00 00
20 | (591,1) | 16 | f | f | 01 00 00 00 01 00 00 00
21 | (592,1) | 16 | f | f | 01 00 00 00 01 00 00 00
22 | (593,1) | 16 | f | f | 01 00 00 00 01 00 00 00
23 | (594,1) | 16 | f | f | 01 00 00 00 01 00 00 00
24 | (595,1) | 16 | f | f | 01 00 00 00 01 00 00 00
25 | (596,1) | 16 | f | f | 01 00 00 00 01 00 00 00
26 | (597,1) | 16 | f | f | 01 00 00 00 01 00 00 00
27 | (598,1) | 16 | f | f | 01 00 00 00 01 00 00 00
28 | (599,1) | 16 | f | f | 01 00 00 00 01 00 00 00
29 | (600,1) | 16 | f | f | 01 00 00 00 01 00 00 00
30 | (601,1) | 16 | f | f | 01 00 00 00 01 00 00 00
31 | (602,1) | 16 | f | f | 01 00 00 00 01 00 00 00
32 | (603,1) | 16 | f | f | 01 00 00 00 01 00 00 00
33 | (604,1) | 16 | f | f | 01 00 00 00 01 00 00 00
34 | (605,1) | 16 | f | f | 01 00 00 00 01 00 00 00
35 | (606,1) | 16 | f | f | 01 00 00 00 01 00 00 00
36 | (607,1) | 16 | f | f | 01 00 00 00 01 00 00 00
37 | (608,1) | 16 | f | f | 01 00 00 00 01 00 00 00
38 | (609,1) | 16 | f | f | 01 00 00 00 01 00 00 00
39 | (610,1) | 16 | f | f | 01 00 00 00 01 00 00 00
40 | (5488,1) | 16 | f | f | 01 00 00 00 01 00 00 00
41 | (8961,1) | 16 | f | f | 01 00 00 00 03 00 00 00
42 | (8960,1) | 16 | f | f | 01 00 00 00 03 00 00 00
。。。。。。。。。。。。。。
通過這兩行，找到了c1=1.c2=2應該在5488號索引頁中。
40 | (5488,1) | 16 | f | f | 01 00 00 00 01 00 00 00
41 | (8961,1) | 16 | f | f | 01 00 00 00 03 00 00 00
繼續搜尋索引也，第二層（最後一層），葉子節點
postgres=# SELECT * FROM bt_page_items(`idx_tab1`, 5488);
itemoffset | ctid | itemlen | nulls | vars | data
------------+-------------+---------+-------+------+-------------------------
1 | (16215,25) | 16 | f | f | 01 00 00 00 03 00 00 00
2 | (5398,127) | 16 | f | f | 01 00 00 00 01 00 00 00
3 | (5398,137) | 16 | f | f | 01 00 00 00 01 00 00 00
4 | (5398,156) | 16 | f | f | 01 00 00 00 01 00 00 00
5 | (5398,172) | 16 | f | f | 01 00 00 00 01 00 00 00
.....
130 | (5405,10) | 16 | f | f | 01 00 00 00 01 00 00 00
131 | (5405,15) | 16 | f | f | 01 00 00 00 01 00 00 00
132 | (5405,17) | 16 | f | f | 01 00 00 00 01 00 00 00
133 | (5405,35) | 16 | f | f | 01 00 00 00 01 00 00 00
134 | (5405,59) | 16 | f | f | 01 00 00 00 01 00 00 00
135 | (10810,151) | 16 | f | f | 01 00 00 00 02 00 00 00
136 | (16216,41) | 16 | f | f | 01 00 00 00 03 00 00 00
137 | (16216,40) | 16 | f | f | 01 00 00 00 03 00 00 00
138 | (16216,39) | 16 | f | f | 01 00 00 00 03 00 00 00
...
找到記錄
HEAP PAGE
135 | (10810,151) | 16 | f | f | 01 00 00 00 02 00 00 00
因為是葉子節點，所以ctid表示的是HEAP的偏移值，直接在HEAP PAGE中檢視
postgres=# select * from tab1 where ctid=`(10810,151)`;
id | c1 | c2
----+----+----
1 | 1 | 2
(1 row)
在瞭解了多列索引的內部結構後，可以來看一下幾種查詢場景的優化
例子 – 範圍+等值查詢
驅動列使用範圍條件，第二列使用等值條件
雖然走了索引，但是掃描了第一列的所有索引頁。
效能不佳
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tab1 where c1 between 1 and 9 and c2=2;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_tab1 on public.tab1 (cost=0.43..60757.38 rows=1 width=12) (actual time=0.027..106.362 rows=1 loops=1)
Output: id, c1, c2
Index Cond: ((tab1.c1 >= 1) AND (tab1.c1 <= 9) AND (tab1.c2 = 2))
Buffers: shared hit=8321
Planning time: 0.099 ms
Execution time: 106.422 ms
(6 rows)
優化
新建複合索引，將等值列放在前面
postgres=# create index idx_tab1_2 on tab1 using btree (c2,c1);
CREATE INDEX
等值條件直接被過濾，只需要掃描一條索引ITEM
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tab1 where c1 between 1 and 9 and c2=2;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_tab1_2 on public.tab1 (cost=0.43..2.35 rows=1 width=12) (actual time=0.017..0.018 rows=1 loops=1)
Output: id, c1, c2
Index Cond: ((tab1.c2 = 2) AND (tab1.c1 >= 1) AND (tab1.c1 <= 9))
Buffers: shared hit=4
Planning time: 0.095 ms
Execution time: 0.040 ms
(6 rows)
例子 – 多值+等值查詢
PostgreSQL針對離散多值查詢，有一定的優化，僅僅掃描了多個離散值的索引ITEM
drop index idx_tab1_2;
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tab1 where c1 in (1,2,3,4,5,6,7,8,9) and c2=2;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_tab1 on public.tab1 (cost=0.43..13.90 rows=1 width=12) (actual time=0.024..0.186 rows=1 loops=1)
Output: id, c1, c2
Index Cond: ((tab1.c1 = ANY (`{1,2,3,4,5,6,7,8,9}`::integer[])) AND (tab1.c2 = 2))
Buffers: shared hit=21 read=7
Planning time: 0.114 ms
Execution time: 0.208 ms
(6 rows)
而如果將單值列放在前面，多值列放在後面，掃描的BLOCK會更少，但是會將離散過濾條件作為FILTER條件。
postgres=# create index idx_tab1_2 on tab1 using btree (c2,c1);
CREATE INDEX
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tab1 where c1 in (1,2,3,4,5,6,7,8,9) and c2=2;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_tab1_2 on public.tab1 (cost=0.43..2.35 rows=1 width=12) (actual time=0.027..0.027 rows=1 loops=1)
Output: id, c1, c2
Index Cond: (tab1.c2 = 2)
Filter: (tab1.c1 = ANY (`{1,2,3,4,5,6,7,8,9}`::integer[]))
Buffers: shared hit=4
Planning time: 0.107 ms
Execution time: 0.047 ms
(7 rows)
因為c2=2是驅動列，使用第二個索引，可以直接命中到1條item，其他的不需要掃到，所以快了很多。
假設有兩個索引存在，對於資料存在傾斜的情況，資料庫會根據過濾性自動選擇合適的索引。
小結
PostgreSQL目前還不支援非連續性的索引掃描，所以當驅動列（第一列）使用了範圍掃描後，即使複合索引有第二列，並且第二列是個等值查詢，那麼也要掃描第一列範圍覆蓋的所有索引。
這樣就出現了索引頁掃描的IO放大（因為可能掃了一些實際條件不符的INDEX PAGE）。
多列複合索引的建立建議：
1、離散查詢條件（例如 等值）的列放在最前面，如果一個複合查詢中有多個等值查詢的列，儘量將選擇性好（count(distinct) 值多的）的放在前面。
2、離散查詢條件（例如 多值）的列放在後面，如果一個複合查詢中有多個多值查詢的列，儘量將選擇性好（count(distinct) 值多的）的放在前面。
3、連續查詢條件（例如 範圍查詢）的列放在最後面，如果一個複合查詢中有多個多值查詢的列，儘量將輸入範圍條件返回結果集少的列放前面，提高篩選效率（同時也減少索引掃描的範圍）。
4、如果返回的結果集非常大（或者說條件命中率很高），並且屬於流式返回（或需要高效率優先返回前面幾條記錄），同時有排序輸出的需求。建議按排序鍵建立索引。
參考
《PostgreSQL bitmapAnd, bitmapOr, bitmap index scan, bitmap heap scan》
https://www.postgresql.org/docs/devel/static/pageinspect.html
相關文章
- oracle複合索引介紹(多欄位索引)Oracle索引
- 兩列複合索引中(交換索引中列的順序)，選索引的原則是?因為索引名字ascii碼小？索引ASCII
- PostgreSQL：資料庫的選擇SQL資料庫
- Oracle 計算欄位選擇性 判別列的索引潛力Oracle索引
- 位運算-設計資料庫表的多選狀態欄位資料庫
- CSS 連結偽類選擇器順序原理CSS
- 資料庫索引欄位請不要為NULL資料庫索引Null
- oracle更改欄位名順序的方法Oracle
- 時間序列化資料庫選型？時序資料庫的選擇？資料庫
- 順序與選擇結構
- 如何選擇合適的NoSQL資料庫SQL資料庫
- css選擇器的優先順序CSS
- css選擇器順序的小技巧CSS
- MySQL查詢索引的選擇性、索引欄位、註釋等基本資訊的SQLMySql索引
- MySQL 查詢索引的選擇性、索引欄位、註釋等基本資訊的SQLMySql索引
- CSS font 複合屬性順序CSS
- ORACLE 9i資料庫優化案例(2) --- 單列索引變複合索引的優化Oracle資料庫優化索引
- 程式流程 （順序，選擇，迴圈）
- css 選擇器優先順序CSS
- 改變表的欄位順序dbms_REDEFINITION
- 查詢/刪除重複的資料(單個欄位和多個欄位條件)
- 用Elasticsearch做大規模資料的多欄位、多型別索引檢索Elasticsearch多型型別索引
- Elasticsearch 複合查詢——多字串多欄位查詢Elasticsearch字串
- 序列化，資料庫存多個欄位資料資料庫
- MySQL資料庫索引選擇使用B+樹MySql資料庫索引
- 能否在Oracle資料庫表中指定列順序OKOracle資料庫
- 欄位按照指定 ID 順序進行排序排序
- 資料庫索引原理資料庫索引
- 優化資料庫的合理順序優化資料庫
- 組合索引的選擇原則索引
- mysql聯合索引的選擇性MySql索引
- pandas索引和選擇資料索引
- 資料庫索引的工作原理資料庫索引
- 列定義的順序和列儲存的順序
- SQL Server 索引列的順序——真的沒關係嗎SQLServer索引
- MySQL與PostgreSQL：該選擇哪個開源資料庫？MySql資料庫
- 資料庫索引原理-轉資料庫索引
- 根據開源資料庫選擇合適的工具資料庫