MySQL——关于删除/purge/删除加锁/删除大量数据/truncate&delete

谁借莪１个温暖的怀抱￠ 2023-09-24 16:50 57阅读 0赞

本篇介绍MySQL执行删除命令时的一些机制，包括：

*  MySQL如何删除一行数据？

*  解释为什么删除数据后表文件大小不变？

*  purge线程

*  为什么建议逻辑删除数据而非物理删除？为什么建议自增主键？

*  为什么建议删除数据的语句条件上加索引？

*  如何删除大量数据？truncate与delete/drop的区别？

## MySQL如何删除一行数据？ ##

InnoDB里的数据都是用B+树的结构组织的；

![3d36befd1b7446c982f0d3029a43f444.png][]

如图，要删掉R4这个记录，InnoDB引擎只会把R4这个记录标记为"删除"；如果之后要再插入一个ID在300和600之间的记录时，可能会**复用**这个位置；但是，**磁盘文件的大小并不会缩小**；

InnoDB的数据是按页存储的，如果删掉了一个数据页上的所有记录，那么整个页都可以被复用；

**数据"空洞"导致页合并**

*  如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合到其中一个页上，另外一个数据页就被标记为可复用；

*  或者一次删除了整个页上的所有数据，这一整个数据页也会被标记为可复用；

## 为什么删除数据后表文件大小不变？ ##

无论是删除记录还是整个页数据删除，磁盘上的文件不会变小，因为delete只是标记为已删除，而不是真正的物理删除，即表空间数据回收；

delete命令其实只是把记录的位置或者数据页标记为了mark del，在后台purge执行回收后，被删除数据部分对应的磁盘空间标记为"可用"，可以被后续写入操作使用，但磁盘文件的大小是不会变的；也就是说，通过delete命令是不能回收表物理空间的；

## purge线程 ##

本节介绍跟删除相关的purge线程相关的知识；

### **为什么MySQL InnoDB需要Purge操作？** ###

明确这个问题的答案，首先还得从InnoDB的多版本并发控制（MVCC）开始；

"快照"是InnoDB在实现MVCC时用到的一致性读视图，用于支持RC（Read Committed，读提交）和RR（Repeatable Read，可重复读）隔离级别的实现；它没有物理结构，作用是事务执行期间用来定义“我能看到什么数据”；

InnoDB 利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力；

但是这里"快照"并不是说把此刻的整库数据拷贝一份，而是类似git，对于每一行数据，仅记录其在某一次事务中的增量更新，因此大部分的数据都是没有更新的，从而不会像"全量物理拷贝"那样占用巨大的内存；

也就是说，对于数据表中的一行记录，在数据库的不断更新下，可能存在多个数据版本 (row)，而每个数据版本有自己的 row trx\_id；如下图所示，就是一条记录被多个事务连续更新后的过程；

![c884b00f2657427bb8cd7ad153e9b18b.png][]

由图可知：语句所在的事务ID与语句更新结果的数据版本的row trx\_id一一对应；

实际上，历史版本数据的值并不会物理存在，而是在每次需要的时候根据当前版本和 undo log 计算出来的；

在事务不断的执行过程中，undo log占用的空间会不断地扩张；

另外，对于更新和删除操作，InnoDB并不是真正的删除原来的记录，而是设置原记录的delete mark标志位为1，也就是说原数据依然存于数据页上；那么可以标记为mark delete的数据都有哪些类型呢？包括主键记录、二级索引记录：

因此为了清除数据Page和Undo Log膨胀的问题，需要引入purge机制进行回收；

### **purge流程简介** ###

purge的主要任务是将数据库中已经mark del的数据删除，另外也会批量回收undo pages；而数据库的数据页很多，要清除被删除的数据，不可能遍历所有的数据页；

由于所有的变更都有undo log， 因此，执行purge时，MySQL从undo作为切入点，在清理过期的undo的同时，也将数据页中的被删除的记录一并清除；

### 一个关于删除数据后磁盘空间再次利用的实验 ###

1.初始化表t1； session1插入一定数量的数据；在 session2中观察到表的t1.ibd大小在增加；

2. session1删除t1所有记录；执行后稍等等片刻（等purge线程自动清理数据、master线程将数据从缓存落盘），这时候在 session2中观察到t1.ibd文件体积一点也没有减少；

3. session1重新执行少量的插入操作；在 session2中观察到t1.ibd文件体积并没有再次增长；

原因：purge线程将上述实验中被删除数据部分对应的磁盘空间标记为可用，可以被后续写入操作使用，这样就不用再次分配磁盘空间了；

本章参考：

[阿里云-MySQL Innodb Purge简介][-MySQL Innodb Purge]

[MySQL purge线程 相关参数][MySQL purge_]

## 为什么建议逻辑删除数据而非物理删除？为什么建议自增主键？ ##

逻辑删除的一个天然的好处是方便数据的恢复和归档查询；此外，另一个是避免删完数据导致的页利用率降低，低于阈值时会产生的相邻叶子节点的页合并；

使用自增主键的好处就是避免频繁的"页分裂"；结合B+数的结构，叶子节点是有序的，**如果数据是按照索引递增顺序插入的，页写满申请新页时，是不移动原有页面的任何记录的，因此索引是紧凑的**；注意，这里的"按照索引递增顺序插入"并不是说一定要自然数值连续，而是大小关系连续即可，如 1 2 4 7 9；这也是为什么**推荐使用自增主键**的原因；

![c415b36214074aadb98916d3000e72cc.png][]

如图，传统B+树页面分裂是按照原页面中50%的数据量进行分裂，**随机插入就可能导致原先的数据页放不下了，造成索引的数据页分裂**，从而导致分裂后的数据页产生了"空洞"；

出现数据空洞时，一般使用重建表DDL语句来优化表存储空间；

## 为什么建议删除数据的语句条件上加索引？ ##

从性能和加锁来看delete操作；

一般来说，DELETE的加锁和SELECT FOR UPDATE 或 UPDATE 并没有太大的差异；

因为，在MySQL数据库中，执行DELETE语句其实并没有直接删除记录，而是在记录上打上一个删除标记，然后通过后台的一个叫做purge的线程来清理；从这一点来看，DELETE和UPDATE确实是非常相像；事实上，DELETE和UPDATE的加锁也几乎是一样的；

前面有文章分析过[全表扫描的加锁方式][Link 1]和[更新记录时加锁的规则][Link 2]，可知：如果更新条件可以走索引，则间隙锁会加在条件所在的索引位置的前后间隙；如果查询条件没走索引走全表扫描，则对全表所有行之间加间隙锁；

MySQL delete语句的加锁分析总结可参考：

*  [《MySQL DELETE 删除语句加锁分析》][MySQL DELETE]

*  [《mysql delete语句 MySQL死锁系列-常见加锁场景分析》][mysql delete_ MySQL_-]

因此建议删除语句的条件尽量走索引查询，或者先查出这条记录的主键ID，再根据主键ID（唯一索引）条件删除；

## 如何删除大量数据？ ##

根据where条件删除数据前，会先扫描数据检查是否符合where条件，该阶段会对扫描中所有数据行及行的间隙加锁；若表的数据量大且delete操作无法有效利用索引减少扫描数据量，该步骤对于数据库带来的锁争用、CPU/IO资源的消耗都是巨大的；

单次删除大量数据属于大事务，由于一次修改的表记录太多，无论是产生的binlog日志的数量，还是加锁的范围，都会比较大；较大的加锁范围很容易出现事务执行超时的情况，或阻塞其他更新操作；

此外，大事务的执行时间长，会导致主从延迟不断增加；在一些读写分离（写主库读从库）的场景下，导致读库读不到最新的数据引发业务问题；

下面介绍删除大量数据时，常用的方案及建议：

**方案：分批次删除**

减小单次删除的数据的数量，拆成分批次执行，减小批次执行间隔，控制执行的速度；此方案一定程度缓解主从延迟不断增加的问题，减小事务涉及的记录行数，减小事务的执行时间，避免一次锁住太多数据阻塞后序SQL命令；

**方案：新建表迁移数据**

批量删除大量数据的场景一般是做历史数据归档，释放表空间；考虑到删除数据的成本和数据迁移的成本，例如1000W数据表中清理时间小于某个时刻的100W条数据，则可以通过以下步骤操作来减少锁表的时间：

1.  选择不需要删除的数据，并把它们迁移插入到一张相同结构的空表里；

1.  重命名原始表，并给新表命名为原始表的原始表名；

1.  删掉原始表；

此外，**建议**：

*  建议在delete的SQL语句中使用limit，防止一次删除全部满足条件的大量的记录；且limit后面的数量也不能太大，要选择一个合适的量；

*  如果delete的查询条件不走索引，建议先查出满足条件的记录的主键，然后根据主键删除记录；

*  为了临时提高删除速度，在不影响业务的情况下，如凌晨业务低谷期，可以临时删除部分索引；因为MySQL官方手册提到：删除数据的速度和索引数量是成正比的；因此可以先删除这个表中的其他索引，即除了delete的where条件命中的索引，等删除完数据后再重新加上原来的索引；

## truncate与delete的区别？ ##

### truncate都做了哪些操作？ ###

truncate操作实际上分为drop、re-create两步：

*  drop操作的第一个阶段，是对buffer pool页面进行清除的过程，将表相关的数据页从flush链中删除；该操作会导致其他事务在获取buffer pool instance的锁时被阻塞，从而影响数据库性能；

*  drop操作的第二个阶段，是删除ibd磁盘文件的过程；删除数据库物理文件越大，则占用I/O资源消耗越大，删除操作耗时越久；

*  re-create操作阶段，只要被删除的表的.frm文件完好无损，在drop table之后就可以按照原表结构信息进行重建，重建后表的auto\_increment值会被重置；

### truncate与delete的区别？ ###

*  关于删除的数据范围：

truncate不带任何条件，属于表级别删除；

而delete可以根据where条件删除多条满足where条件的数据；

*  关于回滚：

truncate操作是一个不可回滚的ddl操作；MySQL为了提高删除整张表数据的性能，truncate操作其本质上其实是先drop table一次性删除所有数据，然后再re-create table，因此不可回滚；

delete语句是一个可回滚的dml语句；delete语句可以通过where条件对要删除的记录进行选择，数据一行一行的删除，binlog日志会记录每行数据的删除记录，事务提交前可通过undolog回滚，通过undolog恢复数据；

*  对自增主键的影响：

如果一个表中有自增字段，使用TRUNCATE TABLE和没有WHERE子句的DELETE删除所有记录后，这个自增字段将起始值恢复成1；

否则，delete语句不会重置自增主键的起点；

*  对表空间的影响：

truncate对表数据直接进行一次性的物理删除；当表被truncate后，这个表和索引所占用的空间会恢复到初始大小；

delete操作不会减少表和索引所占用的空间，会产生数据空洞导致空间利用率低，可能引发页合并；

*  执行效率：

truncate执行效率高；无需逐行扫描表数据删除，而是直接一次性进行物理删除，快速释放空间占用；

delete效率依赖where条件的编写；大表删除会产品大量的binlog且删除效率低，删除操作可能出现较多的碎片空间而不是直接释放空间占用；

其他：对于由FOREIGN KEY约束引用的表，不能使用TRUNCATE TABLE，而应使用不带WHERE子句的DELETE语句；由于TRUNCATE TABLE不记录在日志中，所以它不能激活触发器；

[3d36befd1b7446c982f0d3029a43f444.png]: https://img-blog.csdnimg.cn/img_convert/3d36befd1b7446c982f0d3029a43f444.png
[c884b00f2657427bb8cd7ad153e9b18b.png]: https://img-blog.csdnimg.cn/img_convert/c884b00f2657427bb8cd7ad153e9b18b.png
[-MySQL Innodb Purge]: https://developer.aliyun.com/article/646471
[MySQL purge_]: https://developer.aliyun.com/article/502721
[c415b36214074aadb98916d3000e72cc.png]: https://img-blog.csdnimg.cn/img_convert/c415b36214074aadb98916d3000e72cc.png
[Link 1]: https://blog.csdn.net/minghao0508/article/details/128179144
[Link 2]: https://blog.csdn.net/minghao0508/article/details/128205386
[MySQL DELETE]: https://blog.csdn.net/Mrcoderdu/article/details/119485777
[mysql delete_ MySQL_-]: https://blog.csdn.net/weixin_39574928/article/details/110861756