システムで特定のユーザーごとにユーザー メッセージを検索できるようにしたいと考えています。 次のテーブルがあるとします
リーリーユーザー 1 からの「foo」という単語を含むすべてのメッセージを検索したい場合、ここでどのインデックスを使用すればよいでしょうか。
特定のユーザー メッセージのみをフィルターし、特定の単語を完全にスキャンします。
これは、すべてのユーザーのすべてのメッセージを検索し、ID でフィルター処理しますが、ユーザーの量が多い場合は非効率的と思われます。
の複合
ID でメッセージをフィルターし、インデックス内の残りの行に対してテキスト検索を実行します。
それぞれのメッセージが約 100 件ある場合、完全な反復ではリソースが多すぎるのではありませんか?
メッセージにユーザー名を含めて BOOLEAN 全文検索モードを使用することもできますが、インデックス
user_idを使用するよりも遅くなると思います。
P粉4211197782023-11-08 15:18:58
全文インデックスを message に追加し、通常のインデックスを user_id に追加し、次のクエリを使用する必要があります:
おっしゃるとおりです。オプション 3 は実行できません。ただし、1 か 2 のどちらかを選択しようとするのではなく、MySQL に作業を任せてください。 MySQL は 2 つのインデックスのうち 1 つだけを使用し、リニア スキャンを実行して 2 番目のフィルタリングを完了しますが、各インデックスの有効性を推定して最適なインデックスを選択します。
注: これは、2 つのインデックスのオーバーヘッド (遅い挿入/更新/削除) に余裕がある場合にのみ実行してください。また、ユーザーごとに少数のメッセージしか存在しないことが わかっている場合は、単純なインデックスを使用し、アプリケーション層で正規表現などを実行することが合理的である可能性があります。
P粉0769873862023-11-08 12:16:14
@Alden Quimby の答えはそれ自体は正しいですが、MySQL は最良のインデックスを選択するために try のみを行うだけであり、その決定を下す能力が制限されているため、この話には続きがあります。フルテキスト インデックスがオプティマイザと対話する方法。
実際に起こったことは次のとおりです:
指定された user_id がテーブル内の 0 または 1 つの一致する行に存在する場合、オプティマイザーはこれを認識し、このクエリのインデックスとして user_id を選択します。すぐに実行してください。
それ以外の場合、オプティマイザはフルテキスト インデックスを選択し、フルテキスト インデックスに一致する各行をフィルタリングして、WHERE 句に一致する user_id を含まない行を削除します。そんなに早くない。
つまり、これは実際には「最善の」パスではありません。これはむしろ全文に似ており、テーブル内に関心のあるものがほとんどないことがわかっている場合には全文検索の実行を回避する優れた最適化が施されています。
これが発生する理由は、フルテキスト インデックスがオプティマイザに意味のある統計を提供しないためです。 「はい、クエリでは 1 行のチェックだけで済むと思います」とだけ表示されます。もちろん、これはオプティマイザを大いに満足させるため、インデックスに整数が含まれていない限り、フルテキスト インデックスが最低コストで入札されます。値が多すぎる 比較的低いか、それより低い。
とはいえ、これを最初に試さないというわけではありません。
フルテキスト クエリ IN BOOLEAN MODE に最適な別のオプションがあります。それは、CONCAT('user_id_',user_id) などを入力できる別の列を作成してから宣言することです。 2 列の全文インデックス。
次に、クエリにすべてを指定します。
リーリーこれで、フルテキスト インデックスは、両方の列を組み合わせたフルテキスト インデックスに子猫、子犬、および "user_id_500" が表示される行のみを照合することになりますが、ここでも整数フィルターを使用して、次のことを保証する必要があります。 「user_id_500」というメッセージがランダムに表示されますが、最終的な結果は制限されています。