SQL删除语句的幕后花絮

迪鲁宾
829 阅读8分钟

在这篇文章中，我们将探讨执行SQL删除语句时幕后发生的细节。

简介

SQL删除语句是数据库操作命令（DML）之一，用于从表中删除记录。 当我们执行删除语句时，数据库引擎将执行许多活动，但有些因素会影响SQL删除语句的锁定和性能行为。

  • 表中索引的数量
  • 可怜的索引外键
  • 锁的升级模式
  • 隔离级别
  • 删除语句的批量大小
  • 触发器(s)
  • 时间性的表

由于这个原因，了解删除语句的后台情况将有助于发现关于删除操作的锁和性能问题。

SQL删除语句对堆表的影响

没有聚类索引的表被称为堆表。堆表的结构不允许以任何特定的顺序对存储的行进行排序。另一方面，当我们从有聚类索引的表中删除一条记录时，空页将被取消分配并交给数据库，但这种工作机制对于堆表来说有点不同。让我们通过一个例子来看看堆表的删除操作的后台。首先，我们将创建一个堆表并向其中插入一些行。

创建 TABLE HeapTable_Test

(

ColId INT NOT NULL ,

Col1 CHAR(8000) NOT NULL DEFAULT ('Heap table test data')

);

declare @i as int=1

WHILE @I <=8000 BEGIN INSERT INTO HeapTable_Test VALUES(@I,DEFAULT) SET @I=@I+1 END

在向表中插入一些数据后，我们将检查这个表分配了多少页。我们可以使用sys.dm_db_database_page_allocations 动态管理函数来返回与表有关的数据页。

SELECT

page_id = allocated_page_page_id,

index_id,

page_type_desc ,page_free_space_percent,is_allocated ,

上一页_页面_id,下一页_页面_id

FROM sys.dm_db_database_page_allocations

(

DB_ID()。

OBJECT_ID(N'dbo.HeapTable_Test')。

NULL。

NULL。

N'DETAILED'

) WHERE is_allocated = 1;

Heap table allocated page

我们可以看到8000个页面被这个表直接分配。例如，当我们执行下面的查询时，IO统计报告将显示8000页的读取操作被执行。

set statistics io on

GO

SELECT * FROM HeapTable_Test

SQL Server IO statistics 现在，让我们删除1/3的样本表，并重新检查这次删除操作后分配了多少页。

DELETE HeapTable_Test WHERE ColId % 3 = 0;

Heap table unallocated data pages

我们可以看到，被删除的行的页仍然被堆表分配。为了解决这个问题，我们可以使用以下方法。

  • 在SQL删除语句中使用TABLOCK提示
  • 使用ALTER TABLE"heap table name"REBUILD 命令
  • 在堆表上创建并丢弃一个聚类索引

提示。 创建和丢弃聚类索引是一种非常昂贵的、不可取的技术，用于删除堆表中的空页。在这个技术中，如果堆表包含非聚类索引，将执行以下操作。

  • 创建一个聚类索引将更新所有非聚类索引对聚类键的指针。
  • 丢弃一个聚类索引将更新所有非聚类索引指向堆表行标识符（RID）的指针

SQL删除语句的执行计划

当我们执行一个SQL删除语句时，查询优化器会生成一个执行计划，但是由于触发器、外键、时态表等的存在，这个执行计划可能比我们预期的要复杂。下面的查询从WorkOrder表中删除了一条记录。这个表的主键列是从WorkOrderRouting 表中引用的外键。

DELETE FROM Production.WorkOrder WHERE WorkOrderID = 1

Execution plan of a delete command

在执行计划的右上角，可以看到一个聚类索引删除操作符，这个操作符的任务是从聚类索引中删除记录。

第二个操作符是聚类索引查找操作符，这个操作符被执行是因为WorkOrder和WorkOrderRouting表之间有一个父子关系。由于父-子关系，SQL Server需要在子表中执行删除行的存在性检查。嵌套的左半连接返回左输入的所有行（已删除的行），无论右输入中是否存在匹配的行。

Assert操作符的任务是验证条件，当它返回一个不同于NULL的值时，查询将返回一个错误。对于我们的例子，断言运算符返回NULL，因为在WorkOrderRouting表中没有与被删除的记录相匹配的记录。

SQL删除语句执行计划

当多个用户想同时访问同一个数据时，数据库引擎使用锁机制来确保数据的完整性。在删除操作中，锁管理器会锁定必要的对象，采取的锁类型取决于事务的隔离级别。现在，让我们来分析一下，在执行一个SQL删除语句的例子中，哪些对象将被锁定。Extended Event是一个非常有用的工具，可以监控SQL Server中的事件，我们将使用这个工具来监控哪些对象在我们的示例查询执行过程中获得了锁。

首先，我们将创建一个事件会话并选择lock_acquired、lock_relased 和rollback_tran_completed事件。

Create an extended event session

在第二步，我们将点击配置按钮并过滤事件数据。对于我们的会话，我们将只过滤独占（X）和打算独占（IX）的锁，同时也过滤会话ID。

Filtering the extended events

在最后一步，我们将配置存储事件数据的目标存储。对于这个例子，我们将选择event_file，这样事件数据就会被存储在一个物理文件中。

Setting the extended event storage type for a SQL delete statement

创建会话后，它将出现在扩展事件会话下。右键单击该事件并选择观察实时数据菜单。

SQL extended event and watch live data

现在，我们将执行下面的查询，并尝试分析哪些对象将在执行删除语句时获得锁。

DELETE FROM Production.WorkOrder where WorkOrderID =2

Understand the lock types of a delete command

现在让我们逐行了解捕获的事件数据。

1-事件数据中的resource_type列表示WorkOrder表。WorkOrder表获得了一个意图独占锁。

SELECT OBJECT_NAME(1502628396) As Object_Name

Find object name in SQL Server

2- 集群索引页获得了一个意图独占锁。

SELECT OBJECT_NAME(p.object_id) AS objectname,

(SELECT name

FROM sys.indexes AS i

WHERE i.object_id = p.object_id

AND i.index_id = p.index_id) AS indexName

FROM sys.partitions AS p

WHERE p.hobt_id = 72057594052411392。

Find index name in SQL Server

3- 聚类索引行获得了一个独占锁。在这一点上，我们需要考虑到一个问题，在这个事件之后，锁管理器不会因为非聚类索引而释放锁。由于非聚类索引的结构，当表内的聚类索引被修改时，非聚类索引必须被更新。由于这个原因，接下来的4个步骤是由SQL Server执行的。

4- 非集群索引（IX_WorkOrder_ScrapReasonID）页获得一个意图独占锁。

**5-**非集群索引（IX_WorkOrder_ScrapReasonID）页获得了一个独占锁。

6- 非集群索引（IX_WorkOrder_ProductID）页获得了一个意图独占锁。

**7-**非集群索引(IX_WorkOrder_ProductID)页获得了一个独占锁。

在所有的锁活动之后，所有的锁都被释放，lock_relased事件识别这个活动。

SQL删除语句和时态表

SQL Server系统版本的时态表是在SQL Server 2016中引入的，它的目的是存储一个表的修改历史。所以，时态表一个解决方案。

  • 数据审计
  • 启用到行级还原

当我们启用时态表创建时，将创建两个表。

当我们启用一个具有时态表功能的表时，将创建两个表的副本。第一个是原始表，第二个是历史表。

CREATE TABLE dbo.测试表

(ColId INT PRIMARY KEY IDENTITY(1,1) NOT NULL,

TextCol VARCHAR(100),

TextCol2 VARCHAR(100),

ValidSince datetime2(7) GENERATED ALWAYS AS ROW START NOT NULL,

ValidUntil datetime2(7) GENERATED ALWAYS AS ROW END NOT NULL。

PERIOD FOR SYSTEM_TIME(ValidSince, ValidUntil)

)

与 (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TestTable_History))。

去

INSERT INTO TestTable (TextCol,TextCol2) VALUES('Test Text' ,'Test Text2')

Temporal tables

为了删除TestTable中的一行，我们将执行以下查询并分析其执行计划。

DELETE FROM TestTable WHERE ColId=3

Temporal table query plan

如上面的执行计划所示，由于时间表的特点，已经增加了三个操作符。现在我们将逐一解释所有运算符的任务。

集群索引删除操作符表示从TestTable中删除一条记录，并将4个列传递给下一个操作符。

Query plan output

计算Scalar调用**systrandatetime()**函数来获取历史表的ValidUntil值。

Compute scalar operator in SQL Server

集群索引插入为时间表历史表添加一条新行。

Assert操作符验证了ValidSince和ValidUntil列之间的条件。

Assert operator in SQL Server

总结

在这篇文章中，我们进行了一次探索之旅，以了解当我们执行SQL删除语句时幕后的情况。在这个旅程中，我们已经了解到当我们执行删除命令时，数据库引擎会进行哪些活动。

Esat Erkec

Esat Erkec是一名SQL Server专业人员，8年多前作为一名软件开发人员开始了他的职业生涯。他是一名SQL Server微软认证解决方案专家。

他职业生涯的大部分时间都集中在SQL Server数据库管理和开发方面。他目前的兴趣是数据库管理和商业智能。你可以在LinkedIn上找到他。

查看Esat Erkec发表的所有帖子

Esat Erkec

avatar
文章
阅读
粉丝