每筆資料 查詢超級慢且效率低下，資料庫管理者或者程式設計師，可以在資料表的任何欄位上分配索引，所以設定在where會用到的欄位，得到額外的索引列表(Search Key + Pointer)，索引通常比原始文件小得多，資料庫會將索引值進行排序，並且可以只看符合條件的內容，所以不需要遍歷所有的索引。

我們來舉個例子，假設我們想要找出薪水低於3萬的人，針對薪水建立索引，資料庫會在背後進行排序，當條件找低於3萬只會找到第一筆資料之後就會中止不會再繼續搜尋後續的資料，使其查詢能夠提升效率。(真實情況下需要大量的資料，才會提升效率)

資料表

索引表

可以怎麼使用索引

• 單欄索引 (SINGLE-COLUMN)
• 多欄索引 (MULTI-COLUMN)
• 唯一索引 (UNIQUE)
• 部分索引 (PARTIAL)
• 隱式索引 (IMPLICIT)

起始資料

範例資料

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(300) NOT NULL,
  email VARCHAR(300) UNIQUE NOT NULL,
  salary INT CHECK (salary > 0)
);

CREATE TABLE addresses (
  id SERIAL PRIMARY KEY,
  address VARCHAR(300) NOT NULL,
  city VARCHAR(300) NOT NULL,
  user_id INT REFERENCES users ON DELETE CASCADE
);

INSERT INTO users (name, email, salary)
VALUES ('王大空', 'faker1@test.com', 45000),
  ('陳小明', 'faker2@test.com', 48000),
  ('張三俊', 'faker3@test.com', 38000),
  ('葛四爺', 'faker4@test.com', 29000),
  ('林五傑', 'faker5@test.com', 33000);

INSERT INTO addresses (address, city, user_id)
VALUES ('林森路101號', '太陽市', 1),
  ('中正路202號', '木市', 2),
  ('光華路303號', '畫市', 3),
  ('安安路404號', '農市', 4),
  ('你好路505號', '海市', 5);

如何確認執行效率

可以使用EXPLAIN與EXPLAIN ANALYZE確認SQL執行出來的速度，我們可以依執行速度來分析是否需要使用INDEX或是SQL語法是否需要調整寫法。

-- 預期效能
EXPLAIN
SELECT * FROM users
WHERE salary < 30000;

-- 實際執行分析
EXPLAIN ANALYZE
SELECT * FROM users
WHERE salary < 30000;

創建INDEX

要給索引一個名子，這邊以salaryidx為例。

CREATE INDEX salaryidx ON users (salary);

創建唯一索引(UNIQUE INDEX)

如果要創建的索引欄位是需要唯一值的情況，可以創建UNIQUE INDEX，不過其實這樣做的效果等同UNIQUE CONSTRAINT，因此這個做法較少被使用。

CREATE UNIQUE INDEX salaryidx ON users (salary);

創建多欄索引(MULTI-COLUMN INDEX)

如果索引的欄位不只一欄，那麼就會把他們一起建立索引，稱為多欄索引(MULTI-COLUMN INDEX)或稱複合索引 (COMPOSITE INDEX)，要注意的是多欄索引，必需是WHERE條件式使用AND才有用，如果是使用OR的話，則必需分別建立單欄索引。

CREATE INDEX multiaddridx ON addresses (address, city)

創建部分索引(PARTIAL INDEX)

最後還有一種情況是，索引只想建立在某個條件當中，例如僅在薪水超過38000的情況才這麼做。

CREATE INDEX salaryidx2 ON users (salary)
WHERE salary >= 38000;

隱式索引

由資料庫自動建立的索引。

• PRIMARY KEY
• UNIQUE KEY

索引類別

PostgreSQL 提供 B-Tree、Hash、GiST、SP-GiST、GIN、BRIN 索引類別，每一種索引類別有其適合的類型，B-Tree使用情況最為常見，也是預設的索引演算法，HASH只能處理簡單的相等比較 (=)。這意味著每當索引列涉及使用等於（=）運算符的比較時，查詢計劃器將考慮使用哈希索引，可以使用USING語法來指定HASH演算法，GIN用來處理一個欄位有多個值的資料，GiST則是可以處理TEXT類型的資料。

B-TREE

<
<=
=
>=
BETWEEN
IN
IS NULL
IS NOT NULL

HASH

CREATE INDEX index_name 
ON table_name USING HASH (indexed_column);

GIN

array
json

GIST

geometric
full-text

什麼情況適合建立索引

1. 主鍵自動建立索引。
2. 索引只建立在要查詢的條件中。(WHERE 會用到的欄位)
3. 儲存空間有定義固定大小更適合，例如VARCHAR比TEXT更適合。

什麼情況不適合建立索引

1. 不要為了加索引而加索引
2. 不要在資料不大的情況下加索引
3. 不要在經常更新的資料表加索引
4. 不要在可能含有大量空值(NULL)的欄位上加索引
5. 不要在有大量重復資料加索引，例如性別欄位。
6. 不要用於計算式上。

不要使用太多索引

太多所以可能獲得更差的性能，或者資料頻繁更動，如果資料庫是變更是每秒更新，那麼每個索引列表也必需每秒更新，這會造成查詢變慢還有整個資料庫性能變差。