正 文：

MySQL下INNODB引擎的SELECT COUNT(*)性能優(yōu)化及思考

最近有項目有高并發(fā)需求，服務器采用負載均衡，數(shù)據(jù)庫采用阿里云的RDS MYSQL，16核64G內(nèi)存，連接數(shù):16000 IOPS:14000，前幾場活動一切正常，RDS的cpu都維持在40%以內(nèi)的使用率，但是后面幾場的時候發(fā)現(xiàn)RDS的cpu達到了100%，RDS幾乎掛掉了！

排查原因，發(fā)現(xiàn)了大量慢日志：

select count(*) from `roundmember`

該表的引擎原來是myisam，同樣的程序在其他服務器(如騰訊云數(shù)據(jù)庫)運行時并沒有出現(xiàn)異常。檢查該表，在阿里云的rds上是innodb引擎，阿里云的rds for mysql目前只支持innodb引擎，不支持myisam引擎，阿里云給出的解釋是：MyISAM引擎表不支持事務，讀寫操作會相互沖突，僅支持表級別鎖。當其上的查詢或者寫入操作時間比較長的時候，會阻塞其他操作，容易導致連接堆積，而且在crash?后存在數(shù)據(jù)丟失的風險，因此RDS?for?MySQL推薦使用?Innodb?引擎。

目前RDS?for?MySQL如果導入表、新建表是MyISAM引擎或調(diào)整表引擎為MyISAM，會自動修改為Innodb引擎。

雖然從MySQL 5.5版本開始，默認引擎變成了InnoDB，但是并不代表Myisam引擎就要被淘汰，對于一些非重要數(shù)據(jù)無需事務處理而又要求高讀取速度的表，飄易倒是更愿意使用myisam引擎。阿里云應該讓用戶自己選擇，而不是代替用戶選擇。

話說回來，innodb引擎的表在使用select count的時候，如果表的總行數(shù)在1-2萬條以內(nèi)，速度應該不是瓶頸，但是一旦超過了這個值，隨著行數(shù)的增多，select count查詢效率會迅速的下降。

在本項目中，由于RDS性能較高，在roungmember表的總行數(shù)3萬條以內(nèi)的時候，select count查詢速度還不錯，沒有成為性能瓶頸，但是超過了3萬條之后，大量的慢日志就出來了，RDS CPU使用率直線飆升到100%。

此時，RDS的并發(fā)連接數(shù)在400上下，并不算太高：

本文的關(guān)鍵點來了，InnoDB引擎并不適合使用select count(*)查詢總行數(shù)。

測試表大約4.3萬行

Myisam引擎：

SELECT?SQL_NO_CACHE?COUNT(*)?FROM?`roundmember2`

耗費105微秒

innodb引擎：

耗費10335微秒

可以看出innodb引擎耗時是myisam引擎的98倍！這還是僅僅是4萬多行的數(shù)據(jù)下測試的差距，隨著記錄行的增加，這個差距會越來越大。

MyISAM會保存表的總行數(shù)，這段代碼在MyISAM存儲引擎中執(zhí)行，MyISAM只要簡單地讀出保存好的行數(shù)即可。因此，如果表中沒有使用事務之類的操作，這是最好的優(yōu)化方案。然而，innodb表不像myisam有個內(nèi)置的計數(shù)器，InnoDB存儲引擎不會保存表的具體行數(shù)，因此，在InnoDB存儲引擎中執(zhí)行這段代碼，InnoDB要掃描一遍整個表來計算有多少行。

innodb引擎：

SELECT?SQL_NO_CACHE?COUNT(*)?FROM?`roundmember`?WHERE?id>0

采用where加主鍵id查詢，耗時達到16.2ms，性能比未帶id查詢反而降低了。

在uid列上建立索引：

SELECT?SQL_NO_CACHE?COUNT(*)?FROM?`roundmember`?WHERE?uid>0

采用where加普通索引(第二索引)查詢，耗時26.2ms，性能更低了。

而這樣的結(jié)果似乎和網(wǎng)上得到的結(jié)論有一些出入：

網(wǎng)上的結(jié)論1：采用?secondary?index?查詢要比用?primary?key?查詢來的快很多。那么，為什么用?secondary?index?掃描反而比?primary?key?掃描來的要快呢？我們就需要了解innodb的

clustered?index?和?secondary?index?之間的區(qū)別了。

innodb?的?clustered?index?是把?primary?key?以及?row?data?保存在一起的，而?secondary?index?則是單獨存放，然后有個指針指向?primary?key。因此，需要進行?count(*)?統(tǒng)計表記錄總數(shù)時，利用?secondary?index?掃描起來，顯然更快。

網(wǎng)上的結(jié)論2：在第一次使用了唯一索引(u_id)的時候，InnoDB使用了唯一索引作為表的聚簇索引。而在InnoDB存儲引擎中，count(*)函數(shù)是先從內(nèi)存中讀取表中的數(shù)據(jù)到內(nèi)存緩沖區(qū)，然后掃描全表獲得行記錄數(shù)的。因此，使用唯一索引作為聚簇索引的時候，InnoDB需要先讀取110W條的數(shù)據(jù)到數(shù)據(jù)緩沖區(qū)中，這里發(fā)生了很多次I/O，因此造成了主要的時間消耗。而添加了輔助索引后，mysql在執(zhí)行查詢時會使用內(nèi)部的優(yōu)化機制：即使用輔助索引來統(tǒng)計數(shù)量。輔助索引保存的是index的值，此時只需要讀取一個字段，I/O減少了，性能就提高了。因此在InnoDB中，如果有統(tǒng)計整張表的數(shù)量的需求，可以考慮增加一個輔助索引。

InnoDB PitfallsHowever,?all?is?not?rosy?with?InnoDB.?Because?of?its?transactional?nature,?it?has?bottlenecks?of?its?own.?On?MyISAM,?doing?a?query?that?does?SELECT?COUNT(*)?FROM?{some_table},?is?very?fast,?since?MyISAM?keeps?the?information?in?the?index.

On?InnoDB,?this?info?is?not?stored?in?an?index,?and?even?the?index?and?the?data?are?kept?in?the?same?file.?So,?doing?the?same?query?on?a?table?can?incur?a?significant?performance?penalty.

To?check?what?overhead?this?has,?I?wrote?a?simple?test?benchmark?code.?I?duplicated?a?client?node?table?that?has?20,243?rows?from?MyISAM?to?InnoDB.

On?a?quiescent?AMD?64?machine?with?MySQL?server?5.0.24,?doing?a?SELECT?COUNT(*)?FROM?node?takes?0.835?milliseconds?on?MyISAM,?while?on?InnoDB?it?takes?12.292?milliseconds!

既然我們知道innodb引擎并不適合做 select count(*)查詢，那么我們回過頭來看看實際業(yè)務的需求是怎樣的？

select count(*) from `roundmember`

采用這樣的查詢是因為需要查出實時參與活動的總?cè)藬?shù)，但是這里的“實時”是否一定要實時，采用準實時，用戶會反感嗎？或者即使有一定的偏差，普通用戶能感知嗎？如果我們得出答案，這個實時的參與數(shù)只要準實時，允許一定的偏差，那么就好辦了：

select id from roundmember order by id desc limit 1

我們只要查最新的一條記錄，id是自增字段，取當前的這個id值就可以大約知道總參與人數(shù)了(注意我們的項目里并不會刪除參與記錄)。

關(guān)于innodb引擎的一些知識點：

知識點一：innodb存儲引擎的默認的行格式為compact(redundant為兼容以前的版本)，對于blob，text，varchar(8099)這樣的大字段，innodb只會存放前768字節(jié)在數(shù)據(jù)頁中，而剩余的數(shù)據(jù)則會存儲在溢出段中(發(fā)生溢出情況的時候適用)；

知識點二：innodb的塊大小默認為16kb，由于innodb存儲引擎表為索引組織表，樹底層的葉子節(jié)點為一雙向鏈表，因此每個頁中至少應該有兩行記錄，這就決定了innodb在存儲一行數(shù)據(jù)的時候不能夠超過8k(8098字節(jié))；

知識點三：使用了blob數(shù)據(jù)類型，是不是一定就會存放在溢出段中？通常我們認為blob，clob這類的大對象的存儲會把數(shù)據(jù)存放在數(shù)據(jù)頁之外，其實不然，關(guān)鍵點還是要看一個page中到底能否存放兩行數(shù)據(jù)，blob可以完全存放在數(shù)據(jù)頁中(單行長度沒有超過8098字節(jié))，而varchar類型的也有可能存放在溢出頁中(單行長度超過8098字節(jié)，前768字節(jié)存放在數(shù)據(jù)頁中)；

知識點四：5.1中的innodb_plugin引入了新的文件格式：barracuda(將compact和redundant合稱為antelope)，該文件格式擁有新的兩種行格式：compressed和dynamic，兩種格式對blob字段采用完全溢出的方式，數(shù)據(jù)頁中只存放20字節(jié)，其余的都存放在溢出段中:

知識點五：mysql在操作數(shù)據(jù)的時候，以page為單位，不管是更新，插入，刪除一行數(shù)據(jù)，都需要將那行數(shù)據(jù)所在的page讀到內(nèi)存中，然后在進行操作，這樣就存在一個命中率的問題，如果一個page中能夠相對的存放足夠多的行，那么命中率就會相對高一些，性能就會有提升；

關(guān)于innodb引擎的一些優(yōu)化建議：

1、每個字段的長度控制在768字節(jié)以內(nèi)

原因：當Innodb的存儲格式是 ROW_FORMAT=COMPACT (or ROW_FORMAT=REDUNDANT)的時候，Innodb只會存儲前768字節(jié)的長度，剩余的數(shù)據(jù)存放到“溢出頁”中。一旦采用了這種“溢出存儲”，返回數(shù)據(jù)的時候本來是順序讀取的數(shù)據(jù)，就變成了隨機讀取了，會導致性能急劇下降。并且Innodb并沒有將溢出頁(overflow page)緩存到內(nèi)存里面。

2、InnoDB 中不保存表的具體行數(shù)，也就是說，執(zhí)行select count(*) from table時，InnoDB要掃描一遍整個表來計算有多少行，但是MyISAM只要簡單的讀出保存好的行數(shù)即可。注意的是，當count(*)語句包含 where條件時，兩種表的操作有些不同，InnoDB類型的表用count(*)或者count(主鍵)，加上where col 條件。其中col列是表的主鍵之外的其他具有唯一約束索引的列。這樣查詢時速度會很快，就是可以避免全表掃描。

3、對于AUTO_INCREMENT類型的字段，InnoDB中必須包含只有該字段的索引，但是在MyISAM表中，可以和其他字段一起建立聯(lián)合索引。

4、DELETE FROM table時，InnoDB不會重新建立表，而是一行一行的刪除。

5、LOAD TABLE FROM MASTER操作對InnoDB是不起作用的，解決方法是首先把InnoDB表改成MyISAM表，導入數(shù)據(jù)后再改成InnoDB表，但是對于使用的額外的InnoDB特性(例如外鍵)的表不適用。

6、innodb要熟練使用覆蓋索引技術(shù)(建立合適的聯(lián)合索引)

select a from table_name where b 這樣的一個查詢，都知道索引應該加在b上面，查詢的處理過程：首先去檢索b索引找到與其對應的索引，然后根據(jù)索引去檢索正確的數(shù)據(jù)行。這樣一來一去就是兩次檢索，能不能通過一次檢索而得到數(shù)據(jù)呢?

如果希望通過一次檢索得到數(shù)據(jù)，那么索引上面就應該包含其索引相對的數(shù)據(jù)，這樣可能嗎?

alter table_name add index (b,a);

添加一個這樣的索引就能實現(xiàn)了, 查看是否使用了覆蓋索引; 使用 explain select ...如果 extra:?use index就表示使用了覆蓋索引。

【參考】：

總結(jié)

以上是生活随笔為你收集整理的mysql innodb count_MySQL下INNODB引擎的SELECT COUNT(*)性能优化及思考的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。