MySQLグループクエリ最適化方法

MySQL はほとんどの場合、GROUP BY クエリと DISTINCT クエリを同様に処理します。実際、最適化中にこれら 2 つを切り替えることもあります。どちらのタイプのクエリもインデックス作成の恩恵を受けることができ、これは通常、クエリを最適化する最も重要な方法です。

インデックスを使用できない場合、MySQL には、グループ化を実行するために一時テーブルまたはファイルソートを使用するという 2 つの GROUP BY クエリ戦略があります。特定のクエリに対して、どちらのアプローチもより効率的になることはありません。 SQL_BIG_RESULT と SQL_SMALL_RESULT を構成して、オプティマイザーがこれらの方法のいずれかを選択するように指定できます。

通常、クエリテーブルを値ではなく ID でグループ化する方が効率的です。たとえば、次のクエリは非効率的です。

俳優.first_name、俳優.last_name、COUNT(*) を選択
sakila.file_actor から
INNER JOIN sakila.actor USING(actor_id)
GROUP BY actor.first_name、actor.last_name;

次のクエリの方が効率的です。

俳優.first_name、俳優.last_name、COUNT(*) を選択
sakila.file_actor から
INNER JOIN sakila.actor USING(actor_id)
film_actor.actor_id でグループ化します。

グループ化には、film_actor.actor_id を使用するよりも actor.actor_id を使用する方が効率的です。

このクエリは、俳優の名前が actor_id に依存しているため、同じ結果を返すという利点がありますが、結果が異なる場合は同じ結果が返されません。場合によっては、サーバーが SQL_MODE 構成を介して GROUP BY を無効にすることもあります。現時点で取得された値を気にせず、グループ化に使用する列の値が一意である場合は、MIN と MAX を使用してこの問題を解決できます。

MIN(俳優.first_name)、MAX(俳優.last_name)、...を選択します。

完璧主義者は、あなたのグループ分けが間違っていて、自分たちが正しいと考えるでしょう。ダミーの MIN または MAX を使用すると、クエリが正しく組み立てられなくなります。ただし、MySQL でクエリをより高速に実行したい場合もあります。完璧主義者は次のような質問に満足するでしょう。

俳優.first_name、俳優.last_name、c.cnt を選択
sakila.actorより
	内部結合（
    actor_id、COUNT(*) を cnt として選択します。
    sakila.film_actorより
    俳優IDでグループ化
  ) AS c USING(actor_id);

ただし、サブクエリで一時テーブルを作成してデータを入力すると、理論上よりもコストがかかる可能性があります。サブクエリによって構築された一時テーブルにはインデックスがないため、パフォーマンスが低下することに注意することが重要です。

一般的に、グループ化されたクエリでは、グループ化されていない列を選択することはお勧めできません。これは、クエリ結果が不確実であるためです。インデックスが変更されたり、オプティマイザーが異なる戦略を使用したりすると、結果も変わります。実際、サーバーの SQL_MODE を ONLY_FULL_GROUP_BY に設定することをお勧めします。これにより、不適切に記述された group by クエリが記述された場合、システムはそれを直接実行するのではなく、エラーを生成します。 ONLY_FULL_GROUP_BY を有効にすると、SELECT のフィールドは GROUP BY で指定されたフィールドのみになります。この場合、ステップバイステップのクエリまたはサブクエリを作成して、最初にグループ化された列を検索し、次にセカンダリクエリを実行できます。

MySQL は、ORDER BY を使用してソートルールを指定しない限り、GROUP BY で指定された列の順序に従って自動的にグループ化します。順序を気にせず、これによってファイルソートが発生する場合は、ORDER BY NULL を使用して自動ソートをスキップできます。また、GROUP BY の後に DESC または ASC を追加することで、結果を指定した方向に並べ替えるように指定することもできます。

場合によっては、クエリをグループ化するときに、結果でスーパー集計を実行するように MySQL に要求できます。これは、GROUP BY の後に WITH ROLLUP 句を追加することで実行できますが、目的の最適化が達成されない可能性があります。 EXPLAIN を通じて実行方法を確認し、グループ化がファイルソートまたは一時テーブルを通じて行われているかどうかに注意することができます。次に、WITH ROLLUP を削除した後、同じクエリを比較します。比較することで、最適化する方法が見つかるかもしれません。

この方法ではより多くの行が返されますが、集計クエリを追加する方が効率的な場合もあります。 FROM の後にサブクエリをネストして中間クエリ結果を保持し、UNION を使用して最終結果を取得することもできます。

ただし、アプリケーションから WITH ROLLUP を削除し、最適化を通じてグループ化されたクエリを実行するのが最善であることに注意してください。

結論: グループ化されたクエリに GROUP BY を使用する場合は、グループ化にインデックス列を使用するのが最適です。順序を指定する必要がない場合は、最適化のために ORDER BY NULL を使用できます。インデックス列でグループ化していない場合は、代替案を検討し、最適化する前にサブクエリを使用するか、WITH ROLLUP を使用してパフォーマンスをチェックする必要があります。同時に、グループクエリでの予期しないエラーを防ぐために、ONLY_FULL_GROUP_BY を有効にするのが最適です。

以上がMySQLグループクエリの最適化方法の詳細です。MySQLグループクエリの最適化の詳細については、123WORDPRESS.COMの他の関連記事をご覧ください。

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