Analysieren Sie count(*) in MySQL wirklich schneller als count(1)?

Ist count(*) in MySQL wirklich schneller als count(1)? Der folgende Artikel vergleicht count(*) und count(1) in MySQL, um ihre Leistungsunterschiede zu sehen. Ich hoffe, er wird Ihnen helfen!

Jemand hat mir heute gesagt, dass count(1) schneller ist als count(*) in MySQL. Kann ich das tolerieren? Du musst mit ihm Schluss machen. 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;

Haftungsausschluss: Die folgende Diskussion basiert auf der InnoDB-Speicher-Engine. Aufgrund der besonderen Situation werde ich am Ende des Artikels gesondert darauf eingehen. [Verwandte Empfehlungen: MySQL-Video-Tutorial]🎜🎜 Lassen Sie mich mit der Schlussfolgerung beginnen:Es gibt keinen großen Leistungsunterschied zwischen den beiden. 🎜

1. Übung

🎜Ich habe eine Tabelle mit 1 Million Datenstücken vorbereitet: 🎜rrreee🎜Wie Sie sehen können , es gibt einen Primärschlüsselindex. 🎜🎜Lassen Sie uns die Anzahl der Datensätze in der Tabelle auf zwei Arten zählen, wie folgt: 🎜🎜🎜🎜Es ist ersichtlich, dass die Ausführungseffizienz der beiden SQLs tatsächlich fast gleich ist, beide betragen 0,14 Sekunden. 🎜🎜Schauen wir uns die anderen beiden Statistiken an: 🎜🎜🎜🎜id ist der Primärschlüssel, Benutzername und Adresse sind gewöhnliche Felder. 🎜🎜Es ist ersichtlich, dass die Verwendung von IDs für Statistiken einige Vorteile hat. Bruder Song, da die Testdatenstichprobe relativ klein ist, ist der Effekt nicht offensichtlich. Freunde können die Testdatenmenge erhöhen, dann wird der Unterschied offensichtlicher. 🎜🎜Was genau ist der Grund für diesen Unterschied? Lassen Sie uns ihn als nächstes kurz analysieren. 🎜

2. EXPLAIN-Analyse

🎜Lass uns zuerst EXPLAIN verwenden, um uns diese verschiedenen SQL-Ausführungspläne anzusehen: 🎜🎜🎜🎜Sie können sehen, dass die Ausführungspläne der ersten drei statistischen Methoden gleich sind. die letzten beiden sind gleich. 🎜🎜Ich werde die verschiedenen Elemente in der Erklärung hier mit Ihnen vergleichen: 🎜

• Typ: Der Typwert der ersten drei ist Index, was einen vollständigen Indexscan bedeutet, d. h. einfach den gesamten Index durchgehen (Beachten Sie, dass der Index nicht die gesamte Tabelle ist); der Typwert der letzten beiden ist all, was bedeutet, dass der Index nicht vollständig gescannt wird gebraucht.
• Schlüssel: Dies bedeutet, dass MySQL entscheidet, welcher Index zur Optimierung des Zugriffs auf die Tabelle verwendet wird. PRIMÄR bedeutet, dass der Primärschlüsselindex verwendet wird, und NULL bedeutet, dass kein Index verwendet wird.
• key_len: Dies stellt die von MySQL verwendete Schlüssellänge dar. Da unser Primärschlüsseltyp INT und nicht Null ist, ist der Wert 4.
• Extra: Die Verwendung eines Index bedeutet, dass der Optimierer nur auf den Index zugreifen muss, um die erforderlichen Daten zu erhalten (die Tabelle muss nicht zurückgegeben werden).

🎜Durch die Erklärung können wir tatsächlich grob erkennen, dass die Ausführungseffizienz der ersten drei statistischen Methoden höher ist (aufgrund der Verwendung von Indizes), während die statistische Effizienz der beiden letzteren höher ist Er soll relativ niedrig sein (es wird kein Index verwendet, ein vollständiger Tabellenscan ist erforderlich). 🎜🎜Die obige Analyse allein reicht nicht aus. Analysieren wir sie aus einer prinzipiellen Perspektive. 🎜

3. Prinzipanalyse

🎜3.1 Primärschlüsselindex und gewöhnlicher Index🎜🎜Bevor ich mit der Prinzipanalyse beginne, möchte ich Sie zu einem Blick auf den B+-Baum mitnehmen, der für uns wichtig ist, um den folgenden Inhalt zu verstehen. 🎜🎜Jeder weiß, dass die Speicherstruktur von Indizes in InnoDB ein B+-Baum ist (was ein B+-Baum ist und was der Unterschied zwischen einem B+-Baum und einem B-Baum ist, darauf wird in diesem Artikel nicht eingegangen. Diese beiden allein können es ein Artikel sein), Die Speicherung des Primärschlüsselindex und des normalen Index ist unterschiedlich. Die folgende Abbildung zeigt den Primärschlüsselindex: 🎜🎜🎜🎜Sie können sehen, dass im Primärschlüsselindex die Blattknoten die Daten jeder Zeile speichern. 🎜🎜In einem gewöhnlichen Index speichern die Blattknoten den Primärschlüsselwert. Wenn wir einen gewöhnlichen Index zum Suchen nach Daten verwenden, finden wir zuerst den Primärschlüssel im Blattknoten und verwenden dann den Primärschlüssel, um nach den Daten darin zu suchen Der Primärschlüsselindex ist ziemlich groß. Yu hat zwei Suchvorgänge durchgeführt, was wir normalerweise als Tabellenrückgabe-Operation bezeichnen. 🎜🎜3.2 Prinzipanalyse🎜🎜Ich weiß nicht, ob Ihnen das aufgefallen ist, als wir MySQL lernten, die Zählfunktion Was Zu den Aggregatfunktionen gehören „Durchschnitt“, „Summe“ usw. Die Zählfunktion wird zusammen mit diesen gruppiert, was darauf hinweist, dass es sich ebenfalls um eine Aggregatfunktion handelt. 🎜🎜Da es sich um eine Aggregatfunktion handelt, ist es notwendig, die zurückgegebene Ergebnismenge Zeile für Zeile zu beurteilen. Dabei stellt sich die Frage: Was ist das zurückgegebene Ergebnis? Schauen wir sie uns separat an: 🎜🎜Für die Abfrage select count(1) from user; findet die InnoDB-Engine einen minimalen Indexbaum zum Durchlaufen (nicht unbedingt den Primärschlüsselindex), aber das wird sie nicht Es werden Daten gelesen, aber wenn es einen Blattknoten liest, gibt es 1 zurück und schließlich werden die Ergebnisse akkumuliert. 🎜

Für die Abfrage select count(id) from user; durchläuft die InnoDB-Engine den gesamten Primärschlüsselindex, liest dann die ID und gibt sie zurück, aber da die ID der Primärschlüssel ist, ist sie es auf dem Blattknoten des B+-Baums, daher umfasst dieser Prozess keine zufälligen E/A (es sind keine Vorgänge wie die Rückkehr zur Tabelle erforderlich, um Daten von der Datenseite abzurufen), und die Leistung ist ebenfalls in Ordnung. 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;

Für die Abfrage select count(username) from user; durchläuft die InnoDB-Engine die gesamte Tabelle, um einen vollständigen Tabellenscan durchzuführen, liest das Benutzernamenfeld jeder Zeile und gibt es zurück, wenn der Benutzername lautet definiert, wenn nicht null festgelegt ist, wird die Anzahl der Benutzernamen direkt gezählt. Wenn Benutzername bei der Definition nicht auf Null gesetzt ist, ermitteln Sie zunächst, ob Benutzername leer ist, und zählen Sie dann.

Lassen Sie uns abschließend über select count(*) from user; sprechen. Das Besondere an diesem SQL ist, dass es von MySQL optimiert wurde, wenn count(*)angezeigt wird > Beachten Sie einfach, dass Sie, wenn Sie die Gesamtzahl der Datensätze zählen möchten, einen minimalen Indexbaum zum Durchlaufen finden und dann die Anzahl der Datensätze zählen müssen.

Da die Blattknoten des Primärschlüsselindex (Clustered-Index) Daten sind, während die Blattknoten des gewöhnlichen Index die Primärschlüsselwerte sind, ist der Indexbaum des gewöhnlichen Index kleiner. Im obigen Fall haben wir jedoch nur den Primärschlüsselindex, sodass letztendlich der Primärschlüsselindex verwendet wird.

Wenn ich nun die obige Tabelle ändere und einen Index zum Feld „Benutzername“ hinzufüge, sehen wir uns den Ausführungsplan von explain select count(*) from user; an:

Sie können das sehen Der Index ist der Benutzernamenindex, der mit unseren vorherigen Analyseergebnissen übereinstimmt.

Aus der obigen Beschreibung können wir ersehen, dass die erste Abfrage die höchste Leistung aufweist, die zweite die zweite ist (weil sie die ID lesen und zurückgeben muss) und die dritte die schlechteste ist (weil sie die gesamte Tabelle scannen muss). ) und der vierte Die Abfrageleistung des zweiten liegt nahe an der ersten.

4. Was ist mit MyISAM?

Einige Freunde wissen vielleicht, dass die Operation select count(*) from user; in der MyISAM-Engine sehr schnell ist. Das liegt daran, dass MyISAM die Anzahl der Zeilen in der Tabelle direkt speichert Es befindet sich auf der Festplatte und kann bei Bedarf direkt gelesen werden, sodass es sehr schnell ist. Der Grund, warum die MyISAM-Engine dies tut, liegt hauptsächlich darin, dass sie keine Transaktionen unterstützt. Daher sind ihre Statistiken eigentlich sehr einfach. Fügen Sie einfach eine Zeile mit Datensätzen hinzu. 🎜🎜Aber unsere häufig verwendete InnoDB kann das nicht! Warum? Weil InnoDB Transaktionen unterstützt! Um Transaktionen zu unterstützen, führt InnoDB die MVCC-Parallelitätskontrolle für mehrere Versionen ein. Daher kann es beim Lesen von Daten zu Problemen wie fehlerhaften Lesevorgängen, Phantomlesevorgängen und nicht wiederholbaren Lesevorgängen kommen. Weitere Informationen finden Sie unter: https:/ /www.bilibili.com/video/BV14L4y1B7mB🎜🎜🎜InnoDB muss also jede Datenzeile herausnehmen und feststellen, ob die Datenzeile für die aktuelle Sitzung sichtbar ist , sonst wird es nicht gezählt. 🎜🎜Natürlich ist MVCC in MySQL tatsächlich ein sehr großes Thema. Bruder Song wird Ihnen MVCC ausführlich vorstellen, wenn er in Zukunft Zeit hat. 🎜🎜Okay, Freunde, habt ihr es jetzt verstanden? Wenn Sie Fragen haben, hinterlassen Sie bitte eine Nachricht zur Diskussion. 🎜🎜Weitere Kenntnisse zum Thema Programmierung finden Sie unter: 🎜Programmiervideos🎜! ! 🎜

Das obige ist der detaillierte Inhalt vonAnalysieren Sie count(*) in MySQL wirklich schneller als count(1)?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!