侯体宗的博客
  • 首页
  • Hyperf版
  • beego仿版
  • 人生(杂谈)
  • 技术
  • 关于我
  • 更多分类
    • 文件下载
    • 文字修仙
    • 中国象棋ai
    • 群聊
    • 九宫格抽奖
    • 拼图
    • 消消乐
    • 相册

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

数据库  /  管理员 发布于 5年前   242

本文只是一个山寨试验品,思路仅供参考.
--------------------------------------------------------------------------------
原理介绍:
索引建立
目录结构划分方案也只是很简易的实现了一下,通过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


  • 上一条:
    SQL2005CLR函数扩展-繁简转换的实现代码
    下一条:
    基于SQL2005 SQL2008 表结构信息查询升级版的详解(含外键信息)
  • 昵称:

    邮箱:

    0条评论 (评论内容有缓存机制,请悉知!)
    最新最热
    • 分类目录
    • 人生(杂谈)
    • 技术
    • linux
    • Java
    • php
    • 框架(架构)
    • 前端
    • ThinkPHP
    • 数据库
    • 微信(小程序)
    • Laravel
    • Redis
    • Docker
    • Go
    • swoole
    • Windows
    • Python
    • 苹果(mac/ios)
    • 相关文章
    • 分库分表的目的、优缺点及具体实现方式介绍(0个评论)
    • DevDB - 在 VS 代码中直接访问数据库(0个评论)
    • 在ubuntu系统中实现mysql数据存储目录迁移流程步骤(0个评论)
    • 在mysql中使用存储过程批量新增测试数据流程步骤(0个评论)
    • php+mysql数据库批量根据条件快速更新、连表更新sql实现(0个评论)
    • 近期文章
    • 智能合约Solidity学习CryptoZombie第四课:僵尸作战系统(0个评论)
    • 智能合约Solidity学习CryptoZombie第三课:组建僵尸军队(高级Solidity理论)(0个评论)
    • 智能合约Solidity学习CryptoZombie第二课:让你的僵尸猎食(0个评论)
    • 智能合约Solidity学习CryptoZombie第一课:生成一只你的僵尸(0个评论)
    • 在go中实现一个常用的先进先出的缓存淘汰算法示例代码(0个评论)
    • 在go+gin中使用"github.com/skip2/go-qrcode"实现url转二维码功能(0个评论)
    • 在go语言中使用api.geonames.org接口实现根据国际邮政编码获取地址信息功能(1个评论)
    • 在go语言中使用github.com/signintech/gopdf实现生成pdf分页文件功能(0个评论)
    • gmail发邮件报错:534 5.7.9 Application-specific password required...解决方案(0个评论)
    • 欧盟关于强迫劳动的规定的官方举报渠道及官方举报网站(0个评论)
    • 近期评论
    • 122 在

      学历:一种延缓就业设计,生活需求下的权衡之选中评论 工作几年后,报名考研了,到现在还没认真学习备考,迷茫中。作为一名北漂互联网打工人..
    • 123 在

      Clash for Windows作者删库跑路了,github已404中评论 按理说只要你在国内,所有的流量进出都在监控范围内,不管你怎么隐藏也没用,想搞你分..
    • 原梓番博客 在

      在Laravel框架中使用模型Model分表最简单的方法中评论 好久好久都没看友情链接申请了,今天刚看,已经添加。..
    • 博主 在

      佛跳墙vpn软件不会用?上不了网?佛跳墙vpn常见问题以及解决办法中评论 @1111老铁这个不行了,可以看看近期评论的其他文章..
    • 1111 在

      佛跳墙vpn软件不会用?上不了网?佛跳墙vpn常见问题以及解决办法中评论 网站不能打开,博主百忙中能否发个APP下载链接,佛跳墙或极光..
    • 2017-06
    • 2017-08
    • 2017-09
    • 2017-10
    • 2017-11
    • 2018-01
    • 2018-05
    • 2018-10
    • 2018-11
    • 2020-02
    • 2020-03
    • 2020-04
    • 2020-05
    • 2020-06
    • 2020-07
    • 2020-08
    • 2020-09
    • 2021-02
    • 2021-04
    • 2021-07
    • 2021-08
    • 2021-11
    • 2021-12
    • 2022-02
    • 2022-03
    • 2022-05
    • 2022-06
    • 2022-07
    • 2022-08
    • 2022-09
    • 2022-10
    • 2022-11
    • 2022-12
    • 2023-01
    • 2023-03
    • 2023-04
    • 2023-05
    • 2023-07
    • 2023-08
    • 2023-10
    • 2023-11
    • 2023-12
    • 2024-01
    • 2024-03
    Top

    Copyright·© 2019 侯体宗版权所有· 粤ICP备20027696号 PHP交流群

    侯体宗的博客