資料庫知識不完全總結--春招實習

yvonneit發表於2019-03-09
資料庫

基本SQL

DDL、DML和DCL

  • DDL：資料定義語言，主要用於定義或改變表（TABLE）的結構，資料型別，表之間的連結和約束等初始化工作，如CREATE、ALTER、DROP，TRUNCATE
  • DML：資料操作語言，用於處理資料，如SELECT（查詢）、INSERT（新增）、UPDATE、DELETE、CALL、EXPLANIN PLAN、LOCK TABLE
  • DCL：資料控制語言，用來授予或回收訪問資料庫的某種特權，並控制資料庫操縱事務發生的時間及效果，對資料庫實行監視等。如COMMIT、SAVEPOINT、SET TRANSACTION

MySQL伺服器的邏輯架構分為幾層

  • 第一層：服務層(為客戶端服務): 為請求做連線處理，授權認證，安全等。
  • 第二層：Mysql核心服務層：主要提供，查詢解析、分析、優化、快取以及內建函式，跨儲存引擎功能（儲存過程、檢視、觸發器）
  • 第三層：儲存引擎層，負責資料的儲存和提取

20190302221137.png

MySQL常見的日誌種類？redo、undo、binlog 日誌的作用

二進位制日誌（binlog） 錯誤日誌（errorlog） 慢查詢日誌（slow query log） 一般查詢日誌（general log）

Undo日誌記錄某資料被修改前的值，可以用來在事務失敗時進行rollback；

Redo日誌記錄某資料塊被修改後的值，可以用來恢復未寫入data file的已成功事務更新的資料

連線查詢和子查詢的比較

子查

mysql中in 和exists 區別

drop、delete、truncate 比較

  • truncate用於清除表內所有資料，delete如果不帶where也會刪除指定表的所有資料。但是truncate 和 delete 只刪除資料不刪除表的結構。
  • drop會將表刪除，同時表的約束、觸發器(trigger)、索引(index)也會刪除。但依賴於該表的儲存過程和函式會保留，但會變成無效
  • delete 操作會放到 rollback segement 中，事務提交之後才生效；如果有相應的 trigger，執行的時候將被觸發。 truncate、drop 操作立即生效，原資料不放到 rollback segment 中，不能回滾，操作不觸發 trigger

select for update

什麼是檢視？什麼時候更新檢視

檢視是一種虛擬存在的表

資料庫自增主鍵可能的問題

SQL慢查詢的優化

資料結構

B+樹

事務

什麼是事務

事務就是一組原子性的SQL語句，或者說一個獨立的工作單元，事務內的語句要麼全部執行成功，要麼全部執行失敗。

事務的特性

  1. 原子性（Atomicity）：事務被視為不可分割的最小單元，事務的所有操作要麼全部提交成功，要麼全部失敗回滾。回滾可以用回滾日誌來實現，回滾日誌記錄著事務所執行的修改操作，在回滾時反向執行這些修改操作即可。
  2. 一致性（Consistency）：資料庫在事務執行前後都保持一致性狀態。在一致性狀態下，所有事務對一個資料的讀取結果都是相同的。
  3. 隔離性（Isolation）：一個事務所做的修改在最終提交以前，對其它事務是不可見的。
  4. 永續性（Durability）：一旦事務提交，則其所做的修改將會永遠儲存到資料庫中。即使系統發生崩潰，事務執行的結果也不能丟失。使用重做日誌來保證永續性。

只有滿足一致性，事務的執行結果才是正確的。

在無併發的情況下，事務序列執行，隔離性一定能夠滿足。此時只要能滿足原子性，就一定能滿足一致性。

在併發的情況下，多個事務並行執行，事務不僅要滿足原子性，還需要滿足隔離性，才能滿足一致性。

事務滿足持久化是為了能應對資料庫崩潰的情況

在併發環境下，資料庫可能出現的併發一致性問題

  1. 髒讀（讀取未提交資料）：T1 修改一個資料，T2 隨後讀取這個資料。如果 T1 撤銷了這次修改，那麼 T2 讀取的資料是髒資料。
  2. 不可重複讀（前後多次讀取，資料內容不一致）：T2 讀取一個資料，T1 對該資料做了修改。如果 T2 再次讀取這個資料，此時讀取的結果和第一次讀取的結果不同
  3. 幻讀（前後多次讀取，資料總量不一致）：T1 讀取某個範圍的資料，T2 在這個範圍內插入新的資料，T1 再次讀取這個範圍的資料，此時讀取的結果和和第一次讀取的結果不同。

產生併發不一致主要原因是破壞事務的隔離性。解決方法是通過併發控制（鎖）來保證隔離性。

不可重複讀和幻讀的區別

  1. 不可重複讀是讀取了其他事務更改的資料，針對update操作 解決：使用行級鎖，鎖定該行，事務A多次讀取操作完成後才釋放該鎖，這個時候才允許其他事務更改剛才的資料。

  2. 幻讀是讀取了其他事務新增的資料，針對insert操作 解決：使用表級鎖，鎖定整張表，事務A多次讀取資料總量之後才釋放該鎖，這個時候才允許其他事務新增資料。

Mysql的鎖粒度

  • 表級鎖：Mysql中鎖定 粒度最大 的一種鎖，對當前操作的整張表加鎖，實現簡單 ，資源消耗也比較少，加鎖快，不會出現死鎖 。其鎖定粒度最大，觸發鎖衝突的概率最高，併發度最低，MyISAM和 InnoDB引擎都支援表級鎖
  • 行級鎖：Mysql中鎖定 粒度最小 的一種鎖，只針對當前操作的行進行加鎖。 行級鎖能大大減少資料庫操作的衝突。其加鎖粒度最小，併發度高，但加鎖的開銷也最大，加鎖慢，會出現死鎖。 InnoDB支援的行級鎖。
    • Record Lock: 對索引項加鎖，鎖定符合條件的行。其他事務不能修改和刪除加鎖項；
    • Gap Lock: 對索引項之間的“間隙”加鎖，鎖定記錄的範圍（對第一條記錄前的間隙或最後一條將記錄後的間隙加鎖），不包含索引項本身。其他事務不能在鎖範圍內插入資料，這樣就防止了別的事務新增幻影行。
    • Next-key Lock： 鎖定索引項本身和索引範圍。即Record Lock和Gap Lock的結合。可解決幻讀問題。

MyISAM採用表級鎖，InnoDB支援行級鎖和表級鎖,預設為行級鎖

MySQL鎖分類：共享鎖（S鎖）和排他鎖（X鎖），意向鎖

表級鎖和行級鎖可以進一步劃分為共享鎖（s）和排他鎖（X）

  • 共享鎖（Share Locks，簡記為S鎖）又被稱為讀鎖，其他使用者可以併發讀取資料，但任何事務都不能獲取資料上的排他鎖，直到已釋放所有共享鎖。

    共享鎖(S鎖)又稱為讀鎖，若事務T對資料物件A加上S鎖，則事務T只能讀A；其他事務只能再對A加S鎖，而不能加X鎖，直到T釋放A上的S鎖。這就保證了其他事務可以讀A，但在T釋放A上的S鎖之前不能對A做任何修改。

  • 排它鎖（(Exclusive lock,簡記為X鎖)）又稱為寫鎖，若事務T對資料物件A加上X鎖，則只允許T讀取和修改A，其它任何事務都不能再對A加任何型別的鎖，直到T釋放A上的鎖。它防止任何其它事務獲取資源上的鎖，直到在事務的末尾將資源上的原始鎖釋放為止。在更新操作(INSERT、UPDATE 或 DELETE)過程中始終應用排它鎖。

即加S鎖後，其他事務可以加S鎖但不能加X鎖，但只能讀資料。加X鎖，其他事務不能加鎖，加X鎖後可以讀寫資料

意向鎖：

意向鎖只有innodb引擎支援，是一種表鎖。

  • 意向共享鎖（intention shared lock, IS）：事務有意向對錶中的某些行加共享鎖（S鎖）。在獲取某些行的 S 鎖之前必須獲得IS鎖
  • 意向排他鎖（intention exclusive lock, IX）：事務有意向對錶中的某些行加排他鎖（X鎖）。事務要獲取某些行的 X 鎖，必須先獲得表的 IX 鎖。

意向鎖是引擎自己維護的，使用者無法操作意向鎖

意向鎖不會與行級的共享 / 排他鎖互斥!!

意向鎖存在的意義：如果另一個任務試圖在該表級別上應用共享或排它鎖，則受到由第一個任務控制的表級別意向鎖的阻塞。第二個任務在鎖定該表前不必檢查各個頁或行鎖，而只需檢查表上的意向鎖。

在存在行級鎖和表級鎖的情況下，事務 T 想要對錶 A 加 X 鎖，就需要先檢測是否有其它事務對錶 A 或者表 A 中的任意一行加了鎖，那麼就需要對錶 A 的每一行都檢測一次。這樣很耗時。

通過意向鎖可以解決問題，意向鎖會與普通的排他 / 共享鎖（這裡的鎖指表鎖）互斥。在事務T檢測到有其它事務持有意向排他鎖，則對該表加鎖就會被阻塞，而無需檢測每一行。

意向鎖之間互不排斥，但除了 IS 與 S 相容外，意向鎖會與 共享鎖 / 排他鎖 互斥。

IX，IS是表級鎖，不會和行級的X，S鎖發生衝突。只會和表級的X，S發生衝突。

意向鎖在保證併發性的前提下，實現了行鎖和表鎖共存且滿足事務隔離性的要求。

參考：juejin.im/post/5b8512…

MySQL的事務隔離級別

  • 未提交讀（READ UNCOMMITTED）：最低的隔離級別，事務中的修改，即使沒有提交，對其它事務也是可見的。可能會導致髒讀、幻讀或不可重複讀
  • 提交讀（READ COMMITTED）：一個事務只能讀取已經提交的事務所做的修改。換句話說，一個事務所做的修改在提交之前對其它事務是不可見的。可以阻止髒讀，但是幻讀或不可重複讀仍有可能發生
  • 可重複讀（REPEATABLE READ）：保證在同一個事務中多次讀取同樣資料的結果是一樣的。可以阻止髒讀和不可重複讀，但幻讀仍有可能發生。
  • 可序列化（SERIALIZABLE）：強制事務序列執行。該級別可以防止髒讀、不可重複讀以及幻讀

MySQL的MVCC含義，是如何實現的

MVCC指多版本控制，是為了應對高併發事發事務。Innodb引擎支援MVCC，MVCC只在 READ COMMITTED(提交讀) 和 REPEATABLE READ（可重複讀） 兩個隔離級別下工作。

MVVC是

Mysql索引

索引是什麼？作用？為什麼索引能提高查詢速度？索引的缺點

索引（Index）是幫助MySQL高效獲取資料的資料結構，也就是說索引是一種資料結構。作用是協助快速查詢、更新資料庫表中資料

為什麼能提高查詢速度:

MySQL在儲存資料是頁為基本資料結構的，各個資料頁可以組成一個雙向連結串列，每個資料頁中的記錄又可以組成一個單向連結串列。如果不使用索引在查詢時需要先遍歷雙向連結串列，找到所在的頁從所在的頁內中查詢相應的記錄:如果不是根據主鍵查詢，就只能遍歷所在頁的單連結串列

通過索引可以將無序的資料變成有序，可以通過查詢樹來快速定位到記錄所在的頁。B+樹索引就是為記錄建立B+樹來提高查詢的速度

索引的缺點：

索引需要佔物理和資料空間。

如果使用的是B+樹索引，B+樹是一顆平衡樹，如果我們對這顆樹增刪改的話，那肯定會破壞原有的資料結構。要維持平衡樹，就必須做額外的工作。正因為這些額外的工作開銷，導致索引會降低增刪改的速度。

所以如果表經常進行插入、刪除、修改，就不適合適用索引

資料庫有哪些索引？原理是什麼？

  1. 普通索引 :這是最基本的索引型別，而且它沒有唯一性之類的限制
  2. 唯一索引:這種索引和前面的“普通索引”基本相同，但有一個區別：索引列的所值都只能出現一次，即必須唯一。
  3. 主鍵索引：特殊的唯一索引，不允許有空值
  4. 全文索引:MySQL從3.23版開始支援全文索引和全文檢索。在MySQL中，全文索引索引型別為FULLTEXT。全文索引可以在VARCHAR或者TEXT型別的列上建立。
  5. 組合索引：複合索引指多個欄位上建立的索引，遵循”最左字首“原則

另一種分類：從資料結構方面

  1. B+Tree 索引：大多數 MySQL 儲存引擎的預設索引型別，
  2. 雜湊索引：雜湊索引能以 O(1) 時間進行查詢，但是失去了有序性：無法用於排序與分組；只支援精確查詢，無法用於部分查詢和範圍查詢。InnoDB 儲存引擎有一個特殊的功能叫“自適應雜湊索引”，當某個索引值被使用的非常頻繁時，會在 B+Tree 索引之上再建立一個雜湊索引，這樣就讓 B+Tree 索引具有雜湊索引的一些優點，比如快速的雜湊查詢
  3. 全文索引：MyISAM 儲存引擎支援全文索引，用於查詢文字中的關鍵詞，而不是直接比較是否相等。查詢條件使用 MATCH AGAINST，而不是普通的 WHERE。全文索引使用倒排索引實現，它記錄著關鍵詞到其所在文件的對映。InnoDB 儲存引擎在 MySQL 5.6.4 版本中也開始支援全文索引
  4. 空間資料索引：MyISAM 儲存引擎支援空間資料索引（R-Tree），可以用於地理資料儲存

原理：索引是資料結構，是資料庫管理系統中一個排序的資料結構，以協助快速查詢、更新資料庫表中資料。索引的實現通常使用B樹及其變種B+樹，或者hash。MySQL就普遍使用B+Tree實現其索引結構。

MySQL索引為什麼要用B+樹實現

聚簇索引和非聚簇索引（二級索引）區別

  • 聚簇索引:以主鍵建立的索引,以主鍵值的大小為頁和記錄的排序規則，在葉子節點處儲存的記錄包含了表中所有的列
  • 二級索引:以自定義的列的大小為頁和記錄的排序規則，在葉子節點處儲存的記錄內容是列 + 主鍵。然後再根據主鍵查詢資料（回表操作）。非聚簇索引在建立是可以是多列的

如果索引包含所有滿足查詢需要的資料的索引成為覆蓋索引，不需要回表操作。

如索引(username,age)，查詢資料的時候：select username , age from user where username = 'Java3y' and age = 20。明顯查詢時會用索引，且需要的資料都在索引中，所以不再需要回表。

hash索引

索引最左匹配原則是什麼

在mysql建立聯合索引時會遵循最左字首匹配的原則，即最左優先，在檢索資料時從聯合索引的最左邊開始匹配

儲存引擎

Mysql常見的儲存的引擎

  • innodb引擎：預設的事務型引擎，支援行鎖，採用MVCC來支援高併發，有可能死鎖 支援事務，支援外來鍵，支援崩潰後的安全恢復
  • MyISAM儲存引擎：擁有全文索引、壓縮、空間函式，不支援事務和行級鎖，不支援外來鍵，崩潰後無法安全恢復，對於不會進行修改的表，支援 壓縮表
  • MEMORY儲存引擎
  • Merge儲存引擎

Innodb和MyISAM的對比

MyISAM特點:

  • 不支援行鎖(MyISAM只有表鎖)，讀取時對需要讀到的所有表加鎖，寫入時則對錶加排他鎖；
  • 不支援事務
  • 不支援外來鍵
  • 不支援崩潰後的安全恢復
  • 在表有讀取查詢的同時，支援往表中插入新紀錄
  • 支援BLOB和TEXT的前500個字元索引，支援全文索引
  • 支援延遲更新索引，極大地提升了寫入效能
  • 對於不會進行修改的表，支援 壓縮表 ，極大地減少了磁碟空間的佔用

InnoDB: mysql 5.5之後預設的引擎

  • 支援行鎖，採用MVCC來支援高併發，有可能死鎖
  • 支援事務
  • 支援外來鍵
  • 支援崩潰後的安全恢復
  • 不支援全文索引

MyISAM更適合讀密集的表，而InnoDB更適合寫密集的的表。據庫做主從分離的情況下，經常選擇MyISAM作為從庫的儲存引擎

MySQL優化

水平切分和垂直切分

  • 水平切分：水平切分又稱為 Sharding，它是將同一個表中的記錄拆分到多個結構相同的表中。當一個表的資料不斷增多時，Sharding 是必然的選擇，它可以將資料分佈到叢集的不同節點上，從而快取單個資料庫的壓力

    水平切分策略： + 雜湊取模：hash(key) % N； + 範圍：可以是 ID 範圍也可以是時間範圍； + 對映表：使用單獨的一個資料庫來儲存對映關係。 存在的問題： + 事務的問題：分散式事務 + 連線 + ID唯一性：使用全域性唯一 ID（GUID），為每個分片指定一個 ID 範圍，分散式 ID 生成器 (如 Twitter 的 Snowflake 演算法)

  • 垂直切分是將一張表按列切分成多個表，通常是按照列的關係密集程度進行切分，也可以利用垂直切分將經常被使用的列和不經常被使用的列切分到不同的表中。

    在資料庫的層面使用垂直切分將按資料庫中表的密集程度部署到不同的庫中，例如將原來的電商資料庫垂直切分成商品資料庫、使用者資料庫等。

MySQL主從分離（讀寫分離）：為什麼需要主從分離？如何保證主資料庫和從資料庫同步？如何實現主從分離？主從分離的延遲問題如何解決

主從分離也就是讀寫分離：通常情況下是一個主資料庫(Master)負責寫操作，從資料庫（Slave，通常多個Slave）負責讀請求。主資料庫在一定情況下可能也會承擔讀請求，以應對延遲問題

主從分離主要是在讀多寫少的情況下使用

為什麼需要主從分離:

  • 主從伺服器負責各自的讀和寫，極大程度緩解了鎖的爭用；
  • 從伺服器可以使用 MyISAM，提升查詢效能以及節約系統開銷；
  • 增加冗餘，提高可用性。

主從分離的資料同步：

主要通過通過主從複製實現

主要涉及三個執行緒：binlog 執行緒、I/O 執行緒和 SQL 執行緒。

  • binlog 執行緒 ：負責將主伺服器上的資料更改寫入二進位制日誌（Binary log）中。
  • I/O 執行緒 ：負責從主伺服器上讀取二進位制日誌，並寫入從伺服器的重放日誌（Replay log）中。
  • SQL 執行緒 ：負責讀取重放日誌並重放其中的 SQL 語句。

主伺服器master由binlog 執行緒記錄資料庫操作日誌到Binary log，從伺服器開啟i/o執行緒將二進位制日誌記錄的操作同步到relay log（存在從伺服器的快取中），另外sql執行緒將relay log日誌記錄的操作在從伺服器執行

延遲問題： 延遲問題指的是主從資料庫的同步會有時間差，由此可能引發向從資料庫的讀請求異常。 解決方法：

  • 在Master上增加一個自增表，這個表僅含有1個的欄位。當Master接收到任何資料更新的請求時，均會觸發這個觸發器，該觸發器更新自增表中的記錄。由於自增表也參與Mysq的主從同步，因此在Master上作的 Update更新也會同步到Slave上。當Client通過Proxy進行資料讀取時，Proxy可以先向Master和Slave的自增表傳送查詢請求，當二者的資料相同時，Proxy可以認定 Master和Slave的資料狀態是一致的，然後把select請求傳送到Slave伺服器上，否則就傳送到Master上。

補充

三大正規化

  • 第一正規化（確保每列保持原子性） 資料庫表的所有欄位都不能再分解為更基本的資料單位。
  • 第二正規化（確保表中的每列都和主鍵相關） 第二正規化在第一正規化的基礎上更進一層，第二正規化需要確保資料庫表中每一列都和主鍵相關，而不能只與主鍵的某一部分相關（主要針對聯合主鍵而言）。也就是說在一個資料庫表中，一個表中只能儲存一種資料，不可以把多種資料儲存在同一張資料庫表中。
  • 第三正規化（確保每列都和主鍵列直接相關，而不是間接相關） 第三正規化需要確保資料表中的每一列資料都和主鍵直接相關，而不能間接相關。

參見：www.cnblogs.com/1906859953L…

一些問題

  • 手寫 SQL 語句，特別是連線查詢與分組查詢。

  • 連線查詢與子查詢的比較。

  • drop、delete、truncate 比較。

  • 檢視的作用，以及何時能更新檢視。

  • 理解儲存過程、觸發器等作用。

  • ACID 的作用以及實現原理。

  • 四大隔離級別，以及不可重複讀和幻影讀的出現原因。

  • 封鎖的型別以及粒度，兩段鎖協議，隱式和顯示鎖定。

  • 樂觀鎖與悲觀鎖。

  • MVCC 原理，當前讀以及快照讀，Next-Key Locks 解決幻影讀。

  • 正規化理論。

  • SQL 與 NoSQL 的比較。

  • B+ Tree 原理，與其它查詢樹的比較。

  • MySQL 索引以及優化。

  • 查詢優化。

  • InnoDB 與 MyISAM 比較。

  • 水平切分與垂直切分。

  • 主從複製原理、作用、實現。

  • redo、undo、binlog 日誌的作用。

  • MySQL資料庫的索引原理、與慢SQL優化的5大原則

  • 資料庫隔離級別有哪些，各自的含義是什麼，MYSQL預設的隔離級別是是什麼。

  • MYSQL有哪些儲存引擎，各自優缺點。

  • 高併發下，如何做到安全的修改同一行資料。

  • 樂觀鎖和悲觀鎖是什麼，INNODB的標準行級鎖有哪2種，解釋其含義。

  • SQL優化的一般步驟是什麼，怎麼看執行計劃，如何理解其中各個欄位的含義。

  • 資料庫會死鎖嗎，舉一個死鎖的例子，mysql怎麼解決死鎖。

  • MYsql的索引原理，索引的型別有哪些，如何建立合理的索引，索引如何優化。

  • 聚集索引和非聚集索引的區別。

  • select for update 是什麼含義，會鎖表還是鎖行或是其他。

  • 為什麼要用Btree實現，它是怎麼分裂的，什麼時候分裂，為什麼是平衡的。

  • 資料庫的ACID是什麼。

  • 某個表有近千萬資料，CRUD比較慢，如何優化。

  • Mysql怎麼優化table scan的。

  • 如何寫sql能夠有效的使用到複合索引。

  • mysql中in 和exists 區別。

  • 資料庫自增主鍵可能的問題。

  • 你做過的專案裡遇到分庫分表了嗎，怎麼做的，有用到中介軟體麼，比如sharding jdbc等,他們的原*理知道麼。

相關文章