お問い合わせ
IBSurgeon
このページは機械翻訳されています。英語の原文をお読みください。 English

IBSurgeon ライブラリ

15のFirebirdアンチパターン

Alexey Kovyazin著、2025年1月14日

はじめに

このドキュメントでは、Firebird データベースを扱う際によくある15のアンチパターンを概説し、それぞれの解決策を提供します。

1. MON$ への複数の並列クエリ

アンチパターン: 非常によくある間違い - OnConnect トリガーで、監査目的でユーザーの詳細を取得するために MON$ATTACHMENTS へクエリを実行したり、ライセンス目的で接続数を計算したりすることです。

なぜ悪いのか？

  • MON$ テーブルは fbNN_mon_xx システムファイルに格納される仮想テーブルで、パフォーマンス統計などが含まれています

  • ファイルが1Gbを超えるということは、使いすぎているということです

  • これらはシステム管理者の使用のみを目的として設計されています - つまり、管理者専用の1～2の並列クエリです

  • 200以上の接続で MON$ への並列クエリを実行すると Firebird は非常に大きく遅くなり、500以上の同時クエリでは高い確率で Firebird が「ハング」します

解決策:

  • 非管理的なタスク、つまりカウントや監査に MON$ を使用しないでください。OnConnect での使用は避けてください

  • 監査目的の場合:

  • CURRENT_USER、CURRENT_TIMESTAMP などのコンテキスト変数を使用する

  • トリガーよりもはるかに強力な Firebird ネイティブ機能である Audit を使用する

  • ライセンス目的の場合 - ユーザーのコンテキスト変数を使用する

2. 遅いダッシュボードの読み込み

アンチパターン: アプリケーション起動時に、先月または過去1年間のすべての注文や請求書を合計する包括的なダッシュボードやスコアボードを読み込んだり、毎分またはそれ以上の頻度で一部のメトリクスを更新したりすることです。

sql 
SELECT
 SUM(total_sales) as yearly_sales,
 COUNT(DISTINCT customers) as customer_count,
 AVG(order_value) as avg_order_value
FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-01-01';

なぜ悪いのか？

  • ユーザーは実際の作業を始める前に、会社全体の統計を見るために数秒待たなければなりません

  • Firebird の観点から見ると - 多数の並列クエリを常に実行し、大量のデータを取得して並べ替え/グループ化するために、Firebird は複数の CPU コアを集中的に使用し、ディスク、キャッシュ、並べ替え専用のメモリから読み取りを行います（場合によっては並べ替えがディスクに書き込まれます）

  • これは1分間に何度もレポートを作成するようなものです！

解決策:

  1. ダッシュボードを表示するユーザー数を減らす:

  • 通常、ダッシュボードはアナリストと経営陣にのみ必要なので、一般的なアプリケーションの負荷から除外する

  • 起動時/一部のフォームでのダッシュボードの読み込みをオプションにし、デフォルトで無効にする

  • ダッシュボードのデータは起動時ではなく、明示的なボタンクリックで読み込む（つまり、レポートとして作成する）

  1. スケジュール（つまりロボット）によって1つのプロセスでダッシュボードのデータを計算し、シンプルなクエリで取得できるようにシンプルなテーブルに保存する

  2. トリガーを使用してデータを集計し、すぐに使用できる状態で保存する

  3. レプリカデータベースを使用してダッシュボードのデータ（およびすべての重いレポートも）を計算する

3. 不要なレコードの読み込み

**アプリケーションやフォームを開くときに、数十万件のレコードが含まれているかどうかに関係なく、フィルタリングせずにすべてのデータをグリッドに読み込むことです。

delphi 
procedure TDataForm.LoadAllRecords;
begin
 FDQuery1.SQL.Text := 'SELECT * FROM large_table';
 FDQuery1.Open;
 // Loads entire table into memory
 DBGrid1.DataSource.DataSet := FDQuery1;
end;

なぜ悪いのか？

  • グリッドには50件のレコードしか表示されないにもかかわらず、ユーザーは検索機能を使う代わりに何千件ものレコードをスクロールしなければなりません

  • 99%の場合、ユーザーが必要とするのはごく狭い範囲のデータです。たとえば、最新の売上レコードなどです

  • Firebird の観点から見ると:

  • 開くたびに、何千件ものレコードの読み取り、キャッシュへの保存、ネットワーク経由の転送が必要になります

  • （Delphi で）データセットを開いたままにすると、Firebird はデータセットを閉じるまで、バッファや並べ替えられたレコードを一時領域に保持します（ORDER BY、GROUP BY などの場合）

解決策:

  1. FIRST/SKIP/ROWS でレコード数を制限する

  2. 何らかの条件でレコード数を制限する。たとえば、最近3日間に作成/変更されたレコードのみを表示する

  3. 一般に、クエリはできるだけ早く閉じる

4. スクロール時の過剰なクエリ

アンチパターン: スクロールイベントでクエリを実行することです。たとえば、グリッドやテーブルにデータを表示する際に、レコードごとに個別のクエリを実行したり、2つのグリッドでのマスター・ディテールのスクロールの古典的な例を遅延なしで使用したりすることです。

delphi 
procedure TForm1.GridScrolled(Sender: TObject);
begin
 // query for each row
 FDQuery2.SQL.Text :=
 'SELECT additional_info FROM details ' +
 'WHERE id = ' + IntToStr(CurrentRowId);
 FDQuery2.Open;
end;

なぜ悪いのか？

  • 動的グリッドでレコードごとに個別のクエリを実行すると、Firebird は何千件もの小さなクエリを処理することを強いられ、不必要に CPU リソースを消費します

  • Firebird の観点から見ると:

  • 多数の（1秒間に何千件もの）小さなクエリは、統計上クエリが0msと表示されても、準備、実行、結果の転送などが必要なため、大きな CPU 負荷を生み出します

解決策:

  1. バッチ操作を使用して複数の行を一度に読み込む

  2. グリッドのメインクエリを拡張して、詳細クエリをその一部として実行する

  3. グリッドの表示部分の詳細を読み込むための明示的なボタンを追加する

  4. 詳細を取得するクエリの実行に遅延を追加し、スクロール中の即時クエリを防ぐ

  5. すべてのユーザーに対してスクロール時の詳細読み込みをデフォルトで有効にしない

5. 不要な自動更新

アンチパターン: すべてのクライアントアプリケーションで最小間隔でグリッドデータを自動的に更新し、この機能をデフォルトで有効にすることです。

なぜ悪いのか？

  • その結果、何百ものクライアント接続がほぼ同一のクエリを実行して同じレコードを取得します

  • どこで起こるか: スケジュールの自動更新、キューの位置の選択、「最も近い空き枠」の検索など

  • Firebird の観点から見ると:

  • ダッシュボードの読み込みとスクロールイベントの組み合わせ: 多くの中規模クエリがシステムに負荷をかけます

解決策:

  1. 間隔を長くする！

  2. 明示的な（ユーザーがトリガーする）更新を実装する

  3. 実際のデータ変更に基づいたデータセットの選択的な更新を使用する（ストリーミング、トリガー、またはイベント+ストリーミング）

6. 頻繁なレコード更新

アンチパターン: 異なるトランザクションで同じレコードを頻繁に更新し、多数のレコードバージョンを作成することです。

なぜ悪いのか？

  • 数十のバージョンを持つレコードはパフォーマンスを大幅に低下させる可能性があり、数千のバージョンを持つレコードはブロッカーになる可能性があります

  • Firebird の観点から見ると: 特定のトランザクションの適切なバージョンを特定するためにレコードバージョンのチェーンを再構築する必要があり、多数の読み取り操作が必要になります。その結果、ガベージコレクションが大幅に遅くなります。

解決策:

  1. Firebird 4+ に移行する。中間ガベージコレクションがあります

  2. 長時間実行される書き込み可能なトランザクションを保持せず、適切なガベージコレクションを行う

  3. Firebird <4 の場合、UPDATE の代わりに DELETE+INSERT の使用を検討する

7. 読み取り専用の SELECT に書き込みトランザクションを使用する

アンチパターン: 読み取り専用の SELECT に書き込みトランザクションを使用すると、過剰な操作につながります。

なぜ悪いのか？

  • 読み取り専用の SELECT に書き込みトランザクションを使用すると、ヘッダーページの不要な書き込みが多数発生します

  • 読み取り専用の操作に書き込みトランザクションを使用するのは非効率です（コミット時の大きな TIP がサーバーに追加の負荷をかけます）

解決策:

  • データを変更しない操作には、別の読み取り専用トランザクションを使用する

  • Firebird は、単一の接続の枠内で複数のトランザクションを開くことを許可する数少ないデータベースの1つです

  • グローバル一時テーブルは読み取り専用トランザクションで使用できます

8. LIKE :param の使用

以下のパラメータ付きクエリは、fieldName のインデックスを使用しません（インデックスが存在する場合でも）:

sql 
SELECT * FROM Table1 WHERE fieldName LIKE :param1

なぜ悪いのか？

LIKE は任意の数の記号を置き換えることができるワイルドカード検索（%）を許可するため、Firebird はパラメータ値が事前にインデックス検索に適しているかどうかを判断できません。

通常、開発者はクエリテキストにパラメータ値を埋め込むことでこれを回避しようとします:

  • fieldName LIKE «Alex%» - インデックスを使用可能

  • fieldName LIKE «%Alex» - 標準インデックスを使用することはできません

  • fieldName LIKE «%Alex%» - インデックスをまったく使用できません

これは他の問題につながります（下記の#10を参照）。

解決策:

1. 既知の文字列プレフィックスには STARTING WITH を使用する

検索値がワイルドカード % で始まらない場合は、LIKE ではなく STARTING WITH を優先してください:

sql 
WHERE fieldName STARTING WITH ?param1

2. 双方向の文字列検索を最適化する

既知のプレフィックスまたはサフィックスのパターンを持つ文字列には、逆順インデックスを使用してください:

sql 
-- Create reversed index
CREATE INDEX ixreverse1 ON TABLE1 COMPUTED BY (REVERSE(fieldName));
-- Query using both directions
WHERE fieldName STARTING WITH :param1
   OR reverse(fieldName) STARTING WITH reverse(:param2)

3. 段階的検索戦略を実装する

文字列が先頭/末尾/中間に現れる場合（ただし同時ではない場合）:

  • まず STARTING WITH を使った高速なインデックス検索を試す

  • 結果が見つからない場合は、より低速な LIKE 検索にフォールバックする

4. 単語ベースの検索最適化

完全な単語（スペース、カンマなどで区切られた）を検索する場合:

  • 単語IDマッピング用の別テーブルを作成する

  • 元のテキストではなく、マッピングテーブルを通じて検索する

5. 包括的な全文検索機能については:

  • IBSurgeon Full Text Search UDR の使用を検討してください

  • このオープンソースソリューションは高度なテキスト検索機能を提供します

9. 読み取り専用操作でトランザクションをクローズしない

なぜ悪いのか？

  • トランザクションを長時間開いたままにすると、Firebird がスナップショットトランザクションのために多数のバックバージョンを維持することを強いられる可能性があります

解決策:

  • 可能な場合は読み取り専用トランザクションを使用し、書き込み可能なトランザクションはできるだけ早くクローズする

  • レコードバージョンチェーンの影響を減らすために、モダンな Firebird バージョン（4+）を使用する

  • 適切なスイープを実装する

10. クエリのパラメータ化の問題

アンチパターン: プリペアドクエリとパラメータ化を避け、代わりにパラメータ値をクエリテキストに直接埋め込む。

delphi 
FDQuery1.SQL.Text :=
 'SELECT * FROM users WHERE name = ''' +
 EditUsername.Text + '''';
FDQuery1.Open;

なぜ悪いのか？

  • この慣行は、繰り返し実行されるクエリのパフォーマンスを低下させます

  • パラメータ値を埋め込んだすべてのクエリは、新しいものとして準備する必要があります

  • 大きなテーブルでは、準備に長い時間がかかる場合があります

  • 問題分析を複雑にします

  • テキストでクエリをグループ化することが困難になります

  • SQLインジェクションの脆弱性を生み出します

解決策:

delphi 
FDQuery1.SQL.Text :=
 'SELECT * FROM users WHERE name = :username';
FDQuery1.ParamByName('username').AsString :=
 EditUsername.Text;
FDQuery1.Open;

11. 誤った整合性チェック: Primary Key の代わりにトリガー/CHECK を使用する

アンチパターン: データベースの整合性チェックに Primary Key の代わりにトリガーや CHECK を使用する。

なぜ悪いのか？

  • これは、Primary Key の検証が、ユーザーのトランザクション分離レベルに関係なく、レコードの現在のバージョンを読み取る特別なモードを使用することを無視しています。

  • ユーザートランザクション内でトリガーによる PK チェックを行うと、重複の可能性が高まり、ロジックが不必要に複雑になります

解決策:

  • 主キーを使用する

  • 冗長な整合性チェックを避ける

  • データベースロジックをシンプルに保つ

12. MAX() による ID 生成

アンチパターン: 新しい識別子に MAX(id)+1 を使用するのは信頼性が低く、非効率的です。

sql 
INSERT INTO users (id, name)
VALUES ((SELECT MAX(id) + 1 FROM users),
 'John Doe');

なぜ悪いのか？

  • 新しい識別子にシーケンス（ジェネレータ）の代わりに MAX(id)+1 を使用している

  • MAX(id)+1 は一般的なトランザクションパラメータでは一意性を保証しません - 2つの並行トランザクションが同じ MAX() 値を受け取る可能性があります

  • Max()+1 と CHECK(select if unique) の組み合わせも機能しません！

解決策:

sql 
-- Use generator/sequence!
CREATE GENERATOR gen_user_id;
-- Use generator for ID generation
INSERT INTO users (id, name)
VALUES (
 GEN_ID(gen_user_id, 1),
 'John Doe' );

13. 非効率な GUID の使用

なぜ悪いのか？

  • gen_uuid() の代わりにシステム生成の GUID を使用すると、インデックスのパフォーマンスに影響を与える可能性があります

  • システム生成の GUID は高度にランダム化されています

解決策:

  • gen_uuid() 関数を使用する

  • 代わりに BIGINT の使用を検討する

  • バージョン6では UUID v7 が導入される予定です

14. 非効率な計算フィールド

アンチパターン: 他のテーブルへの SELECT を含む計算フィールドを使用すると、単純な SELECT 操作のパフォーマンスが大幅に低下します。

sql 
CREATE TABLE orders (
 id INTEGER,
 total_amount COMPUTED BY (
 (SELECT SUM(item_price) FROM order_items
 WHERE order_items.order_id = orders.id)));

なぜ悪いのか？

  • 計算フィールドはオンザフライで計算され、複雑なロジックを実装するためのものではなく、最適化の取り組みを大幅に複雑にする可能性があります

  • テーブル間の関係を強化します

  • 計算フィールドは、連結のようなテーブルのフィールドを使った軽量な計算にのみ使用するのが理にかなっています

解決策:

sql 
CREATE TABLE orders (
 id INTEGER PRIMARY KEY,
 cached_total_amount DECIMAL(10,2));

CREATE TRIGGER update_order_total
BEFORE INSERT OR UPDATE ON orders
AS
BEGIN
 NEW.cached_total_amount = (
 SELECT SUM(item_price)
 FROM order_items
 WHERE order_items.order_id = NEW.id
 );
END;

15. ログを記録しないエラーの抑制

アンチパターン: Firebird のエラーや警告をログに記録せずに抑制しないでください！

delphi 
try
 FDQuery1.Open;
except
 // Silent failure
end;

なぜ悪いのか？

  • エラーを隠すと、適切な診断とデバッグが妨げられます。適切なエラーログは、問題を迅速に理解し解決するために不可欠です。

解決策:

delphi 
try
 FDQuery1.Open;
except
 on E: Exception do
 begin
 // Comprehensive logging
 Logger.Error('Database connection failed: ' + E.Message);
 ShowMessage('Unable to connect to database. Please contact support.');
 // Log additional context
 Logger.LogStackTrace(E);
 end;
end;

お問い合わせ