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

資料庫基礎面試第二彈

2023-09-06 06:01:15

1. 樂觀鎖和悲觀鎖的理解及使用

樂觀鎖和悲觀鎖是在並行程式設計中使用的兩種並行控制機制，用於解決多執行緒或多程序環境下的資料一致性問題。

1. 悲觀鎖（Pessimistic Locking）：
　　悲觀鎖的思想是假設並行存取會導致衝突，因此在存取共用資源之前，悲觀鎖會將資源鎖定，確保其他執行緒無法修改資源。悲觀鎖的典型應用是資料庫中的行級鎖，使用SELECT...FOR

UPDATE語句鎖定查詢結果。

使用悲觀鎖的過程如下：

  • 當一個執行緒要存取共用資源時，它會先嚐試獲取鎖。
  • 如果鎖已經被其他執行緒獲取，則當前執行緒會被阻塞，直到鎖被釋放。
  • 當執行緒獲得了鎖之後，它可以安全地存取共用資源，其他執行緒無法修改該資源。
  • 當執行緒完成操作後，釋放鎖，其他執行緒可以獲取到鎖並存取資源。

悲觀鎖的優點是保證了資料的一致性，但是它的缺點是在高並行環境下，鎖的競爭會導致效能下降。

2. 樂觀鎖（Optimistic Locking）：
樂觀鎖的思想是假設並行存取不會導致衝突，因此線上程存取共用資源之前，不會加鎖。相反，樂觀鎖會在更新資源時，檢查在此期間是否有其他執行緒修改了資源。

使用樂觀鎖的過程如下：

  • 當一個執行緒要更新共用資源時，它首先會讀取資源的版本號或標識。
  • 在進行更新之前，執行緒會檢查資源的版本號是否發生了變化。
  • 如果資源的版本號沒有變化，執行緒會更新資源，並更新版本號。
  • 如果資源的版本號發生了變化，表示有其他執行緒已經修改過資源，當前執行緒的操作可能會產生衝突。
  • 在發生衝突時，可以選擇進行回滾操作或者重試整個過程。

　　樂觀鎖的優點是在無衝突的情況下，不需要進行加鎖操作，從而提高了並行效能。然而，如果衝突頻繁發生，會導致大量的回滾和重試操作，降低效能。

總的來說，悲觀鎖適合對於衝突頻繁發生的場景，可以保證資料的一致性；而樂觀鎖適合對於衝突較少發生的場景，可以提高並行效能。選擇使用哪種鎖要根據具體的應用場景和效能需求進行

權衡。

樂觀鎖與悲觀鎖例子：

SELECT stock FROM inventory WHERE product_id = <product_id> FOR UPDATE;
```

在上述程式碼中，使用 `FOR UPDATE` 子句來鎖定庫存行，確保其他並行操作無法修改庫存數量。

START TRANSACTION;  -- 開啟事務
SELECT stock FROM inventory WHERE product_id = <product_id> FOR UPDATE;  -- 獲取並鎖定庫存行
-- 檢查庫存是否足夠
IF stock >= <quantity> THEN
    UPDATE inventory SET stock = stock - <quantity> WHERE product_id = <product_id>;  -- 更新庫存
    COMMIT;  -- 提交事務
    -- 庫存更新成功，繼續後續操作
ELSE
    ROLLBACK;  -- 回滾事務
    -- 庫存不足，處理相應邏輯，如提示使用者庫存不足
END IF;
```

2. 聚集索引和非聚集索引的區別：

聚集索引（Clustered Index）和非聚集索引（Non-clustered Index）是資料庫中常用的索引型別，它們在索引的組織方式和資料存取方式上存在一些區別。

聚集索引：

  • 聚集索引定義了資料表的物理排序方式。每個表只能有一個聚集索引，它決定了表中資料行的物理儲存順序。如果一個表有聚集索引，那麼資料行將按照聚集索引的排序順序儲存在磁碟上。
  • 聚集索引的葉子節點包含了整個資料行的資訊，因此當使用聚集索引進行資料查詢時，可以直接通過索引找到所需的資料行。
  • 由於每個表只能有一個聚集索引，一般情況下，聚集索引會選擇主鍵作為索引鍵。

非聚集索引：

  • 非聚集索引是基於表中的列建立的索引，它儲存了索引鍵和指向對應資料行的指標。一個表可以有多個非聚集索引。
  • 非聚集索引的葉子節點不包含完整的資料行，而是包含了索引鍵和指向對應資料行的指標。當使用非聚集索引進行資料查詢時，首先通過索引找到對應的資料行的指標，然後再通過指標獲取完整的資料行。
  • 非聚集索引可以加快資料的查詢速度，尤其是在涉及到過濾和排序的查詢操作中。

區別總結：

  • 聚集索引決定了資料行的物理儲存順序，而非聚集索引只是提供了資料行的邏輯順序。
  • 聚集索引的葉子節點包含完整的資料行，而非聚集索引的葉子節點只包含索引鍵和指向資料行的指標。
  • 一個表只能有一個聚集索引，但可以有多個非聚集索引。

在實際使用中，根據具體的查詢需求和資料特點，可以根據需要選擇適當的索引型別，以提高資料庫的查詢效能和資料存取效率。

建立聚集索引和非聚集索引的範例：

CREATE CLUSTERED INDEX idx_Orders_OrderID ON Orders (OrderID);

上述語句建立了一個名為 "idx_Orders_OrderID" 的聚集索引，它基於 "Orders" 表的 "OrderID" 列。


CREATE NONCLUSTERED INDEX idx_Customers_Email ON Customers (Email);

上述語句建立了一個名為 "idx_Customers_Email" 的非聚集索引，它基於 "Customers" 表的 "Email" 列。

3.  為什麼索參照B+樹，而不用普通的二元樹

  1. 磁碟存取效率： B+樹是一種多叉樹，它具有分支因子（即子節點的最大數量）更高的特點。相比之下，二元樹每個節點只有兩個子節點。在磁碟上，每次讀取或寫入的開銷是非常昂貴的操作，因此減少磁碟存取次數可以提高索引的效能。B+樹的高分支因子意味著在相同高度的情況下，它可以儲存更多的鍵值對，減少了磁碟I/O次數。而二元樹的分支因子較低，可能需要更多的磁碟存取來定位目標資料。

  2. 順序存取效能： B+樹的葉子節點使用連結串列連線起來，形成一個有序的連結串列結構。這使得範圍查詢和順序存取非常高效。例如，在資料庫中，如果需要查詢某個範圍內的資料（如按時間排序的記錄），B+樹可以利用有序的葉子節點連結串列進行快速定位和遍歷。而在二元樹中，由於沒有有序連結串列結構，需要進行中序遍歷才能獲取有序資料，這會增加額外的開銷。

  3. 索引的穩定性： B+樹作為一種自平衡樹結構，對於插入和刪除操作具有較好的穩定性。當在B+樹中進行插入或刪除操作時，只需要對樹的部分節點進行修改，而不需要像二元樹那樣進行全域性的重新平衡。這種特性使得B+樹更適合於高效地維護索引結構。

  4. 快取利用： 現代計算機系統通常都有層次化的快取結構，其中記憶體快取（如CPU快取）的存取速度遠高於磁碟。B+樹由於具有高的分支因子，可以儲存更多的鍵值對在每個節點中，因此在搜尋過程中可以利用更好地利用快取，減少記憶體存取的次數。而二元樹由於分支因子較低，可能需要更多的記憶體存取來獲取相同數量的鍵值對。

綜上所述，B+樹相對於普通的二元樹具有更好的磁碟存取效率、順序存取效能、穩定性和快取利用，使其成為了廣泛應用於索引結構的一種理想選擇。

4. Hash索引和B+樹索引區別：

Hash索引和B+樹索引是兩種常見的索引結構，它們在實現原理、適用場景和效能方面存在一些區別。

實現原理：

  • Hash索引： Hash索引使用雜湊函數將索引鍵轉換為儲存位置的雜湊碼。雜湊碼經過對映後直接指向儲存資料的位置，因此在存取資料時具有固定的時間複雜度（O(1)）。
  • B+樹索引： B+樹索引是一種多叉樹結構，具有根節點、內部節點和葉子節點。每個節點包含多個鍵值對，通過比較鍵值來導航到下一個節點。B+樹索引通過在樹中進行有序查詢來定位資料，因此存取時間的複雜度與樹的高度相關（通常為O(log n)）。

適用場景：

  • Hash索引： Hash索引適用於等值查詢，即根據精確的索引鍵值查詢資料。它對於相等比較非常快速，但不適用於範圍查詢或排序操作。
  • B+樹索引： B+樹索引適用於範圍查詢、排序操作和模糊查詢。由於B+樹的有序性，它可以支援按順序遍歷資料和快速定位範圍內的資料。

資料存取效能：

  • Hash索引： Hash索引具有固定的存取時間複雜度（O(1)），因此在等值查詢方面非常高效。但是，由於雜湊函數的雜湊衝突可能導致連結串列的形成，這可能會影響效能，尤其是在高負載情況下。
  • B+樹索引： B+樹索引的存取時間複雜度與樹的高度相關，通常為O(log n)。儘管每次存取可能需要多次磁碟I/O，但B+樹索引在範圍查詢和順序存取方面具有良好的效能。

儲存空間利用：

  • Hash索引： Hash索引通常需要預先分配足夠的雜湊槽位，以防止雜湊衝突。這可能會導致儲存空間的浪費，尤其是在資料分佈不均勻的情況下。
  • B+樹索引： B+樹索引在內部節點上儲存鍵值對和子節點指標，而葉子節點上儲存鍵值對和指向資料的指標。相比之下，B+樹索引通常可以更好地利用儲存空間。

綜上所述，Hash索引適用於等值查詢，具有固定的存取時間，但不支援範圍查詢和排序操作。B+樹索引適用於範圍查詢、排序操作和模糊查詢，具有較好的順序存取效能和穩定性，但存取時間複雜度與樹的高度相關。選擇使用哪種索引結構取決於具體的應用需求和資料存取模式。

5. 索引不適合哪些場景以及有哪些優缺點：

索引在大多數情況下可以顯著提高資料庫查詢的效能，但並不適合所有場景。以下是索引不適合的一些場景以及索引的一些優點和缺點：

索引不適合的場景：

  1. 低基數列： 當列中的唯一值較少時，例如性別列只有兩個可能的取值（男或女），建立索引可能不會帶來明顯的效能提升。在這種情況下，掃描整個表的代價可能比使用索引更低。
  2. 頻繁更新的列： 如果一個列經常被更新，索引的維護成本會很高。每次更新列的值時，可能需要更新索引資料結構，這會增加寫操作的開銷。頻繁更新的列可能會導致索引失去效益，甚至降低效能。
  3. 小表： 對於非常小的表，建立索引可能沒有太大意義。在小表中，全表掃描的代價可能相對較低，而使用索引進行查詢可能會增加額外的開銷。

索引的優點：

  1. 加速查詢： 索引可以大大加速查詢操作，特別是在大型表中。通過使用索引，資料庫引擎可以快速定位滿足查詢條件的資料行，減少了全表掃描的需要。
  2. 支援排序和聚合操作： 索引可以使排序和聚合操作更高效。通過使用有序索引，資料庫引擎可以避免對整個表進行排序，從而提高操作的效能。
  3. 優化連線操作： 對於連線操作（如JOIN），索引可以幫助加速資料的查詢和匹配，減少連線操作的成本。

索引的缺點：

  1. 佔用儲存空間： 索引需要佔用額外的儲存空間。對於大型表和多個索引的情況，索引可能佔據相當大的儲存空間。
  2. 增加寫操作的開銷： 當插入、更新或刪除資料時，索引需要進行相應的維護操作，這會增加寫操作的開銷。如果頻繁進行寫操作，索引的維護成本可能會成為效能瓶頸。
  3. 索引選擇和調優困難： 在設計索引時，需要根據實際查詢模式和資料存取模式進行權衡和選擇。選擇不當的索引或過多的索引可能會導致效能下降，而調優索引可能需要經驗和測試。

綜上所述，索引在大多數情況下是資料庫效能優化的重要工具，但在某些場景下可能不適用或需要謹慎使用。正確的索引設計和調優可以提高查詢效能，而錯誤的使用可能會導致額外的開銷和效能下降。

6.最左字首匹配原則是什麼

最左字首匹配原則是資料庫索引設計中的一個重要原則，它指出在使用複合索引（Composite Index）時，索引將按照索引鍵的順序進行匹配和檢索，並且只能利用索引的最左字首來進行匹配。

具體來說，如果一個複合索引包含多個列（例如(A, B, C)），那麼在查詢時，最左字首匹配原則要求查詢條件必須從索引的最左側開始，並且連續地匹配到索引的某個位置為止。也就是說，查詢條件可以是(A)、(A, B)或者(A, B, C)，但不能是(B)、(C)或者(B, C)。

遵循最左字首匹配原則的好處是可以最大程度地利用索引的有序性，提高查詢的效率。由於索引按照鍵的順序儲存，因此在查詢時只需定位到滿足最左字首條件的索引位置，而不需要掃描整個索引。

舉個例子，假設有一個複合索引 (A, B, C)，如果查詢條件是(A = 1)，那麼索引可以用於加速查詢，因為最左字首 (A) 匹配成功。但如果查詢條件是(B = 2)，即使索引中包含了列 B，也無法利用索引進行加速，因為最左字首不匹配。

需要注意的是，最左字首匹配原則並不意味著只有最左側的列可以使用索引，而是強調索引的有序性和連續性。在某些情況下，可以通過建立單列索引或調整索引順序來更好地利用索引。