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

摘要： 標籤 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、分區索引通常用在多個有條件的搜尋中，其中分區條件作為其中的一種搜尋條件。當然它也能用在對單個列的搜尋中。

3、PostgreSQL除了支援分區索引（partial index），還支援運算式索引、函數索引。

歡迎使用阿裡雲RDSPostgreSQL。

相關產品：

1.雲資料庫RDS

2.安全管家

3.Web套用防火牆

4.雲端服務器ECS