SQL Serverのフルテキスト クエリのパフォーマンスを向上させる

この記事では、SQL Serverでフルテキスト述語を使用するクエリのパフォーマンスを向上させる方法について説明します。

元の製品バージョン: SQL Server 2008 Developer、SQL Server 2008 Enterprise、SQL Server 2008 R2 Datacenter、SQL Server 2008 R2 Developer、SQL Server 2008 R2 Enterprise、SQL Server 2008 R2 Standard、SQL Server 2012 Developer、SQL Server 2012 Standard、SQL Server 2012 Web、SQL Server 2012 Enterprise
元の KB 番号: 2549443

概要

この記事では、フルテキスト検索述語 (や などCONTAINSCONTAINSTABLE) を使用し、データをフィルター処理する Microsoft SQL Server クエリのパフォーマンスを向上させる方法について説明します。 たとえば、このメソッドを使用すると、次のクエリのパフォーマンスが向上します。

SELECT * FROM dbo.ftTest WHERE CONTAINS(TextData, '"keyword"') AND CDate > @date

このメソッドを使用すると、フルテキスト検索エンジンが結果をリレーショナル エンジンに送信する前にフィルター処理するように、クエリ、テーブル スキーマ、フルテキスト インデックスを設計できます。 そのため、リレーショナル エンジンは大きなデータセットをフィルター処理する必要はありません。

詳細

フルテキスト検索クエリを作成する場合、クエリのパフォーマンスに影響を与える主な要因は、残りのデータがリレーショナル エンジンに送信される前にフルテキスト検索エンジンが処理する必要があるデータの量です。 SQL Serverでは、クエリのパフォーマンスを向上させるには、後で処理する必要がある行の数を減らすために、行を早期にフィルター処理します。

2008 年SQL Server前にリリースされたSQL Serverのバージョンでは、フルテキスト検索エンジンは検索用語に一致するすべての行を返し、リレーショナル エンジンはフィルターを適用します。 この動作の改善は、SQL Server 2008、SQL Server 2008 R2、および SQL Server 2012 で行われました。 ただし、フルテキスト検索インデックスはデータベース インデックスとは異なる方法で編成されているため、これらの機能強化を使用するのは困難です。 さらに、フルテキスト検索エンジンとリレーショナル エンジンの動作は異なります。 したがって、この記事で説明するメソッドでは、Table-Valued関数 (TVF) を使用して行を早期に除外し、後で処理する必要がある行の数を減らします。

たとえば、次のクエリ プランは、検索文字列131051一致する行を CONTAINS 返します。 さらに、プランの結合演算子は、インデックス シークを使用して追加のフィルター処理を実行します。

Rows StmtText
-------------------- ----------------------------------------------------------------------------------------
1167 SELECT CDate, ID FROM dbo.fttest WHERE contains (c2, '"create"') AND CDate> '08/05/2019'

1167 |--Merge Join(Left Semi Join, MERGE:([FTSdb].[dbo].[fttest].[ID])=(FulltextMatch.[docid]), RESIDUA
5858 |--Sort(ORDER BY:([FTSdb].[dbo].[fttest].[ID] ASC))
5858 | |--Clustered Index Seek(OBJECT:([FTSdb].[dbo].[fttest].[clidx1]), SEEK:([FTSdb].[
131051 |--Table-valued function

ただし、クエリにフルテキスト一意インデックス キー列が述語として含まれている場合、フルテキスト検索エンジンは述語を使用してフルテキスト レベルで結果をフィルター処理できます。 この状況では、追加のフィルター処理を適用する必要がある前に、TVF によって返されるデータ量が大幅に少なくなります。 たとえば、次のクエリでは、c2 条件に一致する必要がある 5 つの値を指定し、TVF は 5 つの値に一致する結果のみを返します。

Rows StmtText
-------------------------------------------------------------------------------------------------------------------------------------------
5 SELECT CDate, ID FROM dbo.fttest WHERE contains (c2, '"create"') AND CDate > '08/05/2019' AND ID IN ( 654051, 644051, 649106, 465, 105)

5 |--Nested Loops(Left Semi Join, OUTER REFERENCES:([FTSdb].[dbo].[fttest].[ID]))
5 |--Index Seek(OBJECT:([FTSdb].[dbo].[fttest].[idx1]), SEEK:([FTSdb].[dbo].[fttest].[ID]=(105) OR ...
5 |--Table-valued function

一意のインデックス キーで使用される値をプッシュダウンするフルテキスト検索エンジンの機能は、次のメソッドの基礎です。

述語にデータ型列が DateTime 含まれている場合は、一意のインデックス キー列に日付情報を含めて、この述語に一致する行のみが返されるようにすることができます。 これを行うには、キー列に日付情報を論理的に組み込む必要があります。 ただし、クエリを使用するキー列のデータ型とアプリケーションを変更する必要がある場合もあります。

メソッドを実装するには、フルテキスト一意キー ID のデータ型を に BIGINT変更します。 キー ID の最初の 4 バイトは日付列から年、月、日付の値をキャプチャし、最後の 4 バイトは同じままです。 たとえば、キー ID の最初のバイトは年を参照し、次のバイトは月を参照し、最後の 2 バイトは日付を参照できます。 アプリケーションは、このデータ型の変更に対応する必要があります。

次に、範囲述語をキー ID の述語に変換します。 たとえば、範囲述語を x < CDate < y 述語に (x*2^32 < ID < y*2^32) 変換できます。 変換された述語はフルテキスト キーの述語であるため、述語はフルテキスト ストリーミング Table-Valued Functions (STVF) にプッシュダウンされます。 この動作は、日付範囲内の検索を効果的に実行します。