MySQL中聚合函數(shù)count的使用和性能優(yōu)化技巧

本文的環(huán)境是Windows 10，MySQL版本是5.7.12-log

一、 基本使用

count的基本作用是有兩個(gè)：

• 統(tǒng)計(jì)某個(gè)列的數(shù)據(jù)的數(shù)量；
• 統(tǒng)計(jì)結(jié)果集的行數(shù)；

用來(lái)獲取滿足條件的數(shù)據(jù)的數(shù)量。但是其中有一些與使用中印象不同的情況，比如當(dāng)count作用一列、多列、以及使用*來(lái)表達(dá)整行產(chǎn)生的效果是不同的。

示例表如下：

CREATE TABLE `NewTable` (
`id` int(11) NULL DEFAULT NULL ,
`name` varchar(30) NULL DEFAULT NULL ,
`country` varchar(50) NULL DEFAULT NULL ,
`province` varchar(30) NULL DEFAULT NULL ,
`city` varchar(30) NULL DEFAULT NULL 
)ENGINE=InnoDB

1.1 不計(jì)算NULL的值

如果有NULL值，在返回的結(jié)果中會(huì)被過(guò)濾掉

select count(country) from person;

返回結(jié)果如下：

如果滿足條件的數(shù)據(jù)項(xiàng)不存在，則結(jié)構(gòu)返回0，經(jīng)常通過(guò)這種方式判斷是否有滿足條件的數(shù)據(jù)存在；返回的數(shù)據(jù)類型是bigint。

1.2 對(duì)count(*)的處理

count(*)的處理是有點(diǎn)不同的，它會(huì)返回所有數(shù)據(jù)的數(shù)量，但是不會(huì)過(guò)濾其中的NULL值，它也并不是相當(dāng)于展開成所有的列，而是直接會(huì)忽略所有的列而直接統(tǒng)計(jì)所有的行數(shù)。語(yǔ)句如下：

select count(*) from person;

返回結(jié)果如下：

當(dāng)想要返回所有的數(shù)據(jù)的數(shù)量的時(shí)候，但是又不想包括全部是NULL的列，使用count(*)是不可能做到的，但是在1.1中說(shuō)到count作用于列的時(shí)候會(huì)過(guò)濾NULL，那么直接這么寫是不是對(duì)？

select count(id, `name`, country, province, city) from person;

那就錯(cuò)了，count只能作用于單列，不能作用于多列 ，所以上面的寫法是錯(cuò)誤的。

另外針對(duì)count(*)語(yǔ)句，在MyISAM存儲(chǔ)引擎中做了優(yōu)化，每個(gè)表的數(shù)據(jù)行數(shù)都會(huì)存儲(chǔ)在存儲(chǔ)引擎中，可以很快拿到；但是在事務(wù)性的存儲(chǔ)引擎中，比如InnoDB中，因?yàn)闀?huì)涉及到多個(gè)事務(wù)；

1.3 對(duì)count(distinct …)的處理

count(distinct …)會(huì)返回彼此不同但是非NULL的數(shù)據(jù)的行數(shù)。這一點(diǎn)和只使用distinct是有區(qū)別的，因?yàn)閐istinct是不過(guò)濾NULL值的，詳見(jiàn)MySQL中distinct的使用方法 。

- 如果沒(méi)有符合條件的數(shù)據(jù)則返回0；
- 該語(yǔ)句可以作用于多列，是當(dāng)各個(gè)列之間有一個(gè)不同，就認(rèn)為整行數(shù)據(jù)不同，與distinct作用于多列時(shí)效果相同；

select count(DISTINCT country) from person;

返回結(jié)果如下：

但是對(duì)于count(*)和count(distinct )兩者的結(jié)合，如下：

select count(DISTINCT *) from person;

該語(yǔ)句是錯(cuò)誤的，無(wú)法執(zhí)行，因此與select count(DISTINCT *) from person 還是有區(qū)別的。

二、 性能優(yōu)化

通常情況下，count(*)操作需要大量掃描數(shù)據(jù)表中的行，如果避免掃描大量的數(shù)據(jù)就成為優(yōu)化該語(yǔ)句的關(guān)鍵所在。針對(duì)這個(gè)問(wèn)題可以從如下兩個(gè)角度考慮。

2.1 在數(shù)據(jù)庫(kù)的層次上優(yōu)化

2.1.1 針對(duì)count(*)

在MySQL內(nèi)部已經(jīng)針對(duì)count(*)進(jìn)行了優(yōu)化，使用explain查詢?nèi)缦拢?/p>

EXPLAIN select count(*) from person;

從中可以看出該查詢沒(méi)有使用全表掃描也沒(méi)有使用索引，甚至不需要查詢數(shù)據(jù)表，在上面的示例數(shù)據(jù)庫(kù)中得知，該庫(kù)的存儲(chǔ)引擎是InnoDB ，而且其中既沒(méi)有主鍵也沒(méi)有索引。

2.2 針對(duì)單個(gè)列進(jìn)行count

查詢?nèi)缦拢?/p>

EXPLAIN select count(country) from person where id > 2;

發(fā)現(xiàn)在沒(méi)有主鍵和索引的情況下，對(duì)全表進(jìn)行了掃描。在數(shù)據(jù)中避免大量掃描數(shù)據(jù)行，一個(gè)最直接的方法使用索引：

當(dāng)對(duì)id設(shè)置為一般索引 ：INDEX abc (id) USING BTREE 。

執(zhí)行查詢?nèi)缦拢?/p>

EXPLAIN select count(country) from person where id > 2;

結(jié)果如下：

此時(shí)發(fā)現(xiàn)并沒(méi)有使用索引，仍然進(jìn)行的是全表掃描，當(dāng)執(zhí)行如下時(shí)：

EXPLAIN select count(country) from person where id > 4;

結(jié)果如下：

這是使用了索引進(jìn)行了范圍查詢，顯然比上面的要好。

但是問(wèn)題來(lái)了，為什么有時(shí)候使用索引，有時(shí)候不用索引？在上面的第一次查詢中已經(jīng)能夠檢測(cè)出可能的key但是并沒(méi)有使用？如果有知道的大神給解讀一下！

對(duì)id設(shè)置為主鍵，執(zhí)行查詢?nèi)缦拢?/p>

EXPLAIN select count(country) from person where id > 2;

結(jié)果如下：

2.2 在應(yīng)用的層次上優(yōu)化

在應(yīng)用的層次上優(yōu)化，可以考慮在系統(tǒng)架構(gòu)中引入緩存子系統(tǒng)，比如在過(guò)去中常用的Memcached，或者現(xiàn)在非常流行的Redis， 但是這樣會(huì)增加系統(tǒng)的復(fù)雜性。

mysql group by與聚合函數(shù)(sum,count等)實(shí)例

首先我們先來(lái)了解一下mysql聚合函數(shù)

mysql中一種特殊的函數(shù):聚合函數(shù)，SUM, COUNT, MAX, MIN, AVG等。這些函數(shù)和其它函數(shù)的根本區(qū)別就是它們一般作用在多條記錄上。例如：

SELECT SUM(score) FROM table

這個(gè)sql的意思是查詢表table里面所有score列的總和。

接著我們通過(guò)一個(gè)實(shí)例來(lái)講解group by語(yǔ)句中如何使用聚合函數(shù)。

book表如下：