MySQL はサブクエリをどのように最適化しますか?

実際の開発では、指定されたフィールドに一致する値のセットに相当する in サブクエリをよく使用します。これにより、フィルタリングとクエリがより便利になります。ただし、このサブクエリ方法は、データ量が大きい場合にパフォーマンスに大きな問題を引き起こします。この記事では、MySQL が in サブクエリを最適化する方法を紹介します。

1. subquery での使用を避ける

実際のプロジェクトでは、このような書き方をよく見かけます:

SELECT *
FROM table
WHERE col1 IN (SELECT col1 FROM table2 WHERE condition);

このステートメントは、 subquery の条件に従って、最も単純です。 table2では、複数行のcol1の値を取り出し、テーブル内の値と照合し、一致した行を返します。ただし、MySQL が内部的に in サブクエリを実行する方法では、サブクエリの結果セットがメモリ (またはディスク) にキャッシュされるため、この方法で記述するとパフォーマンスのボトルネックが発生します。その後、in 判定が実行されるたびに、メモリ (またはディスク)ディスク) がキャッシュされます。)、これにより大量の I/O 操作が発生し、サブクエリの結果セットが大きい場合は、大量のメモリも占有します。

したがって、実際の開発ではサブクエリでの使用を避け、代わりに join を使用するようにしてください。

2. join を使用して in サブクエリを置き換えます

Use join を使用して in サブクエリを置き換えます。サブクエリの記述とサブクエリの記述方法に違いはありません。変換するだけです。オリジナルの in サブクエリを結合して SQL 構文を最適化するだけで、実行効率は in サブクエリよりもはるかに高くなります。以下に示すように、col1 を検索し、table1 のcol1 と結合します。

SELECT table.*
FROM table
JOIN table2 ON table.col1 = table2.col1
WHERE table2.condition;

in サブクエリと比較して、結合を使用すると、サブクエリの結果セットをテーブルに接続できるため、メモリ (ディスク) が大幅に削減されます。 ) 読み取り操作。

3. サブクエリの代わりに存在を使用します。

サブクエリの代わりに存在を使用することは、実際には結合を使用することになります。 in サブクエリとは異なり、exists サブクエリは結果セットのサイズに関係なく、単純な判定を実行するだけで済みます。以下は、exists サブクエリの構文例です。

SELECT *
FROM table
WHERE EXISTS (SELECT 1 FROM table2 WHERE table.col1 = table2.col1 AND table2.condition);

サブクエリの代わりにexistsを使用すると、効率が大幅に向上し、大量のI/Oとメモリ消費を節約できます。

4. インデックスを使用して in ステートメントを最適化する

インデックスを使用してクエリ中のサブクエリを高速化できれば、クエリの効率も大幅に向上します。 MySQL のインデックスは主キー インデックス、ユニーク インデックス、通常のインデックスの 3 種類に分かれており、適切なインデックスを作成できれば、MySQL によるフル テーブル スキャンの実行を回避し、クエリ効率を向上させることができます。

CREATE INDEX idx_col1 ON table (col1);

col1 値が大きい場合、インデックスを使用するとクエリ効率が大幅に最適化され、サブクエリでの使用によって発生する効率の問題が軽減されます。

5. サブクエリで最適化するには、limit とexistes を使用します。

サブクエリの結果セットが非常に大きい場合は、limit とexistes を使用して、テーブル全体のスキャンを回避しながらページング クエリを実行できます。 、クエリ効率を最適化するという目的を達成するために。

SELECT *
FROM table
WHERE EXISTS (SELECT 1 FROM table2 WHERE table.col1 = table2.col1 AND table2.condition LIMIT 1000, 20);

この SQL ステートメントの機能は、table2 の結果セットを検索し、col1 と table を使用して、exists を実行することです。クエリ結果セットを 20 項目に制限し、1000 行目からクエリを実行します。

6. ステートメントでのメモリ最適化の適切な使用

クエリで使用される in サブクエリ結果の数が多くない場合は、in の代わりに set を使用できます。 set は、後続のクエリ照合のために in サブクエリの結果セットをメモリに保存します。メモリを使用して in ステートメントを最適化すると、パフォーマンスも大幅に向上します。

SET @col1 = (SELECT GROUP_CONCAT(DISTINCT col1) FROM table2 WHERE condition);
SELECT *
FROM table
WHERE FIND_IN_SET(table.col1, @col1);

このステートメントは、最初にデータ照合に select を使用し、次に GROUP_CONCAT を使用して、col1 値リストを文字列に接続し、文字列は @col1 に保存されます。後続のクエリでは、FIND_IN_SET が照合に使用され、メモリ キャッシュがクエリ効率を最適化するために使用されます。

7. 概要

in サブクエリを使用するときは、特にデータ量が多い場合にはテーブル全体のスキャンを必ず避けてください。そうしないと、深刻なパフォーマンスの問題が発生します。結合、存在、インデックスの最適化、リミットの適切な使用、メモリやその他のメソッドの使用により、クエリの効率を向上させ、サブクエリのパフォーマンスを最適化できます。実際のプロジェクトでは、最高のパフォーマンス最適化効果を達成するために、特定の状況に応じて最適なソリューションを選択する必要があります。

以上がMySQL はサブクエリをどのように最適化しますか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。