お問い合わせ
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 ネイティブ機能である監査を使用する

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

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. ダッシュボードを表示するユーザー数を減らす:

    • 通常、ダッシュボードはアナリストと管理職のみに必要であり、一般的なアプリケーションの読み込みから除外する

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

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

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

  3. トリガーを使用してデータを集計し、すぐに使用できる状態で格納する

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

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. 読み取り専用セレクトに書き込みトランザクションを使用する

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

なぜ問題なのか？

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

  • 読み取り専用操作に書き込みトランザクションを使用するのは非効率的です（コミット時の大きな 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 INDEX ixreverse1 ON TABLE1 COMPUTED BY (REVERSE(fieldName));
-- 両方向を使用したクエリ
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. 誤った整合性チェック：主キーの代わりにトリガー/CHECKを使用

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

なぜ問題なのか？

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

  • ユーザートランザクションでトリガーによる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 
-- ジェネレータ/シーケンスを使用！
CREATE GENERATOR gen_user_id;
-- ID生成にジェネレータを使用
INSERT INTO users (id, name)
VALUES (
 GEN_ID(gen_user_id, 1),
 'John Doe' );

13. 非効率的なGUID使用

なぜ問題なのか？

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

  • システム生成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
 // 黙って失敗
end;

なぜ問題なのか？

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

解決策：

delphi 
try
 FDQuery1.Open;
except
 on E: Exception do
 begin
 // 包括的なログ記録
 Logger.Error('データベース接続に失敗しました: ' + E.Message);
 ShowMessage('データベースに接続できません。サポートにお問い合わせください。');
 // 追加コンテキストのログ記録
 Logger.LogStackTrace(E);
 end;
end;

連絡先情報