本文主要是介绍MySQL——关于删除/purge/删除加锁/删除大量数据/truncatedelete,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
本篇介绍MySQL执行删除命令时的一些机制,包括:
MySQL如何删除一行数据?
解释为什么删除数据后表文件大小不变?
purge线程
为什么建议逻辑删除数据而非物理删除?为什么建议自增主键?
为什么建议删除数据的语句条件上加索引?
如何删除大量数据?truncate与delete/drop的区别?
MySQL如何删除一行数据?
InnoDB里的数据都是用B+树的结构组织的;
如图,要删掉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;如下图所示,就是一条记录被多个事务连续更新后的过程;
由图可知:语句所在的事务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 purge线程 相关参数
为什么建议逻辑删除数据而非物理删除?为什么建议自增主键?
逻辑删除的一个天然的好处是方便数据的恢复和归档查询;此外,另一个是避免删完数据导致的页利用率降低,低于阈值时会产生的相邻叶子节点的页合并;
使用自增主键的好处就是避免频繁的"页分裂";结合B+数的结构,叶子节点是有序的,如果数据是按照索引递增顺序插入的,页写满申请新页时,是不移动原有页面的任何记录的,因此索引是紧凑的;注意,这里的"按照索引递增顺序插入"并不是说一定要自然数值连续,而是大小关系连续即可,如 1 2 4 7 9;这也是为什么推荐使用自增主键的原因;
如图,传统B+树页面分裂是按照原页面中50%的数据量进行分裂,随机插入就可能导致原先的数据页放不下了,造成索引的数据页分裂,从而导致分裂后的数据页产生了"空洞";
出现数据空洞时,一般使用重建表DDL语句来优化表存储空间;
为什么建议删除数据的语句条件上加索引?
从性能和加锁来看delete操作;
一般来说,DELETE的加锁和SELECT FOR UPDATE 或 UPDATE 并没有太大的差异;
因为,在MySQL数据库中,执行DELETE语句其实并没有直接删除记录,而是在记录上打上一个删除标记,然后通过后台的一个叫做purge的线程来清理;从这一点来看,DELETE和UPDATE确实是非常相像;事实上,DELETE和UPDATE的加锁也几乎是一样的;
前面有文章分析过全表扫描的加锁方式和更新记录时加锁的规则,可知:如果更新条件可以走索引,则间隙锁会加在条件所在的索引位置的前后间隙;如果查询条件没走索引走全表扫描,则对全表所有行之间加间隙锁;
MySQL delete语句的加锁分析总结可参考:
《MySQL DELETE 删除语句加锁分析》
《mysql delete语句 MySQL死锁系列-常见加锁场景分析》
因此建议删除语句的条件尽量走索引查询,或者先查出这条记录的主键ID,再根据主键ID(唯一索引)条件删除;
如何删除大量数据?
根据where条件删除数据前,会先扫描数据检查是否符合where条件,该阶段会对扫描中所有数据行及行的间隙加锁;若表的数据量大且delete操作无法有效利用索引减少扫描数据量,该步骤对于数据库带来的锁争用、CPU/IO资源的消耗都是巨大的;
单次删除大量数据属于大事务,由于一次修改的表记录太多,无论是产生的binlog日志的数量,还是加锁的范围,都会比较大;较大的加锁范围很容易出现事务执行超时的情况,或阻塞其他更新操作;
此外,大事务的执行时间长,会导致主从延迟不断增加;在一些读写分离(写主库读从库)的场景下,导致读库读不到最新的数据引发业务问题;
下面介绍删除大量数据时,常用的方案及建议:
方案:分批次删除
减小单次删除的数据的数量,拆成分批次执行,减小批次执行间隔,控制执行的速度;此方案一定程度缓解主从延迟不断增加的问题,减小事务涉及的记录行数,减小事务的执行时间,避免一次锁住太多数据阻塞后序SQL命令;
方案:新建表迁移数据
批量删除大量数据的场景一般是做历史数据归档,释放表空间;考虑到删除数据的成本和数据迁移的成本,例如1000W数据表中清理时间小于某个时刻的100W条数据,则可以通过以下步骤操作来减少锁表的时间:
选择不需要删除的数据,并把它们迁移插入到一张相同结构的空表里;
重命名原始表,并给新表命名为原始表的原始表名;
删掉原始表;
此外,建议:
建议在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不记录在日志中,所以它不能激活触发器;
这篇关于MySQL——关于删除/purge/删除加锁/删除大量数据/truncatedelete的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!