分區索引的套用和實踐 - 阿裡雲RDS PostgreSQL最佳實踐

來源:互聯網
上載者:User

摘要: 標籤 PostgreSQL , partial index , partition index 幕後 當表很大時,大家可能會想到磁碟分割表格的概念,例如用戶表,按用戶ID雜湊或者定界分割,拆成很多表。又比如行為資料工作表,可以按時間分區,拆成很多表。

標籤

PostgreSQL, partial index , partition index

幕後

當表很大時,大家可能會想到磁碟分割表格的概念,例如用戶表,按用戶ID雜湊或者定界分割,拆成很多表。

又比如行為資料工作表,可以按時間分區,拆成很多表。

拆表的好處:

1、可以將表放到不同的資料表空間,資料表空間和塊裝置勾點,例如歷史資料存取量低,資料量大,可以放到機械盤所在的資料表空間。而活躍資料則可以放到SSD對應的資料表空間。

2、拆表後,方便維修,例如移除歷史資料,直接DROP TABLE就可以了,不會產生REDO

索引實際上也有分區的概念,例如按USER ID HASH分區,按時間分區等。

分區索引的好處與磁碟分割表格的好處類似。同時還有其他好處:

1、不需要被取出的部份資料,可以不對它建立索引。

例如一張用戶表,我們只取出已啟用的用戶,對於未啟用的用戶,我們不對它進行取出,那麼可以只對已啟用使用者建立索引。

2、不同建構的資料,可以使用不同的索引介面。

例如某張表裡面資料出現了傾斜,某些VALUE占比很高,而某些VALUE占比則很低。我們可以對占比很高的VALUE使用bitmap或者gin的索引方法,而對於出現頻率低的使用btree的索引方法。

那麼我們接下來看看PostgreSQL分區索引是如何?的?

全域索引

首先是全域索引,就是我們平常建立的索引。

create table test(id int, crt_time timestamp, info text); 
  
create index idx_test_id on test(id); 

一級分區索引

create table test(id int, crt_time timestamp, info text); 
  
分區索引如下 
  
create index idx_test_id_1 on test(id) where crt_time between '2017-01-01' and '2017-02-01'; 
create index idx_test_id_2 on test(id) where crt_time between '2017-02-01' and '2017-03-01'; 
... 
create index idx_test_id_12 on test(id) where crt_time between '2017-12-01' and '2018-01-01'; 

多級分區索引

create table test(id int, crt_time timestamp, province_code int, info text); 
  
分區索引如下 
  
create index idx_test_id_1_1 on test(id) where crt_time between '2017-01-01' and '2017-02-01' and province_code=1; 
create index idx_test_id_1_2 on test(id) where crt_time between '2017-02-01' and '2017-03-01' and province_code=1; 
... 
create index idx_test_id_1_12 on test(id) where crt_time between '2017-12-01' and '2018-01-01' and province_code=1; 
  
.... 
  
create index idx_test_id_2_1 on test(id) where crt_time between '2017-01-01' and '2017-02-01' and province_code=2; 
create index idx_test_id_2_2 on test(id) where crt_time between '2017-02-01' and '2017-03-01' and province_code=2; 
... 
create index idx_test_id_2_12 on test(id) where crt_time between '2017-12-01' and '2018-01-01' and province_code=2; 

資料扭曲分區例子

create table test(uid int, crt_time timestamp, province_code int, info text); 
  
create index idx_test_1 on test using gin(uid) where uid<1000;    --該號段包含大量重複值(高頻值),使用gin索引加速 
create index idx_test_1 on test using btree(uid) where uid>=1000; --該號段為低頻值,使用btree索引加速 

小結

1、在搜尋資料時,用戶帶上索引分割區條件,索引欄位。使用對應的操作符,即可實現分區索引的取出。 

2、分區索引通常用在多個有條件的搜尋中,其中分區條件作為其中的一種搜尋條件。當然它也能用在對單個列的搜尋中。

3PostgreSQL除了支援分區索引(partial index),還支援運算式索引、函數索引。

歡迎使用阿裡雲RDSPostgreSQL

相關產品:

1.雲資料庫RDS

2.安全管家

3.Web套用防火牆

4.雲端服務器ECS

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.