PostgreSQL中索引与CTE简介

banq

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，您可以执行复杂的查询，否则这些查询将需要更复杂且效率更低的解决方案。