postgresql——索引

標籤：重建   注意   text   最佳化器   包含   order   c語言   環境   phone   

postgresql的索引

postgresql提供的索引類型有：B-tree、hash、gist和gin。大多情況下，B-tree索引比較常用，使用者可以使用create index命令建立一個B-tree索引。

1、B-tree索引：

   B-tree適合處理那些能夠按順序儲存的資料，比如對於一些欄位涉及使用：< ,<= ,= ,>= 或 >操作符之一進行比較的時候，可以建立一個索引。

也可以使用B-tree索引搜尋來實現與這些運算子的組合相同的構造，如BETWEEN和IN。此外，索引列上的IS NULL或IS NOT NULL條件可以與B-tree索引一起使用。

  對於涉及模式比對運算子LIKE的查詢，最佳化器還可以使用B-tree索引，如果模式是常量，並且錨定到字串的開頭，例如col LIKE 'foo％'或 col?'^ foo'，但不能是col LIKE'％bar'。但是，如果您的資料庫不使用C語言環境，則需要使用特殊的運算子類建立索引，以支援對模式比對查詢的索引;見下文第11.9節。也可以對 ILIKE和?*使用B-tree索引，但只有當模式以非字母字元（即不受大小寫轉換影響的字元）開始時才可以。

2、hash索引：

  hash索引只能處理簡單的等於比較。當一個索引的列涉及使用=操作符進行比較的時候，查詢規劃器會考慮使用hash索引。

Hash索引操作目前不記錄WAL-log，所以如果有沒有寫入的更改，Hash索引可能需要在資料庫崩潰後用REINDEX重建。此外，在初始基本備份之後，不會通過流式或基於檔案的複製來複製Hash索引的更改，因此它們對隨後使用它們的查詢給出錯誤的答案。由於這些原因，目前不鼓勵使用Hash索引。

3、gist索引：

   gist索引不是單獨一種索引類型，而是一種架構，可以在這種架構上實現很多不同的索引策略。因此，可以使用gist索引的特定操作符類型高度依賴於索引策略（操作符類 ）

GiST索引不是一種單一的索引，而是可以實現許多不同索引策略的基礎設施。因此，可以使用GiST索引的特定運算子根據索引策略（運算子類）而變化。

4、GIN索引

   GIN索引是反轉索引，可以處理包含多個鍵的值（比如數組）。與gist類似，gin支援使用者定義的索引策略，可以使用GIN索引的特定操作符類型根據索引策略的不同而不同 。

索引的設計原則：

①：索引並非越多越好。如果一個表中有大量的索引，那麼不僅會佔用大量磁碟空間，還會影響：insert、delete、update等語句的效能，因為更改表中的資料時，索引也會進行調整和更新。

②：避免對經常更新的表進行過多索引，並且索引中的列要儘可能少。對經常用於查詢的欄位應該建立索引，但要避免添加不必要的欄位。

③：資料量小的表最好不要使用索引。資料較少時，查詢花費的時間可能比遍曆索引的時間還要短，索引可能不會產生最佳化效果。

④：在條件運算式中經常用到的不同值較多的列上建立索引，在不同值少的列上不要建立索引。

⑤：當唯一性是某種資料本身的特徵時，指定唯一索引。使用唯一索引能夠確保定義的列的資料完整性，提高查詢速度。

⑥：在頻繁進行排序或分組（進行group by或order by操作）的列上建立索引。如果待排序的列有多個，可以在這些列上建立複合式索引。

---常見操作：（注意：預設建立的是B-tree索引）

基本文法：

create [unique |fulltext |spatial]  index  index_name  on  table_name  (col_name[length],....)  [ ASC | DESC ]

1、建立普通索引：B-tree索引

create index idx_contacts_name on contacts(name);

--建立唯一索引：

create  unique index idx_emp  on  emp(id);

--建立複合式索引：

create  index  idx_emp  on  emp(id,name); 

2、數組索引

create index idx_contacts_phone on contacts using gin(phone);

註：phone在contacts表中是一個數群組類型

3、降序索引

create index idx_contacts_name on contacts(name desc);

4、指定儲存參數

create index idx_contacts_name on contacts(name) with(fillfactor=50);

註：fillfactor是常用的儲存參數

5、指定空值排在前面

create index idx_contacts_name on contacts(name desc nulls first);

6、避免建立索引的長時間阻塞，可以在index關鍵字後面增加concurrently關鍵字，可以減少索引的阻塞時間

create index concurrently idx_contacts_name on contacts(name desc);

注意，重建索引時不支援concurrently ，可以建立一個索引，然後刪除舊索引，另外並發索引被強製取消，可能會留下無效索引，這個索引將會導致更新變慢，如果是唯一索引，還會導致插入重複值失敗。

7、修改索引

索引重新命名：alter index name rename to new_name;

設定資料表空間：alter index name set tablespace tablespace_name;

設定儲存參數：alter index name set(storage_parameter=value[,...])

重設儲存參數：alter index name reset(storeage_parameter[,...])

8、刪除索引

drop index if exists idx_emp;

8、cascade會把索引和依賴索引的對象全部刪除

postgresql——索引