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

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

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

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

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

2、MySQL 5.7版本InnoDB對COUNT(*)的優化

MySQL每發布一個新版本,都會放出相應的Release Notes,我們注意到5.7.2版本的發布說明中提到：

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.?

簡單地說就是：COUNT(*)會選擇聚集索引,進行一次內部handler函數調用,即可快速獲得該表總數.我們可以通過執行計劃看到這個變化,例如：



很明顯,在查詢優化器階段就已經得到優化了,相比效率應該杠杠的吧,我們稍后再來對比看看.

補充說下,5.7以前的版本中,COUNT(*)請求通常是：掃描普通索引來獲得這個總數.也來看看5.6下的執行計劃是怎樣的：



可以看到,可以利用覆蓋索引來完成COUNT(*)請求.MYSQL學習

3、對比測試

先看一組測試數據：

可以看到,兩次數據量相當,但SQL耗時5.7約只有5.6的1/5,這個效率還是不錯的吧.

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



4、別高興得太早

看完上面的對比測試,相信您已經心動了吧,但還別高興得太早哦,官方文檔里其實埋了一個伏筆：

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.?

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

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

count(*)對比測試MYSQL學習	MySQL 5.6.33MYSQL學習	MySQL 5.7.15MYSQL學習	相差MYSQL學習
表數據量MYSQL學習	1億MYSQL學習	1億MYSQL學習	0.00%MYSQL學習
耗時(秒)MYSQL學習	693.66MYSQL學習	5331.69MYSQL學習	768.63%MYSQL學習 ?

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

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



這次的SQL執行耗時和在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

轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/5723.html