postgresql——索引

postgresql的索引

postgresql提供的索引類型有：B-tree、hash、gist和gin。大多情況下，B-tree索引比較常用，用戶可以使用create index命令創(chuàng)建一個(gè)B-tree索引。

1、B-tree索引：

   B-tree適合處理那些能夠按順序存儲(chǔ)的數(shù)據(jù)，比如對(duì)于一些字段涉及使用：< ,<= ,= ,>= 或 >操作符之一進(jìn)行比較的時(shí)候，可以建立一個(gè)索引。

也可以使用B-tree索引搜索來實(shí)現(xiàn)與這些運(yùn)算符的組合相同的構(gòu)造，如BETWEEN和IN。此外，索引列上的IS NULL或IS NOT NULL條件可以與B-tree索引一起使用。

  對(duì)于涉及模式匹配運(yùn)算符LIKE的查詢，優(yōu)化器還可以使用B-tree索引，如果模式是常量，并且錨定到字符串的開頭，例如col LIKE 'foo％'或 col?'^ foo'，但不能是col LIKE'％bar'。但是，如果您的數(shù)據(jù)庫不使用C語言環(huán)境，則需要使用特殊的運(yùn)算符類創(chuàng)建索引，以支持對(duì)模式匹配查詢的索引;見下文第11.9節(jié)。也可以對(duì) ILIKE和?*使用B-tree索引，但只有當(dāng)模式以非字母字符（即不受大小寫轉(zhuǎn)換影響的字符）開始時(shí)才可以。

2、hash索引：

  hash索引只能處理簡單的等于比較。當(dāng)一個(gè)索引的列涉及使用=操作符進(jìn)行比較的時(shí)候，查詢規(guī)劃器會(huì)考慮使用hash索引。

Hash索引操作目前不記錄WAL-log，所以如果有沒有寫入的更改，Hash索引可能需要在數(shù)據(jù)庫崩潰后用REINDEX重建。此外，在初始基本備份之后，不會(huì)通過流式或基于文件的復(fù)制來復(fù)制Hash索引的更改，因此它們對(duì)隨后使用它們的查詢給出錯(cuò)誤的答案。由于這些原因，目前不鼓勵(lì)使用Hash索引。

3、gist索引：

   gist索引不是單獨(dú)一種索引類型，而是一種架構(gòu)，可以在這種架構(gòu)上實(shí)現(xiàn)很多不同的索引策略。因此，可以使用gist索引的特定操作符類型高度依賴于索引策略（操作符類 ）

GiST索引不是一種單一的索引，而是可以實(shí)現(xiàn)許多不同索引策略的基礎(chǔ)設(shè)施。因此，可以使用GiST索引的特定運(yùn)算符根據(jù)索引策略（運(yùn)算符類）而變化。

4、GIN索引

   GIN索引是反轉(zhuǎn)索引，可以處理包含多個(gè)鍵的值（比如數(shù)組）。與gist類似，gin支持用戶定義的索引策略，可以使用GIN索引的特定操作符類型根據(jù)索引策略的不同而不同 。

索引的設(shè)計(jì)原則：

①：索引并非越多越好。如果一個(gè)表中有大量的索引，那么不僅會(huì)占用大量磁盤空間，還會(huì)影響：insert、delete、update等語句的性能，因?yàn)楦谋碇械臄?shù)據(jù)時(shí)，索引也會(huì)進(jìn)行調(diào)整和更新。

②：避免對(duì)經(jīng)常更新的表進(jìn)行過多索引，并且索引中的列要盡可能少。對(duì)經(jīng)常用于查詢的字段應(yīng)該創(chuàng)建索引，但要避免添加不必要的字段。

③：數(shù)據(jù)量小的表最好不要使用索引。數(shù)據(jù)較少時(shí)，查詢花費(fèi)的時(shí)間可能比遍歷索引的時(shí)間還要短，索引可能不會(huì)產(chǎn)生優(yōu)化效果。

④：在條件表達(dá)式中經(jīng)常用到的不同值較多的列上建立索引，在不同值少的列上不要建立索引。

⑤：當(dāng)唯一性是某種數(shù)據(jù)本身的特征時(shí)，指定唯一索引。使用唯一索引能夠確保定義的列的數(shù)據(jù)完整性，提高查詢速度。

⑥：在頻繁進(jìn)行排序或分組（進(jìn)行g(shù)roup by或order by操作）的列上建立索引。如果待排序的列有多個(gè)，可以在這些列上建立組合索引。

---常見操作：（注意：默認(rèn)創(chuàng)建的是B-tree索引）

基本語法：

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

1、創(chuàng)建普通索引：B-tree索引

create index idx_contacts_name on contacts(name);

--創(chuàng)建唯一索引：

create  unique index idx_emp  on  emp(id);

--創(chuàng)建組合索引：

create  index  idx_emp  on  emp(id,name); 

2、數(shù)組索引

create index idx_contacts_phone on contacts using gin(phone);

注：phone在contacts表中是一個(gè)數(shù)組類型

3、降序索引

create index idx_contacts_name on contacts(name desc);

4、指定存儲(chǔ)參數(shù)

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

注：fillfactor是常用的存儲(chǔ)參數(shù)

5、指定空值排在前面

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

6、避免創(chuàng)建索引的長時(shí)間阻塞，可以在index關(guān)鍵字后面增加concurrently關(guān)鍵字，可以減少索引的阻塞時(shí)間

create index concurrently idx_contacts_name on contacts(name desc);

注意，重建索引時(shí)不支持concurrently ，可以新建一個(gè)索引，然后刪除舊索引，另外并發(fā)索引被強(qiáng)制取消，可能會(huì)留下無效索引，這個(gè)索引將會(huì)導(dǎo)致更新變慢，如果是唯一索引，還會(huì)導(dǎo)致插入重復(fù)值失敗。

7、修改索引

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

設(shè)置表空間：alter index name set tablespace tablespace_name;

設(shè)置存儲(chǔ)參數(shù)：alter index name set(storage_parameter=value[,...])

重設(shè)存儲(chǔ)參數(shù)：alter index name reset(storeage_parameter[,...])

8、刪除索引

drop index if exists idx_emp;

8、cascade會(huì)把索引和依賴索引的對(duì)象全部刪除