MySQL でのフィルター条件なしのカウントの詳細な説明

カウント（*）

成し遂げる

1. MyISAM: テーブル内の行の総数をディスク上に保存し、フィルタリング条件なしでクエリの行数を直接返すことができます。

count(*) フィルター条件がある場合、MyISAM はすぐに返されません。

2. InnoDB: ストレージエンジンからデータを行ごとに読み取り、カウントを累積する

MVCC のため、InnoDB が同時に返す行数は不確実です。

例

テーブルtに10,000件のレコードがあると仮定する

セッションA	セッションB	セッションC
始める;
SELECT COUNT(*) FROM t; (10000を返します)
		INSERT INTO t; (行を挿入)
	始める;
	INSERT INTO t (行を挿入)
SELECT COUNT(*) FROM t; (10000を返します)	SELECT COUNT(*) FROM t; (10002を返します)	SELECT COUNT(*) FROM T; (10001を返します)

最後の瞬間、3つのセッションが同時にtの行の合計数を照会しますが、結果は異なります。

InnoDBのデフォルトのトランザクション分離レベルはRRであり、MVCCを通じて実装されています。

各トランザクションは、各レコード行が自分自身に可視であるかどうかを判断する必要がある。

最適化

1. InnoDBはインデックス構成テーブルです

クラスター化インデックスツリー: リーフノードはデータです
セカンダリインデックスツリー: リーフノードは主キー値です

2. セカンダリインデックスツリーが占めるスペースは、クラスター化インデックスツリーのスペースよりもはるかに小さい

3. オプティマイザーは最小のインデックスツリーをトラバースし、正しいロジックを確保しながらスキャンされるデータの量を最小限に抑えます。

フィルタリング条件のないカウント操作の場合、どのインデックスツリーを走査しても効果は同じです。
オプティマイザはcount(*)に最適なインデックスツリーを選択します。

テーブルステータスを表示

mysql> テーブルステータスを表示\G;
************************** 1. 行 ****************************
 名前: t
 エンジン: InnoDB
 バージョン: 10
 行形式: 動的
 行数: 100256
 平均行長: 47
 データ長: 4734976
最大データ長: 0
 インデックスの長さ: 5275648
 データ空き容量: 0
 自動増分: NULL
 作成時間: 2019-02-01 17:49:07
 更新時間: NULL
 チェック時間: NULL
 照合順序: utf8_general_ci
 チェックサム: NULL
 作成オプション:
 コメント：

SHOW TABLE STATUSもサンプリング（非常に不正確）を通じて推定し、誤差は40%～50%です。

メンテナンス回数

キャッシュ

プラン

Redis を使用してテーブルの合計行数を保存します (フィルタリング条件なし)
このテーブルに行が挿入されるたびにRedisは+1をカウントし、行が削除されるたびにRedisは-1をカウントします。

欠点

更新情報が失われました

1. Redisは更新を失う可能性がある

2. 解決方法: Redisが異常再起動したら、データベースでcount(*)を1回実行します。

異常な再起動はまれなので、テーブル全体のスキャンにかかるコストは許容範囲内です。

不正確な論理 – 致命的

1. シナリオ: 操作記録の総数と最新の操作記録100件を表示する

2. Redis と MySQL は 2 つの異なるストレージシステムであり、分散トランザクションをサポートしていないため、正確で一貫したビューを取得することは不可能です。

タイミングA

T3 の時点で、セッション B は最後に挿入されたレコードを含む 100 行を見つけますが、Redis はまだ +1 を追加していないため、論理的な矛盾が生じます。

時間	セッションA	セッションB
T1
T2	データ行 R を挿入します。
T3		Redis カウントを読み取ります。最新の 100 件のレコードを照会します。
T4	Redis カウント + 1;

タイミングB

T3 の時点で、セッション B は 100 行に最後に挿入されたレコードが含まれていないことを発見しますが、Redis は 1 行を追加しており、これは論理的な矛盾です。

時間	セッションA	セッションB
T1
T2	Redis カウント + 1;
T3		Redis カウントを読み取ります。最新の 100 件のレコードを照会します。
T4	データ行 R を挿入します。

データベース

カウント値をデータベース内の別のカウントテーブルCに格納する
InnoDBのクラッシュセーフ機能を使用すると、クラッシュ損失の問題が解決されます
InnoDBのトランザクションサポート機能を使用すると、一貫性のあるビューの問題が解決されます。
時刻 T3 では、セッション A のセッション B のトランザクションはまだコミットされていないため、テーブル C のカウント値 +1 はそれ自体には表示されず、ロジックは一貫しています。

時間	セッションA	セッションB
T1
T2	始める; 表Cのカウント値+1。
T3		始める; メーターCのカウント値を読み取ります。最新の 100 件のレコードを照会します。専念;
T4	データ行 R を挿入します。専念;

カウントのパフォーマンス

セマンティクス

1. count() は、返された結果セットを行ごとに判断する集計関数です。

count 関数のパラメータ値が NULL でない場合は累積値に 1 が加算され、それ以外の場合は加算されずに累積値が返されます。

2. カウント（フィールドF）

フィールドFはNULLの可能性があります
条件を満たす結果セット内の NULL 以外のフィールドの合計数を示します。

3. count(主キーID)、count(1)、count(*)

NULL にすることはできません
返された条件を満たす結果セットの合計数を示します

4. InnoDB エンジンは、サーバー層に必要なフィールドを返します。

count(*)は例外で、行全体を返すのではなく、空の行のみを返します。

パフォーマンス比較

カウント(フィールドF)

1. フィールド F が NULL を許可しないと定義されている場合、このフィールドをレコードから 1 行ずつ読み取り、判断に合格した後、1 行ずつ累積します。

テーブル構造から判断すると、このフィールドがNULLになることは不可能です。

2. フィールド F が NULL を許可するように定義されている場合、このフィールドをレコードから 1 行ずつ読み取り、判定を通過した後、1 行ずつ累積します。

テーブル構造から判断すると、このフィールドはNULLである可能性があります
フィールド値が実際にNULLであるかどうかを判定する

3. フィールドFにセカンダリインデックスがない場合、テーブル全体をトラバースすることしかできません（クラスター化インデックス）

4. InnoDBはフィールドFを返す必要があるため、オプティマイザは最適化の決定を少なくすることができます。

たとえば、トラバースする最適なインデックスを選択することはできません

count(主キーID)

InnoDBはテーブル全体（クラスター化インデックス）を走査し、各行のID値を取り出してサーバー層に返します。
サーバー層はIDを取得した後、それがNULLではないと判断し、行ごとに蓄積します。
オプティマイザは、トラバースする最適なインデックスを選択する場合があります。

カウント(1)

InnoDBエンジンはテーブル全体（クラスタ化インデックス）を走査しますが、値は取得しません。
サーバー層は、返された各行に数値 1 を入れて NULL かどうかを判断して、行ごとに累積します。
count(1)はcount(主キーID)よりも高速です。これはcount(主キーID)には2つの操作が含まれるためです。

データ行の解析
フィールド値をコピー

カウント（*）

count(*)はすべての値を取得するわけではありませんが、"*"は間違いなくNULLではなく、行ごとに累積されるため、値を取得しないように特別に最適化されています。
値なし: InnoDBは、サーバー層にNULLではなくカウント可能であることを通知するために空白行を返します。

効率ランキング

count(フィールドF) < count(主キーID) < count(1) ≈ count(*)
count(*) を使ってみてください

例

mysql> SHOW CREATE TABLE prop_action_batch_reward\G;
************************** 1. 行 ****************************
 テーブル: prop_action_batch_reward
テーブルの作成: CREATE TABLE `prop_action_batch_reward` (
 `id` bigint(20) NULLではない、
 `source` int(11) デフォルト NULL,
 `serial_id` bigint(20) NOT NULL,
 `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP、
 `user_ids` 中テキスト、
 `serial_index` tinyint(4) デフォルト '0',
 主キー (`id`)、
 ユニークキー `uniq_serial_id_source_index` (`serial_id`,`source`,`serial_index`),
 キー `idx_create_time` (`create_time`)
) エンジン=InnoDB デフォルト文字セット=utf8

カウント(フィールドF)

インデックスなし

user_ids にはインデックスがありませんが、InnoDB は user_ids フィールドを返す必要があるため、クラスター化インデックスのみをトラバースできます。

mysql> EXPLAIN SELECT COUNT(user_ids) FROM prop_action_batch_reward;
+----+-------------+--------------------------+------+---------------+-------+------+------+------+------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+--------------------------+------+---------------+-------+------+------+------+------+
| 1 | シンプル | prop_action_batch_reward | すべて | NULL | NULL | NULL | NULL | 16435876 | NULL |
+----+-------------+--------------------------+------+---------------+-------+------+------+------+------+

mysql> prop_action_batch_reward から COUNT(user_ids) を選択します。
+-----------------+
| ユーザーIDをカウント |
+-----------------+
|17689788 |
+-----------------+
セット1列目（10.93秒）

インデックス付き

1. serial_idにはインデックスがあり、uniq_serial_id_source_indexでトラバースできます。

2. ただし、InnoDB は serial_id フィールドを返す必要があるため、論理的に同等の idx_create_time を走査しません。

idx_create_time が選択され、serial_id フィールドが返される場合、テーブルを返す必要があることを意味します。

mysql> EXPLAIN SELECT COUNT(serial_id) FROM prop_action_batch_reward;
+----+-------------+---------------------------+--------+---------------+-----------------------------+--------+-----------+-------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+---------------------------+--------+---------------+-----------------------------+--------+-----------+-------------+
| 1 | SIMPLE | prop_action_batch_reward | インデックス | NULL | uniq_serial_id_source_index | 15 | NULL | 16434890 | インデックスを使用 |
+----+-------------+---------------------------+--------+---------------+-----------------------------+--------+-----------+-------------+

mysql> prop_action_batch_reward から COUNT(serial_id) を選択します。
+------------------+
| カウント(シリアルID) |
+------------------+
|17705069 |
+------------------+
セット1列目（5.04秒）

count(主キーID)

オプティマイザは、クラスタ化インデックスの代わりに、トラバースする最適なインデックスidx_create_timeを選択します。

mysql> EXPLAIN SELECT COUNT(id) FROM prop_action_batch_reward;
+----+-------------+---------------------------+--------+---------------+---------+-----------+-------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+---------------------------+--------+---------------+---------+-----------+-------------+
| 1 | SIMPLE | prop_action_batch_reward | インデックス | NULL | idx_create_time | 5 | NULL | 16436797 | インデックスを使用 |
+----+-------------+---------------------------+--------+---------------+---------+-----------+-------------+

mysql> prop_action_batch_reward から COUNT(id) を選択します。
+-----------+
| カウント(ID) |
+-----------+
|17705383 |
+-----------+
セット1列目（4.54秒）

カウント(1)

mysql> EXPLAIN SELECT COUNT(1) FROM prop_action_batch_reward;
+----+-------------+---------------------------+--------+---------------+---------+-----------+-------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+---------------------------+--------+---------------+---------+-----------+-------------+
| 1 | SIMPLE | prop_action_batch_reward | インデックス | NULL | idx_create_time | 5 | NULL | 16437220 | インデックスを使用 |
+----+-------------+---------------------------+--------+---------------+---------+-----------+-------------+

mysql> prop_action_batch_rewardからCOUNT(1)を選択します。
+----------+
| カウント(1) |
+----------+
|17705808 |
+----------+
セット1列目（4.12秒）

カウント（*）

mysql> EXPLAIN SELECT COUNT(*) FROM prop_action_batch_reward;
+----+-------------+---------------------------+--------+---------------+---------+-----------+-------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+---------------------------+--------+---------------+---------+-----------+-------------+
| 1 | SIMPLE | prop_action_batch_reward | インデックス | NULL | idx_create_time | 5 | NULL | 16437518 | インデックスを使用 |
+----+-------------+---------------------------+--------+---------------+---------+-----------+-------------+

mysql> prop_action_batch_reward から COUNT(*) を選択します。
+----------+
| カウント(*) |
+----------+
|17706074|
+----------+
セット1列目（4.06秒）

参考文献

「MySQL実践45講義」

要約する

上記はこの記事の全内容です。この記事の内容が皆さんの勉強や仕事に一定の参考学習価値を持つことを願っています。ご質問があれば、メッセージを残してコミュニケーションしてください。123WORDPRESS.COM を応援していただきありがとうございます。

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