[SQL] 索引維護成本

透過執行計畫來觀察 Insert、Update、Delete 時索引維護情況

建立範例資料

USE [AdventureWorks2019]
GO

DROP TABLE IF EXISTS [dbo].[EmployeeWithIndex]
DROP TABLE IF EXISTS [dbo].[Employee]

CREATE TABLE [dbo].[EmployeeWithIndex]
(
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[FullName] [nvarchar](100) NULL,
	[HireDate] [date] NULL,
	CONSTRAINT [PK_EmployeeWithIndex] PRIMARY KEY CLUSTERED ( [ID] ASC)
) 
GO

-- 建立該索引來觀察
CREATE INDEX IX_EmployeeWithIndex_FullName ON EmployeeWithIndex (FullName)
GO

CREATE TABLE [dbo].[Employee]
(
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[FullName] [nvarchar](100) NULL,
	[HireDate] [date] NULL,
	CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ( [ID] ASC)
) 
GO

Insert 觀察

Insert 語法

INSERT INTO EmployeeWithIndex (FullName , HireDate)
	VALUES(N'張三' , '20211223')

INSERT INTO Employee (FullName , HireDate)
	VALUES(N'張三' , '20211223')

從執行計畫成本比較可以看出 EmployeeWithIndex 成本因為要更新 IX_EmployeeWithIndex_FullName 所以成本較高

 

從叢集索引插入 operator 內可以看見 EmployeeWithIndex Insert 時有更新 IX_EmployeeWithIndex_FullName

     

Update 觀察

Update 語法

UPDATE EmployeeWithIndex SET FullName = N'張三 Update' WHERE ID = 1
UPDATE Employee SET Hiredate = '20211224' WHERE ID = 1

更新時跑不同執行計畫，單純閱讀執行計畫差異

從叢集索引更新 operator 內可以看見 EmployeeWithIndex Insert 時有更新 IX_EmployeeWithIndex_FullName

     

Delete 觀察

Delete 語法

DELETE FROM EmployeeWithIndex WHERE ID = 1
DELETE FROM Employee WHERE ID = 1

從執行計畫成本比較可以看出 EmployeeWithIndex 成本因為要更新 IX_EmployeeWithIndex_FullName 所以成本較高

從叢集索引刪除 operator 內可以看見 EmployeeWithIndex Insert 時有更新 IX_EmployeeWithIndex_FullName

 

• 參考資料
• 索引建立時，將欄位放在key中跟用include涵蓋進來有什麼差異？