Oracle和PostgreSQL中Storage Index特性與BRIN索引是什么

本篇內(nèi)容介紹了“Oracle和PostgreSQL中Storage Index特性與BRIN索引是什么”的有關(guān)知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠?qū)W有所成！

Exadata的Storage Index不說了，因為那并非數(shù)據(jù)庫范疇的解決方案，而Oracle數(shù)據(jù)庫12.1.0.2中的新功能Zone Maps曾讓我非常激動，但是最終發(fā)現(xiàn)該功能也只能在運行于Exadata上的Oracle中才能啟用，略失望。

同樣的思路，在一個類索引結(jié)構(gòu)中存儲一定范圍的數(shù)據(jù)塊中某個列的最小和最大值，當查詢語句中包含該列的過濾條件時，就會自動忽略那些肯定不包含符合條件的列值的數(shù)據(jù)塊，從而減少IO讀取量，提升查詢速度。

以下借用Pg wiki中的例子解釋BRIN indexes的強大。

 -- 創(chuàng)建測試表orders

 CREATE TABLE orders (
     id int,
     order_date timestamptz,
     item text);

 -- 在表中插入大量記錄，Pg的函數(shù)generate_series非常好用。

 INSERT INTO orders (order_date, item)

 SELECT x, 'dfiojdso'
 FROM generate_series('2000-01-01 00:00:00'::timestamptz, '2015-03-01 00:00:00'::timestamptz,'2 seconds'::interval) a(x);

 -- 該表目前有13GB大小，算是大表了。

 # \dt+ orders
                    List of relations
  Schema |  Name  | Type  | Owner | Size  | Description
 --------+--------+-------+-------+-------+-------------
  public | orders | table | thom  | 13 GB |
 (1 row)

 -- 以全表掃描的方式查詢兩天內(nèi)的記錄，注意這里預計需要30s，這是一個存儲在SSD上Pg數(shù)據(jù)庫，因此速度已經(jīng)很理想了。

 # EXPLAIN ANALYSE SELECT count(*) FROM orders WHERE order_date BETWEEN '2012-01-04 09:00:00' and '2014-01-04 14:30:00';
                                                                          QUERY PLAN                                                                         
 -------------------------------------------------------------------------------------------------------------------------------------------------------------
  Aggregate  (cost=5425021.80..5425021.81 rows=1 width=0) (actual time=30172.428..30172.429 rows=1 loops=1)
    ->  Seq Scan on orders  (cost=0.00..5347754.00 rows=30907121 width=0) (actual time=6050.015..28552.976 rows=31589101 loops=1)
          Filter: ((order_date >= '2012-01-04 09:00:00+00'::timestamp with time zone) AND (order_date <= '2014-01-04 14:30:00+00'::timestamp with time zone))
          Rows Removed by Filter: 207652500
  Planning time: 0.140 ms
  Execution time: 30172.482 ms
 (6 rows)

 -- 接下來在order_date列上創(chuàng)建一個BRIN index

 CREATE INDEX idx_order_date_brin
   ON orders
   USING BRIN (order_date);

 -- 查看這個BRIN index占多少物理空間，13GB的表，而BRIN index只有504KB大小，非常精簡。

 # \di+ idx_order_date_brin
                               List of relations
  Schema |        Name         | Type  | Owner | Table  |  Size  | Description
 --------+---------------------+-------+-------+--------+--------+-------------
  public | idx_order_date_brin | index | thom  | orders | 504 kB |
 (1 row)

 -- 再次執(zhí)行相同的SQL，看看性能提升多少。速度上升到只需要6秒鐘，提升了5倍。如果這是存儲在HDD上的Pg庫，這個效果還能更明顯。

 # EXPLAIN ANALYSE SELECT count(*) FROM orders WHERE order_date BETWEEN '2012-01-04 09:00:00' and '2014-01-04 14:30:00';
                                                                               QUERY PLAN                                                                              
 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Aggregate  (cost=2616868.60..2616868.61 rows=1 width=0) (actual time=6347.651..6347.651 rows=1 loops=1)
    ->  Bitmap Heap Scan on orders  (cost=316863.99..2539600.80 rows=30907121 width=0) (actual time=36.366..4686.634 rows=31589101 loops=1)
          Recheck Cond: ((order_date >= '2012-01-04 09:00:00+00'::timestamp with time zone) AND (order_date <= '2014-01-04 14:30:00+00'::timestamp with time zone))
          Rows Removed by Index Recheck: 6419
          Heap Blocks: lossy=232320
          ->  Bitmap Index Scan on idx_order_date_brin  (cost=0.00..309137.21 rows=30907121 width=0) (actual time=35.567..35.567 rows=2323200 loops=1)
                Index Cond: ((order_date >= '2012-01-04 09:00:00+00'::timestamp with time zone) AND (order_date <= '2014-01-04 14:30:00+00'::timestamp with time zone))
  Planning time: 0.108 ms
  Execution time: 6347.701 ms
 (9 rows)

 --能夠讓用戶自行設(shè)定一個range中可以包含的數(shù)據(jù)塊數(shù)，也是很體貼的設(shè)計。默認情況下一個range包含128個page，我們可以修改為更小或者更大，包含的page越少則精度越細，相應(yīng)的BRIN index也就會越大；反之則精度粗，BRIN index小。

 -- 創(chuàng)建一個每個range包含32 pages的索引。

 CREATE INDEX idx_order_date_brin_32
 ON orders
 USING BRIN (order_date) WITH (pages_per_range = 32);

 -- 再創(chuàng)建一個每個range包含512 pages的索引。

 CREATE INDEX idx_order_date_brin_512
 ON orders
 USING BRIN (order_date) WITH (pages_per_range = 512);

--比較一下各個索引的大小。

 # \di+ idx_order_date_brin*
                                  List of relations
  Schema |          Name           | Type  | Owner | Table  |  Size   | Description
 --------+-------------------------+-------+-------+--------+---------+-------------
  public | idx_order_date_brin     | index | thom  | orders | 504 kB  |
  public | idx_order_date_brin_32  | index | thom  | orders | 1872 kB |
  public | idx_order_date_brin_512 | index | thom  | orders | 152 kB  |
 (3 rows)

“Oracle和PostgreSQL中Storage Index特性與BRIN索引是什么”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實用文章！