MySQL COUNT(*) のパフォーマンス原理は何ですか?-mysql チュートリアル-php.cn

1.COUNT(1)、COUNT(*)、COUNT(field) のどれが速いですか?

実行効果:

##COUNT(*)MySQL は count(*) を順番に実行します。最適化するために、count(*) は主キーインデックスレコードを直接スキャンし、すべてのフィールドを取り出すのではなく、それらを行ごとに直接累積します。
COUNT(1)InnoDB エンジンはテーブル全体を走査しますが、値は取得しません。サーバー層は返された各行に数値「1」を入れます。 . 行ごとに集計します。
COUNT(field)この「フィールド」が NOT NULL として定義されている場合、InnoDB エンジンはレコードからこのフィールドを 1 行ずつ読み取り、サーバーはレイヤーの判定は NULL にすることができず、行ごとに累積されます。「フィールド」定義で NULL が許可されている場合、InnoDB エンジンはレコードからこのフィールドを行ごとに読み取り、値を取り出して再度判定します。は NULL ではないため、蓄積されます。

実験分析

この記事のテストに使用される環境:

[root@zhyno1 ~]# cat /etc/system-release
CentOS Linux release 7.9.2009 (Core)

[root@zhyno1 ~]# uname -a
Linux zhyno1 3.10.0-1160.62.1.el7.x86_64 #1 SMP Tue Apr 5 16:57:59 UTC 2022 x86_64 x86_64 x86_64 GNU/Linux

ログイン後にコピー

テストデータベースは (ストレージエンジン InnoDB を使用する、他のパラメータはデフォルトです):

(Mon Jul 25 09:41:39 2022)[root@GreatSQL][(none)]>select version();
+-----------+
| version() |
+-----------+
| 8.0.25-16 |
+-----------+
1 row in set (0.00 sec)

ログイン後にコピー

実験の開始:

#首先我们创建一个实验表

CREATE TABLE test_count (
  `id` int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(20) NOT NULL,
  `salary` int(1) NOT NULL,
  KEY `idx_salary` (`salary`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#插入1000W条数据
DELIMITER //
CREATE PROCEDURE insert_1000w()
BEGIN
    DECLARE i INT;
    SET i=1;
    WHILE i<=10000000 DO
        INSERT INTO test_count(name,salary) VALUES(&#39;KAiTO&#39;,1);
        SET i=i+1;
    END WHILE;
END//
DELIMITER ;
#执行存储过程
call insert_1000w();

ログイン後にコピー

次に、個別に実験してみましょう:

COUNT(1)4.19 秒かかりました

(Sat Jul 23 22:56:04 2022)[root@GreatSQL][test]>select count(1) from test_count;
+----------+
| count(1) |
+----------+
| 10000000 |
+----------+
1 row in set (4.19 sec)

ログイン後にコピー

COUNT(*)4.16 秒かかりました

(Sat Jul 23 22:57:41 2022)[root@GreatSQL][test]>select count(*) from test_count;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (4.16 sec)

ログイン後にコピー

COUNT (フィールド)4.23 秒かかりました

(Sat Jul 23 22:58:56 2022)[root@GreatSQL][test]>select count(id) from test_count;
+-----------+
| count(id) |
+-----------+
|  10000000 |
+-----------+
1 row in set (4.23 sec)

ログイン後にコピー

実行計画を再度テストできます

COUNT(*)

(Sat Jul 23 22:59:16 2022)[root@GreatSQL][test]>explain select count(*) from test_count;
+----+-------------+------------+------------+-------+---------------+------------+---------+------+---------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key        | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+------------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test_count | NULL       | index | NULL          | idx_salary | 4       | NULL | 9980612 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+------------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

(Sat Jul 23 22:59:48 2022)[root@GreatSQL][test]>show warnings;
+-------+------+-----------------------------------------------------------------------+
| Level | Code | Message                                                               |
+-------+------+-----------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select count(0) AS `count(*)` from `test`.`test_count` |
+-------+------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

ログイン後にコピー

COUNT(1)

(Sat Jul 23 23:12:45 2022)[root@GreatSQL][test]>explain select count(1) from test_count;
+----+-------------+------------+------------+-------+---------------+------------+---------+------+---------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key        | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+------------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test_count | NULL       | index | NULL          | idx_salary | 4       | NULL | 9980612 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+------------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

(Sat Jul 23 23:13:02 2022)[root@GreatSQL][test]>show warnings;
+-------+------+-----------------------------------------------------------------------+
| Level | Code | Message                                                               |
+-------+------+-----------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select count(1) AS `count(1)` from `test`.`test_count` |
+-------+------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

ログイン後にコピー

COUNT(field)

(Sat Jul 23 23:13:14 2022)[root@GreatSQL][test]>explain select count(id) from test_count;
+----+-------------+------------+------------+-------+---------------+------------+---------+------+---------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key        | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+------------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test_count | NULL       | index | NULL          | idx_salary | 4       | NULL | 9980612 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+------------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

(Sat Jul 23 23:13:29 2022)[root@GreatSQL][test]>show warnings;
+-------+------+-----------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                       |
+-------+------+-----------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select count(`test`.`test_count`.`id`) AS `count(id)` from `test`.`test_count` |
+-------+------+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

ログイン後にコピー

主キー以外のフィールドがある場合に注意してください。 COUNT

(Tue Jul 26 14:01:57 2022)[root@GreatSQL][test]>explain select count(name) from test_count where id <100 ;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test_count | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   99 |   100.00 | Using where |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

ログイン後にコピー

実験結果

1. 上記の実験から、
COUNT(*) および COUNT と結論付けることができます。 (1) が最も速く、次に COUNT(id) が続きます。
2.
count(*) は MySQL クエリオプティマイザーによって count(0) に書き換えられ、idx_salary インデックスが選択されました。
3.
count(1) と count(id) は両方とも idx_salary インデックスを選択します。

概要:

COUNT(*)=COUNT(1)>COUNT(id)

MySQL の公式ドキュメントには次のように書かれています:

#InnoDB は SELECT COUNT(*) と SELECT COUNT(1) 操作を同じ方法で処理します。パフォーマンスに違いはありません

#翻訳:

InnoDB は、SELECT COUNT(*) 操作と SELECT COUNT(1) 操作を同じ方法で処理します。パフォーマンスに違いはありません

つまり、COUNT(1)
または

COUNT(*)

では、MySQL の最適化は実際にはまったく同じであることを意味します。はありません。パフォーマンスの違いはありません。ただし、COUNT(*) を使用することをお勧めします。これは、MySQL92 で定義されている行をカウントするための標準構文であるためです。

2.COUNT(*) と TABLES_ROWS InnoDB では、MySQL データベースの各テーブルが占めるスペースとテーブルに記録された行数は、MySQL# を開くことで開くことができます。 ##information_schema

データベース。このライブラリには

TABLES

テーブルがあります。

このテーブルの主なフィールドは次のとおりです: TABLE_SCHEMA:

Database name

TABLE_NAME：
テーブル名
ENGINE：
使用するストレージエンジン
TABLES_ROWS:
レコード数
DATA_LENGTH: データサイズ
INDEX_LENGTH: インデックスサイズ
TABLE_ROWS は、テーブルに現在存在する行数を表示するために使用されます。このコマンドは非常に迅速に実行されます。この TABLE_ROWS は
count(*)
を置き換えることはできますか?

TABLES_ROWS を使用してテーブルレコード数をクエリします。

(Sat Jul 23 23:15:14 2022)[root@GreatSQL][test]>SELECT TABLE_ROWS
    -> FROM INFORMATION_SCHEMA.TABLES
    -> WHERE TABLE_NAME = &#39;test_count&#39;;
+------------+
| TABLE_ROWS |
+------------+
|    9980612 |
+------------+
1 row in set (0.03 sec)

ログイン後にコピー

InnoDB エンジンでは TABLES_ROWS 行数がカウントされるため、レコード数が正確ではないことがわかります。あくまで概算です。

3. COUNT(*) はどのように実行されますか?

まず明確にしておきたいのは、MySQL にはさまざまなエンジンがあるということです。さまざまなエンジンでは、

count(*)

さまざまな実装方法がありますが、この記事では主に InnoDB エンジンでの実行処理を紹介します

InnoDB ストレージエンジンでは、

count(*) 関数が最初にメモリから読み取ります。テーブル内のデータをメモリバッファーに取り込み、テーブル全体をスキャンして行レコードの数を取得します。簡単に言えば、これはテーブル全体のスキャンです。ループによって問題が解決されます。ループ内: 最初に行を読み取り、次にその行が count

に含まれるかどうかを判断します。ループは行ごとにカウントします。。

MyISAM エンジンでは、テーブルの総行数がディスクに保存されるため、count(*) を実行すると、この数値が直接返されるため、非常に効率的です。。

InnoDB が MyISAM のような数値を保存しない理由は、複数のクエリが同時に存在する場合でも、マルチバージョン同時実行制御 (MVCC) により、InnoDB テーブルが返すべき行数が制限されるためです。関係ない、確かに。 InnoDB は、トランザクションサポート、同時実行性、またはデータセキュリティの点で MyISAM よりも優れたパフォーマンスを発揮します。

これにもかかわらず、InnoDB は count(*) 操作を最適化しました。 InnoDB はインデックス構成テーブルであり、主キーインデックスツリーのリーフノードはデータであり、通常のインデックスツリーのリーフノードは主キー値です。したがって、通常のインデックスツリーは主キーインデックスツリーよりもはるかに小さくなります。 count(*) のような操作の場合、どのインデックスツリーを走査しても得られる結果は論理的に同じです。したがって、MySQL オプティマイザは、走査する最小のツリーを見つけます。

この記事で説明するのは、フィルタ条件なしの count(*) であることに注意してください。WHERE 条件を追加すると、MyISAM エンジンのテーブルがそんなに早く戻ることはできません。

4.概要

COUNT(*)=COUNT(1)>COUNT(id)
COUNT(*)、COUNT(field)、および COUNT(1)
COUNT(*)
は SQL92 であるためです。行数をカウントするための標準構文が定義されているため、MySQL では多くの最適化が行われています。MyISAM は COUNT(*) クエリのテーブル内の合計行数を直接記録しますが、InnoDB はコストを削減するために最小のインデックスを選択する場合は、テーブルをスキャンします。これらの最適化の前提は、WHERE および GROUP 条件付きクエリがないことです。
COUNT(*)
と COUNT(1) の実装に違いはなく、効率は次のようになります。同じですが、COUNT(field)フィールドがNULLかどうかの判定が必要なので効率は悪くなります。
COUNT(*)
は SQL92 で定義された行をカウントするための標準構文であり、非常に効率的であるため、COUNT( * )クエリテーブル内の行数。
COUNT(name)
の使用例と同様に、テーブル作成プロセス中に、次に従って高パフォーマンスのインデックスを確立する必要があります。ビジネスニーズに合わせて、不必要なインデックス作成を避けることにも注意してください。

以上がMySQL COUNT(*) のパフォーマンス原理は何ですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。