浅谈sql server聚集索引与非聚集索引

今天同事的服务程序在执行批量插入数据操作时，会超时失败，代码debug了几遍一点问题都没有，SQL单条插入也可以正常录入数据，调试了一上午还是很迷茫，场面一度很尴尬，最后还是发现了问题的根本，原来是这个同事为了提升查询效率滥用了索引在搞鬼，没有合理的运用索引使每次新增和修改数据时效率极低，大批量插入和修改数据时会使服务器超时。

所以我也简单的对索引相关的知识简单的做了一些总结，我查阅整理了一下在数据的管理中有如下可用的索引：

索引类型 描述
1，哈希：借助于哈希索引，可通过内存中的哈希表来访问数据。 哈希索引的内存用量固定不变，是存储桶数量的函数。

2，内存优化非聚集索引： 对于内存优化的非聚集索引，内存使用量依赖于行计数以及索引键列的大小

3，群集： 聚集索引基于聚集索引键按顺序排序和存储表或视图中的数据行。 聚集索引按 B 树索引结构实现，B 树索引结构支持基于聚集索引键值对行进行快速检索。

4，非聚集 ：既可以使用聚集索引来为表或视图定义非聚集索引，也可以根据堆来定义非聚集索引。 非聚集索引中的每个索引行都包含非聚集键值和行定位符。 此定位符指向聚集索引或堆中包含该键值的数据行。 索引中的行按索引键值的顺序存储，但是不保证数据行按任何特定顺序存储，除非对表创建聚集索引。

5，唯一： 唯一索引确保索引键不包含重复的值，因此，表或视图中的每一行在某种程度上是唯一的。唯一性可以是聚集索引和非聚集索引的属性。

6，columnstore： 内存中列存储索引通过使用基于列的数据存储和基于列的查询处理来存储和管理数据。列存储索引适合于主要执行大容量加载和只读查询的数据仓库工作负荷。 与传统面向行的存储方式相比，使用列存储索引存档可最多提高 10 倍查询性能 ，与使用非压缩数据大小相比，可提供多达 7 倍数据压缩率 。

7，带有包含列的索引： 一种非聚集索引，它扩展后不仅包含键列，还包含非键列。 

8，计算列上的索引： 从一个或多个其他列的值或某些确定的输入值派生的列上的索引。

9，筛选： 一种经过优化的非聚集索引，尤其适用于涵盖从定义完善的数据子集中选择数据的查询。 筛选索引使用筛选谓词对表中的部分行进行索引。 与全表索引相比，设计良好的筛选索引可以提高查询性能、减少索引维护开销并可降低索引存储开销。

10，空间 ：利用空间索引，可以更高效地对几何数据类型的列中的空间对象（ 空间数据 ）执行某些操作。 空间索引可减少需要应用开销相对较大的空间操作的对象数。

11，XML： xml 数据类型列中 XML 二进制大型对象 (BLOB) 的已拆分持久表示形式。

12，全文： 一种特殊类型的基于标记的功能性索引，由 Microsoft SQL Server全文引擎生成和维护。 用于帮助在字符串数据中搜索复杂的词。

不难看出要搞清楚所有索引，并灵活的去应用它其实并不是一件容易的事情，很多索引我们甚至都在工作中没用过，甚至没听说过，也不用担心，可以先大致的了解它，可以在需要实际用到时再去学习也不迟。

不过索引虽多，不用个个深入学习，个人认为搞懂聚集索引和非聚集索引的基本概念还是很有必要的，因为在实际的优化查询，修改，插入等操作时，这两个概念用的最多的，也是最基础的

1，什么是索引？

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。最常见的索引就是书的目录

2，如何理解聚集索引和非聚集索引

概念：

　　聚集索引是指数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同。

　　非聚集索引是一种索引，该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。

上面是资料概念的概述，在讲讲我自己对聚集索引和非聚集索引的理解：

　　聚集索引最现实的可以体现的例子就是字典的拼音查询，一般我们常用的中文字典会将字典的每个字按照abcd...的拼音顺序去排列，字典中的每一个字好比是一条条数据，而字典最前面更具拼音查询指定字的页码的小目录就是索引，目录拼音排序和实际数据顺序是相同的，也就是所谓的数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同。

　　那非聚集索引勒？非聚集索引其实最现实的体现也是字典，只不过换成了字典的部首查询的体现，我们看看部首查询和拼音查询有何区别，不难发现，从部首排序的角度去看，发现字典中字典中实际的字（实际数据）排列顺序是没有任何部首排列规则的，但是查询部首的目录（索引）都是按照指定的部首规则排序的，这样的好处可以在插入和更新的数据的时候不必重新排列数据，这就是所谓的索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。

聚集索引和非聚集索引都可以优化查询，我在此处只是浅显的从，概念的角度去谈对两个简单索引的理解，希望对大家有用，实际索引的原理还是比较复杂的，需要深入所有索引的原理个人建议还是从数据结构入手

附：

• 群集

  • 聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。 索引定义中包含聚集索引列。 每个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。

  • 只有当表包含聚集索引时，表中的数据行才按排序顺序存储。 如果表具有聚集索引，则该表称为聚集表。 如果表没有聚集索引，则其数据行存储在一个称为堆的无序结构中。

• 非聚集

  • 非聚集索引具有独立于数据行的结构。 非聚集索引包含非聚集索引键值，并且每个键值项都有指向包含该键值的数据行的指针。

  • 从非聚集索引中的索引行指向数据行的指针称为行定位器。 行定位器的结构取决于数据页是存储在堆中还是聚集表中。 对于堆，行定位器是指向行的指针。 对于聚集表，行定位器是聚集索引键。

  • 可以向非聚集索引的叶级添加非键列以跳过现有的索引键限制，并执行完整范围内的索引查询。

聚集索引和非聚集索引都可以是唯一的。 这意味着任何两行都不能有相同的索引键值。 另外，索引也可以不是唯一的，即多行可以共享同一键值。 

每当修改了表数据后，都会自动维护表或视图的索引。

聚集索引的缺点是对表进行修改速度较慢，这是为了保持表中的记录的物理顺序与索引的顺序一致，而把记录插入到数据页的相应位置，必须在数据页中进行数据重排，降低了执行速度。非聚集索引指定了表中记录的逻辑顺序，但记录的物理顺序和索引的顺序不一致，聚集索引和非聚集索引都采用了B+树的结构，但非聚集索引的叶子层并不与实际的数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针的方式。非聚集索引比聚集索引层次多，添加记录不会引起数据顺序的重组。

 

转载于:https://www.cnblogs.com/ruanraun/p/indexes.html