count函數(shù)是用來(lái)獲取表中滿足一定條件的記錄數(shù)猾昆,常見用法有三種,count(*),count(1),count(field)骡苞,這三種有什么區(qū)別垂蜗?在性能上有何差異楷扬?本文將通過測(cè)試案例詳細(xì)介紹和分析。
原文地址:
mytecdb.com/blogDetail.php?id=81
三者有何區(qū)別:
- count(field)不包含字段值為NULL的記錄贴见。
- count(*)包含NULL記錄烘苹。
- select(*)與select(1) 在InnoDB中性能沒有任何區(qū)別,處理方式相同片部。官方文檔描述如下:
InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.
1. 性能對(duì)比
通過案例來(lái)測(cè)試一下count(*)镣衡,count(1),count(field)的性能差異档悠,MySQL版本為5.7.19廊鸥,測(cè)試表是一張sysbench生成的表,表名sbtest1辖所,總記錄數(shù)2411645惰说,如下:
CREATE TABLE sbtest1 (
id int(11) NOT NULL AUTO_INCREMENT,
k int(11) DEFAULT NULL,
c char(120) NOT NULL DEFAULT '',
pad char(60) NOT NULL DEFAULT '',
PRIMARY KEY (id),
KEY k_1 (k)
) ENGINE=InnoDB;
測(cè)試SQL語(yǔ)句:
select count(*) from sbtest1;
select count(1) from sbtest1;
select count(id) from sbtest1;
select count(k) from sbtest1;
select count(c) from sbtest1;
select count(pad) from sbtest1;
針對(duì)count(*)、count(1)和count(id)缘回,加了強(qiáng)制走主鍵的測(cè)試吆视,如下:
select count(*) from sbtest1 force index(primary);
select count(1) from sbtest1 force index(primary);
select count(id) from sbtest1 force index(primary);
另外對(duì)不同的測(cè)試SQL,收集了profile酥宴,發(fā)現(xiàn)主要耗時(shí)都在Sending data這個(gè)階段啦吧,記錄Sending data值。
匯總測(cè)試結(jié)果:
類型 | 耗時(shí)(s) | 索引 | Sending data耗時(shí)(s) |
---|---|---|---|
count(*) | 0.47 | k_1 | 0.463624 |
count(1) | 0.46 | k_1 | 0.463242 |
count(id) | 0.52 | k_1 | 0.521618 |
count(*)強(qiáng)制走主鍵 | 0.54 | primay key | 0.538737 |
count(1)強(qiáng)制走主鍵 | 0.55 | primary key | 0.545007 |
count(id)強(qiáng)制走主鍵 | 0.60 | primary key | 0.598975 |
count(k) | 0.53 | k_1 | 0.529366 |
count(c) | 0.81 | NULL | 0.813918 |
count(pad) | 0.76 | NULL | 0.762040 |
結(jié)果分析:
- 從以上測(cè)試結(jié)果來(lái)看幅虑,count(*)和count(1)性能基本一樣丰滑,默認(rèn)走二級(jí)索引(k_1),性能最好倒庵,這也驗(yàn)證了count(*)和count(1)在InnoDB內(nèi)部處理方式一樣。
- count(id) 雖然也走二級(jí)索引(k_1)炫刷,但是性能明顯低于count(*)和count(1)擎宝,可能MySQL內(nèi)部在處理count(*)和count(1)時(shí)做了額外的優(yōu)化。
- 強(qiáng)制走主鍵索引時(shí)浑玛,性能反而沒有走更小的二級(jí)索引好绍申,InnoDB存儲(chǔ)引擎是索引組織表,行數(shù)據(jù)在主鍵索引的葉子節(jié)點(diǎn)上顾彰,走主鍵索引掃描時(shí)极阅,處理的數(shù)據(jù)量比二級(jí)索引更多,所以性能不及二級(jí)索引涨享。
- count(c)和count(pad)沒有走索引筋搏,性能最差,但是明顯count(pad)比count(c)好厕隧,因?yàn)閜ad字段類型為char(60)奔脐,小于字段c的char(120)俄周,盡管兩者性能墊底,但是字段小的性能相對(duì)更好些髓迎。
2. count(*)延伸
- 在5.7.18版本之前峦朗,InnoDB處理select count(*) 是通過掃描聚簇索引,來(lái)獲取總記錄數(shù)排龄。
- 從5.7.18版本開始波势,InnoDB掃描一個(gè)最小的可用的二級(jí)索引來(lái)獲取總記錄數(shù),或者由SQL hint來(lái)告訴優(yōu)化器使用哪個(gè)索引橄维。如果二級(jí)索引不存在艰亮,InnoDB將會(huì)掃描聚簇索引。
執(zhí)行select count(*)在大部分場(chǎng)景下性能都不會(huì)太好挣郭,尤其是表記錄數(shù)特別大的情況下迄埃,索引數(shù)據(jù)不在buffer pool里面,需要頻繁的讀磁盤兑障,性能將更差侄非。
3. count(*)優(yōu)化思路
- 一種優(yōu)化方法,是使用一個(gè)統(tǒng)計(jì)表來(lái)存儲(chǔ)表的記錄總數(shù)流译,在執(zhí)行DML操作時(shí)逞怨,同時(shí)更新該統(tǒng)計(jì)表。這種方法適用于更新較少福澡,讀較多的場(chǎng)景叠赦,而對(duì)于高并發(fā)寫操作,性能有很大影響革砸,因?yàn)樾枰l(fā)更新熱點(diǎn)記錄除秀。
- 如果業(yè)務(wù)對(duì)count數(shù)量的精度沒有太大要求,可使用show table status中的行數(shù)作為近似值算利。