萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

透過 SQL Server 整合 Windows 索引服務實現上傳檔案全文檢索

專案需對上傳文件 (Office、PDF、文字檔) 內容進行全文搜尋。方案:Windows Indexing Service 建立內容索引,SQL Server 透過連結伺服器查詢索引目錄。

架構總覽

[上傳檔案] → [GUID 命名 + 保留副檔名] → [檔案系統目錄]
                    ↓
[SQL Server] ← [連結伺服器] ← [Indexing Service Catalog] ← [索引目錄內容]

實作步驟

1. 檔案儲存規範

  • 檔名 = NEWID() (GUID) + 原始副檔名 (如 A3F2... .docx)
  • 資料表記錄:FileGuid, OriginalName, Extension, UploadPath, BusinessRefId
  • 集中儲存於單一目錄 (如 D:\Uploads\Indexed\),便於索引服務掃描

2. 設定 Windows 索引服務

  1. 「電腦管理」→ 「服務與應用程式」→ 「索引服務」
  2. 右鍵 → 「新增」→ 「類別目錄」→ 命名 (如 DCSII)、選擇索引檔儲存位置
  3. 右鍵類別目錄 → 「新增」→ 「目錄」→ 選擇上傳檔案實體目錄 (D:\Uploads\Indexed\)
  4. 啟動索引服務,等待初始掃描完成 (狀態顯示「閒置」)
  5. 3. 效能調校

    • 右鍵索引服務 → 「所有工作」→ 「調整效能」→ 「自訂」→ 將「索引」與「查詢」滑桿拉至最右 (最高資源)
    • 停用/刪除系統預設 System 類別目錄 (避免索引系統檔案)

    4. 建立 SQL Server 連結伺服器

    EXEC sp_addlinkedserver 
        @server = 'IDX_SRV',           -- 連結伺服器名稱
        @srvproduct = 'Index Server',  -- 固定值
        @provider = 'MSIDXS',          -- 索引服務 OLE DB Provider
        @datasrc = 'DCSII';            -- 類別目錄名稱

    5. 查詢語法 (OPENQUERY)

    SELECT Q.*
    FROM OPENQUERY(IDX_SRV, '
        SELECT FileName, Size, DocAuthor, Path, Rank
        FROM SCOPE()
        WHERE CONTAINS(''關鍵字1 AND 關鍵字2'')
        ORDER BY Rank DESC
    ') AS Q;
    • SCOPE():指定搜尋範圍 (預設整個類別目錄)
    • CONTAINS / FREETEXT:全文檢索述詞
    • Rank:相關性分數
    • FileName 即 GUID 檔名,可 JOIN 业务表取得業務欄位

    6. 應用層整合 (動態 SQL 組裝)

    -- C# 範例
    string keywords = "關鍵字1 關鍵字2"; // 使用者輸入
    string ftQuery = keywords.Replace(" ", " AND "); // 簡易轉換
    
    string sql = $@"
    SELECT B.*, Q.Rank
    FROM BusinessTable B
    INNER JOIN OPENQUERY(IDX_SRV, '
        SELECT FileName, Rank
        FROM SCOPE()
        WHERE CONTAINS(''{0}'')
    ') Q ON Q.FileName = B.FileGuid
    ORDER BY Q.Rank DESC;";
    
    sql = string.Format(sql, ftQuery.Replace("'", "''")); // 防注入
    // 執行 sql...

    替代方案:SQL Server 內建全文檢索 (FTS)

  6. 維度Indexing Service + Linked ServerSQL Server Full-Text Search
    資料儲存檔案系統資料庫 VARBINARY(MAX)/FILESTREAM/FileTable
    備份/還原分離管理隨資料庫備份 (一致性保證)
    索引更新非同步、延遲同步/非同步可選、交易一致性
    濾鏡器 (Filter)系統 IFilter相同 IFilter,但整合更緊密
    查詢語法OPENQUERY + CONTAINS原生 CONTAINS/FREETEXT/CONTAINSTABLE
    維護複雜度高 (雙系統)低 (單一引擎)

    **現代專案強烈建議直接使用 SQL Server Full-Text Search (2008+ 內建、2012+ 重大增強)**,僅在無法將檔案存入資料庫 (法規、既有系統限制) 時考慮 Indexing Service 方案。

copyright © 萬盛學電腦網 all rights reserved