昨日からフルテキストインデックスについて勉強をしています。
その際に調べたことのメモを。
技術情報としては以下の内容が参考になります。
フルテキスト検索のアーキテクチャ
フルテキスト インデックスの作成
サーバー インスタンスでのフルテキスト検索の利用状況の監視
フルテキスト インデックスのパフォーマンスの向上
フルテキスト作成 (クロール) で発生したエラーのトラブルシューティング
フルテキスト検索とセマンティック検索の動的管理ビューおよび関数
トラブルシューティング : フィルター処理によるフルテキスト インデックス作成のパフォーマンスの低下
Best Practices for Integrated Full Text Search (iFTS) in SQL 2008
max full-text crawl range サーバー構成オプション
ALTER FULLTEXT INDEX (Transact-SQL)
ALTER FULLTEXT CATALOG (Transact-SQL)
CONTAINS (Transact-SQL)
sp_fulltext_service (Transact-SQL)
Contents
■フルテキストインデックスの検索
フルテキストインデックスは基本的にはワードブレイカーによって区切られた単語を検索することになると思います。
そのため、単純な以下のような検索だとヒットしないことがあります。 
単語として認識しそうな語句であればヒットします。 
最初に実行した条件のような検索をする場合にはワイルドーカードの指定が必要になってきます。 
フルテキストインデックスはクロールされ作成された内容をもとに作成されていますので、厳密性を求める検索の場合は LIKE を使った通常の検索方法を使用する必要があるかもしれないですね。
# 検索速度とのトレードオフですが。
■フルテキストインデックスの構成
フルテキストインデックスは
- フルテキストカタログ
- フルテキストインデックス
の 2 つの要素で構成されているかと思います。
フルテキストカタログはフルテキストインデックスを格納するために必要となります。
また、フルテキストカタログがフルテキストインデックスのメンテナンスをする単位 (再構築 / 再構成) となります。
そのため、大きなフルテキストインデックスは専用のカタログ、小さなフルテキストインデックスは一つのカタログにまとめるというようなことを考える必要が出てくるかと。
SQL Server 2005 まではフルテキストカタログの作成時にファイルグループを指定することができましたが SQL Server 2008 以降はファイルグループを指定することができなくなっている点もポイントかもしれないですね。
フルテキストインデックスが実際に検索時に使用されるインデックスになります。
フルテキストインデックスの作成はこの単位で実施することができます。
なお、一つのテーブルに作成できるフルテキストインデックスは一つとなるようです。
また、フルテキストインデックスは一つのフルテキストカタログにしか所属することができません。
インデックスの更新作業はフルテキストインデックス単位 (正確にはテーブル単位) でできますのでインデックスのメンテナンスとカタログのメンテナンスは分けて考える必要が出てきそうですね。
フルテキストインデックスは内部では以下の Internal Table で構成されているようです。
|
SELECT OBJECT_NAME(parent_object_id), * FROM sys.objects WHERE type=N’IT’ |
- sys.fulltext_index_docidmap_xxxxxxx
- sys.fulltext_index_docidstatus_xxxxxxx
- sys.fulltext_indexeddocid_xxxxxxx
- sys.fulltext_avdl_xxxxxxx
- sys.fulltext_docidfilter_xxxxxxx
- sys.ifts_comp_fragment_xxxxxxx_xx
- sys.syscompfragments
これらのテーブルは Internal Table (内部利用のみ) の扱いとなっており、通常は内容を見ることができません。
ただし、専用管理者接続 (DAC) を使用した場合はアクセスをすることができます。
SQL Server Management Studio で専用管理者接続を使用する方法
データベース管理者用の診断接続
この内容が見れるとフルテキストインデックスの理解が深まるかと。
フルテキストインデックスは、全文検索により作成したインデックスを外部ファイルとして持つのではなく内部テーブルとして格納しているということが見えてきますね。
フルテキストインデックスの作成/更新はテーブルに対しての操作となり、トランザクションログもそれなりに書き込まれますのでこの辺も意識しておいたほうがよさそうです。
なお、sys.ifts_comp_fragment に関してはフルテキストインデックスを設定しただけでは作成されず、実際にインデックスが作成されることでテーブルが出てきます。
# このテーブルが単語検索で使用されるコアなものになるようです。
■フルテキストインデックスの CPU 使用状況
フルテキストインデックスを作成する際には対象の列に対してアクセスを行いインデックスを作成する必要があります。
ワードブレイカーによる単語の分割等がありますので、fdhost.exe がCPU をそれなりに使用するのですがデフォルトの状態だと最大 4 スレッドで動作するようで、私の環境は 8 論理コア搭載されているのですが、CPU の使用率は 50% が上限 (4 コア分) となっていました。
デフォルトの設定だと max full-text crawl range サーバー構成オプション の制限により使用するスレッドが 4 スレッドとなるようです。そのため、CPU コアが多い環境ではこの数値を調整して、スレッドの並列度を上げることでインデックス作成の速度を上げることができます。
フルテキストインデックス系のオプションとしては ft で始まるものがいくつかあるのですが、SQL Server 2008 になり、フルテキストインデックスの機能がコアコンポーネントのほうに統合されたことにより今後削除される機能としてマークされているものがありますのでこの辺は注意したほうがよいかもしれないですね。
私の環境では タイミングによっては4 スレッドではなく、2 スレッドの動作となることがあり、この辺の仕組みがまだわかっていません。何回か実行していると max full-text crawl renge のスレッド数になったのですが。
この辺りはデータのパーティション数が少なかっただけかもしれないですが。
sp_fulltext_service の master_merge_dop も実行してみたりはしていたので、この辺も影響していたのかもしれないです。
# max full-text crawl renge と master_merge_dop は異なる設定のようですので、必要に応じてそれぞれを設定する必要があるかと。
一度以下の操作を実行してどれくらい CPU が使用されるかは稼働前に確認をしておいたほうがよさそうですね。
# UPDATE POPULATION や REORGANIZE は追加データやフラグメントテーブルを複数ある状態で試す必要があるかと。
- ALTER FULLTEXT INDEX ON <テーブル名> START FULL POPULATION
- ALTER FULLTEXT INDEX ON <テーブル名> START UPDATE POPULATION
- ALTER FULLTEXT INDEX ON <テーブル名> START INCREMENTAL POPULATION
- ALTER FULLTEXT CATALOG <カタログ名> REORGANIZE
- ALTER FULLTEXT CATALOG <カタログ名> REBUILD
CPU コア数と max full-text crawl range を合わせるとこのように CPU を全部使い切って全力で処理してくれます。 
fdhost.exe の CPU 使用率が高く、SQL Server の動作に影響が出ている場合などは [ALTER FULLTEXT INDEX ON <カタログ名> STOP POPULATION] でインデックスの作成を中止することができます。
# RESUME POPULATION で一時中断でもよいかもしれないですね。
■フルテキストインデックスの作成処理
フルテキストインデックスの作成処理としては
- フルテキストインデックスの作成
- マスター フルテキスト インデックスにマージ
の 2 種類があるかと思います。
フルテキストインデックスの作成は ALTER FULLTEXT INDEX を実行することでフルテキストインデックスの実体を作成します。
フルテキストインデックスは作成していると断片化 (フラグメンテーション) が発生していきます。
フルテキストインデックスで断片化が発生した場合、テーブル名の利用ページが多くなるというよりはインデックス フラグメントテーブルが増えていくというイメージのようです。
そのため、断片化が発生していくとフルテキストインデックスのテーブル (sys.ifts_comp_fragment) が増えてきます。
|
SELECT OBJECT_NAME(parent_object_id), * FROM sys.objects WHERE type=N’IT’ |
この断片化により作成されたインデックスフラグメントテーブルを最適化するための処理が ALTER FULLTEXT CATALOG による再構築(REBUILD)/再構成(REORGANIZE) となります。
最適化をすることでインデックスフラグメントテーブルをマスターにマージして一つのテーブルにします。
# sys.ifts_comp_fragment のテーブルが一つになります。 
再構築に関しては現在のデータを使用してフルテキストインデックスを再構築、再構成は断片化したフラグメントテーブルを結合するというイメージになるのでしょうか。
# 再構築をするとフルテキストインデックスが 0KB になりカタログが作成されていたので。
■フルテキストインデックス作成時のメモリ使用状況
フルテキストインデックス作成時のメモリ使用状況ですが、対象となる列を読み込むため、データのキャッシュが増加します。
■フルテキストインデックスの作成方法
フルテキストインデックスは最初の作成時には全件のデータから作成されますが、それ以降に関しては
- データ追加時に自動でインデックスを作成
- データ追加時にはインデックスは作成せず任意のタイミングで手動で作成
の 2 つの方法があります。
こ
れが、[変更の追跡] になります。 
[自動] を設定した場合は、データ追加時に自動的にインデックスに追加されていきます。
# リアルタイムではなく手動設定時に使用しているような変更追跡のテーブルにある程度データをキューイングして非同期で処理しているのかもしれませんが。(そこまで詳しく調べられていません…)
[手動] と [変更を追跡しない] を設定した場合は、データを追加してもその内容はインデックスには含まれません。
[手動] と [変更を追跡しない] を選択している場合は、テーブルの構造によって利用可能なインデックスの追加方法が変わってきます。
テーブルに [timestamp] のデータ型を持つ列がない場合は、[手動] を選択していないとインデックスの追加ができません。
[手動] を設定している場合、データに変更があると変更されたデータが追跡できるように変更対象のデータを識別できる情報が [sys.fulltext_index_docidstatus] に格納されていきます。 
インデックスの作成 (ALTER FULLTEXT INDEX ON <テーブル名> START UPDATE POPULATION) をした際にはこの、変更を追跡できる情報を使用して追加/変更があったデータをフルテキストインデックスに追加していくようです。
データ作成/変更時に変更の追跡をできるようにするためのオーバーヘッドは考慮する必要がありそうですね。
テーブルに [timestamp] のデータ型を持つ列が存在している場合には [増分更新] という方法を使用することができるようになります。
この [増分更新] (ALTER FULLTEXT INDEX ON <テーブル名> START INCREMENTAL POPULATION) は先ほどの、[sys.fulltext_index_docidstatus] を参照して、変更データを追跡するのとは全く異なる方法で変更データを判断します。
[timestamp] が設定されている場合、データに対して変更が行われると [timestamp] の値が更新されます。
増分更新に対応したテーブル ([timestamp] データ型の列を持つ) でフルテキストインデックスを作成した場合には、前回のインデックス作成時の [timestamp] の最大値が判断できるようにするための情報を [sys.syscompfragments] に保持しておきます。
# ts の値が [timestamp] の値になります。また、このテーブルの情報は各フルテキストインデックスがいくつフラグメントテーブルを持っているかを判断するためにも使用できそうです。 
[sys.fulltext_index_docidstatus] の画像は ts を降順で並べたものになります。
[sys.syscompfragments] の ts と近似値になっていることが確認できますね。
これらの値はフルテキストインデックス作成対象のテーブルの [timestamp] とリンクするようになっています。
前回、どの [timestamp] の値までインデックスを作成したかはこれらの情報を使用することでわかりますので、
- データの追跡を行うためのテーブルを確認し、その行がどの実データと対応するのかを取得しインデックスに追加
するのではなく
- 前回作成後に変更されたデータを [ts] の値から取得し、そのタイムスタンプを使用して実データを取得しインデックスを追加
というように変更された行単位で情報を取得するのではなく、特定の timestamp のデータを一括で取得して処理が行えるようになります。
そのため、増分更新をする場合には timestamp 列にインデックスを設定しておくことで効率を上げることができます。
timestamp を使用した増分更新の場合は、変更の追跡は timestamp で行えますので、変更の追跡は [手動] だけでなく、[変更を追跡しない] でもインデックスを更新できるようになります。
定期的にフルテキストインデックスを全件再作成ではなく、変更分のみを追加することで維持していく場合、
timestamp 列がない場合
- [手動] に設定し、[ALTER FULLTEXT INDEX ON <テーブル名> START UPDATE POPULATION] を実行
timestamp 列がある場合
- [手動] に設定し、[ALTER FULLTEXT INDEX ON <テーブル名> START UPDATE POPULATION] を実行
- [手動] に設定し、[ALTER FULLTEXT INDEX ON <テーブル名> START INCREMENTAL POPULATION] を実行
- [変更を追跡しない]に設定し、[ALTER FULLTEXT INDEX ON <テーブル名> START INCREMENTAL POPULATION] を実行
# 変更を追跡しないにした場合、REBUILD した際にカタログが 0KB になりアイドル状態となったので、この辺は要調査。
の方法があるかと思います。
増分更新で行う場合は、[手動] に設定することで変更追跡のためのオーバーヘッドがあるので [変更を追跡しない] に設定し、変更追跡のレコード追加のためのオーバーヘッドをなくしたほうがよさそうですね。
■インデックスのメンテナンス
フルテキストインデックスのメンテナンスは、
- 変更データのインデックスを作成
- インデックスの断片化を解消
の 順になるかと思います。
フルテキストカタログの [作成スケジュール] の [カタログ – 最適化] のジョブは、[ALTER FULLTEXT CATALOG <カタログ名> REORGANIZE] (断片化を解消) するためのジョブになるようですので、手動更新の場合は ALTER FULLTEXT INDEX を実行するためのジョブは別途設定する必要があるようですね。
ALTER FULLTEXT INDEX を実行しないと変更分に対してのインデックスが作成されず、ALTER FULLTEXT CATALOG REORGANIZE は現状のインデックスに対してのみの処理になるかと思いますので。
ざっくりとではありますが勉強
したことをまとめてみました。
DAC で接続することでいろいろと情報が見れますので、何となくではありますが動きがわかってきた気がします。