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

(0)

相关推荐

  • 在SQL SERVER中导致索引查找变成索引扫描的问题分析

    SQL Server 中什么情况会导致其执行计划从索引查找(Index Seek)变成索引扫描(Index Scan)呢? 下面从几个方面结合上下文具体场景做了下测试.总结.归纳. 1:隐式转换会导致执行计划从索引查找(Index Seek)变为索引扫描(Index Scan) Implicit Conversion will cause index scan instead of index seek. While implicit conversions occur in SQL Serve

  • SQL2005重新生成索引的的存储过程 sp_rebuild_index 原创

    公司运营着的网站,流量很大,网站是交互式的,经常在过了三四个月的时候索引生成的碎片就很多,由于很大一部分页面没有生成静态,这就导致网站在打开的速度上会变慢. 以前都是手工右击索引重新生成,但是索引太多,操作起来费时费力,索引在网上找了个存储过程,自己整理了一下,执行的时候只需要选择相应的数据库,运行exec sp_rebuild_index即可,如下. USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE

  • 详解sqlserver查询表索引

    SELECT   索引名称=a.name ,表名=c.name ,索引字段名=d.name ,索引字段位置=d.colid FROM sysindexes a JOIN sysindexkeys b ON a.id=b.id AND a.indid=b.indid JOIN sysobjects c ON b.id=c.id JOIN syscolumns d ON b.id=d.id AND b.colid=d.colid WHERE a.indid NOT IN(0,255) -- and

  • mssql 建立索引第1/2页

    表的索引与附在一本书后面的索引非常相似.它可以极大地提高查询的速度.对一个较大的表来说,通过加索引,一个通常要花费几个小时来完成的查询只要几分钟就可以完成.因此没有理由对需要频繁查询的表增加索引. 注意: 当你的内存容量或硬盘空间不足时,也许你不想给一个表增加索引.对于包含索引的数据库,SQL Sever需要一个可观的额外空间.例如,要建立一个聚簇索引,需要大约1.2倍于数据大小的空间.要看一看一个表的索引在数据库中所占的空间大小,你可以使用系统存储过程sp_spaceused,对象名指定为被索

  • Sql Server中的非聚集索引详细介

    非聚集索引,这个是大家都非常熟悉的一个东西,有时候我们由于业务原因,sql写的非常复杂,需要join很多张表,然后就泪流满面了...这时候就有DBA或者资深的开发给你看这个猥琐的sql,通过执行计划一分析...或许就看出了不该有的表扫描...万恶之源...然后给你在关键的字段加上非聚集索引后...才发现提速比阿斯顿马丁还要快...那么一个问题来了,为什么非聚集索引能提速这么快...怎么做到的???是不是非常的好奇??? 这篇我们来解开神秘面纱.  一:现象 先让我们一睹非聚集索引的真容,看看到底

  • 理解Sql Server中的聚集索引

    说到聚集索引,我想每个码农都明白,但是也有很多像我这样的猥程序员,只能用死记硬背来解决这个问题,什么表中只能建一个聚集索引,然后又扯到了目录查找来帮助读者记忆....问题就在这里,我们不是学文科,,,不需要去死记硬背,,,我们需要的就是能看到在眼里面的真实东西.....我们都喜欢聚集索引,因为它能够把无序的堆表记录变成有序,还玩起了B树...这样就把复杂度从N降低到了LogMN... 这样的话逻辑读,物理读就下来了.  一:现象 1:无索引的情况 还是老规矩,看个例子感受下,首先我有一个Prod

  • SQL2000 全文索引完全图解

    全文检索可以对varchar,text,image型字段进行检索,但一个表最多只能建一个全文索引SQL Server 2000 引入了对存储在 image 列中的这些类型的数据执行全文检索的能力.如果没有全文索引,对字符的模糊查询只能对基表进行全表扫描(或索引扫描),执行模糊查询都需要对全表扫描或索引扫描意味着消耗大量IO.如果模糊查询经常发生,会造成数据库性能恶化.本篇为简单起见,仅在varchar型字段上图文演示非常完整的而且是高效可行的全文索引创建及维护过程.1:在企业管理器中展开要建立全

  • 关于重新组织和重新生成索引sp_RefreshIndex的介绍

    开始: -------------------------------------------------------------------------------- 在上周,客户反映一个系统问题,当处理大量数据的时候,出现网络超时.后来,我们跟踪测试,发现是由于索引碎片多而引起的网络超时. 解决方法,自然是重新组织和重新生成索引.在这里,我写了一个存储过程sp_RefreshIndex来实现. 存储过程sp_RefreshIndex: 复制代码 代码如下: use mastergoif ob

  • MSSQL 大量数据时,建立索引或添加字段后保存更改提示超时的解决方法

    一般我们都喜欢用数据库管理器的UI来对数据表结构进行更改,然后自然而然地点"保存" 按钮进行保存,但数据量比较大的时候,用这招往往会出现"无法创建索引"IX_索引名". 超时时间已到.在操作完成之前超时时间已过或服务器未响应. "这种错误.一时不知所措,蜡人张的文章 复制代码 代码如下: 修改表属性后使用"索引/键"对话框为一个大型表(记录数13,239,473)创建索引,提示: - 无法创建索引"IX_TableN

  • MSSQL自动重建出现碎片的索引的方法分享

    1.索引碎片的产生? 由于在表里大量的插入.修改.删除操作而使索引页分裂.如果索引有了高的碎片,有两种情况,一种情况是扫描索引需要花费很多的时间,另一种情况是在查询的时候索引根本不使用索引,都会导致性能降低. 2.碎片类型分为: 2.1 内部破碎 由于索引页里的数据插入或修改操作而发生,以数据作为稀疏矩阵的形式的分布而结束,这将导致数据页的增加,从而增加查询时间. 2.2外部破碎 由于索引/数据页的数据插入或修改而发生,以页码分离和在文件系统里不连贯的新的索引页的分配而结束,数据库服务器不能利用

随机推荐