Was ist das Leistungsprinzip von MySQL COUNT(*)-MySQL-Tutorial-php.cn

1. Welches ist schneller, COUNT(1), COUNT(*) oder COUNT(field)?

Ausführungseffekt:

COUNT(*)MySQL-Paarcount(*) wurde optimiert. count(*) scannt den Primärschlüsselindexdatensatz direkt, ohne alle Felder zu entfernen, und akkumuliert sie direkt zeilenweise. 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

Nach dem Login kopieren

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

Nach dem Login kopieren

实验开始：

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

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

Nach dem Login kopieren

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

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)

Nach dem Login kopieren

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)

Nach dem Login kopieren

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)

Nach dem Login kopieren

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

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)

Nach dem Login kopieren

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)

Nach dem Login kopieren

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)

Nach dem Login kopieren

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

Nach dem Login kopieren

实验结果

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)

Nach dem Login kopieren

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

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

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

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

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

COUNT(1)Die InnoDB-Engine durchläuft die gesamte Tabelle, übernimmt jedoch keine Werte für jede Zeile zurückgegeben. „1“ geht hinein und zählt Zeile für Zeile hoch.

#🎜🎜##🎜🎜#COUNT(field)Wenn dieses „Feld“ als NOT NULL definiert ist, liest die InnoDB-Engine Zeile für Zeile aus dem Datensatz Für dieses Feld bestimmt die Serverschicht, dass es nicht NULL sein darf und zeilenweise akkumuliert wird. Wenn die „Feld“-Definition NULL zulässt, liest die InnoDB-Engine dieses Feld zeilenweise aus dem Datensatz und entnimmt dann den Wert und beurteilen Sie erneut, ob es NULL ist. Es summiert sich einfach. #🎜🎜#

Experimentelle Analyse

#🎜🎜##🎜🎜#Die zum Testen in diesem Artikel verwendete Umgebung: #🎜🎜##🎜🎜#rrreee#🎜🎜 ##🎜 🎜#Die Testdatenbank verwendet InnoDB (Speicher-Engine verwendet InnoDB, andere Parameter sind Standard): #🎜🎜##🎜🎜#rrreee#🎜🎜##🎜🎜#Experiment startet: #🎜🎜##🎜🎜# rrreee#🎜🎜 ##🎜🎜# Lasst uns separat experimentieren: #🎜🎜##🎜🎜##🎜🎜#COUNT(1) hat 4,19 Sekunden gedauert#🎜🎜#rrreee#🎜🎜#COUNT(*)Es hat 4,16 Sekunden gedauert#🎜🎜#rrreee#🎜🎜#COUNT(field)Es hat 4,23 Sekunden gedauert#🎜🎜#rrreee#🎜🎜#Wir können wiederkommen Testen Sie den Ausführungsplan#🎜🎜##🎜🎜#COUNT(*)#🎜🎜#rrreee#🎜🎜#COUNT(1)#🎜🎜#rrreee#🎜 🎜#COUNT(field)#🎜🎜#rrreee#🎜🎜##🎜🎜#Es ist zu beachten, dass, wenn in COUNT# ein Nicht-Primärschlüsselfeld vorhanden ist, #🎜🎜##🎜🎜# rrreee

Experimentergebnisse

#🎜🎜##🎜🎜##🎜🎜#1 Aus dem obigen Experiment können wir schließen, dass COUNT(*) und COUNT(1 ) ist am schnellsten, gefolgt von COUNT(id). #🎜🎜##🎜🎜##🎜🎜#2.count(*) wurde vom MySQL-Abfrageoptimierer in count(0) umgeschrieben, und Der Index idx_salary ist ausgewählt. #🎜🎜##🎜🎜##🎜🎜#3. Sowohl count(1) als auch count(id) wählen den idx_salary-Index aus. #🎜🎜#

Experimentelle Schlussfolgerung

#🎜🎜#Zusammenfassung: COUNT(*)=COUNT(1)>COUNT(id)# 🎜🎜##🎜🎜##🎜🎜#In der offiziellen Dokumentation von MySQL heißt es außerdem: #🎜🎜##🎜🎜#

#🎜🎜#InnoDB behandelt SELECT COUNT(*)- und SELECT COUNT(1)-Operationen auf die gleiche Weise . Es gibt keinen Leistungsunterschied. Es gibt keinen Leistungsunterschied#🎜🎜#

#🎜🎜#Es zeigt also, dass für COUNT(1) oder COUNT(*) die MySQL-Optimierung tatsächlich zutrifft Völlig gleich, es gibt keinen Leistungsunterschied. #🎜🎜##🎜🎜#Es wird jedoch empfohlen, COUNT(*) zu verwenden, da dies die von MySQL92 definierte Standardsyntax zum Zählen von Zeilen ist. #🎜🎜##🎜🎜#2.COUNT(*) und TABLES_ROWS#🎜🎜##🎜🎜#In InnoDB können der von jeder Tabelle der MySQL-Datenbank belegte Speicherplatz und die Anzahl der in der Tabelle aufgezeichneten Zeilen geöffnet werden MySQLs information_schema Datenbank. In der Bibliothek gibt es eine TABLES-Tabelle. Die Hauptfelder von #🎜🎜# sind: #🎜🎜##🎜🎜##🎜🎜##🎜🎜##🎜🎜##🎜🎜 # TABLE_SCHEMA: #🎜🎜#Datenbankname#🎜🎜##🎜🎜##🎜🎜##🎜🎜#TABLE_NAME: #🎜🎜#Tabellenname#🎜🎜##🎜🎜## 🎜 🎜##🎜🎜#ENGINE: #🎜🎜# Die verwendete Speicher-Engine #🎜🎜##🎜🎜##🎜🎜##🎜🎜#TABLES_ROWS: #🎜🎜#Anzahl der Datensätze#🎜🎜# #🎜🎜##🎜🎜#DATA_LENGTH: Datengröße #🎜🎜# #🎜🎜##🎜🎜#INDEX_LENGTH: Indexgröße #🎜🎜# #🎜 🎜 #TABLE_ROWS wird verwendet, um anzuzeigen, wie viele Zeilen die Tabelle derzeit hat. Dieser Befehl wird sehr schnell ausgeführt. Kann dieser TABLE_ROWS count(*) ersetzen? #🎜🎜##🎜🎜##🎜🎜#Wir verwenden TABLES_ROWS, um die Anzahl der Tabellendatensätze abzufragen: #🎜🎜##🎜🎜#rrreee#🎜🎜#Sie können sehen, dass die Anzahl der Datensätze aufgrund der nicht korrekt ist InnoDB-Engine Die Zeilenanzahlen unter TABLES_ROWS sind nur ungefähre Schätzungen. #🎜🎜##🎜🎜#3. Wie wird COUNT(*) ausgeführt? #🎜🎜##🎜🎜#Als Erstes muss klargestellt werden, dass MySQL in verschiedenen Engines viele verschiedene Engines hat. ) verfügt über verschiedene Implementierungsmethoden. In diesem Artikel wird hauptsächlich der Ausführungsprozess auf der InnoDB-Engine vorgestellt. #🎜🎜##🎜🎜#In der InnoDB-Speicher-Engine count(*)Die Funktion liest zuerst die Daten in der Tabelle aus dem Speicher in den Speicherpuffer und durchsucht dann die gesamte Tabelle, um die Anzahl der Zeilendatensätze zu ermitteln. Einfach ausgedrückt handelt es sich um einen vollständigen Tabellenscan. Eine Schleife löst das Problem innerhalb der Schleife: Lesen Sie zuerst eine Zeile und entscheiden Sie dann, ob die Zeile in count enthalten ist Reihe. #🎜🎜##🎜🎜#In der MyISAM-Engine wird die Gesamtzahl der Zeilen einer Tabelle auf der Festplatte gespeichert. Wenn also count(*) ausgeführt wird, wird diese Zahl direkt zurückgegeben. Das ist sehr effizient. #🎜🎜##🎜🎜#Der Grund, warum InnoDB keine Zahlen wie MyISAM speichert, liegt darin, dass selbst bei mehreren Abfragen gleichzeitig aufgrund der Multi-Version-Parallelitätskontrolle (MVCC) wie viele Zeilen die InnoDB-Tabelle zurückgeben sollte ? Es ist auch ungewiss. InnoDB schneidet in Bezug auf Transaktionsunterstützung, Parallelität oder Datensicherheit besser ab als MyISAM. #🎜🎜#

Trotzdem hat InnoDB die Operation count(*) optimiert. InnoDB ist eine indexorganisierte Tabelle. Die Blattknoten des Primärschlüssel-Indexbaums sind Daten, während die Blattknoten des gewöhnlichen Indexbaums Primärschlüsselwerte sind. Daher ist der gewöhnliche Indexbaum viel kleiner als der Primärschlüssel-Indexbaum. Bei Operationen wie count(*) sind die beim Durchlaufen eines beliebigen Indexbaums erhaltenen Ergebnisse logisch dieselben. Daher findet der MySQL-Optimierer den kleinsten zu durchquerenden Baum. 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)
Es sollte beachtet werden, dass das, was wir in diesem Artikel besprechen, count(*) ohne Filterbedingungen ist, wenn die WHERE-Bedingung hinzugefügt wird Tabellen der MyISAM-Engine können nicht so schnell zurückgegeben werden.

4. Zusammenfassung

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

#🎜🎜#2. Die Verwendung der COUNT-Funktion wird hauptsächlich zum Zählen der Anzahl der Tabellenzeilen verwendet. Die Hauptverwendungen sind COUNT(*), COUNT(field) und COUNT(1)#🎜🎜##🎜🎜#
#🎜🎜#3 Weil COUNT(*) ist die von SQL92 definierte Standardsyntax zum Zählen von Zeilen, daher hat MySQL viele Optimierungen dafür vorgenommen. MyISAM zeichnet die Gesamtzahl der Zeilen in der Tabelle direkt separat für COUNT(*)auf > Abfrage, und InnoDB wählt beim Scannen der Tabelle den kleinsten Index aus, um die Kosten zu senken. Die Voraussetzung dieser Optimierungen ist, dass es keine bedingten WHERE- und GROUP-Abfragen gibt. #🎜🎜##🎜🎜#
#🎜🎜#4 Es gibt keinen Unterschied in der Implementierung zwischen COUNT(*) und COUNT(1) in InnoDB, und sie sind genauso effizient, aber COUNT(field) erfordert eine Nicht-NULL-Beurteilung des Feldes, sodass die Effizienz geringer ist. #🎜🎜##🎜🎜#
#🎜🎜#5. Da COUNT(*) die von SQL92 definierte Standardsyntax zum Zählen von Zeilen ist und sehr effizient ist, wird die Verwendung von COUNT(*)Fragen Sie die Anzahl der Zeilen in der Tabelle ab. #🎜🎜##🎜🎜#
#🎜🎜#6 Genau wie im vorherigen Anwendungsfall von COUNT(name) ist es während des Tabellenerstellungsprozesses notwendig, einen hohen Wert festzulegen. Achten Sie auch darauf, den Leistungsindex entsprechend den Geschäftsanforderungen zu erstellen. #🎜🎜##🎜🎜##🎜🎜#

Das obige ist der detaillierte Inhalt vonWas ist das Leistungsprinzip von MySQL COUNT(*). Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!