MySQL フルテキストインデックス、ジョイントインデックス、Like クエリ、JSON クエリのうち、どれが高速ですか?

クエリの背景

約 100,000 件のレコードを含む tmp_test_course テーブルがあり、そこに 1 対多の関係を格納する outline という JSON フィールドがあります (jy1577683381775 などの複数のコードが保存されます)。

これらの 100,000 件のレコードから特定の種類のデータを取得する必要があります。対象データの合計量は次のとおりです: 2931レコード

SELECT COUNT(*) FROM tmp_test_course WHERE `type`=5 AND del=2 AND is_leaf=1

上記のタイプを制限しながら、以下のコード（つまりORクエリ）も含める必要があります。

1577683381775 翻訳
1577683380808
1577683379178 翻訳
1577683378676 翻訳
1577683377617 翻訳
1577683376672 翻訳
1577683375903 翻訳
1578385720787 翻訳
1499916986208 翻訳
1499917112460 翻訳
1499917093400 翻訳
1499917335579 翻訳
1499917334770 翻訳
1499917333339 1499917333339
1499917331557 翻訳
1499917330833
1499917329615 翻訳
1499917328496 翻訳
1576922006950 翻訳
1499916993558 翻訳
1499916992308 翻訳
1499917003454 1499917003454
1499917002952 1499917002952

以下に、アウトラインフィールドをクエリする 4 つの方法と、対応するクエリ時間とスキャンされた行数を示します。

1. クエリをいいね

248ミリ秒かかりました

tmp_test_course から * を選択 
ここで、`type`=5 かつ del=2 かつ is_leaf=1 
そして （
'%jy1577683381775%' のようなアウトライン
または '%jy1577683380808%' のようなアウトライン
または '%jy1577683379178%' のようなアウトライン
または '%jy1577683378676%' のようなアウトライン
または '%jy1577683377617%' のようなアウトライン
または、「%jy1577683376672%」のようなアウトライン
または '%jy1577683375903%' のようなアウトライン
または '%jy1578385720787%' のようなアウトライン
または '%jy1499916986208%' のようなアウトライン
または '%jy1499917112460%' のようなアウトライン
または '%jy1499917093400%' のようなアウトライン
または '%jy1499917335579%' のようなアウトライン
または '%jy1499917334770%' のようなアウトライン
または、「%jy1499917333339%」のようなアウトライン
または '%jy1499917331557%' のようなアウトライン
または '%jy1499917330833%' のようなアウトライン
または '%jy1499917329615%' のようなアウトライン
または '%jy1499917328496%' のようなアウトライン
または '%jy1576922006950%' のようなアウトライン
または '%jy1499916993558%' のようなアウトライン
または '%jy1499916992308%' のようなアウトライン
または '%jy1499917003454%' のようなアウトライン
または '%jy1499917002952%' のようなアウトライン
）

EXPLAIN分析結果は次のとおりです。フルテーブルスキャン

2. JSON関数クエリ

json公式関数

所要時間は 196 ミリ秒で、わずかに速くなりました。

tmp_test_course から * を選択 
ここで、`type`=5 かつ del=2 かつ is_leaf=1
そして 
（
JSON_SEARCH(アウトライン、'one'、'jy1577683381775') は NULL ではないか、
JSON_SEARCH(アウトライン、'one'、'jy1577683380808') は NULL ではないか、
JSON_SEARCH(アウトライン、'one'、'jy1577683379178')はNULLではないか、
JSON_SEARCH(アウトライン、'one'、'jy1577683378676') は NULL ではないか、
JSON_SEARCH(アウトライン、'one'、'jy1577683377617')はNULLではないか、
JSON_SEARCH(アウトライン、'one'、'jy1577683376672')はNULLではないか、
JSON_SEARCH(アウトライン、'one'、'jy1577683375903')はNULLではないか、
JSON_SEARCH(アウトライン、'one'、'jy1578385720787')はNULLではないか、
JSON_SEARCH(アウトライン、'one'、'jy1499916986208')はNULLではないか、
JSON_SEARCH(アウトライン、'one'、'jy1499917112460') は NULL ではないか、
JSON_SEARCH(アウトライン、'one'、'jy1499917093400') は NULL ではないか、
JSON_SEARCH(アウトライン、'one'、'jy1499917335579')はNULLではないか、
JSON_SEARCH(アウトライン、'one'、'jy1499917334770') は NULL ではないか、
JSON_SEARCH(アウトライン、'one'、'jy1499917333339')はNULLではないか、
JSON_SEARCH(アウトライン、'one'、'jy1499917331557')はNULLではないか、
JSON_SEARCH(アウトライン、'one'、'jy1499917330833') は NULL ではないか、
JSON_SEARCH(アウトライン、'one'、'jy1499917329615')はNULLではないか、
JSON_SEARCH(アウトライン、'one'、'jy1499917328496')はNULLではないか、
JSON_SEARCH(アウトライン、'one'、'jy1576922006950') は NULL ではないか、
JSON_SEARCH(アウトライン、'one'、'jy1499916993558')はNULLではないか、
JSON_SEARCH(アウトライン、'one'、'jy1499916992308')はNULLではないか、
JSON_SEARCH(アウトライン、'one'、'jy1499917003454')はNULLではないか、
JSON_SEARCH(アウトライン、'one'、'jy1499917002952') は NULL ではありません  
）

EXPLAIN分析の結果は次のようになります。または、テーブル全体のスキャン

3. 共同インデックスクエリ

次に、テーブルのジョイントインデックスを作成します（当初はtype-del-is_leaf-outlineのインデックスを作成したかったのですが、アウトラインフィールドが長すぎるため、type-del-is_leafのジョイントインデックスのみを追加しました）。

ALTER TABLE tmp_test_course に `type-del-is_leaf` キーを追加します (`type`,`del`,`is_leaf`)

インデックスを追加すると、like および json クエリが大幅に高速化されます。
実行には136ミリ秒かかり、jsonクエリには82.6ミリ秒かかりました。これは、json関数クエリをjson型に使用すると、likeよりも高速であることを示しています。

EXPLAIN 分析結果は次のとおりです。両方のクエリでスキャンされる行数は 2931 行に制限されています。

4. 全文インデックスクエリ

フルテキストインデックスは CHAR、VARCHAR、TEXT のみをサポートしているため、JSON フィールド定義を変更する必要があります。

ALTER TABLE tmp_test_course MODIFY `outline` VARCHAR(1024) NOT NULL DEFAULT '[]'

全文インデックスを追加する

ALTER TABLE tmp_test_course に FULLTEXT INDEX outline (outline) を追加します。

それでは、フルテキストインデックスを使って検索してみましょう

tmp_test_course から * を選択 
ここで、`type`=5 かつ del=2 かつ is_leaf=1
そして 
一致（アウトライン）と（ 'JY157683381775 JY157777683380808 JY15777683378676 JY15777776837617 JY1577737767672 20787 jy1499916986208 jy1499917112460 jy1499917093400 jy149999917335579 jy14999917334770 jy1499917333333399999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999997 49917329615 jy1499917328496 jy1576922006950 jy14999916993558 jy1499916992308 jy1499917003454 jy1499991777002952 '）

所要時間は 11.6 ミリ秒で、速度が大幅に向上しており、フルテキストインデックスの威力がわかります。

EXPLAIN分析の結果は以下の通りで、1行のみがスキャンされたことが分かります。

結論は

以下は4つのケースの実行結果です

全文インデックス: 11.6ms
ジョイントインデックス: 82.6ms (json)、136ms (like)
json 関数クエリ: 196 ミリ秒
いいねクエリ: 248ms

結論: 全文インデックス > 結合インデックス > JSON 関数クエリ > Like クエリ

データ量が多いほど、フルテキストインデックスの速度は速くなります。10 万個のデータポイントを持つテーブルの場合、クエリ速度は直接クエリよりも約 20 倍速くなります。テーブルに数百万または数千万のデータポイントがある場合は、改善はさらに大きくなります。したがって、可能な場合はフルテキストインデックスを使用してください。

これで、MySQL フルテキストインデックス、複合インデックス、Like クエリ、JSON クエリのどれが速いかという記事は終わりです。MySQL フルテキストインデックス、複合インデックス、Like クエリ、JSON クエリの詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません: