MS SQL truncate能释放表空间吗?

经常遇到需要清理一些大表的数据的情况,但是发现清完之后表空间并不释放,如何解决。

1.删除表中所有的行,释放数据所占用的自由空间;

2.删除表中所有的行,保留表所占用的空间,留待该表下次使用。

2.如果大批量truncate表,如何解决?

上周同事小姐姐问我:“哈哥你看,我发现MySQL有bug,我下午为了清理磁盘,明明删除了100万条MySQL数据,磁盘不仅没有变小,反而更满了呢??”

“怪不得,其实要删除MySQL数据是有好几种方式的,有些场景下是不应该用DELETE的,比如你这种情况。好了,让我来给你讲一下吧。”

MySQL删除数据的方式都有哪些?

咱们常用的三种删除方式:通过 delete、truncate、drop 关键字进行删除;这三种都可以用来删除数据,但场景不同。

  • DELETE属于数据库DML操作语言,只删除数据不删除表的结构,会走事务,执行时会触发trigger;
  • 在 InnoDB 中,DELETE其实并不会真的把数据删除,mysql 实际上只是给删除的数据打了个标记为已删除,因此 delete 删除表中的数据时,表文件在磁盘上所占空间不会变小,存储空间不会被释放,只是把删除的数据行设置为不可见。虽然未释放磁盘空间,但是下次插入数据的时候,仍然可以重用这部分空间(重用 → 覆盖)。

示例:查看表占用硬盘空间大小的SQL语句如下:(用M做展示单位,数据库名:csjdemo,表名:demo2)

然后执行空间优化语句,以及执行后的表Size变化:

再看看这张表的大小,就只剩下表结构size了。

  • delete 操作是一行一行执行删除的,并且同时将该行的的删除操作日志记录在redo和undo表空间中以便进行回滚(rollback)和重做操作,生成的大量日志也会占用磁盘空间
  • 执行后立即生效,无法找回
  • 执行后立即生效,无法找回
  • 执行后立即生效,无法找回

但对于不同的类型存储引擎需要注意的地方是:

4、小心使用 truncate,尤其没有备份的时候,如果误删除线上的表,记得及时联系中国民航,订票电话:400-806-9553

  • 执行后立即生效,无法找回
  • 执行后立即生效,无法找回
  • 执行后立即生效,无法找回

3、小心使用 drop ,要删表跑路的兄弟,请在订票成功后再执行操作!订票电话:400-806-9553

可以这么理解,一本书,delete是把目录撕了,truncate是把书的内容撕下来烧了,drop是把书烧了

修改数据库的属性,包括它的名称、所有者、连接数限制、对象隔离属性等。

设置应用于将来创建的对象的权限(这不会影响分配到已有对象中的权限)。

修改自定义函数的属性。

修改一个用户组的属性。

用于更改一个large object的定义。它的唯一的功能是分配一个新的所有者。

更改一个现有物化视图的多个辅助属性。

修改一个操作符的定义。

修改一个资源池,指定其他控制组。

对已存在的行访问控制策略(包括行访问控制策略的名称,行访问控制指定的用户,行访问控制的策略表达式)进行修改。

修改一个现有的序列的参数。

ALTER SESSION命令用于定义或修改那些对当前会话有影响的条件或参数。修改后的会话参数会一直保持,直到断开当前会话。

修改SYNONYM对象的属性。

用于设置POSTMASTER、SIGHUP、BACKEND级别的GUC参数。此命令会将参数写入配置文件,不同级别生效方式有所不同。

修改表,包括修改表的定义、重命名表、重命名表中指定的列、重命名表的约束、设置表的所属模式、添加/更新多个列、打开/关闭行访问控制开关。

更改文本搜索配置的定义。用户可以将映射从字串类型调整为字典,或者改变配置的名称或者所有者,或者修改搜索配置的配置参数。

修改全文检索词典的相关定义,包括参数、名称、所有者、以及模式等。

修改数据库用户的属性。

更改视图的各种辅助属性。

用于收集与数据库中普通表内容相关的统计信息,统计结果存储在系统表PG_STATISTIC下。执行计划生成器会使用这些统计数据,以确定最有效的执行计划。

匿名块(Anonymous Block)一般用于不频繁执行的脚本或不重复进行的活动。它们在一个会话中执行,并不被存储。

BEGIN可以用于开始一个匿名块,也可以用于开始一个事务。

可以调用已定义的函数和存储过程。

检查点(CHECKPOINT)是一个事务日志中的点,所有数据文件都在该点被更新以反映日志中的信息,所有数据文件都将被刷新到磁盘。

用来清理数据库连接。允许在节点上清理指定数据库的指定用户的相关连接。

释放和一个游标关联的所有资源。

根据一个索引对表进行聚簇排序。

定义或修改一个对象的注释。

通过COMMIT或者END可完成提交事务的功能,即提交事务的所有操作。

提交一个早先为两阶段提交准备好的事务。

通过COPY命令实现在表和文件之间拷贝数据。

创建一个客户端主密钥对象,该对象可用于加密Column Encryption Key对象。

创建一个列加密密钥,该密钥可用于加密表中指定列。

创建一个新的外部数据源对象,该对象用于定义openGauss要连接的目标库信息。

创建一个新的数据库。缺省情况下新数据库将通过复制标准系统数据库template0来创建,且仅支持使用template0来创建。

使用CREATE DIRECTORY语句创建一个目录对象,该目录对象定义了服务器文件系统上目录的别名,用于存放用户使用的数据文件。

在指定的表上创建索引。

定义一种新的过程语言。单机和集中式暂不支持创建过程语言。

训练机器学习模型并保存模型。

创建一个新的存储过程。

创建一个资源池,并指定此资源池相关联的控制组。

对表创建行访问控制策略。

用于向当前数据库里增加一个新的序列。序列的Owner为创建此序列的用户。

定义一个新的外部服务器。

创建一个同义词对象。同义词是数据库对象的别名,用于记录与其他数据库对象名间的映射关系,用户可以使用同义词访问关联的数据库对象。

在当前数据库中创建一个新的空白表,该表由命令执行者所有。

创建分区表。分区表是把逻辑上的一张表根据某种方案分成几张物理块进行存储,这张逻辑上的表称之为分区表,物理块称之为分区。分区表是一张逻辑表,不存储数据,数据实际是存储在分区上的。

在数据库中创建一个新的表空间。

创建新的文本搜索配置。一个文本搜索配置声明一个能将一个字符串划分成符号的文本搜索解析器,加上可以用于确定搜索对哪些标记感兴趣的字典

创建一个触发器。 触发器将与指定的表或视图关联,并在特定条件下执行指定的函数。

在当前数据库中定义一种新的数据类型。定义数据类型的用户将成为该数据类型的拥有者。类型只适用于行存表

定义一个游标,用于在一个大的查询里面检索少数几行数据。

用于删除前面编写的预备语句。如果用户没有明确删除一个预备语句,那么它将在会话结束的时候被删除。

用于删除前面编写的预备语句。如果用户没有明确删除一个预备语句,那么它将在会话结束的时候被删除。

从指定的表里删除满足WHERE子句的行。如果WHERE子句不存在,将删除表中所有行,结果只保留表结构。

删除一个客户端加密主密钥(CMK)。

删除一个列加密密钥(cek)。

删除一个已存在的函数。

强制删除数据库中已有的物化视图。

删除一个已训练完成保存的模型对象。

删除一个数据库角色所拥有的数据库对象。

删除已存在的存储过程。

删除表上某个行访问控制策略。

从当前数据库里删除序列。

删除现有的一个数据服务器。

删除指定的SYNONYM对象。

删除已有文本搜索配置。

删除一个用户定义的数据类型。

删除用户,同时会删除同名的schema。

数据库中强制删除已有的视图。

END可完成提交事务的功能,即提交事务的所有操作。

执行一个前面准备好的预备语句。因为一个预备语句只在会话的生命期里存在,那么预备语句必须是在当前会话的前些时候用PREPARE语句创建的。

在指定的节点上执行SQL语句。一般情况下,SQL语句的执行是由集群负载自动分配到合适的节点上,execute direct主要用于数据库维护和测试。

显示SQL语句的执行计划。

FETCH通过已创建的游标来检索数据。

对角色和用户进行授权操作。

向表中添加一行或多行数据。

将目标表和源表中数据针对关联条件进行匹配,若关联条件匹配时对目标表进行UPDATE,无法匹配时对目标表执行INSERT。此语法可以很方便地用来合并执行UPDATE和INSERT,避免多次执行。

MOVE在不检索数据的情况下重新定位一个游标。MOVE的作用类似于FETCH命令,但只是重定位游标而不返回行。

为当前事务做两阶段提交的准备。

修改数据库对象的属主。

会以全量刷新的方式对物化视图进行刷新。

为表中的数据重建索引。

将指定的运行时参数恢复为缺省值。这些参数的缺省值是指postgresql.conf配置文件中所描述的参数缺省值。

用于撤销一个或多个角色的权限。

回滚当前事务并取消当前事务中的所有更新。

为当前事务做两阶段提交的准备。

用于在当前事务里建立一个新的保存点。

SELECT用于从表或视图中取出数据。

SELECT INTO用于根据查询结果创建一个新表,并且将查询到的数据插入到新表中。

用于修改运行时配置参数。

设置当前事务检查行为的约束条件。

设置当前会话的当前用户标识符。

把当前会话里的会话用户标识和当前用户标识都设置为指定的用户。

SET CONSTRAINTS设置当前事务检查行为的约束条件。

SHOW将显示当前运行时参数的数值。

通过START TRANSACTION启动事务。如果声明了隔离级别、读写模式,那么新事务就使用这些特性,类似执行了SET TRANSACTION。

清理表数据,TRUNCATE快速地从表中删除所有行。

更新表中的数据。UPDATE修改满足条件的所有行中指定的字段值,WHERE子句声明条件,SET子句指定的字段会被修改,没有出现的字段则保持它们的原值。

VACUUM回收表或B-Tree索引中已经删除的行所占据的存储空间。在一般的数据库操作里,那些已经DELETE的行并没有从它们所属的表中物理删除;在完成VACUUM之前它们仍然存在。因此有必要周期地运行VACUUM,特别是在经常更新的表上。

根据给定的值表达式计算一个或一组行的值。它通常用于在一个较大的命令内生成一个“常数表”。

我要回帖

更多关于 存储过程truncate不了table 的文章

 

随机推荐