技術文章 » 資料庫 基礎面試第一彈

資料庫 基礎面試第一彈

2023-09-05 06:00:38

1. SQL語句型別

1. DDL（Data Definition Language，資料定義語言）：

DDL語句用於定義資料庫物件（如表、索引、檢視等）。常見的DDL語句包括：

　　CREATE：用於建立資料庫物件，如建立表、索引、檢視等。

　　ALTER：用於修改資料庫物件的結構，如修改表的列、新增約束等。

　　DROP：用於刪除資料庫物件，如刪除表、索引、檢視等。

　　TRUNCATE：用於刪除表中的所有資料，但保留表結構

DDL（資料定義語言）範例：

# 建立表
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  age INT,
  salary DECIMAL(10, 2)
);

# 修改表結構
ALTER TABLE employees ADD COLUMN department VARCHAR(50);

DROP TABLE employees; #刪除表

2. DML（Data Manipulation Language，資料操作語言）：

DML語句用於對資料庫中的資料進行操作（插入、更新、刪除）。常見的DML語句包括：

  • SELECT：用於從資料庫中查詢資料。
  • INSERT：用於向表中插入新的資料。
  • UPDATE：用於更新表中的資料。
  • DELETE：用於刪除表中的資料。

DML（資料操作語言）範例：

INSERT INTO employees (id, name, age, salary)
VALUES (1, 'John Doe', 30, 5000);  # 插入資料

UPDATE employees SET salary = 6000 WHERE id = 1; #更新資料

DELETE FROM employees
WHERE id = 1;  # 刪除資料

3. DQL（Data Query Language，資料查詢語言）：

DQL語句用於從資料庫中查詢資料。DQL語句的核心是SELECT語句，可以使用SELECT語句查詢滿足特定條件的資料，並對結果進行排序、分組等處理

DQL（資料查詢語言）範例：

SELECT * FROM employees; 

SELECT * FROM employees
WHERE age > 25;     # 查詢特定條件的資料


SELECT name, salary FROM employees; # 查詢特定列的資料

4. DCL（Data Control Language，資料控制語言）

DCL語句用於對資料庫的存取許可權進行管理。常見的DCL語句包括：

  • GRANT：用於授予使用者存取許可權。
  • REVOKE：用於復原使用者的存取許可權。
  • DENY：用於拒絕使用者的存取許可權。

DCL（資料控制語言）範例：

GRANT SELECT, INSERT ON employees TO user1;  #授予使用者存取許可權

REVOKE SELECT, INSERT ON employees FROM user1; # 復原使用者的存取許可權

DENY SELECT, INSERT ON employees TO user1; # 拒絕使用者的存取許可權

2. 索引作用，底層結構及常見型別

索引在資料庫中起著重要的作用，它可以提高資料庫的查詢效能和資料的檢索速度。索引是一種資料結構，用於快速定位和存取資料庫中的特定資料。

作用：

  • 提高查詢效能：通過使用索引，可以減少資料庫查詢的資料量，從而提高查詢速度。
  • 加速資料檢索：索引可以幫助資料庫快速定位和存取滿足特定條件的資料，減少資料的掃描時間。

底層結構：
資料庫索引的底層結構可以有多種實現方式，常見的包括以下幾種：

  1. B-樹（B-Tree）索引：B-樹是一種平衡的多路搜尋樹，它的特點是可以自動調整樹的結構以適應資料的插入和刪除操作。B-樹索引常用於磁碟儲存的資料庫，因為它可以減少磁碟存取次數，提高查詢效率。

  2. B+樹（B+Tree）索引：B+樹是在B-樹的基礎上進行優化的一種資料結構。它與B-樹類似，但在葉子節點上儲存了所有的關鍵字和對應的資料指標，這樣可以加快範圍查詢和順序存取的速度。B+樹索引是大多數關係型資料庫中最常用的索引型別。

  3. 雜湊（Hash）索引：雜湊索引使用雜湊函數將關鍵字對映到一個固定長度的雜湊值，然後將雜湊值與資料的儲存位置關聯起來。雜湊索引適用於等值查詢，但不適用於範圍查詢或排序操作。

  4. 全文（Full-Text）索引：全文索參照於對文字內容進行搜尋，它可以對文字欄位中的關鍵詞進行索引和檢索，支援全文搜尋和模糊匹配。

常見型別：
在常見的關係型資料庫中，常用的索引型別包括：

    1. 主鍵索引（Primary Key Index）：用於唯一標識表中的記錄，保證主鍵的唯一性和索引的快速存取。

    2. 唯一索引（Unique Index）：用於保證某個列或列組合的唯一性，可以加速唯一性檢查。

    3. 聚集索引（Clustered Index）：指定表的物理順序，表中的記錄按照聚集索引的順序儲存。

    4. 非聚集索引（Non-Clustered Index）：不指定表的物理順序，獨立儲存索引的資料結構。

    5. 複合索引（Composite Index）：使用多個列組合作為索引的鍵，支援多個列的聯合查詢。

    6. 全文索引（Full-Text Index）：用於全文搜尋和模糊匹配的索引型別，支援對文字內容進行搜尋。

3. 事務的特性

  1. 原子性（Atomicity）：事務是一個原子操作單元，要麼全部執行成功，要麼全部失敗回滾。原子性確保事務中的所有操作要麼全都執行，要麼全都不執行，不會出現部分操作成功而部分操作失敗的情況。

  2. 一致性（Consistency）：事務在執行之前和執行之後，資料庫的完整性約束沒有被破壞。一致性確保資料庫從一個一致的狀態轉移到另一個一致的狀態，它定義了資料在事務執行過程中的合法變化。

  3. 隔離性（Isolation）：事務的執行是相互隔離的，一個事務的操作不會被其他並行事務所幹擾。隔離性確保事務在並行執行時，每個事務的操作都像是在獨立執行，避免了並行讀寫操作導致的資料不一致問題。

  4. 永續性（Durability）：一旦事務提交，其所做的修改將永久儲存在資料庫中，即使系統發生故障或重啟。永續性確保事務提交後的修改是永久性的，不會因為系統故障而丟失。

4. 事務的隔離級別

  1. 讀未提交（Read Uncommitted）：

    • 最低的隔離級別，事務中的未提交修改對其他事務都是可見的。
    • 可能導致髒讀（Dirty Read），即讀取到其他事務尚未提交的資料，可能是不一致的資料。
    • 存在幻讀（Phantom Read），即在同一個事務中多次執行同樣的查詢，結果集不一致。

  2. 讀已提交（Read Committed）：

    • 事務只能讀取到已經提交的資料，未提交的資料對其他事務不可見。
    • 避免了髒讀的問題，但仍可能導致幻讀。
    • 大多數常見資料庫的預設隔離級別。

  3. 可重複讀（Repeatable Read）：

    • 保證了在同一事務中多次讀取同一資料時，結果保持一致。
    • 讀取的資料是在事務開始時確定的快照，即使其他事務對資料進行修改也不可見。
    • 避免了髒讀和幻讀的問題。

  4. 序列化（Serializable）：

    • 最高的隔離級別，通過強制事務序列執行來避免並行問題。
    • 保證了事務之間的完全隔離，避免了髒讀、幻讀和不可重複讀的問題。
    • 效能較差，一般情況下只在特殊需求下使用。

5. 事務並行引起的三大問題

  1. 髒讀（Dirty Read）：

    • 髒讀指的是一個事務讀取了另一個事務尚未提交的資料。當一個事務讀取到了被另一個事務修改但尚未提交的資料時，如果另一個事務最終回滾，則讀取到的資料是無效的。
    • 髒讀可能導致資料不一致性和錯誤的結果。

  2. 不可重複讀（Non-repeatable Read）：

    • 不可重複讀指的是在同一個事務中，多次讀取同一資料時，得到的結果不一致。這是因為在讀取過程中，其他並行事務對該資料進行了修改或刪除。
    • 不可重複讀可能導致事務在多次讀取同一資料時無法保持一致性，破壞了事務的隔離性。

  3. 幻讀（Phantom Read）：

    • 幻讀是指在同一個事務中，多次執行同樣的查詢，得到的結果集不一致。這是因為在查詢過程中，其他並行事務插入了新的資料行，導致結果集發生了變化。
    • 幻讀可能導致事務在同一查詢中讀取到不同的資料行，無法保持一致性。

6. 死鎖的原因及解決辦法：

死鎖是指兩個或多個事務因為互相等待對方釋放資源而無法繼續執行的狀態。死鎖的發生是由於以下原因之一或多個原因共同作用：

  1. 互斥條件（Mutual Exclusion）：資源只能同時被一個事務佔用，當某個事務佔用了一個資源後，其他事務無法同時佔用該資源。

  2. 請求與保持條件（Hold and Wait）：一個事務在持有資源的同時，又申請其他事務所佔有的資源。

  3. 不可剝奪條件（No Preemption）：資源只能由持有者顯式釋放，其他事務無法強制搶佔。

  4. 迴圈等待條件（Circular Wait）：多個事務形成一個迴圈等待資源的鏈，每個事務都在等待下一個事務所佔有的資源。

為了解決死鎖問題，可以採取以下幾種常用的解決辦法：

  1. 預防死鎖（Deadlock Prevention）：

    • 通過破壞死鎖發生的四個必要條件中的一個或多個，來預防死鎖的發生。
    • 可以在系統設計階段採用資源分配策略、事務排程策略等方式來預防死鎖。

  2. 避免死鎖（Deadlock Avoidance）：

    • 在執行時動態判斷是否分配資源，避免可能導致死鎖的資源分配情況。
    • 通過資源分配的安全性檢查和資源請求的合理判斷，避免進入可能導致死鎖的狀態。

  3. 檢測與恢復（Deadlock Detection and Recovery）：

    • 允許死鎖發生，但通過週期性地檢測系統中的死鎖狀態，並採取恢復措施來解除死鎖。
    • 可以使用圖演演算法（如資源分配圖）來檢測死鎖，並通過回滾、搶佔資源等方式進行恢復。

  4. 死鎖忽略（Deadlock Ignorance）：

    • 假設死鎖很少發生或發生死鎖的代價較低，可以忽略死鎖問題，不採取專門的死鎖處理措施。
    • 此方法適用於某些特定環境下，如批次處理系統等。