每個開發(fā)人員最終都會遇到查詢速度緩慢的問題。當(dāng)表格中的行數(shù)從幾百條增加到幾百萬條時，原本只需要幾毫秒就能完成的查詢現(xiàn)在可能需要幾秒鐘甚至更長的時間才能完成。

解決這個問題的方法通常是創(chuàng)建索引。

數(shù)據(jù)庫索引是一種數(shù)據(jù)結(jié)構(gòu)，它可以幫助數(shù)據(jù)庫更快地找到所需的記錄，而無需掃描整個表格。它的原理與教科書末尾的索引非常相似：你不需要逐頁閱讀內(nèi)容來查找某個主題，而是可以通過索引直接找到對應(yīng)的頁碼并跳到該位置進(jìn)行查詢。

在本教程中，你將學(xué)習(xí)索引在底層是如何工作的，如何在PostgreSQL中有效地創(chuàng)建和使用索引，以及如何避免那些會使得索引變得毫無用處甚至帶來負(fù)面影響的常見錯誤。

目錄

• 先決條件

• 為什么需要索引？

• 索引在底層的工作原理

• 如何創(chuàng)建你的第一個索引

• 如何使用EXPLAIN ANALYZE來評估性能

• PostgreSQL中的索引類型

• 如何創(chuàng)建復(fù)合索引

• 如何創(chuàng)建部分索引

• 如何創(chuàng)建表達(dá)式索引

• 如何創(chuàng)建唯一索引

• 如何管理索引

• 當(dāng)索引起反作用時

• 阻礙索引使用的常見錯誤

• 創(chuàng)建索引的最佳實踐

• 結(jié)論

先決條件

為了能夠跟隨示例進(jìn)行學(xué)習(xí)，你需要具備以下條件：

• 基本的SQL知識（SELECT、INSERT、UPDATE、DELETE、WHERE、JOIN等操作語句）

• 一個正在運行的PostgreSQL實例（版本12或更高版本）

• 一個SQL客戶端工具，例如psql、pgAdmin或DBeaver

如果你沒有在本地安裝PostgreSQL，也可以使用Neon或Supabase等提供的免費云托管實例。

為什么需要索引？

當(dāng)你執(zhí)行像SELECT * FROM users WHERE email = 'jane@example.com'這樣的查詢時，數(shù)據(jù)庫需要找到匹配的記錄。如果沒有索引，PostgreSQL會進(jìn)行順序掃描——它會讀取表格中的每一行數(shù)據(jù)，然后檢查email列的值是否與查詢條件相符。

對于包含100行的表格來說，這種處理方式完全沒問題；但對于包含1000萬行的表格而言，其查詢速度就會慢得令人難以忍受。

索引通過創(chuàng)建一種獨立的、經(jīng)過排序的數(shù)據(jù)結(jié)構(gòu)來解決這個問題，這種結(jié)構(gòu)能夠?qū)⒘兄蹬c對應(yīng)的行位置建立關(guān)聯(lián)。這樣一來，PostgreSQL無需遍歷全部1000萬行數(shù)據(jù)，而是可以直接在索引中查找目標(biāo)值，然后直接跳轉(zhuǎn)到相應(yīng)的行。這樣，查詢時間可以從幾秒縮短到幾毫秒。

然而，使用索引并非沒有代價。在到處添加索引之前，你必須了解其中所涉及的各種權(quán)衡因素。在本教程中，你將會了解到這些權(quán)衡點。

索引的內(nèi)部工作原理

PostgreSQL默認(rèn)使用的索引類型是B樹（平衡樹）。了解B樹的工作原理，將有助于你更明智地決定何時以及如何使用索引。

B樹會將數(shù)據(jù)組織成一種有序的、層次結(jié)構(gòu)分明的數(shù)據(jù)結(jié)構(gòu)，這種結(jié)構(gòu)包含三個層級：

1. 根節(jié)點——位于樹的頂層。它包含一些用于將數(shù)據(jù)范圍劃分開來的值。

2. 內(nèi)部節(jié)點——每個內(nèi)部節(jié)點都會進(jìn)一步縮小數(shù)據(jù)范圍的范圍。

3. 葉節(jié)點——位于結(jié)構(gòu)的最底層。這些節(jié)點保存著實際被索引的值，同時還包含指向表中對應(yīng)行的指針。

當(dāng)PostgreSQL使用B樹索引來查找某個值時，它會從根節(jié)點開始，沿著能夠匹配目標(biāo)值的路徑依次訪問各個內(nèi)部節(jié)點，最終到達(dá)正確的葉節(jié)點。這種查找過程被稱為樹遍歷，即使對于包含數(shù)百萬行的表格來說，這一過程通常也只需要3到4步即可完成。

可以把這個過程想象成翻電話簿：你不會從第一頁開始逐個查看所有名字，而是先找到相應(yīng)的分類目錄（相當(dāng)于根節(jié)點），然后再縮小范圍找到具體的頁面（相當(dāng)于內(nèi)部節(jié)點），最后在 ?? 頁面上查找所需的信息（相當(dāng)于葉節(jié)點）。

正是這種有序的結(jié)構(gòu)，使得B樹索引在處理像WHERE price > 50 AND price < 100這樣的范圍查詢時能夠發(fā)揮出良好的效果。數(shù)據(jù)庫會先在樹結(jié)構(gòu)中找到起始位置，然后依次遍歷那些已經(jīng)按順序排列的葉節(jié)點，從而快速完成查詢。

如何創(chuàng)建你的第一個索引

讓我們通過一個實際例子來學(xué)習(xí)如何操作。你會創(chuàng)建一個表格，向其中插入數(shù)據(jù)，然后親身體會索引帶來的性能提升效果。

步驟1 – 創(chuàng)建表格并插入示例數(shù)據(jù)

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    city VARCHAR(50),
    created_at TIMESTAMP DEFAULT NOW()
);

現(xiàn)在，我們需要插入大量數(shù)據(jù)，以便能夠清楚地看到索引帶來的性能差異。以下代碼會生成50萬條示例數(shù)據(jù)：

INSERT INTO customers (first_name, last_name, email, city)
SELECT
    'User' || gs,
    'Last' || gs,
    'user' || gs || '@example.com',
    (ARRAY['Lagos', 'London', 'New York', 'Berlin', 'Tokyo')[1 + (gs % 5)]
FROM generate_series(1, 500000) AS gs;

步驟 2 – 不使用索引進(jìn)行查詢

EXPLAIN ANALYZE
SELECT * FROM customers WHERE email = 'user250000@example.com';

您會看到類似以下的輸出結(jié)果：

對 customers 表進(jìn)行順序掃描（成本=0.00..11374.00，行數(shù)=1，列寬=52）（實際耗時=45.123..91.456秒，行數(shù)=1，循環(huán)次數(shù)=1）
  過濾條件：((email)::text = 'user250000@example.com'::text)
  被過濾掉的行數(shù)：499999
  規(guī)劃時間：0.085毫秒
  執(zhí)行時間：91.502毫秒

這里的關(guān)鍵點是順序掃描——PostgreSQL掃描了全部500,000行數(shù)據(jù)才找到了匹配的結(jié)果。這意味著有499,999行數(shù)據(jù)被白白掃描了，造成了大量的資源浪費。

步驟 3 – 創(chuàng)建索引

CREATE INDEX idx_customers_email ON customers (email);

這條命令會在email列上創(chuàng)建一個B樹索引。索引名稱idx_customers_email遵循了常見的命名規(guī)則：以idx_為前綴，接著是表名，最后是列名。

步驟 4 – 使用索引進(jìn)行查詢

再次運行相同的查詢：

EXPLAIN ANALYZE
SELECT * FROM customers WHERE email = 'user250000@example.com';

現(xiàn)在您會看到如下輸出結(jié)果：

如何使用EXPLAIN ANALYZE來評估性能
EXPLAIN ANALYZE是您了解PostgreSQL如何執(zhí)行查詢命令的重要工具。在上一節(jié)中您已經(jīng)看到了它的用法，現(xiàn)在讓我們詳細(xì)分析一下這些輸出信息的含義。
EXPLAIN ANALYZE SELECT * FROM customers WHERE city = 'Lagos';

輸出結(jié)果會告訴您以下幾項信息：


掃描類型——PostgreSQL是使用了順序掃描、索引掃描、位圖索引掃描，還是其他訪問方式


成本——以任意單位表示的預(yù)估執(zhí)行成本。第一個數(shù)字代表初始化成本，第二個數(shù)字代表總成本


匹配的行數(shù)——PostgreSQL估計會找到多少行數(shù)據(jù)，實際找到的又是多少行


實際執(zhí)行時間——查詢命令實際完成所需的時間，單位為毫秒


被過濾掉的行數(shù)

——雖然被掃描了，但并不滿足查詢條件的行數(shù)



如果你在一個大型表上看到使用了選擇性WHERE子句的Seq Scan操作，那么這通常說明你需要創(chuàng)建索引。而當(dāng)看到Index Scan或Index Only Scan時，就意味著你的索引正在正常發(fā)揮作用。
需要記住的一點是：EXPLAIN命令在沒有ANALYZE選項的情況下，只會顯示查詢的執(zhí)行計劃而不會實際執(zhí)行該查詢。EXPLAIN ANALYZE則會執(zhí)行查詢并顯示真實的執(zhí)行時間數(shù)據(jù)。在分析性能問題時，一定要使用EXPLAIN ANALYZE；但對于那些可能會修改數(shù)據(jù)的查詢來說，使用這個命令時要特別小心——比如EXPLAIN ANALYZE DELETE FROM ...這樣的命令實際上會刪除數(shù)據(jù)表中的記錄。因此，在使用這類命令時，應(yīng)該將其放在事務(wù)中執(zhí)行，并在操作完成后進(jìn)行回滾：
BEGIN;
EXPLAIN ANALYZE DELETE FROM customers WHERE city = 'Berlin';
ROLLBACK;

PostgreSQL中的索引類型
PostgreSQL支持多種類型的索引，每種索引都針對不同的查詢模式進(jìn)行了優(yōu)化。
B樹（默認(rèn)類型）
B樹是默認(rèn)的索引類型，適用于絕大多數(shù)使用場景。它支持相等性檢查(=)、范圍查詢(<, >, <=, >=, BETWEEN),排序操作(ORDER BY),以及IS NULL/IS NOT NULL判斷。
-- 這兩種語句是等效的——B樹是默認(rèn)類型
CREATE INDEX idx_name ON customers (last_name);
CREATE INDEX idx_name ON customers USING btree (last_name);

除非有特別的理由需要使用其他類型的索引，否則應(yīng)該優(yōu)先選擇B樹。
哈希索引
哈希索引專為相等性比較(=)而設(shè)計，不支持范圍查詢或排序操作。實際上，B樹在處理相等性檢查時速度也幾乎與哈希索引相當(dāng)，因此很少有必要使用哈希索引。
CREATE INDEX idx_email_hash ON customers USING hash (email);

只有當(dāng)你擁有一個規(guī)模非常大的表，并且經(jīng)常需要對表中的數(shù)據(jù)進(jìn)行僅針對相等性條件的查詢，同時又希望節(jié)省一些索引空間時，才可以考慮使用哈希索引。
GIN（通用倒排索引）
GIN索引適用于那些包含多個元素的值，比如數(shù)組、JSONB格式的數(shù)據(jù)或全文搜索所需的數(shù)據(jù)結(jié)構(gòu)。與傳統(tǒng)的索引不同，GIN索引會為這些值中的每個元素分別創(chuàng)建索引條目，而不是只為每行數(shù)據(jù)創(chuàng)建一個索引。
-- 添加一個JSONB類型的列
ALTER TABLE customers ADD COLUMN preferences JSONB DEFAULT '{}';

-- 為這個JSONB列創(chuàng)建索引
CREATE INDEX idx_preferences ON customers USING gin (preferences);

-- 現(xiàn)在，這個查詢就會使用GIN索引了
SELECT * FROM customers WHERE preferences @> '{"newsletter": true '}';

當(dāng)你在JSONB數(shù)據(jù)中執(zhí)行查詢、使用@>或&&來搜索數(shù)組中的元素，或者使用tsvector進(jìn)行全文搜索時，應(yīng)該考慮使用GIN索引。
GiST（通用搜索樹）
GiST索引支持幾何數(shù)據(jù)、范圍值以及全文搜索功能。它們通常與PostGIS結(jié)合使用，用于執(zhí)行地理空間相關(guān)的查詢。
-- 范圍類型的示例
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    duration TSRANGE
);

CREATE INDEX idx_event_duration ON events USING gist (duration);

-- 查找重疊的事件
SELECT * FROM events WHERE duration && '[2025-01-01, 2025-01-31]'::tsrange;

在處理空間數(shù)據(jù)、范圍類型的數(shù)據(jù)，或者需要使用重疊/包含操作符時，請使用GiST索引。
BRIN（塊范圍索引）
BRIN索引體積非常小，且適用于那些物理行順序與被索引列的值相匹配的大型表格。一個常見的例子就是那些只能進(jìn)行追加操作的表格中的時間戳列——新添加的行總是具有更晚的時間戳。
CREATE INDEX idx_created_at_brin ON customers USING brin (created_at);

BRIN索引會為每組連續(xù)的行存儲匯總信息（如最小值/最大值），而不是對每一行都進(jìn)行索引處理。因此，這種索引的結(jié)構(gòu)要比B樹索引小得多，但它只適用于數(shù)據(jù)本身已經(jīng)有序的情況。
對于那些數(shù)據(jù)量非常大、且只能進(jìn)行追加操作、同時數(shù)據(jù)又具有自然排序順序的表格來說，BRIN索引是非常適用的——比如日志記錄、事件信息或時間序列數(shù)據(jù)等。
如何創(chuàng)建復(fù)合索引
復(fù)合索引（也稱為多列索引）能夠覆蓋多個列。當(dāng)你的查詢經(jīng)常需要同時根據(jù)多個列進(jìn)行篩選或排序時，使用復(fù)合索引會非常方便。
CREATE INDEX idx_city_lastname ON customers (city, last_name);

在復(fù)合索引中，列的排列順序非常重要。PostgreSQL可以使用這種索引來執(zhí)行那些僅基于city列進(jìn)行篩選的查詢，也可以用來執(zhí)行同時基于city和last_name列進(jìn)行篩選的查詢；但是，對于那些只基于last_name列進(jìn)行篩選的查詢來說，PostgreSQL就無法高效地利用這種索引。
可以把這種機(jī)制想象成按城市先排序、然后再在每個城市內(nèi)按姓氏排序的電話簿：你可以輕松地找到拉各斯的所有居民，也可以找到拉各斯所有姓“Adeyemi”的人；但要想找到所有城市中姓“Adeyemi”的人，就必須掃描整本電話簿。
這個原則被稱為最左前綴規(guī)則：PostgreSQL可以使用復(fù)合索引來執(zhí)行那些包含索引中最左邊列的查詢，但對于那些跳過了最左邊列的查詢，則無法使用這種索引。
-- ? 使用了索引（匹配了最左邊的列）
SELECT * FROM customers WHERE city = 'Lagos';

-- ? 使用了索引（同時匹配了最左邊和右邊的列）
SELECT * FROM customers WHERE city = 'Lagos' AND last_name = 'Adeyemi';

-- ? 無法高效地使用此索引（跳過了最左邊的列）
SELECT * FROM customers WHERE last_name = 'Adeyemi';

在決定列的排列順序時，應(yīng)該將那些能夠最大程度縮小查詢結(jié)果范圍的列放在最前面。
如何創(chuàng)建部分索引
部分索引僅覆蓋表中的一部分行。你可以通過在索引定義中使用WHERE子句來指定這一部分行。
當(dāng)你只需要查詢數(shù)據(jù)中的特定部分時，這種索引非常有用。例如，如果你有一個orders表，而你經(jīng)常查詢待處理的訂單信息，但很少查看已完成的訂單：
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    total NUMERIC(10, 2),
    created_at TIMESTAMP DEFAULT NOW()
);

-- 僅為狀態(tài)為'pending'的記錄創(chuàng)建索引
CREATE INDEX idx_orders_pending ON orders (customer_id)
WHERE status = 'pending';

由于這種索引會跳過所有不符合WHERE條件的記錄，因此它的大小會比全索引小。體積較小的索引會占用更少的磁盤空間、消耗更少的內(nèi)存，并且在寫入數(shù)據(jù)時維護(hù)起來也會更快。
要想讓索引能夠被查詢語句使用，你的查詢中的WHERE子句必須與索引的條件相匹配：
-- ? 使用了部分索引
SELECT * FROM orders WHERE status = 'pending' AND customer_id = 42;

-- ? 無法使用部分索引（狀態(tài)不同）
SELECT * FROM orders WHERE status = 'shipped' AND customer_id = 42;

如何創(chuàng)建表達(dá)式索引
有時，你需要為函數(shù)或表達(dá)式的計算結(jié)果創(chuàng)建索引，而不是為原始的列值創(chuàng)建索引。表達(dá)式索引（也稱為功能索引）就可以滿足這種需求。
一個常見的例子就是不區(qū)分大小寫的電子郵件查詢。如果你的查詢中使用了LOWER(email)函數(shù)，那么在email列上創(chuàng)建的普通索引是無法幫助你完成查詢的——因為PostgreSQL會將這個函數(shù)調(diào)用視為一個不同的表達(dá)式。
-- 在email列上創(chuàng)建的普通索引對使用LOWER()函數(shù)的查詢沒有幫助
CREATE INDEX idx_email ON customers (email);

-- 下面的查詢不會使用上面的索引
SELECT * FROM customers WHERE LOWER(email) = 'user100@example.com';

為了解決這個問題，你需要在表達(dá)式本身上創(chuàng)建一個索引：
CREATE INDEX idx_email_lower ON customers (LOWER(email));

現(xiàn)在，那些在WHERE子句中使用了LOWER(email)的查詢就會使用這個新創(chuàng)建的索引了：
-- ? 使用了表達(dá)式索引
SELECT * FROM customers WHERE LOWER(email) = 'user100@example.com';

規(guī)則很簡單：你的查詢中的表達(dá)式必須與索引中的表達(dá)式完全一致。如果索引是基于LOWER(email)這個表達(dá)式創(chuàng)建的，那么你的查詢中也必須使用LOWER(email)。
如何創(chuàng)建唯一索引
唯一索引可以確保被索引列中的任何兩行都不會具有相同的值（或值組合）。它具有雙重作用：既能夠保證數(shù)據(jù)的一致性，又能提高查詢效率。
CREATE UNIQUE INDEX idx_customers_email_unique ON customers (email);
如果你嘗試插入重復(fù)的值，PostgreSQL會拒絕這個操作：
INSERT INTO customers (first_name, last_name, email, city)
VALUES ('Test', 'User', 'user1@example.com', 'Lagos');
-- 錯誤：重復(fù)的鍵值違反了唯一性約束 "idx_customers_email_unique"

你可能會想知道，這與 UNIQUE 約束有什么不同。實際上，PostgreSQL 是通過創(chuàng)建唯一索引來實現(xiàn) UNIQUE 約束的。從功能上來說，這兩種方式是完全相同的。
區(qū)別在于它們的目的：UNIQUE 約束體現(xiàn)的是數(shù)據(jù)完整性規(guī)則，而唯一索引則主要是為了提升查詢性能，同時順便滿足唯一性要求。
如何管理索引
隨著數(shù)據(jù)庫規(guī)模的擴(kuò)大，你將需要定期檢查、監(jiān)控并維護(hù)這些索引。
如何列出表上的所有索引
SELECT
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename = 'customers';

這條查詢可以顯示表上每個索引的名稱及其完整定義。
如何檢查索引大小
SELECT
    pg_size_pretty(pg_relation_size('idx_customers_email')) AS index_size;

如果你想全面了解所有索引及其大小，可以執(zhí)行以下查詢：
SELECT
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'customers'
ORDER BY pg_relation_size(indexrelid) DESC;

如何查找未使用的索引
那些從未被使用過的索引會浪費磁盤空間，并降低數(shù)據(jù)寫入的速度。你可以通過查詢 pg_stat_user_indexes 來找到這些索引：
SELECT
    indexrelname AS index_name,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_userindexes
WHERE relname = 'customers'
AND idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

如果某個索引在經(jīng)過一段時間的正常使用后，其 idx_scan 值仍然為 0，那么這個索引就可以被刪除了。不過，在做出決定之前，請確保觀察整個業(yè)務(wù)周期內(nèi)的使用情況——因為有些索引可能只在每月生成報告或進(jìn)行季節(jié)性操作時才會被使用。
如何刪除索引
DROP INDEX IF EXISTS idx_customers_email;
如果你要刪除生產(chǎn)環(huán)境中的表上的索引，并且希望避免因此導(dǎo)致寫操作被鎖定，可以使用 CONCURRENTLY 關(guān)鍵字：
DROP INDEX CONCURRENTLY IF EXISTS idx_customers_email;
如何重建索引
隨著時間的推移，由于數(shù)據(jù)的插入、更新和刪除操作，索引文件的大小可能會逐漸增大。你可以通過重建索引來釋放占用的空間：
REINDEX INDEX idx_customers_email;
或者，可以重建表中的所有索引：
REINDEX TABLE customers;
在生產(chǎn)系統(tǒng)中，應(yīng)使用 REINDEX CONCURRENTLY（PostgreSQL 12及以上版本支持）來避免鎖定表格：
REINDEX INDEX CONCURRENTLY idx_customers_email;
當(dāng)索引反而帶來負(fù)面影響時
索引并非毫無成本。每當(dāng)添加一個索引，都會產(chǎn)生相應(yīng)的開銷：


寫操作的開銷 — 每次執(zhí)行 INSERT、UPDATE 或 DELETE 操作時，都需要更新表中所有的索引。如果一個表有10個索引，而你插入了一條記錄，PostgreSQL實際上會執(zhí)行11次寫操作（一次針對表本身，另外10次分別針對每個索引）。對于那些需要頻繁進(jìn)行寫操作的表格來說，過多的索引會顯著降低數(shù)據(jù)修改的效率。


存儲成本 — 索引會占用磁盤空間。對于大型表格而言，索引所占用的空間可能與表本身相當(dāng)，甚至更多。你可以使用 pg_relation_size 命令來查看這些信息。


內(nèi)存消耗 — PostgreSQL 會將經(jīng)常被使用的索引緩存在內(nèi)存中。索引越多，內(nèi)存壓力就會越大，這可能會導(dǎo)致一些有用的數(shù)據(jù)被從緩存中移除，從而影響其他查詢的效率。


維護(hù)成本 — 索引需要定期進(jìn)行維護(hù)（如清除無效數(shù)據(jù)、重新構(gòu)建索引等），這些操作會增加數(shù)據(jù)庫架構(gòu)遷移的復(fù)雜性。



因此，我們應(yīng)該問自己的不是“是否應(yīng)該添加索引？”，而是“對于當(dāng)前這個表格的工作負(fù)載來說，讀取性能的提升是否足以彌補(bǔ)寫入操作所帶來的開銷？”
阻礙索引使用的常見錯誤
即使你創(chuàng)建了完美的索引，PostgreSQL也可能不會使用它。以下是一些最常見的原因。
在函數(shù)中使用了被索引的列
-- 為 email 列創(chuàng)建索引
CREATE INDEX idx_email ON customers (email);

-- ? 因為使用了 LOWER() 函數(shù)，PostgreSQL 無法使用該索引
SELECT * FROM customers WHERE LOWER(email) = 'user1@example.com';

-- ? 解決方法：為 LOWER(email) 創(chuàng)建一個表達(dá)式索引
CREATE INDEX idx_email_lower ON customers (LOWER(email));

如果在 WHERE 子句中使用了被索引的列，并且該列被放在了函數(shù)內(nèi)部，那么 PostgreSQL 就不會使用這個索引。此時，你需要創(chuàng)建一個與所使用的函數(shù)相匹配的表達(dá)式索引。
隱式類型轉(zhuǎn)換
-- id 是一個帶有索引的 INTEGER 類型列
-- ? 將字符串值賦給這個列會觸發(fā)類型轉(zhuǎn)換，從而導(dǎo)致索引無法被使用
SELECT * FROM customers WHERE id = '42';

-- ? 應(yīng)該使用正確的數(shù)據(jù)類型
SELECT * FROM customers WHERE id = 42;

當(dāng)查詢中的值類型與列的類型不匹配時，PostgreSQL 會嘗試進(jìn)行類型轉(zhuǎn)換，而這種轉(zhuǎn)換可能會導(dǎo)致索引無法被使用。
在不同列上使用 OR 條件
-- ? 在不同的列上使用 OR 運算會阻礙索引的使用
SELECT * FROM customers WHERE email = 'user1@example.com' OR city = 'Lagos';

-- ? 將查詢改寫為 UNION 可以更好地利用索引
SELECT * FROM customers WHERE email = 'user1@example.com'
UNION
SELECT * FROM customers WHERE city = 'Lagos';

在 LIKE 查詢中使用前置通配符
-- ? 前置通配符會使得數(shù)據(jù)庫無法使用 B 樹索引
SELECT * FROM customers WHERE email LIKE '%@example.com';

-- ? 后置通配符可以使用 B 樹索引
SELECT * FROM customers WHERE email LIKE 'user1%';

B 樹索引是按照從左到右的順序進(jìn)行排序的。如果查詢中使用前置通配符（如 %something），數(shù)據(jù)庫就無法利用這種排序結(jié)構(gòu)，而不得不進(jìn)行順序掃描。如果你需要根據(jù)后綴或子字符串來進(jìn)行搜索，可以考慮使用帶有 pg_trgm 擴(kuò)展名的 GIN 索引。
選擇度低
如果某列中不同值的數(shù)量相對于總行數(shù)來說非常少（即選擇度較低），PostgreSQL 可能會認(rèn)為進(jìn)行順序掃描比使用索引更快。
例如，如果一個 status 列只有三種可能的值（'pending'、'shipped'、'delivered'），且每種值對應(yīng)的記錄數(shù)大約占表格總記錄數(shù)的三分之一，那么僅為 status 列創(chuàng)建索引并不會帶來太大效果。PostgreSQL 仍然需要讀取表格中的大部分?jǐn)?shù)據(jù)，而額外的索引查詢操作只會增加性能開銷。
在這種情況下，創(chuàng)建部分索引通常會是更好的解決方案。
建立索引的最佳實踐
以下是一些關(guān)鍵原則的總結(jié)：


為那些出現(xiàn)在 WHERE、JOIN 和 ORDER BY 子句中的列創(chuàng)建索引。這些正是數(shù)據(jù)庫需要進(jìn)行搜索、匹配或排序的列。優(yōu)先考慮那些執(zhí)行頻率最高或耗時最長的查詢。


使用 EXPLAIN ANALYZE 來測試創(chuàng)建索引前后的性能差異。千萬不要憑猜測來決定是否創(chuàng)建索引。先使用 EXPLAIN ANALYZE 執(zhí)行查詢，然后創(chuàng)建索引后再重新執(zhí)行查詢。如果執(zhí)行時間沒有明顯改善，那就說明這個索引并沒有起到預(yù)期的作用。


不要為所有列都創(chuàng)建索引。每個索引都會降低寫入速度并占用更多存儲空間。要根據(jù)實際的查詢模式來有針對性地創(chuàng)建索引。


對于需要同時過濾多個列的數(shù)據(jù)，使用復(fù)合索引會更高效。如果你的查詢經(jīng)常同時涉及 city 和 last_name 這兩列，那么為 (city, last_name) 創(chuàng)建復(fù)合索引會比分別為這兩列創(chuàng)建單獨的索引更有效率。



在復(fù)合索引中，將選擇度最高的列放在首位。能夠最有效地縮小搜索范圍的列應(yīng)該被排在索引的第一位。


當(dāng)只需要查詢數(shù)據(jù)的一部分時，使用部分索引會更有優(yōu)勢。如果 90% 的查詢都是針對那些 status = 'active' 的記錄進(jìn)行的，那么為這些記錄創(chuàng)建部分索引會比創(chuàng)建完整索引更加高效且占用更少的存儲空間。



定期監(jiān)控索引的使用情況。通過執(zhí)行 pg_stat_user_indexes 命令來檢查哪些索引沒有被使用到，并及時刪除它們。



定期重新構(gòu)建那些變得臃腫的索引。對于那些經(jīng)常發(fā)生大量更新或刪除操作的表格，索引很容易變得龐大且效率低下。在生產(chǎn)環(huán)境中，可以使用 REINDEX CONCURRENTLY 命令來重新構(gòu)建索引。


結(jié)論
通過本教程，你了解了什么是數(shù)據(jù)庫索引，以及為什么它們對查詢性能至關(guān)重要。你學(xué)習(xí)了B樹索引在內(nèi)部是如何工作的，創(chuàng)建了多種類型的索引（單列索引、復(fù)合索引、部分索引、表達(dá)式索引和唯一索引），并使用EXPLAIN ANALYZE命令來評估這些索引的實際效果。
你還了解了索引會帶來的一些權(quán)衡因素——寫入開銷、存儲成本以及內(nèi)存占用問題——同時也認(rèn)識到了那些會阻礙PostgreSQL有效利用這些索引的常見錯誤。
核心原則很簡單：要根據(jù)你的實際查詢需求有針對性地創(chuàng)建索引，然后測試其效果，及時刪除那些無法發(fā)揮應(yīng)有作用的索引。
如果你覺得本教程對你有幫助，可以在freeCodeCamp上閱讀我更多的文章，在LinkedIn和X平臺上與我保持聯(lián)系。