資料庫知識點整理

資料庫

• 基礎知識點
• • 第一部分
• MySQL
• • 第一部分
• Redis
• session cookie cache token差別

基礎知識點

第一部分

1. 資料庫事務特性（ACID特性）
   • 原子性（Atomicity）整個事務中的操作要麼完成要麼不完成。事務執行過程中出錯，則復原(rollback)到事務開始之前的狀态。
   • 一緻性 (Correspondence) 事務執行前後資料庫的狀态都是有效的。
   • 隔離性 (Isolation) 隔離狀态執行事務，即在相同時間内執行的兩個事務互相隔離，不能通路到其他事務操作中的狀态。
   • 持久性 (Durability) 事務操作一旦送出，就永久儲存在資料庫中。
2. 資料庫索引及其優缺點
   • 什麼是資料庫索引
     • 索引是一種資料結構，已排好序，友善查詢資料庫。
     • 主要采用的資料結構包括，Hash Map， 紅黑樹，B樹， B+樹。
   • 優缺點
     • B+樹和Hash Map的差別
       • Hash Map适合等值查詢，無法進行範圍查詢
       • Hash Map沒辦法利用索引完成排序，不支援多列聯合
       • 如果有大量重複key的話，哈希索引效率較低，因為存在哈希碰撞問題。
     • 聚集索引和非聚集索引
       • 聚集索引（資料檔案就是索引檔案）
         • 葉子節點存的是整行資料，可以直接通過聚集索引的key找到某行
         • 資料的實體存放順序和索引順序是一緻的，隻要索引是相鄰的，對應的資料在磁盤上也是相鄰的
         • 一個表隻能有一個聚集索引（資料存放位置隻有一個）
       • 非聚集索引
         • 葉子節點存儲的是指針，該指針指向表中相應的資料行。需要回表進行二次查詢。
     • 輔助索引
       • 非主鍵索引，允許重複key，葉子節點存儲主鍵或位址（InnoDB和MyISAM不太一樣）
     • 覆寫索引
       • 查詢列時，從索引中直接擷取查詢結果。
     • 聯合索引及最左字首比對
       • 建立（k1, k2, k3）聯合索引，相當于建立了（k1）,（k1，k2）,（k1, k2, k3）三個索引，最左比對原則。

3. 資料庫邏輯設計之三大範式（Normal Format）

   參考博文

   資料庫設計範式是資料庫設計所需要滿足的規範。

   • 1NF，字段原子性。字段不可再分割，關系型資料庫，預設滿足第一範式。
   • 2NF，記錄唯一性。要求記錄具有唯一性，屬性完全依賴于主鍵，不存在部分依賴。

     反例： 表 學号 課程号 姓名 學分

     學分依賴課程号，姓名依賴學号，存在部分依賴。
     • 可能存在的問題：
       • 資料備援
       • 删除異常
       • 插入異常
       • 更新異常
   • 3NF，字段不備援。任何字段不能由其他字段派生出來，不存在傳遞依賴。

     反例：表 學号 姓名 年齡 學院名稱 學院電話

     學院名稱和學院電話存在傳遞依賴
     • 可能存在的問題：
       • 資料備援
       • 更新異常
   • 反範式化。沒有備援的資料庫未必是最好的資料庫，有時為了提高運作效率，就必須降低範式标準，适當保留備援資料。

MySQL

第一部分

1. 多使用者并發操作可能出現的問題
   • 髒讀：A事務讀取了B事務執行過程的中間狀态
   • 不可重複讀：A事務執行過程多次查詢的結果不一樣(在A事務執行過程中，B事務修改了資料庫)
   • 幻讀：A事務執行過程中，B事務修改了資料庫，導緻幻讀。差別于不可重複讀，前者針對一批資料（例如資料條目），後者針對同一個資料項。

2. MySQL定義的四種隔離級别

   低級别的隔離一般支援更高的并發處理，并且擁有更低的系統開銷。

   • Read UnCommitted：髒讀，讀到其他事務未送出的資料。
   • Read Committed 不可重複讀：大多數資料庫系統的預設隔離級别。一個事務送出前不可見。兩次讀取結果不一緻。
   • Repeatable Read 可重複讀：MySQL資料庫預設的隔離級别。每次讀取結果一樣（讀取的結果不一定是最新事務送出的，但一定是已送出事務），不過，這會導緻另外一個棘手問題“幻讀”。InnoDB和Falcon存儲引擎通過多版本并發控制機制解決了幻讀問題。
   • Serializable：它通過強制事務排序，使之不可能互相沖突，進而解決幻讀問題。簡而言之，SERIALIZABLE是在每個讀的資料行上加鎖。在這個級别，可能導緻大量的逾時Timeout和鎖競争Lock Contention現象，實際應用中很少使用到這個級别，但如果使用者的應用為了資料的穩定性，需要強制減少并發的話，也可以選擇這種隔離級。
3. MySQL的搜尋引擎

   功能	MyISAM	MEMORY	InnoDB	Archive
   存儲限制	256TB	RAM	64TB	None
   支援事務	No	No	Yes	No
   支援全文索引	Yes	No	No	No
   支援樹索引	Yes	Yes	Yes	No
   支援哈希索引	No	Yes	No	No
   支援資料緩存	No	N/A	Yes	No
   支援外鍵	No	No	Yes	No

   • 如果要提供送出、復原和恢複的事務安全（ACID 相容）能力，并要求實作并發控制，InnoDB 是一個很好的選擇。
   • 如果資料表主要用來插入和查詢記錄，則 MyISAM 引擎提供較高的處理效率
   • 如果隻是臨時存放資料，資料量不大，并且不需要較高的資料安全性，可以選擇将資料儲存在記憶體的 MEMORY 引擎中，MySQL 中使用該引擎作為臨時表，存放查詢的中間結果
   • 如果隻有 INSERT 和 SELECT 操作，可以選擇Archive 引擎，Archive 存儲引擎支援高并發的插入操作，但是本身并不是事務安全的。Archive 存儲引擎非常适合存儲歸檔資料，如記錄日志資訊可以使用 Archive 引擎

4. 索引下推（index condition pushdown ，ICP）

   參考博文 對查詢條件包含like的較為适用，無法通過最左比對原則将查詢條件一一等值比對，則其他條件就需要伺服器或搜尋引擎來判斷。

   • 即索引條件下沉到搜尋引擎。
   • 不使用ICP，存儲引擎通過索引檢索到資料，再傳回MySQL伺服器，伺服器判斷是否滿足條件
   • 使用ICP，如果存在索引的列為判斷條件之一時，伺服器将判斷條件傳遞給存儲引擎，通過存儲引擎判斷索引是否符合MySQL伺服器傳遞條件，符合時才傳遞
   • 索引條件下推優化可以減少存儲引擎查詢基礎表的次數，也可以減少MySQL伺服器從存儲引擎接收資料的次數，IO操作上的優化。
5. 查詢優化器
   • explain檢視sql語句執行計劃，看有沒有走索引查詢，分析情況
   • MySQL的查詢優化器，優化過程如下：
     • 根據搜尋條件，找出所有可能使用的索引
     • 計算全表掃描的代價
     • 計算使用不同索引執行查詢的代價
     • 對比各種執行方案的代價，找出成本最低的一個

Redis

session cookie cache token差別