資料庫搜尋效能優化：從 SQL LIKE、MSSQL Full-Text Search 到 Elasticsearch

---

---

隨著網站內容和用戶數量的增長，搜尋功能的效能往往成為系統的瓶頸。本文紀錄筆者遇到瓶頸當下的考量、研究與最終根據系統使用量的發展所做的權衡，將探討如何從最基本的 SQL LIKE 查詢，逐步優化到企業級的全文搜尋解決方案。

當網站文章數量增加、用戶搜尋頻率提高時，傳統的搜尋方式會遇到明顯的效能問題。

sql
select * from posts
where content like '%search_text%'

• 優點: 簡單易用、支援模糊搜尋

• 缺點: 資料庫無法使用索引，造成全表掃描（Full Table Scan），在資料量大時會非常慢。

---

當面對大量文本搜尋需求時，我們需要更好的解決方案。基本上都是從索引的概念延伸。

資料寫入時，針對要被搜尋的內容進行分段，並建立反向索引提升搜尋效能。

json
"apple is a red fruit"

["apple", "is", "a", "red", "fruit"]

"蘋果是一種紅色的水果"

["蘋果", "是", "一種", "紅色", "的", "水果"]

• 正向索引（Forward Index）：每份文件記錄它有哪些詞
• 反向索引（Inverted Index）：每個詞記錄它出現在那些文件中

json
[
  { "post_id": 1, "content": "apple is a red fruit" },
  { "post_id": 2, "content": "apple is green" },
  { "post_id": 3, "content": "banana and apple" }
]

以下為模擬實際儲存在資料庫的形式:

原始資料（正向索引）：

反向索引結果：

所以只要查詢關鍵字，透過反向索引就能快速找到對應的原始資料。

接下來將介紹兩種主要的優化方法。

在安裝 SQL Server 時，全文檢索 (Full-Text Search) 是一個可選元件。要確認是否已安裝：

sql
SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AS IsFullTextInstalled;

• 回傳 1 → 已安裝
• 回傳 0 → 未安裝，需要重新安裝 SQL Server 功能

全文索引需要一個 Catalog 來存放。

sql
CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;

這會建立一個名稱為 ftCatalog 的全文檢索目錄，並設為預設目錄。

假設有一個 Articles 資料表，裡面有 Title 與 Content 欄位：

sql
CREATE TABLE Articles (
    ID INT PRIMARY KEY,
    Title NVARCHAR(200),
    Content NVARCHAR(MAX)
);

你要先對表格加上唯一索引 (Unique Index)，作為全文索引的「錨點」：

sql
CREATE UNIQUE INDEX IX_Articles_ID ON Articles(ID);

接著建立全文檢索索引：

sql
CREATE FULLTEXT INDEX ON Articles
(
    Title LANGUAGE 1033,   -- 英文
    Content LANGUAGE 1028  -- 繁體中文
)
KEY INDEX IX_Articles_ID
ON ftCatalog
WITH CHANGE_TRACKING AUTO;

說明：

• LANGUAGE 1033 → 英文 (LCID)
• LANGUAGE 1028 → 繁體中文 (LCID)
• CHANGE_TRACKING AUTO → 自動更新索引（建議測試環境用；正式環境可考慮 MANUAL，定期批次更新）

sql
-- 使用 CONTAINS
SELECT * FROM Articles WHERE CONTAINS(Content, N'資料庫');

-- 使用 FREETEXT
SELECT * FROM Articles WHERE FREETEXT(Content, N'SQL 很強大');

全文索引會隨時間膨脹，需要定期維護：

sql
-- 重建全文索引
ALTER FULLTEXT CATALOG ftCatalog REBUILD;

-- 重組全文索引
ALTER FULLTEXT CATALOG ftCatalog REORGANIZE;

• 錯誤：找不到全文檢索服務 → 沒有安裝 Full-Text Search 功能
• 錯誤：無法建立全文索引，因為沒有唯一索引 → 必須先對主鍵或唯一欄位建立索引
• 查不到中文內容 → 要確認欄位使用 NVARCHAR，且建立索引時指定 LANGUAGE 1028（繁體中文）

---

可以使用 CONTAINS 和 FREETEXT 語法

sql
SELECT * 
FROM your_table
WHERE CONTAINS(column_name, @query_text)  
or 
WHERE FREETEXT(column_name, @query_text)

多欄位查詢

sql
SELECT *
FROM your_table
WHERE CONTAINS( (column1, column2, column3), @query_text );

---

sql
原始內容：
SQL is powerful

SQL Server 拆成：
[SQL], [is], [powerful]

反向索引的分詞為[SQL], [is], [powerful]

sql
CONTAINS(content, 'sq')  //若輸入'sq'，會查不到

sql
原始內容：
SQL很強大
SQL Server 拆成：
[SQL],[很],[強大]

反向索引的分詞為[SQL],[很],[強大]

sql
CONTAINS(content, 'SQL')  -- ✅ 查得到
CONTAINS(content, '強大')          -- ✅ 查得到（如果查整段）
CONTAINS(content, '很強大')            -- ❌ 查不到（因為沒分詞）

原理為在輸入查詢字的時候，進行預處理，增加搜尋廣度，再嘗試搜尋 語意上相關的內容

• "很強大" → ["很", "強", "大"]
• "running" → "run"
• driving" → "drive"

等於是針對輸入的查詢字串分割再嘗試搜尋，希望能有效碰撞到反向索引的分詞。

不需精準詞、模糊搜尋、但效能略低於 CONTAINS，但仍比 LIKE 快很多

為了較符合業務需求，預防分詞沒對上就沒搜尋結果的情況，會混合兩個語法使用。

sql
DECLARE @keyword NVARCHAR(100) = N'內容';

-- 優先用 CONTAINS 查
IF EXISTS (
    SELECT 1 FROM Articles
    WHERE CONTAINS(Content, @keyword)
)
BEGIN
    SELECT ID, Title, Content
    FROM Articles
    WHERE CONTAINS(Content, @keyword);
END
ELSE
BEGIN
    -- 如果找不到，退而求其次用 FREETEXT
    SELECT ID, Title, Content
    FROM Articles
    WHERE FREETEXT(Content, @keyword);
END

---

Elasticsearch 是一個基於 Apache Lucene 的分散式搜尋引擎，專門用來處理大量文字資料的搜尋和分析。在現代網站架構中，它幾乎已經成為構建搜尋功能的標準選擇。

說到處理百萬、千萬級別的資料搜尋，Elasticsearch 確實是目前最好的解決方案。這不是沒有原因的：

Elasticsearch 從一開始就是為了處理大量資料而設計的。當你的資料越來越多時，你可以很輕鬆地增加更多機器來分擔工作負載。資料會自動分散到不同的節點上，這樣就不會有某台機器特別忙碌的問題。

而且當有很多人同時搜尋時，這些查詢請求也會分散到不同的節點去處理，大家一起來幫忙回答問題。就算其中一台機器掛了，其他機器還是可以繼續服務，不會影響到使用者。

Elasticsearch 也是使用的是反向索引，且使用記憶體來快取資料，所以搜尋速度可以比傳統資料庫快上數十倍。

另外，它可以同時用多個執行緒來處理不同部分的查詢，這就像是有很多人同時在不同的書架上幫你找書一樣，當然會比一個人慢慢找要快得多。

這裡放一個實際的效能比較表，讓大家有更具體的概念：

可以看到，當資料量越來越大時，Elasticsearch 的優勢就越來越明顯。

如果你的系統有以下情況，請直接考慮使用 Elasticsearch~

資料量超過 100 萬筆：這時候傳統資料庫就會開始力不從心了。

每天搜尋請求超過 10,000 次：這代表你的搜尋功能被大量使用，需要更強健的架構。

需要毫秒級回應：如果使用者希望輸入關鍵字後能即時看到結果。

需要複雜的數據分析：比如統計搜尋熱門關鍵字、分析使用者行為等。

Elasticsearch 的好處也包含提供API的形式取得資料，減少開發流程。

json
POST /articles/_search
{
  "query": {
    "match": {
      "content": "Elasticsearch"
    }
  }
}

開啟模糊搜尋

json
{
  "query": {
    "fuzzy": {
      "title": {
        "value": "elasticsarch",      ← 打錯字
        "fuzziness": "AUTO",          ← 自動判斷允許幾個錯
        "prefix_length": 1,           ← 前面幾個字要正確才比
        "max_expansions": 3          ← 匹配的詞的上限
      }
    }
  }
}

中文在文章中，總是以逗號句號分隔，並不像英文，以空格來區分每個單字，這在分詞與建立索引時，便會產生複雜度，因為建立分詞的演算法，對應的是查詢結果的準確性，所以選擇好的分詞器，變得非常重要，在MSSQL 全文搜尋分詞器是內建的，只能依靠微軟，Elasticsearch 則是提供多種分詞器，依據不同的選擇來達到開發者實際想要達到的效果。

• 中文模糊查詢支援有限，因為中文不是字母構成，沒辦法像英文一樣比「字母差異」。

Elasticsearch 可以對中文做 fuzzy，但結果常不符合直覺，因為不是基於語意或拼音，而是字元編碼差異

以下比較目前普遍常見的模糊搜尋策略

範例:

查詢：「怎麼買 iPhone」

---

為了驗證優化效果，我在包含數千篇文章的測試環境中進行了效能比較：

在規劃搜尋系統時，需要考慮數據增長對效能的影響：

容量計算說明：

• 原始數據估算：以平均每篇文章 10KB 計算（包含標題、內容、標籤等元數據）
• 全文索引大小估算：基於實際測試經驗，索引大小通常為原始數據的 1.2-1.8 倍
  • MSSQL 全文索引：包含反向索引、詞頻統計、位置資訊等
  • Elasticsearch 索引：包含分詞、反向索引、段落合併、壓縮等優化，索引大小可能更大，依分詞器與設定而異
• 效能測試環境：SQL Server 2019, 8GB RAM, SSD 硬碟

⚠️ 注意：實際索引大小會因內容類型、語言、分詞器設定而有顯著差異，建議根據實際數據進行測試。

基於實際業務需求，使用 MSSQL Full-Text Search 已能達到，以下是幾個針對搜尋系統能持續優化的方向：

選擇合適的搜尋技術需要考慮你的實際需求：

• 建議：SQL LIKE 或 MSSQL Full-Text Search
• 原因：實作簡單、維護成本低、效能足夠日常使用

• 建議：MSSQL Full-Text Search
• 原因：實作相對簡單、維護成本還算合理、效能比 LIKE 查詢好很多

• 強烈建議：Elasticsearch
• 原因：
  • 分散式設計：可以隨時加機器來處理更大的資料量
  • 速度很快：即使資料很多也能保持毫秒級的查詢速度
  • 功能豐富：除了搜尋還能做各種數據分析
  • 容易擴展：不用擔心未來資料量增長的問題

• 唯一選擇：Elasticsearch 叢集部署
• 建議配置：
  • 用多台機器組成叢集
  • 合理規劃資料分片
  • 搭配 Kibana 來看數據報表
  • 把舊資料和新資料分開存放

• 建議：Elasticsearch + 向量語意搜尋
• 原因：可以提供類似 Google 那樣聰明的搜尋體驗

搜尋功能的優化是一個循序漸進的過程：

• SQL LIKE：適合小型系統（少於 1 萬筆資料）
• MSSQL Full-Text Search：中型系統的穩健選擇（1–10 萬筆資料）
• Elasticsearch：大規模資料的最佳解法（超過 10 萬筆資料）
• 向量語意搜尋：未來的發展方向

👉 若系統資料量超過 百萬筆，Elasticsearch 幾乎是唯一解，因為它能同時兼顧效能、擴展性與成本效益。
小型系統則不必過度設計，MSSQL Full-Text Search 就能滿足日常需求。

參考資料

• Elasticsearch 官方文檔
• MSSQL Full-Text Search 最佳實務

SQLElasticsearch全文搜尋效能優化

---

---