最全的數(shù)據(jù)庫-MySQL知識匯總2

7、數(shù)據(jù)庫如何實現(xiàn)四種隔離級別

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

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

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

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

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

InnoDB默認(rèn)的隔離級別是RR，用得最多的隔離級別是RC。

8、數(shù)據(jù)庫樂觀鎖和悲觀鎖

8.1、悲觀鎖

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

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

8.2、樂觀鎖

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

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

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

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

9、索引

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

1. 聚集索引

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

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

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

輔助索引中，葉結(jié)點的data域存放的是對應(yīng)記錄的主鍵的key。

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

2. 非聚集索引

非聚集索引則就是普通索引了，僅僅只是對數(shù)據(jù)列創(chuàng)建相應(yīng)的索引，不影響整個表的物理存儲順序。

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

3. 區(qū)別

• 聚集索引表里數(shù)據(jù)物理存儲順序和主鍵索引的順序一致，所以如果新增數(shù)據(jù)是離散的，會導(dǎo)致數(shù)據(jù)塊趨于離散，而不是趨于順序。而非聚集索引表數(shù)據(jù)寫入的順序是按寫入時間順序存儲的。
• 聚簇索引索引的葉節(jié)點就是數(shù)據(jù)節(jié)點；而非聚簇索引的葉節(jié)點仍然是索引節(jié)點，只不過有一個指針指向?qū)?yīng)的數(shù)據(jù)塊。

4. 適用情景

9.2、從數(shù)據(jù)結(jié)構(gòu)角度:

9.2.1、b+樹索引

優(yōu)點：

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

缺點：

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

1. 主鍵不是有序遞增的，導(dǎo)致每次插入數(shù)據(jù)產(chǎn)生大量的數(shù)據(jù)遷移和空間碎片；
2. 即使主鍵是有序遞增的，大量寫請求的分布仍是隨機(jī)的；

9.2.2、hash索引

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

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

對比：

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

9.3、從邏輯角度:

1. 主鍵索引：索引值必須唯一，不能為NULL，在B+TREE中的InnoDB引擎中,主鍵索引起到了至關(guān)重要的地位。普通索引或者單列索引：最普通的索引，沒有任何限制。
2. 多列索引（復(fù)合索引）：多個單列索引與單個多列索引的查詢效果不同，因為執(zhí)行查詢時，MySQL只能使用一個索引，會從多個索引中選擇一個限制最為嚴(yán)格的索引。復(fù)合索引指多個字段 上創(chuàng)建的索引，只有在查詢條件中使用了創(chuàng)建索引時的第一個字段，索引才會被使用。使用復(fù)合索引時遵循最左前綴集合 。
3. 唯一索引或者非唯一索引：與普通索引的不同的是，索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。
4. 組合索引：平時用的SQL查詢語句一般都有比較多的限制條件，所以為了進(jìn)一步榨取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. 不能跳過某個字段進(jìn)行查詢。

使用索引優(yōu)點：

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

使用索引缺點：

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