SQL Server索引基础
SQL Server索引基础
SQL Server索引基础
索引是一个单独的，存储在磁盘上的数据结构——排序，它们包含则对数据表里所有记录的引用指针，使用索引用于快速找出在某个或多个列中有某一特定值的行，对相关列使用索引是降低查询操作时间的最佳途径。
一般来说建立索引的原则包括以下内容：
避免对经常更新的表进行过多的索引，并且索引中的列尽可能少。而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段。
在条件表达式中经常用到的、不同值较多的列上建立索引，在不同值少的列上不要建立索引。
在频繁进行排序或分组（即进行 GROUP BY 或 ORDER BY 操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。
在选择索引键时，尽可能采用小数据类型的列作为键以使每个索引页能容纳尽可能多的索引键和指针，通过这种方式，可使一个查询必需遍历的索引页面降低到最小，此外，尽可能的使用整数做为键值，因为整数的访问速度最快。
设计索引需要需要综合考虑，如有一个varchar(255)的列，如果在前10个或者30个字符内，多数值是唯一的，则不需要对整个列进行索引，使用短索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和减少I/O操作。
索引的优点：
通过创建唯一索引，可以保证数据库表的每一行数据的唯一性。
可以大大加快数据的查询速度，这也是创建索引的最主要的原因。
实现数据的参照完整性，可以速表和表之间的连接。
在使用分组和排序子句进行查询时，也可以显著减少查询中分组和排序的时间。
索引也有不利的一面：
创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到做大文件尺寸。
当对表中的数据进行增加，删除和修改的时候，索引也要动态地维护，这样就就降低了数据的维护速度。
聚集索引 (clustered index)
一种索引，该索引中键值的逻辑顺序决定了表中相应行的物理顺序。
聚集索引类似于课本中目录表，目录表的顺序与实际的页码顺序是一致的。通过目录可以找到你想要的信息。聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。
如果该表上尚未创建聚集索引，且在创建 PRIMARY KEY 约束时未指定非聚集索引，PRIMARY KEY 约束会自动创建聚集索引。
非聚集索引 (nonclustered index)
一种索引，该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。
非聚集索引与课本中的索引类似。数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。索引中的项目按索引键值的顺序存储，而表中的信息按另一种顺序存储。与使用书中索引的方式相似，在搜索数据值时，先对非聚集索引进行搜索，找到数据值在表中的位置，然后从该位置直接检索数据。
聚集与非聚集索引的区别：
1.聚集索引一个表只能有一个，因为数据表的行只能按一种顺序存储；而非聚集索引一个表可以存在多个。
2.聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续。
3. 在 SQL Server 中，索引是按 B-树(Balanced-tree) 结构进行组织的。聚集索引的叶节点就是最终的数据节点，而非聚集索引的叶节仍然是索引节点，但它有一个指向最终数据的指针。
在 SQL Server 中，索引是按 B-树结构进行组织的。索引 B-树中的每一页(page)称为一个索引节点。B-树的顶端节点称为根（root）节点。索引中的底层节点称为叶节点。根节点与叶(leaf)节点之间的任何索引级别统称为中间级。聚集索引与聚集索引具有相同的 B-树结构，它们之间的差别：
在聚集索引中，叶节点包含基础表的数据页。根节点和中间级节点包含存有索引行的索引页。聚集索引中每个索引行包含一个键值和一个指针，该指针指向 B-树上的某一中间级页或叶级索引中的某个数据行。
非聚集索引的基础表的数据行不按非聚集键的顺序排序和存储。非聚集索引的叶层是由索引页而不是由数据页组成。行定位器的结构取决于数据页是存储在堆（heap）中还是聚集表中。 对于堆，行定位器是指向行的指针——如果表是堆（意味着该表没有聚集索引），则行定位器是指向行的指针。 对于聚集表，行定位器是聚集索引键——如果表有聚集索引或索引视图上有聚集索引，则行定位器是行的聚集索引键，SQL Server 通过使用存储在非聚集索引的叶行内的聚集索引键搜索聚集索引来检索数据行。参见下面简化示意图。
聚集索引按 B 树索引结构实现，数据行基于聚集索引键按顺序存储。B 树索引结构支持基于聚集索引键值对行进行快速检索。聚集索引中每个级别的页（包括叶级别的数据页，聚集索引的数据页是聚集索引的叶节点）链接在一个双向链表（Doubly linked list）中。通过使用键值来执行从一个级别到另一级别的导航。
大多数表都应该需要一个聚集索引。优化器非常倾向于采用聚集索引，因为聚集索引能够直接在叶级别找到数据。由于定义了数据的逻辑顺序，聚集索引能够特别快的访问针对范围值的查询，查询优化器能够发现只有某一段范围的数据页需要扫描。
非聚集索引与聚集索引有一个相似的 B 树索引结构。不同的是，非聚集索引不影响数据行的顺序。叶级别包含索引行，每个叶级别中的索引行中包含行定位器( row locator），行定位器有两种形式：
如果表是堆（意味着该表没有聚集索引），则行定位器是指向行的指针。该指针由文件标识符 (ID)、页码和页上的行数生成。整个指针称为行 ID (RID，row identifier)。
如果表有聚集索引或索引视图上有聚集索引，则行定位器是行的聚集索引键。如果聚集索引不是唯一的索引，SQL Server 将添加在内部生成的值（称为唯一值）以使所有重复键唯一。此四字节的值对于用户不可见。仅当需要使聚集键唯一以用于非聚集索引中时，才添加该值。SQL Server 通过使用存储在非聚集索引的叶行内的聚集索引键搜索聚集索引来检索数据行。
【双向链表（Doubly linked list）也叫双链表，是链表的一种，它的每个中间结点中都有两个指针，分别指向直接后继和直接前驱。所以，从双向链表中的任意一个结点开始，都可以很方便地访问它的前驱结点和后继结点。如果要在两个节点中插入一个新的节点只需修改节点的前驱和后继，而且无需修改新节点的物理位置。
堆（heap）是没有聚集索引的表。数据行不按任何特殊的顺序存储，数据页也没有任何特殊的顺序。数据页不在链接列表内链接。】
聚集与非聚集索引选择，参见下表
特别提示：
☆无论是聚集索引还是非聚集索引都是B树结构。
☆创建主键时会自动创建聚集索引，除非当前表中已经含有了聚集索引或是创建主键时指定了NONCLUSTERED关键字。
聚集索引默认与主键相匹配，在设置主键时，SQL Server会默认在主键列创建聚集索引。但是可以手动更改为在任意一个列创建聚集索引，然后在另一个字段或多个字段上定义主键。这时主键将会被作为一个唯一的非聚集索引(唯一索引)被创建。通过指定NONCLUSTERED关键字就可以做到。
☆唯一索引，故名思议就是它要求该列上的值是唯一的。聚集索引列可以重复，非聚集索引列也可以重复。这就是为什么要有唯一索引了。声明唯一索引的语法很简单，只是多了个UNIQUE关键字。
如果唯一索引或约束所约束的列在当前的表中已经含有了重复值，那么创建索引会失败。而当唯一索引创建成功后，所有违反这个约束的INSERT、UPDATE语句都会失败。
进一步了解 https://docs.microsoft.com/zh-cn/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver15
————————————————
版权声明：本文为CSDN博主「软件技术学习开发爱好者」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/cnds123/article/details/116081372