Warum ist count(*) so langsam? Ursachenanalyse

Warum ist count(*) so langsam? Der folgende Artikel wird die Gründe für Sie analysieren und über den Ausführungsprozess von count (*) sprechen. Ich hoffe, dass er für alle hilfreich ist!

Ich wollte diesen Artikel nicht schreiben, weil ich denke, dass die meisten erfahrenen Entwickler auf dieses Problem gestoßen sind und die damit verbundenen Gründe verstanden haben müssen, aber kürzlich habe ich ein paar besorgte technische Öffentlichkeitsarbeit gesehen. Nr. drängt auf verwandte Artikel. Es hat mich wirklich überrascht!

Kommen wir zunächst zum Fazit des Artikels zum öffentlichen Konto:

• count(*): Es werden die Daten aller Zeilen ohne Verarbeitung abgerufen und die Anzahl der Zeilen wird um 1 erhöht.
• count(1): Es werden die Daten aller Zeilen abgerufen, mit einem festen Wert von 1 für jede Zeile, was auch der Anzahl der Zeilen plus 1 entspricht.
• count(id): id stellt den Primärschlüssel dar. Es muss das ID-Feld aus allen Datenzeilen analysiert werden. Die ID darf nicht NULL sein und die Anzahl der Zeilen wird um 1 erhöht.
• count (normale Indexspalte): Es muss die gewöhnliche Indexspalte aus den Daten aller Zeilen analysieren und dann feststellen, ob sie NULL ist. Wenn sie nicht NULL ist, ist die Anzahl der Zeilen + 1.
• count (nicht indizierte Spalte): Durchsucht die gesamte Tabelle, um alle Daten zu erhalten, fügt der Analyse keine indizierten Spalten hinzu und bestimmt dann, ob sie nicht NULL ist, die Anzahl der Zeilen + 1.

Schlussfolgerung: count(*) ≈ count(1) > count (normale Indexspalte) > count (nicht indizierte Spalte)

Ich möchte nicht zu prätentiös sein, die obige Schlussfolgerung basiert ausschließlich auf Fart. Es ist einfach etwas, das aus einer persönlichen Perspektive kommt, und ich möchte es nicht einmal überprüfen. Selbst wenn ich mir den Ausführungsplan ansehe, kann ich nicht zu einer so unverschämten Schlussfolgerung kommen.

Ich kann nicht glauben, dass dies ein Artikel ist, der von mehreren technischen Fachpublikationen nachgedruckt wurde!

Alle folgenden Inhalte basieren auf der Analyse von mysql 5.7 + InnoDB engine. 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(无索引)

Erweiterung:

MyISAM Wenn es keine Abfragebedingungen gibt, sondern einfach die Gesamtzahl der Daten in der Tabelle zählt, erfolgt die Rückgabe sehr schnell, da die Gesamtzahl der Zeilen in den von der Serviceschicht erhaltenen Tabelleninformationen genau ist , während InnoDB nur eine Schätzung ist.

Beispiel

Schauen wir uns ohne weitere Umschweife zunächst ein Beispiel an.

Das Folgende ist eine Tabelle mit einem Datenvolumen von 1 Million. Die Felder in der Tabelle sind relativ kurz und das Gesamtdatenvolumen ist nicht groß.

select * from hospital_statistics_data where hospital_code is not null;

Der Anfangszustand dieser Tabelle hat nur einen Clustered Index.

Das Folgende ist ein Blick auf den Ausführungsplan von COUNT(*) in verschiedenen Indexsituationen.

• 1) Sehen Sie sich den Ausführungsplan an, wenn nur ein Clustered-Index vorhanden ist. 🎜🎜rrreee🎜Ergebnisse: 🎜🎜🎜🎜Die Bedeutung der einzelnen Parameter des Ausführungsplans liegt nicht im Rahmen dieses Artikels, Sie können sie selbst verstehen. 🎜🎜Konzentrieren Sie sich hier nur auf die folgenden Attribute. 🎜🎜🎜🎜Typ: Hier wird der Index angezeigt, was darauf hinweist, dass der Index verwendet wird. 🎜🎜🎜🎜key: PRIMARY verwendet den Primärschlüsselindex. 🎜🎜🎜🎜key_len: Indexlänge 8 Bytes. 🎜🎜🎜🎜Hier gibt es einen sehr kritischen Punkt: count(*) wird auch den Index verwenden, im aktuellen Fall wird ein Clustered-Index verwendet. 🎜🎜Okay, schau nach unten. 🎜🎜🎜2) Es gibt einen nicht gruppierten Index (Sekundärindex) 🎜🎜🎜Fügen Sie der Tabelle einen hospital_code-Index hinzu. 🎜rrreee🎜Zu diesem Zeitpunkt gibt es zwei Indizes in der Tabelle: Primärschlüssel und hospital_code. 🎜🎜Das Gleiche, noch einmal ausführen: 🎜rrreee🎜Ergebnis: 🎜🎜🎜🎜Sehen Sie sich in ähnlicher Weise die drei Felder Typ, Schlüssel und Schlüssellänge an. 🎜🎜Fühlst du dich ein wenig „🎜magisch🎜“? 🎜🎜Warum wurde der Index zum neu hinzugefügten idx_hospital_code? 🎜🎜Kommen Sie nicht erst zu einer voreiligen Schlussfolgerung und schauen Sie sich dann die folgende Situation an. 🎜🎜🎜3) Es gibt zwei nicht gruppierte Indizes (Sekundärindizes) 🎜🎜🎜Fügen Sie auf der Grundlage des oben Gesagten einen weiteren Sekundärindex hinzu. 🎜rrreee🎜Zu diesem Zeitpunkt gibt es 3 Indizes in der Tabelle: Primärschlüssel, hospital_code und biz_type. 🎜🎜Führen Sie in ähnlicher Weise Folgendes aus: 🎜rrreee🎜Ergebnis: 🎜🎜🎜🎜Sind Sie noch verwirrter? Der Index hat sich ... erneut geändert. 🎜🎜 ist neu geworden. idx_biz_type hinzugefügt. 🎜🎜Reden wir nicht darüber, warum die oben genannten Änderungen stattgefunden haben, sondern setzen wir die Analyse weiter unten fort. 🎜🎜Basierend auf den oben genannten drei Indizes werfen wir einen Blick auf sie: count(1), count(id), count(index)</ code> , <code>count (no index)🎜🎜Was sind die Unterschiede zwischen diesen vier Situationen und dem Ausführungsplan von count(*). 🎜🎜🎜🎜Anzahl(1)🎜🎜🎜🎜🎜🎜🎜🎜🎜Anzahl(ID) Für die Beispieltabelle ist der Primärschlüssel 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个字：回表。

Es ist, als würde man Zuckerorangen kaufen. Wenn man nur ein paar Kilogramm kauft, kann man einfach die im Korb pflücken. Aber wenn Sie einen Korb kaufen möchten, wird der Chef Sie meiner Meinung nach nicht einen nach dem anderen auswählen lassen, sondern Ihnen einen ganzen Korb auf einmal geben. Natürlich ist nicht jeder dumm und alle wissen, dass es mehrere sein müssen schlechte Früchte im Korb. Dies ist jedoch am effizientesten und verursacht dem Chef weniger Verluste.

Ausführungsprozess

Auszug aus „Understanding MySQL from the Root“. Ich empfehle dringend, dass diejenigen, die MySQL nicht systematisch erlernt haben, dieses Buch lesen.

1. Pflegen Sie zunächst eine Zählvariable in der Serverschicht

2. Die Serverschicht fragt die InnoDB-Engine nach dem ersten Datensatz

3 dazu zählt nur die Anzahl der Datensätze, sodass keine Rückkehr zur Tabelle erforderlich ist)

4 ​​Da der Parameter der COUNT-Funktion * ist, behandelt MySQL * als Konstante 0. Da 0 nicht NULL ist, addiert die Serverschicht 1 zur Zählvariablen.

5. Die Serverschicht fragt InnoDB nach dem nächsten Datensatz.

6.InnoDB findet den nächsten sekundären Indexdatensatz über das Attribut next_record des sekundären Indexdatensatzes und gibt ihn an die Serverschicht zurück.

7. Die Serverschicht addiert weiterhin 1 zur Zählvariablen.

8. Wiederholen Sie den obigen Vorgang, bis InnoDB keine aufzeichnbare Nachricht an die Serverschicht zurückgibt.

9. Die Serverschicht sendet den Endwert der Zählvariablen an den Client.

Zusammenfassung

Nachdem ich mit dem Schreiben fertig war, fühlte ich mich immer noch ziemlich deprimiert. Es gibt immer weniger gute Artikel, die man über öffentliche Konten erhalten kann.

Ich vermisse die Tage, als ich anfing zu arbeiten, wirklich. Damals habe ich jeden Morgen die Zeit damit verbracht, offizielle Account-Artikel zu lesen, aber jetzt sind sie alle Werbung. Warum!

Aber es ist normal, niemand kann immer Strom für die Liebe erzeugen.

Es wird empfohlen, beim Lernen mehr Bücher zu lesen, diejenigen, die in Bücher geschrieben werden können, sind nicht so schlecht. Jetzt kann ich nachts nur noch nach denselben Artikeln suchen, ich weiß nicht, ob sie richtig oder falsch sind. Online

【Verwandte Empfehlungen: MySQL-Video-Tutorial】

Das obige ist der detaillierte Inhalt vonWarum ist count(*) so langsam? Ursachenanalyse. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!