count(*)為什麼很慢？原因分析

count(*)為什麼很慢？以下這篇文章就來跟大家分析一下原因，並聊聊count(*)的執行過程，希望對大家有幫助！

本來沒想著寫這篇文章的，因為我覺得這個東西大多數有經驗的開發遇到過，肯定也了解相關的原因，但最近我看到有幾個關注的技術公眾號在推播相關的文章。實在令我吃驚！

先上公眾號文章的結論：

• count(*) ：它會取得所有行的數據，不做任何處理，行數加1。
• count(1)：它會取得所有行的數據，每行固定值1，也是行數加1。
• count(id)：id代表主鍵，它需要從所有行的資料中解析出id字段，其中id肯定都不為NULL，行數加1。
• count(普通索引列)：它需要從所有行的資料中解析出普通索引列，然後判斷是否為NULL，如果不是NULL，則行數 1。
• count(未加索引列)：它會全表掃描取得所有數據，解析中未加索引列，然後判斷是否為NULL，如果不是NULL，則行數 1。

結論：count(*) ≈ count(1) > count(id) > count(普通索引列) > count(未加索引列)

我也不想賣關子了，以上結論純屬放屁。根本就是個人yy出來的東西，甚至不願意去驗證一下，就算看一眼執行計劃，也得不出這麼離譜的結論。

我不敢相信這是一篇被多個技術公眾號轉載的文章！

以下所有的內容都是基於，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(無索引)

這4種情況，與count(*)的執行計畫有何不同。

• count(1)

• count(id) 對於範例表來說是,主鍵是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个字：回表。

好比去買砂糖橘，如果你只買幾斤，那麼你隨便挑籃裡面好的就行。但如果你要買一筐，我相信老闆不會讓你在裡面一個個挑，而是一次給你一整筐，當然大家都不傻，都知道筐裡面一定有那麼幾個壞果子。但是這樣效率最高，對老闆來說損失更小。

執行過程

摘抄自《從根上理解mysql》。我強烈推薦沒有系統學過mysql的，看看這本書。

1.首先在server層維護一個count變數

2.server層向InnoDB引擎要第一筆記錄

3.InnoDB找到第一個二級索引記錄，並回傳至server層（注意：由於此時只是統計記錄數量，所以不需要回表）

4.由於COUNT函數的參數是*，MySQL會將*當作常數0處理。由於0並不是NULL，server層給count變數加1。

5.server層向InnoDB要下一筆記錄。

6.InnoDB透過二級索引記錄的next_record屬性找到下一筆二級索引記錄，並傳回給server層。

7.server層繼續為count變數加1。

8.重複上述過程，直到InnoDB向server層傳回沒記錄可查的訊息。

9.server層將最終的count變數的值傳送到客戶端。

總結

寫完後還是心中挺鬱悶的，現在能從公眾號獲取到的好文章越來越少了，現在已經是知識付費的時代了。

挺懷念剛工作的時候，那時候每天上午都花點時間看看公眾號文章，現在全都是廣告。哎！

不過也正常，誰也不能一直為愛發電。

學習還是建議多看書籍，一般能成書的都不會太差。現在晚上能搜到的都是千篇一律的文章，對錯不知。網路上

【相關推薦：mysql影片教學】

以上是count(*)為什麼很慢？原因分析的詳細內容。更多資訊請關注PHP中文網其他相關文章！