在Innodb可重複讀隔離級別下,對count(欄位)、count(主鍵 id)、count(1)、count(*)四種方案,我們應如何進行選擇?
1。原因
1。1為什麼有四個count
count定義
:count()是一個聚合函式,對於返回的結果集,一行行地判斷,
如果 count 函式的引數不是 NULL
,累計值就加 1,否則不加。最後返回累計值。
count(*)、count(主鍵 id) 和 count(1) 都表示返回滿足條件的結果集的總行數;而count(欄位),則表示返回滿足條件的資料行裡面,
引數“欄位”不為 NULL 的總個數
。
根據定義能推斷出,使用count(*)、count(主鍵 id) 和 count(1)獲得的結果是相同的,count(欄位)因為”欄位“可能不為NULL,所以結果存在與其它三個不一致的可能。
雖有4個count,其實對應相同函式。
1。2count有時很慢
很多同學有直觀感受,當表比較大時,count速度很慢,有時需要幾十秒甚至幾分鐘,但MyISAM能快速返回值。
主要原因為:
MyISAM會儲存表的具體行數,因此這段程式碼在MyISAM儲存引擎中執行,只需讀出儲存好的行數即可。
Innodb不儲存具體行數,count需遍歷全表,一行一行獲取。之所以這麼做,是因為MVCC需判斷對當前的count語句,哪些行可見,哪些行不可見,具體細節可看Innodb事務隔離性實現原理,你瞭解嗎?。
2。count流程
count操作涉及MySQL的Server層和儲存引擎層。Server負責判斷與計數, Innodb儲存引擎負責獲取資料集。
在Innodb為啥要刷髒頁?裡講過,讀取資料時,需要把資料從磁碟讀到記憶體,所以count耗時的節點有如下幾個:
將資料從磁碟讀入記憶體,這會發生了很多次I/O,
因此造成了主要的時間消耗
將資料從記憶體讀出,放入返回資料集。引擎返回資料集會涉及到解析資料行,以及複製欄位值
server層對資料集進行NULL判斷,進行計數操作
由此可分析出影響效能的幾個方面:
從磁碟讀入記憶體的資料量
是否需要將數值從記憶體中取出
server層是否需要進行NULL判斷
3。效能區別
根據以上幾點,我們看一下count(欄位)、count(主鍵 id)、count(1)、count(*)的區別。
對於 count(主鍵 id) 來說
,InnoDB 引擎會遍歷整張表,把每一行的 id 值都取出來,返回給 server 層。server 層拿到 id 後,判斷是不可能為空的,就按行累加。
對於 count(1) 來說
,InnoDB 引擎遍歷整張表,但不取值。server 層對於返回的每一行,放一個數字“1”進去,判斷是不可能為空的,按行累加。
對於 count(欄位) 來說
:
如果這個“欄位”是定義為 not null 的話,一行行地從記錄裡面讀出這個欄位,判斷不能為 null,按行累加;
如果這個“欄位”定義允許為 null,那麼執行的時候,判斷到有可能是 null,還要把值取出來再判斷一下,不是 null 才累加。
但 count(*) 是例外
,並不會把全部欄位取出來,而是專門做了最佳化,不取值。count(*)肯定不是 null,按行累加。
總結下各個操作:
操作
是否取值
是否判斷
備註
count(*)
否
否
最快
count(1)
否
是
count(主鍵id)
是
是
可能使用最小的索引樹
count(欄位)
是
是
欄位上無索引時,只能選主鍵索引
按照效率排序的話,count(欄位)<=count(主鍵 id) count(欄位)<=count(主鍵 id)是因為count(主鍵 id)極有可能選擇最小的非聚簇索引,而count(欄位)如果“欄位”沒有索引的話,只能選主鍵索引,這樣載入到記憶體的資料量就大了很多。另外兩者有可能選用同一索引樹。 當然, count(主鍵id)選用最小索引樹有一個前提,查詢語句中不包含where條件和group by條件。 其實count( )、count(1)、count(主鍵id)可做相同最佳化, 都選用最小索引樹、不取值、無需判斷 的方案,不過目前只有count( )做了這個最佳化。 大家count的時候,儘量選擇count(*)即可。 4。最佳化 count(* )已是最快的方案了,如果還不滿足條件,如何最佳化count(* )效能呢? 有兩個方案: 建立一個最小索引,這樣count(*)計算時會選擇最小索引,減少IO 利用事務,自己計數。建立計數表,增加或刪除資料時,同時更新計數表。 總結 大家對count的瞭解相對多一些,不過仍有很多小細節值得思考。 資料 innodb中count(*),count(欄位),count(主鍵id),count(1)的區別 MySQL學習筆記:count(1)、count(*)、count(欄位)的區別 MySQL 全表 COUNT(*) 簡述 在MySQL的InnoDB儲存引擎中count(*)函式的最佳化 MySQL InnoDB count()函式 最後 大家如果喜歡我的文章,可以關注我的公眾號(程式設計師麻辣燙) 我的個人部落格為:https://shidawuhen。github。io/ 往期文章回顧: 設計模式 招聘 思考 儲存 算法系列 讀書筆記 小工具 架構 網路 Go語言