索引

InnoDB

MySQL5.6版本之后默認(rèn)引擎是innoDB，以B+樹(shù)作為索引的數(shù)據(jù)存儲(chǔ)結(jié)構(gòu)。
B+數(shù)是以B樹(shù)為基礎(chǔ)的另一種更適合數(shù)據(jù)庫(kù)存儲(chǔ)的數(shù)據(jù)結(jié)構(gòu)。

• B樹(shù)
  B樹(shù)是一個(gè)平衡多叉數(shù)，結(jié)構(gòu)如圖

  
  
  圖1 B樹(shù)
  
  

  因?yàn)槭嵌嗖?，相比平衡二叉?shù)而言，能用更少的層存儲(chǔ)更多的數(shù)據(jù)。

• B+樹(shù)
  B+樹(shù)是B樹(shù)的進(jìn)化。區(qū)別在于，B+樹(shù)的所有數(shù)據(jù)均存儲(chǔ)在葉子節(jié)點(diǎn)上，根節(jié)點(diǎn)和子節(jié)點(diǎn)不存儲(chǔ)數(shù)據(jù)，這樣做的好處是根節(jié)點(diǎn)和子節(jié)點(diǎn)的內(nèi)容最小化，只用存儲(chǔ)索引值和指針即可。

  
  
  圖2 B+樹(shù)
  
  

  傳統(tǒng)平衡二叉樹(shù)，需要反復(fù)讀取多次才能取到葉子節(jié)點(diǎn)的數(shù)據(jù)。而因?yàn)榇疟P(pán)每次IO讀取最小均為4K數(shù)據(jù)，則會(huì)產(chǎn)生極大的浪費(fèi)。4K數(shù)據(jù)里，差不多能放1200個(gè)節(jié)點(diǎn)。所以對(duì)于一個(gè)有1200個(gè)節(jié)點(diǎn)的B+樹(shù)，存儲(chǔ)10億級(jí)別的數(shù)據(jù)也就三層結(jié)構(gòu)。磁盤(pán)讀取3次即可。

主鍵索引、非主鍵索引

像圖2，或圖3第一個(gè)這種，葉子節(jié)點(diǎn)上掛載具體數(shù)據(jù)的索引，被稱為主鍵索引。主鍵索引又叫聚簇索引

圖3 主鍵索引和非主鍵索引

假如我們要查找k=5的值，通過(guò)k的索引，我們找到k=5的數(shù)據(jù)的ID=500，然后我們?cè)僭贗D索引里查找ID=500的數(shù)據(jù)，最后找到數(shù)據(jù)R4。 這一過(guò)程被稱為回表。

從圖中可以理解到，一個(gè)表里主鍵索引只有一個(gè)，非主鍵索引可以有多個(gè)。

在大多數(shù)情況下，數(shù)據(jù)里最好包含一個(gè)自增的主鍵，也就是唯一的聚簇索引。

覆蓋索引

上文已經(jīng)講過(guò)，如果執(zhí)行select * from T where k=5這一條語(yǔ)句，會(huì)先通過(guò)k=5的索引查出需要的數(shù)據(jù)的ID=500，然后回表，在主鍵索引上查詢ID=500的值為R4。

如果是select ID from T where k=5，在第一次查詢k=5的索引時(shí)，就查出來(lái)了ID=500，此時(shí)就已經(jīng)取到了需要的數(shù)據(jù)，不需要回表，這種情況被稱為覆蓋索引。覆蓋索引是一種常用的性能優(yōu)化手段。

聯(lián)合索引，最左前綴法則。

以姓名和年齡做聯(lián)合索引

圖4 聯(lián)合索引

在圖4中，如果查詢... like 張%，那么此索引是沒(méi)問(wèn)題的，會(huì)迅速查到姓張的人。但如果是...age=10... 那么此索引無(wú)效，依然會(huì)全盤(pán)掃描數(shù)據(jù)。

索引下推

在圖4中，查詢語(yǔ)句為...name like 張% and age=10...，在MySQL5.6之前，會(huì)先查出所有姓張的，然后一個(gè)個(gè)回表判斷age是否=10。5.6引入了索引下推的優(yōu)化，簡(jiǎn)單的說(shuō)就是在查找姓張的數(shù)據(jù)的時(shí)候，直接也判斷一下聯(lián)合索引里的數(shù)據(jù)age是否等于10。避免了多余的回表。

ACID（原子性、一致性、隔離性、持久性）

隔離性

事務(wù)的隔離分級(jí)別從松要嚴(yán)可以分為讀未提交、讀提交、可重復(fù)讀、串行化。隔離越嚴(yán)，效率越低。

• 讀未提交
  讀未提交是指，一個(gè)事物還沒(méi)提交時(shí)，它做的變更就能被別的事物看到。
• 讀提交
  讀提交是指，一個(gè)事物提交后，他做的變更才能被別的事物看到。
  讀提交可以解決臟讀問(wèn)題。
• 可重復(fù)讀
  可重復(fù)讀是指，一個(gè)事物在執(zhí)行過(guò)程中看到的數(shù)據(jù)總和這個(gè)事物在開(kāi)始時(shí)看到的數(shù)據(jù)是一致的，也就是說(shuō)一個(gè)事物一旦開(kāi)始了，多次讀書(shū)數(shù)據(jù)的值均一致，即可重復(fù)讀。
  可重復(fù)讀可以解決臟讀和不可重復(fù)讀問(wèn)題。但不能解決臟讀問(wèn)題。
  MySQL默認(rèn)隔離級(jí)別為此級(jí)別。
  MySQL里，在快照讀情況下，通過(guò)MVCC等解決了不可重復(fù)讀的問(wèn)題。在當(dāng)前讀情況下，通過(guò)行鎖和間隙鎖解決不可重復(fù)讀問(wèn)題。

• 串行化
  串行化是指，對(duì)于同一行記錄，寫(xiě)會(huì)加鎖，讀也會(huì)加讀鎖。當(dāng)執(zhí)行的操作被鎖的時(shí)候，只能依次等待鎖放開(kāi)。
  串行化性能最慢，但能解決臟讀，不可重復(fù)讀，幻讀等問(wèn)題。

  
  
  圖5 事物隔離
  
  

  在讀未提交的隔離下，V1的值為2，因?yàn)榭梢宰x到事物B的未提交修改。
  在讀提交的情況下，V1的值為1，V2的值為2。因?yàn)橹挥性谔峤涣耸挛顱后事物A才能讀到數(shù)據(jù)。
  在可重復(fù)讀的情況下，V1=V2=1，V3的值為2。
  在串行化情況下，事物B執(zhí)行修改語(yǔ)句時(shí)，只能等待事物A結(jié)束，才能執(zhí)行。