精华内容
下载资源
问答
  • 索引键列和包含性列

    2016-06-21 14:03:00
    2、sql语句是where先执行,然后再执行order by,所以我们在建非聚集索引时要注意顺序并且where与order by里面的列都要在索引键列里面。select部份可以放在包含性列里面,但请注意索引大小的空间问题。 3、order by...

    1、主键必须是唯一性的,不一定就是聚集索引,我们在创建主键时默认是设主键为聚集索引。可通过手动删除后重新建聚集索引。

    2、sql语句是where先执行,然后再执行order by,所以我们在建非聚集索引时要注意顺序并且where与order by里面的列都要在索引键列里面。select部份可以放在包含性列里面,但请注意索引大小的空间问题。

    3、order by里面的升序和降序问题一定要和索引键列里面的一样。

     

    例:select id,title from table1 where classid=123 order by created DESC

    情况一

    操作:建非聚集索引IX_A->索引键列为classid(升序降序无所谓)、created(一定要降序) 

    注意索引键列中两个字段的先后顺序,两个键列缺一不可。

    执行:1.IX_A索引查找出ID,2.根据ID通过 键查找 找出title->返回结果

     

    情况二

    操作:建非聚集索引IX_B->索引键列为classid(升序降序无所谓)、created(一定要降序) ->添加包含性列id,title

    注意索引键列中两个字段的先后顺序,两个键列缺一不可。

    执行:1.IX_B索引查找->返回结果

     

    以上两个方法如果created的排序弄错了,还将多一步,即:

    情况一:1.IX_A索引查找出ID,2.根据ID通过 键查找 找出title,3.排序->返回结果

    情况二:1.IX_B索引查找,2.排序->返回结果

    转载于:https://www.cnblogs.com/zhuangke668/p/5603431.html

    展开全文
  • 具有包含性列索引

    2019-05-17 15:42:20
    在 SQL Server 2005 中,可以通过将非键列添加到非聚集索引的叶级别来扩展非聚集索引的...当查询中的所有列都作为键列或非键列包含索引中时,带有包含非键列的索引可以显著提高查询性能。这样可以实现性能提升...

    在 SQL Server 2005 中,可以通过将非键列添加到非聚集索引的叶级别来扩展非聚集索引的功能。通过包含非键列,可以创建覆盖更多查询的非聚集索引。这是因为非键列具有下列优点:

    • 它们可以是不允许作为索引键列的数据类型。
    • 在计算索引键列数或索引键大小时,数据库引擎不考虑它们。

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

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

    键列存储在索引的所有级别中,而非键列仅存储在叶级别中。有关索引级别的详细信息,请参阅表组织和索引组织。

    使用包含性列以避免大小限制

    可以将非键列包含在非聚集索引中,以避免超过当前索引大小的限制(最大键列数为 16,最大索引键大小为 900 字节)。数据库引擎计算索引键列数或索引键大小时,不考虑非键列。

    例如,假设要为 AdventureWorks 示例数据库的 Document 表中的以下列建立索引:

         Title nvarchar(50)
    
         Revision nchar(5)
    
         FileName nvarchar(400)
    

    因为 ncharnvarchar 数据类型的每个字符需要 2 个字节,所以包含这三列的索引将超出 900 字节的大小限制 10 个字节 (455 * 2)。使用CREATE INDEX 语句的 INCLUDE 子句,可以将索引键定义为 (Title, Revision),将 FileName 定义为非键列。这样,索引键大小将为 110 个字节 (55 * 2),并且索引仍将包含所需的所有列。下面的语句就创建了这样的索引。

    USE AdventureWorks;
    GO
    CREATE INDEX IX_Document_Title       
    ON Production.Document (Title, Revision)       
    INCLUDE (FileName);       
    

    带有包含性列的索引准则

    设计带有包含性列的非聚集索引时,请考虑下列准则:

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

    列大小准则

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

    列修改准则

    修改已定义为包含性列的表列时,要受下列限制:

    • 除非先删除索引,否则无法从表中删除非键列。
    • 除进行下列更改外,不能对非键列进行其他更改:
      • 将列的为空性从 NOT NULL 改为 NULL
      • 增加 varcharnvarcharvarbinary 列的长度。

    注意:这些列修改限制也适用于索引键列。

    设计建议

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

    例如,假设要设计覆盖下列查询的索引。

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

    若要覆盖查询,必须在索引中定义每列。尽管可以将所有列定义为键列,但键大小为 334 字节。因为实际上用作搜索条件的唯一列是 PostalCode 列(长度为 30 字节),所以更好的索引设计应该将 PostalCode 定义为键列并包含作为非键列的所有其他列。

    下面的语句创建了一个覆盖查询的带有包含性列的索引。

    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数据类型添加为非键索引列会显著增加磁盘空间要求。这是因为列值被复制到了索引叶级别。因此,它们既驻留在索引中,也驻留在基表中。
    • 索引维护可能会增加对基础表或索引视图执行修改、插入、更新或删除操作所需的时间。

    您应该确定修改数据时在查询性能上的提升是否超过了对性能的影响,以及是否需要额外的磁盘空间要求。有关评估查询性能的详细信息,请参阅查询优化。

    展开全文
  • SQL Server 索引中include的魅力(具有包含性列索引) 2010-01-11 20:44 by 听风吹雨, 33101 阅读, 25 评论, 收藏, 编辑 开文之前首先要讲讲几个概念  【覆盖查询】  当索引包含查询引用的所...

        转载地址:http://www.cnblogs.com/gaizai/archive/2010/01/11/1644358.html

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

    2010-01-11 20:44 by 听风吹雨, 33101 阅读, 25 评论, 收藏, 编辑

    开文之前首先要讲讲几个概念

      【覆盖查询】

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

     

      【索引覆盖】

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

     

      【复合索引】

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

      

      【非键列】

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

     

    下面就开始今天的主题

      【摘要1】

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

      说明:第一:只能是针对非聚集索引;第二:比起复合索引是有性能上的提升的,因为索引的大小变小了;

     

      【摘要2】

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

      说明:这就表现为包含与不包含的关系了。有关索引级别的详细信息,请参阅表组织和索引组织

     

      【摘要3】

    复制代码

      使用包含性列以避免大小限制
      可以将非键列包含在非聚集索引中,以避免超过当前索引大小的限制(最大键列数为 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),并且索引仍将包含所需的所有列。下面的语句就创建了这样的索引。

    复制代码

      说明:当你把一个nvarchar(500)的字段设置为主键的时候,你就可以看到不能超出900字节的提示了。一般来说我们是不太会做这些操作的,所以那个错误提示也是不常见的,也许你可能还见过。

      一个数据页的大小才8k,所以我们合理的设置每个字段的大小,不要浪费太多的空间,这样对查询也是有好处的,这个include就比较好的的解决了索引和空间的问题,虽然那些include的数据也会占用空间。

      虽然可以设置include,但是也尽量不要使用太多的字段作为索引包含的非键列。

     

      【摘要4】

    复制代码

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

    复制代码

      说明:include不能使用在聚集索引中。后面的两点,这个在实际中很难想象会有这样的需求要把重复列放到一个索引中。如果有朋友遇到过这样的需求可以告知一些,不胜感激。那如果有是否可以通过不同的列名(其实保存是同样的值)来解决这个问题呢??

     

      【摘要5】

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

      说明:varchar(max)这样的定义是在2005之后才有的,所以这些数值也是对2005后的版本才生效的。

      最大的表列数为:1024

      最大非键列数为:1023

     

      【摘要6】

    复制代码

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

    复制代码

      说明:这些细小的东西一直没有注意过。所以要记录下来,用来“防身”,呵呵。

     

      【摘要7】

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

     

      说明:也就是说把常用的where后面的条件查询的字段作为索引的键列,而需要返回的字段就作为索引包含的非键列

      如果where的是两个或两个以上的谓词的话,这个索引就可以创建为复合索引了。以前天真的认为要返回的字段只能通过在复合索引中入这些字段,不管它是否会用来做谓词。看到这篇文章,才有了豁然开朗的感觉。

     

      【摘要8】

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

      说明:这个是使用include的语法,在表的设计中的索引设计中是没有办法选择的;

     

     

      【摘要9】

    复制代码

      性能注意事项
      避免添加不必要的列。添加过多的索引列(键列或非键列)会对性能产生下列影响:
        
    * 一页上能容纳的索引行将更少。这样会使 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吧。这句话是对自己说的。呵呵。
    展开全文
  • MS SqlServer提供了两种索引:聚集索引和非聚集索引; 聚集索引是将数据按照索引的顺序存放 非聚集索引是将索引和数据分离存放,通过指针将二者联系到一起。 因为两种索引对比: 使用聚集索引查询效率更高,但...

    1.索引分类

    MS SqlServer提供了两种索引:聚集索引和非聚集索引;

    • 聚集索引是将数据按照索引的顺序存放
    • 非聚集索引是将索引和数据分离存放,通过指针将二者联系到一起。

    因为两种索引对比:

    • 使用聚集索引查询效率更高,但对表的更新效率影响较大,因此适用于更新读写比较大的表,例如商品详情(创建后很少改动,查看的频率却相对较高)
    • 使用非聚集索引则查询效率较低,对表的更新效率影响较小,适用于对查询需求不大打表,例如日志表

     

    聚集索引注意点:

    1. 聚集索引在一个表中只能有一个,但是不限于一列,可以是包含多个列的组合索引
    2. 虽然可以多列,但是列越少越好
    3. 创建聚集索引的列的重复性越低越好,因为如果聚集索引不是唯一值,SQL SERVER会生成额外的标识信息以区分数据
    4. 适合使用在返回一个返回值的场合:BETWEEN...AND , >= , <= , > , <
    5. 适合返回大型结果集的查询
    6. 适合被连续访问的列
    7. 适合经常被连接及Group By的列
    8. 最好使用窄列或窄列组合作为聚集索引列
    9. 不适合频繁更改的列(由于表是按照索引排序的,索引的更新将导致数据整行移动

    非聚集索引注意点:

    1. 非聚集索引可以看成是高效率查询数据的一种途径。例如对人群进行筛选可以从年龄,收入,性别等多种角度切入,每个角度都可以建立一个非聚集索引

     

    创建聚集索引和非聚集索引的列是有要求的,以下类型无法创建索引:

    • binary、varbinary
    • ntext、text、image
    • varchar(max)、nvarchar(max)
    • uniqueidentifier
    • rowversion、timestamp
    • sql_variant
    • 精度大于 18 位的 decimal 和 numeric
    • 标量大于 2 的 datetimeoffset
    • CLR 类型(hierarchyid和空间类型)
    • xml
    •  如果使用以上类型的列,会出现错误【表'TABLE_NAME'中的列 'COLUMN_NAME' 的类型不能用作索引中的键列。

       

      使用索引的好处:能够根据查询要求,迅速缩小查询范围,避免全表扫描。

     

    索引的创建和使用:

    创建唯一索引要在前面加上unique

    Create [unique] index index_name

    On table_name (column[,…n])

    index_name:索引名

    table_name:表名

    column:字段名

    SQL中创建的语法:

    Create [unique][clustered][nonclustered]index index_name

    On {table|view} (column[asc|desc][,…n

     

    可选参数的意思:unique  唯一的

                                 clustered    聚集索引    一张表只能有一个聚集索引

                                 nonclustered  非聚集索引    一张表可以有多个非聚集索引

    删除索引:语法drop index tablename.indexname

    Drop index 表名.索引名

    索引可以加快查询的速度,数据量比较大的时候是很有用的,但是会减慢数据的插入更新速度。

    展开全文
  • 包含列索引的列分为键列和非键列,所谓的非键列就是INCLUDE中包含的列,至少需要有一个键列,且键列和非键列不允许重复,非键列最多允许1023列(也就是表的最多列-1),由于索引键列(不包括非键)必须遵守现有索引...
  •  如果返回的数据包含索引的键值中,或者包含索引的键值+聚集索引的键值中,那么就不会发生Bookup Lookup,因为找到索引项,就已经找到所需的数据了,没有必要再到数据行去找了。这种情况,叫做索引覆盖; ...
  • 在 SQL Server 2005 中,可以通过将非键列添加到非聚集索引的叶级别来扩展非聚集索引的... 当查询中的所有列都作为键列或非键列包含索引中时,带有包含非键列的索引可以显著提高查询性能。这样可以实现性能提升...
  • 开文之前首先要讲讲几个概念  【覆盖查询】  当索引包含查询引用的所有时,它通常称为“覆盖查询”。  【索引覆盖】  如果返回的数据包含索引的键值中,或者包含索引的键值
  • 一、覆盖索引 如果所构建的查询只需访问索引中的数据即可满足查询的需求,那便...如果两个索引中至少有一是相同的,则SQL Server能将两个索引联在一起以满足查询的需求。 数据库中有索引是一件好事,而覆盖...
  • SQL Server 索引中include的魅力(具有包含性列索引) http://www.cnblogs.com/gaizai/archive/2010/01/11/1644358.html 上个月刚发现sql2016 之前仅支持 索引包含 16个 (之后支持32个) 这个月发现 ...
  •  【索引覆盖】 如果返回的数据包含索引的键值中,或者包含索引的键值+聚集索引的键值中,那么就不会发生Bookup Lookup,因为找到索引项,就已经找到所需的数据了,没有必要再到数据行去找了。这种情况,...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 115,628
精华内容 46,251
关键字:

索引键列和包含性列