`
zheyiw
  • 浏览: 1017091 次
  • 性别: Icon_minigender_1
  • 来自: 广州
社区版块
存档分类
最新评论

SQL Server 索引中include的魅力(具有包含性列的索引)

    博客分类:
  • SQL
阅读更多
开文之前首先要讲讲几个概念

  【覆盖查询】
    当索引包含查询引用的所有列时,它通常称为“覆盖查询”。

  【索引覆盖】
     如果返回的数据列就包含于索引的键值中,或者包含于索引的键值+聚集索引的键值中,那么就不会发生Bookup Lookup,因为找到索引项,就已经找到所需的数据了,没有必要再到数据行去找了。这种情况,叫做索引覆盖;

  【复合索引】
    和复合索引相对的就是单一索引了,就是索引只包含一个字段,所以复合索引就是包含两个或者多个字段的索引;

  【非键列】
    键列就是在索引中所包含的列,当然非键列就是该索引之外的列了;


下面就开始今天的主题
在 SQL Server 2005 中,可以通过将非键列添加到非聚集索引的叶级别来扩展非聚集索引的功能。通过包含非键列,可以创建覆盖更多查询的非聚集索引。这是因为非键列具有下列优点:
    * 它们可以是不允许作为索引键列的数据类型。
    * 在计算索引键列数或索引键大小时,数据库引擎不考虑它们。
  当查询中的所有列都作为键列或非键列包含在索引中时,带有包含性非键列的索引可以显著提高查询性能。这样可以实现性能提升,因为查询优化器可以在索引中找到所有列值;不访问表或聚集索引数据,从而减少磁盘 I/O 操作。


键列存储在索引的所有级别中,而非键列仅存储在叶级别中。


使用包含性列以避免大小限制
  可以将非键列包含在非聚集索引中,以避免超过当前索引大小的限制(最大键列数为 16,最大索引键大小为 900 字节)。数据库引擎计算索引键列数或索引键大小时,不考虑非键列。
  例如,假设要为 AdventureWorks 示例数据库的 Document 表中的以下列建立索引:
     Title nvarchar(50)
     Revision nchar(5)
     FileName nvarchar(400)
  因为 nchar 和 nvarchar 数据类型的每个字符需要 2 个字节,所以包含这三列的索引将超出 900 字节的大小限制 10 个字节 (455 * 2)。使用 CREATE INDEX 语句的 INCLUDE 子句,可以将索引键定义为 (Title, Revision),将 FileName 定义为非键列。这样,索引键大小将为 110 个字节 (55 * 2),并且索引仍将包含所需的所有列。下面的语句就创建了这样的索引。


带有包含性列的索引准则
  设计带有包含性列的非聚集索引时,请考虑下列准则:
    * 在 CREATE INDEX 语句的 INCLUDE 子句中定义非键列。
    * 只能对表或索引视图的非聚集索引定义非键列。
    * 除 text、ntext 和 image 之外,允许所有数据类型。
    * 精确或不精确的确定性计算列都可以是包含性列。有关详细信息,请参阅为计算列创建索引。
    * 与键列一样,只要允许将计算列数据类型作为非键索引列,从 image、ntext 和 text 数据类型派生的计算列就可以作为非键(包含性)列。
    * 不能同时在 INCLUDE 列表和键列列表中指定列名。
    * INCLUDE 列表中的列名不能重复。


列大小准则
    * 必须至少定义一个键列。最大非键列数为 1023 列。也就是最大的表列数减 1。
    * 索引键列(不包括非键)必须遵守现有索引大小的限制(最大键列数为 16,总索引键大小为 900 字节)。
    * 所有非键列的总大小只受 INCLUDE 子句中所指定列的大小限制;例如,varchar(max) 列限制为 2 GB。


修改已定义为包含性列的表列时,要受下列限制:
    * 除非先删除索引,否则无法从表中删除非键列。
    * 除进行下列更改外,不能对非键列进行其他更改:
          o 将列的为空性从 NOT NULL 改为 NULL。
          o 增加 varchar、nvarchar 或 varbinary 列的长度。 
    * 这些列修改限制也适用于索引键列。


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


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


性能注意事项
  避免添加不必要的列。添加过多的索引列(键列或非键列)会对性能产生下列影响:
    * 一页上能容纳的索引行将更少。这样会使 I/O 增加并降低缓存效率。
    * 需要更多的磁盘空间来存储索引。特别是,将 varchar(max)、nvarchar(max)、varbinary(max) 或 xml 数据类型添加为非键索引列会显著增加磁盘空间要求。这是因为列值被复制到了索引叶级别。因此,它们既驻留在索引中,也驻留在基表中。
    * 索引维护可能会增加对基础表或索引视图执行修改、插入、更新或删除操作所需的时间。
  您应该确定修改数据时在查询性能上的提升是否超过了对性能的影响,以及是否需要额外的磁盘空间要求。有关评估查询性能的详细信息,请参阅查询优化。



【一个例子】
SELECT UserName,Password,RealName,Mobile,Age FROM bw_Users WHERE UserName = XXX AND Age = XX

说明:
1.这是一个我们很常见的查询语句,我们如何提高查询效率呢? 
2.首先我们来看看谓词,这条语句是通过UserName = XXX AND Age = XX作为条件的,那么我们就应该建立一个组合索引,也称为复合索引,注意索引中的键列的位置,先UserName后Age; 
3.其实上面那个是一个非聚集索引,那我们就可以把Password,RealName,Mobile这三列作为索引包含列; 
4.所以,最终就是建立一个以UserName 和 Age做为键列、Password,RealName,Mobile作为非键列的非聚集索引; 
5.通常来说我们系统的用户表并不是很大,所以这样的优化起不了很明显的效果,如果有兴趣的可以使用大表进行性能测试;


   【其它】
1.有一点我很奇怪,那就是为什么在修改表的时候,为什么【包含的列】是不可用的?只能通过命令来编写该类索引?
2.另外一点我想说,微软的MSDN的确是最好的学习工具,在网络上搜索出来的东西很多都是重复的,而且说的不全,不过能讲的比较简单、通俗而已。所以有空还是多看看MSDN吧。这句话是对自己说的。呵呵。 

分享到:
评论

相关推荐

    SQL server 2008 索引与视图

    详细解说了索引与视图的相关内容

    SqlServer 索引自动优化工具

    初始索引设计将JOIN和WHERE列放入索引键中,SELECT列则根据数据类型和修改频率决定是否包含在INCLUDE中。通过比较不同索引的覆盖情况,剔除重复和部分重叠的索引,并对差异较小的索引进行合并。此外,还需要考虑与...

    SQL SERVER2005索引

    SQL Server 2005是微软推出的一款关系型数据库管理系统,它在数据管理与数据分析方面具有强大的功能。索引作为数据库中的重要组成部分,对于提升查询性能、优化数据访问有着至关重要的作用。本篇将深入探讨SQL ...

    浅谈SQL Server 2012列存储索引技术.pdf

    SQL Server 2012 引入了一种创新的索引技术——列存储索引,它显著提升了数据仓库的查询性能,尤其是在决策支持类查询中,可以实现大约10倍的性能提升。列存储索引的核心理念在于将数据按照列而不是行进行组织和存储...

    SQL SERVER 2008 开发系列

    除了筛选索引之外,SQL Server 2008还引入了索引包含列的概念。包含列是非键列,即不在索引键中出现的列,但这些列可以出现在索引的叶子级别,以帮助提高查询性能。 **1. 包含列的特点** - 包含列可以是任何数据...

    sqlserver sql语法大全

    - `INCLUDE`:指定非键列用于包含索引。 #### 创建视图 创建视图提供数据的抽象层,使用`CREATE VIEW`命令。 **语法示例:** ```sql CREATE VIEW [视图名称] AS SELECT [列名称列表] FROM [表名称] WHERE [条件]; ...

    SQL Server上的一个奇怪的Deadlock及其分析方法

    Deadlock是SQL Server中的一种常见问题,它会导致事务无法继续执行,影响系统的性能和可靠性。了解Deadlock的产生原因和分析方法是非常重要的。本文将详细介绍SQL Server上的一个奇怪的Deadlock及其分析方法。 ...

    VC连接 SQL Server

    本文将详细介绍如何在Visual C++(VC)中建立与SQL Server的连接,包括所需的库、API函数、步骤以及可能遇到的问题。 首先,我们需要知道的是,VC++通过ODBC(Open Database Connectivity)或OLE DB接口来连接SQL ...

    sqlserver索引的原理及索引建立的注意事项小结

    4. 使用覆盖索引,即索引包含所有查询所需列,减少对原始表的访问,提高查询速度。可以通过在CREATE INDEX语句中使用INCLUDE子句来指定覆盖索引。 5. 创建适量的索引,过多的索引会增加写操作的开销,因为索引需要...

    查看Sql中自动创建的索引

    在SQL Server中,系统可能会根据某些操作自动为表创建索引,这类索引通常带有前缀`_wa_sys`,用以区分手动创建的索引。自动创建的索引主要出现在以下几种情况: 1. **覆盖索引提示**:当查询中使用了`INCLUDE`或`...

    Jsp+Sql Server的BBS小系统

    此外,SQL Server还提供了事务处理、索引、视图、存储过程等功能,以提升数据库的性能和安全性。 **BBS系统功能** 1. **用户注册与登录**:用户可以注册新账号,填写用户名、密码等信息,通过邮箱或手机验证进行...

    Sql Server查询性能优化之不可小觑的书签查找介绍

    优化这种查询的方法可能是创建一个包含所有需要列的非聚集索引,或者在索引定义中使用INCLUDE子句添加额外的列。 总的来说,理解书签查找并学会避免它对于提升SQL Server的查询性能至关重要。通过合理设计索引、...

    xml在sql_server_2008中的应用

    在SQL Server 2008中,XML数据类型具有一定的限制,包括但不限于: - XML数据类型的实例所占据的存储空间不能超过2GB。 - 不能用作`sql_variant`实例的子类型。 - 不支持转换或转换为`text`或`ntext`类型。可以考虑...

    详解SQL Server的聚焦过滤索引

    覆盖索引包含了查询所需的所有列,从而避免了回表操作,进一步提高了性能。例如: ```sql CREATE NONCLUSTERED INDEX idx_FilteredInclude ON Customers(CustomerID) WHERE CustomerStatus = 'Active' INCLUDE ...

    jsp电子商务网站修改版SQLserver版

    【标题解析】:“jsp电子商务网站修改版SQLserver版”这个标题揭示了这是一个基于JSP(Java Server Pages)技术构建的电子商务网站的修改版,且采用了SQL Server作为后端数据库管理系统。这表明该网站可能经过了功能...

    jsp课程设计学生成绩管理系统(sqlserver2000)

    【标题】"jsp课程设计学生成绩管理系统(sqlserver2000)"涉及的核心技术主要包括JSP(Java Server Pages)和SQLServer2000,这是一个基于Web的学生成绩管理系统的实现。JSP是一种动态网页开发技术,用于创建交互式...

    对索引Include子句的深入分析(已校对)1

    标题中的“对索引Include子句的深入分析”是指探讨PostgreSQL数据库中创建索引时的一个特性,即在`CREATE INDEX`语句中使用`INCLUDE`子句。这个子句允许用户将某些列添加到索引的叶子节点,但不包含在B-Tree部分,以...

Global site tag (gtag.js) - Google Analytics