MySQL 索引設(shè)計(jì)概要

在 MySQL 中，頁的大小一般為 16KB，不過也可能是 8KB、32KB 或者其他值，這跟 MySQL 的存儲引擎對數(shù)據(jù)的存儲方式有很大的關(guān)系，無論我們是想要讀取一個(gè)頁面上的多條數(shù)據(jù)還是一條數(shù)據(jù)，都需要消耗約10ms 左右的時(shí)間（這 10ms 的一次隨機(jī)讀取是按照每秒 50 次的讀取計(jì)算得到的，其中等待時(shí)間為 3ms、磁盤的實(shí)際繁忙時(shí)間約為 6ms，最終數(shù)據(jù)頁從磁盤傳輸?shù)骄彌_池的時(shí)間為 1ms 左右，在對查詢進(jìn)行估算時(shí)并不需要準(zhǔn)確的知道隨機(jī)讀取的時(shí)間，只需要知道估算出的 10ms 就可以了。），10ms 的時(shí)間在計(jì)算領(lǐng)域其實(shí)是一個(gè)非常巨大的成本，假設(shè)我們使用腳本向裝了 SSD 的磁盤上順序?qū)懭胱止?jié)，那么在 10ms 內(nèi)可以寫入大概 3MB 左右的內(nèi)容，但是數(shù)據(jù)庫程序在 10ms 之內(nèi)只能將一頁的數(shù)據(jù)加載到數(shù)據(jù)庫緩沖池中，從這里可以看出隨機(jī)讀取的代價(jià)是巨大的。

MySQL 在執(zhí)行讀操作時(shí)，會先從數(shù)據(jù)庫的緩沖區(qū)中讀取，如果不存在與緩沖區(qū)中就會嘗試從內(nèi)存中加載頁面，如果前面的兩個(gè)步驟都失敗了，最后就只能執(zhí)行隨機(jī) IO 從磁盤中獲取對應(yīng)的數(shù)據(jù)頁。當(dāng)對應(yīng)的頁面存在于內(nèi)存時(shí)，數(shù)據(jù)庫程序就會使用內(nèi)存中的頁，這能夠?qū)?shù)據(jù)的讀取時(shí)間降低一個(gè)數(shù)量級，將 10ms 降低到 1ms；

主鍵列id在所有的 MySQL 索引中都是一定會存在的。

對于窄索引，每一個(gè)在索引中匹配到的記錄行最終都需要執(zhí)行另外的隨機(jī)讀取從聚集索引中獲得剩余的數(shù)據(jù)，如果結(jié)果集非常大，那么就會導(dǎo)致隨機(jī)讀取的次數(shù)過多進(jìn)而影響性能。在這時(shí)如何讓索引片變『薄』就是我們需要做的了。一個(gè) SQL 查詢掃描的索引片大小其實(shí)是由過濾因子決定的，也就是滿足查詢條件的記錄行數(shù)所占的比例。

『淺入淺出』MySQL 和 InnoDB

樂觀鎖不會存在死鎖的問題，但是由于更新后驗(yàn)證，所以當(dāng)沖突頻率和重試成本較高時(shí)更推薦使用悲觀鎖，而需要非常高的響應(yīng)速度并且并發(fā)量非常大的時(shí)候使用樂觀鎖就能較好的解決問題，在這時(shí)使用悲觀鎖就可能出現(xiàn)嚴(yán)重的性能問題；在選擇并發(fā)控制機(jī)制時(shí)，需要綜合考慮上面的四個(gè)方面（沖突頻率、重試成本、響應(yīng)速度和并發(fā)量）進(jìn)行選擇。

InnoDB 存儲引擎引入了意向鎖（Intention Lock），意向鎖就是一種表級鎖。有的人可能會對意向鎖的目的并不是完全的理解，我們在這里可以舉一個(gè)例子：如果沒有意向鎖，當(dāng)已經(jīng)有人使用行鎖對表中的某一行進(jìn)行修改時(shí)，如果另外一個(gè)請求要對全表進(jìn)行修改，那么就需要對所有的行是否被鎖定進(jìn)行掃描，在這種情況下，效率是非常低的；不過，在引入意向鎖之后，當(dāng)有人使用行鎖對表中的某一行進(jìn)行修改之前，會先為表添加意向互斥鎖（IX），再為行記錄添加互斥鎖（X），在這時(shí)如果有人嘗試對全表進(jìn)行修改就不需要判斷表中的每一行數(shù)據(jù)是否被加鎖了，只需要通過等待意向互斥鎖被釋放就可以了。

『淺入深出』MySQL 中事務(wù)的實(shí)現(xiàn)

事務(wù)其實(shí)就是并發(fā)控制的基本單位，事務(wù)是一個(gè)序列操作，其中的操作要么都執(zhí)行，要么都不執(zhí)行，它是一個(gè)不可分割的工作單位；

原子性

事務(wù)其實(shí)和一個(gè)操作沒有什么太大的區(qū)別，它是一系列的數(shù)據(jù)庫操作（可以理解為 SQL）的集合，如果事務(wù)不具備原子性，那么就沒辦法保證同一個(gè)事務(wù)中的所有操作都被執(zhí)行或者未被執(zhí)行了，整個(gè)數(shù)據(jù)庫系統(tǒng)就既不可用也不可信。想要保證事務(wù)的原子性，就需要在異常發(fā)生時(shí)，對已經(jīng)執(zhí)行的操作進(jìn)行回滾，而在 MySQL 中，恢復(fù)機(jī)制是通過回滾日志（undo log）實(shí)現(xiàn)的，所有事務(wù)進(jìn)行的修改都會先記錄到這個(gè)回滾日志中，然后在對數(shù)據(jù)庫中的對應(yīng)行進(jìn)行寫入。

回滾日志除了能夠在發(fā)生錯(cuò)誤或者用戶執(zhí)行ROLLBACK時(shí)提供回滾相關(guān)的信息，它還能夠在整個(gè)系統(tǒng)發(fā)生崩潰、數(shù)據(jù)庫進(jìn)程直接被殺死后，當(dāng)用戶再次啟動數(shù)據(jù)庫進(jìn)程時(shí)，還能夠立刻通過查詢回滾日志將之前未完成的事務(wù)進(jìn)行回滾，這也就需要回滾日志必須先于數(shù)據(jù)持久化到磁盤上，是我們需要先寫日志后寫數(shù)據(jù)庫的主要原因。回滾日志并不能將數(shù)據(jù)庫物理地恢復(fù)到執(zhí)行語句或者事務(wù)之前的樣子；它是邏輯日志，當(dāng)回滾日志被使用時(shí)，它只會按照日志邏輯地將數(shù)據(jù)庫中的修改撤銷掉看，可以理解為，我們在事務(wù)中使用的每一條INSERT都對應(yīng)了一條DELETE，每一條UPDATE也都對應(yīng)一條相反的UPDATE語句。

持久性

事務(wù)的持久性就體現(xiàn)在，一旦事務(wù)被提交，那么數(shù)據(jù)一定會被寫入到數(shù)據(jù)庫中并持久存儲起來。當(dāng)事務(wù)已經(jīng)被提交之后，就無法再次回滾了，唯一能夠撤回已經(jīng)提交的事務(wù)的方式就是創(chuàng)建一個(gè)相反的事務(wù)對原操作進(jìn)行『補(bǔ)償』，這也是事務(wù)持久性的體現(xiàn)之一。

當(dāng)我們在一個(gè)事務(wù)中嘗試對數(shù)據(jù)進(jìn)行修改時(shí)，它會先將數(shù)據(jù)從磁盤讀入內(nèi)存，并更新內(nèi)存中緩存的數(shù)據(jù)，然后生成一條重做日志并寫入重做日志緩存，當(dāng)事務(wù)真正提交時(shí)，MySQL 會將重做日志緩存中的內(nèi)容刷新到重做日志文件，再將內(nèi)存中的數(shù)據(jù)更新到磁盤上。

在 InnoDB 中，重做日志都是以 512 字節(jié)的塊的形式進(jìn)行存儲的，同時(shí)因?yàn)閴K的大小與磁盤扇區(qū)大小相同，所以重做日志的寫入可以保證原子性，不會由于機(jī)器斷電導(dǎo)致重做日志僅寫入一半并留下臟數(shù)據(jù)。

到現(xiàn)在為止我們了解了 MySQL 中的兩種日志，回滾日志（undo log）和重做日志（redo log）；在數(shù)據(jù)庫系統(tǒng)中，事務(wù)的原子性和持久性是由事務(wù)日志（transaction log）保證的，在實(shí)現(xiàn)時(shí)也就是上面提到的兩種日志，前者用于對事務(wù)的影響進(jìn)行撤銷，后者在錯(cuò)誤處理時(shí)對已經(jīng)提交的事務(wù)進(jìn)行重做，它們能保證兩點(diǎn)：

-- 發(fā)生錯(cuò)誤或者需要回滾的事務(wù)能夠成功回滾（原子性）；

-- 在事務(wù)提交后，數(shù)據(jù)沒來得及寫會磁盤就宕機(jī)時(shí)，在下次重新啟動后能夠成功恢復(fù)數(shù)據(jù)（持久性）；

隔離性

如果沒有數(shù)據(jù)庫的事務(wù)之間沒有隔離性，就會發(fā)生在并行事務(wù)的原子性一節(jié)中提到的級聯(lián)回滾等問題，造成性能上的巨大損失。如果所有的事務(wù)的執(zhí)行順序都是線性的，那么對于事務(wù)的管理容易得多，但是允許事務(wù)的并行執(zhí)行卻能能夠提升吞吐量和資源利用率，并且可以減少每個(gè)事務(wù)的等待時(shí)間。
在 SQL 標(biāo)準(zhǔn)中定義了四種數(shù)據(jù)庫的事務(wù)的隔離級別：READ UNCOMMITED、READ COMMITED、REPEATABLE READ和SERIALIZABLE；以上的所有的事務(wù)隔離級別都不允許臟寫入（Dirty Write），也就是當(dāng)前事務(wù)更新了另一個(gè)事務(wù)已經(jīng)更新但是還未提交的數(shù)據(jù)。

介紹三種最重要的并發(fā)控制器機(jī)制的工作原理。

1）鎖是一種最為常見的并發(fā)控制機(jī)制，在一個(gè)事務(wù)中，我們并不會將整個(gè)數(shù)據(jù)庫都加鎖，而是只會鎖住那些需要訪問的數(shù)據(jù)項(xiàng)， MySQL 和常見數(shù)據(jù)庫中的鎖都分為兩種，共享鎖（Shared）和互斥鎖（Exclusive），前者也叫讀鎖，后者叫寫鎖。讀鎖保證了讀操作可以并發(fā)執(zhí)行，相互不會影響，而寫鎖保證了在更新數(shù)據(jù)庫數(shù)據(jù)時(shí)不會有其他的事務(wù)訪問或者更改同一條記錄造成不可預(yù)知的問題。

2）通過時(shí)間戳實(shí)現(xiàn)事務(wù)的數(shù)據(jù)庫，使用時(shí)間戳實(shí)現(xiàn)事務(wù)的隔離性時(shí)，往往都會使用樂觀鎖，先對數(shù)據(jù)進(jìn)行修改，在寫回時(shí)再去判斷當(dāng)前值，也就是時(shí)間戳是否改變過，如果沒有改變過，就寫入，否則，生成一個(gè)新的時(shí)間戳并再次更新數(shù)據(jù)

3）多版本和快照隔離

通過維護(hù)多個(gè)版本的數(shù)據(jù)，數(shù)據(jù)庫可以允許事務(wù)在數(shù)據(jù)被其他事務(wù)更新時(shí)對舊版本的數(shù)據(jù)進(jìn)行讀取，很多數(shù)據(jù)庫都對這一機(jī)制進(jìn)行了實(shí)現(xiàn)；因?yàn)樗械淖x操作不再需要等待寫鎖的釋放，所以能夠顯著地提升讀的性能，MySQL 和 PostgreSQL 都對這一機(jī)制進(jìn)行自己的實(shí)現(xiàn)，也就是 MVCC，雖然各自實(shí)現(xiàn)的方式有所不同，MySQL 就通過文章中提到的回滾日志實(shí)現(xiàn)了 MVCC，保證事務(wù)并行執(zhí)行時(shí)能夠不等待互斥鎖的釋放直接獲取數(shù)據(jù)。

隔離性與原子性

在這里就需要簡單提一下在在原子性一節(jié)中遇到的級聯(lián)回滾等問題了，如果一個(gè)事務(wù)對數(shù)據(jù)進(jìn)行了寫入，這時(shí)就會獲取一個(gè)互斥鎖，其他的事務(wù)就想要獲得改行數(shù)據(jù)的讀鎖就必須等待寫鎖的釋放，自然就不會發(fā)生級聯(lián)回滾等問題了。

不過在大多數(shù)的數(shù)據(jù)庫，比如 MySQL 中都使用了 MVCC 等特性，也就是正常的讀方法是不需要獲取鎖的，在想要對讀取的數(shù)據(jù)進(jìn)行更新時(shí)需要使用SELECT ... FOR UPDATE嘗試獲取對應(yīng)行的互斥鎖，以保證不同事務(wù)可以正常工作

一致性

它的第一層意思就是對于數(shù)據(jù)完整性的約束，包括主鍵約束、引用約束以及一些約束檢查等等，在事務(wù)的執(zhí)行的前后以及過程中不會違背對數(shù)據(jù)完整性的約束，所有對數(shù)據(jù)庫寫入的操作都應(yīng)該是合法的，并不能產(chǎn)生不合法的數(shù)據(jù)狀態(tài)。我們可以將事務(wù)理解成一個(gè)函數(shù)，它接受一個(gè)外界的 SQL 輸入和一個(gè)一致的數(shù)據(jù)庫，它一定會返回一個(gè)一致的數(shù)據(jù)庫。而第二層意思其實(shí)是指邏輯上的對于開發(fā)者的要求，我們要在代碼中寫出正確的事務(wù)邏輯，比如銀行轉(zhuǎn)賬，事務(wù)中的邏輯不可能只扣錢或者只加錢，這是應(yīng)用層面上對于數(shù)據(jù)庫一致性的要求。

總結(jié)

事務(wù)的 ACID 四大基本特性是保證數(shù)據(jù)庫能夠運(yùn)行的基石，但是完全保證數(shù)據(jù)庫的 ACID，尤其是隔離性會對性能有比較大影響，在實(shí)際的使用中我們也會根據(jù)業(yè)務(wù)的需求對隔離性進(jìn)行調(diào)整，除了隔離性，數(shù)據(jù)庫的原子性和持久性相信都是比較好理解的特性，前者保證數(shù)據(jù)庫的事務(wù)要么全部執(zhí)行、要么全部不執(zhí)行，后者保證了對數(shù)據(jù)庫的寫入都是持久存儲的、非易失的，而一致性不僅是數(shù)據(jù)庫對本身數(shù)據(jù)的完整性的要求，同時(shí)也對開發(fā)者提出了要求 - 寫出邏輯正確并且合理的事務(wù)。