【SQL Server】フルテキスト検索について

SQL Serverのフルテキスト検索機能について説明します。フルテキスト検索の概要、利用方法、LIKE検索との比較などについて、具体例を交えながら解説します。

今回はSQL Serverの機能に関する記事です。
"フルテキスト検索"というのをご存じでしょうか？
簡単にいうと、あらかじめ用意しておいたキーなどの索引情報を使用してデータを特定する方式です。
例えば、膨大なデータを取り扱う場合にこの方式を利用することで高速に検索できると考えられています。

SQL Serverならびに各種DBMS（Oracle、MySQLなど）にはそれぞれ、"フルテキスト検索"機能が用意されています。

フルテキスト検索とは？

概要は、下記をご覧ください。

冒頭でも少し触れましたが、フルテキスト検索は"ワードブレーカー"と呼ばれる、索引辞書のようなものがあり、その辞書によって区切られた語句（ワード）を検索することになります。

フルテキスト検索対象となり得るクエリ

1つ以上の語または句（単純語句）
指定したテキストで始まる語または句（プレフィックス語句）
特定の語の変化形（生成語）
他の語または句に近接する語または句（近接語句）
特定の語のシノニム形（類義語）
重み付け値を使用している語または句（重み付け語句）

例えば、下記のようなデータに対して下記のようなクエリを実行すると、

実行クエリ

SELECT [word] FROM dbo.fulltext_word WHERE CONTAINS([word],'"スカイテックブログ"'); -- ①CONTAINS検索（特定の単語やフレーズの正確な検索）
SELECT [word] FROM dbo.fulltext_word WHERE FREETEXT([word],'"スカイテックブログ"'); -- ②FREETEXT検索（自然言語に基づいた検索）

検索結果（上部が①の結果、下部が②の結果）

①のほうは精確な検索を行いたい場合に適し、②のほうが柔軟な検索を行いたい場合に適しています。

上記のように、従来のLIKE検索のような"単語検索"ではなく、"ワード検索"が実施されるため、大量データを一括に検索して抽出する場合において、パフォーマンスに優れています。
・・・というのが、一般的なフルテキスト検索の使い方になりますが、自分の環境で試してみました。

検証結果

仮想サーバーでLIKE検索と、フルテキスト検索を比較しました。
（以下は Windows Server 2016、SQL Server 2017、CPU 4Core、メモリ 3GBでの検証です。）

例えば、下記のような100万件あるレコードをLIKE検索とフルテキスト検索で比較してみます。
下記はテスト的にレコードを100万件INSERTして、普通に全件取得した結果です。

①LIKE検索時の計測

以下のクエリを発行して計測します。

-- キャッシュを消す
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

-- キャッシュ消去後数秒待機する
WAITFOR DELAY '00:00:10'

-- 開始日時を取得する
DECLARE @STARTDATETIME datetime2 = SYSDATETIME()

SELECT *  FROM [sky_test].[dbo].[text_data_history]
  WHERE 
  user_name_search LIKE  '%亜%'

-- 終了日時と開始日時の差から処理時間を取得する
SELECT DATEDIFF(MILLISECOND, @STARTDATETIME, SYSDATETIME()) AS PROCESSING_TIME_MS

②フルテキスト検索時の計測

以下のクエリを発行して計測します。

-- キャッシュを消す
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

-- キャッシュ消去後数秒待機する
WAITFOR DELAY '00:00:10'

-- 開始日時を取得する
DECLARE @STARTDATETIME datetime2 = SYSDATETIME()

SELECT *
  FROM [sky_test].[dbo].[text_data_history]
  WHERE 
  CONTAINS((user_name_search), '"*亜*"')

SELECT DATEDIFF(MILLISECOND, @STARTDATETIME, SYSDATETIME()) AS PROCESSING_TIME_MS