SQL2005CLR函数扩展 - 关于山寨索引

原理介绍

索引建立

目录结构划分方案基于一个相对直接的思路：利用Unicode编码，将任意连续的两个字符（无论是中文还是英文）拆分为4个字节，并以此为基础构造一个四层目录结构。具体怎么做呢？就是将需要建立索引的内容对应的主键（主要目的是利用SQL索引并确保唯一性）作为文件名，同时，把这两个字符在原内容中的起始字节位置，作为文件的后缀名来保存。有意思的是，文件本身是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

索引使用

理解了建立过程，搜索就很好懂了。比如，我们要搜索关键词“寨索引”。

系统会先提取“寨索引”的Unicode字节，比如其前四个字节对应“232, 91, 34, 125”。那么，搜索的第一步就是去查找“../232/91/34/125/”这个目录下所有的文件。找到文件后，你会看到类似“pk001.1”这样的文件名，这里的后缀“1”很关键。

接下来，系统会根据这个后缀“1”，去检查下一个预期的文件是否存在。预期的文件路径是基于搜索词的下两个字节和主键构建的，比如“../34/125/21/95/pk001.2”。如果这个文件也存在，就说明“寨索引”这个连续序列在“山寨索引”这条数据的索引内容中间出现了，并且位置是连续的。

系统会这样依次类推，验证整个搜索词的连续匹配性，最后将所有满足条件的主键（在这个例子里就是pk001）作为一个结果集返回。这个过程本质上实现了一种精确的、类似数据库LIKE操作的通配符匹配。

实用性

这个方案的实用性究竟如何？坦率地说，还有待更多的验证和测试。

首先，它的核心能力是实现精确的LIKE匹配，这意味着它无法做到像常见搜索引擎那样进行智能分词。用户输入“北京天气”，它只会去找完全连续包含这四个字的记录，而不会分别匹配“北京”和“天气”。这算是它的一个主要局限。

其次，面对海量数据时，重建索引的性能可能会成为一个严峻的挑战。想象一下，为千万甚至上亿条文本数据建立这样的文件系统索引，对CPU和磁盘IO的压力是巨大的。每一个字符的变动，都需要在文件系统中进行一系列目录和文件的创建或删除操作，开销不小。

还有一个细节问题：在像Windows这样的操作系统下，单个目录内能存放多少文件而不至于导致文件检索性能急剧下降？这也是一个需要评估的点。当然，一个可行的优化思路是，可以根据主键的文件名计算一个哈希值，用这个哈希值再增加几层目录深度，从而将文件打散到不同的子目录中，降低单一目录下的文件数量，缓解性能压力。

演示效果

为了让大家有个直观的感受，这里有一个针对测试表`test`的`name`和`caption`两个字段建立索引并进行搜索的完整演示。

-- 设置和获取索引文件根目录
--select dbo.xfn_SetMyIndexFileRoot('d:/MyIndex')
--select dbo.xfn_GetMyIndexFileRoot()

-- 建立测试环境
go
create table test( id uniqueidentifier , name nvarchar ( 100), caption nvarchar ( 100))
insert into test select top 3 newid(), '我的索引' , '测试' from sysobjects
insert into test select top 3 newid(), '我的测试' , '索引' from sysobjects
insert into test select top 3 newid(), '测试索引' , '测试索引' from sysobjects
insert into test select top 3 newid(), '我的索引' , '索引' 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( '测试 索引 我的' , 'testIndex' ) b
where a.id = b.pk
/*
0C4634EA-DF94-419A-A8E5-793BD5F54EED 我的索引 测试
2DD87B38-CD3F-4F14-BB4A-00678463898F 我的索引 测试
8C67A6C3-753F-474C-97BA-CE85A2455E3E 我的索引 测试
C9706BF1-FB1F-42FB-8A48-69EC37EAD3E5 我的测试 索引
8BBF25CC-9DBB-4FCB-B2EB-D318E587DD5F 我的测试 索引
8B45322D-8E46-4691-961A-CD0078F1FA0A 我的测试 索引
*/
--drop table test

clr代码

方案的核心逻辑是通过SQL CLR实现的一段C#代码，编译为MyFullIndex.dll。其主要函数包括设置/获取索引根目录、建立索引以及查询索引。

完整代码如下（已精简格式）：

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

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean SetRoot(SqlString value) { ... } // 设置索引目录

    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString GetRoot() { ... } // 获取索引目录

    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlInt32 SetIndex(SqlString key, SqlString indexName, SqlString content) { ... } // 建立索引

    [SqlFunction(TableDefinition = "pk nvarchar(900)", Name = "GetIndex", FillRowMethodName = "FillRow")]
    public static IEnumerable GetIndex(SqlString word, SqlString indexName) { ... } // 查询索引

    public static void FillRow(Object obj, out SqlString pk) { ... }

    static string root = @"d:/index";

    // 内部处理函数：处理带空格的查询、单个词查询、建立索引文件等
    static System.Collections.Generic.List _getIndex2(string word, string indexName) { ... }
    static System.Collections.Generic.List _getIndex(string word, string indexName) { ... }
    static int _setIndex(string key, string indexName, string content) { ... }
};

部署的SQL脚本

最后，将CLR程序集部署到SQL Server中，并创建对应的用户自定义函数，以便在T-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

-- 设置索引文件根目录（默认目录为 d:/myindex ）
CREATE FUNCTION dbo.xfn_SetMyIndexFileRoot (@FileRoot nvarchar(max))
RETURNS bit
AS EXTERNAL NAME MyFullIndex.UserDefinedFunctions.SetRoot
GO