SQL Server 2008 二進位圖片儲存與存取最佳實踐

SQL Server 二進位大物件 (BLOB) 儲存完整指南

在資料庫中儲存圖片、文件等二進位資料是常見需求。SQL Server 2008 引入 FILESTREAM 與 `VARBINARY(MAX)`，提供比舊版 `IMAGE` 型別更強大、效能更好的解決方案。

儲存方案選擇對照

方案一：VARBINARY(MAX) 純資料庫儲存

建表範例

CREATE TABLE product_images
(
    image_id INT IDENTITY PRIMARY KEY,
    product_id INT NOT NULL REFERENCES products(id),
    file_name NVARCHAR(255) NOT NULL,
    content_type NVARCHAR(50) NOT NULL,  -- image/jpeg, image/png
    file_size BIGINT NOT NULL,
    image_data VARBINARY(MAX) NOT NULL,
    created_at DATETIME DEFAULT GETDATE()
)

-- 索引優化查詢
CREATE INDEX IX_product_images_product ON product_images(product_id)

插入圖片 (T-SQL / 應用層)

-- 1. 使用 OPENROWSET (僅限伺服器端檔案)
INSERT INTO product_images (product_id, file_name, content_type, file_size, image_data)
SELECT 1, 'photo.jpg', 'image/jpeg', 
       (SELECT LEN(BulkColumn) FROM OPENROWSET(BULK 'C:\Images\photo.jpg', SINGLE_BLOB) AS x),
       BulkColumn
FROM OPENROWSET(BULK 'C:\Images\photo.jpg', SINGLE_BLOB) AS img

-- 2. 應用層參數化插入 (C# 範例)
/*
using (var cmd = new SqlCommand(
    "INSERT INTO product_images (product_id, file_name, content_type, file_size, image_data) VALUES (@pid, @fn, @ct, @fs, @data)", conn))
{
    cmd.Parameters.Add("@pid", SqlDbType.Int).Value = 1;
    cmd.Parameters.Add("@fn", SqlDbType.NVarChar).Value = "photo.jpg";
    cmd.Parameters.Add("@ct", SqlDbType.NVarChar).Value = "image/jpeg";
    cmd.Parameters.Add("@fs", SqlDbType.BigInt).Value = fileBytes.Length;
    cmd.Parameters.Add("@data", SqlDbType.VarBinary).Value = fileBytes;
    cmd.ExecuteNonQuery();
}
*/

讀取圖片

-- T-SQL 讀取 (應用層再輸出至 Response/BinaryWrite)
SELECT file_name, content_type, image_data 
FROM product_images WHERE image_id = 1

-- 大檔案分塊讀取 (避免記憶體壓力)
SELECT SUBSTRING(image_data, @offset, @chunk_size) 
FROM product_images WHERE image_id = 1

方案二：FILESTREAM 實作步驟

1. 啟用 FILESTREAM (執行個體層級)

SQL Server Configuration Manager → 服務屬性 → FILESTREAM 頁籤 → 勾選所有選項

2. 建立 FILESTREAM 檔案群組

ALTER DATABASE [MyDB] 
ADD FILEGROUP fg_filestream CONTAINS FILESTREAM

ALTER DATABASE [MyDB] 
ADD FILE (NAME = fs_data, FILENAME = 'D:\FSData') 
TO FILEGROUP fg_filestream

3. 建立含 FILESTREAM 欄位的表

CREATE TABLE document_store
(
    doc_id UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWID(),
    file_name NVARCHAR(255),
    file_extension NVARCHAR(10),
    doc_content VARBINARY(MAX) FILESTREAM NULL,
    created_at DATETIME DEFAULT GETDATE()
)
FILESTREAM_ON fg_filestream

4. 存取方式

• T-SQL：同 VARBINARY(MAX) 插入/查詢
• Win32 API：`OpenSqlFilestream` 取得控制代碼，用 `ReadFile`/`WriteFile` 串流存取 (效能最佳)
• 管理：檔案實體在 `D:\FSData\\` 目錄，可直接備份檔案系統

效能優化關鍵點

1. 大檔案分塊讀寫：避免單次佔用大量記憶體
2. 啟用壓縮：`ROW_COMPRESSION` 或 `PAGE_COMPRESSION` 減少儲存空間
3. 分離檔案群組：BLOB 放獨立檔案群組，配置不同磁碟 (SSD/HDD 分級)
4. 非同步處理：上傳/下載用背景工作，避免阻塞資料庫連線
5. 快取策略：熱門圖片用 Redis/CDN 快取，減少資料庫 IO

安全性與維護建議

• 資料庫角色最小權限：應用帳號僅需 `INSERT/SELECT`，無 `sysadmin`
• 定期 `DBCC CHECKDB` 檢查 BLOB 頁面完整性
• 備份策略：FILESTREAM 需 `BACKUP DATABASE` + 檔案系統備份，或使用 `FILESTREAM` 感知備份工具
• 清理孤立檔案：定期比對資料表與檔案系統，刪除無參考的 BLOB

遷移建議：IMAGE → VARBINARY(MAX)

-- 1. 新增欄位
ALTER TABLE old_table ADD new_image VARBINARY(MAX)

-- 2. 分批遷移 (避免長鎖定)
UPDATE TOP (1000) old_table SET new_image = CAST(old_image AS VARBINARY(MAX))
WHERE new_image IS NULL

-- 3. 驗證後切換應用、刪除舊欄位