mysqlのセットフィールドタイプに関するファジークエリの問題

400,000 項目のテスト データ テーブルがあります
flag set('r', 'l', 'c', 'p')

SELECT a. * , b.typedir
FROM mzrui_archives a
LEFT JOIN mzrui_kind b ON a.kid = b.uid
WHERE a.flag LIKE '%p%'
AND a.kid
IN ( 3, 17, 18 )
ORDER BY a.uid
LIMIT 0 , 15

このステートメントクエリには 2.5 が必要です数秒、「いいね！」を削除した後のクエリは非常に高速です。最適化する方法がわかりません。アドバイスをお願いします。

uid は主キー
キー kid(kid,flag) インデックス

ディスカッションへの返信 (解決策)

セットなので、なぜクエリをいいねする必要があるのでしょうか? Find_in_set('p',a.flag)

セットなので、なぜクエリを like する必要があるのでしょうか? find_in_set('p',a.flag)

find_in_set の効率は同じですが、find_in_set には複数の条件 ('c,p',a.flag) を含めることはできません。レコードを見つけてください

インデックス 何か間違っていますか?

セットなので、なぜクエリをいいねする必要があるのですか? find_in_set('p',a.flag)

find_in_set の効率は同じですが、find_in_set には複数の条件 ('c,p',a.flag) を含めることはできません。レコードを見つけてください

You Yes

find_in_set('p',a.flag) and find_in_set('c',a.flag)

セットなので、なぜクエリにいいねする必要があるのですか？ find_in_set('p',a.flag)

find_in_set の効率は同じですが、find_in_set には複数の条件 ('c,p',a.flag) を含めることはできません。レコードを見つけてください

あなた はい

find_in_set('p',a.flag) and find_in_set('c',a.flag)

はい、このようにすることもできますが、今私が心配しているのは効率の問題です

find_in_setテーブル全体をスキャンすることになりますが、効率は実際には高くありません。
テーブル構造の変更を検討してください。
セットの内容を保存する別の中間テーブルを作成します。接続クエリを実行し、同時にフィールドにインデックスを作成するたびに、効率が若干向上するはずです。

4 つのフラグ ('r'、'l'、'c'、'p') しかない場合は、フラグ ビットとして 1 2 4 8 を使用し、1|2 = 3 の方法を使用することをお勧めします。フラグを識別するには、クエリを実行します。 where フラグ & 2 を使用すると、はるかに高速になるはずです

フラグが 4 つしかない場合 ('r'、'l'、'c'、'p')、1 つを使用する方がよいでしょう。 2 4 8 をフラグ ビットとして使用し、 1|2 = 3 のメソッドを使用してフラグを識別します。クエリを実行するときは、 where flag & 2 のメソッドを使用できます。これははるかに高速です

このようなファジー クエリは作成できません。たとえば、1 つのレコードに 1,2 があり、4 の値は 7 として保存する必要があります
2 を含むレコードをクエリしたいのですが、フラグ & 2 はクエリできませんよね? ? ? ?

4 つのフラグ ('r'、'l'、'c'、'p') しかない場合は、フラグ ビットとして 1 2 4 8 を使用し、1|2 = 3 の方法を使用することをお勧めします。フラグを識別するには、クエリを実行します。 where flag & 2 を使用すると、はるかに高速になるはずです

where flag & 2 が '%l%' と同じ効果を持つことをテストしましたが、効率は向上しません

set タイプの場合フィールドでは、find_in_set が使用されます。これは単なるビット操作です
しかし、「%l%」のようなものは絶対にお勧めできません。「%l,r%」や「%l,p%」のようなものでは、何も見つかりません。
もちろん、 like と find_in_set はどちらもテーブル全体を走査する必要があります。そうしないと、どのレコードが一致するかわかりません
セット型は長整数として保存され、インデックスを追加する方が高速になる可能性があります

セット型フィールドの場合は find_in_setビット演算を使用します
ただし、「%l%」のようなものは絶対にお勧めできません。「%l,r%」や「%l,p%」のようなものでは、何も見つかりません。
もちろん、like と find_in_set はテーブル全体を走査する必要があります。そうしないと、どのレコードが一致するかわかりません
セットの型は長整数として保存され、インデックスを追加する方が高速になる可能性があります

はい、その通りです。このキー kid(kid,flag) 複合インデックスを追加しました。これが正しいインデックスかどうかはわかりません。現在のクエリ時間は 0.0 秒以内に最適化したいと考えています。他に方法がない場合は、テーブル構造を変更するしかありません

set 型フィールドの場合、find_in_set はビット演算を使用します
ただし、「%l%」のようなものは絶対にお勧めできません。「%l,r%」や「%l,p%」のようなものであれば、何も起こりません。見つからないですか？
もちろん、like と find_in_set はテーブル全体を走査する必要があります。そうしないと、どのレコードが一致するかわかりません
セットの型は長整数として保存され、インデックスを追加する方が高速になる可能性があります

はい、その通りです。このキー kid(kid,flag) 複合インデックスを追加しました。これが正しいインデックスかどうかはわかりません。現在のクエリ時間は 0.0 秒以内に最適化したいと考えています。他に方法がない場合は、「%xxx」クエリのようにテーブル構造を変更するしかありません。インデックスは基本的に B ツリーまたは B+ ツリーであるため、それらは使用されません

Like と find_in_set はクエリ時にテーブル全体を走査し、400,000 を超えるデータの場合は 2.5 秒かかる可能性がありますが、将来的には使用されません。データが数百万に達するとさらに遅くなり、どちらもインデックスを使用しないため、効率を向上させたい場合は、ビジネス ニーズに基づいて SQL を改善できるかどうかを確認する必要があります。

EXPLAIN を実行して mysql の提案を確認することもできます

id select_type table type possible_keys key key_len ref rows 追加

1 SIMPLE a インデックス kid PRIMARY 4 NULL 15 where の使用

1 SIMPLE b eq_ref PRIMARY 3 mzrui.a.kid 1


Explain を使用すると次のようになります。最適化の提案はありますか?