資訊專欄INFORMATION COLUMN
前 言
Oracle的位圖索引適用于列的基數很少，可枚舉，重復值很多，數據不會被經常更新的列。
它的索引結構是一個鍵值對應很多行(rowid)，對于報表類數據庫，重復率高的數據，特定類型的查詢例如count、or、and等邏輯操作，只需要進行位運算即可得到我們需要的結果，可以說是相當的效率。
最近項目在進行上云工作，有許多Oracle到PostgreSQL的遷移要做。其中涉及到位圖索引，
然而PostgreSQL沒有位圖索引。怎么改造這種索引，來實現相應的索引場景呢？
今天我們來聊一聊PostgreSQL中的黑科技Brin索引。
索 引 原 理
BRIN索引是塊級索引，有別于B-TREE等索引，BRIN記錄并不是以行號為單位記錄索引明細，而是記錄每個數據塊或者每段連續的數據塊的統計信息。因此BRIN索引空間占用特別的小，對數據寫入、更新、刪除的影響也很小。
BRIN索引的掃描原理很簡單，掃描BRIN的元數據，根據元數據和用戶輸入的條件進行比較，過濾不符合條件的HEAPPAGE，只掃描需要掃描的HEAPPAGE。
如果數據排列的比較隨機時，那么索引效果就非常差。達不到索引快速掃描的效果。
我們創建兩張表，一張順序插入，一張亂序插入：
--順序插入tab_brin1：
otter_pg=# create table tab_brin1(id int,name varchar(40),c_time timestamp); CREATE TABLE otter_pg=# insert into tab_brin1 select *,md5(random()::text),clock_timestamp() from generate_series(1,10000000); INSERT 0 10000000 |
--亂序插入tab_brin2：
otter_pg=# create table tab_brin2(id int,name varchar(40),c_time timestamp); CREATE TABLE tter_pg=# insert into tab_brin2 select (random()*(10^6))::integer,md5(random()::text),timestamp 2019-01-10 20:00:00 + random() * (timestamp 2019-01-20 20:00:00 - timestamp 2021-01-10 10:00:00) from generate_series(1,10000000); INSERT 0 10000000 |
--兩張表都創建BRIN索引和BTREE索引
otter_pg=# create index idx1_tab_brin1 on tab_brin1 using brin(c_time); CREATE INDEX otter_pg=# create index idx1_tab_brin2 on tab_brin2 using brin(c_time); CREATE INDEX otter_pg=# create index idx2_tab_brin1 on tab_brin1 using btree(c_time); CREATE INDEX otter_pg=# create index idx2_tab_brin2 on tab_brin1 using btree(c_time); CREATE INDEX |
--我們看看索引大小
可以看到表為700M，BTREE索引需要214M，而BRIN索引只有40K。
otter_pg=# select pg_size_pretty(pg_relation_size(tab_brin1)); pg_size_pretty ---------------- 730 MB otter_pg=# select pg_size_pretty(pg_relation_size(idx1_tab_brin1)); pg_size_pretty ---------------- 40 kB otter_pg=# select pg_size_pretty(pg_relation_size(idx2_tab_brin1)); pg_size_pretty ---------------- 214 MB |
--我們在來看看BRIN索引的使用。
首先看看兩表的離散度,如下可以看出tab_brin1表的邏輯順序和物理順序一致性更好些。
otter_pg=# select correlation from pg_stats where tablename=tab_brin1; correlation --------------------- 1 0.0048282277 1 otter_pg=# select correlation from pg_stats where tablename=tab_brin2; correlation --------------------- 0.0010042704 -0.002086642 0.006167772 |
對比下使用兩表BRIN索引時的效率，這里我們需要刪除前面創建的BTREE索引。
--tab_brin1的執行計劃如下：可以看到耗時0.6ms。
--tab_brin2的執行計劃如下：可以看到耗時21ms。
經過分析，物理順序和邏輯順序越一致，該列更適合建立BRIN索引。
BRIN索引有一個參數pages_per_range可以用來近一步提升Brin索引的性能。
pages_per_range是粒度，默認為128(表示每128個數據塊統計一次邊界)，它影響BRIN索引的精確度和 BRIN索引的大小。
--精度為1時，耗時46.6ms
otter_pg=# create index idx1_tab_brin1 on tab_brin1 using brin(c_time) with (pages_per_range=1); CREATE INDEX otter_pg=# otter_pg=# explain (analyze ,verbose,timing,costs,buffers) select * from tab_brin1 where c_time between 2019-01-10 20:00:00 and 2020-01-10 20:00:00; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----- Bitmap Heap Scan on public.tab_brin1 (cost=424.40..543.34 rows=1 width=45) (actual time=46.544..46.544 rows=0 loops=1) Output: id, name, c_time Recheck Cond: ((tab_brin1.c_time >= 2019-01-10 20:00:00::timestamp without time zone) AND (tab_brin1.c_time <= 2020-01-10 20:00:00::timestamp without time zone)) Buffers: shared hit=527 -> Bitmap Index Scan on idx1_tab_brin1 (cost=0.00..424.40 rows=107 width=0) (actual time=46.536..46.536 rows=0 loops=1) Index Cond: ((tab_brin1.c_time >= 2019-01-10 20:00:00::timestamp without time zone) AND (tab_brin1.c_time <= 2020-01-10 20:00:00::timestamp without time zo ne)) Buffers: shared hit=527 Planning Time: 0.632 ms Execution Time: 46.639 ms (9 rows) |
--精度為50時，耗時1.18ms
otter_pg=# create index idx1_tab_brin1 on tab_brin1 using brin(c_time) with (pages_per_range=50); CREATE INDEX otter_pg=# explain (analyze ,verbose,timing,costs,buffers) select * from tab_brin1 where c_time between 2019-01-10 20:00:00 and 2020-01-10 20:00:00; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----- Bitmap Heap Scan on public.tab_brin1 (cost=10.91..5688.47 rows=1 width=45) (actual time=1.115..1.115 rows=0 loops=1) Output: id, name, c_time Recheck Cond: ((tab_brin1.c_time >= 2019-01-10 20:00:00::timestamp without time zone) AND (tab_brin1.c_time <= 2020-01-10 20:00:00::timestamp without time zone)) Buffers: shared hit=11 -> Bitmap Index Scan on idx1_tab_brin1 (cost=0.00..10.91 rows=5348 width=0) (actual time=1.105..1.105 rows=0 loops=1) Index Cond: ((tab_brin1.c_time >= 2019-01-10 20:00:00::timestamp without time zone) AND (tab_brin1.c_time <= 2020-01-10 20:00:00::timestamp without time zo ne)) Buffers: shared hit=11 Planning Time: 0.566 ms Execution Time: 1.186 ms (9 rows) |
pages_per_range定義數據塊的數量，為BRIN索引的每條記錄統計的數據塊范圍。默認值為128。
如果這個值很大，則索引就會很小，索引掃描就會很迅速，但是后續內存中的Recheck就會很多，因為把大量的不相關數據拉到內存中了。
如果這個值很小，索引的過濾性越好，但索引也會越大。由于每篩選一次字段PostgreSQL 都要掃描全部的BRIN索引，所花費的時間也會變長，因此需要根據表的大小與應用場景去調整其值的大小。
BRIN主要適用于類似時序數據之類的，有著天然的順序，而且都是添加寫的場景。相比于BTREE索引，它的體積小得多，非常適用于大數據量的場景。
文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。
轉載請注明本文地址：http://m.specialneedsforspecialkids.com/yun/130048.html
摘要：類型說明根據中的說明，數據類型是用來存儲數據的。它們幾乎接受完全相同的值集合作為輸入。該結構是非強制的，但是有一個可預測的結構會使集合的查詢更容易。如中，表示在和這兩個位置出現過，在中這些位置實際上就是元組的行號，包括數據塊以及。 json 類型 說明 根據RFC 7159中的說明，JSON 數據類型是用來存儲 JSON（JavaScript Object Notation）數據的。這...
摘要：類型說明根據中的說明，數據類型是用來存儲數據的。它們幾乎接受完全相同的值集合作為輸入。該結構是非強制的，但是有一個可預測的結構會使集合的查詢更容易。如中，表示在和這兩個位置出現過，在中這些位置實際上就是元組的行號，包括數據塊以及。 json 類型 說明 根據RFC 7159中的說明，JSON 數據類型是用來存儲 JSON（JavaScript Object Notation）數據的。這...
摘要：作者譚峰張文升出版日期年月頁數頁定價元本書特色中國開源軟件推進聯盟分會特聘專家撰寫，國內多位開源數據庫專家鼎力推薦。張文升中國開源軟件推進聯盟分會核心成員之一。 很高興《PostgreSQL實戰》一書終于出版，本書大體上系統總結了筆者 PostgreSQL DBA 職業生涯的經驗總結，本書的另一位作者張文升擁有豐富的PostgreSQL運維經驗，目前就職于探探科技任首席PostgreS...
閱讀 1353·2023-01-11 13:20
閱讀 1699·2023-01-11 13:20
閱讀 1211·2023-01-11 13:20
閱讀 1902·2023-01-11 13:20
閱讀 4161·2023-01-11 13:20
閱讀 2751·2023-01-11 13:20
閱讀 1397·2023-01-11 13:20
閱讀 3664·2023-01-11 13:20
