資料庫效能優化

為什麼效能很重要？

想像你在一個巨大的圖書館找一本書：

• 沒有索引：從第一本書開始，一本一本翻，直到找到為止（全表掃描）
• 有索引：查目錄，直接走到正確的書架（索引查詢）

當資料量從 100 筆變成 100 萬筆，差別就是一秒和一小時的區別。

索引（Index）

叢集索引（Clustered Index）

每張表只能有一個叢集索引，決定資料的實際儲存順序。

-- 主鍵預設就是叢集索引
CREATE TABLE Students (
    Id INT PRIMARY KEY CLUSTERED,          -- 主鍵 = 叢集索引，資料按 Id 排序儲存
    Name NVARCHAR(100),                    -- 姓名
    Email NVARCHAR(200)                    -- 信箱
);

非叢集索引（Non-Clustered Index）

可以有多個，像書的目錄一樣指向實際資料。

-- 在 Email 欄位建立非叢集索引
CREATE NONCLUSTERED INDEX IX_Students_Email -- 建立非叢集索引
ON Students(Email);                        -- 在 Email 欄位上

-- 複合索引（多欄位）
CREATE INDEX IX_Students_Name_Score        -- 建立複合索引
ON Students(Name ASC, Score DESC);         -- 先按姓名升序，再按分數降序
-- 注意：複合索引的欄位順序很重要！
-- WHERE Name = 'xxx' → ✅ 會用到索引
-- WHERE Score > 90   → ❌ 不會用到索引（必須從最左邊的欄位開始匹配）

在 EF Core 中建立索引

// Fluent API 建立索引
modelBuilder.Entity<Student>(entity =>     // 設定 Student Entity
{
    entity.HasIndex(s => s.Email)           // 在 Email 上建索引
        .IsUnique();                       // 唯一索引

    entity.HasIndex(s => new { s.Name, s.Score }) // 複合索引
        .HasDatabaseName("IX_Students_Name_Score"); // 指定索引名稱
});

查詢執行計畫（Execution Plan）

-- 查看查詢的執行計畫
SET STATISTICS IO ON;                      -- 開啟 IO 統計
SET STATISTICS TIME ON;                    -- 開啟時間統計

-- 使用 EXPLAIN 查看執行計畫（MSSQL 用法）
SET SHOWPLAN_TEXT ON;                      -- 開啟文字格式的執行計畫
GO
SELECT * FROM Students WHERE Email = 'test@test.com'; -- 要分析的查詢
GO
SET SHOWPLAN_TEXT OFF;                     -- 關閉執行計畫顯示

-- 執行計畫結果範例
-- Index Seek（好！用到索引）→ 直接跳到對的位置
-- Table Scan（不好！全表掃描）→ 逐列搜尋，很慢
-- Index Scan（普通）→ 掃描整個索引，比 Table Scan 快一點

N+1 問題與解決方案

// ❌ N+1 問題：查 1 次取得清單 + N 次取得關聯資料
var students = await db.Students.ToListAsync(); // 查詢 1：取得所有學生
foreach (var student in students)               // 迴圈 N 次
{
    // 每次迴圈都產生一個 SQL 查詢
    var enrollments = await db.Enrollments      // 查詢 2~N+1
        .Where(e => e.StudentId == student.Id)  // 篩選該學生的選課
        .ToListAsync();                         // 執行查詢
}
// 100 個學生 = 101 次查詢 😱

// ✅ 解法 1：Eager Loading
var students = await db.Students               // 查詢學生
    .Include(s => s.Enrollments)               // 一次載入所有選課記錄
    .ToListAsync();                            // 只有 1 次查詢 ✅

// ✅ 解法 2：投影（最佳效能）
var result = await db.Students                 // 查詢學生
    .Select(s => new                           // 投影成需要的形狀
    {
        s.Name,                                // 只選需要的欄位
        EnrollmentCount = s.Enrollments.Count  // 在 SQL 端計算
    })
    .ToListAsync();                            // 一次查詢搞定 ✅

// ✅ 解法 3：分批載入
var studentIds = students.Select(s => s.Id).ToList(); // 先取得所有學生 ID
var allEnrollments = await db.Enrollments              // 一次查詢所有相關選課
    .Where(e => studentIds.Contains(e.StudentId))      // 用 IN 查詢
    .ToListAsync();                                    // 只有 2 次查詢 ✅

批次操作

// ❌ 逐筆更新（慢）
foreach (var student in students)              // 遍歷每個學生
{
    student.Score += 5;                        // 加分
    await db.SaveChangesAsync();               // 每次都送一個 UPDATE 語句
}

// ✅ 批次更新（EF Core 7+ 支援 ExecuteUpdate）
await db.Students                              // 查詢學生
    .Where(s => s.Score < 60)                  // 篩選不及格的
    .ExecuteUpdateAsync(s =>                   // 批次更新
        s.SetProperty(x => x.Score, x => x.Score + 5) // 加 5 分
    );                                         // 只產生一個 UPDATE 語句 ✅

// ✅ 批次刪除（EF Core 7+）
await db.Students                              // 查詢學生
    .Where(s => s.IsDeleted)                   // 篩選已刪除的
    .ExecuteDeleteAsync();                     // 一個 DELETE 語句搞定 ✅

Compiled Queries（編譯查詢）

// 預先編譯常用查詢，避免每次都重新產生 SQL
private static readonly Func<AppDbContext, int, Task<Student?>> // 宣告編譯查詢
    GetStudentById = EF.CompileAsyncQuery(     // 編譯查詢
        (AppDbContext db, int id) =>           // 參數：DbContext 和 ID
            db.Students.FirstOrDefault(s => s.Id == id) // 查詢邏輯
    );

// 使用編譯查詢
var student = await GetStudentById(db, 1);     // 直接呼叫，不需要重新產生 SQL

快取策略

// MemoryCache — 記憶體快取（單機）
using Microsoft.Extensions.Caching.Memory;     // 引用快取命名空間

public class StudentService                    // 學生服務
{
    private readonly IMemoryCache _cache;       // 注入快取服務
    private readonly AppDbContext _db;          // 注入 DbContext

    public async Task<Student?> GetById(int id) // 查詢學生（帶快取）
    {
        var cacheKey = $"student_{id}";       // 快取鍵
        if (_cache.TryGetValue(cacheKey, out Student? student)) // 嘗試從快取取得
        {
            return student;                    // 快取命中，直接回傳
        }

        student = await _db.Students           // 快取未命中，查詢資料庫
            .FirstOrDefaultAsync(s => s.Id == id); // 依 ID 查詢

        if (student is not null)               // 如果找到學生
        {
            _cache.Set(cacheKey, student,       // 存入快取
                TimeSpan.FromMinutes(5));       // 5 分鐘後過期
        }
        return student;                        // 回傳結果
    }
}

💡 Redis 是分散式快取方案，適合多台伺服器共用快取的情境。概念類似 MemoryCache，但資料存在獨立的 Redis 伺服器上。

🤔 我這樣寫為什麼會錯？

❌ 錯誤 1：SELECT *（選取所有欄位）

// ❌ 只需要姓名，卻把所有欄位都選出來
var students = await db.Students.ToListAsync(); // SELECT * FROM Students
var names = students.Select(s => s.Name);       // 在 C# 端才篩選欄位
// 如果 Student 有大型的 ProfileImage 欄位 → 浪費大量頻寬和記憶體

// ✅ 只選取需要的欄位
var names = await db.Students                  // 查詢學生
    .Select(s => s.Name)                       // 只選 Name 欄位
    .ToListAsync();                            // SQL: SELECT Name FROM Students ✅

❌ 錯誤 2：缺少索引

-- ❌ 每次查詢都要全表掃描
SELECT * FROM Orders                           -- 查詢訂單
WHERE CustomerId = 12345                       -- 按客戶 ID 篩選
AND Status = 'Pending';                        -- 按狀態篩選
-- 如果沒有索引，100 萬筆資料每次都要掃描全部 😱

-- ✅ 建立適當的索引
CREATE INDEX IX_Orders_CustomerId_Status       -- 建立複合索引
ON Orders(CustomerId, Status);                 -- 按查詢條件建索引
-- 現在查詢只需要毫秒等級 ✅

❌ 錯誤 3：過度索引

-- ❌ 在每個欄位上都建索引
CREATE INDEX IX_1 ON Students(Name);           -- 索引 1
CREATE INDEX IX_2 ON Students(Email);          -- 索引 2
CREATE INDEX IX_3 ON Students(Score);          -- 索引 3
CREATE INDEX IX_4 ON Students(Name, Email);    -- 索引 4
CREATE INDEX IX_5 ON Students(Name, Score);    -- 索引 5
CREATE INDEX IX_6 ON Students(Email, Score);   -- 索引 6
-- 每次 INSERT/UPDATE/DELETE 都要更新所有索引 → 寫入效能暴降！

-- ✅ 只在常用查詢條件上建索引
-- 分析查詢模式後，只建必要的索引
CREATE INDEX IX_Students_Email ON Students(Email); -- 經常用 Email 查詢
CREATE INDEX IX_Students_Name_Score                -- 經常用姓名+分數排序
ON Students(Name, Score DESC);

💡 重點整理