您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

SQL Server 2005 CLR 函數擴展：自製「山寨索引」實作詳解

編輯：MySQL 資料庫相關知識

前言

本文記錄一個基於 SQL Server 2005 CLR 整合的實驗性專案，實作一套利用檔案系統目錄結構模擬倒排索引的「山寨索引」。此方案僅供技術參考，生產環境實用性尚待驗證。

核心原理

索引建立機制

採用四層目錄結構對應 Unicode 編碼：將任意連續兩個字符（中英文皆可）轉為 4 位元組，作為四層目錄路徑。索引內容以主鍵值為檔名，字符在原文中的位置作為副檔名。檔案本身為 0 位元組，不儲存實際資料。

具體範例

假設資料：主鍵 pk001，內容 山寨索引 四字的 Unicode 位元組陣列為：

[0]: 113  [1]: 92  [2]: 232  [3]: 91
[4]: 34   [5]: 125 [6]: 21  [7]: 95

對應生成的檔案結構：

../113/92/232/91/pk001.0
../232/91/34/125/pk001.1
../34/125/21/95/pk001.2

索引查詢邏輯

搜尋 寨索引 時，先定位到 ../232/91/34/125/ 目錄下的所有檔案，根據副檔名 .1 推導下一個應存在的檔案 ../34/125/21/95/pk001.2 是否存在，以此類推完成精確匹配。

實用性評估與限制

僅支援精確 LIKE 搜尋，無法實現分詞搜尋引擎效果
海量資料重建索引時面臨 CPU 與磁碟 I/O 雙重壓力
單一目錄檔案數量上限對效能影響需評估，可透過主鍵 Hash 增加目錄深度緩解

演示環境與部署腳本

SQL 測試腳本

-- 設定/取得索引根目錄
SELECT dbo.xfn_SetMyIndexFileRoot('d:/MyIndex');
SELECT dbo.xfn_GetMyIndexFileRoot();

-- 建立測試表
CREATE TABLE test(
    id UNIQUEIDENTIFIER,
    name NVARCHAR(100),
    caption NVARCHAR(100)
);
INSERT INTO test SELECT TOP 3 NEWID(), N'我的索引', N'測試' FROM sysobjects;
INSERT INTO test SELECT TOP 3 NEWID(), N'我的測試', N'索引' FROM sysobjects;
INSERT INTO test SELECT TOP 3 NEWID(), N'測試索引', N'測試索引' FROM sysobjects;
INSERT INTO test SELECT TOP 3 NEWID(), N'我的索引', N'索引' FROM sysobjects;
CREATE INDEX i_testid ON test(id);

-- 建立索引檔案
DECLARE @t INT;
SELECT @t = dbo.xfn_SetKeyForMyIndex(id, 'testIndex', name + ' ' + caption) FROM test;

-- 查詢測試
SELECT a.* FROM test a, dbo.xfn_GetKeyFromMyIndex(N'測試 索引 我的', 'testIndex') b
WHERE a.id = b.pk;

CLR 核心程式碼 (C#)

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
using System.IO;
using System.Text;

public partial class UserDefinedFunctions
{
    static string root = @"d:/index";

    [SqlFunction]
    public static SqlBoolean SetRoot(SqlString value)
    {
        if (value.IsNull) return false;
        return Directory.Exists(root = value.Value);
    }

    [SqlFunction]
    public static SqlString GetRoot() => new SqlString(root);

    [SqlFunction]
    public static SqlInt32 SetIndex(SqlString key, SqlString indexName, SqlString content)
    {
        if (key.IsNull || content.IsNull || indexName.IsNull) return 0;
        return _setIndex(key.Value, indexName.Value, content.Value);
    }

    [SqlFunction(TableDefinition = "pk nvarchar(900)", Name = "GetIndex", FillRowMethodName = "FillRow")]
    public static IEnumerable GetIndex(SqlString word, SqlString indexName)
    {
        var ret = new List();
        if (word.IsNull || indexName.IsNull) return ret;
        return _getIndex2(word.Value, indexName.Value);
    }

    public static void FillRow(object obj, out SqlString pk)
    {
        pk = obj.ToString();
    }

    static List _getIndex2(string word, string indexName)
    {
        var arrWord = word.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
        var key0 = _getIndex(arrWord[0], indexName);
        if (arrWord.Length <= 1) return key0;

        var keyList = new List[arrWord.Length - 1];
        for (int i = 0; i < arrWord.Length - 1; i++)
            keyList[i] = _getIndex(arrWord[i + 1], indexName);

        for (int i = key0.Count - 1; i >= 0; i--)
        {
            foreach (var keyI in keyList)
                if (!keyI.Contains(key0[i])) { key0.RemoveAt(i); break; }
        }
        return key0;
    }

    static List _getIndex(string word, string indexName)
    {
        var ret = new List();
        byte[] bWord = Encoding.Unicode.GetBytes(word);
        if (bWord.Length < 4) return ret;

        string path = $"{root}/{indexName}/{bWord[0]}/{bWord[1]}/{bWord[2]}/{bWord[3]}/";
        if (!Directory.Exists(path)) return ret;

        foreach (string file in Directory.GetFiles(path))
        {
            string key = Path.GetFileNameWithoutExtension(file);
            int cIndex = int.Parse(Path.GetExtension(file).TrimStart('.'));
            bool has = true;

            for (int i = 2; i < bWord.Length - 3; i += 2)
            {
                string nextFile = $"{root}/{indexName}/{bWord[i]}/{bWord[i+1]}/{bWord[i+2]}/{bWord[i+3]}/{key}.{++cIndex}";
                if (!File.Exists(nextFile)) { has = false; break; }
            }
            if (has && !ret.Contains(key)) ret.Add(key);
        }
        return ret;
    }

    static int _setIndex(string key, string indexName, string content)
    {
        byte[] bContent = Encoding.Unicode.GetBytes(content);
        if (bContent.Length <= 4) return 0;

        for (int i = 0; i < bContent.Length - 3; i += 2)
        {
            string path = $"{root}/{indexName}/{bContent[i]}/{bContent[i+1]}/{bContent[i+2]}/{bContent[i+3]}/";
            if (!Directory.Exists(path)) Directory.CreateDirectory(path);

            string file = $"{path}{key}.{i/2}";
            if (!File.Exists(file)) File.Create(file).Close();
        }
        return content.Length;
    }
}

SQL 部署腳本

CREATE ASSEMBLY MyFullIndex FROM 'd:/SQLCLR/MyFullIndex.dll' WITH PERMISSION_SET = UNSAFE;
GO

CREATE FUNCTION dbo.xfn_GetKeyFromMyIndex(@word NVARCHAR(MAX), @indexName NVARCHAR(900))
RETURNS TABLE (pk NVARCHAR(100))
AS EXTERNAL NAME MyFullIndex.UserDefinedFunctions.GetIndex;
GO

CREATE FUNCTION dbo.xfn_SetKeyForMyIndex(@pk NVARCHAR(900), @indexName NVARCHAR(900), @word NVARCHAR(MAX))
RETURNS INT
AS EXTERNAL NAME MyFullIndex.UserDefinedFunctions.SetIndex;
GO

CREATE FUNCTION dbo.xfn_GetMyIndexFileRoot()
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME MyFullIndex.UserDefinedFunctions.GetRoot;
GO

CREATE FUNCTION dbo.xfn_SetMyIndexFileRoot(@FileRoot NVARCHAR(MAX))
RETURNS BIT
AS EXTERNAL NAME MyFullIndex.UserDefinedFunctions.SetRoot;
GO