7、數據庫如何實現四種隔離級別

InnoDB使用不同的鎖策略(Locking Strategy)來實現不同的隔離級別。

(1) 讀未提交 ：select不加鎖，可能出現讀臟。

(2) 讀提交(RC) ：普通select快照讀，鎖select /update /delete 會使用記錄鎖，可能出現不可重復讀。

(3) 可重復讀(RR) ：普通select快照讀，鎖select /update /delete 根據查詢條件情況，會選擇記錄鎖，或者間隙鎖/臨鍵鎖，以防止讀取到幻影記錄。

(4) 串行化 ：select隱式轉化為select ... in share mode，會被update與delete互斥；

InnoDB默認的隔離級別是RR，用得最多的隔離級別是RC。

8、數據庫樂觀鎖和悲觀鎖

8.1、悲觀鎖

悲觀鎖（Pessimistic Lock），顧名思義，就是很悲觀，每次去拿數據的時候都認為別人會修改，所以每次在拿數據的時候都會上鎖，這樣別人想拿這個數據就會block直到它拿到鎖。悲觀鎖：假定會發生并發沖突，屏蔽一切可能違反數據完整性的操作。

Java synchronized 就屬于悲觀鎖的一種實現，每次線程要修改數據時都先獲得鎖，保證同一時刻只有一個線程能操作數據，其他線程則會被block。

8.2、樂觀鎖

樂觀鎖（Optimistic Lock），顧名思義，就是很樂觀，每次去拿數據的時候都認為別人不會修改，所以不會上鎖，但是在提交更新的時候會判斷一下在此期間別人有沒有去更新這個數據。樂觀鎖適用于讀多寫少的應用場景，這樣可以提高吞吐量。

樂觀鎖：假設不會發生并發沖突，只在提交操作時檢查是否違反數據完整性。

樂觀鎖一般來說有以下2種方式：

• 使用數據版本（Version）記錄機制實現，這是樂觀鎖最常用的一種實現方式。何謂數據版本？即為數據增加一個版本標識，一般是通過為數據庫表增加一個數字類型的 “version” 字段來實現。當讀取數據時，將version字段的值一同讀出，數據每更新一次，對此version值加一。當我們提交更新的時候，判斷數據庫表對應記錄的當前版本信息與第一次取出來的version值進行比對，如果數據庫表當前版本號與第一次取出來的version值相等，則予以更新，否則認為是過期數據。
• 使用時間戳（timestamp）。樂觀鎖定的第二種實現方式和第一種差不多，同樣是在需要樂觀鎖控制的table中增加一個字段，名稱無所謂，字段類型使用時間戳（timestamp）, 和上面的version類似，也是在更新提交的時候檢查當前數據庫中數據的時間戳和自己更新前取到的時間戳進行對比，如果一致則OK，否則就是版本沖突。

9、索引

9.1、從物理存儲角度:動作描述

1. 聚集索引

聚集索引是一種索引組織形式，索引的鍵值邏輯順序決定了表數據行的物理存儲順序。

聚集索引對于那些經常要搜索范圍值的列特別有效。使用聚集索引找到包含第一個值的行后，便可以確保包含后續索引值的行在物理相鄰。

InnoDB的數據文件本身要按主鍵聚集，所以InnoDB要求表必須有主鍵（MyISAM可以沒有），如果沒有顯式指定，則MySQL系統會自動選擇一個可以唯一標識數據記錄的列作為主鍵，如果不存在這種列，則MySQL自動為InnoDB表生成一個隱含字段作為主鍵，這個字段長度為6個字節，類型為長整形。

輔助索引中，葉結點的data域存放的是對應記錄的主鍵的key。

對于建立輔助索引的表需要先根據輔助索引找到相應的主鍵，再根據主鍵在聚集索引中找到相應的記錄集。

2. 非聚集索引

非聚集索引則就是普通索引了，僅僅只是對數據列創建相應的索引，不影響整個表的物理存儲順序。

主鍵索引中，葉節點的data域存放的是數據記錄的地址，如果指定的Key存在，則取出其data域的值，然后以data域的值為地址，讀取相應數據記錄。（MYISAM采用此種索引方式）。

3. 區別

• 聚集索引表里數據物理存儲順序和主鍵索引的順序一致，所以如果新增數據是離散的，會導致數據塊趨于離散，而不是趨于順序。而非聚集索引表數據寫入的順序是按寫入時間順序存儲的。
• 聚簇索引索引的葉節點就是數據節點；而非聚簇索引的葉節點仍然是索引節點，只不過有一個指針指向對應的數據塊。

4. 適用情景

9.2、從數據結構角度:

9.2.1、b+樹索引

優點：

1. 單次請求涉及的磁盤IO次數少（出度d大，且非葉子節點不包含表數據，樹的高度小）；
2. 查詢效率穩定（任何關鍵字的查詢必須走從根結點到葉子結點，查詢路徑長度相同）；
3. 遍歷效率高（從符合條件的某個葉子節點開始遍歷即可）；

缺點：

B+樹最大的性能問題在于會產生大量的隨機IO，主要存在以下兩種情況：

1. 主鍵不是有序遞增的，導致每次插入數據產生大量的數據遷移和空間碎片；
2. 即使主鍵是有序遞增的，大量寫請求的分布仍是隨機的；

9.2.2、hash索引

哈希索引就是采用一定的哈希算法，把鍵值換算成新的哈希值 ，檢索時不需要類似 B+樹那樣從根節點到葉子節點逐級查找， 只需一次哈希算法即可立刻定位到相應的位置 ，速度非常快。

Hash 索引結構的特殊性，其檢索效率非常高，索引的檢索可以一次定位，不像B-Tree 索引需要從根節點到枝點，最后才能訪問到頁節點這樣多次的IO訪問，所以 Hash 索引的查詢效率要遠高于B-Tree 索引。

對比：

• Hash 索引僅僅能滿足"=",和"<=>"等值查詢，不能使用范圍查詢。
• Hash 索引無法被用來避免數據的排序操作。
• Hash 索引 不支持多列聯合索引的最左匹配規則 ；
• Hash 索引在任何時候都不能避免表掃描。
• B+樹索引的關鍵字檢索效率比較平均，不像B樹那樣波動幅度大，在有 大量重復鍵值情況下 ，哈希索引的效率也是極低的，因為存在所謂的哈希碰撞問題。

9.3、從邏輯角度:

1. 主鍵索引：索引值必須唯一，不能為NULL，在B+TREE中的InnoDB引擎中,主鍵索引起到了至關重要的地位。普通索引或者單列索引：最普通的索引，沒有任何限制。
2. 多列索引（復合索引）：多個單列索引與單個多列索引的查詢效果不同，因為執行查詢時，MySQL只能使用一個索引，會從多個索引中選擇一個限制最為嚴格的索引。復合索引指多個字段 上創建的索引，只有在查詢條件中使用了創建索引時的第一個字段，索引才會被使用。使用復合索引時遵循最左前綴集合 。
3. 唯一索引或者非唯一索引：與普通索引的不同的是，索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。
4. 組合索引：平時用的SQL查詢語句一般都有比較多的限制條件，所以為了進一步榨取MySQL的效率，就要考慮建立組合索引。在使用查詢的時候遵循“最左前綴”：
   1. 不按索引最左列開始查詢不適用索引。例如對idnex( c1 , c2 , c3 ),使用where c2 = “aaa” and c3 = “bbb”不能使用索引 。
   2. 查詢中某個列有范圍查詢，則其右邊的所有列都無法使用查詢。例如對idnex( c1 , c2 , c3 )， where c1 = “xxx” and c2 like = “aa%” and c3 = “sss”查詢只會使用索引的前兩列，因為like是范圍查詢。
   3. 不能跳過某個字段進行查詢。

使用索引優點：

1. 可以通過建立唯一索引或者主鍵索引,保證數據庫表中每一行數據的唯一性 。
2. 建立索引可以大大提高檢索的數據,以及減少表的檢索行數 。
3. 在表連接的連接條件，可以加速表與表直接的相連 。
4. 在分組和排序字句進行數據檢索,可以減少查詢時間中分組和 排序時所消耗的時間(數據庫的記錄會重新排序) 。
5. 建立索引,在查詢中使用索引，可以提高性能。

使用索引缺點：

1. 創建索引和維護索引會耗費時間,隨著數據量的增加而增加 。
2. 索引文件會占用物理空間,除了數據表需要占用物理空間之外,每一個索引還會占用一定的物理空間。
3. 當對表的數據進行 INSERT,UPDATE,DELETE 的時候,索引也要動態的維護,這樣就會降低數據的維護速度,(建立索引會占用磁盤空間的索引文件。一般情況這個問題不太嚴重，但如果你在一個大表上創建了多種組合索引，索引文件的會膨脹很快)。