以下记录最近在数据清洗的时候,遇到的两个问题。
大纲:
- Mysql执行事务sql,中途报错,执行的相关表空间增大,可见数据依旧不变。
- Mysql执行delete删除语句后,表空建没有释放,数据长度不变。
问题分析:
1. Mysql执行事务sql,中途报错,执行的相关表空间增大,可见数据依旧不变
查看当前会话隔离级别
select @@tx_isolation;
查看当前系统隔离级别
select @@global.tx_isolation;
查看当前系统正在执行的事务
SELECT * FROM information_schema.INNODB_TRX;
START TRANSACTION开启一个事务,COMMIT提交事务或者ROLLBACK回滚事务。事务开启后,要么执行成功,要么回滚,回滚将不对数据库做任何改动。
而事实是开启一个事务后,事务执行中报错,数据库不会自动回滚或者提交,所有已经执行的语句会有状态标记,等待你手动执行回滚或者提交。
手动执行commit提交,已执行的正确语句,修改的数据,会正常入库或者执行。手动执行rollback回滚,系统会回滚已经执行的数据,至原始状态。
如果未手动执行commit,或是rollback,则会产生数据碎片,占用空间。如果表中设置了唯一索引,或者其他限制键,再下一次进行数据入库的时候,数据库会报索引或者其他错误。
2.Mysql执行delete删除语句后,表空建没有释放,数据长度不变
delete删除:delete删除的是数据标志状态,并未释放数据文件,因此我们平常使用delete语句的时候,删除数据后,虽然看不见了,但是空间并未释放。
truncate table,删整表,直接删除数据文件,因此效率比delete高,也不存在空间不释放的问题。
delete与truncate的区别这里不详细描述,在涉及到整表删除时,可以用turncate。
解决办法:
以下介绍清除数据碎片的方法:
-- 查询当前正在执行的事务(有事务对目标表进行操作的时候,是不能清除数据碎片的)
SELECT * FROM information_schema.INNODB_TRX;
-- 停止事务
select concat('KILL ',id,';') from information_schema.processlist p inner
join information_schema.INNODB_TRX x on p.id=x.trx_mysql_thread_id where db='库名';
-- 停止事务,或者commit提交,或者rollback回滚,保证没有事务对表进行操作
-- 清除优化碎片数据
OPTIMIZE TABLE TABLE(表名);
-- 清除,优化缓存
FLUSH TABLES;