MySQL とインデックスの関係は存在しません

一部のビジネス シナリオでは、返されたデータが特定のコレクションに存在しないことを確認するために NOT EXISTS ステートメントが使用されます。一部の同僚は、NOT ステートメントのパフォーマンスが低下していることに気づくでしょう。一部のシナリオでの EXISTS は貧弱であり、オンラインでも「NOT EXISTS はインデックス を使用しない」という噂があります。NOT EXISTS ステートメントを最適化するにはどうすればよいですか?

今日の最適化された SQL を例に挙げます。最適化前の SQL は次のとおりです:

SELECT count(1) FROM t_monitor m WHERE NOT exists (  SELECT 1   FROM t_alarm_realtime AS a   WHERE a.resource_id=m.resource_id   AND a.resource_type=m.resource_type   AND a.monitor_name=m.monitor_name)

LEFT JOIN メソッドを使用して最適化します。最適化後の SQL は次のとおりです:

SELECT count(1) FROM t_monitor m LEFT JOIN t_alarm_realtime AS a    ON a.resource_id=m.resource_id   AND a.resource_type=m.resource_type   AND a.monitor_name=m.monitor_name WHERE a.resource_id is NULL

最適化効果:

最適化前の実行時間は 29 秒以上、最適化後は 1.2 秒となり、最適化は 25 倍改善されました。

NOT EXISTS 本当にインデックスされていませんか?

2 つの SQL の実行プランを表示します。

NOT EXIST メソッドを使用した実行計画:

##LEFT JOIN メソッドを使用した実行計画:

#実行プランから見ると、どちらのテーブルもインデックス

を使用しています。違いは、NOT EXISTS が "DEPENDENT SUBQUERY" メソッドを使用するのに対し、LEFT JOIN であることです。通常のテーブル関連付けを使用します。

お勧めの読書:

インデックスによってクエリ速度が向上するのはなぜですか? MySQL が提供する

Profiling

メソッドを使用して、2 つのメソッドの実行プロセスを表示します。 NOT EXIST モードを使用した実行プロセス:

LEFT JOIN メソッドを使用した実行プロセス:

実行プロセスのうち、LEFT JOIN メソッドの主な消費は送信です。データ 1 つの項目 (1.2 秒) ですが、NOT EXISTS メソッドは主に実行データと送信データの 2 つの項目を消費します。これは、プロファイリングが 100 行のレコードのみを保存するという事実によって制限されます。

プロファイリングからは、「実行とデータ送信」の組み合わせが 47 通りしか確認できません (各組み合わせは約 50us) 実行計画から、外側の t_monitor のデータ量が 578436 行であることがわかります。情報が不正確な場合、NOT EXISTS メソッドを使用すると、「実行中とデータ送信」の組み合わせが 578436 通り生成され、総消費時間は =50μs*578436=28921800us=28.92s になります。

上記の実行プロセスから推測できます:

NOT EXISTS メソッドの実行パフォーマンスは、メソッドの実行回数に大きく依存します。 NOT EXISTS サブクエリ、つまり、外側のクエリ結果セット内のデータの量。

1. 外部クエリ結果セットのデータ量 N が小さい場合、実行パフォーマンスが向上します。N=10 の場合、実行時間は50μs*10= 500us=0.005s に追加消費を加えた場合、実行結果は 0.01 秒または 10 ミリ秒の範囲内になる可能性があり、この応答時間はほとんどのアプリケーションで許容できるはずです。

2. #外側の Chengxun 結果セットのデータ量 N が大きい場合、または数千万データになる場合、NOT EXISTS のクエリ パフォーマンスは非常に悪くなります。サーバー IO と CPU リソースを大量に消費し、他のビジネスの通常の運用に影響を与える可能性もあります。

上記の問題に加えて、最適化プロセス中に、同じデータを格納する必要がある resource_id 列が 2 つのテーブルで別々に定義されていることが判明しました。一方のテーブルはVARCHAR で、他のテーブルは BIGINT、外部結果セットのフィールド タイプが NOT EXIST ワード テーブルのフィールド タイプと異なるため、インデックスを NOT EXISTS サブクエリで使用できなくなります。その結果、サブクエリのパフォーマンスが低下し、最終的にはクエリ全体の実行パフォーマンスに影響を与えます。

京東モールでも同様のケースが多数発生しています。一部のテーブルでは VARCHAR を使用して注文番号を保存し、他のテーブルでは BIGINT を使用して注文番号を保存しています。2 つのテーブルを管理する場合、パフォーマンスは研究開発の同僚がこれを警告として受け取ってくれることを願っています。公開アカウント Java Technology Stack をフォローし、m36 に返信して MySQL R&D 軍事規則のコピーを入手してください。

関連する学習に関する推奨事項: mysql ビデオ チュートリアル

以上がMySQL とインデックスの関係は存在しませんの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。