Pourquoi count(*) est-il si lent ? Analyse des causes

Pourquoi count(*) est-il si lent ? L'article suivant analysera les raisons pour vous et parlera du processus d'exécution de count(*).

Je ne voulais pas écrire cet article, car je pense que la plupart des développeurs expérimentés ont rencontré ce problème et doivent avoir compris les raisons qui y sont liées, mais récemment j'ai vu quelques publics techniques inquiets qui publient des articles connexes. Cela m'a vraiment surpris !

Arrivons d'abord à la conclusion de article sur le compte public :

• count(*) : il obtiendra les données de toutes les lignes sans aucun traitement, et le nombre de lignes sera augmenté de 1.
• count(1) : il obtiendra les données de toutes les lignes, avec une valeur fixe de 1 pour chaque ligne, qui est également le nombre de lignes plus 1.
• count(id) : id représente la clé primaire. Il doit analyser le champ id de toutes les lignes de données. L'identifiant ne doit pas être NULL et le nombre de lignes est augmenté de 1.
• count (colonne d'index ordinaire) : il doit analyser la colonne d'index ordinaire à partir des données de toutes les lignes, puis déterminer si elle est NULL. Si elle n'est pas NULL, le nombre de lignes + 1.
• count (colonne non indexée) : il analyse la table entière pour obtenir toutes les données, n'ajoute pas de colonnes indexées dans l'analyse, puis détermine si elle est NULL. Si elle n'est pas NULL, le nombre de lignes + 1.

Conclusion : count(*) ≈ count(1) > count(id) > count (colonne d'index ordinaire) > count (colonne non indexée)

Je ne veux pas être trop prétentieux, la conclusion ci-dessus est purement basé sur Fart. C'est juste quelque chose qui a été créé par une personne, et je ne veux même pas le vérifier. Même si je regarde le plan d'exécution, je ne peux pas arriver à une conclusion aussi scandaleuse.

Je n'arrive pas à croire qu'il s'agisse d'un article qui a été republié par plusieurs comptes publics techniques !

Tout le contenu suivant est basé sur l'analyse du mysql 5.7 + moteur InnoDB. mysql 5.7 + InnoDB引擎， 进行的分析。

拓展：

MyISAM 如果没有查询条件，只是简单的统计表中数据总数，将会返回的超快，因为service层中获取到表信息中的总行数是准确的，而InnoDB只是一个估值。

实例

废话不多说，先看一个例子。

以下是一张表数据量有100w，表中字段相对较短，整体数据量不算大。

CREATE TABLE `hospital_statistics_data` (
  `pk_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `id` varchar(36) COLLATE utf8mb4_general_ci NOT NULL COMMENT '外键',
  `hospital_code` varchar(36) COLLATE utf8mb4_general_ci NOT NULL COMMENT '医院编码',
  `biz_type` tinyint NOT NULL COMMENT '1服务流程  2管理效果',
  `item_code` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '考核项目编码',
  `item_name` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '考核项目名称',
  `item_value` varchar(36) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '考核结果',
  `is_deleted` tinyint DEFAULT NULL COMMENT '是否删除 0否 1是',
  `gmt_created` datetime DEFAULT NULL COMMENT '创建时间',
  `gmt_modified` datetime DEFAULT NULL COMMENT 'gmt_modified',
  `gmt_deleted` datetime(3) DEFAULT '9999-12-31 23:59:59.000' COMMENT '删除时间',
  PRIMARY KEY (`pk_id`)
) DEFAULT CHARSET=utf8mb4  COMMENT='医院统计数据';

此表初始状态只有一个聚簇索引。

以下分不同索引情况，看一下COUNT(*)的执行计划。

1）在只有一个聚簇索引的情况下看一下执行计划。

EXPLAIN select COUNT(*) from hospital_statistics_data;

结果：

关于执行计划的各个参数的含义，不在本文的讨论范围内，可自行了解。

这里只关注以下几个属性。

• type: 这里显示index，说明使用了索引。

• key：PRIMARY使用了主键索引。

• key_len: 索引长度8字节。

这里有很关键的一点：count(*)也会走索引，在当前情况下使用了聚簇索引。

好，再往下看。

2）存在一个非聚簇索引（二级索引）

给表添加一个hospital_code索引。

alter table hospital_statistics_data add index idx_hospital_code(hospital_code)

此时表中存在2个索引，主键 和 hospital_code。

同样的，再执行一下：

EXPLAIN select COUNT(*) from hospital_statistics_data;

结果：

同样的，看一下 type、key和key_len三个字段。

是不是觉得有点“神奇”。

为何索引变成刚添加的idx_hospital_code了。

先别急着想结论，再看下面一种情况。

3）存在两个非聚簇索引（二级索引）

在上面的基础上，再添加一个二级索引。

alter table hospital_statistics_data add index idx_biz_type(biz_type)

此时表中存在3个索引，主键 、hospital_code 和 biz_type。

同样的，执行一下：

EXPLAIN select COUNT(*) from hospital_statistics_data;

结果：

是不是更困惑了，索引又..又又...变了.

变成新添加的idx_biz_type。

先不说为何会产生以上的变化，继续往下分析。

在以上3个索引的基础上，分别看一下，count(1)、count(id)、count(index)、count(无索引)

Extension :

MyISAM S'il n'y a pas de conditions de requête, mais comptez simplement le nombre total de données dans la table, le retour sera ultra rapide, car le nombre total de lignes dans les informations de la table obtenues par la couche de service est exacte , alors qu'InnoDB n'est qu'une estimation .

Exemple

Sans plus tarder, regardons d'abord un exemple.

Ce qui suit est un tableau avec un volume de données de 1 million. Les champs du tableau sont relativement courts et le volume global de données n'est pas important.

select * from hospital_statistics_data where hospital_code is not null;

L'état initial de cette table n'a qu'un seul index clusterisé.

Ce qui suit est un aperçu du plan d'exécution de COUNT(*) dans différentes situations d'index.

• 1) Jetez un œil au plan d'exécution lorsqu'il n'y a qu'un seul index clusterisé. 🎜🎜rrreee🎜Résultats : 🎜🎜🎜🎜La signification de chaque paramètre du plan d'exécution n'entre pas dans le cadre de cet article, vous pouvez la comprendre vous-même. 🎜🎜Concentrez-vous uniquement sur les attributs suivants ici. 🎜🎜🎜🎜type : L'index est affiché ici, indiquant que l'index est utilisé. 🎜🎜🎜🎜key : PRIMARY utilise l'index de clé primaire. 🎜🎜🎜🎜key_len : longueur de l'index 8 octets. 🎜🎜🎜🎜Il y a ici un point très critique : count(*) utilisera également l'index, dans le cas actuel un index clusterisé est utilisé. 🎜🎜D'accord, regarde en bas. 🎜🎜🎜2) Il existe un index non clusterisé (index secondaire) 🎜🎜🎜Ajoutez un index hospital_code au tableau. 🎜rrreee🎜À l'heure actuelle, il y a deux index dans la table, primary key et hospital_code. 🎜🎜Idem, exécutez à nouveau : 🎜rrreee🎜Résultat : 🎜🎜🎜🎜De même, regardez les trois champs type, key et key_len. 🎜🎜Vous vous sentez un peu "🎜magique🎜" ? 🎜🎜Pourquoi l'index est-il devenu le idx_hospital_code nouvellement ajouté. 🎜🎜Ne vous précipitez pas d’abord vers la conclusion, puis examinez la situation suivante. 🎜🎜🎜3) Il existe deux index non clusterisés (index secondaires) 🎜🎜🎜Sur la base de ce qui précède, ajoutez un index secondaire. 🎜rrreee🎜À l'heure actuelle, il y a 3 index dans la table, clé primaire, hospital_code et biz_type. 🎜🎜De même, exécutez : 🎜rrreee🎜Résultat : 🎜🎜🎜🎜Êtes-vous encore plus confus ? L'index a... encore changé. 🎜🎜 est devenu nouveau Ajout de idx_biz_type. 🎜🎜Ne parlons pas de la raison pour laquelle les changements ci-dessus se sont produits, poursuivons l'analyse ci-dessous. 🎜🎜Sur la base des trois index ci-dessus, examinons-les respectivement : count(1), count(id), count(index)</ code> , <code>count (no index)🎜🎜Quelles sont les différences entre ces quatre situations et le plan d'exécution de count(*). 🎜🎜🎜🎜count(1)🎜🎜🎜🎜🎜🎜🎜🎜🎜count(id) Pour l'exemple de table, la clé primaire est pk_id🎜

• count(index)

这里选取biz_type索引字段。

• count(无索引)

小结：

• count(index) 会使用当前index指定的索引。

• count(无索引) 是全表扫描，未走索引。

• count(1) , count(*), count(id) 一样都会选择idx_biz_type索引

看到这，你还觉得那些千篇一律的公众号文章的结论正确吗？

必要知识点

• mysql 分为service层和引擎层。

• 所有的sql在执行前会经过service层的优化，优化分为很多类型，简单的来说可分为成本和规则。

• 执行计划所反映的是service层经过sql优化后，可能的执行过程。并非绝对（免得有些人说我只看执行计划过于片面）。绝大多数情况执行计划是可信的。

• 索引类型分为聚簇索引和非聚簇索引（二级索引）。其中数据都是挂在聚簇索引上的，非聚簇索引上只是记录的主键id。

• 抛开数据内存，只谈数据量，都是扯淡。什么500w就是极限，什么2个表以上的join都需要优化了，什么is null不会走索引等，纯纯的放屁。

• 相信一点，编写mysql代码的人比，看此文章的大部分人都要优秀。他们会尽可能在执行前，对我这样菜逼写的乱七八糟的sql进行优化。

原因分析

其实原因非常非常简单，上面也说了，service层会基于成本进行优化。

并且，正常情况下，非聚簇索引所占有的内存要远远小于聚簇索引。所以问题来了，如果你是mysql的开发人员，你在执行count(*)查询的时候会使用那个索引？

我相信正常人都会使用非聚簇索引。

那如果存在2个甚至多个非聚簇索引又该如何选择呢？

那肯定选择最短的，占用内存最小的一个呀，在回头看看上面的实例，还迷惑吗。

同样都是非聚簇索引。idx_hospital_code的len是146字节；而idx_biz_type的len只有1。那还要选吗？

那为何count(*)走了索引，却还是很慢呢？

这里要明确一点，索引只是提升效率的一种方式，但不能完全的解决效率问题。count(*)有一个明显的缺陷，就是它要计算总数，那就意味着要遍历所有符合条件的数据，相当于一个计数器，在数据量足够大的情况下，即使使用非聚簇索引也无法优化太多。

官方文档：

InnoDBhandlesSELECT COUNT(*)andSELECT COUNT(1)operations in the same way. There is no performance difference.

简单的来说就是，InnoDB下 count(*) 等价于 count(1)

既然会自动走索引，那么上面那个所谓的速度排序还觉得对吗？ count(*)的性能跟数据量有很大的关系，此外最好有一个字段长度较短的二级索引。

拓展：

另外，多说一下，关于网上说的那些索引失效的情况，大多都是片面的，我这里只说一点。量变才能引起质变，索引的失效取决于你圈定数据的范围，若你圈定的数据量占整体数据量的比例过高，则会放弃使用索引，反之则会优先使用索引。但是此规则并不是完美的，有时候可能与你预期的不同，也可以通过一些技巧强制使用索引，但这种方式少用。

举个栗子：

通过上面这个表hospital_statistics_data，我进行了如下查询：

select * from hospital_statistics_data where hospital_code is not null;

此时这个sql会使用到hospital_code的索引吗？

这里也不卖关子了，若hospital_code只有很少一部分数据是null值，那么将不会走索引，反之则走索引。

原因就2个字：回表。

C’est comme acheter des oranges sucrées. Si vous n’achetez que quelques kilos, vous pouvez simplement choisir les meilleures dans le panier. Mais si vous voulez acheter un panier, je crois que le patron ne vous laissera pas choisir un par un, mais vous donnera un panier entier d'un coup. Bien sûr, tout le monde n'est pas stupide, et ils savent tous qu'il doit y en avoir quelques-uns. mauvais fruits dans le panier. Mais c’est le plus efficace et cela cause moins de pertes au patron.

Processus d'exécution

Extrait de "Comprendre MySQL à partir de la racine". Je recommande fortement à ceux qui n'ont pas systématiquement appris MySQL de lire ce livre.

1. Conservez d'abord une variable de comptage dans la couche serveur

2. La couche serveur demande au moteur InnoDB le premier enregistrement

3. InnoDB trouve le premier enregistrement d'index secondaire et le renvoie à la couche serveur (remarque : dû). à cela Il ne compte que le nombre d'enregistrements, il n'est donc pas nécessaire de revenir au tableau)

4 ​​Puisque le paramètre de la fonction COUNT est *, MySQL traitera * comme une constante 0. Puisque 0 n'est pas NULL, la couche serveur ajoute 1 à la variable count.

5. La couche serveur demande à InnoDB le prochain enregistrement.

6.InnoDB trouve l'enregistrement d'index secondaire suivant via l'attribut next_record de l'enregistrement d'index secondaire et le renvoie à la couche serveur.

7. La couche serveur continue d'ajouter 1 à la variable count.

8. Répétez le processus ci-dessus jusqu'à ce qu'InnoDB ne renvoie aucun message enregistrable à la couche serveur.

9. La couche serveur envoie la valeur finale de la variable count au client.

Résumé

Après avoir fini d'écrire, je me sentais encore assez déprimé. Il y a de moins en moins de bons articles qui peuvent être obtenus sur les comptes publics. C'est maintenant l'ère du paiement pour la connaissance.

L'époque où j'ai commencé à travailler me manque vraiment. À cette époque, je passais du temps à lire des articles de comptes officiels tous les matins, mais maintenant ce ne sont que des publicités. Pourquoi!

Mais c’est normal, personne ne peut toujours produire de l’électricité par amour.

Il est recommandé de lire plus de livres lors des études. Généralement, ceux qui peuvent être écrits dans des livres ne sont pas trop mauvais. Maintenant, tout ce que je peux rechercher la nuit, ce sont les mêmes articles, je ne sais pas s'ils sont vrais ou faux. En ligne

【Recommandations associées : tutoriel vidéo mysql】

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!