Analyser count(*) dans MySQL Est-ce vraiment plus rapide que count(1) ?

Count(*) dans MySQL est-il vraiment plus rapide que count(1) ? L'article suivant comparera count(*) et count(1) dans MySQL pour voir leurs différences de performances. J'espère qu'il vous sera utile !

Quelqu'un m'a dit aujourd'hui que count(1) est plus rapide que count(*) dans MySQL. Puis-je tolérer cela ? Il faut que tu fasses une pause avec lui. count(1) 比 count(*) 快，这能忍？必须得和他掰扯掰扯。

声明：以下讨论基于 InnoDB 存储引擎，MyISAM 因为情况特殊我在文末会单独说一下。【相关推荐：mysql视频教程】

先说结论：这两个性能差别不大。

1.实践

我准备了一张有 100W 条数据的表，表结构如下：

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

可以看到，有一个主键索引。

我们来用两种方式统计一下表中的记录数，如下：

可以看到，两条 SQL 的执行效率其实差不多，都是 0.14s。

再来看另外两个统计：

id 是主键，username 以及 address 则是普通字段。

可以看出，用 id 来统计，也有一丢丢优势。松哥这里因为测试数据样板比较小，所以效果不明显，小伙伴们可以加大测试数据量，那么这种差异会更加明显。

那么到底是什么原因造成的这种差异，接下来我们就来简单分析一下。

2. explain 分析

我们先用 explain 来看下这几个 SQL 不同的执行计划：

可以看到，前三个统计方式的执行计划是一样的，后面两个是一样的。

我这里和大家比较下 explain 中的不同项：

• type：前三个的 type 值为 index，表示全索引扫描，就是把整个索引过一遍就行（注意是索引不是整个表）；后两个的 type 值为 all，表示全表扫描，即不会使用索引。
• key：这个表示 MySQL 决定采用哪个索引来优化对该表的访问，PRIMARY 表示利用主键索引，NULL 表示不用索引。
• key_len：这个表示 MySQL 使用的键长度，因为我们的主键类型是 INT 且非空，所以值为 4。
• Extra：这个中的 Using index 表示优化器只需要通过访问索引就可以获取到需要的数据（不需要回表）。

通过 explain 我们其实也能大概看出来前三种统计方式的执行效率是要高一些的（因为用到了索引），而后面两种的统计效率相对来说要低一些的（没用索引，需要全表扫描）。

仅有上面的分析还不够，我们再来从原理角度来分析一下。

3. 原理分析

3.1 主键索引与普通索引

在开始原理分析以前，我想先带领大家看一下 B+ 树，这对于我们理解接下来的内容有重要作用。

大家都知道，InnoDB 中索引的存储结构都是 B+ 树（至于什么是 B+ 树，和 B 树有什么区别，这个本文就不讨论了，这两个单独都能整出来一篇文章），主键索引和普通索引的存储又有所不同，如下图表示主键索引：

可以看到，在主键索引中，叶子结点保存了每一行的数据。

而在普通索引中，叶子结点保存的是主键值，当我们使用普通索引去搜索数据的时候，先在叶子结点中找到主键，再拿着主键去主键索引中查找数据，相当于做了两次查找，这也就是我们平常所说的回表操作。

3.2 原理分析

不知道小伙伴们有没有注意过，我们学习 MySQL 的时候，count 函数是归在聚合函数那一类的，就是 avg、sum 等，count 函数和这些归在一起，说明它也是一个聚合函数。

既然是聚合函数，那么就需要对返回的结果集进行一行行的判断，这里就涉及到一个问题，返回的结果是啥？我们分别来看：

对于 select count(1) from user;

Avertissement : La discussion suivante est basée sur le moteur de stockage InnoDB. Je parlerai de MyISAM séparément à la fin de l'article en raison de la situation particulière. [Recommandations associées : mysql video tutoriel]🎜🎜 Permettez-moi de commencer par la conclusion :Il n’y a pas beaucoup de différence de performances entre les deux. 🎜

1. Pratique

🎜J'ai préparé un tableau avec 1 million de données. La structure du tableau est la suivante : 🎜rrreee🎜Comme vous pouvez le voir. , il existe un index de clé primaire. 🎜🎜Comptons le nombre d'enregistrements dans le tableau de deux manières, comme suit : 🎜🎜🎜🎜On peut voir que l'efficacité d'exécution des deux SQL est en fait presque la même, les deux sont de 0,14 s. 🎜🎜Regardons les deux autres statistiques : 🎜🎜🎜🎜id est la clé primaire, le nom d'utilisateur et l'adresse sont des champs ordinaires. 🎜🎜On peut voir que l'utilisation de l'identifiant pour les statistiques présente certains avantages. Brother Song, parce que l'échantillon de données de test est relativement petit, l'effet n'est pas évident. Les amis peuvent augmenter la quantité de données de test, la différence sera alors plus évidente. 🎜🎜Alors, quelle est la raison de cette différence? Analysons-la brièvement ensuite. 🎜

2. expliquer l'analyse

🎜Utilisons d'abord expliquer pour examiner ces différents plans d'exécution SQL : 🎜🎜🎜🎜Vous pouvez voir que les plans d'exécution des trois premières méthodes statistiques sont identiques, les deux derniers sont les mêmes. 🎜🎜Je vais comparer les différents éléments de l'explication avec vous ici : 🎜

• type : La valeur de type des trois premiers est index, ce qui signifie une analyse complète de l'index, c'est-à-dire qu'il suffit de parcourir l'intégralité de l'index (Notez que l'index n'est pas la table entière) ; la valeur de type des deux derniers est tout, ce qui signifie une analyse complète de la table, c'est-à-dire que l'index ne sera pas utilisé.
• Clé : cela signifie que MySQL décide quel index utiliser pour optimiser l'accès à la table. PRIMARY signifie utiliser l'index de clé primaire, et NULL signifie qu'aucun index n'est utilisé.
• key_len : ceci représente la longueur de clé utilisée par MySQL. Étant donné que notre type de clé primaire est INT et non nul, la valeur est 4.
• Extra : utiliser l'index dans cela signifie que l'optimiseur n'a besoin que d'accéder à l'index pour obtenir les données requises (pas besoin de renvoyer la table).

🎜Grâce à l'explication, nous pouvons en fait voir grossièrement que l'efficacité d'exécution des trois premières méthodes statistiques est plus élevée (en raison de l'utilisation d'index), tandis que l'efficacité statistique des deux dernières est relativement faible. On dit qu'il est inférieur (aucun index n'est utilisé, une analyse complète de la table est requise). 🎜🎜L’analyse ci-dessus ne suffit pas à elle seule. Analysons-la d’un point de vue principe. 🎜

3. Analyse des principes

🎜3.1 Index de clé primaire et index ordinaire🎜🎜Avant de commencer l'analyse des principes, j'aimerais vous amener à jeter un œil à l'arbre B+, qui est important pour nous pour comprendre le contenu suivant. 🎜🎜Tout le monde sait que la structure de stockage des index dans InnoDB est un arbre B+ (quant à ce qu'est un arbre B+ et quelle est la différence entre un arbre B+ et un arbre B, cet article n'en discutera pas. Ces deux-là seuls peuvent être un article), Le stockage de l'index de clé primaire et de l'index ordinaire est différent. La figure suivante montre l'index de clé primaire : 🎜🎜🎜🎜Vous pouvez voir que dans l'index de clé primaire, les nœuds feuilles enregistrent les données de chaque ligne. 🎜🎜Dans un index ordinaire, les nœuds feuilles stockent la valeur de la clé primaire. Lorsque nous utilisons un index ordinaire pour rechercher des données, nous trouvons d'abord la clé primaire dans le nœud feuille, puis utilisons la clé primaire pour rechercher les données dans. l'index de clé primaire. C'est tout à fait Yu qui a effectué deux recherches, ce que nous appelons habituellement l'opération de retour de table. 🎜🎜3.2 Analyse des principes🎜🎜Je ne sais pas si vous avez remarqué que lorsque nous apprenions MySQL, la fonction de comptage was Ceux qui entrent dans la catégorie des fonctions d'agrégation sont avg, sum, etc. La fonction de comptage est regroupée avec celles-ci, indiquant qu'il s'agit également d'une fonction d'agrégation. 🎜🎜 Puisqu'il s'agit d'une fonction d'agrégation, il est nécessaire de juger l'ensemble de résultats renvoyé ligne par ligne. Cela implique une question : quel est le résultat renvoyé ? Examinons-les séparément : 🎜🎜Pour la requête select count(1) from user;, le moteur InnoDB trouvera un arbre d'index minimum à parcourir (pas nécessairement l'index de clé primaire), mais il le fera non Il lira les données, mais lorsqu'il lit un nœud feuille, il renvoie 1, et finalement les résultats sont accumulés. 🎜

Pour la requête select count(id) from user;, le moteur InnoDB parcourra l'intégralité de l'index de clé primaire, puis lira l'identifiant et le renverra, mais comme l'identifiant est la clé primaire, il est au nœud feuille de l'arborescence B+, ce processus n'implique donc pas d'E/S aléatoires (il ne nécessite pas d'opérations telles que le retour à la table pour obtenir des données de la page de données), et les performances sont également correctes. select count(id) from user;  这个查询来说，InnoDB 引擎会遍历整个主键索引，然后读取 id 并返回，不过因为 id 是主键，就在 B+ 树的叶子节点上，所以这个过程不会涉及到随机 IO（并不需要回表等操作去数据页拿数据），性能也是 OK 的。

对于 select count(username) from user;  这个查询来说，InnoDB 引擎会遍历整张表做全表扫描，读取每一行的 username 字段并返回，如果 username 在定义时候设置了 not null，那么直接统计 username 的个数；如果 username 在定义的时候没有设置 not null，那么就先判断一下 username 是否为空，然后再统计。

最后再来说说 select count(*) from user; ，这个 SQL 的特殊之处在于它被 MySQL 优化过，当 MySQL 看到 count(*) 就知道你是想统计总记录数，就会去找到一个最小的索引树去遍历，然后统计记录数。

因为主键索引（聚集索引）的叶子节点是数据，而普通索引的叶子节点则是主键值，所以普通索引的索引树要小一些。然而在上文的案例中，我们只有主键索引，所以最终使用的就是主键索引。

现在，如果我修改上面的表，为 username 字段也添加索引，然后我们再来看 explain select count(*) from user; 的执行计划：

可以看到，此时使用的索引就是 username 索引了，和我们前面的分析结果是一致的。

从上面的描述中我们就可以看出，第一个查询性能最高，第二个次之（因为需要读取 id 并返回），第三个最差（因为需要全表扫描），第四个的查询性能则接近第一个。

4. MyISAM 呢？

可能有小伙伴知道，MyISAM 引擎中的 select count(*) from user;

Pour la requête select count(username) from user;, le moteur InnoDB parcourra toute la table pour effectuer une analyse complète de la table, lira le champ du nom d'utilisateur de chaque ligne et le renverra si le nom d'utilisateur est. défini lorsque Si non null est défini, alors le nombre de noms d'utilisateur sera compté directement ; si le nom d'utilisateur n'est pas défini sur null lors de la définition, déterminez d'abord si le nom d'utilisateur est vide, puis comptez.

Parlons enfin de select count(*) from user;. La particularité de ce SQL est qu'il a été optimisé par MySQL lorsque MySQL voit count(*) Sachez simplement que si vous souhaitez compter le nombre total d'enregistrements, vous trouverez un arbre d'index minimum à parcourir, puis comptez le nombre d'enregistrements. <p></p> <blockquote>Parce que les nœuds feuilles de l'index de clé primaire (index clusterisé) sont des données, tandis que les nœuds feuilles de l'index ordinaire sont les valeurs de clé primaire, donc l'arbre d'index de l'index ordinaire est plus petit. Cependant, dans le cas ci-dessus, nous n'avons que l'index de clé primaire, donc l'index de clé primaire est finalement utilisé. <p></p> </blockquote>Maintenant, si je modifie le tableau ci-dessus et ajoute un index au champ du nom d'utilisateur, alors nous examinerons le plan d'exécution de <code>explain select count(*) from user ; :

Vous pouvez voir que le L'index est l'index du nom d'utilisateur, ce qui est cohérent avec nos résultats d'analyse précédents.

Nous pouvons voir dans la description ci-dessus que la première requête a les performances les plus élevées, la seconde est la deuxième (car elle doit lire l'identifiant et les retours), la troisième est la pire (car elle doit analyser toute la table) , et le quatrième Les performances des requêtes du second sont proches de celles du premier.

4. Et MyISAM ?

Certains amis savent peut-être que l'opération select count(*) from user; dans le moteur MyISAM est très rapide, car MyISAM stocke directement le nombre de lignes dans la table. il est sur le disque, il peut être lu directement en cas de besoin, donc c'est très rapide. La raison pour laquelle le moteur MyISAM fait cela est principalement parce qu'il ne prend pas en charge les transactions, donc ses statistiques sont en fait très simples, il suffit d'ajouter une ligne d'enregistrements. 🎜🎜Mais notre InnoDB couramment utilisé ne peut pas faire cela ! Pourquoi? Parce qu'InnoDB prend en charge les transactions ! Afin de prendre en charge les transactions, InnoDB introduit le contrôle de concurrence multi-version MVCC, il peut donc y avoir des problèmes tels que des lectures sales, des lectures fantômes et des lectures non répétables lors de la lecture des données 🎜🎜🎜Pour plus de détails, veuillez vous référer à : https://. /www.bilibili.com/video/BV14L4y1B7mB🎜🎜🎜Donc, InnoDB doit supprimer chaque ligne de données et déterminer si la ligne de données est visible pour la session en cours. Si elle est visible, la ligne de données sera comptée. , sinon il ne sera pas compté. 🎜🎜Bien sûr, MVCC dans MySQL est en fait un très grand sujet. Brother Song vous présentera MVCC en détail lorsqu'il sera gratuit à l'avenir. 🎜🎜D'accord, les amis, comprenez-vous maintenant ? Si vous avez des questions, veuillez laisser un message pour en discuter. 🎜🎜Pour plus de connaissances sur la programmation, veuillez visiter : 🎜Vidéos de programmation🎜 ! ! 🎜

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!