こんにちは、株式会社divxでエンジニアをしている清水です。
以前、私はプロジェクト内でElastic Searchを導入するか、あるいはSQLのフルテキストインデックスを使用して全文検索を行うかを検討する機会がありました。その際、明確な答えを持って答えることができなかったため、今回、比較した記事を作成することにしました。
現在、Elastic Searchの導入を検討している方は、ぜひこの記事を参考にしてください。

SQLとElastic Searchの違いとは？

SQL

リレーショナルデータベース
クエリを書くことによってデータの格納・検索を行います
FULLTEXT INDEX
- ストレージエンジンの制限: FULLTEXTインデックスはMyISAM、Aria、Mroonga、InnoDBストレージエンジンで使用可能です。特に、InnoDBはMySQL 5.6.4以降でサポートされている
- 文字列長の制限: FULLTEXTインデックスの列は、MyISAMでは最大65,535バイト（UTF-8の場合、約21,845文字）、InnoDBではMySQL 5.6で最大767バイト、MySQL 5.7以降は3072バイトまで、MySQL 8.0以降はさらに大きな文字列長がサポートされます。
- 最小単語長: FULLTEXTインデックスでは、検索に使用する単語の最小文字数が設定されており、これもデフォルトでは4文字とされています。この設定は、ft_min_word_len変数を変更することで調整可能

Elastic Search

RESTful検索/分析エンジン
データはJSON形式でHTTPリクエストを用いてインデックスされ、検索されます
本記事ではElasticsearch 7.17.4で検証していますが、最新の安定版を確認するDBはMySQLを使用します

Elastic Searchの環境構築(Mac)

brew tap elastic/tap を実行する
brew install elastic/tap/elasticsearch-full を実行する
brew install elastic/tap/kibana-full を実行する
Elastic Searchが置いてあるディレクトリに移動する
homebrewの場合は、cd /opt/homebrew/bin
./elasticsearch を実行する
ElasticSearchのサーバーがローカル上に立ち上がる

何を検証するのか

FULLTEXT INDEX検索とElastic Searchを利用したテキスト検索を実施し、検索速度にどの程度の違いが出るかを以下の観点で検証します。

レコード数の速度比較
1. 大規模データ（レコード数100万件）と小規模データ（レコード数100件）での検索速度を比較します。
文字数の速度比較
1. 各レコードの文字数が多い場合（1000文字）と少ない場合（10文字）での検索速度を比較します。
重複文字の速度比較
1. データの文字列に重複が多い場合と少ない場合での検索速度を比較します

レコード数での比較

テストデータの準備

SQL側の準備

データベースを作成し切り替える

CREATE DATABASE IF NOT EXISTS TestDB;
USE TestDB;

2. テーブルを二つ作成する

-- レコード数が多い場合
CREATE TABLE large_dataset (
  id INT AUTO_INCREMENT PRIMARY KEY,
  content TEXT,
  FULLTEXT(content)
);

-- レコード数が少ない場合
CREATE TABLE small_dataset (
id INT AUTO_INCREMENT PRIMARY KEY,
content TEXT,
FULLTEXT(content)
);

3. ストアドプロシージャ作成（SQLの条件などをまとめており、今回はデータ生成に使用）

DELIMITER //

レコード数が多い場合
CREATE PROCEDURE generate_large_dataset(IN num_records INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE sample_text VARCHAR(255);

  WHILE i &lt; num_records DO
    SET sample_text = CONCAT('サンプルテキスト ', i, ' ランダム文字 ', RAND());
    INSERT INTO large_dataset (content) VALUES (sample_text);
    SET i = i + 1;
  END WHILE;
END //

レコード数が少ない場合
CREATE PROCEDURE generate_small_dataset(IN num_records INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE sample_text VARCHAR(255);

  WHILE i &lt; num_records DO
    SET sample_text = CONCAT('サンプルテキスト ', i, ' ランダム文字 ', RAND());
    INSERT INTO small_dataset (content) VALUES (sample_text);
    SET i = i + 1;
  END WHILE;
END //

DELIMITER ;

データ生成

レコード数が多いデータ（10万件）
CALL generate_large_dataset(100000);
レコード数が少ないデータ（100件）
CALL generate_small_dataset(100);

Elastic Search側の準備

Pythonで実装します
上記と同じようにデータセットの作成を行います
厳密には違いますが、indexというのがテーブルの役割をしています

from elasticsearch import Elasticsearch, helpers

es = Elasticsearch("<http://localhost:9200>")

# レコード数が多いデータ / ディスク容量の問題で一発で入り切らないため何回か実行してあげる
# インデックスが存在しない場合は作成
if not es.indices.exists(index=index_name):
  es.indices.create(index=index_name)

current_count = es.count(index=index_name)["count"]
remaining_records = num_records - current_count

if remaining_records <= 0:
  print(
    f"The index '{index_name}' already contains 
   {num_records} or more documents."
  )
  return

バッチ処理でデータをインデックス
for start in range(current_count, num_records, batch_size):
  end = min(start + batch_size, num_records)
  actions = [
    {
      "_index": index_name,
      "_source": {
        "content": f"サンプルテキスト {i} ランダム文字{generate_random_text()}"
      },
    }
    for i in range(start, end)
  ]

retries = 0
max_retries = 3
delay = 2
while retries &lt; max_retries:
    try:
        helpers.bulk(es, actions)
        print(f"Indexed documents from {start} to {end}")
        break  # 成功した場合はループを抜ける
    except helpers.BulkIndexError as bulk_error:
        for error in bulk_error.errors:
            if error["index"]["status"] == 429:
                es.indices.put_settings(
                    index=index_name,
                    body={"index.blocks.read_only_allow_delete": False},
                )
            print(error)  # エラーの詳細を出力
        retries += 1
        if retries &lt; max_retries:
            print(
                f"Retrying batch from {start} to {end} (Attempt {retries}/{max_retries})"
            )
            time.sleep(delay)
        else:
            print(
                f"Failed to index batch from {start} to {end} after {max_retries} attempts"
            )
# レコード数が少ないデータ
for i in range(100):
  actions = [
    {
      "_index": index_name,
      "_source": {
        "content": f"サンプルテキスト {i} ランダム文字 {generate_random_text()}"
      },
    }
    for i in range(100)
   ]
  helpers.bulk(es, actions)

検証

-- プロファイリングを有効にする
SET profiling = 1;

-- large_datasetでのFULLTEXT INDEX検索
SELECT * FROM large_dataset WHERE MATCH(content) AGAINST('"サンプルテキスト 99995"' IN BOOLEAN MODE);


-- small_datasetでのFULLTEXT INDEX検索
SELECT * FROM small_dataset WHERE MATCH(content) AGAINST('"サンプルテキスト 90"' IN BOOLEAN MODE);

-- プロファイリング結果を表示
SHOW PROFILES;
SET profiling = 0;

ElasticSearch

# 大規模データでの取得
curl -X POST "http://localhost:9200/large_dataset/_search?pretty" -H 'Content-Type: application/json' -d'
{
  "profile": true,
  "query": {
    "term": {
      "content": "サンプルテキスト 99995"
    }
  }
}

curl -X POST "http://localhost:9200/small_dataset/_search?pretty" -H 'Content-Type: application/json' -d'
{
  "profile": true,
  "query": {
    "term": {
      "content": "サンプルテキスト 90"    
    }
  }
}

結果

MySQL
- 大規模データ: 0.04845500秒
- 小規模データ: 0.00303800秒
ElasticSearch
- 大規模データ: 0.021秒
- 小規模データ: 0.04秒

大規模データの場合はElasticSearchが早く、小規模データはFULLTEXT INDEXの方が早い

文字数の速度比較

レコードを10000件で検証しましたが、レコード数に結果が依存していたためレコード1件で検証しました。（レコード数が多いと文字数に関係なくFULLTEXT INDEXが早くなる）

テストデータの準備

テーブル作成
- large_text_dataset_table
- small_text_dataset_table
SQL

ストアドプロシージャ作成

DELIMITER //
# 文字数が多い場合
CREATE PROCEDURE large_text_dataset_single_test()
BEGIN
    DECLARE sample_text VARCHAR(1000);
    SET sample_text = CONCAT('サンプルテキスト ', 1, ' ', REPEAT('ランダム文字', 100)); -- 1000文字に調整
    INSERT INTO large_text_dataset_table (content) VALUES (sample_text);
END //

# 文字数が少ない場合
CREATE PROCEDURE small_text_dataset_single_test()
BEGIN
    DECLARE sample_text VARCHAR(10);
    SET sample_text = CONCAT('サンプルテキスト ', 1); -- 10文字に調整
    INSERT INTO small_text_dataset_table (content) VALUES (sample_text);
END //

DELIMITER ;

レコード1件にデータ生成

CALL large_text_dataset_single_test();
CALL small_text_dataset_single_test();

ElasticSearch

from elasticsearch import Elasticsearch

es = Elasticsearch("http://localhost:9200")

# 大きなテキストのデータ
large_text = "サンプルテキスト 1 " + "ランダム文字" * 100  # 1000文字に調整
large_doc = {"content": large_text}

# 小さなテキストのデータ
small_text = "サンプルテキスト 1"  # 10文字に調整
small_doc = {"content": small_text}

# 大きなテキストをlarge_text_datasetにインデックス
es.index(index="large_text_dataset", document=large_doc)

# 小さなテキストをsmall_text_datasetにインデックス
es.index(index="small_text_dataset", document=small_doc)

検証

SET profiling = 1;

- 文字数が多い場合
SELECT * FROM large_text_dataset_table WHERE MATCH(content) AGAINST('"サンプルテキスト"' IN BOOLEAN MODE);

- 文字数が少ない場合
SELECT * FROM small_text_dataset_table WHERE MATCH(content) AGAINST('"サンプルテキスト"' IN BOOLEAN MODE);

SHOW PROFILES;

SET profiling = 0;

Elastic Search

// 文字数が多い方のデータ
curl -X POST "http://localhost:9200/large_text_dataset/_search?pretty" -H 'Content-Type: application/json' -d'
{
  "profile": true,
  "query": {
    "match": {
      "content": "サンプルテキスト"
    }
  }
}
'

// 文字数が少ない方のデータ
curl -X POST "http://localhost:9200/small_text_dataset/_search?pretty" -H 'Content-Type: application/json' -d'
{
  "profile": true,
  "query": {
    "match": {
      "content": "サンプルテキスト"
    }
  }
}
'

結果

SQL
- 文字数が多い方のデータ: 0.01667400
- 文字数が少ない方のデータ: 0.00187100
ElasticSearch
- 文字数が多い方のデータ: 0.057
- 文字数が少ない方のデータ: 0.005

全体的にFULLTEXT INDEXの方が早い

重複文字で比較

テストデータの準備

テーブル作成
- high_duplication_dataset
- low_duplication_dataset
SQL

ストアドプロシージャの作成

DELIMITER //

-- 重複文字が多い場合
CREATE PROCEDURE generate_high_repetition_dataset()
BEGIN
    DECLARE sample_text TEXT;
    SET sample_text = REPEAT('サンプルテキスト ', 10); 
    INSERT INTO high_duplication_dataset (content) VALUES (sample_text);
END //

-- 重複文字が少ない場合
CREATE PROCEDURE generate_low_repetition_dataset()
BEGIN
    DECLARE sample_text TEXT;
    SET sample_text = CONCAT('サンプルテキスト 1 ', REPEAT('ランダム文字', 8)); 
    INSERT INTO low_duplication_dataset (content) VALUES (sample_text);
END //

DELIMITER ;

データ生成

CALL generate_high_repetition_dataset();
CALL generate_low_repetition_dataset();

ElasticSearch

from elasticsearch import Elasticsearch

es = Elasticsearch("http://localhost:9200")

# 重複文字が多いデータセットと少ないデータセットを作成
high_duplication_text = "サンプルテキスト " * 10
high_duplication_doc = {"content": high_duplication_text}

low_duplication_text = "サンプルテキスト 1 " + "ランダム文字" * 8
low_duplication_doc = {"content": low_duplication_text}

es.index(index="high_duplication_dataset", document=high_duplication_doc)
es.index(index="low_duplication_dataset", document=low_duplication_doc)

検証

SET profiling = 1;

-- 重複多い
SELECT * FROM high_duplication_dataset WHERE MATCH(content) AGAINST('"サンプルテキスト"' IN BOOLEAN MODE);

-- 重複少ない
SELECT * FROM low_duplication_dataset WHERE MATCH(content) AGAINST('"サンプルテキスト"' IN BOOLEAN MODE);

SHOW PROFILES;

SET profiling = 0;

ElasticSearch

curl -X GET "http://localhost:9200/high_duplication_dataset/_search?pretty" -H 'Content-Type: application/json' -d '{
  "query": {
    "match": {
      "content": "サンプルテキスト"
    }
  }
}'

curl -X GET "http://localhost:9200/low_duplication_dataset/_search?pretty" -H 'Content-Type: application/json' -d '{
  "query": {
    "match": {
      "content": "サンプルテキスト"
    }
  }
}'

結果

SQL
- 重複文字が多い場合: 0.00286800
- 重複文字が少ない場合: 0.00160700
ElasticSearch
- 重複文字が多い場合: 0.022
- 重複文字が少ない場合: 0.014

まとめ

結果の分析

各ケースにおいて、検索にかかった時間（ミリ秒）を記録し、SQLとElasticsearchのそれぞれで比較しました。全データの集約を行い、実行結果を表などにまとめて、SQLが優れている条件、ElasticSearchが優れている条件を分析しました。

結果

検証結果は、レコード数が増加するにつれてElasticSearchが相対的に高速である一方、データ量が少ない場合にはSQLのFULLTEXTインデックスがより効率的であることを示しました。また、各文字数や重複文字の状況下でも性能差が明確に見られました。
具体的には、SQLのFULLTEXT INDEXは小規模データにおいて速く、ElasticSearchは大規模データにおいて速いという結果が得られました。

お悩みご相談ください

ご相談フォーム | 株式会社divx（ディブエックス） DIVXのご相談フォームページです。株式会社divx（ディブエックス）

DIVX テックブログ

SQLのFULLTEXT INDEXとElastic Searchの速度比較

はじめに

SQLとElastic Searchの違いとは？

Elastic Searchの環境構築(Mac)

何を検証するのか

レコード数での比較

テストデータの準備

SQL側の準備

Elastic Search側の準備

検証

結果

文字数の速度比較

テストデータの準備

検証

結果

重複文字で比較

テストデータの準備

検証

結果

まとめ

結果の分析

結果

お悩みご相談ください

前の記事

次の記事

お気軽にご相談ください

DIVXブログ

テックブログタグ一覧

人気記事ランキング

DIVX テックブログ

SQLのFULLTEXT INDEXとElastic Searchの速度比較

はじめに

SQLとElastic Searchの違いとは？

Elastic Searchの環境構築(Mac)

何を検証するのか

レコード数での比較

テストデータの準備

SQL側の準備

Elastic Search側の準備

検証

結果

文字数の速度比較

テストデータの準備

検証

結果

重複文字で比較

テストデータの準備

検証

結果

まとめ

結果の分析

結果

お悩みご相談ください

前の記事

次の記事

お気軽にご相談ください

DIVXブログ

テックブログ タグ一覧

人気記事ランキング

テックブログタグ一覧