SQL SERVER 索引过多，会造成后果，需要详细的说明！

[梦在旅途]

SQL SERVER 合理的索引，会加快数据查询效率，但若索引过多或不当，则会适得其反，至于为什么为这样，需要相关专业人事帮忙解说一下，非常感谢！

[發糞塗牆]

简要说明： 1、存储空间，每个索引都要空间存储 2、如果非聚集索引很多，一旦聚集索引改变，那么所有非聚集索引都会跟着变。 3、过多索引会导致优化器优化过程需要评估的组合增多。 4、每个索引都有统计信息，索引越多统计信息越多。 5、更新开销，一旦一个数据改变，并且改变的列比较多，可能会引起好几个索引跟着改变

[山寨DBA]

大多数SQL Server表需要索引来提高数据的访问速度，如果没有索引SQL Server要进行表格扫描读取表中的每一个记录才能找到索要的数据。为什么不对表中的每一个列创建一个索引呢？这是因为，增加索引也有许多不利的一个方面：第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加；第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大，如果非聚集索引很多，一旦聚集索引改变，那么所有非聚集索引都会跟着变；第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，一旦一个数据改变，并且改变的列比较多，可能会引起好几个索引跟着改变，这样就降低了数据的维护速度。第四、每个索引都有统计信息，索引越多统计信息越多，过多索引会导致优化器优化过程需要评估的组合增多。创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。一般来说，应该在这些列上创建索引，例如：在经常需要搜索的列上，可以加快搜索的速度；在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；这样查询可以利用索引的排序，加快排序查询时间；在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。当增加索引时，会提高检索性能，但是会降低修改性能。主键约束是一种保持数据完整性的逻辑，它限制表中的记录有相同的主键记录。在创建主键约束时，系统自动创建了一个唯一性的聚簇索引。虽然，在逻辑上，主键约束是一种重要的结构，但是，在物理结构上，与主键约束相对应的结构是唯一性的聚簇索引。换句话说，在物理实现上，不存在主键约束，而只存在唯一性的聚簇索引。同样，在创建唯一性键约束时，也同时创建了索引，这种索引则是唯一性的非聚簇索引。因此，当使用约束创建索引时，索引的类型和特征基本上都已经确定了，由用户定制的余地比较小。 当在表上定义主键或者唯一性键约束时，如果表中已经有了使用CREATE INDEX语句创建的标准索引时，那么主键约束或者唯一性键约束创建的索引覆盖以前创建的标准索引。也就是说，主键约束或者唯一性键约束创建的索引的优先级高于使用CREATE INDEX语句创建的索引。当创建唯一性索引时，应该认真考虑这些规则：当在表中创建主键约束或者唯一性键约束时，SQL Server自动创建一个唯一性索引；如果表中已经包含有数据，那么当创建索引时，SQL Server检查表中已有数据的冗余性；每当使用插入语句插入数据或者使用修改语句修改数据时，SQL Server检查数据的冗余性：如果有冗余值，那么SQL Server取消该语句的执行，并且返回一个错误消息；确保表中的每一行数据都有一个唯一值，这样可以确保每一个实体都可以唯一确认；只能在可以保证实体完整性的列上创建唯一性索引。当创建复合索引时，应该考虑这些规则：最多可以把16个列合并成一个单独的复合索引，构成复合索引的列的总长度不能超过900字节，也就是说复合列的长度不能太长；在复合索引中，所有的列必须来自同一个表中，不能跨表建立复合列；在复合索引中，列的排列顺序是非常重要的，因此要认真排列列的顺序，原则上，应该首先定义最唯一的列，例如在（COL1，COL2）上的索引与在（COL2，COL1）上的索引是不相同的，因为两个索引的列的顺序不同；为了使查询优化器使用复合索引，查询语句中的WHERE子句必须参考复合索引中第一个列；当表中有多个关键列时，复合索引是非常有用的；使用复合索引可以提高查询性能，减少在一个表中所创建的索引数量。索引可以分为簇索引和非簇索引，簇索引通过重排表中的数据来提高数据的访问速度，非簇索引则通过维护表中的数据指针来提高数据的索引。聚簇索引的体系结构：索引的结构类似于树状结构，树的顶部称为叶级，树的其它部分称为非叶级，树的根部在非叶级中。同样，在聚簇索引中，聚簇索引的叶级和非叶级构成了一个树状结构，索引的最低级是叶级。在聚簇索引中，表中的数据所在的数据页是叶级，在叶级之上的索引页是非叶级，索引数据所在的索引页是非叶级。在聚簇索引中，数据值的顺序总是按照升序排列。应该在表中经常搜索的列或者按照顺序访问的列上创建聚簇索引。当创建聚簇索引时，应该考虑这些因素：每一个表只能有一个聚簇索引，因为表中数据的物理顺序只能有一个；表中行的物理顺序和索引中行的物理顺序是相同的，在创建任何非聚簇索引之前创建聚簇索引，这是因为聚簇索引改变了表中行的物理顺序，数据行按照一定的顺序排列，并且自动维护这个顺序；关键值的唯一性要么使用UNIQUE关键字明确维护，要么由一个内部的唯一标识符明确维护，这些唯一性标识符是系统自己使用的，用户不能访问；聚簇索引的平均大小大约是数据表的百分之五，但是，实际的聚簇索引的大小常常根据索引列的大小变化而变化；在索引的创建过程中，SQL Server临时使用当前数据库的磁盘空间，当创建聚簇索引时，需要1.2倍的表空间的大小，因此，一定要保证有足够的空间来创建聚簇索引。索引的体系结构为什么要不断的维护表的索引，首先简单介绍一下索引的体系结构。SQL Server在硬盘中用8KB页面在数据库文件内存放数据，缺省情况下这些页面及其包含的数据是无组织的，为了使混乱变为有序就要生成索引，生成索引后就有了索引页和数据页，数据页保存用户写入的数据信息，索引页存放用于检索列的数据值清单关键词和索引表中该值所在纪录的地址指针。簇索引实质上是将表中的数据排序，就好像是字典的索引目录。非簇索引不对数据排序它只保存了数据的指针地址。向一个带簇索引的表中插入数据，当数据页达到100%时由于页面没有空间插入新的纪录，这时就会发生分页，SQL Server 将大约一半的资料从满页中移到空页中从而生成两个半的满页，这样就有大量的数据空间。簇索引是双向链表，在每一页的头部保存了前一页、后一页地址以及分页后数据移动的地址，由于新页可能在数据库文件中的任何地方因此页面的链接不一定指向磁盘的下一个物理页链接，可能指向了另一个区域这就形成了分块从而减慢了系统的速度。对于非簇索引的表来说，非簇索引的关键词是指向簇索引的而不是指向数据页的本身。为了克服数据分块带来的负面影响需要重构表的索引这是非常费时的，因此只能在需要时进行。

[發糞塗牆]

注意排版

引用 2 楼 hwhmh2010 的回复:

大多数SQL Server表需要索引来提高数据的访问速度，如果没有索引SQL Server要进行表格扫描读取表中的每一个记录才能找到索要的数据。为什么不对表中的每一个列创建一个索引呢？这是因为，增加索引也有许多不利的一个方面：第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加；第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大，如果非聚集索引很多，一旦聚集索引改变，那么所有非聚集索引都会跟着变；第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，一旦一个数据改变，并且改变的列比较多，可能会引起好几个索引跟着改变，这样就降低了数据的维护速度。第四、每个索引都有统计信息，索引越多统计信息越多，过多索引会导致优化器优化过程需要评估的组合增多。创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。一般来说，应该在这些列上创建索引，例如：在经常需要搜索的列上，可以加快搜索的速度；在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；这样查询可以利用索引的排序，加快排序查询时间；在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。当增加索引时，会提高检索性能，但是会降低修改性能。主键约束是一种保持数据完整性的逻辑，它限制表中的记录有相同的主键记录。在创建主键约束时，系统自动创建了一个唯一性的聚簇索引。虽然，在逻辑上，主键约束是一种重要的结构，但是，在物理结构上，与主键约束相对应的结构是唯一性的聚簇索引。换句话说，在物理实现上，不存在主键约束，而只存在唯一性的聚簇索引。同样，在创建唯一性键约束时，也同时创建了索引，这种索引则是唯一性的非聚簇索引。因此，当使用约束创建索引时，索引的类型和特征基本上都已经确定了，由用户定制的余地比较小。 当在表上定义主键或者唯一性键约束时，如果表中已经有了使用CREATE INDEX语句创建的标准索引时，那么主键约束或者唯一性键约束创建的索引覆盖以前创建的标准索引。也就是说，主键约束或者唯一性键约束创建的索引的优先级高于使用CREATE INDEX语句创建的索引。当创建唯一性索引时，应该认真考虑这些规则：当在表中创建主键约束或者唯一性键约束时，SQL Server自动创建一个唯一性索引；如果表中已经包含有数据，那么当创建索引时，SQL Server检查表中已有数据的冗余性；每当使用插入语句插入数据或者使用修改语句修改数据时，SQL Server检查数据的冗余性：如果有冗余值，那么SQL Server取消该语句的执行，并且返回一个错误消息；确保表中的每一行数据都有一个唯一值，这样可以确保每一个实体都可以唯一确认；只能在可以保证实体完整性的列上创建唯一性索引。当创建复合索引时，应该考虑这些规则：最多可以把16个列合并成一个单独的复合索引，构成复合索引的列的总长度不能超过900字节，也就是说复合列的长度不能太长；在复合索引中，所有的列必须来自同一个表中，不能跨表建立复合列；在复合索引中，列的排列顺序是非常重要的，因此要认真排列列的顺序，原则上，应该首先定义最唯一的列，例如在（COL1，COL2）上的索引与在（COL2，COL1）上的索引是不相同的，因为两个索引的列的顺序不同；为了使查询优化器使用复合索引，查询语句中的WHERE子句必须参考复合索引中第一个列；当表中有多个关键列时，复合索引是非常有用的；使用复合索引可以提高查询性能，减少在一个表中所创建的索引数量。索引可以分为簇索引和非簇索引，簇索引通过重排表中的数据来提高数据的访问速度，非簇索引则通过维护表中的数据指针来提高数据的索引。聚簇索引的体系结构：索引的结构类似于树状结构，树的顶部称为叶级，树的其它部分称为非叶级，树的根部在非叶级中。同样，在聚簇索引中，聚簇索引的叶级和非叶级构成了一个树状结构，索引的最低级是叶级。在聚簇索引中，表中的数据所在的数据页是叶级，在叶级之上的索引页是非叶级，索引数据所在的索引页是非叶级。在聚簇索引中，数据值的顺序总是按照升序排列。应该在表中经常搜索的列或者按照顺序访问的列上创建聚簇索引。当创建聚簇索引时，应该考虑这些因素：每一个表只能有一个聚簇索引，因为表中数据的物理顺序只能有一个；表中行的物理顺序和索引中行的物理顺序是相同的，在创建任何非聚簇索引之前创建聚簇索引，这是因为聚簇索引改变了表中行的物理顺序，数据行按照一定的顺序排列，并且自动维护这个顺序；关键值的唯一性要么使用UNIQUE关键字明确维护，要么由一个内部的唯一标识符明确维护，这些唯一性标识符是系统自己使用的，用户不能访问；聚簇索引的平均大小大约是数据表的百分之五，但是，实际的聚簇索引的大小常常根据索引列的大小变化而变化；在索引的创建过程中，SQL Server临时使用当前数据库的磁盘空间，当创建聚簇索引时，需要1.2倍的表空间的大小，因此，一定要保证有足够的空间来创建聚簇索引。索引的体系结构为什么要不断的维护表的索引，首先简单介绍一下索引的体系结构。SQL Server在硬盘中用8KB页面在数据库文件内存放数据，缺省情况下这些页面及其包含的数据是无组织的，为了使混乱变为有序就要生成索引，生成索引后就有了索引页和数据页，数据页保存用户写入的数据信息，索引页存放用于检索列的数据值清单关键词和索引表中该值所在纪录的地址指针。簇索引实质上是将表中的数据排序，就好像是字典的索引目录。非簇索引不对数据排序它只保存了数据的指针地址。向一个带簇索引的表中插入数据，当数据页达到100%时由于页面没有空间插入新的纪录，这时就会发生分页，SQL Server 将大约一半的资料从满页中移到空页中从而生成两个半的满页，这样就有大量的数据空间。簇索引是双向链表，在每一页的头部保存了前一页、后一页地址以及分页后数据移动的地址，由于新页可能在数据库文件中的任何地方因此页面的链接不一定指向磁盘的下一个物理页链接，可能指向了另一个区域这就形成了分块从而减慢了系统的速度。对于非簇索引的表来说，非簇索引的关键词是指向簇索引的而不是指向数据页的本身。为了克服数据分块带来的负面影响需要重构表的索引这是非常费时的，因此只能在需要时进行。

[阳泉酒家小当家]

索引能加快速度，一般人都知道这个。 但是索引也有另一面，就是索引是有开销的，每次update、delete、insert表的时候，都会维护相应的索引，把值维护到索引中去。 所以，一旦你的索引很多，比如你一个表建了10个索引，那么在你进行update操作时，可能就得同时维护10个索引，那么update语句肯定就会变得慢，相应的delete、insert都会慢。

[阳泉酒家小当家]

  
--user_updates会显示1   
select   
    DB_NAME(d.database_id),  
    OBJECT_NAME(d.object_id),  
      
    i.name,  
    user_seeks,  
    user_scans,  
    user_lookups,  
      
    user_updates  --通过用户查询执行的更新次数     
from sys.dm_db_index_usage_stats d  
inner join sys.indexes i  
        on d.object_id = i.object_id  
           and d.index_id = i.index_id  
where database_id = DB_ID('数据库名称')  

对于user_seeks次数很少，或者为0的索引，可以删除这些索引

[山寨DBA]

哈哈，直接清屏

[KevinLiu]

上面解释的很好了。建好索引之后需要定期的查看索引的使用，比如一条索引一次没被查过，但是被修改了几十万甚至上百万次，那么这些修改都是需要资源消耗的。同时也要占用磁盘空间，对于这些删掉。

[zosky]

学习了，，，

[Mr_Nice]

引用 楼主 wheeky 的回复:

SQL SERVER 合理的索引，会加快数据查询效率，但若索引过多或不当，则会适得其反，至于为什么为这样，需要相关专业人事帮忙解说一下，非常感谢！

就说一个例子，如果一本字典，有多个目录，按照ABC，部首，再来个九宫格啥的，如果突然来个字是之前没有收录的，所有的目录，是否都要调整一遍？

[linwaterbin]

引用 1 楼 DBA_Huangzj 的回复:

简要说明： 1、存储空间，每个索引都要空间存储 2、如果非聚集索引很多，一旦聚集索引改变，那么所有非聚集索引都会跟着变。 3、过多索引会导致优化器优化过程需要评估的组合增多。 4、每个索引都有统计信息，索引越多统计信息越多。 5、更新开销，一旦一个数据改变，并且改变的列比较多，可能会引起好几个索引跟着改变

很全！赞！在Oracle里面情况类似。