高性能MySQL——count(*) 和 count(1)和count(列名)區(qū)別

摘自:

https://cloud.tencent.com/developer/article/1401567

https://mp.weixin.qq.com/s/MCFHNOQnTtJ6MGVjM3DP4A

執(zhí)行效果上：

count(*)包括了所有的列看成，相當于行數(shù)漂洋，在統(tǒng)計結(jié)果的時候站绪，不會忽略列值為NULL
count(1)包括了所有列，用1代表代碼行团赁，在統(tǒng)計結(jié)果的時候，不會忽略列值為NULL
count(列名)只包括列名那一列，在統(tǒng)計結(jié)果的時候梳侨，會忽略列值為空（這里的空不是只空字符串或者0尺上，而是表示null）的計數(shù)材蛛，即某個字段值為NULL時圆到，不統(tǒng)計。

執(zhí)行效率上：

列名為主鍵卑吭，count(1)會比count(列名)快
列名不為主鍵芽淡，count(1)會比count(列名)快
如果表多個列并且沒有主鍵，則 count(1) 的執(zhí)行效率優(yōu)于 count(*)
如果有主鍵豆赏，則 select count（主鍵）的執(zhí)行效率是最優(yōu)的
如果表只有一個字段挣菲，則 select count(*) 最優(yōu)。

count(列名)某個字段值為NULL時掷邦，不統(tǒng)計

如果問一個程序員MySQL中SELECT COUNT(1)和SELECT COUNT()有什么區(qū)別白胀，會有很多人給出這樣的答案“SELECT COUNT()”最終會轉(zhuǎn)化成“SELECT COUNT(1)，而SELECT COUNT(1)省略了轉(zhuǎn)換的這一步抚岗，所以SELECT COUNT(1)效率更高“或杠，甚至有一些面試官也會給出類似的答案。最近在看一些歷史遺留代碼宣蔚，絕大多數(shù)統(tǒng)計數(shù)量的SQL都在用SELECT COUNT(1)向抢，覺得有必要搞清楚這個問題。

首先胚委，以我們最常見的兩種數(shù)據(jù)庫表引擎MyISAM和Innodb來講笋额。

MyISAM

MyISAM在統(tǒng)計表的總行數(shù)的時候會很快，但是有個大前提篷扩，不能加有任何WHERE條件兄猩。這是因為：MyISAM對于表的行數(shù)做了優(yōu)化，具體做法是有一個變量存儲了表的行數(shù)鉴未，如果查詢條件沒有WHERE條件則是查詢表中一共有多少條數(shù)據(jù)枢冤，MyISAM可以做到迅速返回，所以也解釋了如果加WHERE條件铜秆，則該優(yōu)化就不起作用了淹真。細心的同學會發(fā)現(xiàn)，innodb的表也有這么一個存儲了表行數(shù)的變量连茧，但是很遺憾這個值是一個估計值核蘸，沒有什么實際意義。

Innodb

在該引擎下啸驯，COUNT(1)和COUNT(*)哪個快呢客扎？結(jié)論是：這倆在高版本的MySQL（5.5及以后，5.1的沒有考證）是沒有什么區(qū)別的罚斗，也就沒有COUN(1)會比COUNT(*)更快這一說了徙鱼。

WHY？這就要從COUNT()函數(shù)的具體含義說起了「み海”

COUNT()有兩個非常不同的作用：它可以統(tǒng)計某個列值的數(shù)量厌衙，也可以統(tǒng)計行數(shù)。在統(tǒng)計列值時要求列值是非空的（不統(tǒng)計NULL）绞绒。如果在COUNT()的括號中定了列或者列表達式婶希，則統(tǒng)計的就是這個表達式有值的結(jié)果數(shù)。......COUNT()的另一個作用是統(tǒng)計結(jié)果集的行數(shù)蓬衡。當MySQL確認括號內(nèi)的表達式值不可能為空時喻杈，實際上就是在統(tǒng)計行數(shù)。最簡單的就是當我們使用COUNT()的時候撤蟆，這種情況下通配符并不像我們猜想的那樣擴展成所有的列奕塑，實際上，他會忽略所有列而直接統(tǒng)計所有的行數(shù)“——《高性能MySQL》家肯。

通常龄砰，我們將第一個字段（一般是ID）作為主鍵，那么這個時候COUNT(id)實際統(tǒng)計的就是行數(shù)讨衣，因為主鍵肯定是非NULL的换棚。問題是Innodb是通過主鍵索引來統(tǒng)計行數(shù)的嗎？結(jié)論是：如果該表只有一個主鍵索引反镇，沒有任何二級索引的情況下固蚤，那么COUNT(*)和COUNT(1)都是通過通過主鍵索引來統(tǒng)計行數(shù)的。如果該表有二級索引歹茶，則COUNT(1)和COUNT(*)都會通過占用空間最小的字段的二級索引進行統(tǒng)計夕玩，也就是說雖然COUNT(id)指定了主鍵列,但是innodb不會真的去統(tǒng)計主鍵索引。

實驗

第一步

新建一張基于Innodb的表惊豺，只有一個ID主鍵燎孟，并插入5w的測試數(shù)據(jù)，建表語句如下：

CREATE TABLE `tb_news` (
  `id` bigint(21) NOT NULL AUTO_INCREMENT,
  `title` varchar(50) NOT NULL,
  `content` mediumtext NOT NULL,
  `count_ass` char(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=50001 DEFAULT CHARSET=utf8

這個時候執(zhí)行COUNT(1)和COUNT(*)可以看到解釋器的結(jié)果如下（兩者一致尸昧，所以就只截了一張圖）揩页，可以看到，兩者都用了主鍵索引進行行數(shù)的統(tǒng)計：

eub848elm8

第二步

新建一個二級索引title烹俗，之后在分別看一下COUNT(1)和COUNT(*)的解釋器結(jié)果（兩者依然完全一致）爆侣，這時已經(jīng)用二級索引進行統(tǒng)計而非主鍵索引：

3wid587yqs

第三步

在我們之前特地預留的一個小字段count_ass字段建一個索引，到這一步目前表中有三個索引：一個主鍵索引幢妄，兩個二級索引兔仰。

g1fepa2xl0

原理

目前基于磁盤的數(shù)據(jù)庫或者搜索引擎（比如Lucene）的性能瓶頸主要都是在IO階段，相比于CPU和RAM磁浇，IO操作實在太慢了斋陪，所以這類系統(tǒng)的優(yōu)化方向也都都是類似的——盡一切可能減少IO的次數(shù)（所以很多用ES的程序在性能優(yōu)化到極限的時候選擇直接上SSD）。這里統(tǒng)計行數(shù)的操作置吓，查詢優(yōu)化器的優(yōu)化方向就是選擇能夠讓IO次數(shù)最少的索引无虚，也就是基于占用空間最小的字段所建的索引（每次IO讀取的數(shù)據(jù)量是固定的，索引占用的空間越小所需的IO次數(shù)也就越少）衍锚。而Innodb的主鍵索引是聚簇索引（包含了KEY友题，除了KEY之外的其他字段值，事務ID和MVCC回滾指針）所以主鍵索引一定會比二級索引（包含KEY和對應的主鍵ID）大戴质，也就是說在有二級索引的情況下度宦，一般COUNT()都不會通過主鍵索引來統(tǒng)計行數(shù)，在有多個二級索引的情況下選擇占用空間最小的告匠。

如果說有張Innodb的表只有主鍵索引戈抄，而且記錄還比較大（比如30K），則統(tǒng)計行的操作會非常慢后专，因為IO次數(shù)會很多（這里就不做實驗截圖了划鸽，有興趣可以自己試一下）。

一個優(yōu)化方案就是預先建一個小字段并建二級索引專門用來統(tǒng)計行數(shù)戚哎，極端情況下這種優(yōu)化速度提高上千倍也是正常的裸诽。

結(jié)論

結(jié)論就是對于COUNT(1)和COUNT(*)執(zhí)行優(yōu)化器的優(yōu)化是完全一樣的，并沒有COUNT(1)會比COUNT(*)快這個說法型凳。

即count(字段)<count(主鍵 id)<count(1)≈count(*)