pg-index-health – PostgreSQL 数据库的静态分析工具-java教程-PHP中文网

首页

Java

java教程

pg-index-health – PostgreSQL 数据库的静态分析工具

Linda Hamilton

Jan 06, 2025 pm 06:20 PM

你好！

自 2019 年以来，我一直在开发一个名为 pg-index-health 的开源工具，它可以分析数据库结构并识别潜在问题。在我之前的一篇文章中，我分享了这个工具如何诞生的故事。

多年来，pg-index-health 不断发展和改进。 2024 年，在几位贡献者的支持下，我成功解决了大部分剩余的“成长的烦恼”，并使该项目达到了可以大规模扩展的状态。

数据库随着微服务的兴起而增长

自 2015 年以来，我一直在使用 PostgreSQL，这段迷人的旅程始于位于雅罗斯拉夫尔的 Tensor 公司。

早在2015年，那还是一个拥有海量数据库和大量表的单体时代。通常，对此类数据库结构的任何更改都需要获得作为关键知识持有者的架构师或开发主管的强制批准。虽然这可以防止大多数错误，但它减慢了更改的过程并且完全无法扩展。

渐渐地，人们开始转向微服务。
数据库的数量显着增加，但每个数据库中的表数量却相反减少。现在，每个团队开始独立管理自己的数据库结构。集中的专业知识来源消失了，数据库设计错误开始成倍增加并从一项服务传播到另一项服务。

测试金字塔及其形状

你们中的大多数人可能都听说过测试金字塔。对于整体而言，它具有相当典型的形状和广泛的单元测试基础。欲了解更多详情，我推荐 Martin Fowler 的文章。

pg-index-health – a static analysis tool for you PostgreSQL database

微服务不仅改变了开发方法，还改变了测试金字塔的外观。这种转变很大程度上是由容器化技术（Docker、Testcontainers）的兴起推动的。如今，测试金字塔根本不再是金字塔。它可以有一个非常奇怪的形状。最著名的例子是蜂巢和测试奖杯。

pg-index-health – a static analysis tool for you PostgreSQL database

现代趋势是编写尽可能少的单元测试，重点关注实现细节，并优先考虑验证服务提供的实际功能的组件和集成测试。

我个人最喜欢的是测试奖杯。其基础是静态代码分析，旨在防止常见错误。

静态代码分析的重要性

Java 和 Kotlin 代码的静态分析现在是常见的做法。对于 Kotlin 服务，选择的工具通常是 detekt。对于 Java 应用程序，可用工具（通常称为 linter）的范围更广。主要工具包括Checkstyle、PMD、SpotBugs和Error Prone。您可以在我的上一篇文章中阅读有关它们的更多信息。

值得注意的是，detekt 和 Checkstyle 也可以处理代码格式化，有效地充当格式化程序。

数据库迁移的静态分析

现代微服务通常包括数据库迁移，用于创建和更新数据库结构以及应用程序代码。

在 Java 生态系统中，管理迁移的主要工具是 Liquibase 和 Flyway。对数据库结构的任何更改都必须始终记录在迁移中。即使在生产中发生事件期间手动进行更改，也必须稍后创建迁移以在所有环境中应用这些更改。

用纯 SQL 编写迁移是最佳实践，因为与学习 Liquibase 等工具的 XML 方言相比，它提供了最大的灵活性并节省时间。我在我的文章“在功能测试中使用 PostgreSQL 的六个技巧”中谈到了这一点。

验证SQL迁移代码

要验证迁移中的 SQL 代码，我建议使用 SQLFluff，它本质上是 SQL 的 Checkstyle 等效项。此 linter 支持多种数据库和方言（包括 PostgreSQL），并且可以集成到您的 CI 管道中。它提供了 60 多种可自定义规则，使您能够管理表和列别名、SQL 命令大小写、缩进、查询中的列排序等等。

比较带格式和不带格式的查询：

-- well-formatted SQL
select
    pc.oid::regclass::text as table_name,
    pg_table_size(pc.oid) as table_size
from
    pg_catalog.pg_class pc
    inner join pg_catalog.pg_namespace nsp on nsp.oid = pc.relnamespace
where
    pc.relkind = 'r' and
    pc.oid not in (
        select c.conrelid as table_oid
        from pg_catalog.pg_constraint c
        where c.contype = 'p'
    ) and
    nsp.nspname = :schema_name_param::text
order by table_name;

-- poorly formatted SQL
SELECT pc.oid::regclass::text AS table_name, pg_table_size(pc.oid) AS table_size
FROM pg_catalog.pg_class  pc
JOIN pg_catalog.pg_namespace AS nsp
ON nsp.oid =  pc.relnamespace
WHERE pc.relkind = 'r’
and pc.oid NOT in (
  select c.conrelid as table_oid
  from pg_catalog.pg_constraint   c
  where    c.contype = 'p’
)
and nsp.nspname  = :schema_name_param::text
ORDER BY  table_name;

格式良好的 SQL 代码更容易阅读和理解。最重要的是，代码审查将不再因有关格式首选项的讨论而陷入困境。 SQLFluff 强制执行一致的样式，节省时间。

SQLFluff 的实际应用

这就是真实拉取请求中的样子：

pg-index-health – a static analysis tool for you PostgreSQL database

这里SQLFluff发现select语句中返回值格式化有问题：当只返回一列时，我们不会将其放在单独的行。第二点是选择结果中的列顺序不正确：首先我们返回简单列，然后才返回计算结果。第三个是 join 语句中和的大小写不正确：我更喜欢用小写形式编写所有查询。

有关使用 SQLFluff 的更多示例，请查看我的开源项目：一、二。

使用元数据分析数据库结构

还可以检查数据库本身的结构。然而，处理迁移非常不方便：迁移数量可能很多；新的迁移可能会修复先前迁移中的错误，等等。通常，我们对数据库的最终结构比其中间状态更感兴趣。

利用信息模式

PostgreSQL（像许多其他关系数据库一样）存储有关所有对象及其之间关系的元数据，并以 information_schema 的形式向外部提供。我们可以使用对information_schema的查询来识别任何偏差、问题或常见错误（这正是SchemaCrawler所做的）。

由于我们仅使用 PostgreSQL，因此我们可以使用系统目录（pg_catalog 架构），而不是 information_schema，它提供有关特定数据库内部结构的更多信息。

累计统计系统

除了元数据之外，PostgreSQL还收集每个数据库的运行信息：执行了哪些查询、如何执行、使用了哪些访问方法等。累积统计系统负责收集这个数据。

通过系统视图查询这些统计数据并将其与系统目录中的数据相结合，我们可以：

识别未使用的索引；
检测缺乏足够索引的表。

统计数据可以手动重置。上次重置的日期和时间记录在系统中。考虑这一点对于了解统计数据是否可信非常重要。例如，如果您有一些业务逻辑每月/每季度/每半年执行一次，则需要收集至少上述间隔时间的统计信息。

如果使用数据库集群，则统计信息将在每个主机上独立收集，并且不会在集群内复制。

pg-index-health 及其结构

如上所述，基于数据库本身内的元数据分析数据库结构的想法已由我以名为 pg-index-health 的工具的形式实现。

我的解决方案包括以下组件：

一组 SQL 查询形式的检查，放置在单独的存储库中（当前包含 25 个检查）。这些查询与 Java 代码库解耦，可以在用其他编程语言编写的项目中重用。
领域模型 - 将检查结果表示为对象的最小类集。
HighAvailabilityPgConnection 抽象，用于连接到由多个主机组成的数据库集群。
用于执行 SQL 查询并将结果序列化为域模型对象的实用程序。
Spring Boot 启动器，用于方便快速地将检查集成到单元/组件/集成测试中。
迁移生成器，可以为已识别的问题创建纠正性 SQL 迁移。

支票类型

所有检查（也称为诊断）分为两组：

运行时检查（需要统计）。
静态检查（不需要统计）。

运行时检查

运行时检查仅在生产中的实时数据库实例上执行时才有意义。这些检查需要累积统计数据并聚合来自集群中所有主机的数据。

让我们考虑一个由三个主机组成的数据库集群：主主机、辅助主机和异步副本。某些服务使用具有类似拓扑的集群，并且仅在异步副本上执行大量读取查询以平衡负载。此类查询通常不会在主主机上执行，因为它们会产生额外的负载并对其他查询的延迟产生负面影响。

pg-index-health – a static analysis tool for you PostgreSQL database

如前所述，在 PostgreSQL 中，统计信息是在每个主机上单独收集的，并且不会在集群内复制。因此，您很容易遇到仅在异步副本上使用和需要某些索引的情况。为了可靠地确定是否需要索引，需要在集群中的每个主机上运行检查并聚合结果。

静态检查

静态检查不需要累积统计数据，可以在应用迁移后立即在主主机上执行。当然，它们也可以用于生产数据库来实时获取数据。然而，大多数检查都是静态的，它们在测试中特别有用，因为它们有助于捕获和防止开发阶段的常见错误。

pg-index-health – a static analysis tool for you PostgreSQL database

如何使用 pg-index-health

pg-index-health 的主要用例是添加测试来验证测试管道中的数据库结构。

对于 Spring Boot 应用程序，您需要将启动器添加到测试依赖项中：

-- well-formatted SQL
select
    pc.oid::regclass::text as table_name,
    pg_table_size(pc.oid) as table_size
from
    pg_catalog.pg_class pc
    inner join pg_catalog.pg_namespace nsp on nsp.oid = pc.relnamespace
where
    pc.relkind = 'r' and
    pc.oid not in (
        select c.conrelid as table_oid
        from pg_catalog.pg_constraint c
        where c.contype = 'p'
    ) and
    nsp.nspname = :schema_name_param::text
order by table_name;

然后添加标准测试：

-- poorly formatted SQL
SELECT pc.oid::regclass::text AS table_name, pg_table_size(pc.oid) AS table_size
FROM pg_catalog.pg_class  pc
JOIN pg_catalog.pg_namespace AS nsp
ON nsp.oid =  pc.relnamespace
WHERE pc.relkind = 'r’
and pc.oid NOT in (
  select c.conrelid as table_oid
  from pg_catalog.pg_constraint   c
  where    c.contype = 'p’
)
and nsp.nspname  = :schema_name_param::text
ORDER BY  table_name;

在此测试中，所有可用的检查都作为列表注入。然后，只有静态检查会在部署在应用了迁移的容器中的真实数据库上过滤和执行。

理想情况下，每次检查都应返回一个空列表。如果添加下一个迁移时有任何偏差，测试将会失败。开发人员将被迫关注这一点并以任何方式解决问题：要么在迁移中修复它，要么明确忽略它。

误报和添加排除

重要的是要了解 pg-index-health 与任何其他静态分析器一样，可能会产生误报。此外，某些检查可能与您的项目无关。例如，记录数据库结构被认为是很好的做法。 PostgreSQL 允许向几乎所有数据库对象添加注释。在迁移中，这可能如下所示：

dependencies {
    testImplementation("io.github.mfvanek:pg-index-health-test-starter:0.14.4")
}

在您的团队中，您可能同意不这样做。在这种情况下，相应检查的结果 (TABLES_WITHOUT_DESCRIPTION、COLUMNS_WITHOUT_DESCRIPTION、FUNCTIONS_WITHOUT_DESCRIPTION）对您来说变得无关紧要。

您可以完全排除这些检查：

import io.github.mfvanek.pg.core.checks.common.DatabaseCheckOnHost;
import io.github.mfvanek.pg.core.checks.common.Diagnostic;
import io.github.mfvanek.pg.model.dbobject.DbObject;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;

import java.util.List;

import static org.assertj.core.api.Assertions.assertThat;

@SpringBootTest
@ActiveProfiles("test")
class DatabaseStructureStaticAnalysisTest {

    @Autowired
    private List<databasecheckonhost extends dbobject>> checks;

    @Test
    void checksShouldWork() {
        assertThat(checks)
            .hasSameSizeAs(Diagnostic.values());

        checks.stream()
            .filter(DatabaseCheckOnHost::isStatic)
            .forEach(c -> assertThat(c.check())
                .as(c.getDiagnostic().name())
                .isEmpty());
    }
}
</databasecheckonhost>

或者干脆忽略他们的结果：

create table if not exists demo.warehouse
(
    id bigint primary key generated always as identity,
    name varchar(255) not null
);

comment on table demo.warehouse is 'Information about the warehouses';
comment on column demo.warehouse.id is 'Unique identifier of the warehouse';
comment on column demo.warehouse.name is 'Human readable name of the warehouse';

在引入pg-index-health时，你可能经常会遇到数据库结构已经存在一些偏差的情况，但你又不想立即解决它们。同时，该检查是相关的，禁用它不是一个选项。在这种情况下，最好修复代码中的所有偏差：

@Test
void checksShouldWork() {
    assertThat(checks)
        .hasSameSizeAs(Diagnostic.values());

    checks.stream()
        .filter(DatabaseCheckOnHost::isStatic)
        .filter(c -> c.getDiagnostic() != Diagnostic.TABLES_WITHOUT_DESCRIPTION &&
            c.getDiagnostic() != Diagnostic.COLUMNS_WITHOUT_DESCRIPTION)
        .forEach(c -> assertThat(c.check())
            .as(c.getDiagnostic().name())
            .isEmpty());
}

现在，我想更详细地关注最常遇到的问题。

没有主键的表

由于 PostgreSQL 中 MVCC 机制的特殊性，可能会发生诸如膨胀之类的情况，即表（或索引）的大小由于大量死元组而快速增长。例如，由于长时间运行的事务或一次性更新大量行，可能会发生这种情况。

数据库内的垃圾收集由autovacuum进程处理，但它不会释放占用的物理磁盘空间。有效减少表物理大小的唯一方法是使用 VACUUM FULL 命令，该命令在操作期间需要独占锁。对于大桌子，这可能需要几个小时，使得完全吸尘对于大多数现代服务来说是不切实际的。

为了在不停机的情况下解决表膨胀问题，经常使用像pg_repack这样的第三方扩展。 pg_repack 的强制要求之一是目标表上存在主键或其他一些唯一性约束。 TABLES_WITHOUT_PRIMARY_KEY 诊断有助于检测没有主键的表并防止将来出现维护问题。

下面是一个没有主键的表的示例。如果此表中出现 bloat，pg_repack 将无法处理它并返回错误。

-- well-formatted SQL
select
    pc.oid::regclass::text as table_name,
    pg_table_size(pc.oid) as table_size
from
    pg_catalog.pg_class pc
    inner join pg_catalog.pg_namespace nsp on nsp.oid = pc.relnamespace
where
    pc.relkind = 'r' and
    pc.oid not in (
        select c.conrelid as table_oid
        from pg_catalog.pg_constraint c
        where c.contype = 'p'
    ) and
    nsp.nspname = :schema_name_param::text
order by table_name;

重复索引

我们的数据库运行在资源有限的主机上，磁盘空间就是其中之一。使用数据库即服务解决方案时，最大数据库大小通常存在无法更改的物理限制。

表中的每个索引都是磁盘上的一个单独的实体。它占用空间并且需要资源进行维护，这会减慢数据插入和更新的速度。我们创建索引是为了加快搜索速度或确保某些值的唯一性。然而，索引使用不当可能会导致它们的总大小超过表本身有用数据的大小。因此，表中的索引数量应该尽可能少，但足以满足其功能。

我遇到过很多在迁移中创建不必要索引的情况。例如，主键的索引是自动创建的。虽然技术上可以手动索引 id 列，但这样做完全没有意义。

-- poorly formatted SQL
SELECT pc.oid::regclass::text AS table_name, pg_table_size(pc.oid) AS table_size
FROM pg_catalog.pg_class  pc
JOIN pg_catalog.pg_namespace AS nsp
ON nsp.oid =  pc.relnamespace
WHERE pc.relkind = 'r’
and pc.oid NOT in (
  select c.conrelid as table_oid
  from pg_catalog.pg_constraint   c
  where    c.contype = 'p’
)
and nsp.nspname  = :schema_name_param::text
ORDER BY  table_name;

独特约束也会出现类似的情况。当您使用 unique 关键字标记一列（或一组列）时，PostgreSQL 会自动为该列（或一组列）创建唯一索引。无需手动创建额外的索引。如果这样做，这会导致重复的索引。此类冗余索引可以而且应该被删除，DUPLICATED_INDEXES 诊断可以帮助识别它们。

-- well-formatted SQL
select
    pc.oid::regclass::text as table_name,
    pg_table_size(pc.oid) as table_size
from
    pg_catalog.pg_class pc
    inner join pg_catalog.pg_namespace nsp on nsp.oid = pc.relnamespace
where
    pc.relkind = 'r' and
    pc.oid not in (
        select c.conrelid as table_oid
        from pg_catalog.pg_constraint c
        where c.contype = 'p'
    ) and
    nsp.nspname = :schema_name_param::text
order by table_name;

重叠（相交）索引

大多数索引都是为单个列创建的。当查询优化开始时，可能会添加更复杂的索引，涉及多个列。这导致了为 A、A B 和 A B C 等列创建索引的情况。本系列中的前两个索引通常可以丢弃，因为它们是第三个索引的前缀（我建议观看此视频）。删除这些冗余索引可以节省大量磁盘空间，INTERSECTED_INDEXES 诊断旨在检测此类情况。

-- poorly formatted SQL
SELECT pc.oid::regclass::text AS table_name, pg_table_size(pc.oid) AS table_size
FROM pg_catalog.pg_class  pc
JOIN pg_catalog.pg_namespace AS nsp
ON nsp.oid =  pc.relnamespace
WHERE pc.relkind = 'r’
and pc.oid NOT in (
  select c.conrelid as table_oid
  from pg_catalog.pg_constraint   c
  where    c.contype = 'p’
)
and nsp.nspname  = :schema_name_param::text
ORDER BY  table_name;

没有索引的外键

PostgreSQL 允许创建外键约束而不指定支持索引，这意味着引用另一个表不需要也不会自动创建索引。在某些情况下，这可能不是问题，并且可能根本不会显现出来。然而，有时它可能会导致生产中发生事故。

让我们看一个小例子（我使用的是 PostgreSQL 16.6）：

dependencies {
    testImplementation("io.github.mfvanek:pg-index-health-test-starter:0.14.4")
}

我们有一个 orders 表和一个 order_item 表。它们通过 order_id 列上的外键链接。外键应始终引用主键或某些唯一约束，这在我们的示例中得到满足。

让我们用数据填充表格并收集统计数据。我们将添加 100,000 个订单，其中一半有两件商品，另一半有一件商品。

import io.github.mfvanek.pg.core.checks.common.DatabaseCheckOnHost;
import io.github.mfvanek.pg.core.checks.common.Diagnostic;
import io.github.mfvanek.pg.model.dbobject.DbObject;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;

import java.util.List;

import static org.assertj.core.api.Assertions.assertThat;

@SpringBootTest
@ActiveProfiles("test")
class DatabaseStructureStaticAnalysisTest {

    @Autowired
    private List<databasecheckonhost extends dbobject>> checks;

    @Test
    void checksShouldWork() {
        assertThat(checks)
            .hasSameSizeAs(Diagnostic.values());

        checks.stream()
            .filter(DatabaseCheckOnHost::isStatic)
            .forEach(c -> assertThat(c.check())
                .as(c.getDiagnostic().name())
                .isEmpty());
    }
}
</databasecheckonhost>

如果我们尝试检索 ID=100 的订单的商品，我们应该成功返回 2 行。由于订单表中的 id 列上有索引，因此该查询似乎应该很快。

create table if not exists demo.warehouse
(
    id bigint primary key generated always as identity,
    name varchar(255) not null
);

comment on table demo.warehouse is 'Information about the warehouses';
comment on column demo.warehouse.id is 'Unique identifier of the warehouse';
comment on column demo.warehouse.name is 'Human readable name of the warehouse';

但是，如果我们尝试分析此查询，我们将在执行计划中看到对表的顺序扫描。我们还应该关注需要读取的大量页面（Buffers 参数）。

@Test
void checksShouldWork() {
    assertThat(checks)
        .hasSameSizeAs(Diagnostic.values());

    checks.stream()
        .filter(DatabaseCheckOnHost::isStatic)
        .filter(c -> c.getDiagnostic() != Diagnostic.TABLES_WITHOUT_DESCRIPTION &&
            c.getDiagnostic() != Diagnostic.COLUMNS_WITHOUT_DESCRIPTION)
        .forEach(c -> assertThat(c.check())
            .as(c.getDiagnostic().name())
            .isEmpty());
}

@Test
void checksShouldWork() {
    assertThat(checks)
        .hasSameSizeAs(Diagnostic.values());

    checks.stream()
        .filter(DatabaseCheckOnHost::isStatic)
        .forEach(c -> {
            final ListAssert extends DbObject> listAssert = assertThat(c.check())
                .as(c.getDiagnostic().name());
            switch (c.getDiagnostic()) {
                case TABLES_WITHOUT_DESCRIPTION, COLUMNS_WITHOUT_DESCRIPTION -> listAssert.hasSizeGreaterThanOrEqualTo(0); // ignored

                default -> listAssert.isEmpty();
            }
        });
}

如果我们为带有外键的列创建索引，情况就会恢复正常：

@Test
void checksShouldWorkForAdditionalSchema() {
    final PgContext ctx = PgContext.of("additional_schema");
    checks.stream()
        .filter(DatabaseCheckOnHost::isStatic)
        .forEach(c -> {
            final ListAssert extends DbObject> listAssert = assertThat(c.check(ctx))
                .as(c.getDiagnostic().name());

            switch (c.getDiagnostic()) {
                case TABLES_WITHOUT_DESCRIPTION, TABLES_NOT_LINKED_TO_OTHERS ->
                    listAssert.hasSize(1)
                        .asInstanceOf(list(Table.class))
                        .containsExactly(
                            Table.of(ctx, "additional_table")
                        );

                default -> listAssert.isEmpty();
            }
        });
}

顺序扫描将从查询计划中消失，读取的页数将显着减少：

create table if not exists demo.payment
(
    id bigint not null, -- column is not marked as primary key
    order_id bigint references demo.orders (id),
    status int not null,
    created_at timestamp not null,
    payment_total decimal(22, 2) not null
);

FOREIGN_KEYS_WITHOUT_INDEX 诊断将使您能够在开发过程中及早发现此类情况，从而防止出现性能问题。

我是否应该创建索引？

记住误报问题很重要：并非所有外键列都需要索引。尝试估算生产中大概的工作台尺寸；检查您的代码以在外键列上进行过滤、搜索或连接。如果您 100% 确定不需要该索引，则只需将其添加到排除项即可。如果您不确定，最好创建索引（以后随时可以将其删除）。

我经常遇到由于外键上没有索引而导致数据库“变慢”的事件，但我还没有看到任何由于存在此类索引而导致数据库“变慢”的事件。因此，我不同意 Percona 博客文章中提出的观点，即从一开始就不应该创建外键索引。这是一种DBA方法。您的团队中有专门的DBA吗？

索引中的空值

默认情况下，PostgreSQL 在 btree 索引中包含空值，但通常不需要它们。所有空值都是唯一的，您不能简单地检索列值为空的记录。大多数时候，最好通过在 nullable 列上创建部分索引来从索引中排除空值，例如 where ;不为空。诊断INDEXES_WITH_NULL_VALUES有助于检测此类情况。

让我们考虑一个 orders 和 order_items 的示例。 order_item 表有一个 nullable 列 warehouse_id，代表仓库 ID。

-- well-formatted SQL
select
    pc.oid::regclass::text as table_name,
    pg_table_size(pc.oid) as table_size
from
    pg_catalog.pg_class pc
    inner join pg_catalog.pg_namespace nsp on nsp.oid = pc.relnamespace
where
    pc.relkind = 'r' and
    pc.oid not in (
        select c.conrelid as table_oid
        from pg_catalog.pg_constraint c
        where c.contype = 'p'
    ) and
    nsp.nspname = :schema_name_param::text
order by table_name;

假设我们有几个仓库。订单付款后，我们开始组装。我们将更新部分订单的状态并将其标记为已付款。

-- poorly formatted SQL
SELECT pc.oid::regclass::text AS table_name, pg_table_size(pc.oid) AS table_size
FROM pg_catalog.pg_class  pc
JOIN pg_catalog.pg_namespace AS nsp
ON nsp.oid =  pc.relnamespace
WHERE pc.relkind = 'r’
and pc.oid NOT in (
  select c.conrelid as table_oid
  from pg_catalog.pg_constraint   c
  where    c.contype = 'p’
)
and nsp.nspname  = :schema_name_param::text
ORDER BY  table_name;

订单中的单个商品可能会根据内部算法从不同仓库发货，考虑物流、库存、仓库负载等。分配仓库并更新库存后，我们更新warehouse_id 订单中每个商品的字段（最初为空）。

dependencies {
    testImplementation("io.github.mfvanek:pg-index-health-test-starter:0.14.4")
}

我们需要通过特定的仓库 ID 进行搜索，以了解哪些物品需要完成并发货。我们只接受特定时间范围内的付费订单。

import io.github.mfvanek.pg.core.checks.common.DatabaseCheckOnHost;
import io.github.mfvanek.pg.core.checks.common.Diagnostic;
import io.github.mfvanek.pg.model.dbobject.DbObject;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;

import java.util.List;

import static org.assertj.core.api.Assertions.assertThat;

@SpringBootTest
@ActiveProfiles("test")
class DatabaseStructureStaticAnalysisTest {

    @Autowired
    private List<databasecheckonhost extends dbobject>> checks;

    @Test
    void checksShouldWork() {
        assertThat(checks)
            .hasSameSizeAs(Diagnostic.values());

        checks.stream()
            .filter(DatabaseCheckOnHost::isStatic)
            .forEach(c -> assertThat(c.check())
                .as(c.getDiagnostic().name())
                .isEmpty());
    }
}
</databasecheckonhost>

第一个解决方案可能是 warehouse_id 列上的常规索引：

-- well-formatted SQL
select
    pc.oid::regclass::text as table_name,
    pg_table_size(pc.oid) as table_size
from
    pg_catalog.pg_class pc
    inner join pg_catalog.pg_namespace nsp on nsp.oid = pc.relnamespace
where
    pc.relkind = 'r' and
    pc.oid not in (
        select c.conrelid as table_oid
        from pg_catalog.pg_constraint c
        where c.contype = 'p'
    ) and
    nsp.nspname = :schema_name_param::text
order by table_name;

如果我们创建这样的索引，那么在搜索特定仓库的项目时将不会出现问题。看起来这个索引应该允许有效地查找尚未分配仓库的所有项目，过滤条件为warehouse_id为null的记录。

-- poorly formatted SQL
SELECT pc.oid::regclass::text AS table_name, pg_table_size(pc.oid) AS table_size
FROM pg_catalog.pg_class  pc
JOIN pg_catalog.pg_namespace AS nsp
ON nsp.oid =  pc.relnamespace
WHERE pc.relkind = 'r’
and pc.oid NOT in (
  select c.conrelid as table_oid
  from pg_catalog.pg_constraint   c
  where    c.contype = 'p’
)
and nsp.nspname  = :schema_name_param::text
ORDER BY  table_name;

但是，如果我们查看查询执行计划，我们将看到那里的顺序访问 - 未使用索引。

dependencies {
    testImplementation("io.github.mfvanek:pg-index-health-test-starter:0.14.4")
}

当然，这与测试数据库中数据的具体分布有关。 warehouse_id 列的基数较低，这意味着其中唯一值的数量较少。该列上的索引选择性较低。索引选择性是指不同索引值的数量（即基数）与表中总行数的比率distinct / count()。例如，唯一索引的选择性为一。

我们可以通过删除空值并在 warehouse_id 列上创建部分索引来提高索引的选择性。

import io.github.mfvanek.pg.core.checks.common.DatabaseCheckOnHost;
import io.github.mfvanek.pg.core.checks.common.Diagnostic;
import io.github.mfvanek.pg.model.dbobject.DbObject;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;

import java.util.List;

import static org.assertj.core.api.Assertions.assertThat;

@SpringBootTest
@ActiveProfiles("test")
class DatabaseStructureStaticAnalysisTest {

    @Autowired
    private List<databasecheckonhost extends dbobject>> checks;

    @Test
    void checksShouldWork() {
        assertThat(checks)
            .hasSameSizeAs(Diagnostic.values());

        checks.stream()
            .filter(DatabaseCheckOnHost::isStatic)
            .forEach(c -> assertThat(c.check())
                .as(c.getDiagnostic().name())
                .isEmpty());
    }
}
</databasecheckonhost>

我们将立即在查询计划中看到该索引：

create table if not exists demo.warehouse
(
    id bigint primary key generated always as identity,
    name varchar(255) not null
);

comment on table demo.warehouse is 'Information about the warehouses';
comment on column demo.warehouse.id is 'Unique identifier of the warehouse';
comment on column demo.warehouse.name is 'Human readable name of the warehouse';

如果我们比较索引的大小，我们会看到显着的差异。部分索引要小得多，更新频率也较低。使用此索引，我们可以节省磁盘空间并提高性能。

查询获取索引的大小

@Test
void checksShouldWork() {
    assertThat(checks)
        .hasSameSizeAs(Diagnostic.values());

    checks.stream()
        .filter(DatabaseCheckOnHost::isStatic)
        .filter(c -> c.getDiagnostic() != Diagnostic.TABLES_WITHOUT_DESCRIPTION &&
            c.getDiagnostic() != Diagnostic.COLUMNS_WITHOUT_DESCRIPTION)
        .forEach(c -> assertThat(c.check())
            .as(c.getDiagnostic().name())
            .isEmpty());
}