はじめに
本記事では以下のversionのMySQLでの動作を確認しています。
MySQL: 5.7.12
MySQLにはN-gramパーサーを利用した全文検索のための「FULLTEXT インデックス」というものが存在します。
N-gramなのでそこまで精度の高いものではないのですが既にMySQLを利用していてわざわざElasticsearch導入するまでもない、かつLIKEだとちょっとパフォーマンスが気になるというちょっとした検索機能を実装するには十分有用な機能です。
なのですが、特定の条件でFULLTEXT インデックスを使用した検索が正常に行われない事象が発生したので解決策を含めて備忘録として残しておきます。
何が起きたか
問題が起きたSQLは以下の通り
SELECT hoge.*
FROM hoge
WHERE
hoge.type = ?
AND hoge.id IN (
SELECT hoge.id
FROM hoge
WHERE MATCH (caption) AGAINST (? IN BOOLEAN MODE)
OR MATCH (description) AGAINST (? IN BOOLEAN MODE)
)
/***以下はfilterの条件により可変で追加される***/
AND hoge.id IN (
SELECT fuga.hoge_id
FROM fuga
WHERE fuga.user_id = ?
)
AND hoge.id IN (
SELECT piyo.hoge_id
FROM piyo
WHERE piyo.type = ?
)
WHERE条件は可変で、検索対象tableのidをIN句で絞り込むサブクエリが幾つか存在し
サブクエリの1つでFULLTEXT インデックスを使用した全文検索によるkeywordでの絞り込みが行われており
filterの条件によって更に絞り込みの為のサブクエリが増えるような実装になっています。
本来期待する動作としては
- 全文検索のみの場合はkeywordで全文検索した結果が取得される
- 全文検索 + filter条件が指定された場合はkeywordで全文検索した結果から更にfilter条件で絞り込みが行われる
となるはずですが
- 全文検索のみの場合は正常に検索できる
- 全文検索 + filter条件を付与すると全く関係ないレコードが取得される or 0件になる
複数ある環境の中でも特定の環境でのみ上記のような挙動をしており
最初はロジックを疑ったのですが、調査の結果MySQLに問い合わせた結果自体がおかしいという結論に至りました。
再現条件
全文検索のID絞り込みサブクエリのみ
このパターンはOK
SELECT hoge.*
FROM hoge
WHERE hoge.id IN (
SELECT hoge.id
FROM hoge
WHERE MATCH (caption) AGAINST (? IN BOOLEAN MODE)
OR MATCH (description) AGAINST (? IN BOOLEAN MODE)
)
全文検索のID絞り込みサブクエリ＋同テーブルのcolumnの絞り込み
このパターンもOK
SELECT hoge.*
FROM hoge
WHERE
hoge.type = ?
AND hoge.id IN (
SELECT hoge.id
FROM hoge
WHERE MATCH (caption) AGAINST (? IN BOOLEAN MODE)
OR MATCH (description) AGAINST (? IN BOOLEAN MODE)
)
全文検索のID絞り込みサブクエリ＋別のサブクエリでのIDの絞り込み
このパターンだと本来結果が取得できるはずのデータでも結果が取得出来ないのでNG
SELECT hoge.*
FROM hoge
WHERE hoge.id IN (
SELECT hoge.id
FROM hoge
WHERE MATCH (caption) AGAINST (? IN BOOLEAN MODE)
OR MATCH (description) AGAINST (? IN BOOLEAN MODE)
)
AND hoge.id IN (
SELECT fuga.hoge_id
FROM fuga
WHERE fuga.user_id = ?
)
どうやらIDを絞り込む条件が複数存在すると上手く動作しない事があるようです。
INDEXが上手く構築されていない可能性も考慮して OPTIMIZE TABLE を試してみましたが結果は変わらず。
取得できる環境と取得できない環境とでNGパターンのSQLを流してみたのですが EXPLAIN は全く同じでした。
対策
原因はいまいち分かっていないのですが
再現条件としては WHERE TABLE.id IN () AND TABLE.id IN () のように
IDでの絞り込みが複数出現する際に発生するようですので
全文検索のためのSQLとfilterによる絞り込みのためのSQLを分けました。
- 全文検索 SQL
SELECT DISTINCT hoge.id
FROM hoge
WHERE hoge.id IN (
SELECT hoge.id
FROM hoge
WHERE MATCH (caption) AGAINST (? IN BOOLEAN MODE)
OR MATCH (description) AGAINST (? IN BOOLEAN MODE)
)
- filter SQL
SELECT hoge.*
FROM hoge
WHERE hoge.id IN (
/* 全文検索 SQLの結果のID */
)
AND hoge.id IN (
SELECT fuga.hoge_id
FROM fuga
WHERE fuga.user_id = ?
)
全文検索の結果が大量に取れた場合など考慮しなければいけないことが増えたのですが、上記の対策で現状上手く動作しているようです。
原因についてはもう少し調べてみます。