问题描述:
最近遇到一个问题,生产上项目某张表的数据量达到了4千多万的数据量,导致每次执行某个sql的时候数据库一直报警。
相关sql:根据某个字段删除一条数据,但是该字段不是索引列,所以删除起来特别慢。
分析原因:
1、表的数据量太大;
2、删除依赖字段不是索引列;
解决办法分析:数据量:5千万
是一张日志记录表,数据可以不需要了;
1、分批删除数据:
10【0.018s】、
1万【0.712s】、
10万【6.071s, 6.554s ,7.396s】、
百万【64.821s】
2、直接将表删除掉
(1)TRUNCATE TABLE 表名;【时间太长不可行,直接报错】
(2)创建临时表、修改大数据量表名为历史表、将临时表修改为使用表;
【可行,修改表名时间不到1秒,0.079秒左右】
(3)drop表 【大概一秒左右】
总结
1、大数据量的表清除数据:
(1)创建临时表、修改大数据量表名为历史表、将临时表修改为使用表;
(2)历史表的清除:drop表
(3)创建表时给相关慢sql的条件字段加索引:
2、相关sql:
修改表名:ALTER TABLE 原表名 RENAME 新表名;
添加索引:ALTER TABLE 表名 ADD INDEX 索引名 (字段名);
DROP表:DROP TABLE 表名;
TRUNCATE表:TRUNCATE TABLE 表名;
DELETE数据:
delete from 表名 LIMIT 要删除的条数;
delete from 表名 WHERE 索引列< 要删除的数据最大值 LIMIT 要删除的条数
引申:
部分数据清除:
(1)判断要删除的数据多还是要留下的数据多;
(2)删除的数据多:
将要留下的数据备份到临时表;
再按照不需要表的方案处理;
最后在将临时表的数据插入到新建的表中;
(3)留下的数据多:
分批删除。可根据上面给的分批方案作为依据,在自己的环境上进行尝试,每次操作的时间不能太长,尽量保持在1秒以内,以保证相关业务可以正常进行。