菜鸟学SQLServer-------------数据库索引

一、SqlServer索引原理

在SQL Server中，索引是一种增强式的存在，这意味着，即使没有索引，SQL Server仍然可以实现应有的功能。

但索引可以在大多数情况下大大提升查询性能，在OLAP中尤其明显.要完全理解索引的概念，需要了解大量原理性的知识，包括B树，堆，数据库页，区，填充因子，碎片,文件组等等一系列相关知识。

简单地说索引类似于一本书的目录，在一本书中使用目录可以快速找到你想要的信息，而不需要读完全书。

索引在数据库中也起到类似的作用，可以帮助数据库快速定位到存储在表中的数据。通过使用索引，可以减少查询时需要扫描的数据量，从而提高查询效率。

 

索引分为:唯一索引、主键索引、聚集索引和非聚集索引。

(1)唯一索引:唯一索引不允许两行具有相同的索引值。

如果现有数据中存在重复的键值，则一般情况下大多数数据库都不允许创建唯一索引.

因为当新数据将使表中的键值重复时，数据库会拒绝接受此数据。

(2)主键索引:在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。

主键索引要求主键中的每个值是唯一的，当在查询中使用主键索引时，它还允许快速访问数据。

(3)聚集索引:在聚集索引中，表中各行的物理顺序与键值的逻辑(索引)顺序相同，表只能包含一一个聚集索引。例如:汉语字典默认按拼音排序编排字典中的每页页码。拼音字母a,b,c,d… ,x,y,Z就是索引的逻辑顺序，而页码1,2,3…就是物理顺序。

默认按拼音排序的字典,其索引顺序和逻辑顺序是一致的。即拼音顺序较后的字对应的页码也较大。如拼音“ha”对应的字(词)页码就比拼音“ba"对应的字(词)页码靠后。（order by按聚集索引列排序效率最高）

(4)非聚集索引:与聚类索引相反，该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

按照定义，除了聚集索引以外的索引都是非聚集索引

 

二、主键和索引

我们在使用SQL Server数据库的时候常常会创建主键和索引，那么主键和索引到底有什么样的不同呢？本文我们主要介绍了主键和索引的区别。

主键和索引的区别如下：

主键是索引，但索引不一定是主键。

主键具有唯一性，而只有唯一性索引才具有唯一性;主键的值不能为空，不能重复。 索引可以在程序中动态创建删除。也可以是任何有序的字段。

 

三、索引创建和使用

我一般使用Navicat工具对数据库进行管理，不过学习好语句也有好处

XML索引：对 xml 数据类型列创建 XML 索引。 它们对列中 XML 实例的所有标记、值和路径进行索引，从而提高查询性能。

Spatial索引：“空间索引” 是一种扩展索引，允许您对空间列编制索引。 空间列是包含空间数据类型（如 geometry 或 geography）的数据的表列。

索引创建语句：

普通索引
CREATE NONCLUSTERED INDEX 索引名 ON 表名(列名)
聚类索引
CREATE CLUSTERED INDEX 索引名 ON 表名(列名)

 

也可以通过重构索引提升查询速度：

ALTER INDEX  索引名 ON 表名 REBUILD

删除不必要的索引

DROP INDEX 索引名 on 表名

四、索引失效

所谓失效。并不真的就是这个索引被删除了。而是在这些情况下，DBMS不会检索索引列表了。执行速度和没有这个索引时的速度一样。

但是再执行另外的一条语句。同样索引可以正常起作用。所以索引的失效是针对某条sql语句的，而不是针对索引本身的。

 也就是说如果不是直接判断索引字段列，而是判断运算或其它函数处理后的索引列。

 

1.索引字段进行判空查询时。也就是对索引字段判断是否为NULL时。语句为is null 或is not null。
  比如：select * from temp where time is null此时就不检索time字段上的索引表了。也就是索引在这条语句执行时失效了。接着再执

行select * from temp where time = '2010-06-01'此时就会检索索引表了。索引又起作用了。

 

2.对索引字段进行like查询时。比如：

select * from temp where des like '%王某某'。

 

3.判断索引列是否不等于某个值时。‘!=’操作符。比如：

select * from temp where amount != 0.

 

4.对索引列进行运算。这里运算包括+-*/等运算。也包括使用函数。比如：
  select * from temp where amount+count>10 此时索引不起作用。
  select * from temp where round(amount)>10 此时索引也不起作用。

避免在查询语句中进行运算。。。

 

5.查询语句中包含对索引字段的隐式转换：比如：

SELECT * FROM [dbo].[T_User] WHERE Brithday='2023-06-03'--此时索引不起作用

SELECT * FROM [dbo].[T_User] WHERE Brithday=CONVERT(datetime,'2023-06-03')---正确的做法是使用显示转换使参数类型与列保持一致，避免隐式转换

 

6.使用or

SELECT * FROM [dbo].[T_User] WHERE Name='小王' OR Name='张三'--此时索引不起作用

SELECT * FROM [dbo].[T_User] WHERE Name='小王'   union   SELECT * FROM [dbo].[T_User] WHERE Name='张三' ---正确的做法是使用

最后在记录一个数据库索引优化原则的文章

https://www.cnblogs.com/newcapecjmc/p/16298688.html