SQL Server 主键 聚集索引 非聚集索引 唯一索引 索引 性能优化

CONTAINS (Transact-SQL)  
索引的EF Core
创建聚集索引
创建非聚集索引
CREATE INDEX
创建主键
创建唯一索引
SQL Server 和 Azure SQL 索引体系结构和设计指南

1、主键

-- 创建数据库表
create table [dbo].[TestTable](
	[TestCol1] [int] identity(1,1) NOT NULL,
	[TestCol2] [nchar](10) NULL,
	[TestCol3] [nvarchar](50) NULL,
	[TestCol4] [nvarchar](50) NULL,
	[TestCol5] [nvarchar](50) NULL,
	[TestCol6] [nvarchar](50) NULL
)
go

-- 修改主键语法
alter table [dbo].[表名] add constraint [主键名称] primary key clustered (列名)

-- 修改主键实例
alter table [dbo].[TestTable] add constraint PK_TestTable_TestCol1 primary key clustered (TestCol1)

-- 删除主键语法
alter table [dbo].[表名] drop constraint [PK_表名_列名]

-- 删除主键实例
alter table [dbo].[TestTable] drop constraint [PK_TestTable_TestCol1]

2、唯一索引【unique index】

-- 语法
create unique index [AK_表名_列名] on [dbo].[表名] (列名)

-- 实例
create unique index [AK_TestTable_TestCol6] on [dbo].[TestTable] (TestCol6)

3、聚集索引【clustered index】

-- 语法
create clustered index [索引名称] on [dbo].[表名](列名)

-- 语法
create clustered index [IX_表名_列名] on [dbo].[表名](列名)

-- 实例
create clustered index [IX_TestTable_TestCol1] on [dbo].[TestTable](TestCol1)
go

● 创建主键的列，默认添加一个聚集索引，一个表只能有一个聚集索引；
● 主键的作用是为了唯一标识表中的某一行，而索引的作用是为了提高查询效率；
● 聚集索引是一种对【磁盘】上实际数据重新组织以按指定的一列或多列值排序；
● 如同一本书的目录，我们可以通过目录取查找我们的需要的内容，如果没有目录索引，会需要扫描整本书来查找，效率相比可知建立聚集索引的好处；
● 一张表【只能有一个】聚集索引，如果表中含有主键，这个主键默认是这张表的聚集索引。

实例

-- 创建索引
create clustered index [IX_TestTable_TestCol1] on [dbo].[TestTable](TestCol1)
go

-- 查询索引
exec sp_helpindex [表名]
exec sp_helpindex [TestTable]

-- 删除索引
-- 语法：drop index [索引名] on [dbo].[表名]
if exists(select name from sys.indexes where name = N'IX_TestTable_TestCol1') 
begin
    drop index [IX_TestTable_TestCol1] on [dbo].[TestTable]
	print 'IX_TestTable_TestCol1 删除成功'
end
else
begin
    print 'IX_TestTable_TestCol1 不存在'
end

-- 删除索引
-- 语法：drop index [dbo].[表名].[索引名]
if exists(select name from sys.indexes where name = N'IX_TestTable_TestCol2') 
begin
    drop index [dbo].[TestTable].[IX_TestTable_TestCol2]
	print 'IX_TestTable_TestCol2 删除成功'
end
else
begin
    print 'IX_TestTable_TestCol2 不存在'
end

4、非聚集索引【nonclustered indexes】

-- 语法
create nonclustered index [索引名称] on [dbo].[表名](列名)

-- 实例
create nonclustered index [IX_TestTable_TestCol2] on [dbo].[TestTable](TestCol2)
go

5、普通索引

-- 语法1
create index [索引名称] on [dbo].[表名](列名)
-- 语法2
create index [索引名称] on [dbo].[表名](列名1,列名2,列名3)

-- 实例1
create index [IX_TestTable_TestCol3] on [dbo].[TestTable](TestCol3)
-- 实例2
create index [IX_TestTable_TestCol4] on [dbo].[TestTable](TestCol4,TestCol5)

6、删除索引

-- 语法1
drop index [索引名] on [dbo].[表名]

-- 语法2
drop index [dbo].[表名].[索引名]

-- 查询索引语法1
exec sp_helpindex [表名]

-- 删除索引
if exists(select name from sys.indexes where name = N'IX_TestTable_TestCol1') 
begin
    drop index [IX_TestTable_TestCol1] on [dbo].[TestTable]
	print 'IX_TestTable_TestCol1 删除成功'
end
else
begin
    print 'IX_TestTable_TestCol1 不存在'
end

-- 删除索引语法2
if exists(select name from sys.indexes where name = N'IX_TestTable_TestCol2') 
begin
    drop index [dbo].[TestTable].[IX_TestTable_TestCol2]
	print 'IX_TestTable_TestCol2 删除成功'
end
else
begin
    print 'IX_TestTable_TestCol2 不存在'
end

7、使用索引

-- 语法
select * from [dbo].[表名] with(index(索引名)) where 索引列 = 1

-- 实例
select * from [dbo].[TestTable] with(index(IX_TestTable_TestCol1)) where TestCol1 = 1

=========================================================================

1.创建索引时遵循一般原则（利弊）
  在需要经常搜索的列上面创建索引
  在主键上创建索引
  在外键上创建索引
  在经常用于Where子句的列上创建索引
  在经常需要排序order by、group by、distinct 的列上创建索引
  对于数据类型为text，image，bit的列不应该增加索引
  当需要Update性能远高于Select性能时不应该增加索引
  一个或极少不同值不应该创建索引
  对于经常存取的列避免建立索引
  限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作
*
2.索引分类
全文索引：contains【select * from student where contains([username],'"*张三*"')】
聚集索引：物理存储“按照”索引排序；每个表【只能有一个】聚集索引；
非聚集索引：物理存储“不按照”索引排序，每个表【可以有多个】非聚集索引；
唯一索引：要求创建索引的关键字段值在表中不能有重复值；
主键索引：为表定义一个主键将自动创建一个主键索引；（主键索引是唯一索引的特殊类型）
复合索引：对表创建的索引是基于过个字段对表中的记录排序的；
FILLFACTOR(填充因子)：0~100范围内的值，指示索引页填满的空间所占的百分比。（一般很少指定）
3.创建索引3种方法
(1)用企业管理器创建索引；
(2)利用Transact-SQL语句创建索引；
(3)利用索引优化向导创建索引；
第一种：用企业管理器创建索引(略)
第二种：利用Transact-SQL语句创建索引
语法：
CREATE [索引类型] INDEX 索引名称
ON 表名(列名)
WITH FILLFACTOR = 填充因子值0~100
GO
/*实例*/
USE 库名
GO
IF EXISTS (SELECT * FROM SYSINDEXES WHERE NAME='IX_TEST_TNAME')--检测是否已经存在IX_TEST_TNAME索引
DROP INDEX TEST.IX_TEST_TNAME--如果存在则删除
--创建索引
CREATE NONCLUSTERED INDEX IX_TEST_TNAME --创建一个非聚集索引
ON TEST(TNAME)  --为TEST表的TNAME字段创建索引
WITH FILLFACTOR = 30 --填充因子为30%
GO
SELECT * FROM TEST(INDEX = IX_TEST_TNAME) WHERE TNAME = 'A' --指定按‘IX_TEST_TNAME’索引查询
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
其语法形式如下：
CREATE [UNIQUE] [CLUSTERED| NONCLUSTERED ]

INDEX index_name ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )

[with

[PAD_INDEX]

[[,]FILLFACTOR=fillfactor][[,]IGNORE_DUP_KEY]

[[,]DROP_EXISTING]

      [[,]STATISTICS_NORECOMPUTE]

[[,]SORT_IN_TEMPDB]

]

[ ON filegroup ]

CREATE INDEX：命令创建索引各参数说明如下：
UNIQUE：用于指定为表或视图创建唯一索引，即不允许存在索引值相同的两行。
CLUSTERED：用于指定创建的索引为聚集索引。
NONCLUSTERED：用于指定创建的索引为非聚集索引。
index_name：用于指定所创建的索引的名称。
table：用于指定创建索引的表的名称。
view：用于指定创建索引的视图的名称。
ASC|DESC：用于指定具体某个索引列的升序或降序排序方向。
Column：用于指定被索引的列。
PAD_INDEX：用于指定索引中间级中每个页（节点）上保持开放的空间。
FILLFACTOR = fillfactor：用于指定在创建索引时，每个索引页的数据占索引页大小的百分比，fillfactor的值为1到100。
IGNORE_DUP_KEY：用于控制当往包含于一个唯一聚集索引中的列中插入重复数据时SQL Server所作的反应。
DROP_EXISTING：用于指定应删除并重新创建已命名的先前存在的聚集索引或者非聚集索引。
STATISTICS_NORECOMPUTE：用于指定过期的索引统计不会自动重新计算。
SORT_IN_TEMPDB：用于指定创建索引时的中间排序结果将存储在 tempdb 数据库中。
ON filegroup：用于指定存放索引的文件组。

例子1：为表employees创建了一个唯一聚集索引，其程序清单如下：

CREATE UNIQUE CLUSTERED INDEX number_ind

   ON employees (number)

with

   pad_index,

   fillfactor=20,

   ignore_dup_key,

   drop_existing,

   statistics_norecompute

例子2：为表employees创建了一个复合索引，其程序清单如下：

create index employees_cpl_ind

on employees(name,age)

with

   pad_index,

   fillfactor=50