PostgreSQL中索引與CTE簡介

banq發表於2024-06-04
SQL索引

PostgreSQL 中的索引是用於提高資料庫表上資料檢索操作速度的資料結構。它們的工作原理是提供一種基於特定列的值查詢資料的更快方法。讓我們深入瞭解 postgresql 資料庫中的索引。

瞭解 PostgreSQL 索引
PostgreSQL 中的索引類似於書中的索引部分。它們幫助資料庫伺服器根據一個或多個列的值快速找到表中的行。PostgreSQL 提供各種型別的索引，透過高效檢索資料來提高查詢效能。

索引用於加速 SELECT 查詢，尤其是在根據某些列過濾、排序或連線資料時。它們還有助於強制唯一性並加速資料修改操作（例如 INSERT、UPDATE 和 DELETE），儘管它們可能會給這些操作帶來開銷。

優點

  • 改進的查詢效能：索引可以顯著減少從表中檢索資料所需的時間，特別是對於大型資料集。
  • 約束的執行：可以使用索引強制執行唯一和主鍵約束，確保資料完整性。
  • 促進排序和連線：索引使排序和連線操作更加高效，從而加快查詢執行速度。

最佳實踐
  • 識別高影響查詢：分析應用程式的查詢模式，以確定哪些查詢可以從索引中受益最多。
  • 明智地選擇索引列：選擇在 WHERE 子句、JOIN 條件或 ORDER BY 子句中經常使用的列。
  • 避免過度索引：過多的索引會降低效能，因為每個索引在資料修改操作期間都會產生開銷。
  • 定期維護：定期監控和維護索引以確保其保持有效。這包括定期重新索引和更新統計資料。
  • 考慮索引型別：瞭解 PostgreSQL 中可用的不同型別的索引，並根據您的資料和查詢模式選擇適當的型別。

索引型別
PostgreSQL 支援各種型別的索引，包括 B-Tree、Hash、GiST、GIN 和 BRIN 索引。每種型別都有其優點和用例。

B樹索引
B 樹索引是 PostgreSQL 中的預設索引型別，適用於大多數型別的查詢。
-- Create a B-tree index
CREATE INDEX btree_index ON table_name(column_name);

雜湊索引
雜湊索引對於平等性檢查很有用，但不適合範圍查詢或排序。
-- Create a Hash index
CREATE INDEX hash_index ON table_name(column_name) USING hash;

GiST 索引
廣義搜尋樹 (GiST) 索引對於索引幾何物件等複雜資料型別很有用。
-- Create a GiST index
CREATE INDEX gist_index ON table_name USING gist(column_name);

GIN 索引
通用倒排索引（GIN）適用於索引陣列和全文搜尋資料型別。
-- Create a GIN index
CREATE INDEX gin_index ON table_name USING gin(column_name);

BRIN索引
塊範圍索引 (BRIN) 對於非常大的表很有用，它維護有關值範圍的彙總資訊。
- Create a BRIN index
CREATE INDEX brin_index ON table_name USING brin(column_name);

PostgreSQL 索引型別比較
B-樹

  • 高效執行相等性和範圍查詢
  • 支援排序和搜尋

  • 經常查詢的列上的單列索引
  • WHERE 子句中使用的多列複合索引

雜湊

  • 相等性比較非常快
  • 恆定時間搜尋

  • 對經常訪問的列進行精確匹配查詢
  • 不適合範圍查詢或排序

GiST

  • 支援複雜資料型別（例如幾何物件）的索引
  • 允許自定義索引方法

  • 全文搜尋
  • 地理空間資料索引

GIN

  • 針對索引陣列和全文搜尋資料型別進行了最佳化
  • 支援高階搜尋操作

  • 索引陣列列
  • 全文搜尋

BRIN

  • 資料塊的緊湊表示
  • 對於非常大的表來說很有效

  • 包含排序資料的大型表
  • 聚合一系列值的資料

PostgreSQL – 遞迴 CTE
通用表表示式 (CTE)是 SQL 中的一項強大功能，它允許您建立可在 SELECT、INSERT、UPDATE 或 DELETE 語句中引用的臨時結果集。遞迴 CTE 透過允許 CTE 引用自身來擴充套件此功能，從而實現遞迴查詢的執行。

什麼是遞迴 CTE？
遞迴 CTE是引用自身的 CTE，通常用於遍歷分層或樹形結構的資料。它由兩部分組成：

  • 錨點成員是非遞迴查詢。
  • 遞迴成員是引用 CTE 本身的遞迴查詢。

遞迴查詢重複將遞迴成員應用於其輸出，直到不再返回行。

遞迴 CTE 的優點

  • 簡單性和可讀性：遞迴 CTE 允許以簡單易讀的方式編寫複雜的分層和遞迴查詢，從而提高可維護性。
  • 模組化：CTE 將複雜的查詢分解為模組化部分，使其更易於理解和除錯。
  • 效能：遞迴 CTE 比處理分層資料的其他方法（例如應用程式程式碼中的自連線或迴圈）更有效。
  • 靈活性：它們提供了一種靈活的方法，可以直接在 SQL 中執行復雜的遞迴操作，而無需過程程式碼。
  • 臨時儲存：CTE 使用自動管理的臨時儲存，使開發人員免於管理臨時表的開銷。

遞迴 CTE 的用例
  • 分層資料：管理和查詢分層資料，例如組織結構、檔案系統和物料清單。
  • 圖遍歷：執行圖遍歷操作，例如查詢網路中的所有路徑、識別連線的元件或檢測迴圈。
  • 資料聚合：聚合層次結構不同級別的資料，例如計算公司、部門和個人員工的總銷售額。
  • 生成序列：建立數字或日期序列，例如生成斐波那契數列或生成給定範圍的日期列表。
  • 樹結構：使用樹結構，包括在決策支援系統或遊戲開發中查詢和操作樹。

考慮表中儲存的組織層次結構employees：

create table employees
(
    employee_id SERIAL primary key,
    employee_name VARCHAR(100),
    manager_id INTEGER
        references employees (employee_id)
);
 
insert
    into
    employees
(
    employee_name,
    manager_id
)
values
('Alice',
null),
('Bob',
1),
('Charlie',
1),
('David',
2),
('Eve',
2),
('Frank',
3);

該表定義了一個簡單的層次結構，其中 Alice 為最高階別的經理。Bob 和 Charlie 向 Alice 彙報，David 和 Eve 向 Bob 彙報，Frank 向 Charlie 彙報。

我們可以使用遞迴 CTE 列出層次結構中的所有員工，從最高階別的經理開始：

with recursive employee_hierarchy as (
-- Anchor member
select
    employee_id,
    employee_name,
    manager_id,
    1 as level
from
    employees
where
    manager_id is null
union all
-- Recursive member
select
    e.employee_id,
    e.employee_name,
    e.manager_id,
    eh.level + 1 as level
from
    employees e
inner join employee_hierarchy eh on
    e.manager_id = eh.employee_id
)
select
    employee_id,
    employee_name,
    manager_id,
    level
from
    employee_hierarchy
order by
    level,
    manager_id;

在此查詢中：

  • 錨成員選擇最高階別的經理（沒有經理的經理）。
  • 遞迴成員將員工表與 CTE 連線起來，以查詢當前級別員工的下屬，每次增加級別。

PostgreSQL 中的遞迴 CTE 是處理分層和遞迴資料結構的強大工具。透過理解和利用遞迴 CTE，您可以執行復雜的查詢，否則這些查詢將需要更復雜且效率更低的解決方案。

相關文章