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

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の他の関連記事をご覧ください。

以下もご興味があるかもしれません:
  • MySQL インデックス失敗の原理
  • MySQL全文インデックスの原理と欠点
  • MySQL インデックスの原理と使用例の分析
  • MySQLクエリ最適化プロセスを理解する
  • MySQL ページングクエリ最適化テクニック
  • MySQL のインデックスの原理とクエリの最適化の詳細な説明

<<:  HTML CSS に基づく検索アイコン付き検索ボックス機能を実装する

>>:  Docker+K8S+GitLab/SVN+Jenkins+Harbor をベースにした継続的インテグレーション配信環境の構築に関する詳細なチュートリアル

推薦する

Centos7 に MySQL 8.0.23 をインストールする手順 (初心者レベル)

まず、MySQL とは何かを簡単に紹介します。簡単に言えば、データベースはデータを格納するための倉庫...

VMware 仮想マシンのインストール Linux システムのグラフィック チュートリアル

この記事では、LinuxシステムのVMwareインストールの具体的な手順を参考までに紹介します。具体...

MySQLからデータをインポートする際の不正なフォーマット、インポートの遅延、データ損失などの問題を迅速に解決します。

遅い問題を完全に解決したい場合は、MySQL を MySQL 8.0 にアップグレードすることをお勧...

vue-router 履歴モード サーバー側設定プロセス記録

歴史ルート履歴モードとは、HTML5 の履歴 API を使用してクライアント側ルーティングを実装する...

Centos8 (最小インストール) Python3.8+pip のインストール方法に関するチュートリアル

Python8のインストールを最小化した後、Python3.8.1をインストールしました。オンライン...

MySQL 学習ノート: 完全な SELECT ステートメントの使用例と詳細な説明

この記事では、MySQL 学習ノートの select ステートメントの完全な使用方法を例を使用して説...

Linux curl フォームのログインまたは送信と Cookie の使用に関する詳細な説明

序文この記事では主に、curl を介してフォーム送信ログインを実装する方法について説明します。単一の...

Linux仮想メモリについての簡単な説明

目次起源仮想メモリページングとページテーブルメモリのアドレス指定と割り当て関数プロセスメモリ管理デー...

JavaScript で 2 次元配列を作成するためのヒント

Js での 2 次元配列の作成:まず、JavaScript は 1 次元配列のみをサポートしています...

Portainer を使用して Docker のビジュアル インターフェースを構築する方法

ポーテナーの紹介Portainer は、ステータス表示パネル、アプリケーション テンプレートの迅速な...

CSS3プロパティline-clampはテキスト行の使用を制御します

説明: ブロック要素に表示されるテキストの行数を制限します。 -webkit-line-clamp ...

JavaScript バブルソートの例

目次1. バブルソートとは何か2. 例を挙げるラウンド1:第2ラウンド:第3ラウンド:第4ラウンド:...

Alibaba CloudにMySQLをインストールする方法の詳細な説明

軽量のオープンソース データベースである MySQL は、エンタープライズ レベルのアプリケーション...

Vueコンポーネントは、写真やビデオをアップロードするためのサンプルコードをカプセル化します

まず依存関係をダウンロードします: cnpm i -S vue-uuid ali-oss画像フィール...

MySQLのジョイントクエリについて詳しく説明します

目次ユニオンクエリ1. 中国の各省のIDと名前を照会する2. 湖南省のすべての地級市のIDと名称3....