PostgreSQL:INDEX

Ryan_Bai發表於2020-12-14

索引是加速搜尋引擎檢索資料的一種特殊表查詢。簡單地說,索引是一個指向表中資料的指標。

分類

  • B-tree:最常用的索引,B-tree 索引適合處理等值查詢和範圍查詢

  • Hash:只能處理簡單的等只查詢

  • GiST:不是一種單獨的索引型別,而是一種架構,可以在這種架構上實現很多不同的索引策略。GiST 索引定義的特定運算子可以用於特定索引策略。PostgreSQL 的標準釋出中包含了用於二維幾何資料型別的 GiST 運算子類。比如,一個圖形包含另一個圖形的運算子“@>”,一個圖形在另一個圖形的左邊沒有重疊的運算子“<<”,等等

  • SP-GiST:SP-GiST 是“space-partitioned GiST”的縮寫,即空間分割槽 GiST 索引。主要是透過一些新的索引演算法提高 GiST 索引再某個情況下的效能。

  • GIN:反轉索引,它可以處理包含多個鍵的值,如陣列等。與 GiST 類似,GIN 支援使用者定義的索引策略,可透過定義 GIN 索引的特定運算子型別實現不同的功能。比如,它支援包含運算子“@>”、被包含運算子“<@”、相等運算子“=”、重疊運算子“&&”,等等。

CREATE

語法

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name ON table [ USING method ]
    ( { column | ( expression ) } [ opclass ] [, ...] )
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace ]
    [ WHERE predicate ]
  • CONCURRENTLY:併發建立索引。當該選項被使用時,PostgreSQL 會執行表的兩次掃描,因此該方法需要更長一些的時間來建立索引

單列索引

單列索引是一個只基於表的一個列上建立的索引,基本語法如下:

CREATE INDEX index_name ON table_name (column_name);

組合索引

組合索引是基於表的多列上建立的索引,基本語法如下:

CREATE INDEX index_name ON table_name (column1_name, column2_name);

不管是單列索引還是組合索引,該索引必須是在 WHERE 子句的過濾條件中使用非常頻繁的列。

唯一索引

使用唯一索引不僅是為了效能,同時也為了資料的完整性。唯一索引不允許任何重複的值插入到表中。基本語法如下:

CREATE UNIQUE INDEX index_name ON table_name (column_name);

區域性索引

區域性索引是在表的子集上構建的索引;子集由一個條件表示式上定義。索引只包含滿足條件的行。基礎語法如下:

CREATE INDEX index_name ON table_name (conditional_expression);

隱式索引

隱式索引是在建立物件時,由資料庫伺服器自動建立的索引。索引自動建立為主鍵約束和唯一約束。

檢視

  1. \d:檢視錶下的索引

    testdb=# \d company
                      Table "public.company"
     Column  |     Type      | Collation | Nullable | Default 
    ---------+---------------+-----------+----------+---------
     id      | integer       |           | not null | 
     name    | text          |           | not null | 
     age     | integer       |           | not null | 
     address | character(50) |           |          | 
     salary  | real          |           |          | 
    Indexes:
        "company_pkey" PRIMARY KEY, btree (id)
        "salary_index" btree (salary)
  2. \di:檢視所有索引

    runoobdb=# \di
                        List of relations
     Schema |      Name       | Type  |  Owner   |   Table    
    --------+-----------------+-------+----------+------------
     public | company_pkey    | index | postgres | company
     public | department_pkey | index | postgres | department
     public | salary_index    | index | postgres | company
    (3 rows)
  3. SELECT

    select * from pg_indexes where tablename ='table_name';

刪除索引

DROP INDEX index_name;

修改索引

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 ( storage_parameter [, ... ] )

準則

  • 建立

    • 主鍵自動建立唯一索引;

    • 頻繁作為查詢條件的欄位應該建立索引;

    • 查詢中與其他表有關聯的欄位,例如外來鍵關係;

    • 高併發的情況下一般選擇複合索引;

    • 查詢中排序的欄位建立索引將大大提高排序的速度(索引就是排序加快速查詢);

    • 查詢中統計或者分組的欄位;

    • 儲存空間固定的欄位更適合選作索引的關鍵字,例:與 text 型別的欄位相比, char 型別的欄位較為適合選作索引關鍵字

  • 不建立

    • 索引不應該使用在較小的表上。

    • 索引不應該使用在有頻繁的大批次的更新或插入操作的表上。

    • 索引不應該使用在含有大量的 NULL 值的列上。

    • 索引不應該使用在頻繁操作的列上。

    • 經常增刪改的表,不需要建立索引;

    • 表中包含大量重複資料,不需要建立索引,例如性別欄位,只有男女,不適合建立索引;

常見失效原因

  • 沒有查詢條件,或者查詢條件沒有建立索引

  • 在查詢條件上沒有使用引導列

  • 查詢的數量是大表的大部分,應該是 30% 以上

  • 使用內部函式導致索引失效,例:

    select * from test where round(id)=1
  • 表記錄較少

  • 隱式轉換導致索引失效,例如:表的欄位 tu_mdn 定義為 varchar2(20),但在查詢時把該欄位作為 number 型別以 where 條件傳給資料庫:

    • 錯誤的例子:

      select * from test where tu_mdn=13333333333;
    • 正確的例子:

      select * from test where tu_mdn='13333333333';
  • 索引列進行運算導致索引失效,運算包括(+,-,*,/,! 等),例:

    select * from test where round(id)=1
  • like "%_" 百分號在前

  • 單獨引用複合索引裡非第一位置的索引列

  • B-tree索引 is null不會走,is not null會走,點陣圖索引 is null,is not null 都會走

  • 併發建立索引過程中,如果被強行取消,可能會留下一個無效的索引,這個索引仍然會導致更新變慢。如果所建立的是一個唯一索引,這個無效的索引還會導致插入重複值失敗。手工刪除重建即可修復

線上重新建立索引

CREATE INDEX CONCURRENTLY ON <index_name> USING btree(id);

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31490526/viewspace-2742169/,如需轉載,請註明出處,否則將追究法律責任。

相關文章