2025年9月29日 星期一

資料庫設計與優化題庫（適用 MySQL / Laravel）

 

資料庫設計與優化題庫（適用 MySQL / Laravel）

在現代 Web 應用開發中，資料庫設計與優化是資深後端工程師的核心技能，尤其在使用 MySQL 與 Laravel 框架時。本文將提供的題庫擴展為一篇技術文章，分為四大類別。每個問題不僅列出，還附上深入解析、程式碼範例、實務應用建議與潛在陷阱，幫助面試官評估候選人深度，也讓求職者系統化準備。這些題目聚焦於 MySQL 的資料庫原則與 Laravel 的 ORM 整合，涵蓋從基礎設計到高併發優化的實戰議題。

一、資料庫設計與正規化

資料庫設計強調資料完整性與效率，正規化是核心原則，但需權衡效能。這類題目檢視候選人對資料結構的理解。

1. 請說明資料庫正規化的三個主要階段（1NF、2NF、3NF），並舉例說明其目的。

解析：

  • 1NF (First Normal Form)：確保每個欄位原子性（不可再分），消除重複群組。目的：避免資料重複與更新異常。
    • 範例：將「興趣」欄位從逗號分隔轉為單一值，或使用關聯表。
  • 2NF (Second Normal Form)：基於1NF，消除部分依賴（非主鍵欄位僅依賴完整主鍵）。目的：減少冗餘資料。
    • 範例：在訂單表中，若主鍵為 (訂單ID, 產品ID)，則產品價格應移至產品表，避免依賴部分主鍵。
  • 3NF (Third Normal Form)：基於2NF，消除傳遞依賴（非主鍵欄位不依賴其他非主鍵）。目的：防止資料不一致。
    • 範例：在員工表中，若部門地址依賴部門名稱，則移至部門表。

實務應用：正規化減少儲存空間與維護成本，但過度可能導致查詢複雜。在 Laravel 中，使用 migration 定義正規化結構。

潛在陷阱：忽略 BCNF 或更高形式，可能在複雜關聯中產生異常。

2. 在什麼情況下你會選擇「反正規化」？請舉一個實務案例。

解析：

  • 反正規化：有意引入冗餘以提升讀取效能，犧牲寫入一致性。適用於讀多寫少、查詢頻繁的場景，如報告系統或大數據分析。
  • 實務案例：在電商平台，產品表冗餘儲存「類別名稱」（本應在類別表），避免頻繁 JOIN，提升首頁產品列表速度。使用觸發器或 Laravel event 同步更新。

優點與風險：加速查詢，但增加儲存與更新複雜度。監控以避免資料不一致。

3. 如何設計一個支援多語系的產品資料表？

解析：

  • 設計：使用翻譯表 (translations) 與多對一關聯。產品表儲存通用欄位，翻譯表儲存語言特定欄位（如名稱、描述），以 locale 作為複合鍵。
  • 考慮：支援 fallback 語言、索引 locale + product_id。

範例結構 (MySQL)：

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sku VARCHAR(50) UNIQUE
);

CREATE TABLE product_translations (
    product_id INT,
    locale VARCHAR(5),
    name VARCHAR(255),
    description TEXT,
    PRIMARY KEY (product_id, locale),
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

Laravel 實作：使用 Translatable trait 或自訂 scope 查詢當前語言。

4. 請設計一個訂單系統的資料表結構，包含訂單、訂單項目、付款紀錄，並說明關聯方式。

解析：

  • 結構：orders (訂單主表)、order_items (訂單項目，一對多)、payments (付款紀錄，一對多)。
  • 關聯：orders.id 作為 order_items.order_id 與 payments.order_id 的外鍵。使用 ON DELETE CASCADE 確保一致性。

範例結構 (MySQL)：

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    total DECIMAL(10,2),
    status ENUM('pending', 'paid', 'shipped')
);

CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    product_id INT,
    quantity INT,
    price DECIMAL(10,2),
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

CREATE TABLE payments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    amount DECIMAL(10,2),
    method VARCHAR(50),
    status ENUM('success', 'failed'),
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

實務考量：添加 timestamps、軟刪除。Laravel 中使用 hasMany 關聯。

5. 請說明 Laravel 的 migration 如何支援資料表版本控制與 rollback。

解析：

  • Migration：使用 PHP 類別定義 schema 變更，支持 up() 建立、down() 還原。版本控制透過 migrations 表追蹤執行記錄。
  • Rollback：php artisan migrate:rollback 執行 down() 方法，還原最近批次。

範例：

class CreateUsersTable extends Migration {
    public function up() {
        Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->timestamps();
        });
    }
    public function down() {
        Schema::dropIfExists('users');
    }
}

實務：使用 seeders 填充資料，版本控制整合 Git。

二、查詢效能與索引策略

效能優化聚焦索引與查詢分析，這類題目評估候選人對 MySQL 內部機制的理解。

1. 請說明索引的種類（BTREE、HASH、FULLTEXT）與適用場景。

解析：

  • BTREE：平衡樹，支援範圍查詢、排序。適用：主鍵、日期欄位 (e.g., WHERE date > '2023-01-01')。
  • HASH：雜湊表，快速等值查詢。不支援範圍。適用：URL 或 ID 查找 (InnoDB 不支援獨立 HASH)。
  • FULLTEXT：全文檢索，支援自然語言搜尋。適用：文章搜尋 (e.g., MATCH AGAINST)。

實務：MySQL 預設 BTREE；FULLTEXT 需要特定引擎如 InnoDB (MySQL 5.6+)。

2. 如何分析慢查詢？請說明 EXPLAIN 的使用方式與常見指標。

解析：

  • 分析：啟用 slow_query_log，設定 long_query_time。使用 EXPLAIN 檢視執行計劃。
  • EXPLAIN：EXPLAIN SELECT ... 返回 type (ALL/index/range)、rows (掃描行數)、key (使用索引)、Extra (Using filesort 等)。
  • 常見指標：type=ALL 表示全表掃描；rows 高表示低效；Extra=Using temporary 表示需優化。

範例：

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

實務：整合 pt-query-digest 工具分析 log。

3. 在 Laravel 中如何避免 N+1 問題？請舉例說明。

解析：

  • N+1：循環查詢關聯資料導致額外查詢。解決：使用 eager loading (with()) 預載關聯。

範例：

// N+1 問題
$users = User::all();
foreach ($users as $user) {
    echo $user->posts->count();  // 每次 loop 查詢 posts
}

// 解決
$users = User::with('posts')->get();
foreach ($users as $user) {
    echo $user->posts->count();  // 單一查詢預載
}

4. 請說明覆蓋索引（Covering Index）是什麼？如何提升效能？

解析：

  • 覆蓋索引：索引包含所有 SELECT 欄位，無需回表讀取資料。提升：減少 I/O。
  • 實作：複合索引涵蓋 WHERE 與 SELECT 欄位。

範例：

CREATE INDEX idx_name_email ON users (name, email);
EXPLAIN SELECT name, email FROM users WHERE name = 'John';  // Extra: Using index

5. 請說明資料庫分區（Partitioning）與分表（Sharding）的差異與應用場景。

解析：

  • Partitioning：單一資料庫內部分割表 (e.g., 依日期 RANGE)。適用：歷史資料歸檔，MySQL 原生支援。
  • Sharding：跨多資料庫分割資料 (e.g., 依用戶 ID 模數)。適用：水平擴展，高流量系統。

差異：Partitioning 易管理但單點；Sharding 複雜但可分散負載。Laravel 使用 Vitess 或自訂 middleware 實作 Sharding。

三、交易與一致性

確保資料完整性在高併發環境至關重要。

1. 請說明 ACID 是什麼？每個特性在實務中如何體現？

解析：

  • Atomicity：交易全成或全敗 (e.g., 轉帳扣款+加款)。
  • Consistency：交易前後資料符合規則 (e.g., 餘額不負)。
  • Isolation：並行交易不干擾 (e.g., 鎖定避免髒讀)。
  • Durability：提交後持久化 (e.g., WAL 日誌)。

實務：InnoDB 引擎支援 ACID。

2. 如何在 Laravel 中使用 DB Transaction？請舉例說明 rollback 的情境。

解析：

  • 使用 DB::transaction() 包裝操作，異常時自動 rollback。

範例：

DB::transaction(function () {
    $user->update(['balance' => DB::raw('balance - 100')]);
    $recipient->update(['balance' => DB::raw('balance + 100')]);
    // 若異常，rollback
});

情境：轉帳失敗 rollback 避免不一致。

3. 請說明資料庫鎖定（Locking）機制：Row Lock vs Table Lock。

解析：

  • Row Lock：鎖定單行 (InnoDB)，高併發友好。
  • Table Lock：鎖定整表 (MyISAM)，簡單但低併發。

實務：SELECT FOR UPDATE 使用 row lock。

4. 如何處理高併發下的資源競爭？請舉例說明 optimistic lock 的實作方式。

解析：

  • 樂觀鎖：假設無衝突，更新時檢查版本。

範例：

-- 加 version 欄位
UPDATE products SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5;  // 若版本變，更新失敗

Laravel：使用 optimistic locking trait 或自訂 check。

5. 請說明 Laravel 的 queue 任務中如何確保資料一致性與重試機制。

解析：

  • 一致性：任務中使用 transaction；失敗 rollback。
  • 重試：設定 --tries，failed_jobs 表記錄，重試或通知。

範例：

class ProcessOrder implements ShouldQueue {
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;
    public $tries = 3;  // 重試 3 次
}

四、資料庫與 Laravel 整合

聚焦框架整合，提升開發效率。

1. Laravel 的 Eloquent ORM 與 Query Builder 有何差異？何時選用哪一種？

解析：

  • Eloquent：物件導向，模型代表表，支援關聯。適合複雜邏輯。
  • Query Builder：流暢 API，直接建構 SQL。適合簡單或自訂查詢。

選用：Eloquent 日常；Builder 性能關鍵或原始 SQL。

2. 如何在 Laravel 中設計多資料庫連線？請舉例說明。

解析：

範例：

'connections' => [
    'mysql' => [...],
    'mysql_read' => [...]  // 讀寫分離
];
User::on('mysql_read')->get();

3. 請說明 Laravel 的 model observer 與 event 在資料變動時的應用。

解析：

  • Observer：監聽模型事件 (saving, deleted)，集中邏輯。
  • Event：廣播事件，解耦 (e.g., UserCreated)。

範例 Observer：

class UserObserver {
    public function creating(User $user) {
        $user->uuid = Str::uuid();
    }
}

4. 如何在 Laravel 中實作資料快取（Cache）以減少 DB 查詢？

解析：

  • 使用 Cache::remember() 快取查詢結果，支持 tags 失效。

範例：

$users = Cache::remember('users', 3600, function () {
    return User::all();
});

5. 請說明 Laravel 的 pagination 如何運作，並如何優化大量資料的分頁效能。

解析：

  • 運作：paginate() 自動計算 offset/limit，生成 Paginator 物件。
  • 優化：使用 cursor pagination (游標式) 避免 offset 效能衰退；索引排序欄位。

範例：

$users = User::paginate(15);  // 標準
$users = User::cursorPaginate(15);  // 優化大資料

總結

這份題庫與解析涵蓋資料庫設計、優化、一致性與 Laravel 整合的核心知識，強調深度理解與實務應用。面試時，可追問範例細節評估經驗；求職者則可透過練習提升技能。在高流量系統中，平衡正規化與效能是關鍵。

於

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)

熱門文章

  • 從零開始：使用 FastAPI、MySQL 和 Docker 打造你的第一個電商購物車應用
    從零開始：使用 FastAPI、MySQL 和 Docker 打造你的第一個電商購物車應用 引言： 哈囉，各位程式碼愛好者！你是否曾經想過要打造一個自己的網路應用程式，卻不知道從何開始？特別是對 Python 有興趣的初學者，常常會被複雜的 Web 框架和部署流程嚇到。別擔心！今...
  • 資深前端工程師面試指南：Nuxt 3 生命週期與 SSR、CSR、SSG 渲染策略
      資深前端工程師面試指南：Nuxt 3 生命週期與 SSR、CSR、SSG 渲染策略 在資深前端工程師的面試中， Nuxt 3 是一個熱門話題，尤其是其生命週期以及伺服器端渲染 (SSR)、客戶端渲染 (CSR) 和靜態生成 (SSG) 的應用場景。這些議題不僅考驗對框架的...
  • 資料庫正規化（Normalization）與反正規化（Denormalization）的優缺點。
      在資料庫設計中， 正規化 (Normalization) 和 反正規化 (Denormalization) 是兩種截然不同的策略，各有其優缺點，通常需要在實際應用場景中權衡選擇。 一、正規化 (Normalization) 定義 正規化是一套系統性的方法，用於設計關聯式資...
  • 🚀 AI 革命：技術人員必須掌握的三大核心機會點
    🚀 AI 革命：技術人員必須掌握的三大核心機會點 💡 前言：從「數位化」到「認知化」的範式轉移 網際網路將世界資訊 數位化 ，讓存取和傳輸成為可能。而我們正處於人工智慧（AI）革命的開端，這場革命將實現 認知和決策的自動化 。對於技術開發者、工程師和架構師而言，這不僅僅是工具...
  • 《面試官別再問》PHP運用多執行緒(Multi-thread)實現非阻塞方法
    PHP 多執行緒與非阻塞實戰：台灣工程師的務實選擇 前言 身為 PHP 工程師，我們常會遇到需要處理耗時操作的場景，例如發送大量郵件、處理圖片或進行複雜運算。如果這些操作都循序執行，很容易造成程式卡住，使用者體驗當然不好。這時候，「非阻塞」與「多執行緒」的概念就顯得非常重要了。 ...
  • 我的第一個 FastAPI 微服務：當 Laravel 遇上智能文件辨識
    我的第一個 FastAPI 微服務：當 Laravel 遇上智能文件辨識 作為一名主要使用 PHP (尤其是 Laravel) 進行開發的工程師，我始終對探索新的技術棧充滿熱情。最近，我將目光投向了 Python 生態系統，特別是 FastAPI ，並成功地將其與我熟悉的 Lar...
  • Vue 3 + WebSocket 即時聊天室：打造生產級應用的架構與實戰
    Vue 3 WebSocket 聊天室架構設計與實戰教學 實現高彈性、高安全的即時通訊服務 By [您的名字或暱稱] | 資深全端工程師與系統架構師 當我們從傳統的 HTTP 架構轉向 即時（Real-Time） 應用時，WebSocket 幾乎是唯一的選擇。然而，一個生產環境...
  • Django CRM 從零開始：為初學者打造的逐步教學
    Django CRM 從零開始：為初學者打造的逐步教學 (重新排版) 嗨，Django 新手們！準備好踏上開發一個實用且功能強大的 CRM (客戶關係管理) 系統的旅程了嗎？Django 是一個高效的 Web 框架，它能讓您快速地建立複雜的資料庫驅動型應用程式。本教學將帶領您從零...
  • 《面試官別再問》資料庫正規化(Normalization)
    正規化說明 正規化是在資料庫中組織資料的程序。 其中包括建立資料表，以及在這些資料表之間根據規則建立關聯性，這些規則的設計目的是：透過刪除重複性和不一致的相依性，保護資料並讓資料庫更有彈性。 重複的資料會浪費磁碟空間，並產生維護方面的問題。 如果必須變更現有資料，並且該...
  • 駕馭單線程的藝術：JavaScript 與 PHP 的高併發與並行架構解密（資深工程師視角）
    駕馭單線程的藝術：JavaScript 與 PHP 的高併發與並行架構解密 引言：跳脫「單線程」的迷思與工程師的取捨 許多初階工程師一聽到 JavaScript (Node.js) 或 PHP 是單線程，就武斷地認為它們無法處理高併發或並行計算。這錯了。 單線程只是語言執行模...