《Mysql必讀MySQL 5.7 InnoDB對(duì)COUNT(*)的優(yōu)化》要點(diǎn)：
本文介紹了Mysql必讀MySQL 5.7 InnoDB對(duì)COUNT(*)的優(yōu)化，希望對(duì)您有用。如果有疑問，可以聯(lián)系我們。

飽受詬病的InnoDB表COUNT(*)性能問題在5.7下做了優(yōu)化,果真如此嗎?

1、經(jīng)典需求：InnoDB表COUNT(*)

InnoDB引擎表經(jīng)常被抱怨執(zhí)行COUNT(*)的效率太差,因此此類需求通常會(huì)被建議用其他方法來滿足,比如另外加一個(gè)計(jì)數(shù)器表,或者用SHOW TABLE STATUS查看大概數(shù)量.

不過,從MySQL 5.7.2起,這個(gè)問題得到了解決,我們來看看.

2、MySQL 5.7版本InnoDB對(duì)COUNT(*)的優(yōu)化

MySQL每發(fā)布一個(gè)新版本,都會(huì)放出相應(yīng)的Release Notes,我們注意到5.7.2版本的發(fā)布說明中提到：

1. InnoDB:?SELECT?COUNT(*)?FROM?t?statements?now?invoke?a?single?handler?call?to?the?storage?engine?to?scan?the?clustered?index?and?return?the?row?count?to?the?Optimizer.?Previously,?a?row?count?was?typically?performed?by?traversing?a?smaller?secondary?index?and?invoking?a?handler?call?for?each?record.?A?single?handler?call?to?the?storage?engine?to?count?rows?in?the?clustered?index?generally?improves?SELECT?COUNT(*)?FROM?t?performance.?However,?in?the?case?of?a?large?clustered?index?and?a?significantly?smaller?secondary?index,?performance?degradation?is?possible?compared?to?performance?using?the?previous,?non-optimized?implementation.?For?more?information,?see?Limits?on?InnoDB?Tables.?

簡(jiǎn)單地說就是：COUNT(*)會(huì)選擇聚集索引,進(jìn)行一次內(nèi)部handler函數(shù)調(diào)用,即可快速獲得該表總數(shù).我們可以通過執(zhí)行計(jì)劃看到這個(gè)變化,例如：



很明顯,在查詢優(yōu)化器階段就已經(jīng)得到優(yōu)化了,相比效率應(yīng)該杠杠的吧,我們稍后再來對(duì)比看看.

補(bǔ)充說下,5.7以前的版本中,COUNT(*)請(qǐng)求通常是：掃描普通索引來獲得這個(gè)總數(shù).也來看看5.6下的執(zhí)行計(jì)劃是怎樣的：



可以看到,可以利用覆蓋索引來完成COUNT(*)請(qǐng)求.MYSQL學(xué)習(xí)

3、對(duì)比測(cè)試

先看一組測(cè)試數(shù)據(jù)：

可以看到,兩次數(shù)據(jù)量相當(dāng),但SQL耗時(shí)5.7約只有5.6的1/5,這個(gè)效率還是不錯(cuò)的吧.

我們來看看5.6和5.7版本下的status和profiling對(duì)比情況：



4、別高興得太早

看完上面的對(duì)比測(cè)試,相信您已經(jīng)心動(dòng)了吧,但還別高興得太早哦,官方文檔里其實(shí)埋了一個(gè)伏筆：

1. InnoDB:?SELECT?COUNT(*)?FROM?t?statements?now?invoke?a?single?handler?call?to?the?storage?engine?to?scan?the?clustered?index?and?return?the?row?count?to?the?Optimizer.?Previously,?a?row?count?was?typically?performed?by?traversing?a?smaller?secondary?index?and?invoking?a?handler?call?for?each?record.?A?single?handler?call?to?the?storage?engine?to?count?rows?in?the?clustered?index?generally?improves?SELECT?COUNT(*)?FROM?t?performance.?However,?in?the?case?of?a?large?clustered?index?and?a?significantly?smaller?secondary?index,?performance?degradation?is?possible?compared?to?performance?using?the?previous,?non-optimized?implementation.?For?more?information,?see?Limits?on?InnoDB?Tables.?

簡(jiǎn)言之,就是說如果聚集索引較大(或者說表數(shù)據(jù)量較大),沒有完全加載到buffer pool中的話,有可能反而會(huì)更慢,還不如用原先的方式.

下面我們來測(cè)試下,讀取tpcc測(cè)試表stock,該表有1億行記錄,表空間文件約65GB,而innodb buffer pool只分配了12G,這時(shí)候再看下對(duì)比數(shù)據(jù)：

count(*)對(duì)比測(cè)試MYSQL學(xué)習(xí)	MySQL 5.6.33MYSQL學(xué)習(xí)	MySQL 5.7.15MYSQL學(xué)習(xí)	相差MYSQL學(xué)習(xí)
表數(shù)據(jù)量MYSQL學(xué)習(xí)	1億MYSQL學(xué)習(xí)	1億MYSQL學(xué)習(xí)	0.00%MYSQL學(xué)習(xí)
耗時(shí)(秒)MYSQL學(xué)習(xí)	693.66MYSQL學(xué)習(xí)	5331.69MYSQL學(xué)習(xí)	768.63%MYSQL學(xué)習(xí) ?

在這種情況下,5.7版本反而慢的夸張,悲劇啊~

那么在5.7下的大表,有沒有辦法仍舊采用以前的方法來做COUNT(*)統(tǒng)計(jì)呢.當(dāng)然可以了,我們可以強(qiáng)制指定普通索引,不過還需要加上WHERE條件,否則還是不行.后來搜了下,發(fā)現(xiàn)這是個(gè)bug,印風(fēng)(zhaiwx)已經(jīng)報(bào)告給官方了,bug id：81854.



這次的SQL執(zhí)行耗時(shí)和在5.6下的就基本一樣了.

4、后記

5.7版本整體挺贊的,不過還是有不少地方需要完善,期待能更成熟起來.

參考
1. MySQL 5.7.2 Release Notes：http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-2.html
2. Limits on InnoDB Tables：http://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/5723.html