作者：莫那魯道
來源：http://thinkinjava.cn/2019/03/16/2019-03-16-mysql
1.索引
索引，類似書籍的目錄，可以根據目錄的某個頁碼立即找到對應的內容。
索引的優點：1. 天生排序，2. 快速查詢。
索引的缺點：1. 佔用空間，2. 降低更新表的速度。
注意點：小表使用全表掃描更快，中大表才使用索引。超級大表索引基本無效。
索引從實現上說，分成 2 種：聚集索引和輔助索引（也叫二級索引或者非聚集索引）
從功能上說，分為 6 種：普通索引，唯一索引，主鍵索引，複合索引，外來鍵索引，全文索引。
詳細說說 6 種索引：
1、普通索引：最基本的索引，沒有任何約束。 2、唯一索引：與普通索引類似，但具有唯一性約束。 3、主鍵索引：特殊的唯一索引，不允許有空值。 4、複合索引：將多個列組合在一起建立索引，可以覆蓋多個列。 5、外來鍵索引：只有InnoDB型別的表才可以使用外來鍵索引，保證資料的一致性、完整性和實現級聯操作。 6、全文索引：MySQL 自帶的全文索引只能用於 InnoDB、MyISAM ，並且只能對英文進行全文檢索，一般使用全文索引引擎（ES，Solr）。
注意：主鍵就是唯一索引，但是唯一索引不一定是主鍵，唯一索引可以為空，但是空值只能有一個，主鍵不能為空。
另外，InnoDB 通過主鍵聚簇資料，如果沒有定義主鍵且沒有定義聚集索引， MySql 會選擇一個唯一的非空索引代替，如果沒有這樣的索引，會隱式定義個 6 位元組的主鍵作為聚簇索引，使用者不能檢視或訪問。
簡單點說：
1、設定主鍵時，會自動生成一個唯一索引，如果之前沒有聚集索引，那麼主鍵就是聚集索引。 2、沒有設定主鍵時，會選擇一個不為空的唯一索引作為聚集索引，如果還沒有，那就生成一個隱式的 6 位元組的索引。
MySql 將資料按照頁來儲存，預設一頁為 16kb，當你在查詢時，不會只載入某一條資料，而是將這個資料所在的頁都載入到 pageCache 中，這個其實和 OS 的就近訪問原理類似。
MySql 的索引使用 B+ 樹結構。在說 B+ 樹之前，先說說 B 樹，B 樹是一個多路平衡查詢樹，相較於普通的二叉樹，不會發生極度不平衡的狀況，同時也是多路的。
B 樹的特點是：他會將資料也儲存在非頁子節點。
看圖可知：
而這個特點會導致非頁子節點不能儲存大量的索引。
而 B+ Tree 就是針對這個對 B tree 做了優化。如下圖所示：
我們看到，B+ Tree 將所有的 data 資料都儲存到了葉子節點中，非也子節點只儲存索引和指標。
我們假設一個非頁子節點是 16kb，每個索引，即主鍵是 bigint，即 8b，指標為 8b。那麼每頁能儲存大約 1000 個索引（16kb/ 8b + 8b）.
而一顆 3 層的 B+樹能夠儲存多少索引呢？
如下圖：
大約能夠儲存 10 億個索引。通常 B+ 樹的高度在 2-4 層，由於 MySql 在執行時，根節點是常駐記憶體的，因此每次查詢只需要大約 2 -3 次 IO。可以說，B+ 樹的設計，就是根據機械磁碟的特性來進行設計的。
知道了索引的設計，我們能夠知道另外一些資訊：
1、MySql 的主鍵不能太大，如果使用 UUID 這種，將會浪費 B+ 樹的非葉子節點。 2、MySql 的主鍵最好是自增的，如果使用 UUID 這種，每次插入都會調整 B+樹，從而導致頁分裂，嚴重影響效能。
那麼，如果專案中使用了分庫分表，我們通常都會需要一個主鍵進行 sharding，那怎麼辦呢？在實現上，我們可以保留自增主鍵，而邏輯主鍵用來作為唯一索引即可。
2.鎖機制
關於 Mysql 的鎖，各種概念就會噴湧而出，事實上，鎖有好幾種維度，我們來解釋一下。
1.型別維度
共享鎖（讀鎖 / S 鎖） 排它鎖（寫鎖 / X 鎖）
型別細分：
意向共享鎖
意向排他（互斥）鎖
悲觀鎖（使用鎖，即 for update）
樂觀鎖（使用版本號欄位，類似 CAS 機制，即使用者自己控制。缺點：併發很高的時候，多了很多無用的重試）
2.鎖的粒度（粒度維度）
表鎖 頁鎖（Mysql BerkeleyDB 引擎） 行鎖（InnoDB）
3.鎖的演算法（演算法維度）
Record Lock（單行記錄） Gap Lock（間隙鎖，鎖定一個範圍，但不包含鎖定記錄） Next-Key Lock（Record Lock + Gap Lock，鎖定一個範圍，並且鎖定記錄本身， MySql 防止幻讀，就是使用此鎖實現）
4.預設的讀操作，上鎖嗎？
預設是 MVCC 機制（“一致性非鎖定讀”）保證 RR 級別的隔離正確性，是不上鎖的。
可以選擇手動上鎖：select xxxx for update (排他鎖);
select xxxx lock in share mode(共享鎖)，稱之為“一致性鎖定讀”。
使用鎖之後，就能在 RR 級別下，避免幻讀。當然，預設的 MVCC 讀，也能避免幻讀。
既然 RR 能夠防止幻讀，那麼，SERIALIZABLE 有啥用呢？
防止丟失更新。例如下圖：
這個時候，我們必須使用 SERIALIZABLE 級別進行序列讀取。
最後，行鎖的實現原理就是鎖住聚集索引，如果你查詢的時候，沒有正確地擊中索引，MySql 優化器將會拋棄行鎖，使用表鎖。
3.事務
事務是資料庫永恆不變的話題， ACID：原子性，一致性，隔離性，永續性。
四個特性，最重要的就是一致性。而一致性由原子性，隔離性，永續性來保證。
原子性由 Undo log 保證。Undo Log 會儲存每次變更之前的記錄，從而在發生錯誤時進行回滾。隔離性由 MVCC 和 Lock 保證。這個後面說。永續性由 Redo Log 保證。每次真正修改資料之前，都會將記錄寫到 Redo Log 中，只有 Redo Log 寫入成功，才會真正的寫入到 B+ 樹中，如果提交之前斷電，就可以通過 Redo Log 恢復記錄。
然後再說隔離性。
隔離級別：
1、未提交讀（RU）
2、已提交讀（RC）
3、可重複讀（RR）
4、序列化（serializable）
每個級別都會解決不同的問題，通常是3 個問題：髒讀，不可重複讀，幻讀。一張經典的圖：
這裡有個注意點，關於幻讀，在資料庫規範裡，RR 級別會導致幻讀，但是，由於 Mysql 的優化，MySql 的 RR 級別不會導致幻讀：在使用預設的 select 時，MySql 使用 MVCC 機制保證不會幻讀；
你也可以使用鎖，在使用鎖時，例如 for update（X 鎖），lock in share mode（S 鎖），MySql 會使用 Next-Key Lock 來保證不會發生幻讀。前者稱為快照讀，後者稱為當前讀。
原理剖析：
1、RU 發生髒讀的原因：RU 原理是對每個更新語句的行記錄進行加鎖，而不是對整個事務進行加鎖，所以會發生髒讀。而 RC 和 RR 會對整個事務加鎖。 2、RC 不能重複讀的原因：RC 每次執行 SQL 語句都會生成一個新的 Read View，每次讀到的都是不同的。而 RR 的事務從始至終都是使用同一個 Read View。 3、RR 不會發生幻讀的原因： 上面說過了。
那 RR 和 Serializble 有什麼區別呢？答：丟失更新。本文關於鎖的部分已經提到。
MVCC 介紹：全稱多版本併發控制。
innoDB 每個聚集索引都有 4 個隱藏欄位，分別是主鍵（RowID），最近更改的事務 ID（MVCC 核心），Undo Log 的指標（隔離核心），索引刪除標記（當刪除時，不會立即刪除，而是打標記，然後非同步刪除）；
本質上，MVCC 就是用 Undo Log 連結串列實現。
MVCC 的實現方式：事務以排它鎖的方式修改原始資料，把修改前的資料存放於 Undo Log，通過回滾指標與資料關聯，如果修改成功，什麼都不做，如果修改失敗，則恢復 Undo Log 中的資料。
多說一句，通常我們認為 MVCC 是類似樂觀鎖的方式，即使用版本號，而實際上，innoDB 不是這麼實現的。當然，這不影響我們使用 MySql。
更多技術乾貨