假设有一个表有3000万条记录,需要在业务不停止的情况下删除其中status=1的所有记录,差不多有600万条。 如果直接使用delete from tab_name where status=1; 会触发lock wa
假设有一个表有3000万条记录,需要在业务不停止的情况下删除其中status=1的所有记录,差不多有600万条。
如果直接使用delete from tab_name where status=1; 会触发lock wait timeout exceed的错误,因为这条语句涉及的记录数太多。
drop | truncate | delete | |
执行过程 | DDL语句,删除整张表和表结构,以及表的索引、约束和触发器。 | DDL语句,只删除表数据,表的结构、索引、约束等会被保留。 | DML语句,删除表中数据 |
回滚 | 不可 | 不可 | 可以 |
truncate table table_name 后立刻释放磁盘空间,并重置auto_increment的值。 | 事务会记录到日志,并且有行、表锁; delete删除不释放磁盘空间,但后续insert会覆盖在之前删除的数据上。 |
执行效率:drop > truncate > delete
DELETE FROM tab_name WHERE status=1 ORDER BY status LIMIT 10000;
注意:当需要用到order by排序时,必须order by + limit联用,否则order by 就会被优化器优化掉,被认为无意义。
说明:如果delete的where语句不在索引上,可以先找主键,然后根据主键删除数据库。
1)加limit的的优点:
针对上述第二点,前提是statusid上加了索引,大家都知道,加锁都是基于索引的,如果statusid字段没索引,就会扫描到主键索引上,那么就算statusid = 1 的只有一条记录,也会锁表。
2)单条删除、更新操作,使用limit1绝对是个好习惯:
单条更新和删除操作时,如果SQL中有limit 1;这时就return了,否则还会执行完全表扫描才return。效率不言而喻。
一个表有1亿6000万的数据,有一个自增ID。最大值就是1亿6000万,需要删除大于250万以后的数据,有什么办法可以快速删除?
看到mysql文档有一种解决方案:Http://dev.mysql.com/doc/refman/5.0/en/delete.html
删除大表的多行数据时,会超出innod block table size的限制,最小化的减少锁表的时间的方案是:
1)选择不需要删除的数据,并把它们存在一张相同结构的空表里
INSERT INTO t_copy SELECT * FROM t WHERE ... ;
2)利用rename原子操作,重命名原始表和复制表
RENAME TABLE t TO t_old, t_copy TO t;
3)删掉原始表
DROP TABLE t_old;
在My SQL数据库使用中,有的表存储数据量比较大,达到每天三百万条记录左右,此表中建立了三个索引,这些索引都是必须的,其他程序要使用。由于要求此表中的数据只保留当天的数据,所以每当在凌晨的某一时刻当其他程序处理完其中的数据后要删除该表中昨天以及以前的数据,使用delete删除表中的上百万条记录时,MySQL删除速度非常缓慢,每一万条记录需要大概4分钟左右,这样删除所有无用数据要达到八个小时以上,这是难以接受的。
查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的(对于DML操作,如果有索引会更新索引信息,所以会比较慢),于是删除掉其中的两个索引后测试,发现此时删除速度相当快,一百万条记录在一分钟多一些,可是这两个索引其他模块在每天一次的数据整理中还要使用,于是想到了一个折中的办法:
来源地址:https://blog.csdn.net/liuxiao723846/article/details/130360635
--结束END--
本文标题: Mysql在大表中删除大量数据的优化
本文链接: https://www.lsjlt.com/news/387566.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0