MySQL COUNT関数の使用と最適化

COUNT 関数は何をするのですか?

COUNT は、通常、値のカウントとデータの行のカウントという 2 つの異なる方法で使用される特殊な関数です。値は NULL 以外の式を参照します (NULL は値が欠落していることを意味します)。 COUNT パラメータに列名またはその他の式を指定すると、COUNT 関数は式に値が含まれる回数をカウントします。これは多くの人々を混乱させますが、主な理由は値と NULL の概念が曖昧であることです。

COUNT の別の形式は、結果セット内の行数を単純にカウントすることです。これは、引数として指定された式が NULL になる可能性がないことがわかっている場合に、MySQL が COUNT 関数を計算する方法です。最も典型的な例は COUNT(*) です。これは、データ テーブルのすべての列を展開する代わりに使用できると考えるかもしれません。実際、列全体は無視され、データの行数のみがカウントされます。

よくある間違いは、COUNT パラメータで列名を指定して、行をカウントしていると考えてしまうことです。結果の行数を取得する場合は、常に COUNT(*) を使用する必要があります。これにより、クエリがより明確になり、パフォーマンスの問題を回避できます。

MyISAMの「魔法」

よくある誤解は、MyISAM は COUNT クエリに対して非常に高速であるということです。 MyISAM の COUNT クエリは確かに高速ですが、この速度が高速になるシナリオは非常に限られています。この効果は、COUNT() クエリが実行され、WHERE 条件がない場合にのみ達成され、実際にはこのシナリオはまれです。 MySQL がこのステートメントを最適化できる理由は、ストレージ エンジンがデータ テーブル内の行数を常に正確に把握しているためです。 MySQL は列 col が NULL にできないことを認識している場合、最適化のために COUNT(col) を COUNT() に変換します。

COUNT クエリに WHERE 条件がある場合、または値をカウントする他の方法がある場合、MyISAM には「魔法」は何もありません。他の多くの要因に応じて、他のストレージ エンジンよりも高速または低速になる可能性があります。

シンプルなCOUNT最適化

データ行のインデックスカバレッジが高くない場合に、すべての行数をカウントしたい場合は、MyISAM エンジンの COUNT(*) を使用して最適化することができます。次の例では、標準の世界データベースを使用して、ID が 5 より大きい都市の数を検索する際の最適化を示します。記述する SQL ステートメントは次のようになります。

world.City から COUNT(*) を選択 WHERE ID > 5;

SHOW STATUS を使用してクエリを確認すると、4079 行がスキャンされたことがわかります。否定条件クエリを使用して、ID が 5 以下の都市の数を減算すると、スキャン結果を 5 行に減らすことができることがわかります。

SELECT (SELECT COUNT(*) FROM world.City) - COUNT(*) FROM world.City WHERE ID <= 5;

このクエリは、クエリ最適化フェーズ中に定数に変換されるため、読み取る行数が少なくなります。これは、EXPLAIN を使用して確認できます。

よくある問題は、同じ列の異なる値の数のクエリを 1 つのクエリ ステートメントで完了する方法です。たとえば、クエリ ステートメントを通じてさまざまな色の数を調べたいとします。 SELECT COUNT(color = 'blue' OR color='red') FROM items のようなクエリは使用できません。異なる色の対応するカウントの違いがわからないためです。また、SELECT COUNT(*) FROM items WHERE color = 'blue' AND color = 'red' のように、WHERE 条件に色を入れることもできません。色は相互に排他的であるため、次のようにしてこの問題を解決できます。

SELECT SUM(IF(color = 'blue', 1, 0)) AS blue, 
SUM(IF(color = 'red', 1, 0)) を red FROM 項目として実行します。

もう 1 つの方法は、SUM の代わりに COUNT を使用することです。これにより、値のない式の判定式が false であることのみが保証されます。

SELECT COUNT(color = 'blue' OR NULL) を青として選択します。
COUNT(color = 'red' OR NULL) を red FROM items として計算します。

近似値を使用する

正確な数値は必要なく、近似値を使用できる場合もあります。 EXPLAIN オプティマイザーによって提供される推定行数は通常このシナリオを満たすため、実際のクエリの代わりに EXPLAIN を使用できます。

多くの場合、正確な量は近似値よりもはるかに効率が低くなります。あるクライアントから、自社のウェブサイト上のアクティブユーザーの数を数えてほしいと依頼されたことがあります。ユーザー数は 30 分ごとにキャッシュされ、更新されます。これは本質的に不正確なので、推定値を使用することは許容されます。このクエリは、複数の WHERE 条件を使用して、非アクティブなユーザーまたはデフォルト ユーザー (特別な ID を持つユーザー) がカウントされないようにします。これらの条件を削除し、カウント操作を少し変更すると、効率が向上します。さらに最適化するには、不要な DISTINCT 操作を削除し、それによってファイルソート操作を削除します。最適化されたクエリはより高速になり、ほぼ正確な結果を返します。

より複雑な最適化

一般的に、COUNT クエリは多くの行をカウントする必要がある (大量のデータにアクセスする) ため、最適化が困難です。MySQL の別の代替手段は、カバーリング インデックスを使用することです。それだけでは不十分な場合は、システム全体のアプリケーション アーキテクチャを調整する必要があるかもしれません。たとえば、統計データ テーブルを検討したり、外部キャッシュ システム (Memcached など) を使用したりします。私たちはよく同じようなジレンマに直面します。速い、正確、シンプル - 選択できるのは 2 つだけです。

上記はMySQL COUNT関数の使用と最適化の詳細な内容です。MySQL COUNT関数の使用と最適化の詳細については、123WORDPRESS.COMの他の関連記事に注目してください。