MySQL統(tǒng)計總數(shù)就用count，別花里胡哨的《死磕MySQL系列 十》

有一個問題是這樣的統(tǒng)計數(shù)據(jù)總數(shù)用count(*)、count(主鍵ID)、count(字段)、count(1)那個效率高。

先說結論，不用那么花里胡哨遇到統(tǒng)計總數(shù)全部使用count(*).

但是有很多小伙伴就會問為什么呢？本期文章就解決大家的為什么。

文章目錄

系列文章

一、不同存儲引擎的做法

二、Mysql對count(*)做了什么優(yōu)化

三、不同count的用法

五、總結

系列文章

五分鐘，讓你明白Mysql是怎么選擇索引《死磕MySQL系列 六》

字符串可以這樣加索引，你知嗎？《死磕MySQL系列 七》

無法復現(xiàn)的“慢”SQL《死磕MySQL系列 八》

什么？還在用delete刪除數(shù)據(jù)《死磕MySQL系列 九》

一、不同存儲引擎的做法

你需要知道的是在不同的存儲引擎下，MySQL對于使用count(*)返回結果的流程是不一樣的。

在Myisam中，每張表的總行數(shù)都會存儲在磁盤上，因此執(zhí)行count(*)時，是直接從磁盤拿到這個值返回，效率是非常高的。但你也要知道如果加了條件的統(tǒng)計總數(shù)返回也不會那么快的。

在Innodb引擎中，執(zhí)行count(*)，需要把數(shù)據(jù)一行一行的讀出來，然后再統(tǒng)計總數(shù)返回。

問題：為什么Innodb不跟Myisam一樣把表總數(shù)存起來呢？

這個問題就需要追溯的我們之前的MVCC文章，就是因為要實現(xiàn)多版本并發(fā)控制，才會導致Innodb不能直接存儲表總數(shù)。

因為每個事務獲取到的一致性視圖都是不一樣的，所以返回的數(shù)據(jù)總數(shù)也是不一致的。

如果你無法理解，再回到MVCC文章好好看看，意思就跟不同事務看到的數(shù)據(jù)不一致一回事。

實戰(zhàn)案例

假設這三個用戶是并行的，你會看到三個用戶看到最終的數(shù)據(jù)總數(shù)都不一致。

每個用戶會根據(jù)read view存儲的數(shù)據(jù)來判斷那些數(shù)據(jù)是自己可以看見的，那些是看不見的。

read view

當執(zhí)行SQL語句查詢時會產生一致性視圖，也就是read-view，它是由查詢的那一時間所有未提交事務ID組成的數(shù)組，和已經創(chuàng)建的最大事務ID組成的。

在這個數(shù)組中最小的事務ID被稱之為min_id，最大事務ID被稱之為max_id，查詢的數(shù)據(jù)結果要根據(jù)read-view做對比從而得到快照結果。

于是就產生了以下的對比規(guī)則，這個規(guī)則就是使用當前的記錄的trx_id跟read-view進行對比，對比規(guī)則如下。

如果落在trx_id

如果落在trx_id>max_id，表示此版本是由將來啟動的事務生成的，是肯定不可見的

若在min_id<=trx_id<=max_id時

如果row的trx_id在數(shù)組中，表示此版本是由還沒提交的事務生成的，不可見，但是當前自己的事務是可見的

如果row的trx_id不在數(shù)組中，表明是提交的事務生成了該版本，可見

二、MySQL對count(*)做了什么優(yōu)化

先來看兩個索引結構，一個是主鍵索引、另一個是普通索引。

現(xiàn)在你應該知道了，主鍵索引的葉子節(jié)點存儲的是整行數(shù)據(jù)，而普通索引葉子節(jié)點存儲的是主鍵值。

得出結論就是普通索引的比主鍵索引會小很多。

所以，MySQL對于count(*)這樣的操作，不管遍歷那個索引樹得到的結果在邏輯上都一樣。

因此，優(yōu)化器會找到最小的那棵樹來遍歷，在保證正確的邏輯前提下，盡量減少掃描數(shù)據(jù)量，是數(shù)據(jù)庫系統(tǒng)設計的通用法則之一。

問題：為什么存儲的有數(shù)據(jù)怎么不用？

這個圖的數(shù)據(jù)怎么得到的，我想你應該知道了，沒錯，就是執(zhí)行show table status \G;得來的。

那為什么innodb存儲引擎不直接使用Rows這個值呢？

還記不記得在第六期文章中，五分鐘，讓你明白MySQL是怎么選擇索引《死磕MySQL系列 六》

先不要返回去看這篇文章，看下上文圖中最后查到的數(shù)據(jù)總條數(shù)是多少。

你會發(fā)現(xiàn)這兩個統(tǒng)計的數(shù)據(jù)是不一致的，因此這個值肯定是不可以用的。

具體原因

因為Rows這個值跟索引基數(shù)Cardinality一樣，都是通過采樣統(tǒng)計的。

采樣規(guī)則

首先，會選出N個數(shù)據(jù)頁，然后統(tǒng)計每個數(shù)據(jù)頁上不同的值，最后得到一個平均值。再用這個平均值乘索引的數(shù)據(jù)頁總數(shù)得到的就是索引基數(shù)。

并且這個索引基數(shù)也不是一成不變的，會隨著數(shù)據(jù)持續(xù)增刪改，當變更的數(shù)據(jù)超過1/M時才會觸發(fā)，M值是根據(jù)MySQL參數(shù)innodb_stats_persistent得到的，設置為on是10，off是16。

在MySQL8.0這個默認值為on，也就是說當這張表的數(shù)據(jù)變更超過總數(shù)據(jù)的1/10就會重新觸發(fā)采樣統(tǒng)計。

三、不同count的用法

以下所有的結論都基于MySQL的Innodb存儲引擎。

count(主鍵ID)

innodb引擎會遍歷整張表，把每一行的ID值都那出來，然后返回給server層，server層拿到ID后，判斷不可能為空，進行累加。

count(1)

同樣遍歷整張表，但不取值，server層對返回的每一行，放一個數(shù)字1進去，判斷是不可能為空的，按行累加。

count(字段)

分為兩種情況，字段定義為not null和null

為not null時：逐行從記錄里面讀出這個字段，判斷不能為null，累加

為 null時：執(zhí)行時，判斷到有可能是null，還要把值取出來再判斷一下，不是null才累加。

count(*)

這個哥們就厲害了，不是帶了*就把所有值取出來，而是MySQL做了專門的優(yōu)化，count （ * ）肯定不是null，按行累加。

結論

按照效率的話，字段 < 主鍵ID < 1 ~ ，最好都使用count（），別花里胡哨的。

五、總結

本期文章就一句話，統(tǒng)計總數(shù)就用count(*)，別花里胡哨的。

堅持學習、堅持寫作、堅持分享是咔咔從業(yè)以來所秉持的信念。愿文章在偌大的互聯(lián)網上能給你帶來一點幫助，我是咔咔，下期見。

MySQL 數(shù)據(jù)結構

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。