索引

1. 百萬級別或以上的數據如何刪除？
   關於索引：由於索引需要額外的維護成本，因為索引文件是單獨存在的文件，所以當我們對數據的增加、修改、刪除都會產生額外的對索引文件的操，這些操作需要消耗額外的IO，會降低增/改/刪的執行效率。所以，在我們刪除資料庫百萬級別數據的時候，查詢MySQL官方手冊得知刪除數據的速度和創建的索引數量是成正比的。

   • 所以我們想要刪除百萬數據的時候可以先刪除索引（此時大概耗時三分多鐘）。
   • 然後刪除其中無用數據（此過程需要不到兩分鐘）。
   • 刪除完成後重新創建索引(此時數據較少了)創建索引也非常快，約十分鐘左右。
   • 與之前的直接刪除絕對是要快速很多，更別說萬一刪除中斷，一切刪除會回滾。那更是坑了。

2. 首碼索引

   • 語法：index(field(10))，使用欄位值的前10個字元建立索引，預設是使用欄位的全部內容建立索引。
   • 前提：首碼的標識度高。比如密碼就適合建立首碼索引，因為密碼幾乎各不相同。
   • 實操的難度：在於首碼截取的長度。
   • 我們可以利用 select count(*)/count(distinct left(password,prefixLen));，通過從調整prefixLen的值（從1自增）查看不同首碼長度的一個平均匹配度，接近1時就可以了（表示一個密碼的前prefixLen個字元幾乎能確定唯一一條記錄）。

3. 什麼是最左首碼原則？什麼是最左匹配原則?

   • 顧名思義，就是最左優先，在創建多列索引時，要根據業務需求，where子句中使用最頻繁的一列放在最左邊。
   • 最左首碼匹配原則，非常重要的原則，mysql會一直向右匹配直到遇到範圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4
   • 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整。=和in可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優化器會幫你優化成索引可以識別的形式。

4. B樹和B+樹的區別
   

5. 使用B樹的好處

   • B樹可以在內部節點同時存儲鍵和值，因此，把頻繁訪問的數據放在靠近根節點的地方將會大大提高熱點數據的查詢效率。這種特性使得B樹在特定數據重覆多次查詢的場景中更加高效。

6. 使用B+樹的好處

   • 由於B+樹的內部節點只存放鍵，不存放值，因此，一次讀取，可以在記憶體頁中獲取更多的鍵，有利於更快地縮小查找範圍。 B+樹的葉節點由一條鏈相連，因此，當需要進行一次全數據遍歷的時候，B+樹只需要使用O(logN)時間找到最小的一個節點，然後通過鏈進行O(N)的順序遍歷即可。而B樹則需要對樹的每一層進行遍歷，這會需要更多的記憶體置換次數，因此也就需要花費更多的時間。

7. Hash索引和B+樹所有有什麼區別或者說優劣呢?
   首先要知道Hash索引和B+樹索引的底層實現原理：

   • hash索引底層就是hash表，進行查找時，調用一次hash函數就可以獲取到相應的鍵值，之後進行回表查詢獲得實際數據。B+樹底層實現是多路平衡查找樹。對於每一次的查詢都是從根節點出發，查找到葉子節點方可以獲得所查鍵值，然後根據查詢判斷是否需要回表查詢數據。

   • 那麼可以看出他們有以下的不同：

   • 1.hash索引進行等值查詢更快(一般情況下)，但是卻無法進行範圍查詢。

   • 2.因為在hash索引中經過hash函數建立索引之後，索引的順序與原順序無法保持一致，不能支持範圍查詢。而B+樹的的所有節點皆遵循(左節點小於父節點，右節點大於父節點，多叉樹也類似)，天然支持範圍。

   • 3.hash索引不支持使用索引進行排序，原理同上。

   • 4.hash索引不支持模糊查詢以及多列索引的最左首碼匹配。原理也是因為hash函數的不可預測。AAAA和AAAAB的索引沒有相關性。

   • 5.hash索引任何時候都避免不了回表查詢數據，而B+樹在符合某些條件(聚簇索引，覆蓋索引等)的時候可以只通過索引完成查詢。

   • 6.hash索引雖然在等值查詢上較快，但是不穩定。性能不可預測，當某個鍵值存在大量重覆的時候，發生hash碰撞，此時效率可能極差。而B+樹的查詢效率比較穩定。 對於所有的查詢都是從根節點到葉子節點，且樹的高度較低。

因此，在大多數情況下，直接選擇B+樹索引可以獲得穩定且較好的查詢速度。而不需要使用hash索引。

8. 資料庫為什麼使用B+樹而不是B樹？
   • B樹只適合隨機檢索，而B+樹同時支持隨機檢索和順序檢索。
   • B+樹空間利用率更高，可減少I/O次數，磁碟讀寫代價更低。一般來說，索引本身也很大，不可能全部存儲在記憶體中，因此索引往往以索引文件的形式存儲的磁碟上。這樣的話，索引查找過程中就要產生磁碟I/O消耗。B+樹的內部結點並沒有指向關鍵字具體信息的指針，只是作為索引使用，其內部結點比B樹小，盤塊能容納的結點中關鍵字數量更多，一次性讀入記憶體中可以查找的關鍵字也就越多，相對的，IO讀寫次數也就降低了。而IO讀寫次數是影響索引檢索效率的最大因素。
   • B+樹的查詢效率更加穩定。B樹搜索有可能會在非葉子結點結束，越靠近根節點的記錄查找時間越短，只要找到關鍵字即可確定記錄的存在，其性能等價於在關鍵字全集內做一次二分查找。而在B+樹中，順序檢索比較明顯，隨機檢索時，任何關鍵字的查找都必須走一條從根節點到葉節點的路，所有關鍵字的查找路徑長度相同，導致每一個關鍵字的查詢效率相當。
   • B-樹在提高了磁碟IO性能的同時並沒有解決元素遍歷的效率低下的問題。B+樹的葉子節點使用指針順序連接在一起，只要遍歷葉子節點就可以實現整棵樹的遍歷。而且在資料庫中基於範圍的查詢是非常頻繁的，而B樹不支持這樣的操作。
   • 增刪文件（節點）時，效率更高。因為B+樹的葉子節點包含所有關鍵字，並以有序的鏈表結構存儲，這樣可很好提高增刪效率。

事務

9. 什麼是資料庫事務？

   • 事務是一個不可分割的資料庫操作序列，也是資料庫併發控制的基本單位，其執行的結果必須使資料庫從一種一致性狀態變到另一種一致性狀態。事務是邏輯上的一組操作，要麼都執行，要麼都不執行。
   • 事務最經典也經常被拿出來說例子就是轉賬了。
   • 假如小明要給小紅轉賬1000元，這個轉賬會涉及到兩個關鍵操作就是：將小明的餘額減少1000元，將小紅的餘額增加1000元。萬一在這兩個操作之間突然出現錯誤比如銀行系統崩潰，導致小明餘額減少而小紅的餘額沒有增加，這樣就不對了。事務就是保證這兩個關鍵操作要麼都成功，要麼都要失敗。

10. 事物的四大特性(ACID)介紹一下?
    關係性資料庫需要遵循ACID規則，具體內容如下：

    • 原子性： 事務是最小的執行單位，不允許分割。事務的原子性確保動作要麼全部完成，要麼完全不起作用；
    • 一致性： 執行事務前後，數據保持一致，多個事務對同一個數據讀取的結果是相同的；
    • 隔離性： 併發訪問資料庫時，一個用戶的事務不被其他事務所干擾，各併發事務之間資料庫是獨立的；
    • 持久性： 一個事務被提交之後。它對資料庫中數據的改變是持久的，即使資料庫發生故障也不應該對其有任何影響。

11. 什麼是臟讀？幻讀？不可重覆讀？

    • 臟讀(Drity Read)：某個事務已更新一份數據，另一個事務在此時讀取了同一份數據，由於某些原因，前一個RollBack了操作，則後一個事務所讀取的數據就會是不正確的。
    • 不可重覆讀(Non-repeatable read)：在一個事務的兩次查詢之中數據不一致，這可能是兩次查詢過程中間插入了一個事務更新的原有的數據。
    • 幻讀(Phantom Read)：在一個事務的兩次查詢中數據筆數不一致，例如有一個事務查詢了幾列(Row)數據，而另一個事務卻在此時插入了新的幾列數據，先前的事務在接下來的查詢中，就會發現有幾列數據是它先前所沒有的。

12. 什麼是事務的隔離級別？MySQL的預設隔離級別是什麼？
    

    • SQL 標准定義了四個隔離級別：

    • READ-UNCOMMITTED(讀取未提交)： 最低的隔離級別，允許讀取尚未提交的數據變更，可能會導致臟讀、幻讀或不可重覆讀。

    • READ-COMMITTED(讀取已提交)： 允許讀取併發事務已經提交的數據，可以阻止臟讀，但是幻讀或不可重覆讀仍有可能發生。

    • REPEATABLE-READ(可重覆讀)： 對同一欄位的多次讀取結果都是一致的，除非數據是被本身事務自己所修改，可以阻止臟讀和不可重覆讀，但幻讀仍有可能發生。

    • SERIALIZABLE(可串列化)： 最高的隔離級別，完全服從ACID的隔離級別。所有的事務依次逐個執行，這樣事務之間就完全不可能產生干擾，也就是說，該級別可以防止臟讀、不可重覆讀以及幻讀。

    • 需要註意的是：Mysql 預設採用的 REPEATABLE-READ隔離級別，Oracle 預設採用的 READ-COMMITTED隔離級別。

    • 因為隔離級別越低，事務請求的鎖越少，所以大部分資料庫系統的隔離級別都是READ-COMMITTED(讀取提交內容)，但是你要知道的是InnoDB 存儲引擎預設使用 REPEATABLE-READ（可重讀）並不會有任何性能損失。

    • InnoDB 存儲引擎在 分散式事務 的情況下一般會用到SERIALIZABLE(可串列化)隔離級別。

鎖

13. 對MySQL的鎖瞭解嗎？

    • 當資料庫有併發事務的時候，可能會產生數據的不一致，這時候需要一些機制來保證訪問的次序，鎖機制就是這樣的一個機制。
    • 就像酒店的房間，如果大家隨意進出，就會出現多人搶奪同一個房間的情況，而在房間上裝上鎖，申請到鑰匙的人才可以入住並且將房間鎖起來，其他人只有等他使用完畢才可以再次使用。

14. 隔離級別與鎖的關係：

    • 在Read Uncommitted級別下，讀取數據不需要加共用鎖，這樣就不會跟被修改的數據上的排他鎖衝突。
    • 在Read Committed級別下，讀操作需要加共用鎖，但是在語句執行完以後釋放共用鎖。
    • 在Repeatable Read級別下，讀操作需要加共用鎖，但是在事務提交之前並不釋放共用鎖，也就是必須等待事務執行完畢以後才釋放共用鎖。
    • SERIALIZABLE 是限制性最強的隔離級別，因為該級別鎖定整個範圍的鍵，並一直持有鎖，直到事務完成。

15. 按照鎖的粒度分資料庫鎖有哪些？鎖機制與InnoDB鎖演算法：

    • 在關係型資料庫中，可以按照鎖的粒度把資料庫鎖分為行級鎖(INNODB引擎)、表級鎖(MYISAM引擎)和頁級鎖(BDB引擎 )。

    • 行級鎖，表級鎖和頁級鎖對比：

    • 行級鎖 行級鎖是Mysql中鎖定粒度最細的一種鎖，表示只針對當前操作的行進行加鎖。行級鎖能大大減少資料庫操作的衝突。其加鎖粒度最小，但加鎖的開銷也最大。行級鎖分為共用鎖 和 排他鎖。特點：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低，併發度也最高。

    • 表級鎖 表級鎖是MySQL中鎖定粒度最大的一種鎖，表示對當前操作的整張表加鎖，它實現簡單，資源消耗較少，被大部分MySQL引擎支持。最常使用的MYISAM與INNODB都支持表級鎖定。表級鎖定分為表共用讀鎖（共用鎖）與表獨占寫鎖（排他鎖）。特點：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發出鎖衝突的概率最高，併發度最低。

    • 頁級鎖 頁級鎖是MySQL中鎖定粒度介於行級鎖和表級鎖中間的一種鎖。表級鎖速度快，但衝突多，行級衝突少，但速度慢。所以取了折衷的頁級，一次鎖定相鄰的一組記錄。特點：開銷和加鎖時間界於表鎖和行鎖之間；會出現死鎖；鎖定粒度界於表鎖和行鎖之間，併發度一般。

16. 從鎖的類別上分MySQL都有哪些鎖呢？

    • 從鎖的類別上來講，有共用鎖和排他鎖。

    • 共用鎖：又叫做讀鎖。 當用戶要進行數據的讀取時，對數據加上共用鎖。共用鎖可以同時加上多個。

    • 排他鎖：又叫做寫鎖。 當用戶要進行數據的寫入時，對數據加上排他鎖。排他鎖只可以加一個，他和其他的排他鎖，共用鎖都相斥。

    • 用上面的例子來說就是用戶的行為有兩種，一種是來看房，多個用戶一起看房是可以接受的。 一種是真正的入住一晚，在這期間，無論是想入住的還是想看房的都不可以。

    • 鎖的粒度取決於具體的存儲引擎，InnoDB實現了行級鎖，頁級鎖，表級鎖。他們的加鎖開銷從大到小，併發能力也是從大到小。

17. MySQL中InnoDB引擎的行鎖是怎麼實現的？

    • InnoDB是基於索引來完成行鎖
    • 例: select * from tab_with_index where id = 1 for update;
    • for update 可以根據條件來完成行鎖鎖定，並且 id 是有索引鍵的列，如果 id 不是索引鍵那麼InnoDB將完成表鎖，併發將無從談起。

18. InnoDB存儲引擎的鎖的演算法有三種：

    • Record lock：單個行記錄上的鎖。
    • Gap lock：間隙鎖，鎖定一個範圍，不包括記錄本身。
    • Next-key lock：record+gap 鎖定一個範圍，包含記錄本身。

19. 什麼是死鎖？怎麼解決？

    • 死鎖是指兩個或多個事務在同一資源上相互占用，並請求鎖定對方的資源，從而導致惡性迴圈的現象。

    • 常見的解決死鎖的方法：

    • 1、如果不同程式會併發存取多個表，儘量約定以相同的順序訪問表，可以大大降低死鎖機會。

    • 2、在同一個事務中，儘可能做到一次鎖定所需要的所有資源，減少死鎖產生概率。

    • 3、對於非常容易產生死鎖的業務部分，可以嘗試使用升級鎖定顆粒度，通過表級鎖定來減少死鎖產生的概率。

    • 如果業務處理不好可以用分散式事務鎖或者樂觀鎖。

20. 資料庫的樂觀鎖和悲觀鎖是什麼？怎麼實現的？

    • 資料庫管理系統（DBMS）中的併發控制的任務是確保在多個事務同時存取資料庫中同一數據時不破壞事務的隔離性和統一性以及資料庫的統一性。樂觀併發控制（樂觀鎖）和悲觀併發控制（悲觀鎖）是併發控制主要採用的技術手段。
    • 悲觀鎖：假定會發生併發衝突，屏蔽一切可能違反數據完整性的操作。在查詢完數據的時候就把事務鎖起來，直到提交事務。實現方式：使用資料庫中的鎖機制
    • 樂觀鎖：假設不會發生併發衝突，只在提交操作時檢查是否違反數據完整性。在修改數據的時候把事務鎖起來，通過version的方式來進行鎖定。實現方式：樂一般會使用版本號機制或CAS演算法實現。
    • 兩種鎖的使用場景：
    • 從上面對兩種鎖的介紹，我們知道兩種鎖各有優缺點，不可認為一種好於另一種，像樂觀鎖適用於寫比較少的情況下（多讀場景），即衝突真的很少發生的時候，這樣可以省去了鎖的開銷，加大了系統的整個吞吐量。
    • 但如果是多寫的情況，一般會經常產生衝突，這就會導致上層應用會不斷的進行retry，這樣反倒是降低了性能，所以一般多寫的場景下用悲觀鎖就比較合適。效率更高。因為B+樹的葉子節點包含所有關鍵字，並以有序的鏈表結構存儲，這樣可很好提高增刪效率。