SQLSERVER聚集索引与非聚集索引的再次研究（上）

林译川

2026-10-18 • 👁 0人浏览过

 1 CREATE TABLE DBCCResult (
 2 PageFID NVARCHAR(200),
 3 PagePID NVARCHAR(200),
 4 IAMFID NVARCHAR(200),
 5 IAMPID NVARCHAR(200),
 6 ObjectID NVARCHAR(200),
 7 IndexID NVARCHAR(200),
 8 PartitionNumber NVARCHAR(200),
 9 PartitionID NVARCHAR(200),
10 iam_chain_type NVARCHAR(200),
11 PageType NVARCHAR(200),
12 IndexLevel NVARCHAR(200),
13 NextPageFID NVARCHAR(200),
14 NextPagePID NVARCHAR(200),
15 PrevPageFID NVARCHAR(200),
16 PrevPagePID NVARCHAR(200)
17 )

1 --只有聚集索引
2 CREATE TABLE Department(
3     DepartmentID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
4     Name NVARCHAR(200) NOT NULL,
5     GroupName NVARCHAR(200) NOT NULL,
6     Company NVARCHAR(300),
7     ModifiedDate datetime NOT NULL  DEFAULT (getdate())
8 )

1 INSERT INTO Department(name,[Company],groupname) VALUES('销售部','中国你好有限公司XX分公司','销售组')
2 GO 100000

1 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,Department,-1) ')

 1 DBCC TRACEON(3604,-1)
 2 GO
 3 
 4 DBCC PAGE([pratice],1,3527,3)
 5 GO
 6 
 7 
 8 DBCC PAGE([pratice],1,3528,3)
 9 GO
10 
11 DBCC PAGE([pratice],1,14464,3)
12 GO

 1 --只有聚集索引
 2 CREATE TABLE Department2(
 3     DepartmentID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
 4     Name NVARCHAR(200) NOT NULL,
 5     GroupName NVARCHAR(200) NOT NULL,
 6     Company NVARCHAR(300),
 7     ModifiedDate datetime NOT NULL  DEFAULT (getdate())
 8 )
 9 
10 INSERT INTO Department2(name,[Company],groupname) VALUES('销售部','中国你好有限公司XX分公司','销售组')
11 GO 100000
12 
13 
14 SELECT * FROM Department2
15 
16 --先清空[DBCCResult]表里的记录
17 --TRUNCATE TABLE [dbo].[DBCCResult]
18 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,Department2,-1) ')
19 
20 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC 
21 
22 DBCC PAGE([pratice],1,14471,3)
23 GO
24 
25 DBCC PAGE([pratice],1,4375,3)
26 GO
27 DBCC PAGE([pratice],1,4376,3)
28 GO

 1 USE [pratice]
 2 GO
 3 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
 4 GO
 5 --以下查询使用了聚集索引查找 ctrl+l
 6 BEGIN TRAN
 7 SELECT GroupName FROM [dbo].[Department2]  WHERE DepartmentID IN(32641,361,32281) 
 8 
 9 --COMMIT TRAN
10 
11 USE [pratice] --要查询申请锁的数据库
12 GO
13 SELECT
14 [request_session_id],
15 c.[program_name],
16 DB_NAME(c.[dbid]) AS dbname,
17 [resource_type],
18 [request_status],
19 [request_mode],
20 [resource_description],OBJECT_NAME(p.[object_id]) AS objectname,
21 p.[index_id]
22 FROM sys.[dm_tran_locks] AS a LEFT JOIN sys.[partitions] AS p
23 ON a.[resource_associated_entity_id]=p.[hobt_id]
24 LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id]=c.[spid]
25 WHERE c.[dbid]=DB_ID('pratice') AND a.[request_session_id]=@@SPID  ----要查询申请锁的数据库
26 ORDER BY [request_session_id],[resource_type]

 1 --只有聚集索引
 2 CREATE TABLE Department2(
 3     DepartmentID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
 4     Name NVARCHAR(200) NOT NULL,
 5     GroupName NVARCHAR(200) NOT NULL,
 6     Company NVARCHAR(300),
 7     ModifiedDate datetime NOT NULL  DEFAULT (getdate())
 8 )
 9 
10 DECLARE @i INT
11 SET @i=1
12 WHILE @i < 100000 
13     BEGIN
14         INSERT  INTO Department3 ( name, [Company], groupname )
15         VALUES  ( '销售部', '中国你好有限公司XX分公司'+CAST(@i AS VARCHAR(200)), '销售组'+CAST(@i AS VARCHAR(200)) )
16         SET @i = @i + 1
17     END
18 
19 
20 SELECT * FROM Department2

 1 USE [pratice]
 2 GO
 3 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
 4 GO
 5 --以下查询使用了聚集索引查找 ctrl+l
 6 BEGIN TRAN
 7 SELECT * FROM [dbo].[Department2]  WHERE [GroupName] ='销售组83421'
 8 
 9 --COMMIT TRAN
10 
11 USE [pratice] --要查询申请锁的数据库
12 GO
13 SELECT
14 [request_session_id],
15 c.[program_name],
16 DB_NAME(c.[dbid]) AS dbname,
17 [resource_type],
18 [request_status],
19 [request_mode],
20 [resource_description],OBJECT_NAME(p.[object_id]) AS objectname,
21 p.[index_id]
22 FROM sys.[dm_tran_locks] AS a LEFT JOIN sys.[partitions] AS p
23 ON a.[resource_associated_entity_id]=p.[hobt_id]
24 LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id]=c.[spid]
25 WHERE c.[dbid]=DB_ID('pratice') AND a.[request_session_id]=@@SPID  ----要查询申请锁的数据库
26 ORDER BY [request_session_id],[resource_type]

 1 --只有聚集索引
 2 CREATE TABLE Department3(
 3     DepartmentID int IDENTITY(1,1) NOT NULL ,
 4     Name NVARCHAR(200) NOT NULL,
 5     GroupName NVARCHAR(200) NOT NULL,
 6     Company NVARCHAR(300),
 7     ModifiedDate datetime NOT NULL  DEFAULT (getdate())
 8 )
 9 
10 CREATE CLUSTERED INDEX CL_DepartmentID ON [dbo].[Department3]([DepartmentID])
11 
12 DECLARE @i INT
13 SET @i=1
14 WHILE @i < 100000 
15     BEGIN
16         INSERT  INTO Department3 ( name, [Company], groupname )
17         VALUES  ( '销售部', '中国你好有限公司XX分公司'+CAST(@i AS VARCHAR(200)), '销售组'+CAST(@i AS VARCHAR(200)) )
18         SET @i = @i + 1
19     END
20 
21 
22 SELECT * FROM Department3
23 
24 --TRUNCATE TABLE [dbo].[DBCCResult]
25 
26 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,Department3,-1) ')
27 
28 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC 
29 
30 DBCC PAGE([pratice],1,13861,3)
31 GO

 1 --只有聚集索引
 2 CREATE TABLE Department4  --包含复合主键DepartmentID 和Name
 3 (
 4   DepartmentID INT IDENTITY(1, 1) NOT NULL ,
 5   Name NVARCHAR(200) NOT NULL ,
 6   GroupName NVARCHAR(200) NOT NULL ,
 7   Company NVARCHAR(300) ,
 8   ModifiedDate DATETIME NOT NULL DEFAULT ( GETDATE() ) ,
 9   CONSTRAINT [PK_Department4_1] PRIMARY KEY CLUSTERED
10     ( DepartmentID ASC,
11       Name ASC )
12     WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
13            ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
14 ) ON [PRIMARY]
15 
16 
17 DECLARE @i INT
18 SET @i=1
19 WHILE @i < 100000 
20     BEGIN
21         INSERT  INTO Department4 ( name, [Company], groupname )
22         VALUES  ( '销售部'+CAST(@i AS VARCHAR(200)), '中国你好有限公司XX分公司', '销售组' )
23         SET @i = @i + 1
24     END
25 
26 
27 SELECT * FROM [dbo].[Department4]
28 
29 
30 
31 --TRUNCATE TABLE [dbo].[DBCCResult]
32 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,Department4,-1) ')
33 
34 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC 
35 
36 DBCC PAGE([pratice],1,7102,3)
37 GO

1 SELECT * FROM [dbo].[Department4] WHERE name='销售部6'  --聚集索引扫描  因为name不是复合主键中的第一个字段
2 SELECT * FROM [dbo].[Department4] WHERE name='销售部241' AND [DepartmentID]=241  --聚集索引查找
3 SELECT * FROM [dbo].[Department4] WHERE [DepartmentID]=241   --聚集索引查找

 1 --只有聚集索引
 2 USE [pratice]
 3 GO
 4 CREATE TABLE Department5
 5 (
 6   DepartmentID INT IDENTITY(1, 1) NOT NULL ,
 7   Name NVARCHAR(200) NOT NULL ,
 8   GroupName NVARCHAR(200) NOT NULL ,
 9   Company NVARCHAR(300) ,
10   ModifiedDate DATETIME NOT NULL  DEFAULT ( GETDATE() ) 
11 )
12 
13 CREATE CLUSTERED INDEX CL_Company ON [dbo].[Department5]([Company] ASC)
14 
15 --DROP TABLE [dbo].[Department5]
16 
17 DECLARE @i INT
18 SET @i=1
19 WHILE @i < 10000
20     BEGIN
21         INSERT  INTO Department5 ( name, [Company], groupname )
22         VALUES  ( '销售部'+CAST(@i AS VARCHAR(200)), '中国你好有限公司XX分公司', '销售组' )
23         SET @i = @i + 1
24     END

1 --TRUNCATE TABLE [dbo].[DBCCResult]
2 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,Department5,-1) ')
3 
4 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC 
5 
6 DBCC PAGE([pratice],1,14516,3)
7 GO

 1 SET STATISTICS TIME ON
 2 SELECT * FROM [dbo].[Department5] WHERE [Company]='中国你好有限公司XX分公司' AND [DepartmentID]=241 
 3 
 4 SQL Server 分析和编译时间: 
 5    CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
 6 
 7 (1 行受影响)
 8 
 9 SQL Server 执行时间:
10    CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

1 SET STATISTICS TIME ON
2 SELECT * FROM [dbo].[Department5] WHERE name='销售部106' AND [DepartmentID]=106  --聚集索引扫描
3 SQL Server 执行时间:
4    CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

相关文章