> 索引是對數據庫表中的一列或者多列的數據進行排序的一種結構，使用索引可快速訪問數據表中的特定信息。 > pgsql提供了B-tree、Hash、Gin等索引類型，每種索引類型因采用不同的算法而適用于不同的查詢場景。在未指定索引類型時，CREATE INDEX命令將創建B-tree 索引。 **基本術語：** * 元組 - tuple，指的是行(包括heap tuple, index tuple等，都可以叫元組)。 * tid, ctid - 指行號(block\_number, item pointer)，包括HEAP表的BLOCK NUMBER，以及在BLOCK中指向行的item pointer。 * 鍵值 - 指索引結構中的KEY * 基表 - 創建索引的表 **索引的掃描方式：** 1. **Indexscan：** 根據查詢索引找到匹配記錄的[ctid]()，再通過ctid去查堆表把相應的數據讀出來 2. **Bitmapscan：** 通過索引找到匹配記錄的ctid集合，在內存中建一個bitmap對ctid做集合運算和排序等，最后再根據bitmap中的數據文件把對應數據讀取出來。 3. **Seq Scan：** 順序掃描也叫做全表掃描，數據庫的一種掃描方式，跟索引沒啥關系，當語句是這種掃描方式時，說明該查詢語句并沒有走索引。 4. **Index Only Scan：** 這種索引方式說明索引字段中包含了所有返回字段，對vm文件中全為可見的數據塊，不查堆表直接返回索引中的值 > [VM文件](http://docs.linchunyu.top/2280082#span_stylefontsize15pxVMspan_154)是可見性映射文件，如果存在vm文件，則表示該數據塊沒有需要清理的行，即該表做了vacuum操作。[vaccum命令](http://docs.linchunyu.top/2280082#span_stylefontsize15pxvacuumspan_198)時用來對表進行操作，回收已經刪除元祖占據的存儲空間 **索引分類：** * **從索引的類型上區分：** * 唯一索引： 唯一索引不允許任何重復的值插入到表。格式為：`CREATE UNIQUE INDEX index_name on table_name (column_name)` * 單列索引：只基于表的一個列上創建的索引。格式為： `CREATE INDEX index_name ON table_name (column_name);` * 組合索引：基于表的多列上創建的索引。格式為：`CREATE INDEX index_name ON table_name (column1_name, column2_name)` * 表達式索引：字段進行某種運算之后的結果創建索引、格式為：`CREATE INDEX index_name ON table_name(expression|column_name)` * **從索引的實現方法上區分：** * B-tree：創建索引時未指定類型時，默認創建B-tree索引 * Hash：此索引類型只能處理簡單的等值比較 * Gin：通用倒排索引，可以與pg_trgm擴展配合使用以實現字符串匹配的全文檢索，即全模糊搜索 * Gist：通用的搜索樹(Generalized Search Tree)。 它是一種平衡樹結構的訪問方法，在系統中作為一個基本模版，可以使用它實現任意索引模式。B-trees, R-trees和許多其它的索引模式都可以用GiST實現 <table> <thead> <th width="10%">索引類型</th> <th width="20%">說明</th> <th width="20%" >適用場景</th> <th>特性</th> </thead> <tbody> <tr> <td>B-tree</td> <td>默認類型。使用btree索引，where條件必須包含第一個索引列</td> <td>B-tree索引適合用于存儲排序的數據，并支持=，>,>=，<,<=，以及between，in，is null等操作符和以字符串開頭的模糊查詢(前模糊)</td> <td>1、 在PostgreSQL13中，Btree索引新增了Deduplication特性，使得PG數據庫有了新的方式去處理重復的索引鍵值，這大大減小了btree索引所占用的空間，提升了索引掃描的性能，deduplication涉及到底層索引存儲結構的變更。在13版本之前，每一個重復的數據都會占用索引的一個葉子元組leaf，這些重復的key值在索引頁面中重復存儲，帶來很大的空間浪費。通過deduplicate_items 參數開啟（默認開啟）<br>2、 在PostgreSQL當前支持的索引類型中，只有B-tree可以產生排序的輸出，當ORDER BY與LIMIT n組合：顯式排序將必須處理所有數據以識別前n行，但如果存在與ORDER BY匹配的索引，則可以直接檢索前n行，而不掃描其余部分。升序默認null值放在最后，可以使用NULLS FIRST和/或NULLS LAST選項來進行調整。<br>3、支持多列索引，最多可以指定32列，使用最左匹配原則</td> </tr> <tr> <td>Hash</td> <td>此索引類型只能處理簡單的等值比較</td> <td>= 操作符</td> <td>1、由于hash函數沒有特定的排序規則，所以一般的hash索引只支持等值查<br>2、hash索引也不會處理null值，所以不會標記null值<br>3、hash索引不存儲索引鍵，只存儲hash碼，所以不會 index-only掃描，也不支持多列創建hash索引<br>4、從10版本開始，可以通過pageinspect插件查看hash索引的內部情況 `create extension pageinspect`</td> </tr> <tr> <td>Gin</td> <td>通用倒排索引，是一個存儲對(key, posting list)集合的索引結構，其中key是一個鍵值，而posting list 是一組出現過key的位置</td> <td>1、多值類型(數組、全文檢索、枚舉、網絡地址類型)：包含、相交<br>2、JSON類型<br> 3、普通類型(通過btree_gin 插件支持)：與B-Tree類似<br>4、字符串(通過pg_trgm 插件支持)：模糊查詢、相似查詢<br>5、 多列：任意列組合查詢</td> <td>1、在表中的每一個屬性，在建立索引時，都可能會被解析為多個鍵值<br>2、通過這種索引結構可以快速的查找到包含指定關鍵字的元組，因此GIN索引特別適用于多值類型的元素搜索，比如支持全文搜索，數組中元素的搜索<br>3、Gin索引創建耗時比btree索引長約10倍，對頻繁更新的表字段不建議使用</td> </tr> <tr> <td>Gist</d> <td>通用搜索樹，一種平衡樹結構的訪問方法,在系統中作為一個基本模版,可以使用它實現任意索引模式。B-trees, R-trees和許多其它的索引模式都可以用GiST實現</td> <td>1、空間類型：方位（上、下、左、右），空間關系（相交、包含），空間距離排序(KNN)<br>2、范圍數據：=, &&, , >, -|-, &<br>3、普通類型：與B-Tree類似，增加空間類型類似操作符<br>4、數組類型<br>5、多列：任意列組合查詢</td> <td>1、可通過btree_gist擴展，支持多種數據類型的 B 樹等效功能<br>2、可通過cube擴展，支持多維立方體的索引<br>3、可通過hstore擴展，支持存儲鍵值對模塊的索引<br>4、可通過intarray擴展，支持一維 int4 值數組的 RD 樹的索引<br>5、可通過ltree擴展，支持樹狀結構的索引<br>6、可通過pg_trgm擴展，支持全文檢索，模糊搜索<br>7、可通過seg擴展，支持“float ranges”的索引</td> </tr> </tbody> </table> **Gin索引和Gist索引的區別：** 1. GIN索引查找比GiST索引更快、更精準 2. GIN索引的建立和更新比GIST索引耗時更長、占用空間更大 3. GIN索引對靜態數據是最好的，因為查找速度很快。對于動態數據， GiST索引更新比較快。具體而言，GiST索引非常適合動態數據