MySQL是目前互聯(lián)網(wǎng)公司使用最廣的數(shù)據(jù)庫(kù)，InnoDB是MySQL使用最廣的存儲(chǔ)引擎，MyISAM和InnoDB的五項(xiàng)最佳實(shí)踐，和大家聊聊，盡量多講“為什么”。

一、關(guān)于count(*)

知識(shí)點(diǎn)：MyISAM會(huì)直接存儲(chǔ)總行數(shù)，InnoDB則不會(huì)，需要按行掃描。

潛臺(tái)詞是，對(duì)于select count(*) from t; 如果數(shù)據(jù)量大，MyISAM會(huì)瞬間返回，而InnoDB則會(huì)一行行掃描。

實(shí)踐：數(shù)據(jù)量大的表，InnoDB不要輕易select count(*)，性能消耗極大。

常見坑：只有查詢?nèi)淼目傂袛?shù)，MyISAM才會(huì)直接返回結(jié)果，當(dāng)加了where條件后，兩種存儲(chǔ)引擎的處理方式類似。

例如：t_user(uid, uname, age, sex);

• uid PK
• age index

 
 
 
 

  
  
  
  
select count(*) where age<18 and sex='F';
 
 
 
 

查詢未成年少女個(gè)數(shù)，兩種存儲(chǔ)引擎的處理方式類似，都需要進(jìn)行索引掃描。

啟示：不管哪種存儲(chǔ)引擎，都要建立好索引。

二、關(guān)于全文索引

知識(shí)點(diǎn)：MyISAM支持全文索引，InnoDB5.6之前不支持全文索引。

實(shí)踐：不管哪種存儲(chǔ)引擎，在數(shù)據(jù)量大并發(fā)量大的情況下，都不應(yīng)該使用數(shù)據(jù)庫(kù)自帶的全文索引，會(huì)導(dǎo)致小量請(qǐng)求占用大量數(shù)據(jù)庫(kù)資源，而要使用《索引外置》的架構(gòu)設(shè)計(jì)方法。

啟示：大數(shù)據(jù)量+高并發(fā)量的業(yè)務(wù)場(chǎng)景，全文索引，MyISAM也不是最優(yōu)之選。

三、關(guān)于事務(wù)

知識(shí)點(diǎn)：MyISAM不支持事務(wù)，InnoDB支持事務(wù)。

實(shí)踐：事務(wù)是選擇InnoDB非常誘人的原因之一，它提供了commit，rollback，崩潰修復(fù)等能力。在系統(tǒng)異常崩潰時(shí)，MyISAM有一定幾率造成文件損壞，這是非常煩的。但是，事務(wù)也非常耗性能，會(huì)影響吞吐量，建議只對(duì)一致性要求較高的業(yè)務(wù)使用復(fù)雜事務(wù)。

畫外音：Can't open file 'XXX.MYI'. 碰到過(guò)么?

小技巧：MyISAM可以通過(guò)lock table表鎖，來(lái)實(shí)現(xiàn)類似于事務(wù)的東西，但對(duì)數(shù)據(jù)庫(kù)性能影響較大，強(qiáng)烈不推薦使用。

四、關(guān)于外鍵

知識(shí)點(diǎn)：MyISAM不支持外鍵，InnoDB支持外鍵。

實(shí)踐：不管哪種存儲(chǔ)引擎，在數(shù)據(jù)量大并發(fā)量大的情況下，都不應(yīng)該使用外鍵，而建議由應(yīng)用程序保證完整性。

五、關(guān)于行鎖與表鎖

知識(shí)點(diǎn)：MyISAM只支持表鎖，InnoDB可以支持行鎖。

分析：

• MyISAM：執(zhí)行讀寫SQL語(yǔ)句時(shí)，會(huì)對(duì)表加鎖，所以數(shù)據(jù)量大，并發(fā)量高時(shí)，性能會(huì)急劇下降。
• InnoDB：細(xì)粒度行鎖，在數(shù)據(jù)量大，并發(fā)量高時(shí)，性能比較優(yōu)異。

實(shí)踐：網(wǎng)上常常說(shuō)，select+insert的業(yè)務(wù)用MyISAM，因?yàn)镸yISAM在文件尾部順序增加記錄速度極快。樓主的建議是，絕大部分業(yè)務(wù)是混合讀寫，只要數(shù)據(jù)量和并發(fā)量較大，一律使用InnoDB。

常見坑：

InnoDB的行鎖是實(shí)現(xiàn)在索引上的，而不是鎖在物理行記錄上。潛臺(tái)詞是，如果訪問沒有命中索引，也無(wú)法使用行鎖，將要退化為表鎖。

畫外音：Oracle的行鎖實(shí)現(xiàn)機(jī)制不同。

例如：

 
 
 
 

  
  
  
  
t_user(uid, uname, age, sex) innodb;
 
 
 
 

• uid PK
• 無(wú)其他索引

 
 
 
 

  
  
  
  
update t_user set age=10 where uid=1;
 
 
 
 

命中索引，行鎖。

 
 
 
 

  
  
  
  
update t_user set age=10 where uid != 1;
 
 
 
 

未命中索引，表鎖。

 
 
 
 

  
  
  
  
update t_user set age=10 where name='shenjian';
 
 
 
 

無(wú)索引，表鎖。

啟示：InnoDB務(wù)必建好索引，否則鎖粒度較大，會(huì)影響并發(fā)。

總結(jié)

在大數(shù)據(jù)量，高并發(fā)量的互聯(lián)網(wǎng)業(yè)務(wù)場(chǎng)景下，對(duì)于MyISAM和InnoDB

• 有where條件，count(*)兩個(gè)存儲(chǔ)引擎性能差不多
• 不要使用全文索引，應(yīng)當(dāng)使用“外置索引”的設(shè)計(jì)方案
• 事務(wù)影響性能，強(qiáng)一致性要求才使用事務(wù)
• 不用外鍵，由應(yīng)用程序來(lái)保證完整性
• 不命中索引，InnoDB也不能用行鎖

結(jié)論

在大數(shù)據(jù)量，高并發(fā)量的互聯(lián)網(wǎng)業(yè)務(wù)場(chǎng)景下，請(qǐng)使用InnoDB:

• 行鎖，對(duì)提高并發(fā)幫助很大
• 事務(wù)，對(duì)數(shù)據(jù)一致性幫助很大

這兩個(gè)點(diǎn)，是InnoDB最吸引人的地方。

【本文為專欄作者“58沈劍”原創(chuàng)稿件，轉(zhuǎn)載請(qǐng)聯(lián)系原作者】

新聞標(biāo)題：InnoDB，5項(xiàng)優(yōu)秀實(shí)踐
文章起源：http://uogjgqi.cn/article/cccpede.html