SQL Server（七）—— 索引

索引 的分类

** 没有索引的表称属于堆

• 根据存储方式不同划分：
  1、聚集索引：数据按顺序存储，聚集索引的叶子节点不仅包括索引键，还包括数据页。每个表只能有一个聚集索引，有聚集索引的表称为聚集表。适合使用聚集索引的列：被大范围搜索的主键、返回大结果集、用于许多查询的列、强选择列、ORDER BY或GROUP BY查询的列、表级联的列
  2、非聚集索引：非聚集索引的数据与索引页面分开存放，索引独立于数据，如果这个非聚集索引已包含了TSQL查询需要返回的所有子弹，不然所有就需要根据非聚集索引中叶子节点的书签定位到实际的数据页，才能返回所需要的字段值。使用使用非聚集索引的列

• 根据功能和效果划分：
  1、全文索引：可对char、varchar、nvarchar、varbinary(max)、image 列创建全文索引
  2、XML索引：支持 xml 列
  3、空间索引：支持 geography 列
  4、唯一索引：索引键中不包含重复的值。使用关键字 UNIQUE
  5、非唯一索引：
  6、单列索引：索引只选择了单个列
  7、复合索引：在创建索引时如果选择了多个列，那么这个索引被称为复合索引。创建复合索引时索引列的排列顺序与其是否被使用于检索数据是息息相关的
  8、包含性索引：把字段包含进索引，通过非键列添加到非聚集索引的叶集来扩展非聚集索引的功能。关键字是 INCLUDE。
  9、索引覆盖：更多地是反映一个索引的使用情况。T-SQL 查询时
  10、筛选索引：在非聚集索引的基础上添加筛选条件
  11、计算列索引：
  12、索引视图：WITH SCHEMABINDING(基础数据很少更新时性能更优)
  13、列存储索引：

一、创建索引

CREATE INDEX IX_product_name
	ON product(product_name)  -- 创建的索引默认为升序排列
CREATE INDEX IX_product_name_age
	ON product(name,age)  -- 创建复合索引
CREATE UNIQUE INDEX IX_product_name 
	ON product(name) -- 创建唯一索引
CREATE CLUSTERED INDEX IX_id
	ON product(id) -- 创建聚集索引
CREATE NONCLUSTERED INDEX IX_product_name
	ON product(name desc) -- 创建非聚集索引，倒叙排序
CREATE INDEX IX_product_price_amount
	ON [order](price,amount)
	INCLUDE (discount) -- 创建包含性列索引
-- 创建索引并制定其所在文件组
ALTER DATABASE demo ADD FILEGROUP new_file_group
GO
ALTER DATABASE demo 
	ADD FILE(NAME = new_file,
		FILENAME = 'D:\db\demo_new_file.ndf')
		TO FILEGROUP new_file_group
GO
CREATE INDEX IX_fg 
	ON product(store) 
	ON new_file_group
	WITH(
		PAD_INDEX = ON, --启用填充索引
		FILLFACTOR = 60 -- 创建填充因子
		SORT_IN_TEMPDB = ON, -- 在tempdb存储临时排序信息
		IGNORE_DUP_KEY = ON, -- 忽略索引的重复键值
		STATISTICS_NORECOMPUTE = OFF -- 关闭自动重新计算统计信息
	)
GO
CREATE NONCLUSTERED INDEX NC_employee_ssn
ON dbo.employee(ssn DESC)
WHERE employee_id > 100
WITH(
	ONLINE = ON,
	MAXDOP = 1
)--局部索引
GO

二、查看索引信息

EXEC sp_helpindex 'product'
SELECT * FROM sys.indexes
DBCC INDEX
DBCC TRACEON
DECLARE @databaseid INT
DECLARE @objectid INT
SET @databaseid = DB_ID(N'demo')
SET @objectid = OBJECT_ID(N'employee')
-- 查看所有所有的碎片信息
SELECT * FROM sys.dm_db_index_physical_stats
(@databaseid,@objectid,null,null,null)

三、修改索引

ALTER INDEX IX_product_name 
	ON product REBULD -- 重新生成索引，会删除原有索引
	WITH(   -- 设置索引填充
		PAD_INDEX= ON,
		FILLFACTOR = 70)
GO
ALTER INDEX IX_product_name
	ON product REORGANIZE -- 重新组织索引，不删除原有索引
ALTER INDEX PK_prodcut
	ON product
	DISABLE -- 禁用索引
GO

四、删除索引

DROP INDEX product.IX_product_name

五、索引视图

索引视图已经将视图实体化，视图里的查询语句的查询结果是保存在数据库的物理存储空间中的。创建索引视图的前提有：视图必须绑定到架构已经视图的索引里必须有一个是唯一的聚集索引。

CREATE VIEW view_order
	WITH SCHEMABINDING -- 架构绑定
AS 
SELECT e.name,o.id,o.amount,o.payment,o.date FROM 
		employee e JOIN [order] o
		ON e.id = o.employee_id
GO
CREATE UNIQUE CLUSTERED INDEX IX_order
	ON [order](id)
GO

六、全文索引

全文索引时一种特殊的索引，能在给定的列中存储有关重要的词及位置的信息，使用这些信息可以快速进行全文查询，搜索包括特定词和词组的行。
　　全文索引与普通索引不同，普通索引时以B-tree结构来维护的，而全文索引时一种特殊类型的基于标记的功能性索引，由 SQL Server 全文索引引擎服务创建和维护。
　　全文目录：全文目录是存储全文索引的地方。全文目录必须驻留在于 SQL Server 实例相关联的本地硬盘上。每个全文目录可用于满足数据库内的一个或多个表的索引需求。
　　断字符与词干分析器：断字符与词干分析器用于对全文索引的数据进行语言分析。语言分析通常会涉及到查找词的边界和组合动词两个方面。
　　标记：由断字符标识的词或字符串。
　　筛选器：用于从存储在 varbinary(max)或image列中的文件内提取指定文本类型的文本。当 varbinary(max) 或 image 列中包含带有特定文件扩展名的文档时，全文搜索会使用筛选器来解释二进制数据。筛选器会从文档中提取文本化信息并建立索引。
　　填充（爬网）：创建维护全文索引的过程叫填充，也叫爬网。
　　干扰词：经常出现但又不是要搜索的词。
　　创建全文索引的注意事项：
　　a、只能对数据表创建全文索引；
　　b、一个数据库可以创建多个全文目录，每个全文目录都可以存储一个或多个全文索引，一个数据表只能创建一个全文索引。一个全文索引可以包含多个字段；
　　c、要创建全文索引的表必须有一个唯一的针对单列的非空索引；
　　d、包含在全文索引里的字段只能是字符型或 image 型的字段。

使用全文索引

SELECT * FROM article
	WHERE CONTAINS(detail,'"广州" OR "上海"') -- 简单此搜索方式
SELECT * FROM article
	WHERE CONTAINS(detail,'FORMSOF(INFLECTIONAL,download)') -- 派生词搜索
SELECT * FROM article
	WHERE CONTAINS(detail,'"color*" ') -- 前缀词搜索方式，只支持 * 符并且* 只能放在英文字母之后，搜索前缀为color的词，以空格符分割。
SELECT * FROM article 
	WHERE CONTAINS(detial,
		'ISABOUT ("download" weight(0.9),
		"上海" weight(0.6),
		"山西" weight(0.5))') -- 加权词搜索
SELECT * FROM article
	WHERE CONTAINS(detail,'"广东省" NEAR "社保"') -- 临近词搜索
SELECT * FROM article
	WHER FREETEXT(detail,'SQL Server') -- FREETEXT 搜索会将字符串断字成SQL、Server等，只要存在其中之一就匹配，因此使用 FREETEXT 搜索会比 CONTAINS 搜索查询到记录多
SELECT * FROM article JOIN
	CONTRAINSTABLE(article,detail,'"" NEAR ""'，10) as table1 
	ON article.id = table1.[KEY]
	ORDER BY table1.RANK DESC -- 使用 CONTAINSTABLE 函数搜索
SELECT id,title,file,ext FROM article
	WHERE CONTAINS(file,'sql server') -- 搜索 image 类型，只支持 txt，doc，ppt，等文件类型

操作全文索引

-- 先创建全文索引目录
CREATE FULLTEXT CATALOG TSQLFUllCatalog
	ON FILEGROUP [FRIMARY]
	IN PATH 'C:\db\fulltext_catalog\'
	AS DEFAULT -- 设为默认目录
ALTER FULLTEXT CATALOG TSQLFullCatalog
	REBUILD -- 重新生成全文目录	
DROP FULLTEXT CATALOG TSQLFullCatalog -- 删除全文目录，只有当全文中没有全文索引才能删除


DROP FULLTEXT INDEX ON article -- 删除 article 表上的全文索引
CREATE FULLTEXT INDEX
	ON article(content)
	KEY INDEX PK_article_id
	ON TSQLFUllCatalog -- 创建全文索引
ALTER FULLTEXT INDEX ON article DISABLE -- 禁用 article 表的全文索引
ALTER FULLTEXT INDEX ON article ENABLE -- 启用 article 表
ALTER FULLTEXT INDEX ON article 
	DROP (FILE) -- 将 file 字段从全文索引内删除