MySQL の集計関数 count の使用法とパフォーマンスの最適化テクニック

この記事の環境はWindows 10、MySQLのバージョンは5.7.12-logです

1. 基本的な使い方

count には 2 つの基本的な機能があります。

• 列内のデータの数をカウントします。
• 結果セット内の行数をカウントします。

条件を満たすデータの数を取得するために使用します。ただし、count を 1 列に適用した場合と、複数の列に適用した場合、* を使用して行全体を表現した場合など、使用時の印象とは異なる状況もあります。

例の表は次のとおりです。

テーブル `NewTable` を作成します (
`id` int(11) NULL デフォルト NULL ,
`name` varchar(30) NULL デフォルト NULL ,
`country` varchar(50) NULL デフォルト NULL ,
`province` varchar(30) NULL デフォルト NULL ,
`city` varchar(30) NULL デフォルト NULL 
)エンジン=InnoDB 

1.1 NULL値をカウントしない

NULL 値がある場合、返される結果ではそれらの値が除外されます。

personからcount(country)を選択します。

返される結果は次のとおりです。

条件を満たすデータ項目が存在しない場合、構造体は 0 を返します。このメソッドは、条件を満たすデータが存在するかどうかを判断するためによく使用され、返されるデータ型は bigint です。

1.2 カウントの処理(*)

count(*) の処理は少し異なります。すべてのデータの数を返しますが、NULL 値は除外されません。すべての列に展開することと同じではありませんが、すべての列を直接無視し、すべての行を直接カウントします。声明は次のとおりです。

personからcount(*)を選択します。

返される結果は次のとおりです。

すべてのデータの数を返したいが、すべて NULL である列を含めたくない場合は、count(*) を使用することはできません。ただし、1.1 では、count は列に作用するときに NULL を除外すると記載されているため、このように直接記述するのが正しいでしょうか。

person から count(id, `name`, country, Province, city) を選択します。

それは間違っています。Count は複数の列ではなく単一の列でのみ機能するため、上記の記述は間違っています。

さらに、MyISAM ストレージ エンジンでは count(*) ステートメントが最適化されています。各テーブルのデータ行数はストレージ エンジンに保存されており、すぐに取得できます。ただし、InnoDB などのトランザクション ストレージ エンジンでは、複数のトランザクションが関係します。

1.3 count(distinct …) の処理

count(distinct ...) は、一意だが NULL ではない行の数を返します。これは、distinct のみを使用する場合とは異なります。distinct は NULL 値をフィルタリングしないためです。詳細については、「MySQL で distinctive を使用する方法」を参照してください。

- 条件を満たすデータがない場合、0 を返します。
- このステートメントは複数の列に適用できます。列間に違いがある場合、データの行全体が異なるとみなされ、複数の列に distinct を適用した場合と同じ効果があります。

person から count(DISTINCT country) を選択します。

返される結果は次のとおりです。

ただし、count(*) と count(distinct) の組み合わせの場合は次のようになります。

personからcount(DISTINCT *)を選択します。

このステートメントは間違っており、実行できないため、select count(DISTINCT *) from personとは異なります。

2. パフォーマンスの最適化

通常、count(*) 操作では、データ テーブル内の多数の行をスキャンする必要があります。大量のデータのスキャンを回避することが、ステートメントを最適化する鍵となります。この問題は、次の 2 つの観点から考えることができます。

2.1 データベースレベルでの最適化

2.1.1 count(*)の場合

MySQL は内部的に count(*) に最適化されています。explain を使用したクエリは次のようになります。

EXPLAIN personからcount(*)を選択します。

このクエリでは、完全なテーブルスキャンやインデックスは使用されず、データ テーブルをクエリする必要さえないことがわかります。上記のサンプル データベースでは、ライブラリのストレージ エンジンは InnoDB であり、主キーもインデックスもありません。

2.2 単一列のカウント

クエリは次のとおりです。

EXPLAIN select count(country) from person where id > 2;

テーブル全体が主キーとインデックスなしでスキャンされたことが判明しました。データ内の多数の行をスキャンすることを回避する最も直接的な方法の 1 つは、インデックスを使用することです。

id が一般インデックスとして設定されている場合: INDEX abc (id) USING BTREE 。

次のようにクエリを実行します。

EXPLAIN select count(country) from person where id > 2;

結果は次のとおりです。

このとき、インデックスは使用されず、フルテーブルスキャンがまだ実行されていることがわかります。以下を実行すると、

EXPLAIN id > 4 の場合、 person から count(country) を選択します。

結果は次のとおりです。

これはインデックスを使用した範囲クエリであり、明らかに上記よりも優れています。

しかし、疑問は、なぜインデックスを時々使用したり、時々使用しなかったりするのかということです。上記の最初のクエリでは、可能なキーが検出されましたが、使用されませんでしたか?知っている人がいたら説明してください！

id を主キーとして設定し、次のようにクエリを実行します。

EXPLAIN select count(country) from person where id > 2;

結果は次のとおりです。

2.2 アプリケーションレベルでの最適化

アプリケーション レベルで最適化するには、過去によく使用されていた Memcached や、現在非常に人気のある Redis などのキャッシュ サブシステムをシステム アーキテクチャに導入することを検討できますが、これによりシステムの複雑さが増します。

MySQL のグループ化と集計関数 (合計、カウントなど) の例

まず、MySQL の集計関数を見てみましょう。

MySQL の特殊関数: 集計関数、SUM、COUNT、MAX、MIN、AVG など。これらの関数と他の関数との基本的な違いは、通常、複数のレコードに対して作用することです。例えば：

テーブルからSUM(スコア)を選択

この SQL は、テーブルテーブル内のすべてのスコア列の合計を照会することを意味します。

次に、例を使用して、group by ステートメントで集計関数を使用する方法を説明します。

ブックテーブルは次のとおりです。

ここで、都市をグループ化し、各グループ内のレコード数を取得する必要があります。カウント集計関数が必要です。

SELECT *,count(*) FROM book GROUP BY city

結果は次のとおりです。

まず、返されたレコードを都市に基づいて複数のグループに分割します。これが GROUP BY の文字通りの意味です。グループ化後、集計関数を使用して各グループ内の異なるフィールド (1 つ以上のレコード) を操作します。