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

索引使用
比如搜索"寨索引 "
则搜索 "../232/91/34/125/" 目录下的所有文件,然后根据 pk001 .1的文件后缀名1,去看 ../34/125/21/95/pk001.2文件是否存在.依次类推,最后返回一个结果集.
--------------------------------------------------------------------------------
实用性
具 体的实用性还有待验证.这只是实现了精确的like搜索,而不能做常见搜索引擎的分词效果.另外海量数据重建索引的性能也是面临很严峻的问题,比如cpu 负载和磁盘io负载.关于windows一个目录下可以保持多少个文件而不会对文件搜索造成大的性能损失也有待评估,不过这个可以考虑根据主键的文件名 hash来增加文件目录深度降低单一目录下的文件数量.
--------------------------------------------------------------------------------
演示效果
实现了针对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代码如下:编译为MyFullIndex.dll

复制代码 代码如下:

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Collections.Generic;
public partial class UserDefinedFunctions
{
/// <summary>
/// 设置索引目录
/// </summary>
/// <param name="value"></param>
/// <returns></returns>
[Microsoft.SqlServer.Server.SqlFunction ]
public static SqlBoolean SetRoot(SqlString value)
{
if (value.IsNull) return false ;
if (System.IO.Directory .Exists(value.Value))
{
root = value.Value;
return true ;
}
else
{
return false ;
}
}
/// <summary>
/// 获取索引目录
/// </summary>
/// <returns></returns>
[Microsoft.SqlServer.Server.SqlFunction ]
public static SqlString GetRoot()
{
return new SqlString (root);
}
/// <summary>
/// 建立索引
/// </summary>
/// <param name="key"> 主键 </param>
/// <param name="indexName"> 索引名称 </param>
/// <param name="content"> 索引内容 </param>
/// <returns></returns>
[Microsoft.SqlServer.Server.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);
}

/// <summary>
/// 查询索引
/// </summary>
/// <param name="word"> 关键字（空格区分） </param>
/// <param name="indexName"> 索引名称 </param>
/// <returns></returns>
[SqlFunction (TableDefinition = "pk nvarchar(900)" , Name = "GetIndex" , FillRowMethodName = "FillRow" )]
public static IEnumerable GetIndex(SqlString word,SqlString indexName)
{

System.Collections.Generic.List <string > ret = new List <string >();
if (word.IsNull || indexName.IsNull) return ret;
return _getIndex2(word.Value, indexName.Value);
}

public static void FillRow(Object obj, out SqlString pk)
{
string key = obj.ToString();
pk = key;
}
static string root = @"d:/index" ;

/// <summary>
/// 获取有空格分隔的索引信息
/// </summary>
/// <param name="word"></param>
/// <param name="indexName"></param>
/// <returns></returns>
static System.Collections.Generic.List <string > _getIndex2(string word, string indexName)
{
string [] arrWord = word.Split(new char [] { ' ' }, StringSplitOptions .RemoveEmptyEntries);

System.Collections.Generic.List <string > key_0 = _getIndex(arrWord[0], indexName);

if (arrWord.Length == 0) return key_0;
System.Collections.Generic.List <string > [] key_list=new List <string >[arrWord.Length-1];
for (int i = 0; i < arrWord.Length-1; i++)
{
System.Collections.Generic.List <string > key_i = _getIndex(arrWord[i+1],indexName);
key_list[i] = key_i;
}

for (int i=key_0.Count-1;i>=0;i--)
{
foreach (System.Collections.Generic.List <string > key_i in key_list)
{
if (key_i.Contains(key_0[i]) == false )
{
key_0.RemoveAt(i);
continue ;
}
}
}
return key_0;
}
/// <summary>
/// 获取单个词的索引信息
/// </summary>
/// <param name="word"></param>
/// <param name="indexName"></param>
/// <returns></returns>
static System.Collections.Generic.List <string > _getIndex(string word, string indexName)
{
System.Collections.Generic.List <string > ret = new List <string >();
byte [] bWord = System.Text.Encoding .Unicode.GetBytes(word);
if (bWord.Length < 4) return ret;

string path = string .Format(@"{0}/{1}/{2}/{3}/{4}/{5}/" , root,indexName, bWord[0], bWord[1], bWord[2], bWord[3]);
if (System.IO.Directory .Exists(path) == false )
{
return ret;
}
string [] arrFiles = System.IO.Directory .GetFiles(path);

foreach (string file in arrFiles)
{
string key = System.IO.Path .GetFileNameWithoutExtension(file);
string index = System.IO.Path .GetExtension(file).TrimStart(new char [] { '.' });
int cIndex = int .Parse(index);
bool bHas = true ;
for (int i = 2; i < bWord.Length - 3; i = i + 2)
{
string nextFile = string .Format(@"{0}/{1}/{2}/{3}/{4}/{5}/{6}.{7}" ,
root, indexName, bWord[i + 0], bWord[i + 1], bWord[i + 2], bWord[i + 3], key, ++cIndex);

if (System.IO.File .Exists(nextFile) == false )
{
bHas = false ;
break ;
}
}
if (bHas == true &&ret.Contains(key)==false )
ret.Add(key);

}
return ret;
}

/// <summary>
/// 建立索引文件
/// </summary>
/// <param name="key"></param>
/// <param name="indexName"></param>
/// <param name="content"></param>
/// <returns></returns>
static int _setIndex(string key,string indexName, string content)
{
byte [] bContent = System.Text.Encoding .Unicode.GetBytes(content);
if (bContent.Length <= 4) return 0;
for (int i = 0; i < bContent.Length - 3; i = i + 2)
{
string path = string .Format(@"{0}/{1}/{2}/{3}/{4}/{5}/" , root,indexName, bContent[i + 0], bContent[i + 1], bContent[i + 2], bContent[i + 3]);
if (System.IO.Directory .Exists(path) == false )
{
System.IO.Directory .CreateDirectory(path);
}
string file = string .Format(@"{0}/{1}.{2}" , path, key, i / 2);

if (System.IO.File .Exists(file) == false )
{
System.IO.File .Create(file).Close();
}
}
return content.Length;
}
};

--------------------------------------------------------------------------------
部署的sql脚本如下
--drop function dbo.xfn_SetMyIndexFileRoot
--drop function dbo.xfn_GetMyIndexFileRoot
--drop function dbo.xfn_GetKeyFromMyIndex
--drop function dbo.xfn_SetKeyForMyIndex
--drop assembly MyFullIndex
--go
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

时间： 2024-04-19 08:02:13

SQL2005CLR函数扩展 - 关于山寨索引的相关文章

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

本文只是一个山寨试验品,思路仅供参考. --------------------------------------------------------------------------------原理介绍:索引建立 目录结构划分方案也只是很简易的实现了一下,通过unicode把任意连续的两个字符(中文或英文)分为4个字节来做四层目录,把索引的内容对应的主关键字(主要为了使用sql索引和唯一性)作为文件名,两个字符在索引内容中的位置作为文件后缀来存储.文件本身为0字节,不保存任何信息. 比如一

SQL2005CLR函数扩展-深入环比计算的详解

环比就是本月和上月的差值所占上月值的比例.在复杂的olap计算中我们经常会用到同比环比等概念,要求的上个维度的某个字段的实现语句非常简练,比如ssas的mdx语句类似[维度].CurrentMember.Prevmember就可以了   此类问题还可以延伸到类似进销存的批次计算中,这也要关注其他历史记录来决定当前某条记录的状态. sql语句无法简单实现mdx语句的类似功能,必须得用交叉表关联来对比.这里我们用CLR函数来实现mdx语句的类似语法.在select的时候把得到过的做个缓存就可以了.效

SQL2005CLR函数扩展-繁简转换的实现代码

这个方法比较简单,用Microsoft.VisualBasic命名空间下强大的字符串处理函数就可以了c#代码如下,编译为BigConvertor.dll-------------------------------------------------------------------------------- 复制代码 代码如下: using System; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public

SQL2005CLR函数扩展-解析天气服务的实现

我们可以用CLR获取网络服务 来显示到数据库自定函数的结果集中,比如163的天气预报http://news.163.com/xml/weather.xml他的这个xml结果的日期是不正确的,但这个我们暂不讨论.从这个xml获取天气的CLR代码如下,用WebClient访问一下就可以了.然后通过Dom对象遍历节点属性返回给结果集. -------------------------------------------------------------------------------- 复制代

SQL2005CLR函数扩展-繁简转换的实现代码_mssql2005

这个方法比较简单,用Microsoft.VisualBasic命名空间下强大的字符串处理函数就可以了c#代码如下,编译为BigConvertor.dll-------------------------------------------------------------------------------- 复制代码 代码如下: using System; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public

SQL2005CLR函数扩展-深入环比计算的详解_mssql2005

此类问题还可以延伸到类似进销存的批次计算中,这也要关注其他历史记录来决定当前某条记录的状态. sql语句无法简单实现mdx语句的类似功能,必须得用交叉表关联来对比.这里我们用CLR函数来实现mdx语句的类似语法.在select的时候把得到过的做个缓存就可以了.效率应该可以提高不少. clr的代码如下,编译为TestFun.dll,复制到sql服务器的文件目录下.--------------------------------------------------------------------

SQL2005CLR函数扩展-数据导出的实现详解

SQLServer数据导出到excel有很多种方法,比如dts.ssis.还可以用sql语句调用openrowset.我们这里开拓思路,用CLR来生成Excel文件,并且会考虑一些方便操作的细节. 下面我先演示一下我实现的效果,先看测试语句--------------------------------------------------------------------------------exec BulkCopyToXls 'select * from testTable' , 'd:

PostgreSQL 11 preview - pgbench 变量、函数扩展 - 暨pgbench 自定义 benchmark讲解

标签 PostgreSQL , pgbench , 压测 , 变量 , tpc-b , 自定义压测 背景 pgbench是PostgreSQL软件包中的一款benchmark软件,纯C编码,效率高,压测方便. 内置TPC-B benchmark测试,同时支持自定义benchmark. 详细文档见 https://www.postgresql.org/docs/10/static/pgbench.html pgbench 自定义benchmark脚本支持的语法 变量赋值的语法 压测需要生成输入变量

Windows Powershell 通过函数扩展别名_PowerShell

在Powershell中设置别名的确方便快捷,但是在设置别名的过程中并设置参数的相关信息.尽管别名会自动识别参数,但是如何把经常使用的参数默认设定在别名里面呢?例如Test-Connection -Count 2 -ComputerName,让-"-Count 2″ 固化在别名中. 这时简单的别名无法完成上述需求,可以通过函数来完成它,并且一旦把函数拉过来,定义别名会变得更加灵活. PS C:\PS> function test-conn { Test-Connection -Count