一天五道Java面試題----第七天（mysql索引結構，各自的優劣---------＞事務的基本特性和隔離級别）

這裡是參考B站上的大佬做的面試題筆記。大家也可以去看視訊講解！！！

文章目錄

• ​​1 、mysql索引結構，各自的優劣​​
• ​​2 、索引的設計原則​​
• ​​3 、mysql鎖的類型有哪些​​
• ​​4 、mysql執行計劃怎麼看​​
• ​​5 、事務的基本特性和隔離級别​​

1 、mysql索引結構，各自的優劣

索引的資料的資料結構和具體存儲引擎的實作有關，在MySQL中使用較多的索引有hash索引，B+樹索引等，innoDB存儲引擎的預設索引實作為：B+樹索引。對于哈希索引來說，底層的資料結構就是哈希表，是以在絕大多數需求為單條記錄查詢的時候，可以選擇哈希索引，查詢性能最快；其餘大部分場景，建議選擇BTree索引。

B+樹：

• B+樹是一個平衡的多叉樹，從根節點到每個葉子節點的高度內插補點不超過1，而且同層級的節點間有指針互相連結。在B+樹上的正常檢索，從根節點到葉子節點的搜尋效率基本相當，不會出現大幅波動，而且基于索引的順序掃描時，也可以利用雙向指針快速快速左右移動，效率非常高。是以B+樹索引被廣泛應用于資料庫、檔案系統等場景。

哈希索引：

• 哈希索引就是采用一定的雜湊演算法，把鍵值換算成新的哈希值，檢索時不需要類似B+樹那樣從根節點到葉子節點逐級查找，隻需一次雜湊演算法即可立刻定位到相應的位置，速度非常快。
• 如果時等值查詢，那麼哈希索引明顯有絕對優勢，因為隻需要經過一次算法即可找到相應的鍵值；前提是鍵值都是唯一的。如果鍵值不是唯一的，就需要先找到該鍵值所在位置，然後再根據連結清單往後掃描，直到找到相應的資料；
• 若果是範圍查詢檢索，這時候哈希索引就毫無用武之地了，因為原先是有序的鍵值，經過雜湊演算法後，有可能變成不連續的了，就沒辦法再利用索引完成範圍查詢檢索。
• 哈希索引也沒辦法利用索引完成排序，以及like‘xxx%’這樣的部分模糊查詢（這種部分模糊查詢，其實質上也是範圍查詢）；
• 哈希索引也不支援多列聯合索引的最左比對規則。
• B+樹索引的關鍵字檢索效率比較平均，不像B樹那樣波動幅度大，在有大量重複鍵值情況下，哈希索引的效率也是極低的，因為存在哈希碰撞問題。

2 、索引的設計原則

查詢更快、占用空間小

• 1 、适合索引的列是出現在where子句中的列，或者連接配接子句中指定的列
• 2 、基數較小的表，索引效果較差，沒有必要在此建立索引
• 3 、使用短索引，如果對長字元串進行索引，應該指定一個字首長度，這樣能夠節省大量索引空間，如果搜尋詞超過索引字首長度，則使用索引排除不比對的行，然後檢索其餘行是否可能比對。
• 4 、不要過度索引。索引需要額外的磁盤空間，并降低寫操作的性能。在修改表内容的時候，索引會進行更新甚至重構，索引列越多，這個時間就會越長。是以隻保持需要的索引有利于查詢即可。
• 5 、定義有外鍵的資料列一定要建立索引

不适合建立索引的

• 1 、更新頻繁字段不适合建立索引
• 2 、若是不能有效區分資料的列不适合做索引（如性别、男女未知、最多也就三種，區分度實在太低）
• 3 、盡量的擴充索引，不要建立索引。比如表中已經有a索引，現在要加（a，b）的索引，那麼隻需要修改原來的索引即可。
• 4 、對于那些查詢中很少涉及的列，重複值比較多的列不要建立索引。
• 5 、對于定義為text、image和bit的資料類型的列不要建立索引。

3 、mysql鎖的類型有哪些

基于鎖的屬性分類：共享鎖、排他鎖

基于鎖的粒度分類：行級鎖（INNODB)、表級鎖（INNODB、MYISAM）、頁級鎖（BDB引擎）、記錄所、間隙鎖、臨建鎖。

基于鎖的狀态分類：意向共享鎖，意向排他鎖。

• 共享鎖（Share Lock）

共享鎖又稱讀鎖，簡稱S鎖：當一個事務為資料加上讀鎖之後，其他事務隻能對該鎖加讀鎖，而不能對資料加寫鎖，直到所有的讀鎖釋放之後其他事務才能對其進行加持寫鎖。共享鎖的特性主要是為了支援并發的讀取資料，讀取資料的時候不支援修改，避免出現重複讀的問題。

• 排他鎖（exclusive Lock）

排他鎖又稱寫鎖，簡稱X鎖：當一個事務為資料加上寫鎖時，其他請求将不再為資料加任何鎖，直到該鎖釋放之後，其他事務才能對資料進行加鎖。排他鎖的目的是在資料修改時，不允許其他人同時修改，也不允許其他人讀取，避免了出現髒資料和髒讀的問題。

• 表鎖

表鎖是指上鎖的時候鎖住的是整個表，當下一個事務通路該表的時候，必須等前一個事務釋放了鎖才能進行對表進行通路：

特點：粒度大、加鎖簡單、容易沖突

• 行鎖

行鎖是指上鎖的時候鎖住的是表的某一行或多行記錄，其他事務通路同一張表時，隻有被鎖住的記錄不能通路，其他的記錄可正常通路

特點：粒度小、加鎖比表鎖麻煩，不容易沖突，相比表鎖支援的并發要高。

• 記錄所（Record Lock）

記錄鎖也屬于行鎖中的一種，隻不過記錄鎖的範圍隻是表中的某一條記錄，記錄鎖是說事務在加鎖後鎖住的隻是表的某一條記錄。

精準條件名中，并且名中的條件字段是唯一索引

加了記錄鎖之後資料可以避免資料在查詢的時候被修改的重複讀問題，也避免了在修改的事務未送出前被其他事務讀取的髒讀問題。

• 頁鎖

  -頁級鎖是MySQL中鎖定粒度介于行級鎖和表級鎖中間的一種鎖。表級鎖速度快，但沖突多，行級沖突少，但速度慢。是以取了折衷的頁級，一次鎖定相鄰的一組記錄。

  特點：開銷和加鎖時間界于表鎖和行鎖之間；會出現死鎖：鎖定粒度界于表鎖和行鎖之間，并發讀一般。

• 間隙鎖（Gap Lock）

屬于行鎖中的一種，間隙鎖是在事務加鎖後其鎖住的是表記錄的某一個區間，當表的相鄰ID之間出現空隙則會形成一個區間，遵循左開右閉原則。

範圍查詢并且查詢未名中記錄，查詢條件必須名中索引、間隙鎖隻會出現在REPEATABLE_READ（重複讀）的事務隔離級别中。

觸發條件：防止幻讀問題，事務并發的時候，如果沒有間隙鎖，就會發生如下的問題，在同一個事務裡，A事務的兩次查詢出的結果會不一樣。

比如表裡面的資料ID為 1，4，5，7，10，那麼會形成以下幾個間隙區間-n-1區間，1-4區間、7-10區間、10-n區間（-n：代表負無窮大，n代表正無窮大）

• 臨建鎖（Next-Key Lock)

也屬于行鎖的一種，并且它是INNODB的行鎖預設算法，總結來說它就是記錄鎖和間隙鎖的組合，臨建鎖會把查詢出來的記錄鎖住，同時也會把該範圍查詢内的所有空隙空間也會鎖住，再之它會把相鄰的下一個區間也會鎖住

觸發條件：範圍查詢并命中，查詢命中了索引。

結合記錄鎖和間隙鎖的特性，臨建鎖避免了在範圍查詢時出現髒讀、重複度、幻讀問題。加了臨建鎖之後，在範圍區間内資料不允許被修改和插入

如果當事務A加鎖成功之後就設定一個狀态告訴後面的人，已經有人對表裡的行加了一個排他鎖了，你們不能對整個表加共享鎖或排它鎖了，那麼後面需要對整個表加鎖的人隻需要擷取這個狀态就知道自己是不是可以對表加鎖，避免了對整個索引樹的每個節點掃描是否加鎖，而這個狀态就是意向鎖。

• 意向共享鎖

當一個事務試圖對整個表進行加共享鎖之前，首先需要獲得這個表的意向共享鎖。

• 意向排它鎖

當一個事務試圖對整個表進行加排它鎖之前，首先需要獲得這個表的意向排它鎖。

4 、mysql執行計劃怎麼看

執行計劃就是sql的執行查詢順序，以及如何使用索引查詢，傳回的結果集的行數

1 、 id：是一個有順序的編号，是查詢的順序号，有幾個select就顯示幾行。id的順序是按select出現的順序增長的。id列的值越大執行優先級越高越先執行，id列的值相同則從上往下執行，id列的值為NULL最後執行。

2 、selectType 表示查詢中每個select子句的類型

• SIMPLE：表示查詢中每個select子句的類型
• PRIMARY：表示此查詢是最外層的查詢（包括子查詢）
• SUBQUERY：子查詢中的第一個SELECT
• UNION：表示此查詢是UNION的第二或後面的查詢語句，取決于外面的查詢
• UNION RESULT,UNION 的結果
• EEPENDENT SUBQUERY：子查詢中的第一個SELECT,取決于外面的查詢，即子查詢依賴于外層查詢的結果。
• DERIVED：衍生，表示導出表的SELECT(FROM子句的子查詢）

3 、table：表示該語句查詢的表

4 、type：優化sql的重要字段，也就是我們判斷sql性能和優化程度的重要名額。他的取值類型範圍：

• const：通過索引一次名中，比對一行資料
• system：表中隻有一行記錄，相當于系統表
• eq_ref：唯一性索引掃描，對于一個索引鍵，表中隻有一條記錄與之比對
• ref：非唯一性索引掃描，傳回比對某個值的所有
• range：隻檢索給定範圍的行，使用一個索引來選擇行，一般用于between、<>;
• index：隻周遊索引樹；
• ALL：表示全表掃描，這個類型的查詢是性能最差的查詢之一。那麼基本就是随着表的數量增多，執行效率越慢

執行效率 ALL < index < range < ref < eq_ref < const <system。最好是避免ALL和index

5 、possible_key： 它表示MySQL在執行該sql語句的時候，可能用到的索引資訊，僅僅是可能，實際不一定會用到。

6 、 key：此字段是mysql在目前查詢時所真正使用到的索引。它是possible_keys的子集

5 、事務的基本特性和隔離級别

事務的基本特性和隔離級别

原子性：指的是一個事務中的操作要麼全部成功，要麼全部失敗。

一緻性：指的是：資料庫總是從一個一緻性的狀态轉換到另外一個一緻性的狀态。比如A轉賬給B100塊錢，假設A隻有90塊，支付之前我們資料庫裡的資料都是符合限制的，但是如果事務執行成功了，我們的資料庫資料就破壞限制了，是以事務不能成功，這裡我們說事務提供了一緻性的保證。

隔離性：指的是一個事務的修改在最終送出前，對其他事務是不可見的。

持久性指的是：一旦事務送出，所做的修改就會永久儲存到資料庫中。

隔離性有四個隔離級别，分别是：

• read uncommit 讀未送出，可能會讀到其他事務未送出的資料，也叫髒讀。

  使用者本來應該讀取到id=1的使用者age應該是0，結果讀取到了其他事務還沒有送出的事務，結果讀取結果age=20，這就是髒讀。

• read commit 讀已送出，兩次讀取結果不一緻，叫做不可重複讀。

  不可重複讀解決了髒讀的問題，他隻會讀取已經送出的事務。

  使用者開啟事務讀取id=1使用者，查詢到age=10，再次讀取發現結果=20，在同一個事務裡同一個查詢讀取到不同的結果叫做不可重複度。

• repeatable read 可重複度，這是mysql的預設級别，就是每次讀取結果都一樣，但是有可能産生幻讀。
• serializable串行，一般是不會使用的，他會給每一行讀取的資料加鎖，會導緻大量逾時和鎖競争的問題。

髒讀（Drity Read）：某個事務已更新一份資料，另一個事務在此時讀取了同一份資料，由于某些原因，前一個RollBack了操作，則後一個事務所讀取的資料就會是不正确的。