SQL Vector Index 向量索引與優化

當資料量達到數萬甚至數百萬筆時，對每一筆資料計算向量距離（暴力掃描）會導致查詢時間從毫秒級飆升至秒級。為了讓 AI 搜尋具有實用性，我們必須使用特殊的向量索引。

核心概念：ANN (近似最近鄰)

與傳統 B-Tree 索引（尋找 100% 精確值）不同，向量索引通常基於 ANN (Approximate Nearest Neighbors)。

• 為什麼？：在高維度空間中，找到絕對的最近鄰極度耗時。
• 代價：為了極致的速度，我們會犧牲一點點的精準度（召回率 Recall）。例如，100% 精確搜尋需要 5 秒，但 99% 精確的 ANN 搜尋只需要 5 毫秒。

PostgreSQL (pgvector) 的索引實作

PostgreSQL 是目前 SQL 領域中向量索引最成熟的解決方案，主要提供兩大演算法：

1. IVFFlat (Inverted File Index)

IVFFlat 將向量空間劃分為多個聚類 (Lists)。搜尋時只檢查最接近的幾個清單。

• 適用場景：記憶體有限、資料更新頻繁、或對精準度要求適中的情況。
• 關鍵參數：lists。建議設為 資料行數 / 1000 (對於 100 萬行，設為 1000)。

-- 建立索引
CREATE INDEX ON articles USING ivfflat (embedding vector_cosine_ops) 
WITH (lists = 100);

-- 查詢優化：搜尋更多聚類中心以提升精準度
SET ivfflat.probes = 10; 

2. HNSW (Hierarchical Navigable Small Worlds)

HNSW 建立一個多層網絡。它像地圖一樣，先在大範疇移動，再精確定位。

• 適用場景：生產環境的首選。提供極高的召回率與極快的搜尋速度。
• 關鍵參數：m (每節點連線數)、ef_construction (建立時的搜尋深度)。

-- 建立 HNSW 索引
CREATE INDEX ON articles USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- 查詢優化：ef_search 越大越準，但越慢
SET hnsw.ef_search = 100;

MySQL 的索引現況與展望

在 MySQL 8.4+ 核心版本中，向量索引目前仍處於持續演進階段。

1. MySQL HeatWave (雲端版)：支援高效的向量索引與自動轉置，適合大規模 AI 應用。
2. Community Edition (社群版)：目前尚未內建與 HNSW 完全等效的索引。在此之前，常見的優化手段是：
   • 預先篩選：利用 WHERE 子句的其他 B-Tree 索引欄位（如 user_id）將資料範圍縮小，再進行向量運算。
   • 函數索引：針對向量的某些關鍵特徵建立索引（Workaround）。

Azure SQL / SQL Server 的原生索引

微軟在最近的更新中為 Azure SQL 引入了內建的向量索引支援 (Preview)。

建立 HNSW 向量索引

SQL Server 採用的 HNSW 實作經過磁碟 I/O 優化：

CREATE INDEX idx_vector ON TableName (VectorColumn)
WITH (INDEX_TYPE = HNSW);

效能調優策略：三元平衡

在實務中，你需要根據業務需求調整以下三個面向：

1. Recall (召回率)：檢索結果中有多少比例是真正的「最近鄰」。
2. Latency (延遲)：單次搜尋所需的時間 (ms)。
3. Throughput (吞吐量)：系統每秒能處理多少次查詢。

調優 Checklist：

• 硬體資源：HNSW 索引非常消耗記憶體 (RAM)，確保資料庫伺服器有足夠的緩存。
• 正規化：存入向量前執行正規化（將模長轉為 1），能讓 Inner Product 計算快上許多。
• 索引重建：資料大量刪除或變更後，IVFFlat 索引的精準度會下降，建議定期重新建立索引。

總結

• 小數據 (< 10,000)：不需索引，暴力掃描最準。
• 追求極速 (PostgreSQL)：首選 HNSW。
• 追求成本效益 (PostgreSQL)：選擇 IVFFlat。
• MySQL/SQL Server 用戶：優先利用 WHERE 子句篩選資料範圍，並關注最新的 HNSW 支援更新。

透過掌握向量索引，你就能讓資料庫在處理海量 AI 資料時，依然保持如閃電般的反應速度！