SQL SERVER INDEX 总结
使用聚集索引的查询效率要比非聚集索引的效率要高，但是如果需要频繁去改变聚集索引的值，写入性能并不高，因为需要移动对应数据的物理位置。
非聚集索引在查询的时候可以的话就避免二次查询，这样性能会大幅提升。
不是所有的表都适合建立索引，只有数据量大表才适合建立索引，且建立在选择性高的列上面性能会更好。
在SQL Server中还有include的用法，可以把非聚集索引里包含的列包含进来，而不一定需要建立复合索引。
可以通过将非键列添加到非聚集索引的叶级别来扩展非聚集索引的功能。通过包含非键列，可以创建覆盖更多查询的非聚集索引。这是因为非键列具有下列优点：
–1、它们可以是不允许作为索引键列的数据类型。
–2、在计算索引键列数或索引键大小时，数据库引擎不考虑它们。
–当查询中的所有列都作为键列或非键列包含在索引中时，带有包含性非键列的索引可以显著提高查询性能。
–这样可以实现性能提升，因为查询优化器可以在索引中找到所有列值；不访问表或聚集索引数据，从而减少磁盘 I/O 操作。include(列名,…)
唯一索引【UNIQUE INDEX】、主键、clustered index、 unclustered index
索引的数据结构可以是树，也可以是哈希表
聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。
聚簇索引的顺序，就是数据在硬盘上的物理顺序。一般情况下主键就是默认的聚簇索引。
一张表只允许存在一个聚簇索引，因为真实数据的物理顺序只能有一种。如果一张表上还没有聚簇索引，为它新创建聚簇索引时，就需要对已有数据重新进行排序，所以对表进行修改速度较慢是聚簇索引的缺点，对于经常更新的列不宜建立聚簇索引。
聚簇索引性能最好，因为一旦具有第一个索引值的记录被找到，具有连续索引值的记录也一定物理地紧跟其后。一张表只能有一个聚簇索引，所以非常珍贵，必须慎重设置，一般要根据这个表最常用的SQL查询方式选择某个（或多个）字段作为聚簇索引（或复合聚簇索引）。
聚簇索引默认是主键，如果表中没有定义主键，InnoDB[1]会选择一个唯一的非空索引代替（“唯一的非空索引”是指列不能出现null值的唯一索引，跟主键性质一样）。如果没有这样的索引，InnoDB会隐式地定义一个主键来作为聚簇索引。
聚簇索引 与 唯一索引
严格来说，聚簇索引不一定是唯一索引，聚簇索引的索引值并不要求是唯一的，唯一聚簇索引才是！在一个有聚簇索引的列上是可以插入两个或多个相同值的，这些相同值在硬盘上的物理排序与聚簇索引的排序相同，仅此而已
索引可能会提高查询速度（如果查询时使用了索引），但一定会减慢写入速度，因为每次写入时都需要更新索引，所以索引只应该加在经常需要搜索的列上，不要加在写多读少的列上。
唯一索引：不允许两行具有相同的索引值。可以是聚集索引，也可以非聚集索引 。
UNIQUE索引既可以采用聚集索引结构，也可以采用非聚集索引的结构，如果不指明采用的索引结构，则SQL Server系统默认为采用非聚集索引结构。
主键索引：数据库表通常有一列或几列组合，其值用来唯一标识表中的每一行。该列称为表的主键。
在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的。
聚集索引：聚集索引中，表中各行的物理顺序与键值的逻辑（索引）顺序相同。表只能包含一个{有且仅有一个}聚集索引。
索引会存入单独存入[索引]表，[索引]表的逻辑顺序跟表的物理顺序是相同的。
尽量在建立非聚集索引之前建立聚集索引，否则会导致表上所有非聚集索引的重建
非聚集索引：表中各行的物理顺序与键值的逻辑顺序不匹配。
非聚集索引在物理地址上不相邻
聚集索引比非聚集索引有更快的数据访问速度
---创建聚集索引 默认是非聚集索引
create
--[unique] --指定聚集索引是否唯一
[clustered | nonclustered] --指定为聚集索引
index 索引名称 --索引名称
on 表名 --索引添加在哪个表
(列名 [asc | desc],列名 [asc | desc],...) --索引添加在哪个数据列--- 删除索引语法：DROP INDEX table_name.index_name[,table_name.index_name]
说明：table_name: 索引所在的表名称。
index_name : 要删除的索引名称。--显示索引信息：使用系统存储过程：sp_helpindex 查看指定表的索引信息。
执行代码如下：
Exec sp_helpindex book1;–-删除指定约束
alter table --表名
drop constraint --主键约束名称–-将指定字段设置成主键非聚集索引
alter table 表名
add constraint 主键约束名称 primary key NONCLUSTERED(字段名)
区别：
1、主键是一种约束，唯一索引是一种索引；两者在本质上是不同的，只是主键，会默认创建一个唯一索引
2、主键创建后一定包含一个唯一性索引{主键索引是一种特殊的唯一索引，不可以为null}，唯一性索引不一定是主键 {只有能代表唯一就是唯一索引，且可以为null（但只能有一个）}； 主键不可为null，唯一索引可以为null。
3、唯一性索引列允许空值， 主键不允许；
4、主键可被其他表引为外键，唯一索引不能；
5、一个表只能创建一个主键，但可创建多个唯一索引。一个表有且仅有一个聚集索引， 唯一索引不一定是聚集索引，也可以是noclustered
6、聚集索引并不一定是唯一索引，由于SQL SERVER将主键默认定义为聚集索引，ORACLE中则默认是非聚集，事实上，索引是否唯一与是否聚集是不相关的，聚集索引可以是唯一索引，也可以是非唯一索引；
7、将索引设置为唯一，对于等值查找是很有利的，当查到第一条符合条件的纪录时即可停止查找，返回数据，而非唯一索引则要继续查找，同样，由于需要保证唯一性，每一行数据的插入都会去检查重复性；
复合索引
复合索引的最左优先实现原理简单来说就是：
我们知道B+树是非常适合索引的结构，所以一棵树
如果是单列，就按这列数据进行排序
如果是多列，就按多列数据排序，例如有（1,1） （2,2） （2,1） （1,2）
那在索引中的叶子节点的数据顺序就是（1,1）（1,2）（2,1）（2,2）
这也是为什么查询复合索引的前缀是可以用到索引的原因
最左优先就是说组合索引的第一个字段必须出现在查询组句中，这个索引才会被用到。比如假设有一个3列索引(col1,col2,col3),那么SQL只会会建立三个索引(col1),(col1,col2),(col1,col2,col3)。
根据最左匹配原则，where语句必须要有col1才能调用索引（如果没有col1字段那么一个索引也调用不到），如果同时出现col1与col2则会调用两者的组合索引，如果同时出现三者则调用三者的组合索引。
聚集索引与辅助索引
在数据库中，B+树的高度一般都在24层，这也就是说查找某一个键值的行记录时最多只需要2到4次IO，这倒不错。因为当前一般的机械硬盘每秒至少可以做100次IO，24次的IO意味着查询时间只需要0.02~0.04秒。
数据库中的B+树索引可以分为聚集索引（clustered index）和辅助索引（secondary index），
聚集索引与辅助索引相同的是：不管是聚集索引还是辅助索引，其内部都是B+树的形式，即高度是平衡的，叶子结点存放着所有的数据。
聚集索引与辅助索引不同的是：叶子结点存放的是否是一整行的信息
1、聚集索引 叶子结点存放一整行信息
聚集索引的好处之一：它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，查询最后的10位用户信息，由于B+树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录
聚集索引的好处之二：范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可
二分法
覆盖索引
是一种简单有效的覆盖索引的方式，select 后面的字段是索引的字段，而无需到表数据中获取，这样的索引叫做覆盖索引。
好处 直接通过索引就能拿到想要的数据，不需要到表数据中读取行， 如果是主键索引，需要到表中拿到。如果是非主键索引，需要获取到主键，然后从表中拿到
on T(a,b,c) 与on T(a) include(b,c)
include：b、c存放在子叶级。如果select 只包含a、b、c 就不会访问表或聚集索引了，性能会增加
非include：b、c 存放在非子叶级（增加索引空间），还需要维护。如果where 包含a、b、c，效率会提升，否则就徒增索引空间
普通索引INDEX：加速查找
唯一索引：
-主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）
-唯一索引UNIQUE:加速查找+约束（不能重复）
联合索引：
-PRIMARY KEY(id,name):联合主键索引
-UNIQUE(id,name):联合唯一索引
-INDEX(id,name):联合普通索引
----db_id() 返回当前数据库对应的id{再系统表SysDatabases 中的id}
declare @dbid intselect @dbid = db_id()select objectname=object_name(s.object_id), s.object_id, indexname=i.name, i.index_id, user_seeks, user_scans, user_lookups, user_updatesfrom sys.dm_db_index_usage_stats s,sys.indexes iwhere database_id = @dbid and objectproperty(s.object_id,'IsUserTable') = 1and i.object_id = s.object_idand i.index_id = s.index_idorder by (user_seeks + user_scans + user_lookups + user_updates) asc
------返回的结果如下图----------
use master
go
select * from SysDatabases
select * from sys.indexes ---当前数据库中索引表
select * from sys.dm_db_index_usage_stats
sys.dm_db_index_usage_stats 表字段如下
索引重建
索引重建见如下语句：
–1重建指定索引
ALTER INDEX IX_EMP_NAME ON EMPLOYEES REBUILD;–2 重建表里所有索引
ALTER INDEX ALL ON dbo.EMPLOYEES REBUILD
表扫描 Table Scan、 索引扫描 index scan、索引查找 index seek、聚集索引扫描 clustered index scan
表扫描对表中数据从头到尾一行一行的进行出来就是表扫描。这里的处理我们可以理解为sql中where子句的条件判断。我们需要遍历表中的每一行，判断是否满足where条件。最简单的table scan是select * from table。
索引扫描就是对索引中的每个节点从头到尾的访问。假设我们的索引是B树结构的，那么index scan就是访问B树中的每一个节点。
假如索引是聚集索引，那么B树索引的叶子节点保存的是数据页中的实际数据。假如索引是非聚集索引，那么B树叶子节点保存的是指向数据页的指针。
低效 Index Scan(索引扫描)：就全扫描索引（包括根页，中间页和叶级页）：
select 索引列 from table 没有where 条件 索引 就会index scan
高效Index Seek(索引查找)：通过索引向前和向后搜索 ：
index seek和index scan： 索引是一颗B树， index seek是查找从B树的根节点开始，一级一级找到目标行。 index scan则是从左到右，把整个B树遍历一遍。 假设唯一的目标行位于索引树最右的叶节点上（假设是非聚集索引，树深度2，叶节点占用k页物理存储）。 index seek引起的IO是4，而index scan引起的IO是K，性能差别巨大。
seek:从B树根到叶节点的过程 扫描：当SEEK完成后，在叶节点执行范围或全部扫描（按查询的选择性会有不同）
select 索引列，col from table where 条件 索引 就会index seek
clustered index scan(聚集索引扫描)
select 聚集索引列 ，列 from table where 列 >100 就会 clustered index scan
table scan(表扫描)表明正在处理的表格没有聚集索引，SQL Server正在扫描整张表。
clustered index scan(聚集索引扫描)表明SQL Server正在扫描一张有聚集索引的表格，但是也是整表扫描。
Index Scan表明SQL Server正在扫描一个非聚集索引。由于非聚集索引上一般只会有一小部分字段，所以这里虽然也是扫描，但是代价会比整表扫描要小很多。
Clustered Index Seek和Index Seek说明SQL Server正在利用索引结果检索目标数据。如果结果集只占表格总数据量的一小部分，Seek会比Scan便宜很多，索引就起到了提高性能的作用。如果查询结果集很多，那么可能会更倾向使用table scan
clustered index scan：因为在有聚集索引的表格上，数据是直接存放在索引的最底层的，所以要扫描整个表格里的数据，就要把整个聚集索引扫描一遍。在这里，聚集索引扫描就相当于一个表扫描。所要用的时间和资源与表扫描没有什么差别。并不是说这里有了“Index”这个字样，就说明执行计划比表扫描的有多大进步。当然反过来讲，如果看到“Table Scan”的字样，就说明这个表格上没有聚集索引。
Elapsed Time
Elapsed Time表示程序运行的持续时间。
CPU Time
CPU Time是该进程所占用的处理器时间。
对于单线程程序来说，CPU Time指的是该线程在一个逻辑处理器上所花费的时间总量。
对于多线程程序来说，CPU Time指的是所有线程在处理器上花费的时间之和。
User CPU Time
User CPU Time指进程执行用户态代码所使用的时间，是执行此进程所消耗的实际CPU时间。
System CPU Time
System CPU Time指进程在内核态消耗的CPU时间，即在内核执行系统调用所使用的CPU时间。
Wait Time
Wait Time是CPU花费在等待I/O操作上的总时间。
Elapsed Time、CPU Time、Wait Time的关系
在单核处理器的情况下，Elapsed Time = Cpu Time + Wait Time。
在多核处理器的情况下，由于多个CPU同时处理任务所以可能会出现Cpu Time 大于Elapsed Time 的情况。
创建唯一非聚集索引优缺点
优点：
1、只要每个列中的数据是唯一的，就可以为同一个表创建多个唯一非聚集索引。
2、唯一索引提供帮助查询优化器生成更高效的执行计划的其他信息。
3、唯一索引能够确保定义的列的数据完整性。
4、多列唯一索引能够保证索引键中值的每个组合都是唯一的。
5、可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
6、在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
缺点：
1、创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
2、索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。
3、当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。
全文索引
全文索引 {倒排文档技术}
建立好全文索引后就可以使用SQL语句来查询了，主要用带三个关键字 CONTAINS、FREETEXT、CONTAINSTABLE和FREETEXTTABLE
CONTAINS
搜索单个词和短语的精确或模糊的匹配项，要搜索的内容必须是个有意义的词语，比如说“苹果”、“建设厅”，不能是一些没意义的词语，比如“阿迪撒啊是”,“儿儿的”这样的词语即使LIKE是能查询出来，但全文索引对这样没意义的词语可能没有建立索引，查不出来
FREETEXT
和CONTAINS类似，不同的是它会先把要查询的词语先进行分词然后在查询匹配项
全文索引和普通索引
普通索引的结构主要以B+树和哈希索引为主，用于实现对字段中数据的精确查找，比如查找某个字段值等于给定值的记录，A=10这种查询，因此适合数值型字段和短文本字段
全文索引是用于检索字段中是否包含或不包含指定的关键字，有点像搜索引擎的功能，其内部的索引结构采用的是与搜索引擎相同的倒排索引结构，其原理是对字段中的文本进行分词，然后为每一个出现的单词记录一个索引项，这个索引项中保存了所有出现过该单词的记录的信息，也就是说在索引中找到这个单词后，就知道哪些记录的字段中包含这个单词了。因此适合用大文本字段的查找。
大字段之所以不适合做普通索引，最主要的原因是普通索引对检索条件只能进行精确匹配，而大字段中的文本内容很多，通常也不会在这种字段上执行精确的文本匹配查询，而更多的是基于关键字的全文检索查询，例如你查一篇文章信息，你会只输入一些关键字，而不是把整篇文章输入查询（如果有整篇文章也就不用查询了）。而全文索引正是适合这种查询需求。
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！