MySQL での IN データボリュームの使用の最適化された記録

MySQL のバージョン番号は 5.7.28 です。テーブル A には 390 万件のレコードがあり、InnoDB エンジンを使用しています。varchar 型フィールド mac は、B ツリーを使用してインデックス化されています。テーブル B には 5000 件以上のレコードしかありません。

次のように記述された SQL 命令があります。

SELECT * FROM A WHERE mac IN("aa:aa:aa:aa:aa:aa","bb:bb:bb:bb:bb:bb:b",...900 を超えるエントリは省略)

クエリを通じて結果を取得するのに 294.428 秒かかりました。はい、約5分です。

EXPLAIN を使用して分析します。

アクセス タイプは範囲で、インデックスがヒットしましたが、行数は 587776 行しかありません。クエリに時間がかかるのはなぜですか?

mac のインデックス方法は B-tree を使用します。これを HASH と比較して簡単にまとめると、B-tree インデックスは =、>、>=、<、<= と計算間に使用できますが、HASH は等価演算しか実行できず、範囲検索は実行できません。この場合、IN は同等の操作となり、両方のインデックス作成方法を適用できます。この場合、mac のインデックス方式を HASH に変更すると、同じクエリに時間がかかります。

インデックス メソッドを調整してもステートメントのクエリ パフォーマンスを大幅に向上させることはできないため、ステートメント自体からのみ処理できます。実際、洞察力のある人なら誰でも一目見て SELECT * が非常にパフォーマンスを消費することがわかります。そのため、ビジネスに必要なフィールドのみをクエリし、ステートメントを次のように調整します。

SELECT id,mileage FROM A WHERE mac IN("aa:aa:aa:aa:aa:aa","bb:bb:bb:bb:bb:bb:b",...900 を超えるエントリは省略)

時間の消費量に大きな増加はありません。

IN メソッドは最適化が非常に難しいため、LEFT JOIN の使用をあきらめてもよいでしょうか?声明は次のように調整されます。

a.id、a.mileage を A から選択し、a LEFT JOIN B b ON b.mac = a.mac WHERE b.create_time >= '2020-01-01' とします。

5 分以上かかる場合は諦めてください。

条件の数が少ない場合、EXISTS と IN の効果に明らかな違いがないことがわかります。しかし、条件が多数ある場合は、EXISTS よりも IN の方が効率的です。EXISTS を試してみましょう。

SELECT id,mileage FROM A a WHERE EXISTS(SELECT mac FROM B WHERE create_time >= '2020-01-01' AND mac = a.mac)

これも5分以上かかります。確かにEXISTSよりもINの方が効率的なので諦めました。

したがって、最終的な結論としては、IN の後に大量のデータを含む文字列が続く場合は注意する必要がある、ということです。

このプロジェクトでは、mac を一意の識別子として使用し、id を持つ対応するテーブルを作成します。テーブル A では、mac の代わりに mac_id が使用され、クエリ時に IN(1,2,3...) が使用されます。効率が向上します。現在はNoSQLを使うのも一つの方法です。

要約する

大量データ用に IN を使用して MySQL を最適化する方法については、これで終わりです。大量データ用に IN を使用して MySQL を最適化する方法についての詳細は、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。