IBAnalyst: ヒントとコツ

このテキストは元々2012年に書かれたもので、バージョン1.0〜2.5に有効です。バージョン3.0〜5.0では多くの変更があり、反映できませんでした。ドキュメントを読むか、サポートまでお問い合わせください: [email protected]。

IBAnalystの推奨事項やヘルプで回答されていないいくつかの質問：

1. PRIMARY、FOREIGN、UNIQUE制約のインデックスを再構築するには？

A: Firebirdバージョン1.0〜2.5の場合。 はい、制約インデックスに対してALTER INDEX xxx INACTIVE/ACTIVEを使用することはできません。この制約に深いまたは断片化したインデックスが見られる場合、特別なトリック（gbakがリストア時に使用するもの）を使用できます：

RDB$INDICESにはRDB$INDEX_INACTIVEフラグがあり、インデックスがアクティブな場合（CREATE INDEXまたはALTER INDEX ACTIVE後）はnullまたは0です。1はインデックスが非アクティブであることを意味します（ALTER INDEX INACTIVE後）。しかし、制約上の非アクティブなインデックスを示すために値3も使用されます。したがって、そのインデックスにRDB$INDEX_INACTIVE=3を設定し、COMMITし、その後値を0に戻してもう一度コミットすると、インデックスが再構築されます。

Firebird 3.0〜5.0の場合 - 単に ALTER INDEX indexname ACTIVE を実行してください。

2. IBAnalystの推奨事項をすべて使用したが、クエリの高速化に役立たない。

A: これは別の問題で、IBAnalystでは対応できません。問題の原因は2つ考えられます：

1. インデックスの統計情報が古い。SET STATISTICS INDEX xxxコマンドでインデックス統計を更新できます（詳細は http://www.ibase.ru/proc_selectivity/ を参照）。

2. クエリで使用されている条件に適切なインデックスが単に存在しない。

3. クエリが非常に複雑であるか、オプティマイザがクエリを最適化できないため、クエリのリファクタリングが必要。

4. 場合によっては、リストア直後に「断片化されたテーブル」が表示されます。

通常、FirebirdとInterBase（パラメータ-use_all_spaceなし）は、将来の挿入、更新、削除（レコードバージョンを配置するため）のためにデータページに約25%のスペースを予約します。しかし、どのデータベースページサイズ（1、2、4、8K）でも、レコードサイズが小さいテーブル（約12〜20バイト、例えば2つの整数フィールドを持つテーブルの平均レコードサイズは12バイト）では約50%の断片化が見られます。

これは正常です。これをサーバーの魔法の数値（または動作）と考えてください。

したがって、このような小さなレコードのテーブルがある場合、次のことができます：

a) そのテーブルの「断片化」警告を無視する

b) IBAnalystのオプションダイアログで「断片化%」を例えば45%に下げる。

4. 更新されるべきでないテーブルのレコードバージョン

更新されるべきでないテーブル（例えば、イベントログのあるテーブル）にレコードバージョンが見られる場合 - 心配しないでください。これらのバージョンは削除によって生成されます。

したがって、テーブルに現在いくつのレコードがあるか、そして何件のレコードが削除されたかがわかります。

これはMaxVer = 1の場合のみ当てはまります。1より大きい場合、このテーブルは何らかのアプリケーションによって更新されています。このテーブルが絶対に更新されるべきでないと確信している場合、「更新前」トリガーに例外を設定して、どのアプリケーションが更新を行っているかを見つけることをお勧めします。

5. Blobはテーブルの断片化を引き起こす可能性があります。

エンジンはblobを3つの異なる方法で保存します：

1. blobの内容がデータページに収まる場合（十分な空きスペースがある）、そのレコード（またはバージョン）の近くのデータページに保存されます。

2. blobの内容がデータページに収まらない場合、別のページに保存されます。

3. ケース2でblobが1つのデータページに収まらない場合、適切なblobページを指すポインタページが作成されます。

ケース1は、保存されるblobサイズとデータベースページサイズによって異なります。例えば、ページサイズが4Kで平均サイズ約5Kのblobがある場合、それらはデータページではなく追加のblobページに保存されます。

しかし、データベースをバックアップして8Kのページサイズでリストアすると、blobはデータページに収まり、レコードと一緒に保存され、高いレコード断片化を引き起こします。

IBAnalystはこれらのテーブルをPale（レコード列）としてマークし、ヒントにはそのテーブルの推定レコード数（データページ数に基づく）と実際の平均充填値（%）が表示されます。

クエリがそのテーブルからblob以外のフィールドを読み取る場合、自然スキャン、結合、または集計が非常に遅くなります。

これを回避する唯一の解決策：追加のテーブル（元のテーブルに1対1でリンク）を作成し、平均サイズがページサイズより小さいすべてのblob列をそこに移動します。

その場合、より大きなページサイズでバックアップ/リストアを試みないでください！これにより、現在のページサイズでデータページに収まらなかったblobが、より大きなページサイズでのリストア中にデータページに配置されます。したがって、blobを持つテーブルは以前よりも断片化されます。

また、より小さなページサイズでのリストアも推奨されません。インデックスや非blobテーブルのパフォーマンスを低下させる可能性があるためです。

また、blobフィールドをvarcharフィールドに変更しようとしないでください。varcharフィールドは常にレコードの一部として保存されるため、データページに収まらない場合、レコードは2つ以上のフラグメント（2つ以上のデータページに配置）を持つことができます。

追記：IBAnalystはこれらのテーブルを「誤って」報告することがあります。例えば、テーブルにblobフィールドとデータがあったが、テーブル構造から削除された場合などです。残念ながら、この警告には設定可能なオプションはありません。サーバー（統計）から報告されるデータから正確に計算しているためです。

6. VerLenとRecLengthの関係

a) VerLen >= RecLengthの90%：Version列に表示されるバージョンは、ほとんどがレコード削除です。削除されたレコードが多いほど、RecLengthは小さくなります（最大0バイトまで）。また、元のレコードに保存されていたものより大きな文字列データでテーブルを更新した場合、VerLenがRecLenより大きくなることがあります。

b) VerLen <= RecLengthの80%：バージョンはほとんどがレコード更新です。

統計ではテーブル全体の平均レコードサイズとバージョンサイズが表示されるため、これらのケースをより正確に区別することはできません。同時実行トランザクションで表示されるバージョン数は異なる場合があります。

7. IBAnalystが一部のインデックスを「不良」と名付けるのはなぜですか？

選択性の値が0.01未満のインデックスは、IBAnalystで「不良」とマークされます（インデックスビューのヘルプを参照）。特定のインデックスを不良と名付ける理由はいくつかあります：

1. そのインデックスの選択性が0.01未満。理論的にはオプティマイザはそのインデックスを使用すべきではありませんが、他にインデックスが存在しない場合（where、order by、または結合句に対して少なくとも）使用します。

2. そのようなインデックスは非常に遅いガベージコレクションを引き起こします。この問題はInterBase 7.1/7.5には存在せず、Firebird 2.0で修正される予定です。

3. このインデックスはリストアプロセスを非常に遅くし、作成（create/alter index active）も非常に遅くなります。これは、1つのインデックスキーに対するレコード番号チェーンが大きいためです。

4. このインデックスがwhere句で使用される場合、メモリ使用量は検索される値（ビットマスクサイズ）に依存します。レコードチェーンが大きい（キーの重複が多い）ため、メモリ消費も大きくなります。

5. そのインデックスが「order by」で使用され、多くの重複が主に低いキー値にある場合（インデックスのソート順による）、多くのインデックスページ読み取りが発生し、クエリが遅くなります。

これが、IBAnalystがそのようなインデックスの存在を無視できない理由です。

インデックスの最悪のケースは、Uniques列=1の場合、つまりインデックス列のすべての値が同じ場合です。これらのインデックスはサマリーページの「役に立たないインデックス」にリストされます。

もちろん、アプリケーションによってはそのようなインデックスが「良い」場合もあります。例えば、レコードに「アーカイブ」フラグがあり、アプリケーションがその列のインデックスをアーカイブされていない現在のデータのみの検索に使用する場合などです。したがって、そのインデックスを「不良」と名付けることが正しいかどうかは、あなた次第です。

8. 「不良」インデックスが外部キー制約によって作成された場合はどうすればよいですか？

さて、前の段落では「不良」インデックスを削除する方が良いことを示しています（他のキーより重複が少ないキーの検索に使用しない場合）。しかし、そのようなインデックスが外部キーによって作成された場合、外部キーを削除することによってのみインデックスを削除できます。外部キーを削除するとリレーションのチェック制約が無効になり、受け入れられない場合があります。

FKをトリガーに置き換えることはできますが、いくつかの制限があります。FKはインデックスを使用してレコードの関係を制御し、インデックスはトランザクションの状態に関係なくすべてのレコードのすべてのキーを「見る」ことができます。しかし、トリガーはクライアントのトランザクションコンテキストでのみ機能します。したがって、FKをトリガーに置き換える場合、次のことを確認する必要があります：

• マスターテーブルからレコードが削除されないこと、または「スナップショットテーブル予約」モードで削除されること。

• 主表中的主键列永远不会被修改。您可以通过BEFORE UPDATE触发器来限制这一点。

如果您能维持这些条件，就可以删除特定的外键。当然，不要在该列上手动创建索引。

9. 为什么在数据版本百分比行中只有12兆字节的数据，但我的数据库有140兆字节？

1. IBAnalyst在这里显示的是“纯”数据量，不包括其他数据库结构（索引、元数据等）以及页面碎片。

2. 在恢复后，InterBase和Firebird会在数据页中保留一些空闲空间（15-25%），以加快未来的更新和删除操作。

3. 存在特定的服务器行为，当表记录大小较小（约11-22字节）时，它会使数据页碎片化约50%。

10. 在频繁更新的情况下如何提高优化器性能

索引统计信息存储在RDB$INDICES.RDB$STATISTICS列中，并通过3种方式更新：

1. SET STATISTICS INDEX

2. ALTER INDEX ACTIVE，或CREATE INDEX …

3. 恢复过程（所有索引都会被重建，同时执行“ALTER INDEX ACTIVE”）

优化器使用这些统计信息来准备查询。利用统计值，优化器可以判断索引对于检索记录是“足够好”还是“没有用”。

如果统计信息长时间未更新，优化器可能会生成糟糕的执行计划，因为现有的统计值与实际状况不符，因为表数据可能发生了显著变化（例如，记录数量增加了5-10倍，或者相反，所有记录都被删除了）。

您可以用显式的PLAN替换特定查询的糟糕自动查询计划，但这不是一个好方法，因为在计划制定后数据可能会发生显著变化。

另一种（也是正确的）方法是定期对所有索引应用SET STATISTICS语句来刷新统计信息。您可以安排运行SQL脚本来刷新统计信息，使用ISQL或现成的工具gidx（仅限Windows）。

如果您有一些定期重新加载不同记录的表，这种方法将无济于事。让我们考虑一个例子：

• 表A每天被加载数据4-5次。
• 处理完加载的数据后，表A中的所有记录都会被删除。

在这种情况下，我们可以看到表A上索引的两个正确统计值–当它加载数据时，以及当它为空时。因此，在已加载的表上重新计算的统计信息在表为空时将无用，反之亦然。

为避免这种情况，您只需要在表A填充数据时重新计算其索引统计信息。最好是在对该表运行查询之前进行。

自1.91版本起，IBAnalyst会显示索引统计差异，并允许您随时重新计算。首先，您需要查看表记录信息–这是否是通常的平均记录数。如果是，您可以放心地重新计算索引选择性。如果不是–也许最好不要动索引统计信息，因为它可能导致优化器生成更差的查询计划。