Quel est le principe de performance de MySQL COUNT(*)-tutoriel mysql-php.cn

1. Lequel est le plus rapide, COUNT(1), COUNT(*) ou COUNT(field) ?

Effet d'exécution :

COUNT(*)Paire MySQLcount(*) a été optimisé. count(*) analyse directement l'enregistrement de l'index de clé primaire sans supprimer tous les champs et les accumule directement par ligne. COUNT(*)MySQL 对count(*)进行了优化，count(*)直接扫描主键索引记录，并不会把全部字段取出来，直接按行累加。
COUNT(1)InnoDB引擎遍历整张表，但不取值，server 层对于返回的每一行，放一个数字“1”进去，按行累加。
COUNT(字段)如果这个“字段”是定义为NOT NULL，那么InnoDB 引擎会一行行地从记录里面读出这个字段，server 层判断不能为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

Copier après la connexion

测试数据库采用的是（存储引擎采用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)

Copier après la connexion

实验开始：

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

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();

Copier après la connexion

接下来我们分别来实验一下：

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)

Copier après la connexion

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)

Copier après la connexion

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)

Copier après la connexion

我们可以再来测试一下执行计划

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)

Copier après la connexion

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)

Copier après la connexion

COUNT(字段)

(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)

Copier après la connexion

需要注意的是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)

Copier après la connexion

实验结果

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 handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference

翻译： InnoDB以相同的方式处理SELECT COUNT（*）和SELECT COUNT（1）操作。没有性能差异

所以说明了对于COUNT(1)或者是COUNT(*)，MySQL的优化其实是完全一样的，没有存在没有性能的差异。

但是建议使用COUNT(*)，因为这是MySQL92定义的标准统计行数的语法。

2.COUNT(*)与TABLES_ROWS

在InnoDB中，MySQL数据库每个表占用的空间、表记录的行数可以打开MySQL的information_schema数据库。在该库中有一个TABLES表，这个表主要字段分别是：

TABLE_SCHEMA : 数据库名
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)

Copier après la connexion

可以看到，记录的条数并不准确，因为InnoDB引擎下TABLES_ROWS行计数仅是大概估计值。

3.COUNT(*)是怎么样执行的?

首先要明确的是，MySQL有多种不同引擎，在不同的引擎中，count(*)有不同的实现方式，本文主要介绍的是在InnoDB引擎上的执行流程

在InnoDB存储引擎中，count(*)函数是先从内存中读取表中的数据到内存缓冲区，然后扫描全表获得行记录数的。简单来说就是全表扫描，一个循环解决问题，循环内: 先读取一行，再决定该行是否计入count循环内是一行一行进行计数处理的。

在MyISAM引擎中是把一个表的总行数存在了磁盘上，因此执行count(*)

COUNT(1)Le moteur InnoDB parcourt toute la table, mais ne prend pas de valeurs. La couche serveur met un numéro pour chaque ligne. renvoyé. "1" entre et s'additionne par ligne.

#🎜🎜##🎜🎜#COUNT(field)Si ce "champ" est défini comme NOT NULL, alors le moteur InnoDB lira l'enregistrement ligne par ligne Pour ce champ, la couche serveur détermine qu'il ne peut pas être NULL et est accumulé ligne par ligne ; si la définition du « champ » autorise NULL, alors le moteur InnoDB lira ce champ de l'enregistrement ligne par ligne, puis en retirera la valeur. et jugez à nouveau si c'est NULL. Cela s'additionne. #🎜🎜#

Analyse expérimentale

#🎜🎜##🎜🎜#L'environnement utilisé pour les tests dans cet article : #🎜🎜##🎜🎜#rrreee#🎜🎜 ##🎜 🎜#La base de données de test utilise InnoDB (le moteur de stockage utilise InnoDB, les autres paramètres sont par défaut) : #🎜🎜##🎜🎜#rrreee#🎜🎜##🎜🎜#L'expérience démarre : #🎜🎜##🎜🎜# rrreee#🎜🎜 ##🎜🎜# Expérimentons séparément : #🎜🎜##🎜🎜##🎜🎜#COUNT(1) a pris 4,19 secondes#🎜🎜#rrreee#🎜🎜#COUNT(*)Cela a pris 4,16 secondes#🎜🎜#rrreee#🎜🎜#COUNT(field)Cela a pris 4,23 secondes#🎜🎜#rrreee#🎜🎜#Nous pouvons revenir Testez le plan d'exécution#🎜🎜##🎜🎜#COUNT(*)#🎜🎜#rrreee#🎜🎜#COUNT(1)#🎜🎜#rrreee#🎜 🎜#COUNT(field)#🎜🎜#rrreee#🎜🎜##🎜🎜#Il est à noter que s'il y a un champ de clé non primaire dans COUNT#🎜🎜##🎜🎜# rrreee

Résultats de l'expérience

#🎜🎜##🎜🎜##🎜🎜#1 De l'expérience ci-dessus, nous pouvons conclure que COUNT(*) et COUNT(1. ) est le plus rapide, suivi de COUNT(id). #🎜🎜##🎜🎜##🎜🎜#2.count(*) a été réécrit par l'optimiseur de requêtes MySQL en count(0), et L'index idx_salary est sélectionné. #🎜🎜##🎜🎜##🎜🎜#3. count(1) et count(id) sélectionnent l'index idx_salary. #🎜🎜#

Conclusion expérimentale

#🎜🎜#Résumé : COUNT(*)=COUNT(1)>COUNT(id)# 🎜🎜##🎜🎜##🎜🎜#La documentation officielle de MySQL dit également : #🎜🎜##🎜🎜#

#🎜🎜#InnoDB gère les opérations SELECT COUNT(*) et SELECT COUNT(1) de la même manière Il n'y a aucune différence de performance. Il n'y a aucune différence de performances#🎜🎜#

#🎜🎜# Cela montre donc que pour COUNT(1) ou COUNT(*), l'optimisation de MySQL est en fait complètement pareil, il n'y a aucune différence de performances. #🎜🎜##🎜🎜#Mais il est recommandé d'utiliser COUNT(*) car c'est la syntaxe standard pour compter les lignes définie par MySQL92. #🎜🎜##🎜🎜#2.COUNT(*) et TABLES_ROWS#🎜🎜##🎜🎜#Dans InnoDB, l'espace occupé par chaque table de la base de données MySQL et le nombre de lignes enregistrées dans la table peuvent être ouverts par Base de données information_schema de MySQL. Il y a une table TABLES dans la bibliothèque. Les principaux champs de #🎜🎜# sont : #🎜🎜##🎜🎜##🎜🎜##🎜🎜##🎜🎜##🎜🎜 #. TABLE_SCHEMA : #🎜🎜#Nom de la base de données#🎜🎜##🎜🎜##🎜🎜##🎜🎜#TABLE_NAME : #🎜🎜#Nom de la table#🎜🎜##🎜🎜## 🎜 🎜##🎜🎜#ENGINE : #🎜🎜# Le moteur de stockage utilisé #🎜🎜##🎜🎜##🎜🎜##🎜🎜#TABLES_ROWS : #🎜🎜#Nombre d'enregistrements#🎜🎜# #🎜🎜##🎜🎜#DATA_LENGTH : Taille des données #🎜🎜# #🎜🎜##🎜🎜#INDEX_LENGTH : Taille de l'index #🎜🎜# #🎜 🎜 #TABLE_ROWS est utilisé pour afficher le nombre de lignes que possède actuellement la table. Cette commande s'exécute très rapidement. Cette TABLE_ROWS peut-elle remplacer count(*) ? #🎜🎜##🎜🎜##🎜🎜#Nous utilisons TABLES_ROWS pour interroger le nombre d'enregistrements de table : #🎜🎜##🎜🎜#rrreee#🎜🎜#Vous pouvez voir que le nombre d'enregistrements n'est pas exact à cause du Moteur InnoDB Le nombre de lignes sous TABLES_ROWS ne sont que des estimations approximatives. #🎜🎜##🎜🎜#3. Comment COUNT(*) est-il exécuté ? #🎜🎜##🎜🎜#La première chose à préciser est que MySQL a de nombreux moteurs différents, count(*). ) a différentes méthodes d'implémentation.Cet article présente principalement le processus d'exécution sur le moteur InnoDB #🎜🎜##🎜🎜#Dans le moteur de stockage InnoDB, count(*)La fonction lit d'abord les données de la table de la mémoire vers la mémoire tampon, puis analyse la table entière pour obtenir le nombre d'enregistrements de ligne. Pour faire simple, il s'agit d'une analyse complète de la table. Une boucle résout le problème dans la boucle : lisez d'abord une ligne, puis décidez si la ligne est incluse dans count. rangée. #🎜🎜##🎜🎜#Dans le moteur MyISAM, le nombre total de lignes d'une table est stocké sur le disque, donc lorsque count(*) est exécuté, ce nombre sera renvoyé directement, ce qui est très efficace. #🎜🎜##🎜🎜#La raison pour laquelle InnoDB ne stocke pas de nombres comme MyISAM est que même s'il y a plusieurs requêtes en même temps, en raison du contrôle d'accès concurrentiel multi-versions (MVCC), combien de lignes la table InnoDB doit-elle renvoyer ? C'est également incertain. InnoDB est plus performant que MyISAM en termes de prise en charge des transactions, de concurrence ou de sécurité des données. #🎜🎜#

Malgré cela, InnoDB a optimisé l'opération count(*). InnoDB est une table organisée en index.Les nœuds feuilles de l'arborescence d'index de clé primaire sont des données, tandis que les nœuds feuilles de l'arborescence d'index ordinaire sont des valeurs de clé primaire. Par conséquent, l’arborescence d’index ordinaire est beaucoup plus petite que l’arborescence d’index de clé primaire. Pour des opérations telles que count(*), les résultats obtenus en parcourant n'importe quel arbre d'index sont logiquement les mêmes. Par conséquent, l'optimiseur MySQL trouvera le plus petit arbre à parcourir. count(*)操作还是做了优化的。InnoDB是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。所以，普通索引树比主键索引树小很多。对于count(*)这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL 优化器会找到最小的那棵树来遍历。

需要注意的是我们在这篇文章里讨论的是没有过滤条件的count(*)，如果加了WHERE条件的话，MyISAM引擎的表也是不能返回得这么快的。

4.总结

1.COUNT(*)=COUNT(1)>COUNT(id)
2.COUNT函数的用法，主要用于统计表行数。主要用法有COUNT(*)、COUNT(字段)和COUNT(1)
3.因为COUNT(*)是SQL92定义的标准统计行数的语法，所以MySQL对他进行了很多优化，MyISAM中会直接把表的总行数单独记录下来供COUNT(*)查询，而InnoDB则会在扫表的时候选择最小的索引来降低成本。这些优化的前提是没有进行WHERE和GROUP的条件查询。
4.在InnoDB中COUNT(*)和COUNT(1)实现上没有区别，而且效率一样，但是COUNT(字段)需要进行字段的非NULL判断，所以效率会低一些。
5.因为COUNT(*)是SQL92定义的标准统计行数的语法，并且效率高，所以还是建议使用COUNT(*)查询表的行数。
6.正如前面COUNT(name)
Il convient de noter que ce dont nous discutons dans cet article est count(*) sans conditions de filtre. Si la condition WHERE est ajoutée, la table du moteur MyISAM le fera. aussi être Vous ne pouvez pas revenir en arrière si vite.

4. Résumé

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

🎜2. L'utilisation de la fonction COUNT est principalement utilisée pour compter le nombre de lignes du tableau. Les principales utilisations sont COUNT(*), COUNT(field) et COUNT(1)🎜🎜
🎜3 Parce que COUNT(*) est la statistique standard définie. par SQL92 La syntaxe du nombre de lignes, donc MySQL a fait de nombreuses optimisations pour cela. MyISAM enregistrera directement le nombre total de lignes dans la table pour la requête COUNT(*), tandis qu'InnoDB le fera. sélectionnez-le lors de la numérisation de la table. Indexation minimale pour réduire les coûts. Le principe de ces optimisations est qu'il n'y a pas de requêtes conditionnelles WHERE et GROUP. 🎜🎜
🎜4. Dans InnoDB, il n'y a pas de différence d'implémentation entre COUNT(*) et COUNT(1), et l'efficacité est la même, mais COUNT(field )Doit juger si le champ est NULL, donc l'efficacité sera inférieure. 🎜🎜
🎜5. Étant donné que COUNT(*) est la syntaxe standard pour compter les lignes définie par SQL92 et qu'elle est très efficace, il est recommandé d'utiliser COUNT(*) Le nombre de lignes dans la table de requête. 🎜🎜<li>🎜6. Tout comme le cas d'utilisation précédent de <code>COUNT(name), lors du processus de création de table, il est nécessaire d'établir des index performants en fonction des besoins métier, et au dans le même temps, il convient de veiller à éviter de créer un index inutile. 🎜🎜🎜

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!