部分一致は索引が効かない — 全文検索とMeilisearch

全文検索とMeilisearch。検索窓と虫眼鏡、多くの文書から選び出されたカード。

アプリに検索窓を付ける、という話は必ず来ます。そして、ほぼ全員が最初にこう書きます。

前回: 3つ目で言語の地図ができる — Pythonを触る 作業場所はVMの中(Ubuntu)です。ここまでで作った ~/server(API)と appdb の tasks をそのまま使います。

SELECT * FROM tasks WHERE title LIKE '%請求%';

動きます。 数十件のうちは、一瞬で返ってきます。

そして、データが増えたある日、検索窓だけが異様に遅くなります。 他の画面は普通なのに、検索を押した瞬間だけ数秒固まる。

パフォーマンス改善の回で、遅いSQLに索引を張って直すということをやりました。同じ手が、ここでは効きません。

LIKE '%文字%' には、索引がどうやっても効かないからです。

これは設定ミスでも書き方の下手さでもなく、索引という仕組みの構造上そうなる、という話です。まずそこを EXPLAIN で目に見えるようにします。

そのうえで、日本語はさらに厄介だという話をして、MySQL の全文索引でどこまで戦えるかを先に試し、足りなくなったところから専用の道具(Meilisearch)を足します。

対象レベル: 初級〜中級 / 区分: 必要に応じて読む

目次

この記事でわかること

  • LIKE '%文字%' は、索引を張ってあっても使われないこと。前と後ろのどちらにワイルドカードが付くかで決まること
  • 日本語には単語の区切りが無いこと。そこが全文検索の本質的な難しさで、英語の解説記事がそのまま通用しない理由
  • MySQL の FULLTEXT + ngram でかなりの所まで行けること。それでも足りないのはどこからかという線引き
  • 検索用のデータは「コピー」であること。元のデータベースが正で、同期は必ずずれること
  • Elasticsearch が正解ではないこと。重さを払えるかどうかで選ぶという、ログ集約の回と同じ判断軸

この記事でやること

  1. LIKE '%…%' に EXPLAIN をかけて、索引が1つも使われていないことを目で確認する
  2. FULLTEXT 索引を ngram パーサで張って、同じ検索がどう変わるかを測り直す
  3. Meilisearch を Docker Compose で立てる。 ports: を外に出さず、マスターキーを必ず設定する
  4. MySQL から Meilisearch へデータを流し込み、タイプミスしたまま検索してヒットすることを確かめる
  5. わざと同期をずらして、消したはずのデータが検索結果に残る事故を起こし、同期の方式を決める

先に知る言葉

この記事で初めて出てくる言葉です。 基本的な言葉は用語集にもまとめてあります。

言葉意味
全文検索文章の中身から探すこと。 「この値と等しい行」ではなく「この語を含む文書」を探す
前方一致 / 部分一致'請求%'=先頭が決まっている(前方一致)。'%請求%'=どこにあってもいい(部分一致)
トークン(語)検索の最小単位。 文章をここまで刻んでから索引に入れる
トークナイズ(分かち書き)文章をトークンに切る処理。 英語は空白で切れる。日本語はここが難しい
形態素解析辞書を使って日本語を単語に切る方式。「東京都」を「東京 / 都」と切る
N-gram(ngram)辞書を使わず、機械的にN文字ずつ切る方式。「東京都」を「東京 / 京都」と切る
転置インデックス「語 → その語が出てくる文書の番号」の対応表。 全文検索エンジンの中身はほぼこれ
FULLTEXTMySQL に組み込みの全文索引。 MATCH ... AGAINST で引く
あいまい検索(タイポ許容)綴りが少し違っても拾うこと。kubenetes で Kubernetes に当てる
Meilisearch軽い全文検索エンジン。 数百MBのメモリで動き、既定でタイポに強い
Elasticsearch重いが何でもできる全文検索エンジン。 集計やログ分析まで守備範囲
インデックス(検索側)検索エンジンの中の「表」にあたるもの。 データベースの索引とは別の意味なので注意

「索引は辞書順に並んだ早見表である」が、この記事の前半の全部です。 辞書は、先頭の文字が分からないと引けません。 「真ん中に『請求』が入っている単語を全部出せ」と言われたら、辞書は最初のページから全部めくるしかありません。 データベースの索引も、まったく同じ理由で '%請求%' に手が出ません。

① まず確かめる — なぜ LIKE '%文字%' は遅いのか

推測しません。 パフォーマンス改善の回でやったとおり、MySQL に「どう実行するつもりか」を聞きます。

検索できる中身を用意する

tasks にダミーが20万件入っている前提です。消してしまっていたら、入れ直してください。

bash
$ sudo mysql
SET SESSION cte_max_recursion_depth = 1000000;

INSERT INTO appdb.tasks (title, user_id)
WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 200000
)
SELECT CONCAT('ダミー', n), 1 FROM seq;

このままだと「検索する意味のある日本語」が1つも無いので、探したくなる文を数件だけ足します。

INSERT INTO appdb.tasks (title, user_id) VALUES
  ('9月分の請求書を作成して送付する', 1),
  ('請求データのバックアップを取る', 1),
  ('Kubernetes クラスタの証明書を更新する', 1),
  ('東京都庁への提出書類をまとめる', 1),
  ('京都出張の宿を押さえる', 1);

20万件の中に、5件だけ「本物」が混じっている状態です。ここから 請求 を探します。

索引を張っていない状態で測る

SELECT id, title FROM appdb.tasks WHERE title LIKE '%請求%';
+--------+-------------------------------------+
| id     | title                               |
+--------+-------------------------------------+
| 200004 | 9月分の請求書を作成して送付する     |
| 200005 | 請求データのバックアップを取る      |
+--------+-------------------------------------+
2 rows in set (0.11 sec)

0.11 秒。 2件を取り出すのに、です。括弧の中の数字を必ず見る癖は、前の回で付けました。

索引を張っても、変わらない

普通はここで索引を張ります。張ってみます。

CREATE INDEX idx_title ON appdb.tasks (title);

もう一度、同じSQLを打ちます。

SELECT id, title FROM appdb.tasks WHERE title LIKE '%請求%';
2 rows in set (0.11 sec)

1ミリ秒も変わっていません。 ここが今回の入口です。

EXPLAIN で「索引を使っていない」を見る

EXPLAIN SELECT id, title FROM appdb.tasks WHERE title LIKE '%請求%';
+----+-------------+-------+-------+---------------+-----------+---------+------+--------+----------+--------------------------+
| id | select_type | table | type  | possible_keys | key       | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+-------+-------+---------------+-----------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | tasks | index | NULL          | idx_title | 802     | NULL | 199243 |    11.11 | Using where; Using index |
+----+-------------+-------+-------+---------------+-----------+---------+------+--------+----------+--------------------------+

見るのは前の回と同じ3つです。

列この結果意味
typeindexALL の親戚。 「索引を先頭から最後まで全部読む」という意味
possible_keysNULL絞り込みに使える索引が1つも無いと判断されている
rows1992432行のために20万行分めくるつもり

key に索引の名前が出ているので「使っている」と誤読しがちです。 ですが type: index はフルスキャンの一種です。 possible_keys: NULL のほうが本音で、「この WHERE は索引で絞れない」と言っています。 Using index も「速い」ではなく「表の本体を見ずに索引だけで済ませた」という意味です。読む量は減っても、全部読むことは変わりません。

[スクリーンショット: 01_LIKEのEXPLAIN.png] EXPLAIN の結果を撮ってください。type が index、possible_keys が NULL、rows が20万近い、この3点が読めることが条件です。

前方一致なら効く、という決定的な差

同じ列の、同じ索引で、書き方だけ変えます。

EXPLAIN SELECT id, title FROM appdb.tasks WHERE title LIKE 'ダミー1500%';
+----+-------------+-------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tasks | range | idx_title     | idx_title | 802     | NULL |   11 |   100.00 | Using where; Using in |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+----------+-----------------------+

type: range、rows: 11。 20万行が11行になりました。索引はちゃんと効いています。

違いは、先頭の % があるかどうか、それだけです。

書き方索引理由
LIKE 'ダミー1500%'効く先頭が決まっている。 辞書の「た」のページから読み始められる
LIKE '%請求%'効かない先頭が決まらない。 辞書の1ページ目から全部めくるしかない
LIKE '%請求'効かない同上(後方一致も同じく効きません)

索引は、値を辞書順に並べたものです。 並べてあるから「先頭がこれ」で範囲を切れます。 真ん中に何が入っているかは、並べ方と何の関係もありません。 だから絞れません。 これは MySQL の欠点ではなく、PostgreSQL でも SQLite でも同じです。並べる、という仕組みそのものの性質です。

索引を消しておきます。 このあと別の索引を張るので、残しておく理由がありません。

DROP INDEX idx_title ON appdb.tasks;

ここまでが、この記事の前提です。「検索窓が遅い」は、索引を張り忘れているのではなく、張っても効かない種類の遅さだ、という理解が要ります。

② 日本語だと、さらに難しい

「じゃあ全文検索エンジンを入れれば解決」——その前に、なぜ専用のエンジンが必要になるのかを見ます。 ここを飛ばすと、あとで設定の意味がまったく分からなくなります。

全文検索の中身は「語 → 文書番号」の表

全文検索エンジンがやっていることは、実はとても単純です。

文書1: 請求書を作成して送付する
文書2: 請求データのバックアップを取る
文書3: 京都出張の宿を押さえる

これを、あらかじめ語ごとにひっくり返して持っておきます。

請求   → 1, 2
書     → 1
作成   → 1
データ → 2
京都   → 3

これが転置インデックスです。 「請求」で引けば、文書を1つも読まずに 1 と 2 が出ます。

これは索引が効く形です。 「語」が先頭に来ているので、辞書順に並べられます。 LIKE '%請求%' が遅いのは、この表を作っていないからです。全文検索とは、要するに「先に語で切っておく」ことです。

そして、日本語には切れ目が無い

さっきの表を作るには、文章を語に切らなければいけません。 英語なら簡単です。

I sent the invoice
→ I / sent / the / invoice

空白で切ればいい。 世の中の全文検索の解説が簡単そうに見えるのは、これが理由です。

日本語はこうです。

9月分の請求書を作成して送付する
→ ?

空白がありません。 どこで切ればいいのか、文字列を見ただけでは決まりません。ここが全文検索の、日本語における本質的な難しさです。

やり方は大きく2つあります。

形態素解析N-gram(ngram)
やり方辞書を引いて単語に切る機械的にN文字ずつ切る(N=2なら2文字ずつ)
「東京都庁」は東京 / 都庁東京 / 京都 / 都庁
「京都」で検索するとヒットしない(正しい)ヒットする(誤ヒット)
取りこぼし辞書に無い新語を切り損なう原理的に取りこぼさない
索引の大きさ小さい大きい(文字数とほぼ同じ数のトークンが出る)
辞書の保守要る(新語・固有名詞)要らない

さっき「東京都庁」と「京都出張」の両方を入れたのは、これを見るためです。

どちらが正しい、ではありません。 取りこぼしを嫌うなら ngram、ノイズを嫌うなら形態素解析です。 検索窓は「出てこない」ほうが致命的なことが多いので、迷ったら ngram 側から始めるのが無難です。

MySQL の FULLTEXT が使えるのは、標準では ngram のほうだけです(形態素解析用の MeCab プラグインもありますが、別途の導入と辞書の面倒が要ります)。Meilisearch は逆に、日本語を単語に切る側を持っています。この違いが、あとで結果の差になって出てきます。

③ MySQL の全文索引で、どこまで行けるか

専用のエンジンを立てる前に、いま持っている MySQL で試します。 これをやらずに Meilisearch を入れると、「なぜ必要だったのか」が一生分かりません。

FULLTEXT 索引を ngram で張る

bash
$ sudo mysql
ALTER TABLE appdb.tasks ADD FULLTEXT INDEX ft_title (title) WITH PARSER ngram;
Query OK, 0 rows affected (12.31 sec)
Records: 0  Duplicates: 0  Warnings: 0

20万件で十数秒かかります。この「張るのに時間がかかる」ことは、あとで効いてきます。

部分意味
FULLTEXT INDEX普通の索引ではなく、全文索引を作る
ft_title (title)索引の名前と、対象の列
WITH PARSER ngram切り方を ngram にする。 これを書かないと空白で切られます(=日本語がまるごと1語になり、まったく使い物になりません)

何文字ずつ切っているかを確認します。

SHOW VARIABLES LIKE 'ngram_token_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| ngram_token_size | 2     |
+------------------+-------+

既定は 2 文字です。この数字は、あとで「1文字で検索してもヒットしない」という形で必ず踏みます。

MATCH ... AGAINST で引く

全文索引は LIKE では使われません。専用の書き方が要ります。

SELECT id, title FROM appdb.tasks
WHERE MATCH(title) AGAINST('請求' IN BOOLEAN MODE);
+--------+-------------------------------------+
| id     | title                               |
+--------+-------------------------------------+
| 200004 | 9月分の請求書を作成して送付する     |
| 200005 | 請求データのバックアップを取る      |
+--------+-------------------------------------+
2 rows in set (0.00 sec)

0.11 秒 → 0.00 秒。 結果は同じで、時間だけが消えました。

EXPLAIN で理由を見ます。

EXPLAIN SELECT id, title FROM appdb.tasks
WHERE MATCH(title) AGAINST('請求' IN BOOLEAN MODE);
+----+-------------+-------+----------+---------------+----------+---------+-------+------+----------+-------------------------------+
| id | select_type | table | type     | possible_keys | key      | key_len | ref   | rows | filtered | Extra                         |
+----+-------------+-------+----------+---------------+----------+---------+-------+------+----------+-------------------------------+
|  1 | SIMPLE      | tasks | fulltext | ft_title      | ft_title | 0       | const |    1 |   100.00 | Using where; Ft_hints: sorted |
+----+-------------+-------+----------+---------------+----------+---------+-------+------+----------+-------------------------------+
列この結果意味
typefulltext全文索引を使った、という宣言
keyft_title使った索引の名前
rows120万 → 1。 ここが全部です

[スクリーンショット: 02_FULLTEXTのEXPLAIN.png] type が fulltext、rows が 1 桁になっている EXPLAIN の結果を撮ってください。01_ と並べると差が一目で分かります。

IN BOOLEAN MODE は、とりあえず付けておいて構いません。 付けないと「自然言語モード」になり、ストップワード(ありふれた語)の扱いなどが変わります。 検索窓の実装としては BOOLEAN MODE のほうが素直です。+必須 -除外 "完全一致" が使えます。

ngram の限界を、自分の目で見る

ここからが本題です。「どこから専用の道具が要るか」を判断するために、わざと壊します。

限界その1 — 「京都」で「東京都庁」が出る。

SELECT id, title FROM appdb.tasks
WHERE MATCH(title) AGAINST('京都' IN BOOLEAN MODE);
+--------+--------------------------------+
| id     | title                          |
+--------+--------------------------------+
| 200007 | 東京都庁への提出書類をまとめる |
| 200008 | 京都出張の宿を押さえる         |
+--------+--------------------------------+
2 rows in set (0.00 sec)

②で予告したとおりです。 ngram は辞書を持たないので、「東京都庁」の中の「京都」を区別できません。

限界その2 — 1文字では何も出ない。

SELECT id, title FROM appdb.tasks
WHERE MATCH(title) AGAINST('請' IN BOOLEAN MODE);
Empty set (0.00 sec)

ngram_token_size が 2 なので、2文字未満の語は索引に存在しません。 検索窓に1文字打った瞬間に無言で0件になります。エラーは出ません。

限界その3 — タイプミスを1文字も許さない。

SELECT id, title FROM appdb.tasks
WHERE MATCH(title) AGAINST('kubenetes' IN BOOLEAN MODE);
Empty set (0.00 sec)

Kubernetes の r が抜けているだけです。人間なら一発で分かります。 データベースは0件を返します。

判断 — どこから専用の道具が要るのか

ここまでで、MySQL の FULLTEXT はかなり戦えることが分かりました。 正直に整理します。

やりたいことMySQL の FULLTEXT判断
「請求」を含むタスクを速く出すできるこれで足りるなら、何も足さない
+請求 -バックアップ のような絞り込みできる(BOOLEAN MODE)同上
関連度の高い順に並べるできる(AGAINST の戻り値がスコア)同上
タイプミスを吸収するできないここから先
打った途中で候補を出す(入力補完)できない(前方一致は別途工夫が要る)ここから先
ふりがな・表記ゆれを吸収するできないここから先
並び順の重みを自分で調整するほぼできないここから先
書き込みが多い表で使う苦しい(全文索引の更新が書き込みを重くする)ここから先

線はここです。 「含む語で絞れれば十分」なら MySQL で終わり。 「人間が打ち間違える前提の検索窓」を作るなら、専用のエンジンが要ります。

もう1つ、表に書きにくい理由があります。

全文索引は、書き込みを重くします。 INSERT / UPDATE のたびに転置インデックスを更新するからです。 「読むための都合」を「書くための表」に載せている、という構造上の無理があります。 検索を別のプロセスに逃がす最大の理由は、速さではなく、この分離です。

この記事では、線の向こう側へ行きます。

④ Meilisearch を Docker Compose で立てる

なぜ Elasticsearch ではないのか(先に言っておく)

全文検索と検索すると、まず Elasticsearch が出てきます。 実際、多くの現場で動いています。それでも、この記事では入れません。

このVMのメモリの半分以上を、検索の道具が持っていくからです。

ログ集約の回で Loki を選んだのとまったく同じ判断です。詳しい比較は⑦でやります。 ここでは「軽いほうを選んだ」とだけ覚えておいてください。

置き場所を作る

Docker Compose の回でやったとおり、1つのフォルダに1つの compose.yml です。

bash
$ mkdir -p ~/search
$ cd ~/search

マスターキーを先に作る

Meilisearch は、キーを設定しないと「誰でも全部消せる状態」で起動します。 Redis の回の requirepass と同じ話です。

キーを作って、.env に置きます。

bash
$ cd ~/search
$ umask 077
$ echo "MEILI_MASTER_KEY=$(openssl rand -base64 24)" > .env
$ cat .env
MEILI_MASTER_KEY=Qf3xR8mTd2vKpLw6ZaYn1BcH+sEo0iUj

この値は、あとで何度も使います。 表示して控えておいてください。

コマンド意味
umask 077このあと作るファイルを、自分だけが読める権限にする(権限の回の 600)
openssl rand -base64 24推測できないランダムな文字列を作る。24バイト=32文字

.env を Git に入れないでください。 GitHub の回でやった .gitignore に 必ず .env を書きます。 compose.yml は共有していい。.env は共有してはいけない。 この分け方が Compose の .env の存在理由です。

bash
$ ls -l ~/search/.env
-rw------- 1 ubuntu ubuntu 49 Sep  9 10:12 /home/ubuntu/search/.env

-rw------- になっていることを確認してください。 なっていなければ chmod 600 ~/search/.env です。

compose.yml を書く

bash
$ nano ~/search/compose.yml
services:
  meilisearch:
    image: getmeili/meilisearch:v1.11
    container_name: meilisearch
    restart: unless-stopped
    environment:
      MEILI_MASTER_KEY: ${MEILI_MASTER_KEY}
      MEILI_ENV: production
      MEILI_NO_ANALYTICS: "true"
    ports:
      - "127.0.0.1:7700:7700"
    volumes:
      - meili_data:/meili_data

volumes:
  meili_data:

1行ずつ読みます。

行意味
image: ...:v1.11バージョンを固定する。 latest にすると、ある日勝手に上がって壊れます
restart: unless-stoppedVMを再起動しても勝手に立ち上がる。 自分で止めたときだけ止まったまま
MEILI_MASTER_KEY: ${MEILI_MASTER_KEY}.env の値を読み込む。 キーを compose.yml に直接書かないため
MEILI_ENV: productionこれが効きます(下記)
MEILI_NO_ANALYTICS: "true"利用統計を外部に送るのを止める
ports: "127.0.0.1:7700:7700"VMの中からしか繋がらない(下記)
volumes: meili_data:/meili_data索引の実体の置き場。 これが無いと、コンテナを作り直すたびに全部消えます

ports: を外に出さない

ここが今回、一番事故りやすいところです。

書き方誰が繋げるか
"7700:7700"VMの外の全員。 0.0.0.0 に開きます
"127.0.0.1:7700:7700"VMの中だけ
(ports: を書かない)同じ Compose の中のコンテナだけ

Docker Compose の回で「db に ports: を書かなかった」のと同じ話です。本当は ports: を丸ごと書かないのが最善ですが、今回は API(~/server/app.js)がコンテナの外、VM上で直接動いているので、127.0.0.1 に限定して開けます。

ufw で 7700 を塞いだから大丈夫、は成り立ちません。 Docker はポート公開のときに iptables を直接書き換えるので、ufw status に何も出ないまま外へ開きます。 これは Compose の回で一度踏んだ罠です。 塞ぐのではなく、最初から開けないのが正解です。

MEILI_ENV: production も、ここと繋がっています。

値何が変わるか
development(既定)マスターキーが無くても起動する。 ブラウザ用の検索画面が付いてくる
productionマスターキーが無いと起動を拒否する。 16バイト以上を要求する

「うっかりキー無しで起動していた」を、起動そのものを失敗させることで防ぎます。 設定ではなく、仕組みで防ぐやり方です。

立ち上げて、確かめる

bash
$ cd ~/search
$ docker compose up -d
$ docker compose ps
NAME          IMAGE                          STATUS         PORTS
meilisearch   getmeili/meilisearch:v1.11     Up 8 seconds   127.0.0.1:7700->7700/tcp

PORTS が 127.0.0.1:7700->7700/tcp になっていることを必ず見てください。 ここが 0.0.0.0:7700-> だったら、外に開いています。 すぐ docker compose down して compose.yml を直します。

生きているか聞きます。

bash
$ curl http://127.0.0.1:7700/health
{"status":"available"}

/health だけは、キー無しで答えます。 監視から叩くためです。それ以外は全部キーが要ります。

bash
$ curl -s http://127.0.0.1:7700/indexes
{"message":"The Authorization header is missing. It must use the bearer authorization method.","code":"missing_authorization_header","type":"auth","link":"https://docs.meilisearch.com/errors#missing_authorization_header"}

弾かれました。これが正しい状態です。 キーを付け直します。

bash
$ cd ~/search
$ export MEILI_MASTER_KEY=$(grep MEILI_MASTER_KEY .env | cut -d= -f2-)
$ curl -s -H "Authorization: Bearer $MEILI_MASTER_KEY" http://127.0.0.1:7700/indexes
{"results":[],"offset":0,"limit":20,"total":0}

中身は空ですが、認証は通りました。

export した環境変数は、そのターミナルを閉じると消えます。 別のターミナルを開いたら、もう一度打ってください。

メモリを見ておく

入れたものの重さは、必ず数字で見ます。

bash
$ docker stats --no-stream
CONTAINER ID   NAME          CPU %     MEM USAGE / LIMIT     MEM %
b3d1a2f7c904   meilisearch   0.02%     58.4MiB / 3.83GiB     1.49%

まだ空なので 60MB 弱です。データを入れると増えます。 ⑤のあとで、もう一度見ます。

⑤ データを入れて、検索する

検索用のデータは「コピー」である

手を動かす前に、この記事で一番大事なことを先に言います。

Meilisearch に入れるデータは、MySQL のデータの「コピー」です。 正しいのは MySQL のほうで、Meilisearch は「検索を速くするために作った、劣化した写し」です。

この順番を間違えると、こうなります。

  • 「検索結果に出た件数」を、正しい件数として画面に出してしまう
  • Meilisearch にしか無い項目を作ってしまい、MySQL から復元できなくなる
  • Meilisearch のディスクが壊れたときに、戻せなくなる

判断の基準は1つです。 「Meilisearch のデータを全部消しても、MySQL から作り直せるか?」 YES を維持し続けてください。 これが崩れた瞬間、検索エンジンが2つ目のデータベースになります。

この関係は、キャッシュの回の Redis とまったく同じ構造です。速いほうは、いつ消えても構わない側です。

流し込むスクリプトを書く

~/server には mysql2 が入っています(MySQL の回で入れました)。それをそのまま使います。

bash
$ nano ~/server/sync-search.mjs
import mysql from 'mysql2/promise';

const MEILI = 'http://127.0.0.1:7700';
const KEY = process.env.MEILI_MASTER_KEY;
const INDEX = 'tasks';
const CHUNK = 5000;

if (!KEY) {
  console.error('MEILI_MASTER_KEY が設定されていません');
  process.exit(1);
}

const db = await mysql.createConnection({
  host: '127.0.0.1',
  user: 'appuser',
  password: process.env.DB_PASSWORD,
  database: 'appdb',
});

const [rows] = await db.query(
  'SELECT id, title, done, user_id, created_at FROM tasks'
);
await db.end();

console.log(`MySQL から ${rows.length} 件`);

// Meilisearch が扱える形に直す
const docs = rows.map((r) => ({
  id: r.id,
  title: r.title,
  done: Boolean(r.done),
  user_id: r.user_id,
  created_at: Math.floor(r.created_at.getTime() / 1000),
}));

// 一度に全部送らない。5000件ずつに割る
for (let i = 0; i < docs.length; i += CHUNK) {
  const chunk = docs.slice(i, i + CHUNK);
  const res = await fetch(
    `${MEILI}/indexes/${INDEX}/documents?primaryKey=id`,
    {
      method: 'POST',
      headers: {
        'Authorization': `Bearer ${KEY}`,
        'Content-Type': 'application/json',
      },
      body: JSON.stringify(chunk),
    }
  );
  const body = await res.json();
  console.log(`${i + chunk.length} 件目まで送信 → taskUid=${body.taskUid}`);
}

要点は3つです。

部分なぜそうするか
CHUNK = 500020万件を1回のリクエストで送らない。 JSON が数十MBになり、メモリを一気に食います
created_at を数値にMeilisearch は日時型を持ちません。 並べ替えに使うなら秒数(数値)にして渡します
primaryKey=id同じ id で送り直すと上書きになる。 これが「入れ直せる」ことの根拠です

動かします。

bash
$ cd ~/server
$ export MEILI_MASTER_KEY=$(grep MEILI_MASTER_KEY ~/search/.env | cut -d= -f2-)
$ export DB_PASSWORD='(MySQLの回で控えたappuserのパスワード)'
$ node sync-search.mjs
MySQL から 200008 件
5000 件目まで送信 → taskUid=0
10000 件目まで送信 → taskUid=1
...
200008 件目まで送信 → taskUid=40

送信は「受け付けた」だけ。まだ入っていない

taskUid という数字が返ってきました。これが重要です。

Meilisearch への投入は非同期です。 POST が成功しても、それは「行列に並べた」という意味でしかありません。 索引を作る処理は、そのあと裏で走ります。

進み具合を見ます。

bash
$ curl -s -H "Authorization: Bearer $MEILI_MASTER_KEY" \
    "http://127.0.0.1:7700/tasks?limit=1"
{"results":[{"uid":40,"indexUid":"tasks","status":"processing","type":"documentAdditionOrUpdate","details":{"receivedDocuments":5008,"indexedDocuments":null},...

status が processing の間は、まだ検索に出てきません。 succeeded に変わるまで待ちます。

紛らわしいので先に言います。 /tasks は「Meilisearch の処理待ち行列」であって、私たちの tasks インデックスとは無関係です。 インデックスのほうは /indexes/tasks です。このURLの衝突で30分溶かしました(⑧に書きます)。

入り終わったか、件数で確かめます。

bash
$ curl -s -H "Authorization: Bearer $MEILI_MASTER_KEY" \
    http://127.0.0.1:7700/indexes/tasks/stats
{"numberOfDocuments":200008,"isIndexing":false,"fieldDistribution":{"created_at":200008,"done":200008,"id":200008,"title":200008,"user_id":200008}}

isIndexing: false かつ numberOfDocuments が MySQL の件数と一致。 これが「同期できている」の定義です。

検索する

bash
$ curl -s -X POST 'http://127.0.0.1:7700/indexes/tasks/search' \
    -H "Authorization: Bearer $MEILI_MASTER_KEY" \
    -H 'Content-Type: application/json' \
    -d '{"q":"請求"}'
{"hits":[
  {"id":200004,"title":"9月分の請求書を作成して送付する","done":false,"user_id":1,"created_at":1757380000},
  {"id":200005,"title":"請求データのバックアップを取る","done":false,"user_id":1,"created_at":1757380000}
],"query":"請求","processingTimeMs":3,"limit":20,"offset":0,"estimatedTotalHits":2}

processingTimeMs: 3。 20万件から3ミリ秒です。

タイプミスしたまま検索する

ここが、MySQL では絶対にできなかったところです。

bash
$ curl -s -X POST 'http://127.0.0.1:7700/indexes/tasks/search' \
    -H "Authorization: Bearer $MEILI_MASTER_KEY" \
    -H 'Content-Type: application/json' \
    -d '{"q":"kubenetes"}'
{"hits":[
  {"id":200006,"title":"Kubernetes クラスタの証明書を更新する","done":false,"user_id":1,"created_at":1757380000}
],"query":"kubenetes","processingTimeMs":2,"estimatedTotalHits":1}

r が抜けたまま、ヒットしました。 ③で MySQL が Empty set を返したのと同じ検索語です。

[スクリーンショット: 03_タイポ検索がヒットする.png] kubenetes(綴り違い)で検索して Kubernetes の行が返っている curl の結果を撮ってください。"query":"kubenetes" と "title":"Kubernetes ..." の両方が同じ画面に写っていることが条件です。

もう1つ。打ちかけでも出ます。

bash
$ curl -s -X POST 'http://127.0.0.1:7700/indexes/tasks/search' \
    -H "Authorization: Bearer $MEILI_MASTER_KEY" \
    -H 'Content-Type: application/json' \
    -d '{"q":"証明"}'
{"hits":[{"id":200006,"title":"Kubernetes クラスタの証明書を更新する",...}],"query":"証明","processingTimeMs":2,"estimatedTotalHits":1}

「証明」で「証明書」が出ます。 最後の語は前方一致として扱われるので、入力の途中でも結果が出せます。 これが「打ちながら結果が変わる検索窓」の正体です。

日本語では、タイポ許容が効きにくい

正直に書きます。ここは期待しすぎないでください。

bash
$ curl -s -X POST 'http://127.0.0.1:7700/indexes/tasks/search' \
    -H "Authorization: Bearer $MEILI_MASTER_KEY" \
    -H 'Content-Type: application/json' \
    -d '{"q":"請求所"}'
{"hits":[],"query":"請求所","processingTimeMs":1,"estimatedTotalHits":0}

「請求書」の「書」を打ち間違えただけですが、0件です。理由は設定にあります。

Meilisearch は、短い語ではタイポを許しません。 既定では 5文字未満は1文字の誤りも許さず、9文字未満は誤り1文字までです。 kubenetes は9文字なので効きました。 日本語は単語に切ると2〜4文字が大半なので、ほとんどの語が「短すぎて」対象外になります。

設定で下げることはできます。

bash
$ curl -s -X PATCH 'http://127.0.0.1:7700/indexes/tasks/settings/typo-tolerance' \
    -H "Authorization: Bearer $MEILI_MASTER_KEY" \
    -H 'Content-Type: application/json' \
    -d '{"minWordSizeForTypos":{"oneTypo":3,"twoTypos":7}}'

ただし、下げるとノイズが増えます。 2〜3文字の日本語は、1文字違うともう別の意味の語だからです。

「タイポに強い」は、英数字の固有名詞(製品名・人名・型番)で一番効きます。 日本語の一般名詞では、期待値を下げてください。 日本語で効くのは、むしろ「語に切ってくれること」と「前方一致で打ちかけでも出ること」のほうです。

メモリをもう一度見る

bash
$ docker stats --no-stream
CONTAINER ID   NAME          CPU %     MEM USAGE / LIMIT     MEM %
b3d1a2f7c904   meilisearch   0.15%     412.6MiB / 3.83GiB    10.5%

20万件で 400MB 前後。 Elasticsearch なら、空の状態でこの数倍から始まります。 ⑦で比べます。

[スクリーンショット: 04_docker_statsのメモリ.png] docker stats --no-stream の結果を撮ってください。MEM USAGE / LIMIT の列が読めることが条件です。

⑥ 同期は必ずずれる

ここが、検索エンジンを入れたあとに長く付き合う問題です。

わざとずらしてみる

MySQL から1件消します。検索エンジンには何も言いません。

bash
$ sudo mysql
DELETE FROM appdb.tasks WHERE title = '請求データのバックアップを取る';
SELECT COUNT(*) FROM appdb.tasks WHERE title LIKE '%請求%';
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+

MySQL では1件になりました。検索してみます。

bash
$ curl -s -X POST 'http://127.0.0.1:7700/indexes/tasks/search' \
    -H "Authorization: Bearer $MEILI_MASTER_KEY" \
    -H 'Content-Type: application/json' \
    -d '{"q":"請求"}'
{"hits":[
  {"id":200004,"title":"9月分の請求書を作成して送付する",...},
  {"id":200005,"title":"請求データのバックアップを取る",...}
],"query":"請求","estimatedTotalHits":2}

消したはずのデータが、検索結果に出ています。

これは「表示が古い」で済む話ではありません。 退会した利用者、削除した投稿、取り下げた社内文書。 これらが検索窓から出てくるなら、それは削除できていないのと同じです。 キャッシュの回で「一番難しいのは消すこと」と書いたのと、まったく同じ問題が再発しています。

追いつかせます。

bash
$ curl -s -X DELETE "http://127.0.0.1:7700/indexes/tasks/documents/200005" \
    -H "Authorization: Bearer $MEILI_MASTER_KEY"
{"taskUid":41,"indexUid":"tasks","status":"enqueued","type":"documentDeletion","enqueuedAt":"2026-09-09T02:14:33.101Z"}

これも非同期です。 enqueued は「受け付けた」だけです。

どう同期するか — 3つの方式

やり方は3つしかありません。全部に欠点があります。

方式やること欠点
① 更新のたびに送るINSERT/UPDATE/DELETE の直後に Meilisearch を叩く検索エンジンが落ちていると、送信が失敗する。 漏れが積み上がる
② キューに積む更新イベントをメッセージキューに入れ、別プロセスが送る仕組みが1つ増える。 順序の管理が要る
③ 定期的に全部入れ直すsync-search.mjs を1日1回まわすその間ずっとずれている。 件数が増えると流し直しが重い

実務では、②と③を両方やります。

②で普段は追いつかせ、③で「ずれの上限」を切る。 ②だけだと、1回漏れたら永遠に直りません。 ③があると、どんなに漏れても次の実行で必ず揃います。 ③は「保険」ではなく「前提」です。

①をやるときの鉄則を1つだけ。

// ⚠️ こう書かない
await meiliUpdate(task);      // ← ここで落ちると
res.json({ ok: true });        //   タスクの保存まで失敗扱いになる

// こう書く
res.json({ ok: true });        // 本来の処理は先に返す
meiliUpdate(task).catch((e) => {
  console.error('検索インデックスの更新に失敗', e);
  // ここで諦めてよい。③の入れ直しが拾う
});

検索の更新に失敗しても、タスクの保存は成功させます。 検索は「無くても業務が回るもの」です。それを理由に本体を落とさない。 これは障害対応の回でやった「どこまで壊れていいかを先に決める」の実践です。

ずれを検知する

「ずれていないこと」は、祈るものではなく測るものです。

bash
$ mysql -u appuser -p appdb -N -e "SELECT COUNT(*) FROM tasks"
$ curl -s -H "Authorization: Bearer $MEILI_MASTER_KEY" \
    http://127.0.0.1:7700/indexes/tasks/stats

この2つの数字を毎日比べるだけで、大半の事故は先に気づけます。 監視の回で作った仕組みに、この差分を1つのメトリクスとして出すのが正しい形です。

入れ直しは、止めずにやる

③の「全部入れ直す」を素直にやると、入れ直している最中は検索結果が欠けます。 Meilisearch には、そのための仕掛けがあります。

  1. tasks_new という別のインデックスに、全部入れる
  2. 入り終わったら、tasks と入れ替える
bash
$ curl -s -X POST 'http://127.0.0.1:7700/swap-indexes' \
    -H "Authorization: Bearer $MEILI_MASTER_KEY" \
    -H 'Content-Type: application/json' \
    -d '[{"indexes":["tasks","tasks_new"]}]'

アプリは tasks を見たまま、中身だけが一瞬で入れ替わります。 GitOps の回やCI/CDの回でやった「新しいほうを用意してから切り替える」と、同じ考え方です。

⑦ Elasticsearch との使い分け

④で先送りにした比較をします。

MySQL FULLTEXTMeilisearchElasticsearch
メモリ追加ゼロ(MySQL の中)数百MB最低1〜2GB(JVMのヒープ設定が要る)
立てる手間ALTER TABLE 1行Compose 10行JVM設定・vm.max_map_count・複数ノード
日本語ngram のみ(誤ヒットが出る)単語に切る(既定で日本語対応)プラグイン導入で強い(kuromoji 等)
タイポ許容無し既定で有る設定すれば有る
集計ほぼ無しほぼ無し(絞り込みの件数程度)強い。ここが本命
ログ分析無理無理できる(ELK / Kibana)
冗長化MySQL 次第単体前提(クラスタ機能は限定的)クラスタが前提の設計
件数の目安〜数十万〜数百万億でも

選び方の軸は、機能の数ではありません。

「その重さを、いま払えるか」です。 ログ集約の回で Loki を選んだのと、まったく同じ判断です。 あのときも「Elasticsearch が悪いのではなく、1台のVMに載せるには重い」と書きました。同じ結論に、別の道から着きました。

Elasticsearch が要るのは、具体的にはこういうときです。

  • 「先月の問い合わせを、カテゴリ別・日別に集計して、上位10件を出す」——検索ではなく集計が主目的
  • ログを検索したい(kubectl logs を横断して単語で引く。Loki が苦手だと書いたやつです)
  • 1台に載り切らないので、複数台に分けて持つ必要がある
  • 並び順のスコアを、業務ルールで細かく調整したい

逆に、こういうときは Meilisearch で足ります。

  • 「検索窓を1つ付けたい」だけ
  • 打ちながら候補が出てほしい
  • 数百万件までで、当面それ以上に増える見込みが無い

迷ったときの順番は、常にこうです。 ① MySQL の FULLTEXT で足りないか → ② 足りないなら Meilisearch → ③ 集計かログか億件なら Elasticsearch。 ③から始めない。 一度入れたものは、面倒を見続けることになります。

なお、PostgreSQL を使うなら選択肢がもう1つあります。 PostgreSQL の回で「違いは1割」と書きましたが、全文検索はその1割のほうです。PostgreSQL は tsvector という全文検索の仕組みを本体に持っていて、英語のような空白で区切る言語なら MySQL の FULLTEXT よりだいぶ強いです。ただし標準のままでは日本語の単語を切り出せないので、日本語の検索窓に使うなら pg_bigm や PGroonga といった拡張を足すのが前提です。「データベースの中で完結させたい」なら、PostgreSQL は有力な選択肢になります。

⑧ やりすぎない — LIKE で十分な場面

最後に、この記事の全部をひっくり返す話をします。

あなたのテーブルが 3,000 件なら、こうです。

SELECT id, title FROM tasks WHERE title LIKE '%請求%';
2 rows in set (0.00 sec)

索引が効いていなくても、3,000 行を読むのは一瞬です。 「索引が効かない」は「遅い」と同義ではありません。

件数の目安現実的な LIKE '%…%' の速さ判断
〜1万件数ミリ秒LIKE で十分。何もしない
〜10万件数十ミリ秒たいてい LIKE で足りる。測ってから決める
10万〜100万件0.1〜1秒FULLTEXT を検討する
100万件〜数秒〜専用のエンジンを検討する

この表を暗記しないでください。 1行の長さ・列の数・同時アクセス数で簡単に変わります。 パフォーマンス改善の回の結論と同じです。測ってから決める。

そして、Meilisearch を入れると増える仕事は、これだけあります。

  • コンテナが1つ増える(監視対象・更新対象・バックアップ対象が増える)
  • 同期のコードを書き、その失敗を監視する
  • マスターキーを管理する
  • MySQL を復元したとき、検索側も入れ直す手順が要る

検索窓を1つ速くするために、運用対象が1つ増えます。 その交換に見合うかどうかを、件数を測ってから決めてください。 入れないという判断は、負けではありません。

つまずいたところ

遠回りしたところを、そのまま書きます。

/tasks と /indexes/tasks を取り違えて30分

投入したあと、状況を見ようとしてこう打ちました。

bash
$ curl -s -H "Authorization: Bearer $MEILI_MASTER_KEY" http://127.0.0.1:7700/tasks

知らないデータがずらずら出てきて、「別のアプリのデータが混ざった」と本気で焦りました。

違いました。

URL中身
/tasksMeilisearch 自身の処理待ち行列(投入・削除・設定変更の履歴)
/indexes/tasks私が作った tasks というインデックス

インデックスの名前を tasks にしたせいで、完全に重なりました。 名前を app_tasks などにしておけば踏まなかった罠です。教訓は「汎用的な名前を、汎用的なAPIを持つ製品の中で使わない」です。

WITH PARSER ngram を書き忘れて、日本語が1件も出なかった

最初、素直にこう書きました。

ALTER TABLE appdb.tasks ADD FULLTEXT INDEX ft_title (title);

エラーは出ません。索引もできます。そして MATCH ... AGAINST('請求') が 0 件を返します。

既定のパーサは空白で切るので、「9月分の請求書を作成して送付する」がまるごと1語になっていました。「請求」という語は、索引の中に存在しません。

一番怖いのは、エラーが出ないことです。 「検索しても何も出ない」という、原因の想像がつかない症状だけが残ります。 確認方法: SHOW CREATE TABLE appdb.tasks; を打って、FULLTEXT KEY ... /*!50100 WITH PARSER ngram */ が付いているか見ます。

docker compose down でデータが消えた(のは、消したから)

compose.yml を直したくて、こう打ちました。

bash
$ docker compose down -v

20万件の索引が、きれいに消えました。 -v は「名前付きボリュームも消す」です。Compose の回で読んだはずなのに、手が勝手に付けていました。

幸い、③の「全部入れ直す」があったので、node sync-search.mjs を1回流すだけで戻りました。

これが「検索用のデータはコピーである」ことの実利です。元が MySQL にあるので、消えても怖くない。 逆に言うと、消えて困る状態になっていたら、それは設計が間違っています。

MEILI_ENV: production で起動しなくなった

最初、キーを短くしていました。

Error: The master key must be at least 16 bytes long

「起動しない」は不便に見えますが、正しい挙動です。 development のままなら、キー無しで動いてしまい、そのまま忘れます。 不便さで守っている、という設計です。

後片づけ

Meilisearch は動かしっぱなしにするとメモリを食い続けます。 次の回に進む前に落としてください。

bash
$ cd ~/search
$ docker compose down -v

-v を今回は意図的に付けます。 索引の実体(400MB前後)も消すためです。MySQL に元があるので、いつでも作り直せます。

イメージも消します。

bash
$ docker image rm getmeili/meilisearch:v1.11
$ docker system df

MySQL 側の全文索引も外します。

bash
$ sudo mysql
DROP INDEX ft_title ON appdb.tasks;

ダミーの20万件も消します。

DELETE FROM appdb.tasks WHERE title LIKE 'ダミー%';
SELECT COUNT(*) FROM appdb.tasks;

メモリが戻ったか確認します。

bash
$ free -h
$ docker ps

~/server/ と appdb は残してください。 続きの回で使います。 ~/search/.env も消しておくと確実です(rm ~/search/.env)。使わないキーを置きっぱなしにしません。

まとめ

  • LIKE '%文字%' は、索引を張っても使われない。 EXPLAIN の possible_keys: NULL がその証拠
  • key に索引名が出ていても、type: index はフルスキャンの一種。 「使っている」と誤読しない
  • 前方一致('請求%')なら索引は効く。 差は先頭の % があるかどうかだけ
  • これは MySQL の欠点ではない。 並べる仕組みは、真ん中の文字で絞れない
  • 全文検索の中身は「語 → 文書番号」の表。 だから先に語へ切っておく必要がある
  • 日本語には単語の区切りが無い。 ここが本質的な難しさで、英語の解説がそのまま通用しない理由
  • ngram は取りこぼさない代わりに誤ヒットする(「京都」で「東京都庁」)。形態素解析はその逆
  • MySQL の FULLTEXT + ngram でかなり行ける。 20万件が 0.11秒 → 0.00秒 になった
  • WITH PARSER ngram を書き忘れると、エラーも出ずに0件になる
  • ngram_token_size が2なので、1文字では何も出ない。検索窓の実装で必ず踏む
  • 専用のエンジンが要るのは、タイポ許容・入力補完・重み調整・書き込みの多い表から
  • ports: は 127.0.0.1: を付ける。 ufw では止まらない(Docker が iptables を直接書く)
  • MEILI_ENV: production にして、キー無しでは起動できなくする。 設定ではなく仕組みで守る
  • Meilisearch への投入は非同期。 POST の成功は「行列に並べた」であって「入った」ではない
  • 検索用のデータはコピー。 元のデータベースが正。全部消しても作り直せる状態を維持する
  • 同期は必ずずれる。 消したはずのデータが検索に出るのは、削除できていないのと同じ
  • 同期は「更新時に送る」+「定期的に全部入れ直す」の併用。 入れ直しは保険ではなく前提
  • 検索の更新に失敗しても、本体の保存は成功させる。 検索は無くても業務が回る側
  • ずれは測る。 MySQL の COUNT(*) と /stats の numberOfDocuments を突き合わせる
  • 入れ直しは別インデックスに作って入れ替える(swap-indexes)。止めずに済む
  • 日本語ではタイポ許容が効きにくい。 短い語は対象外。効くのは英数字の固有名詞
  • Elasticsearch は集計とログ分析が本命。 重さを払えるかで選ぶ(Loki を選んだときと同じ軸)
  • 数千件なら LIKE で十分。 索引が効かない=遅い、ではない
  • 入れると運用対象が1つ増える。 入れない判断は負けではない

参考リンク


次に読む

この記事は 【第5段階】バックエンドとデータベース の 26 / 29 本目です。

記事
← 前の記事3つ目で言語の地図ができる — Pythonを触る
→ 次の記事実行ファイル1つで動く — Goで同じAPIを書く

上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。

全体の順路は はじめに にあります。

roadmap.sh のどこをやっているか

Backend›Search Engines
  • 全文検索(LIKE では届かない検索を、専用の道具でやる)
  • Elasticsearch / Meilisearch の使い分け

この記事は、上の項目を実際に手を動かして確かめる回です。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

コメント

コメントする

目次