最近讀了一本書《mysql是怎樣執行的》，讀完後在大體上對mysql的執行有一定的瞭解。在以前，我對mysql有以下的為什麼：
- InnoDB中的表空間、段、區和頁是什麼？
- redo log為什麼就能實現事務的永續性？
- 到底什麼是意向鎖？意向鎖有什麼用？
- mysql中的外連線、內連線到底是什麼？
- 事務中的一致性到底是什麼意思？一致性和原子性有什麼不一樣？
現在我對這些為什麼都有了答案，下面說說我看書後的個人理解。
以下都是以InnoDB而言。
問題：InnoDB中的表空間、段、區和頁是什麼？
什麼是頁？為什麼要有頁？
- 假設沒有頁，mysql和磁碟間的互動是這樣的： 每當有一條資料改動，都要進行磁碟IO。如果修改的資料很多，那麼要存取多次磁碟，效能急劇下降。
- 此時就會有一個想法「那麼如果在存取磁碟時，能一次性修改多條資料就好了」。 所以有了頁。在一頁中可以儲存多條資料。
- 有了頁之後，mysql和磁碟間的互動是以頁為單位的。而不是一條資料為單位。那麼就能提升效能。
什麼是表空間？為什麼要有表空間？
- 假設沒有表空間，資料是存放在頁中的，如果要定位某一條資料，就要遍歷所有的頁，效能低。
- 此時就會有一個想法「如果給這些頁弄一個類似於目錄的東西，這樣就能快速定位到資料所在的頁了」。所以有了表空間。一個表空間可以存放多個頁。
- 表空間是一個抽象的概念，對應著檔案系統上一個或多個檔案。
- 表空間是用來管理頁的，一個表空間由許許多多個頁組成。資料存放在某個表空間的某個頁中。
- 表空間有許多型別，如系統表空間、獨立表空間、通用表空間、undo表空間、臨時表空間。最常用的是系統表空間和獨立表空間
- 系統表空間： 在mysql5-6之間，mysql表中所有的資料都是存放在系統表空間中的。
- 獨立表空間： 在mysql7以後，一個mysql表就對應著一個獨立表空間。
什麼是區？為什麼要有區？
- 首先，一個頁的大小是16K，而一個表空間可以存64T的資料。因此如果單靠一個表空間就想管理全部頁，那麼是很困難的，這種做法類似於1個人直接管理1個億的團隊。
- 此時就會有一個想法「既然一個表空間不好管理所有的頁，那麼可以委派出去，建立幾個管理層，形成表空間-->管理層-->頁的管理關係就好了」，所以有了區。
- 其次，在InnoDB中，資料是存放在代表聚集索引的B+樹的葉子結點內的。一個葉子結點就是一個頁。而mysql對B+樹進行了改進，使得葉子結點之間形成一個雙向連結串列。因此要進行範圍查詢的話，只需要找到最小滿足條件的記錄所在的葉子結點，然後沿著雙連結串列遍歷即可。那麼問題就來了，如果葉子結點（頁）的之間的物理位置距離特別遠，那麼遍歷雙向連結串列就是隨機IO，效能低。
- 此時就會又有一個想法「如果B+樹中的葉子結點的物理位置是相鄰的，那麼就不會產生隨機IO，而是順序IO」，所以有了區。
- 一個區保證了64個頁的物理位置連續，因此在這個區內對頁面進行範圍查詢時是順序IO。
- 一個區能存64個頁，也就是一個區預設1M大小。
什麼是段？為什麼要有段？
- 首先，如果把B+樹段所有葉子結點和非葉子結點都放到一個區內，假設區內的 非葉子結點的數量 > 葉子結點的數量 那麼即使有了區，但是進行範圍查詢時，效能也大打折扣，因此要掃描的頁太少了。
- 此時就有一個想法「如果把葉子結點和非葉子結點單獨放一個區就好了」，所以有了段。
- 此次，如果一個範圍查詢中涉及到了多個區，假設區之間的物理位置很遠，遍歷區時就是隨機IO，效能低。
- 此時又有一個想法「讓B+樹中結點所在的區之間物理位置連續就好了」，所以有了段。
- 在一個段中，其所有的區物理位置連續且都存放相同類似的頁，也就是說一個索引會有兩段，一個葉子結點段，一個非葉子結點段。
- 注意：並不是所有的區都會被段管理。有一些區是直接被表空間管理的。
有了段之後帶來的問題： 一個表預設都會有聚集索引，那麼也就是預設有兩個段，而一個段是以區為單位去分配記憶體的，一個區預設佔1M儲存空間，那麼一個普通的小表也需要用到2M的儲存空間？
分析： 問題的原因在於段是隻有一個結點型別的區，一個段內的頁只儲存同種型別的資料，即使有空閒頁，那麼不會另為他用。
解決： 使用碎片區。
- 碎片區也就是不純粹的區，裡面可以存葉子結點和非葉子結點。
- 也就是在一個碎片區中，並不是所有的頁都是為了儲存同一個段的資料而存在的，而是碎片區中的頁可以用於不同的目的，比如有些頁用於段A，有些頁用於段B，有些頁甚至哪個段都不屬於。碎片區直屬於表空間，並不屬於任何一個段。所以此後為某個段分配儲存空間的策略是這樣的：
- 在剛開始向表中插入資料的時候，段是從某個碎片區以單個頁面為單位來分配儲存空間的。
- 當某個段已經佔用了32個碎片區的頁之後，就會以完整的區為單位來分配儲存空間。
有了碎片區以後，段不能僅定義為是某些區的集合，更精確的應該是某些家散的頁面以及一些完整的區的集合。
總結；
- 在InnoDB中，儲存資料的單位是頁，但是由於頁過多，因此有了表空間來進行管理。
- 但是表空間管理不過來這麼多頁，因此有了區來對頁進行直接管理，而表空間對區進行直接管理。
- 如果B+樹中的結點都堆到一個區內，效能會下降，因此有了段，段對區進行直接管理，而表空間對段進行直接管理。
- 但是由於並不是所有的區都會被段管理。有一些區是直接被表空間管理的。所有形成了以下兩條管理鏈：
- 表空間-->區-->頁
- 表空間-->段-->區-->頁
表空間、段、區、頁與B+樹的聯絡：
- 在建立好一張表後，預設會有聚集索引，因此B+樹存在，因此會有葉子結點段和非葉子結點段。
- 表中資料量少時，插入一條資料，會以碎片區中的頁來分配儲存空間。
- 表中資料量大後，插入一條資料，會先分配一個區，然後再從區中的頁來非配器儲存空間。
- 這張表的所有段、區、頁都歸表空間直接或間接管理。
問題：redo log為什麼就能實現事務的永續性？
什麼是Buffer pool？
- 如果mysql每次改動資料都直接去修改磁碟中的資料，即有一條資料出現改動就要存取一次磁碟，那麼收到磁碟IO的影響，效能是很低的。
- 此時有個想法「如果把需要修改的資料提前快取起來，要修改時直接改，改完之後，過一段時間後，修改過的資料可能有多條，那麼此時再統一應用到磁碟上就好了」，所以有了Buffer pool。
- 每次存取mysql時，都是先存取buffer pool中的資料頁。如果要對某條記錄進行修改，那麼就會先修改buffer pool中快取好的資料頁。等一段時間後，Buffer pool通過後臺執行緒再把變更過的資料（髒頁）重新整理到磁碟中。
為什麼要有redo log檔案？
- 假設沒有redo log，在事務提交後，Buffer pool中快取的資料是已經修改完畢了，但是磁碟中真正的資料還沒繼續修改，操作結果返回給使用者。一段時間後，髒頁才會被重新整理到磁碟中，如果重新整理時出現問題（例如刷著刷著，mysql宕機了）或者還沒等到Buffer pool重新整理資料時，mysql就已經宕機了，就會出現資料不一致了，使用者收到修改成功的結果，而磁碟上的資料並沒有修改。事務的永續性就被破壞了。
- 此時有個想法「如果把事務中對資料所做的操作給記錄下來，在mysql重啟後，重新執行這些記錄，這樣就不怕因Buffer pool中的資料沒有重新整理到磁碟上而導致事務的永續性被破壞了」，因此有了redo log檔案。
- 事務永續性被破壞的原因在於提交事務（事務中對資料進行了修改）的時間 與 重新整理Buffer pool資料到磁碟上的時間不一致。這段時間間隔內，可能會出現各種問題，導致Buffer pool的資料丟失，從而造成了明明修改了，但是磁碟上的資料沒有變動的現象。
- 那麼只要消除這個時間間隔，事務的永續性就能得到保障了。也就是說在提交事務時就把該事務對資料所做的操作給記錄到redo log檔案中，那麼就不怕隔了一段時間後Buffer pool重新整理髒頁時，或Buffer pool還沒重新整理髒頁時因為各種問題，導致的資料不一致了。因為在事務提交的瞬間，redo log檔案就已經在磁碟中記錄了其對資料的操作。
什麼是redo log buffer？為什麼要有redo log buffer？
- 開啟一個事務後，每對資料進行一次修改，都會生成一條redo log紀錄檔。也就是說一個事務可能會產生多個redo log紀錄檔，而redo log紀錄檔是要記錄到磁碟上的redo lo檔案中的，那麼在事務中每進行一次資料修改，就存取磁碟，對磁碟上的redo log紀錄檔進行寫操作。效能很低。
- 此時有個想法「如果在事務未提交時先把其生成的redo log紀錄檔快取起來，等事務提交的瞬間在記錄到磁碟上的redo log檔案就好了」，所以有了redo log buffer。
- 事務提交的瞬間會把redo log buffer中的redo log重新整理到redo log紀錄檔中，因為是順序IO，速度極快，所以不必擔心還沒重新整理時就出現了mysql宕機，導致redo log紀錄檔丟失，從而事務永續性被破壞的問題。
問題：到底什麼是意向鎖？意向鎖有什麼用？
什麼是X鎖、S鎖？
- InnoDB的鎖根據粒度分為全域性鎖、表鎖、行鎖。
- 而根據鎖的型別分為了獨佔鎖（X鎖），共用鎖（S鎖）。
- 獨佔鎖（X鎖）：為寫操作而存在。X鎖與X鎖互斥，X鎖與S鎖互斥。
- 共用鎖（S鎖）：為讀操作而存在。S鎖之間不互斥。
- 因此表鎖可以有X型表鎖、S型表鎖，而行鎖也可以有X型行鎖、S型行鎖。
什麼是意向鎖？為什麼要有意向鎖？
- 在一個事務A中當對錶中的某條記錄加了行鎖（X型或S型）後，若其他事務B想對該表加表鎖了。那麼假設事務A加的是X型行鎖，而事務B加S或X型表鎖。事務B的加鎖操作是會被阻塞的。因為X型行鎖的存在。那麼問題來了，事務B加表鎖時怎麼知道這個表裡有沒有行鎖？如果有，行鎖有幾個？行鎖的型別又是什麼？
- 假設沒有意向鎖，事務B只能遍歷整張表，才能知道這張表有多少個行鎖以及其對應的型別。如果這張表資料量大的情況下，全表掃描的效能是很低的。
- 此時有個想法「為這張表設立一個標誌位，事務對記錄加行鎖時就修改標誌位，等到有事務加表鎖時，檢查一下這個標誌位就好了」，所以有了意向鎖。
- 按粒度來劃分，意向鎖屬於表鎖。意向鎖分為兩種：
- 共用意向鎖（IS鎖）：在事務加S型行鎖時，會給表加上一個IS鎖。
- 獨佔意向鎖（IX鎖）：在事務加X型行鎖時，會給表加上一個IX鎖。
- 意向鎖僅僅是為了事務在加表鎖（X型或S型）時可以快速判斷表中的記錄是否有行鎖，從而決定該事務能否加鎖成功。因此
- IX鎖和IS鎖不互斥（意向鎖之間不互斥）
- IX鎖、IS鎖和S型行所、X型行鎖都不互斥（意向鎖和行鎖不互斥）
- IX鎖和S型表鎖、X型表鎖互斥，IS鎖和S型表鎖不互斥（意向鎖和表鎖可能互斥）
總結：
如果表中存在意向鎖（IX或IS型），那麼也意味著有事務在對行進行加鎖，此時如果另一個事務要加表級鎖，就要判斷表級鎖和任意一個意向鎖是否互斥。
- 假設存在多個意向鎖（既有意向排他鎖，也有意向共用鎖），那麼此時是不可能加表級共用鎖和表級排他鎖的。
- 假設存在多個意向鎖（只有意向共用鎖），那麼此時只能加表級共用鎖，不能加表級排他鎖。
- 假設存在多個意向鎖（只有意向排他鎖），那麼此時是不能加表級共用鎖和表級排他鎖的。
問題：mysql中的外連線、內連線到底是什麼？
什麼是連線？
- 在mysql中，進行兩個表之間的連線就是讓一個表中的每條記錄與另一個表中的每條記錄拼接，組成一個結果集（笛卡爾積）。
什麼是驅動表？什麼是被驅動表？
- 連線的本質就是從一個表A中查詢出一條記錄，然後與另一個表B中的所有匹配的記錄分別進行拼接。重複這個過程，直到表A中的記錄都與表B中的記錄拼接完畢為止。
- 而這個表A就是驅動表，表B就是被驅動表。
整個連線的過程就類似一個雙層for迴圈，外層的for就是驅動表，內層的for就是被驅動表。
for(int i='a';i<='b';i++){ //這一層for就類似遍歷表A
for(int j='c';j<='d';j++){ //這一層for就型別遍歷表B
res=i+j; //進行連線
}
}
什麼是外連線？什麼是內連線？
- 首先，在沒有外連線之前，所有的連線都是內連線。
- 內連線： 驅動表中的記錄在被驅動表中找不到匹配的記錄時，那麼就不會拼接，也不會加入結果集。
- 但是有一些需求，要求：驅動表中的記錄即使在被驅動表中找不到匹配的記錄，也要加入結果集。因此有了外連線。
- 外連線：驅動表中的記錄在被驅動表中找不到匹配的記錄時，仍然會進行拼接，會對讓驅動表的記錄與null進行拼接（被驅動表有多少列，就拼接多少個null），然後加入結果集。根據選取的驅動表不同，外連線分為兩種：
- 左外連線： 左側的表為驅動表。
- 右外連線： 右側的表為驅動表。
為什麼外連線要用on？on和where子句的關係是什麼？
- 對於外連線來說，會把驅動表的所有記錄都與被驅動表進行連線，然後加入結果集。假設沒有on子句，有時候希望驅動表中的記錄在被驅動表中找不到匹配的記錄，要加入結果集（也就是外連線的語法，外連線的效果）；有時候又希望驅動表中的記錄在被驅動表中找不到匹配的記錄時，不加入結果集（也就是外連線的語法，內連線的效果）。
- 此時有個想法「控制怎麼連線是由過濾條件where子句來決定的，那麼把where進行拆分就好了」，因此有了on子句。
- on子句作為過濾條件，on子句保證了外連線是最純正的外連線，實現的是外連線的語法，外連線的效果。
- 如果實現外連線的語法，內連線的效果。此時再用where子句過濾掉驅動表中匹配不上的記錄，這樣就好了。
- 因此如果on子句與where子句同時出現，也就是先用on過濾，保證最純的外連線，然後再用where過濾，進一步加工結果集。
由於on子句是為了在外連線時，驅動表中的記錄在被驅動表找不到匹配記錄時是否要加入結果集，從而把外連線的結果集是否要轉換成內連線的結果集的場景下提出的，因此在內連線中，on與where等價。
問題：事務中的一致性到底是什麼意思？一致性和原子性有什麼不一樣？
什麼是資料的一致性？
- 首先，資料庫世界是對現實世界的一種對映。現實世界的一個狀態轉移，對應著資料庫的一組操作。這組操作就是事務啊！！！為了讓資料庫操作符合現實世界中狀態的轉移的規則，因此有了事務的ACID特性。
- 事務的四大特性：原子性、隔離性、永續性、一致性。前三個都很好理解，就一致性很難理解。
- 我認為一致性對應的就是現實世界中的「能量守恆」，在現實世界中有能量的消耗，必然會有能量的增長。在資料庫世界中也如此，有資料的減少，必然就有資料的增加。
- 資料庫是現實是世界的一個對映，現實世界存在的約束在資料庫中要有所體現，如果資料庫中的資料全部符合現實世界中的約束，那麼這些資料是符合一致性的。
- 舉個例子： 轉賬，在現實世界中一個人的餘額減少必定會有一個人的餘額增加。因此無形中有個約束」參與轉賬的賬戶的總餘額不變「，也就是說，一個人轉賬，另一個人必定會收到對應的金額，不會出現收不到，收少了，收多了的情況。對映到資料庫中也就是一條記錄中某個值的減少，必然會有一條記錄某個值的增加。
- 一致性最求的是結果，而不是過程。也就是說只要結果符合約束就是滿足一致性。即使過程中是否滿足原子性、隔離性、永續性都不是滿足一致性的必然因素。
一致性和原子性有什麼不一樣？
- 原子性和一致性的的側重點不同，原子性關注狀態，要麼全部成功，要麼全部失敗，不存在部分成功的狀態。而一致性關注資料的可見性，中間狀態的資料對外部不可見，只有最初狀態和最終狀態的資料對外可見
- 我個人認為原子性和一致性的區別就是：一個是操作 一個是資料；一個是過程 一個是結果；一個是狀態 一個是屬性。