CodeGym /課程 /SQL SELF /用 pg_stat_statements 來分析索引和過濾器的使用情況

用 `pg_stat_statements` 來分析索引和過濾器的使用情況

SQL SELF

等級 42, 課堂 3

開放

索引就像書裡的書籤一樣，幫你快速找到想要的資料。但如果我們加了一堆書籤，結果根本沒人在用？或者更慘，書籤設計很爛，害我們還是要從頭翻到尾？這時候就需要分析一下索引的使用情況啦。

寫得不好的查詢可能會直接無視索引，導致很貴的順序掃描（Seq Scan）。這樣查詢就會變慢，伺服器壓力也會變大。我們的目標就是搞清楚哪些查詢沒用到索引，還有為什麼。

怎麼知道索引有沒有被用到？

來看兩個重點問題：

我們建的索引到底有沒有被用到？
如果有用到，效果好不好？

為了搞清楚這些，我們可以分析 pg_stat_statements 裡的查詢統計，重點看幾個欄位：

rows：查詢處理的資料列數。
shared_blks_hit：從記憶體（不是硬碟）讀到的資料頁數。
shared_blks_read：實際從硬碟讀到的資料頁數。

查詢處理的資料列越少，shared_blks_hit 佔總頁數的比例越高，代表索引效果越好。

索引分析範例

假設我們有一個學生的資料表：

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birth_date DATE,
    grade_level INTEGER
);

-- 幫 grade_level 建一個索引
CREATE INDEX idx_grade_level ON students(grade_level);

現在來塞點資料做實驗：

INSERT INTO students (first_name, last_name, birth_date, grade_level)
SELECT 
    '學生 ' || generate_series(1, 100000),
    '姓氏',
    '2000-01-01'::DATE + (random() * 3650)::INT,
    floor(random() * 12)::INT
FROM generate_series(1, 100000);

來查找特定年級的學生：

SELECT *
FROM students
WHERE grade_level = 10;

在 `pg_stat_statements` 裡檢查

查詢執行幾次後，可以這樣查統計：

SELECT query, calls, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE query LIKE '%grade_level = 10%';

結果怎麼看：

rows：如果查詢回傳太多資料列，這個索引有意義嗎？也許對低選擇性的條件來說根本不需要索引。
shared_blks_hit 和 shared_blks_read：如果很多頁是從硬碟讀的（shared_blks_read），那可能是索引沒發揮作用，或資料根本不在 buffer pool 裡。

索引優化

建索引只是第一步，重點是 PostgreSQL 真的有用它。有時候不管你怎麼努力，資料庫還是選擇順序掃描。為什麼會這樣？來看看原因。

先搞清楚為什麼明明有索引卻沒被用到，然後再學幾招讓資料庫「想起來」我們有索引，該用就用。

如果索引沒被用到怎麼辦？

有時 PostgreSQL 會無視索引，直接順序掃描（Seq Scan）。可能原因有：

條件選擇性太低。如果查詢會回傳超過一半的資料列，順序掃描可能還比較快。
資料型別或函數。如果你在查詢裡對索引欄位用了函數，索引可能就被跳過了。像這樣：

   SELECT *
   FROM students
   WHERE grade_level + 1 = 11; -- 索引沒被用到

這時可以改寫查詢：

   SELECT * 
   FROM students
   WHERE grade_level = 10; -- 會用到索引

索引型別不對。比如做全文檢索時，應該用 GIN 或 GiST 索引，不要用 B-TREE。
統計資訊有問題。如果統計資訊過時，優化器可能會做錯決定。用 ANALYZE 來更新：
```
ANALYZE students;
```

怎麼讓查詢更有效率

回到我們的例子。如果索引沒發揮作用，可以試試這幾招：

確定查詢用的過濾條件能用到索引：不要用函數、型別轉換等等。
如果過濾條件會回傳很多資料，想想索引有沒有必要。如果這是常用查詢，可以考慮改資料表結構或加 materialized view。
如果因為資料太多導致 Seq Scan，可以考慮把表分區（PARTITION BY）。

怎麼檢查索引效果

優化完再跑一次查詢，然後檢查統計：

SELECT query, calls, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE query LIKE '%grade_level%';

比較優化前後的指標。你應該會看到硬碟讀取（shared_blks_read）變少，命中（shared_blks_hit）變多。

實戰案例

索引用錯方式

我們有一個產品表，裡面有個 description 文字欄位：

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    description TEXT
);

-- 做全文檢索的索引
CREATE INDEX idx_description ON products USING GIN (to_tsvector('english', description));

如果我們查詢：

SELECT *
FROM products
WHERE description ILIKE '%smartphone%';

這時候索引不會被用到！因為 ILIKE 跟 GIN 不合。要用索引的話，查詢要這樣寫：

SELECT *
FROM products
WHERE to_tsvector('english', description) @@ to_tsquery('smartphone');

缺少必要的索引

假設查詢：

SELECT *
FROM students
WHERE birth_date BETWEEN '2001-01-01' AND '2003-01-01';

結果是順序掃描（Seq Scan）。這可能是因為 birth_date 沒有索引。建一個索引：

CREATE INDEX idx_birth_date ON students(birth_date);

然後更新統計（ANALYZE students），這樣查詢速度就會快很多啦。

先前先前課堂

下一個下一堂課

留言

TO VIEW ALL COMMENTS OR TO MAKE A COMMENT,
GO TO FULL VERSION

用 pg_stat_statements 來分析索引和過濾器的使用情況