0.前言

MySQL中有九種存儲(chǔ)引擎，其中主要的兩種引擎分別是：

• MyISAM
• InnoDB

MySQL在5.5版本之前的默認(rèn)引擎為MyISAM蜀漆，從5.5版本開(kāi)始默認(rèn)的存儲(chǔ)引擎為InnoDB谅河。

MyISAM不支持事務(wù)特性，而InnoDB支持事務(wù)。

• 查看引擎

  mysql>SHOW ENGINES;
  Engine              Support     Transactions
  ------------------  -------     ------------
  FEDERATED           NO          (NULL) 
  MRG_MYISAM          YES         NO
  MyISAM              YES         NO
  BLACKHOLE           YES         NO
  CSV                 YES         NO
  MEMORY              YES         NO
  ARCHIVE             YES         NO
  InnoDB              DEFAULT     YES
  PERFORMANCE_SCHEMA  YES         NO
  

• 如何查看指定數(shù)據(jù)庫(kù)的表結(jié)構(gòu)

  mysql>USE shop;
  mysql>SHOW TABLE STATUS;
  Name          Engine  Version  Row_format    Rows  Avg_row_length
  ------------  ------  -------  ----------  ------  --------------
  shop_order    InnoDB       10  Compact         23             712
  shop_product  InnoDB       10  Compact          4            4096
  shop_user     InnoDB       10  Compact          2            8192
  

在MyISAM存儲(chǔ)引擎中绷耍，會(huì)把一個(gè)表的總行數(shù)存儲(chǔ)在磁盤(pán)上吐限。而InnoDB存儲(chǔ)引擎由于多版本并發(fā)控制(MVCC)機(jī)制，對(duì)于count(*)應(yīng)該返回多少行并不確定锨天，只能把數(shù)據(jù)一行一行從引擎中讀出毯盈，然后累積計(jì)數(shù)，無(wú)法將總行數(shù)存儲(chǔ)在磁盤(pán)中病袄。

1.InnoDB引擎count(*)計(jì)數(shù)

1.1存在問(wèn)題

假設(shè)表t中現(xiàn)在有 10000 條記錄搂赋，我們?cè)O(shè)計(jì)了三個(gè)用戶并行的會(huì)話。

• 會(huì)話 A 先啟動(dòng)事務(wù)并查詢一次表的總行數(shù)益缠；
• 會(huì)話 B 啟動(dòng)事務(wù)脑奠，插入一行后記錄后，查詢表的總行數(shù)幅慌；
• 會(huì)話 C 先啟動(dòng)一個(gè)單獨(dú)的語(yǔ)句宋欺，插入一行記錄后，查詢表的總行數(shù)胰伍。

我們假設(shè)從上到下是按照時(shí)間順序執(zhí)行的挣跋，同一行語(yǔ)句是在同一時(shí)刻執(zhí)行的绪妹。

數(shù)據(jù)庫(kù)事務(wù)會(huì)話A口蝠、B镣陕、C的執(zhí)行過(guò)程

你會(huì)看到，在最后一個(gè)時(shí)刻渗饮，三個(gè)會(huì)話 A但汞、B、C 會(huì)同時(shí)查詢表 t 的總行數(shù)互站，但拿到的結(jié)果卻不同私蕾。

因?yàn)镮nnoDB引擎的默認(rèn)隔離級(jí)別是可重復(fù)讀，在代碼上就是通過(guò)多版本并發(fā)控制(MVCC)實(shí)現(xiàn)的胡桃，每一行記錄都要判斷是否對(duì)本會(huì)話(Session)是否可見(jiàn)踩叭，對(duì)于count(*)請(qǐng)求來(lái)說(shuō)，只能把數(shù)據(jù)一行一行讀出依次判斷翠胰。

1.2數(shù)據(jù)庫(kù)保存計(jì)數(shù)

由于在InnoDB的可重復(fù)讀隔離級(jí)別下懊纳，每一個(gè)會(huì)話創(chuàng)建的視圖不一定一致，導(dǎo)致count(*)計(jì)數(shù)時(shí)結(jié)果的不一致亡容，這也就是為什么InnoDB引擎不能像MyISAM引擎將表的總行數(shù)固定寫(xiě)到磁盤(pán)中(MyISAM不支持事務(wù)特性)。那么解決這一問(wèn)題的方式大致可以分為兩種：

• 利用緩存系統(tǒng)保存計(jì)數(shù)
• 利用數(shù)據(jù)庫(kù)表保存計(jì)數(shù)

其中利用緩存系統(tǒng)(如Redis)保存計(jì)數(shù)冤今，由于不能支持事務(wù)的強(qiáng)一致性闺兢，同樣會(huì)導(dǎo)致數(shù)據(jù)不一致的結(jié)果，具體例子如下：

緩存系統(tǒng)會(huì)話A、B的執(zhí)行過(guò)程

此時(shí)屋谭，會(huì)話B并不能查詢到新插入的數(shù)據(jù)R脚囊，但是計(jì)數(shù)值已經(jīng)加了1。雖然Redis能正常工作桐磁，但是這個(gè)計(jì)數(shù)值在邏輯上是不精確的悔耘。

而利用數(shù)據(jù)庫(kù)的事務(wù)特性就能實(shí)現(xiàn)邏輯上的一致性，如下圖所示

數(shù)據(jù)庫(kù)事務(wù)會(huì)話A我擂、B的執(zhí)行過(guò)程

由于InnoDB默認(rèn)隔離級(jí)別為可重復(fù)讀衬以，由于會(huì)話B在會(huì)話A未提交事務(wù)之前開(kāi)啟了事務(wù)，所以會(huì)話A開(kāi)啟的事務(wù)視圖與會(huì)話B開(kāi)啟的事務(wù)視圖是一致的校摩，同時(shí)會(huì)話A的計(jì)數(shù)值加1操作對(duì)于會(huì)話B也是不可見(jiàn)的看峻，這就能實(shí)現(xiàn)會(huì)話B查詢最近100條記錄在邏輯的一致性。

2.InnoDB引擎下count()性能比較

count()：返回對(duì)應(yīng)列值不為null的行數(shù)

2.1count(*)

由于聚簇索引下(即主鍵索引)的索引樹(shù)每個(gè)葉子節(jié)點(diǎn)都是整行數(shù)據(jù)衙吩，而非聚簇索引的索引樹(shù)葉子節(jié)點(diǎn)存儲(chǔ)的是索引值互妓，所以Server層優(yōu)化器對(duì)于count()進(jìn)行計(jì)數(shù)時(shí)，會(huì)選擇非聚簇索引中最小的索引樹(shù)來(lái)遍歷坤塞。*并不取值進(jìn)行判斷冯勉。

2.2count(1)

InnoDB引擎會(huì)遍歷整張表，但不取值摹芙。Server層對(duì)于返回的每一行灼狰，放入一個(gè)數(shù)字”1“進(jìn)去。判斷不可能為空瘫辩，按行累加伏嗜。

2.3count(主鍵id)

InnoDB 引擎會(huì)遍歷整張表，把每一行的 id 值都取出來(lái)伐厌，返回給 server 層承绸。server 層拿到 id 后，判斷是不可能為空的挣轨，就按行累加军熏。

count(1) 執(zhí)行得要比 count(主鍵 id) 快。因?yàn)?strong>從引擎返回 id 會(huì)涉及到解析數(shù)據(jù)行卷扮，以及拷貝字段值的操作荡澎。

2.4count(字段)

1. 如果這個(gè)“字段”是定義為 not null 的話，一行行地從記錄里面讀出這個(gè)字段晤锹，判斷不能為 null摩幔，按行累加；
2. 如果這個(gè)“字段”定義允許為 null鞭铆，那么執(zhí)行的時(shí)候或衡，判斷到有可能是 null，還要把值取出來(lái)再判斷一下，不是 null 才累加封断。

查找效率：count(*) ≈ count(1) > count(主鍵id) >= count(字段)