• SQL删除数据的三种方式truncate、delete、drop的区别
  • 发布于 2个月前
  • 286 热度
    0 评论
truncate、delete、drop区别概述
它们 的区别如下表所示:
区别点 drop truncate delete
执行速度 较快
命令分类 DDL(数据定义语言) DDL(数据定义语言) DML(数据操作语言)
删除对象 删除整张表和表结构,以及表的索引、约束和触发器。 只删除表数据,表的结构、索引、约束等会被保留。 只删除表的全部或部分数据,表结构、索引、约束等会被保留。
删除条件(where) 不能用 不能用 可使用
回滚 不可回滚 不可回滚 可回滚
自增初始值 - 重置 不重置
truncate、drop 和 delete 的区别主要有以下 6 点:
1.执行速度:drop > truncate > detele。
2.delete 和 truncate 只删除表数据,而 drop 会删除表数据和表结构以及表的索引、约束和触发器。
3.delete 可以加 where 条件实现部分数据删除,而 truncate 和 drop 不能加 where 条件是整体删除。
4.truncate 和 drop 是立即执行,且不能恢复;而 delete 会走事务,可以撤回和恢复。
5.truncate 会重置自增列为 1,而 delete 不会重置自增列。
6.truncate 和 drop 是 DDL 语句,而 delete 是 DML 语句。

为什么delete相对比较慢
简单来说,delete 是逐行执行的,并且在执行时会把操作日志记录下来,以备日后回滚使用,所以 delete 的执行速度是比较慢的;而 truncate 的操作是先复制一个新的表结构,再把原先的表整体删除,所以它的执行速度居中,而 drop 的执行速度最快。往深入说,为什么delete相对比较慢,原因复杂:
1、查询的表,没有加索引
写了一个查询sql,结果查询的条件字段没有索引,导致需要全表扫描,查找数据,这是大家遇到最多,也是最容易理解的。这种,一般,在表数据量比较少时,如低于十万级,不会觉得慢,但是,当表中数据量达到或超过十万级时,就会体现出查询时间特别长了。

2、查询的索引,无效
知道索引很重要,所以,一般建表的时候,都会加上一些索引,但是,有了索引,并不代表查询速度就一定会快,因为,还要看能否正确使用索引。
查询条件,没有索引字段
查询条件使用 or, 选择式过滤条件,导致索引无效
查询条件使用like,且从头部开始模糊匹配,导致索引无效
查询条件不满足复合索引的最左匹配原则,导致索引无效
查询条件,索引列使用了隐式类型转换,导致索引无效
查询条件,索引列使用了聚合函数,导致索引无效
查询条件,索引列使用了算术运算(+、-、...),导致索引无效
查询条件,索引列使用了逻辑运算(!=、<>、is null、 is not null ...),导致索引无效
左右关联时,字段类型不一致,导致索引无效
3、查询使用了临时表
临时表可能大家不知道,但是回表查询,大家可能听说过,就是说一次查询不满足,还需要再查一次,查两次才能出结果,这当然就会慢啦。哪临时表一般都是怎么产生的呢?通过一次查询返回的数据,要进行下一步的过滤、显示时,发现返回的数据中不满足过滤条件,或者没有显示的字段,又要回头查一次原表,从原表中获取满足条件的数据,这些数据,就放在临时表中。

本来,回头查一次,就已经消耗了时间了,奈何,临时表还有空间大小限制,占用内存空间,还可能空间不够用,存放不下所有数据。所以,一般,只要出现使用了临时表,这个sql的性能都很差。

4、join或子查询,太多
关联查询,在实际工作中,非常场景,关联的表越多,那么,数据过滤筛选就越来复杂,时间自然就会越长了。所以,一般而言,关联表不建议超过3个,而且数据量小的表放左边,大的表放在右边。

5、查询结果数据量,太大了
查询结果数据量太大,常见的有两种,第1种,就是直查的表数据量太大,如千万级。一张表千万级,即使建了索引,索引文件也会很大,深度也会很深,查询速度,自然就会很慢了。第2种,就是联表笛卡尔积量太大。对于第一种,优化建议,一般是对表采用分表分区了。而第二种,就简单粗暴的sql拆分优化。

6、锁竞争
现在MySQL的表一般都是InnoDB存储引擎,这种引擎的表是行锁,每次锁定一行。即,如果有一个事务在操作某一行数据,就会锁定这一行的操作行为,其他事务不能操作,直到前一个事务操作完成,commit数据变更之后,后面的事务才能获取操作。这就会出现,一个事务,做变更,没有结束,后面的所有事务操作就得等待,如果此时又有多个事务在排队等待,当前一事务操作结束,等待的事务就会竞争抢锁,这种‘你不仁,我不义’,一旦发生,SQL的性能就会很慢了。

7、limit分页,太深
有些时候,我们需要偏移一定量数据之后,获取某些数据,就很容易想到用limit,但是,如果偏移量很大时,就会发现SQL执行起来非常非常慢了,因为,偏移量会分页读取到buffpool中,数据量大,占用的buffpool空间就会大,而这个空间大小是配置的,一般不会很大,所以,导致了慢sql。对于这个问题的优化,建议写一个过滤条件,再与limit结合实现。

8、配置参数,不合理
我们很多时候使用数据库,都是安装了之后,就直接用,不会对数据库配置参数进行过多了解和设置。比如buff相关的参数这就是数据库中一类非常重要的配置参数,在mysql中,有很多带有 buff、cache、size、length、max、min、limit等字样的配置参数,都是非常重要的配置参数。这些配置参数,是直接关系数据库的性能的。如果,你数据库安装在一个配置很高的机器上,但是,这些配置参数却不知道修改,都用默认值。哪也就只能哀怨“这么高的硬件配置,性能怎么还是这么差?

9、频繁刷脏页
脏页,是内存数据页和磁盘数据页不一致。这个一般发生在数据更新操作中。更新数据,需要先把数据读取出来,然后再内存中更新,然后再生成日志文件,再回放日志文件,实现表数据更新。而当更新数据量大,buffpool写满,或者是后续生成的回放日志文件写满,都会导致这个操作过程变慢。对于这种问题优化,一般建议是少批量修改,多次提交。

10、系统资源,不够用

数据库是用来存储数据的,要频繁进行磁盘操作,所以,一般,我们都会选择磁盘IO性能比较好的机器作为数据库服务器。同时,数据库还要经常进行数据交换,所以,也需要有足够的内存,所以,内存也会相应要求高些。而这些硬件,仅仅只是作为数据库服务器硬件选择的基本要求;数据库也是一个软件,软件也是安装在操作系统中的,所以,也会受操作系统的参数的一些限制,所以,当硬件资源不够用,或者达到了系统参数限制值时,也是会导致操作变慢的。


用户评论