在SQL Server索引中搜索单词（sqlserver使用索引查询）

25-02-25 12

本文将为您提供关于在SQLServer索引中搜索单词的详细介绍，我们还将为您解释sqlserver使用索引查询的相关知识，同时，我们还将为您提供关于ASP.NETC#在SQLServer数据库表中搜索

本文将为您提供关于在SQL Server索引中搜索单词的详细介绍，我们还将为您解释sqlserver使用索引查询的相关知识，同时，我们还将为您提供关于ASP.NET C#在SQL Server数据库表中搜索、SQL Server索引--（包含列和覆盖索引）、SQL Server索引原理解析、SQL Server索引哪些群集？的实用信息。

本文目录一览：

在SQL Server索引中搜索单词（sqlserver使用索引查询）
ASP.NET C#在SQL Server数据库表中搜索
SQL Server索引--（包含列和覆盖索引）
SQL Server索引原理解析
SQL Server索引哪些群集？

在SQL Server索引中搜索单词（sqlserver使用索引查询）

我需要在全文搜索和索引搜索之间进行操作：
我想在表的一列中搜索文本（如果很重要的话，该列上也可能会有一个索引）。

问题是，我想搜索列中的单词，但是我不想匹配部分。

例如，我的列中可能包含公司名称：
Mighty Muck Miller and Partners Inc.
Boy＆Butter Breakfast company

现在，如果我搜索“ Miller ”，我想找到第一行。但是，如果我搜索“ iller ”，我就不想找到它，因为没有以“
iller”开头的单词。但是，搜索“ Break ”应找到“ Boy＆Butter Breakfast company ”，因为一个单词的开头是“
Break ”。

所以，如果我尝试使用

WHERE BusinessName LIKE %Break%

它将找到太多点击。

有什么方法可以搜索由空格 或其他定界符分隔的 单词？

（LINQ最好，普通的SQL也可以）

重要提示： 到目前为止，空格不是唯一的分隔符！斜线，冒号，圆点，所有非字母数字字符都应考虑在内才能起作用！

ASP.NET C#在SQL Server数据库表中搜索

我正在构建一个门户网站,它不是一个博客引擎,但与一个非常类似.在sql Server中,我有一个包含表的数据库,该表是“posts”的基础.此帖子表包括以下列：

> ID
>作者
>标签
>标题
> Markdown发布内容

这是我第一次构建这样的门户网站,我想在这些行上实现某种ASP.NET搜索,最好使用除ID之外的所有属性(列).此外,从长远来看,我正在考虑是否有可能对这些帖子进行搜索和评论,这些帖子将存储在不同的表格中.

是否有任何开源实现或在线示例代码来完成此搜索？如果没有,我该如何开始？您是否可以指向一些有关如何使用ASP.NET和C#完成此操作的示例代码的教程？此外,Google(或其他公司)是否为此创建了任何内容？

我希望我的问题不是太广泛或含糊不清.提前致谢！

解决方法

你在使用sql Server 2008吗？如果是这样,您可以利用内置的 full-text search features.然后就像将用户的(已清理的)输入传递给SQL查询一样简单.

例如,这是一个查询,它将在Author,Title和PostContent字段中搜索用户输入的文本.

SELECT Author,Title FROM Posts
WHERE CONTAINS((Author,Title,PostContent),@userInput);

sql Server 2008支持different search methods too,如简单令牌,加权字值,同义词,邻近和前缀搜索……它非常棒.

SQL Server索引--（包含列和覆盖索引）

一、覆盖索引

如果所构建的查询只需访问索引中的数据即可满足查询的需求，那便无需访问数据表。 一个可以满足查询全部需求的索引被称为“覆盖索引”（covering index）。

可以在一个给定的查询中使用多个索引。如果两个索引中至少有一列是相同的，则SQL Server能将两个索引联在一起以满足查询的需求。

数据库中有索引是一件好事，而覆盖索引更为查询提供了极好的值。但是，也受限制于每个索引至多16列、900字节的约束。该限制排除了大数据类型列使用覆盖索引的可能性，否则即使针对这类值得查询也可以不从基础表中抽取数据。

当查询中的所有列都作为键列或非键列包含在索引中时，带有包含性非键列的索引可以显著提高查询性能。这样可以实现性能提升，因为查询优化器可以在索引中找到所有列值；不访问表或聚集索引数据，从而减少磁盘 I/O 操作。当索引包含查询引用的所有列时，它通常称为“覆盖查询”。

二、包含列

SQL Server 2005引入了一项新的索引特性，即所谓的包含列(included column)。包含列仅在叶节点层级上成为索引的一部分。来自包含列的值不会出现在索引的根节点或中间级内，且不计入900字节的限制。 您可以通过将非键列添加到非聚集索引的叶级，扩展非聚集索引的功能。通过包含非键列，可以创建覆盖更多查询的非聚集索引。这是因为非键列具有下列优点：

1.它们可以是不允许作为索引键列的数据类型。

2.在计算索引键列数或索引键大小时，数据库引擎不考虑它们。

三、列大小准则

1.必须至少定义一个键列。最大非键列数为 1023 列。也就是最大的表列数减 1。

2.索引键列（不包括非键）必须遵守现有索引大小的限制（最大键列数为 16，总索引键大小为 900 字节）。

3.所有非键列的总大小只受 INCLUDE 子句中所指定列的大小限制；例如，varchar(max) 列限制为 2 GB。

四、设计建议

重新设计索引键大小较大的非聚集索引，以便只有用于搜索和查找的列为键列。将覆盖查询的所有其他列设置为包含性非键列。这样，将具有覆盖查询所需的所有列，但索引键本身较小，而且效率高。例如，假设要设计覆盖下列查询的索引。

1 USE AdventureWorks;
2 GO
3 SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
4 FROM Person.Address
5 WHERE PostalCode BETWEEN N''98000'' and N''99999'';

覆盖查询必须在索引中定义每列。尽管可以将所有列定义为键列，但键大小为 334 字节（ AddressLine1 nvarchar(60), AddressLine2 nvarchar(60), City nvarchar(30), StateProvinceID int, PostalCode nvarchar(15) ）。因为实际上用作搜索条件的唯一列是 PostalCode 列（长度为 30 字节），所以更好的索引设计应该将 PostalCode 定义为键列并包含作为非键列的所有其他列。下面的语句创建了一个覆盖查询的带有包含列的索引。

1 USE AdventureWorks;
2 GO
3 CREATE INDEX IX_Address_PostalCode
4 ON Person.Address (PostalCode)
5 INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);

SQL Server索引原理解析

此文是我之前的笔记整理而来，以索引为入口进行探讨相关数据库知识（又做了修改以让人更好消化）。SQL Server接触不久的朋友可以只看以下蓝色字体字，简单有用节省时间；如果是数据库基础不错的朋友，可以全看，欢迎探讨。

全文章节：

1.聚集索引和非聚集索引

2.索引的结构

3.索引包含列和书签查找

1.聚集索引和非聚集索引

索引分为聚集索引和非聚集索引

1.1 聚集索引

表的数据是存储在数据页中（数据页的PageType标记为1），SqlServer一页是8k，存满一页就开辟下一页存储。如果表有聚集索引，那么一笔一笔物理数据就是按聚集索引字段的大小升/降排序存储在页中。当对聚集索引字段更新或中间插入/删除数据时，都会导致表数据移动（造成性能一定影响），因为它要保持升/降排序。

注意，主键只是默认是聚集索引，它也可以设置为非聚集索引，也可以在非主键字段上设置为聚集索引，全表只能有一个聚集索引。

一个优秀的聚集索引字段一般包含以下4个特性：

(A).自增长

总是在末尾增加记录，减少分页和索引碎片。

(B).不被更改

减少数据移动。

(C).唯一性

唯一性是任何索引最理想的特性，可以明确索引键值在排序中的位置。

更重要的是，索引键指唯一的话，它在每条记录里才可以正确指向源数据行RID。如果聚集索引键值不唯一，SqlServer就需要内部生成uniquifier 列组合当作聚集键保证“键值”唯一性；如果非聚集索引键值不唯一，就会增加RID列（聚集索引键或者堆表中的行指针）保证“键值”唯一性。

思考（可略过）：索引“键值”在非叶子节点也有保证唯一性，原因应该是为了明确索引记录在非叶子节点中的位置。比如有个非聚集索引字段Name2，表中有很多Name2=''a''的记录，导致Name2=''a''在非叶子节点上有多条索引记录（节点），这时候再insert一笔Name2=‘a''的记录时，就可以根据非叶子节点的RID和新增记录的RID很快确定要insert到哪个索引记录（节点）上，如果没有非叶子节点的RID，那得遍历到所有Name2=''a''的叶子节点才能确定位置。另外，当我们select * from Table1 where Name2<=''a''时，返回的数据是按非聚集索引Name2和RID排序的，很好理解返回的数据就是按这边索引存储的顺序排序的。这是这条sql查询时有用到Name2索引的结果，如果数据库查询计划因“临界点”问题选择直接表数据扫描，那返回的数据默认就是按表数据的顺序排序的。

为了“键值”唯一性，对于聚集索引，uniquifier 列只在索引值重复时增加。对于非聚集索引，如果创建索引时没定义唯一，RID会在所有记录增加，就算索引值是唯一的；如果创建索引时定义唯一，RID只在叶子层增加，用于查找源数据行，即书签查找操作。

(D).字段长度小

聚集索引键长度越小，一页索引页就可以容纳更多索引记录，进而减少索引B树结构的深度。例如，一个百万记录的表有一个int聚集索引，可能只需要3层的B树结构。如果把聚集索引定义在更宽的列（比如uniqueidentifier列需要16 字节），那么索引的深度会增加到4层。任何聚集索引查找需要4个I/O操作（确切的说是4个逻辑读），原先只要3个I/O操作。

同样，非聚集索引里会包含聚集索引键值，聚集索引键长度越小非聚集索引记录也就越小，一页索引页就可以容纳更多索引记录。

1.2 非聚集索引

也是存储在页中（PageType标记为2的页，叫索引页)。比如表T建立了一个非聚集索引Index_A，那么表T有100条数据的话，那么索引Index_A也就有100条数据（准确的说是100条叶子节点数据，索引是B树结构，如果树的高度大于0，那么就有根节点页或中间节点页数据，这时索引数据就超过100条），如果表T还有非聚集索引Index_B，那么Index_B也是至少100条数据，所以索引建越多开销越大。

更新索引字段、插入一条数据、删除一条数据都会造成索引的维护从而造成性能的一定影响。在不同情况下，性能影响是不同的。比如当你有一个聚集索引，插入的数据又都是在末尾，这样几乎是不会造成数据移动，影响较小；如果插入的数据在中间位置，一般会导致数据移动，而且可能产生分页和页碎片，影响就会稍大一点（如果插入到的中间页有足够的剩余空间容纳插入的数据，而且位置是在页末，也是不会造成数据移动）

2.索引的结构

都说SqlServer的索引是B树结构（这边假定你对B树结构有一定了解），那它到底长什么个模样呢，可以用Sql语句来查看它的逻辑呈现。

新建查询执行语法： DBCC IND(Test,OrderBo,-1) --其中Test库的OrderBo表有1万笔数据，有聚集索引Id主键字段

（不妨自己动手建个表，有聚集索引字段，插入1万表数据，然后执行这个语法看看，会收获很多，百闻不如一见）

执行结果：

如上图，看到一个IndexLevel=2的索引页2112（这边它就是B树的根节点，IndexLevel最大的就是根节点，往下就是子级、子子级...只有一个根页作为B树结构的访问入口点），说明一定还有IndexLevel=1的索引页和IndexLevel=0的叶子页。由于这边是聚集索引，因此当IndexLevel=0的叶子页就是数据页，存储的是一笔一笔的物理数据。如上图也可以看到，IndexLevel=0的行的PageType等于1，就是代表数据页，上面1.1章节讲到聚集索引时，也有提到PageType=1；而如果是非聚集索引，IndexLevel=0的叶子页，PageType是等于 2，仍然是索引页。

同样，我们用Sql命令DBCC PAGE看一看

-- DBCC TRACEON(3604,-1) 
DBCC PAGE(Test,1,2112,3) 
 --根节点2112，可以查出它的两个子节点2280和2448，然后对这两个子节点再作DBCC PAGE查询
DBCC PAGE(Test,1,2280,3) 
DBCC PAGE(Test,1,2448,3)

如上图，IndexLevel=2的2112页有两个IndexLevel=1的子节点2280和2448，子节点下又有子节点，每个节点负责不同的索引键值的区间（即上图的“Id(key)”栏位，第一行值是Null，表示最小值或倒序时的最大值）。 这样的层级关系是不是就是一棵B树结构，其中IndexLevel其实就是B树结构中的高度Height。

SqlServer在索引中查找某一笔记录时，是从根节点往下找到叶子节点，因为所有数据地址都有存在叶子节点，这其实是B+树的特点之一（B树特点是如果查找的值在非叶子节点就找到，则就能直接返回，显然SqlServer不是这么做，要验证这一点你可以set statistics io on把统计开起来，然后select看下逻辑读的次数）。

既然一定会找到叶子节点，那么索引包含列只要在叶子节点记录就可以了，即非叶子节点没有记录包含列，“索引包含列”见下文第3章节。

B+树这个特点（所有数据地址都有存在叶子节点）也利于between value1 and value2 区间查询，只要找到value1和value2（在叶子节点），然后把中间串起来就是要的结果了。

SqlServer索引结构更像是B+树，最终是B树和B+树的混合版，数据结构都是人定的，不一定就是纯粹的B树或者单纯的B+树。

3.索引包含列和书签查找

谈到索引，这边再讲一个SqlServer2005开始增加的“索引包含列”功能，很实用。

比如，在大报表查询数据时，where条件用到索引字段Name2，但是要select的字段是Name1，这时候可以使用“索引包含列”把Name1包含在索引字段Name2中，大大提高查询性能。

语法： Create [UNIQUE] Nonclustered/Clustered Index IndexName On dbo.Table1(Name2) Include(Name1);

接下来分析为什么索引包含列可以大大提高性能。仍然使用DBCC PAGE命令，查看一个非聚集索引并有包含列的索引数据情况：

由上图可知，包含列Name1也存储在索引数据中。因此，当数据库用索引字段Name2定位到要查找的某一行时，就可以直接把Name1的值返回了，而不用再根据RID（上图是【HEAP RID(Key)】列）定位到数据页中去取值，即减少了书签查找。当查询只返回一条数据，只有一次书签查找时当然没什么，如果查询返回的数据很大，每一笔都要去数据页找数据取出来，1000笔就是1000次书签查找，可想而知性能消耗很大，这时候“索引包含列”价值就大大体现出来了。

关于一次书签查找，表有聚集索引（比如Id）时就是类似执行了一次 select Name1 from Table1 where Id=1 ，利用聚集索引键Id查找（查找方式就是索引Id的B树结构查找），而如果表没有聚集索引，则是根据数据行指针（由“文件号2byte：页号4byte：槽号2byte”组成）查找。聚集索引键和行指针一般统称为RID（Row ID）指针。从这里我们可以想到，如果你的表没有很好的聚集索引字段，建议自增长的Id字段做聚集索引主键（冗余出Id字段也行），它符合自增长、不被更改、唯一性、长度小的特性，是聚集索引的很好选择。

自增长Id绝大部分情况下是适用的，特殊的情况看具体需求而定吧。还有自增长Id要考虑一个缺陷，当对表大数据量的并发insert记录时，可以想象每个线程都是要insert到末尾那个页，就会发生竞争和等待。解决这种情况你可以用uniqueidentifier类型字段（16字节，我是不建议使用）或者哈希分区（就是一个表分成多个表，大数据处理中分库分表是正常的）等。但是我建议先优化你的insert效率（insert性能本身是很快的），测试每秒并发insert数是否满足生产环境，以保留简单稳定高效的自增长Id作法。

自增长Id不一定就是用数据库提供的自增长，你也可以自己写算法生成一个并发情况下也能唯一的Id（这时候一般长度是bitint，8字节整形），这种情况适合场景是分布式数据库中主从复制时Id栏位是要求一定不能出错的情况（主从复制的一般模式下，主库的Id是按主库增长，从库Id也是按从库自己的增长，如果遇到死锁等原因导致主从复制不同步时，那从库的Id就和主库的Id自增长就对不上号了）。如果自增长Id是冗余出的主键，那主从库Id对不上号也就无影响。

另外，上图最后一列【Row Size】还告诉我们，索引列或索引包含列的size不要太长，否则一页容不了几笔记录，这样大大增加了索引页数量，而且索引数据所占的空间也大大增加了。

SQL Server索引哪些群集？

我在某些表上有许多索引，它们都相似，并且我想知道聚集索引是否在正确的列上。以下是两个最活跃索引的统计信息：

Nonclustered
I3_Identity (bigint)
rows: 193,781
pages: 3821
MB: 29.85
user seeks: 463,355
user_scans: 784
user_lookups: 0
updates: 256,516

Clustered Primary Key
I3_RowId (varchar(80))
rows: 193,781
pages: 24,289
MB: 189.76
user_seeks: 2,473,413
user_scans: 958
user_lookups: 463,693
updates: 2,669,261

如您所见，经常搜索PK，但是对i3_identity列的所有搜索也正在对此PK进行关键查找，因此，我是否真的从I3_Identity的索引中受益匪浅？我应该改为使用I3_Identity作为群集吗？由于此表结构在我工作的地方重复了大约10000次，因此可能会产生巨大的影响，因此，我们将不胜感激。

今天关于在SQL Server索引中搜索单词和sqlserver使用索引查询的讲解已经结束，谢谢您的阅读，如果想了解更多关于ASP.NET C#在SQL Server数据库表中搜索、SQL Server索引--（包含列和覆盖索引）、SQL Server索引原理解析、SQL Server索引哪些群集？的相关知识，请在本站搜索。

本文标签：