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

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

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

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

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

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

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

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

8.1、悲觀鎖

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

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

8.2、樂觀鎖

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

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

樂觀鎖一般來(lái)說(shuō)有以下2種方式：

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

9、索引

9.1、從物理存儲(chǔ)角度:動(dòng)作描述

1. 聚集索引

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

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

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

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

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

2. 非聚集索引

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

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

3. 區(qū)別

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

4. 適用情景

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

9.2.1、b+樹索引

優(yōu)點(diǎn)：

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

缺點(diǎn)：

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

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

9.2.2、hash索引

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

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

對(duì)比：

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

9.3、從邏輯角度:

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

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

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

使用索引缺點(diǎn)：

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