pg-index-health – PostgreSQL 資料庫的靜態分析工具

你好！

自 2019 年以來，我一直在開發一個名為 pg-index-health 的開源工具，它可以分析資料庫結構並識別潛在問題。在我之前的一篇文章中，我分享了這個工具如何誕生的故事。

多年來，pg-index-health 不斷發展和改進。 2024 年，在幾位貢獻者的支持下，我成功解決了大部分剩餘的“成長的煩惱”，並使該專案達到了可以大規模擴展的狀態。

資料庫隨著微服務的興起而成長

自 2015 年以來，我一直在使用 PostgreSQL，這段迷人的旅程始於位於雅羅斯拉夫爾的 Tensor 公司。

早在2015年，那還是一個擁有大量資料庫和大量表格的單體時代。通常，對此類資料庫結構的任何變更都需要獲得作為關鍵知識持有者的架構師或開發主管的強制批准。雖然這可以防止大多數錯誤，但它減慢了更改的過程並且完全無法擴展。

漸漸地，人們開始轉向微服務。
資料庫的數量顯著增加，但每個資料庫中的表格數量卻相反減少。現在，每個團隊開始獨立管理自己的資料庫結構。集中的專業知識來源消失了，資料庫設計錯誤開始倍增並從一項服務傳播到另一項服務。

測試金字塔及其形狀

你們中的大多數人可能都聽說過測試金字塔。對於整體而言，它具有相當典型的形狀和廣泛的單元測試基礎。欲了解更多詳情，我推薦 Martin Fowler 的文章。

微服務不僅改變了開發方法，也改變了測試金字塔的外觀。這種轉變很大程度上是由容器化技術（Docker、Testcontainers）的興起所推動的。如今，測試金字塔根本不再是金字塔。它可以有一個非常奇怪的形狀。最著名的例子是蜂巢和測試獎盃。

現代趨勢是編寫盡可能少的單元測試，重點是實現細節，並優先考慮驗證服務提供的實際功能的組件和整合測試。

我個人最喜歡的是測試獎盃。其基礎是靜態程式碼分析，旨在防止常見錯誤。

靜態程式碼分析的重要性

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 的實際應用

這就是真實拉取請求中的樣子：

這裡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 遷移。

支票類型

所有檢查（也稱為診斷）分為兩組：

• 運行時檢查（需要統計）。
• 靜態檢查（不需要統計）。

運行時檢查

運行時檢查僅在生產中的即時資料庫執行個體上執行時才有意義。這些檢查需要累積統計資料並聚合來自叢集中所有主機的資料。

讓我們考慮一個由三個主機組成的資料庫叢集：主主機、輔助主機和非同步副本。某些服務使用具有類似拓撲的集群，並且僅在非同步副本上執行大量讀取查詢以平衡負載。此類查詢通常不會在主主機上執行，因為它們會產生額外的負載並對其他查詢的延遲產生負面影響。

如前所述，在 PostgreSQL 中，統計資料是在每個主機上單獨收集的，並且不會在叢集內複製。因此，您很容易遇到僅在非同步副本上使用和需要某些索引的情況。為了可靠地確定是否需要索引，需要在叢集中的每個主機上執行檢查並聚合結果。

靜態檢查

靜態檢查不需要累積統計數據，可以在應用遷移後立即在主主機上執行。當然，它們也可以用於生產資料庫來即時獲取數據。然而，大多數檢查都是靜態的，它們在測試中特別有用，因為它們有助於捕獲和防止開發階段的常見錯誤。

如何使用 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、

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());
}

table_name	index_name	index_size_bytes
demo.order_item	demo.idx_order_item_warehouse_id	1056768
demo.order_item	demo.idx_order_item_warehouse_id_without_nulls	16384

未來計劃

這些遠遠不是 pg-index-health 可以偵測到的所有問題。完整的診斷清單可在 GitHub 上的專案自述文件中找到，並且會定期擴展。

將 pg-index-health 整合到 Spring Boot 應用程式中非常簡單。運轉檢查的開銷很小。因此，您將免受常見錯誤和問題的影響。我鼓勵您嘗試實施它！

在不久的將來，我計劃在所有檢查中添加對分區表的全面支援。目前，僅對 25 項檢查中的 11 項實施了此措施。我還想擴大檢查數量：已經有實施至少 5 項新檢查的票證。此外，我計劃在 2025 年切換到 Java 17 和 Spring Boot 3。

儲存庫連結

• pg-index-health
• 用於檢查的原始 SQL 查詢
• 示範應用程式

附加材料

• 我的俄文原文
• 類似的解決方案 - SchemaCrawler
• DBA：尋找無用的索引（俄文）
• Java 開發人員眼中的 PostgreSQL 索引健康狀況（俄語）
• 資料庫結構的靜態分析（俄文）

以上是pg-index-health – PostgreSQL 資料庫的靜態分析工具的詳細內容。更多資訊請關注PHP中文網其他相關文章！