SQL Server 创建索引原则主键聚集索引非聚集索引唯一索引性能优化

KingCruel

已于 2025-01-13 14:36:48 修改

阅读量2.3k

点赞数 2

CC 4.0 BY-SA版权

分类专栏： SQL Server 文章标签： sql server 索引

于 2012-09-10 07:59:23 首次发布

本文链接：https://blog.youkuaiyun.com/KingCruel/article/details/7961901

SQL Server 专栏收录该内容

36 篇文章

订阅专栏

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

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范围内的值，指示索引页填满的空间所占的百分比。（一般很少指定）

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

-- 查询索引(查看某个数据库所有索引)
SELECT
    i.name AS IndexName,
    OBJECT_NAME(i.object_id) AS TableName,
    i.index_id,
    i.type_desc,
    i.is_primary_key,
    i.is_unique,
    i.is_disabled
FROM sys.indexes AS i
INNER JOIN sys.tables AS t ON i.object_id = t.object_id
WHERE i.type_desc != 'HEAP'
ORDER BY TableName,IndexName;

-- 查询索引
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)
-- 语法3
create index [索引名称] on [dbo].[表名](列名) WITH (ONLINE = ON)
# with (online = on) 表示在创建索引的同时允许在线操作

-- 创建索引
if exists(select name from sys.indexes where name = N'IX_TestTable_TestCol1') 
    begin
        print 'IX_TestTable_TestCol1 已存在'
    end
else
    begin
        create index [IX_TestTable_TestCol1] on [dbo].[TestTable](TestCol1)
        print 'IX_TestTable_TestCol1 创建成功'
    end

-- 实例1
create index [IX_TestTable_TestCol3] on [dbo].[TestTable](TestCol3)
-- 实例2
create index [IX_TestTable_TestCol4] on [dbo].[TestTable](TestCol4,TestCol5)
-- 实例3
create index [IX_TestTable_Column66] on [dbo].[TestTable](column66) with (online = on)

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

8、查询索引 SQL Server 性能优化 SQL Server Profiler 数据库引擎优化顾问

use [dbTest]
go

-- 查询某个表的所有索引
exec sp_helpindex [表名]
exec sp_helpindex Test
go

SELECT
    --object_name(i.object_id) as TableName,
	t.name as TableName,
	t.type as t_type,
	t.type_desc t_type_desc,
	i.name as IndexName,
    c.name as ColumNname,
    i.type_desc as IndexType,
    i.is_primary_key as IsPrimaryKey,
    i.is_unique as IsUnique,
    i.is_disabled as IsDisabled
FROM sys.indexes i
inner join sys.index_columns ic on i.object_id = ic.object_id and i.index_id = ic.index_id
inner join sys.tables as t on i.object_id = t.object_id
inner join sys.columns c on ic.object_id = c.object_id and c.column_id = ic.column_id
WHERE 1=1
--and i.object_id = object_id('test')                        --如果查询某个表的数据可以加上此条件
--and i.object_id in (object_id('test1'),object_id('test2')) --如果查询多个表的数据可以加上此条件
ORDER BY t.name,i.name
go

SELECT TOP 100 * FROM sys.databases WHERE name = 'dbTest';
SELECT TOP 100 * FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');
SELECT TOP 100 * FROM sys.objects   --记录所有object_id对象，包含表u、视图v、触发器tr、存储过程p等各类型的数据（对象名、object_id、类型、创建时间）
SELECT TOP 100 * FROM sys.tables    --记录用户新建的表（表名、object_id、创建时间、列数量）
SELECT TOP 100 * FROM sys.indexes   --记录所有表对应的索引index_id（object_id、索引名称、index_id、聚集类型、是否唯一）
SELECT TOP 100 * FROM sys.index_columns  --记录索引index_id以及加索引的列column_id的关系（object_id、index_id、index_column_id、column_id）
SELECT TOP 100 * FROM sys.columns    --记录列层面明细（object_id、列名、column_id、最大长度、列类型）
go

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

创建索引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

*
*
*

SQL Server 创建索引原则 主键 聚集索引 非聚集索引 唯一索引 性能优化

SQL Server 创建索引原则主键聚集索引非聚集索引唯一索引性能优化