公司來(lái)了一位架構(gòu)師，看我用count(*)統(tǒng)計(jì)數(shù)據(jù)總數(shù)。

對(duì)我說(shuō)，你怎么用count(*)統(tǒng)計(jì)數(shù)據(jù)，count(*)太慢了，要是把數(shù)據(jù)庫(kù)搞垮了怎么搞，用count(1)。嚇得我趕緊換成了count(1)。

count(1) 性能就比count(*)高嗎?

記得有次面試時(shí)，面試官也問(wèn)我類(lèi)似這樣的問(wèn)題，mysql統(tǒng)計(jì)數(shù)據(jù)總數(shù)count(*)和count(1)哪個(gè)效率高?

今天來(lái)聊一聊count(1)和count(*)效率問(wèn)題。

不同存儲(chǔ)引擎的性能不一樣

我們不知道，Mysql常見(jiàn)的存儲(chǔ)引擎有兩種，MyISAM和Innodb，在這兩種存儲(chǔ)引擎下，MySQL對(duì)于使用count(*)返回結(jié)果的流程是不一樣的。

• 在MyISAM引擎中，每張表的總行數(shù)是存儲(chǔ)在磁盤(pán)上，所以當(dāng)執(zhí)行count(*)時(shí)，是直接從磁盤(pán)拿到這個(gè)值返回，能夠快速返回。但要是在后面加了where查詢(xún)條件時(shí)，統(tǒng)計(jì)總數(shù)也不是像想象中那么快了。
• 在Innodb引擎中，執(zhí)行count(*)，需要將數(shù)據(jù)一行一行地讀，再統(tǒng)計(jì)總數(shù)。

看到這里，不知道你有沒(méi)有這樣的疑問(wèn)：

• 為什么Innodb引擎不像MyISAM引擎一樣把表總記錄存儲(chǔ)起來(lái)呢?

這個(gè)問(wèn)題問(wèn)得好，回答這個(gè)問(wèn)題前，我們先了解下MVCC。

什么是MVCC

全稱(chēng)：Multi-Version Concurrency Control 即多版本并發(fā)控制，MVCC 是一種并發(fā)控制的方法，一般在數(shù)據(jù)庫(kù)管理系統(tǒng)中，實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)的并發(fā)訪問(wèn);在編程語(yǔ)言中實(shí)現(xiàn)事務(wù)內(nèi)存。

MVCC 在 MySQL InnoDB 中的實(shí)現(xiàn)主要是為了提高數(shù)據(jù)庫(kù)并發(fā)性能，用更好的方式去處理讀-寫(xiě)沖突，做到即使有讀寫(xiě)沖突時(shí)，也能做到不加鎖，非阻塞并發(fā)讀。

就是因?yàn)橐獙?shí)現(xiàn)多版本并發(fā)控制，所以才導(dǎo)致Innodb不能直接存儲(chǔ)表總記錄數(shù)。

因?yàn)槊總€(gè)事務(wù)獲取到的一致性視圖都是不一樣的，所以返回的數(shù)據(jù)總記錄也是不一致的。

舉個(gè)例子說(shuō)明下：

假如有一張用戶(hù)表tb_user, 有三處正在查詢(xún)用戶(hù)的總數(shù)。

select count(*) from tb_user

這時(shí)候每次查到的用戶(hù)數(shù)總數(shù)可能不太一樣。

這是因?yàn)槊總€(gè)用戶(hù)會(huì)根據(jù)read view存儲(chǔ)的數(shù)據(jù)來(lái)判斷哪些數(shù)據(jù)是自己可見(jiàn)的，哪些是不可見(jiàn)的。

read view

當(dāng)執(zhí)行SQL語(yǔ)句查詢(xún)時(shí)會(huì)產(chǎn)生一致性視圖，即read-view，它是由查詢(xún)的那一刻所有未提交事務(wù)ID組成的數(shù)組，和已經(jīng)創(chuàng)建的最大事務(wù)ID組成的。

在這個(gè)數(shù)組中最小的事務(wù)ID被稱(chēng)之為min_id，最大事務(wù)ID被稱(chēng)之為max_id，而查詢(xún)的數(shù)據(jù)結(jié)果就是根據(jù)read-view做對(duì)比從而得到快照。

于是就產(chǎn)生了以下的對(duì)比規(guī)則，這個(gè)規(guī)則就是使用當(dāng)前的記錄的trx_id跟read-view進(jìn)行對(duì)比，規(guī)則如下：

• 如果落在trx_id<min_id，表示該版本是已經(jīng)提交的事務(wù)生成的，由于事務(wù)已經(jīng)提交所以數(shù)據(jù)是可見(jiàn)的
• 如果落在trx_id>max_id，表示該版本是由將來(lái)啟動(dòng)的事務(wù)生成的，是不可見(jiàn)的
• 如果落在trx_id 在min_id 和max_id 中間(min_id<=trx_id<=max_id)時(shí)

要是row的trx_id在數(shù)組中，表示該版本是由還沒(méi)提交的事務(wù)生成的，不可見(jiàn)，但是當(dāng)前自己的事務(wù)是可見(jiàn)的;要是row的trx_id不在數(shù)組中，表明是提交的事務(wù)生成了該版本，是可見(jiàn)的。

讀到這，相信你已經(jīng)知道Innodb引擎為什么不像MyISAM引擎一樣把表總記錄存儲(chǔ)起來(lái)了吧。因?yàn)?InnoDB 支持事務(wù)，MyISAM不支持事務(wù)。

在執(zhí)行count(*)操作的時(shí)候還是做了優(yōu)化的。

mysql對(duì)count(*)做了優(yōu)化

InnoDB是索引組織表，主鍵索引樹(shù)的葉子節(jié)點(diǎn)是數(shù)據(jù)，而普通索引樹(shù)的葉子節(jié)點(diǎn)是主鍵值。所以，普通索引樹(shù)比主鍵索引樹(shù)小很多。對(duì)于count(*)這樣的操作，遍歷哪個(gè)索引樹(shù)得到的結(jié)果邏輯上都是一樣的。因此，MySQL優(yōu)化器會(huì)找到最小的那棵樹(shù)來(lái)遍歷。

如果你使用過(guò)show table status 命令的話(huà)，就會(huì)發(fā)現(xiàn)這個(gè)命令的輸出結(jié)果里面也有一個(gè)rows值用于顯示這個(gè)表當(dāng)前有多少行。

那么是不是這個(gè)rows值就能代替count(*)了嗎?

其實(shí)不能，rows這個(gè)是從從采樣估算得來(lái)的，因此它也是不是準(zhǔn)確。不準(zhǔn)確到什么程度，官方文檔說(shuō)是在40%到50%。所以show table status命令顯示的行數(shù)rows是不能直接使用。

基于MySQL的Innodb存儲(chǔ)引擎，統(tǒng)計(jì)表的總記錄數(shù)下面這4種做法，哪種效率最高?

實(shí)踐案例，準(zhǔn)備了一張有 500W多條數(shù)據(jù)的表，表結(jié)構(gòu)如下：

CREATE TABLE `tb_user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL ,
`user_name` varchar(100) DEFAULT NULL ,
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `userId` (`user_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

可以看到，這張表有一個(gè)主鍵索引，用不同方式來(lái)查詢(xún)?cè)摫碛脩?hù)記錄總數(shù)。

count(主鍵id)

用select count(*) from tb_user 耗時(shí)0.739s

InnoDB引擎會(huì)遍歷整張表，把每一行的id值都取出來(lái)，返回給server層。server層拿到id后，判斷是不可能為空的，就按行累加。

count(1)

用select count(1) from tb_user 耗時(shí)0.753s

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

count(字段)

用select count(user_name) from tb_user 耗時(shí)1.436s

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

• 為not null時(shí)：逐行從記錄里面讀出這個(gè)字段，判斷不能為null，累加
• 為 null時(shí)：執(zhí)行時(shí)，判斷到有可能是null，還要把值取出來(lái)再判斷一下，不是null才累加

count(*)

用select count(*) from tb_user 耗時(shí)0.739s

需要注意的是，并不是帶了*就把所有值取出來(lái)，而是mysql做了專(zhuān)門(mén)的優(yōu)化，count(*)肯定不是null，按行累加。

從上面的執(zhí)行結(jié)果，得知count(字段)<count(主鍵id)<count1≈count(*)

總結(jié)

基于MySQL的Innodb存儲(chǔ)引擎，統(tǒng)計(jì)表的總記錄數(shù)按照效率排序的話(huà)count(字段)<count(主鍵id)<count1≈count(*)

效率最高是count(*),并不是count(1)

所以建議盡量使用count()。

如果有面試官問(wèn)你mysql中count(*)和count(1)哪個(gè)效率高?你就可以明確地告訴他，Innodb存儲(chǔ)引擎下效率最高是count(*)。