SQL2005CLR函数扩展-山寨索引
来源:互联网 发布:阿里云服务器费用100万 编辑:程序博客网 时间:2024/05/21 23:52
本文只是一个山寨试验品,思路仅供参考.
对于文件索引lucene才是权威,这里只是自己实现了一个可以实现简单文件索引的半成品.所谓文件索引就是把sql字符串按字节分词保存到磁盘文件目录结构中用来快速定位.
原理介绍:
索引建立
目录结构划分方案也只是很简易的实现了一下,通过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两个字段作索引搜索.
--设置和获取索引文件根目录
--selectdbo.xfn_SetMyIndexFileRoot('d:/MyIndex')
--selectdbo.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 tabletest
clr代码如下:编译为MyFullIndex.dll
usingSystem;
usingSystem.Data.SqlTypes;
usingMicrosoft.SqlServer.Server;
usingSystem.Collections;
usingSystem.Collections.Generic;
public partial class UserDefinedFunctions
{
/// <summary>
/// 设置索引目录
/// </summary>
/// <param name="value"></param>
/// <returns></returns>
[Microsoft.SqlServer.Server.SqlFunction]
public staticSqlBoolean SetRoot(SqlStringvalue)
{
if (value.IsNull) return false;
if (System.IO.Directory.Exists(value.Value))
{
root = value.Value;
returntrue;
}
else
{
returnfalse;
}
}
/// <summary>
/// 获取索引目录
/// </summary>
/// <returns></returns>
[Microsoft.SqlServer.Server.SqlFunction]
public staticSqlString GetRoot()
{
return newSqlString(root);
}
/// <summary>
/// 建立索引
/// </summary>
/// <param name="key">主键</param>
/// <param name="indexName">索引名称</param>
/// <param name="content">索引内容</param>
/// <returns></returns>
[Microsoft.SqlServer.Server.SqlFunction]
public staticSqlInt32 SetIndex(SqlStringkey,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 staticIEnumerable GetIndex(SqlStringword,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 staticvoid FillRow(Objectobj, out SqlStringpk)
{
string key = obj.ToString();
pk = key;
}
static stringroot = @"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=newList<string>[arrWord.Length-1];
for (inti = 0; i < arrWord.Length-1; i++)
{
System.Collections.Generic.List<string>key_i = _getIndex(arrWord[i+1],indexName);
key_list[i] = key_i;
}
for (inti=key_0.Count-1;i>=0;i--)
{
foreach(System.Collections.Generic.List<string> key_i inkey_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, stringindexName)
{
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)
{
returnret;
}
string[] arrFiles = System.IO.Directory.GetFiles(path);
foreach (stringfile in arrFiles)
{
stringkey = System.IO.Path.GetFileNameWithoutExtension(file);
stringindex = System.IO.Path.GetExtension(file).TrimStart(new char[] { '.' });
intcIndex = int.Parse(index);
boolbHas = true;
for(int i = 2; i < bWord.Length - 3; i = i + 2)
{
stringnextFile = 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,stringindexName, string content)
{
byte[] bContent = System.Text.Encoding.Unicode.GetBytes(content);
if (bContent.Length <= 4) return 0;
for (inti = 0; i < bContent.Length - 3; i = i + 2)
{
stringpath = 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);
}
stringfile = 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脚本如下
--dropfunction dbo.xfn_SetMyIndexFileRoot
--dropfunction dbo.xfn_GetMyIndexFileRoot
--dropfunction dbo.xfn_GetKeyFromMyIndex
--dropfunction dbo.xfn_SetKeyForMyIndex
--dropassembly MyFullIndex
--go
CREATE ASSEMBLY MyFullIndex FROM 'd:/SQLCLR/MyFullIndex.dll'WITH PERMISSION_SET =UnSAFE;
--
go
--索引搜索
CREATE FUNCTION dbo.xfn_GetKeyFromMyIndex (@wordnvarchar(max),@indexName nvarchar(900))
RETURNS table(pk nvarchar(100))
AS EXTERNAL NAME MyFullIndex.UserDefinedFunctions.GetIndex
go
--索引建立
CREATE FUNCTION dbo.xfn_SetKeyForMyIndex (@pknvarchar(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 (@FileRootnvarchar(max))
RETURNS bit
AS EXTERNAL NAME MyFullIndex.UserDefinedFunctions.SetRoot
go
- SQL2005CLR函数扩展-山寨索引
- SQL2005CLR函数扩展-字符串函数
- SQL2005CLR函数扩展-字符串函数
- SQL2005CLR函数扩展-正则表达式
- SQL2005CLR函数扩展-数据导出
- SQL2005CLR函数扩展-繁简转换
- SQL2005CLR函数扩展-天气服务
- SQL2005CLR函数扩展-数据导出
- SQL2005CLR函数扩展-数据导出
- SQL2005CLR函数扩展-环比计算
- SQL2005CLR函数扩展-树的结构
- 序列类型函数2—切片索引的扩展
- Oracle扩展索引示例
- oracle索引-函数索引
- 扩展函数
- 扩展函数
- 函数扩展
- 函数索引
- 面向对象设计(OOD)思想(C#)
- 找最近点对问题-分治算法的应用
- A、B、H、S、N股 各是什么意思
- Binutils常用工具部件简介
- JList的问题
- SQL2005CLR函数扩展-山寨索引
- 什么是离散型制造
- GNU开发工具简介(一)
- 用Java的循环实现矩阵乘法代码
- DevExpress AspxGridView系列不能在中文目录下正确运行
- 在miniGUI中增加自定义控件
- [双语阅读]研究:越晚生育 全家越长寿
- 字符函数总结
- Java学习方法的一点个人见解-完整版