SQL 搜尋優化實戰：從濫用暫存表、排序到極致效能的漢字轉拼音函數演進

問題情境

需求：使用者輸入關鍵字「x」，搜尋使用者姓名（含中英文），結果排序規則：

1. 完全匹配優先（x）。
2. 前綴匹配次之，短者優先（xia → xiao）。
3. 包含匹配再次之。
4. 中文姓名需以拼音首字母參與比對（如「夏榮」→ XR，「楊星」→ YX）。

方案演進與效能對比（10,000 筆測試資料）

最終版函數碼

CREATE FUNCTION [dbo].[fn_getpy5](@str NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN
    DECLARE @str_len INT = LEN(@str),
            @result NVARCHAR(4000) = N'',
            @crs NVARCHAR(1);

    WHILE @str_len > 0
    BEGIN
        SET @crs = SUBSTRING(@str, @str_len, 1);

        SELECT @str_len = @str_len - 1,
               @result = CASE
                   WHEN @crs >= N'帀' THEN N'Z'
                   WHEN @crs >= N'丫' THEN N'Y'
                   WHEN @crs >= N'夕' THEN N'X'
                   WHEN @crs >= N'屲' THEN N'W'
                   WHEN @crs >= N'他' THEN N'T'
                   WHEN @crs >= N'仨' THEN N'S'
                   WHEN @crs >= N'呥' THEN N'R'
                   WHEN @crs >= N'七' THEN N'Q'
                   WHEN @crs >= N'妑' THEN N'P'
                   WHEN @crs >= N'噢' THEN N'O'
                   WHEN @crs >= N'拏' THEN N'N'
                   WHEN @crs >= N'嘸' THEN N'M'
                   WHEN @crs >= N'垃' THEN N'L'
                   WHEN @crs >= N'咔' THEN N'K'
                   WHEN @crs >= N'丌' THEN N'J'
                   WHEN @crs >= N'铪' THEN N'H'
                   WHEN @crs >= N'旮' THEN N'G'
                   WHEN @crs >= N'發' THEN N'F'
                   WHEN @crs >= N'妸' THEN N'E'
                   WHEN @crs >= N'咑' THEN N'D'
                   WHEN @crs >= N'嚓' THEN N'C'
                   WHEN @crs >= N'八' THEN N'B'
                   WHEN @crs >= N'吖' THEN N'A'
                   ELSE @crs
               END + @result;
    END
    RETURN @result;
END

核心優化原則

1. 避免暫存表/表變數：在純量函數中建立、填充、掃描暫存物件是效能殺手。
2. 消除排序：ORDER BY、ROW_NUMBER、TOP 1 ... ORDER BY 皆帶來 O(N log N) 開銷。
3. 善用 CASE WHEN 短路求值：由高頻區間向低頻排列，平均比較次數最小化。
4. 集合思維 vs 程序思維：SQL 擅長集合運算，逐字迴圈（RBAR）應為最後手段。

搜尋查詢整合範例

DECLARE @kw NVARCHAR(50) = N'x';

WITH cte AS (
    SELECT name,
           CASE
               WHEN name LIKE @kw + N'%' THEN 1
               WHEN name LIKE N'%' + @kw + N'%' THEN 2
               WHEN dbo.fn_getpy5(name) LIKE @kw + N'%' THEN 3
               WHEN dbo.fn_getpy5(name) LIKE N'%' + @kw + N'%' THEN 4
               ELSE 5
           END AS ord,
           LEN(name) AS ln
    FROM zhuisuos
)
SELECT name
FROM cte
WHERE ord <= 4
ORDER BY ord, ln;

結語

從 20 秒到 0.5 秒，不靠更強硬體，而是回歸簡單：移除不必要的物件、排序、迴圈，用最直觀的邏輯讓 CPU 直接跑完。優化的盡頭，往往是最樸素的代碼。