為啥count(*)會這麼慢?別再被公眾號誤導了!

互聯網高級架構師 發佈 2022-12-30T20:54:57.830797+00:00

本沒想著寫這篇文章的,因為我覺得這個東西大多數有經驗的開發遇到過,肯定也了解過相關的原因,但最近我看到有幾個關注的技術公眾號在推送相關的文章。實在令我吃驚!先上公眾號文章的結論:count(*) :它會獲取所有行的數據,不做任何處理,行數加1。

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

先上公眾號文章的結論:

  • 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;

結果:

關於執行計劃的各個參數的含義,不在本文的討論範圍內,可自行了解。

這裡只關注以下幾個屬性。

  1. type: 這裡顯示index,說明使用了索引。
  2. key:PRIMARY使用了主鍵索引。
  3. 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(*)的執行計劃有何區別。

1.count(1)

2.count(id) 對於樣例表來說是,主鍵是pk_id

3.count(index)

這裡選取biz_type索引欄位。

4.count(無索引)

小結:

  1. count(index) 會使用當前index指定的索引。
  2. count(無索引) 是全表掃描,未走索引。
  3. count(1) , count(*), count(id) 一樣都會選擇idx_biz_type索引

看到這,你還覺得那些千篇一律的公眾號文章的結論正確嗎?

必要知識點

  1. mysql 分為service層和引擎層。
  2. 所有的sql在執行前會經過service層的優化,優化分為很多類型,簡單的來說可分為成本和規則。
  3. 執行計劃所反映的是service層經過sql優化後,可能的執行過程。並非絕對(免得有些人說我只看執行計劃過於片面)。絕大多數情況執行計劃是可信的。
  4. 索引類型分為聚簇索引和非聚簇索引(二級索引)。其中數據都是掛在聚簇索引上的,非聚簇索引上只是記錄的主鍵id。
  5. 拋開數據內存,只談數據量,都是扯淡。什麼500w就是極限,什麼2個表以上的join都需要優化了,什麼is null不會走索引等,純純的放屁。
  6. 相信一點,編寫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變量的值發送到客戶端。

作者:微笑兔
連結:https://juejin.cn/post/7182491131651817531
來源:稀土掘金

關鍵字:

【喝的魔滴】我親身試真的有效!

2021-11-16T07:55:52.238413+00:00

妳們有因為飲食控管,卻減到別的地方嗎⋯

那天去拍攝,廠商居然問最近我是不是吃很好

嚇得我趕快運動加少吃

但…

 

體態是恢復了,我的杯卻整個空掉了!!

我媽看到我還說「哎唷,妳怎麼變飛機場~」

後來有一家專門出女性保健的廠商

跟我說他們推出新的【免動刀無痛魔D】

https://www.cashin.tw/product/000000000034393

 

是用食補的方式!不用動刀!

因為這家我認識很久了,口碑一直也都很好

也都有通過SGS檢驗,所以我蠻安心的~

吃一個禮拜後就覺得怎麼有脹脹的感覺

是不是姨媽要來了,結果沒有!

 

從小滴恢復到大E

杯杯也從空空的到現在快爆出來了XDD

要重買了(甜蜜的負擔)

原本他們送我一盒試吃,到現在我都自己回購

因為買太多了哈哈不好意思一直拿

 

有效成分濃度高、直接喝吸收又最好

跟醫美手術比,更安心便宜不痛!

用喝的就能達到醫美魔滴成果

https://www.cashin.tw/product/000000000034393

 

 

商品資訊

魔滴魅惑V-plus+


https://www.cashin.tw/product/000000000034393